Customizing tables and table elements
This topic includes the following sections:
- Viewing table information
- Deleting a table from the system catalog
- Adding a deleted table back into the system catalog
- Modifying table access levels
- Changing the location or names for table data files
- Viewing columns in a table
- Viewing information about a column
- Deleting a column
- Viewing indexes and index segments
You can use DBA to view information on tables, columns, indexes, and segments, and to delete tables and columns. You'll use a conversion setup file to change a table’s access level, to add deleted tables back into the system catalog, and to change locations and names for data files.
Viewing table information
1. | Open the system catalog in DBA (see Opening the system catalog in DBA), and close any open input windows or lists. |
2. | Select Maintenance > Tables. The Table List window displays the following table information. It also lists the creation date for the system catalog (in the information line at the bottom of the list). To view hidden areas of the Table List, List > Toggle View. |
Table name
The alphanumeric name of the table.
Type
The type of table:
SYSTEM = System table (created by dbcreate)
DATA = Data table (from your Synergy database)
Owner
The table owner. If is marked as PUBLIC, it is available to all users. Owner cannot be modified.
Open filename
The name of the file that contains this table. The Open filename field may include an environment variable, which must be set in the connect file, in an environment setup file, or in the environment. This field corresponds to the Open filename field in Repository.
Access
The access level assigned to each table. You can change this level by using a conversion setup file. See Modifying table access levels.
3. | To view more information on a specific table, highlight the table name in the Table List Window, and then select Table Maintenance > View Table. The Table window displays the following information about the table you selected. You cannot make changes in this window. |
Table owner
The table’s owner. An owner of PUBLIC indicates that the table is available to all users.
Table name
The alphanumeric name of the table.
Table type
The type of table:
SYSTEM = System table (created by dbcreate)
DATA = Data table (from your Synergy database)
File type
The type of file: ISAM, RELATIVE, or ASCII (ASCII sequential)
Access level
The access level assigned to this table. (For more information about access levels, see Modifying table access levels.)
# of columns
The number of columns in the table.
Record size
The number of characters (bytes) allowable in a single record in this table.
File name
The actual name and location of the data file, including an environment variable, if applicable. This corresponds to the Open filename field in the Table List.
To locate a table in a long list of tables,
1. | Make sure the Table List window is active. (If the Table window is open, close it.) |
2. | Select List > Find. |
3. | In the small window that’s displayed, type the table name and select OK. The DBA program highlights the first match. |
The Find feature does not support wildcard characters, such as asterisk (*) or question mark (?).
4. | To view the attributes of the highlighted table, press Enter. |
Deleting a table from the system catalog
Deleting a table removes user access to that table, and it removes the table, its columns, and its indexes only from the system catalog. The repository definition, relations, and the actual data file remain unaltered. (Only the reference to the data table is removed from the system catalog.) You cannot delete system tables (see System catalog for a list of system tables).
Rather than using DBA to delete a table, we recommend that you use DROP TABLE or use S/DE Repository to make such changes and then regenerate the system catalog. |
To use DBA to delete a table,
1. | Open the Table List window. (See Viewing table information.) |
2. | In the Table List window, highlight the name of the table to be deleted. |
3. | Select Table Maintenance > Delete Table. A window is displayed with the selected table’s name, file location, and the following prompt: |
Do you want to delete the current entry?
4. | To delete the table, select Yes. |
If the SODBC_CNVFIL environment variable is set to the location and filename of a conversion setup file, deleting a table in DBA automatically sets the conversion setup file’s IN|OUT setting for the table to OUT. This prevents DBA and dbcreate from reinserting the table if you regenerate the system catalog using the -c option with dbcreate or the “Clear and recreate catalog” option with DBA.
To delete a table from the system catalog and keep it out, even when the system catalog is regenerated, do the following:
1. | Generate a conversion setup file. |
2. | Set the SODBC_CNVFIL environment variable to the conversion setup file. |
3. | Delete the table in DBA using the above procedure or change the IN|OUT setting for the table to OUT by manually editing the conversion setup file (see Generating and editing a conversion setup file). |
4. | Do one of the following: |
- Use dbcreate from the command line to regenerate the system catalog. Be sure to use the -c option and specify the conversion setup file as input. See dbcreate utility for more information.
- Use DBA to regenerate the system catalog. Be sure to use the “Clear and recreate catalog” option and specify the conversion setup file as input.
Adding a deleted table back into the system catalog
There are two ways to add a deleted table back into the system catalog. You can regenerate the system catalog without using a conversion setup file and then regenerate the conversion setup file, or you can do the following:
1. | Generate a conversion setup file. |
2. | Open the conversion setup file in a text editor and manually change the IN|OUT setting for the table. |
3. | Using the conversion setup file as input, regenerate the system catalog. |
For more information, see Generating and editing a conversion setup file.
Modifying table access levels
When you first generate the system catalog, all tables are set by default with an access level of 100 (except the GENESIS_* tables, which are set to 99). You can change the access level if necessary. For more information on table access levels, see Understanding access levels for tables and groups.
We recommend setting table access levels to even numbers. An even-numbered access level enables the xfODBC driver to read from, but not write to a table. If you set a table’s access level to an odd number, a user may update a record that is in use by another application. |
1. | Generate a conversion setup file. |
2. | Open the conversion setup file in a text editor and manually change the levels. In the following, for example, the access levels for the CUSTOMERS, ORDERS, and VENDORS tables have been modified: |
; Synergy/DE xfODBC Conversion Setup File ; Setup file name: GENESIS_HOME:SODBCCNV.INI ; Generated by : XFDBA.DBR Version 10.0.3c ; Creation date : 13-NOV-2012, 07:30:42 CUSTOMERS IN ACC=200 OPEN=XFDBTUT:customer ORDERS IN ACC=200 OPEN=XFDBTUT:orders PLANTS IN ACC=100 OPEN=XFDBTUT:plants VENDORS IN ACC=200 OPEN=XFDBTUT:customer
3. | Using the conversion setup file as input, regenerate the system catalog. For more information, see Generating and editing a conversion setup file. |
Changing the location or names for table data files
1. | Generate a conversion setup file. |
2. | Open the conversion setup file in a text editor and manually change the path or filename of the data file. |
3. | Using the conversion setup file as input, regenerate the system catalog. For more information, see Generating and editing a conversion setup file and Notes on regenerating the system catalog. |
Viewing columns in a table
You can view a list of all the columns in a table, and you can view the attributes of each individual column. Note that the terms column and field are often used interchangeably. When a database is presented visually as a data sheet, typically the top row lists all the columns. On a form in which a user enters data, these columns are represented as fields. For our purposes, a repository field is equivalent to a system catalog column.
To view a list of columns in a table,
1. | Open the Table List window. (See Viewing table information.) |
2. | In the Table List Window, highlight a table name. |
3. | Select Table Maintenance > View Columns. |
The Column List window displays a list of the columns, sorted by offset, along with attributes of these columns.
Column name
The heading name for each data column.
Type
The Synergy data type for the column (ALPHA, DATE, USER, etc.).
Size
The size of the field (in bytes) before the system catalog was generated.
Precision
The number of characters to the right of the decimal point in an implied-decimal field.
Position
the column’s start position (in bytes) from the leftmost character in the record. The columns in this window are sorted by their offset position.
Viewing information about a column
To view more complete information about an individual column,
1. | Open the Column List window. (See Viewing columns in a table.) |
2. | In the Column List window, highlight the column. |
3. | Select Column Maintenance > View Column. |
The Column window displays the following information. You cannot modify the information in this read-only window.
Column name
The name of the column.
SQL type
The data type as defined by the SQL_DescribeCol ODBC API function (SQL_TIME, SQL_BIT, etc.).
The following fields are in the “Synergy details” portion of the Column window.
Type
The Synergy data type (ALPHA, DATE, etc.).
Position
The column’s start position (in bytes) from the leftmost character in the record.
Size
The size of the field as defined in Repository.
Signed
Indicates whether a numeric column in the system catalog is signed or unsigned (which is determined by whether “Negative allowed” or a positive range of values is selected for the corresponding field in the repository).
Precision
The number of characters to the right of the decimal point in an implied-decimal field. If an implied-decimal field has no characters to the right of the decimal point, this field is blank.
User data
The information entered in the S/DE Repository User data field. This can contain any string of up to 30 characters and is available only for user-defined repository fields. xfODBC checks the content of this field when it generates a system catalog. If this field contains a string in the format ^CLASS^=date_format, xfODBC interprets the column as a date with the specified format. See Date and time fields.
The following fields are in the “Support details” portion of the Column window:
Type
The internal data type used by xfODBC.
Ordinal position
The ordinal position of the column in the record. A column with an ordinal position of 1 appears at the leftmost position in a data sheet when viewed using an ODBC-enabled database application.
Length
For use by Synergy/DE Developer Support.
Internal format
For use by Synergy/DE Developer Support.
Null allowed
Indicates whether a null value (or equivalent) is allowed in this column: Y for yes or N for no. See Preventing null updates and interpreting spaces, zeros, and null values.
Deleting a column
Using DBA to delete tables and columns can lead to unpredictable results. We strongly recommend that you make such changes using S/DE Repository and then regenerate the system catalog. You cannot delete columns from the system tables (the GENESIS_* tables). |
1. | Open the Column List window. (See Viewing columns in a table.) |
2. | In the Column List window, highlight the column to be deleted. |
3. | Select Column Maintenance > Delete Column. |
A window is displayed with the selection’s table name, column name, and the following prompt:
Do you want to delete the current entry?
Deleting a column removes only its reference from the system catalog. This action does not delete actual data. However, a deleted column is no longer accessible to ODBC-enabled applications.
Viewing indexes and index segments
Indexes are a superset of keys. They are defined in the repository used to generate the system catalog, and they enable quicker access to records.
1. | Open the Table List window. (See Viewing table information.) |
2. | In the Table List window, highlight the table. |
3. | Select Table Maintenance > View Indexes. |
The Index List window displays a list of indexes with the following information:
Index
The name of the index as defined in the repository.
Type
The index type: UNIQUE or NON-UNIQUE (duplicates allowed)
Segments
The number of segments for the index.
4. | To view segments for an index, highlight the index in the Index List window and select Index Maintenance > View Segments. |
The Segment List window displays the following information:
Segment#
The sequence of the segment in the index.
Field name
The field name of the index segment.
Order
The sort order of the index: ASC for ascending or DESC for descending.