Customizing tables and table elements

This topic includes the following sections:

 

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).

Note

Rather than using DBA to delete a table, we recommend that you use DROP TABLE or use Synergy/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:

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.

Note

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 Synergy/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

Important

Using DBA to delete tables and columns can lead to unpredictable results. We strongly recommend that you make such changes using Synergy/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.