DATATABLES Client Control Table

The DATATABLES client control table is used primarily to disable cloning for one or more of the secondary tables mapped from one DMSII data set. For example, a DMSII data set with several OCCURS items generates multiple relational database tables. If you do not want to clone particular secondary tables, use the active column in the DATATABLES client control table to turn off cloning for those secondary tables.

The DATATABLES client control table contains the entries for each of the relational database tables mapped from the DMSII data sets listed in the DATASETS table. These entries include relational database information rather than DMSII information. For example, the DMSII data set name (in the column named dataset_name) is listed along with the equivalent relational database table name (in the column named table_name). Since a data set can be mapped to several relational database tables (such as when a data set contains OCCURS items), the prim_table column is used to identify the primary table.

The following table contains descriptions of each column in the DATATABLES client control table. It also provides the abbreviated column name from the trace file when applicable. (This name occurs in the trace.log file when you enter the dbutility display command.)






This column contains the name of the data source that identifies the DMSII database from which this data was taken. The data source name is defined when you run dbutility define. It must match the data source name in the DBServer parameter file on the host.



This column contains the name of the DMSII data set from which this table was mapped.


table name

This column contains the name of the table as it appears in the relational database. DMSII data sets correspond to relational tables. To change this name, see Relational Database Table and Column Names.



This column contains the name of the relational database index that is created for fast access to this table. If the table has no index, this column is blank. This index is created via the DATABridge Client script named script.index.tablename. The value in this column is the index name used in the CREATE INDEX SQL statement.




This column, which is zero for all tables mapped from fixed-format data sets, contains the record type of a DMSII variable-format data set. See Variable-Format Data Sets.

Record Type



For a variable-format data set, this represents records that have no variable part.


Represents the variable-format record type as defined in the DASDL.



This column contains the nesting level of OCCURS in the DMSII database. For example, an OCCURS table created from another OCCURS table has an occurs_level of 2. The original OCCURS table has an occurs_level of 1.



This number is used by the SQL*Loader and bcp scripts. The DATABridge Client assigns consecutive numbers to the tables it defines for a data source during the define command. Each table within a data source has a unique table number, and the numbers begin with 1. The redefine command assigns numbers to new tables and redefined tables starting with the highest table number plus 1. Existing tables get their old table numbers restored.



The value of this column determines whether or not a table is cloned during a process or clone command. The default is 1, which indicates that the table will be cloned. If you change this value to 0, the table is not cloned. To disable cloning for an entire set of tables related to a DMSII data set, see DATASETS Client Control Table.



The create_suffix column enables you to specify a value that identifies the index of the create_suffix string defined in the configuration file. For more information, see "create_table_suffix" in Generate Command Parameters.



The index_suffix column enables you to specify a value that identifies the index of the index_suffix string defined in the configuration file. For more information, see "create_index_ suffix" in Generate Command Parameters.



The original_name column defines the original table name. The DATABridge Client saves the original name of the renamed tables in this column so they can be identified during redefine commands.



This column indicates whether or not this is a primary table.




The dt_options column uses the following bits:





DTOPT_Table_Renamed - The table was renamed by the user.


DTOPT_Index_Renamed - The table’s index was renamed by the user.


DTOPT_User_Table - This table was created by the by user. Not supported in DATABridge Client 6.1.


DTOPT_No_aux_stmts - This option inhibits the use of auxiliary statements for a given table during a process or clone command when the configuration parameter aux_stmts (default 10) is not zero.


DTOPT_Occ_Depends - This option, automatically set by the Client during a define or a redefine command, indicates that an OCCURS table (occurs_level > 0) contains an item with an OCCURS DEPENDING ON clause. This bit is used during update processing to properly handle cases where the value of the dms_depends_num item of an OCCURS DEPENDING ON clause changes.


DTOPT_All_Keys - Do not change this value.


DTOPT_No_Unique_Key - Do not change this value.


DTOPT_Preserve_Deletes - Do not change this value.


DTOPT_HistoryTable - This option, which is set by the define and redefine commands, indicates to the Client that this table is a history table and that all records should be treated as inserts to the history table.

Caution: Clearing this bit can corrupt history tables because it causes the Client to treat records as creates, deletes, and modifies instead of inserts.


DTOPT_UserSP - Indicates that the table uses the stored procedure m_tablename to perform customized functions instead of using the procedure i_tablename for an insert. This procedure is used to merge records rather than insert them into the table.

This bit is most often used for Miser databases.


DTOPT_Clustered_Index - This option tells the DATABridge Client to create a clustered index for this table. You can also set this option via the use_clustered_index parameter. See use_clustered_ index.


DTOPT_Primary_Key - This option tells the DATABridge Client to create a primary key (instead of a unique index) for this table. When creating the script to create a primary key constraint, the Microsoft SQL Server client uses the value of the DTOPT_Clustered_Index to determine whether to add the NONCLUSTERED clause to the SQL. If this second option bit is not set, the NONCLUSTERED clause is added. You can also set this option via use_primary_key. parameter.


DTOPT_Delete_Seqno - This option is automatically set by the define or redefine command when the delete_seqno mask is set in the default_user_columns parameter value.


DTOPT_Table_Split - This option is automatically set by the define or redefine command when the table is part of a split table. Do not modify this value.


DTOPT_ConcatItems - This bit is automatically set by the define or redefine command and indicates that the table contains concatenated items. Do not modify this bit.


DTOPT_Clob_in_Table - This bit, which is only used by th Oracle client, indicates that the table contains an item whose data type is CLOB.




These bits are used by the redefine command.


CHG_new - New entry


CHG_modified - Modified entry


CHG_del_before - One or more entries before this one were removed.


CHG_del_after - One or more entries after this one were removed.


CHG_index_changed - This bit, which is set by the redefine command indicates that the table’s index changed. The reorg command uses this bit as an indication that it must drop the index for the table and recreate it.


CHG_IndexType_changed - The index type changed primary key versus unique index or in the case of SQL Server unique index versus clustered index.


CHG_IndexName_changed - The index name changed, the client needs to drop the index using the old name and create the new index using the new name. The old name is saved in a temporary file by the redefine command. The reorg command deletes this file once the new index is successfully created


CHG_new_hist_tab - History tables were added for the data set. The redefine command sets this bit when it finds a new history table. The reorg command then can create these tables and we can continue processing without re-cloning the data set.



The update_count column represents the smoothed average number of updates for the table over the specified period of time.



This update_interval column represent the period of time (in milliseconds) that the update_count spans.

The multi-threaded update code uses the update_count column to balance the thread load. The update_interval column will start out as 0 and increase until it reaches the value representing one hour, after which it never changes, as the average is smoothed to reflect the number of updates for each table over the last hour.

The update_count and update_interval columns were added to hold the table update statistics.



This column, which shadows the dt_options column, contains a bit mask that represent customized columns in dt_options. This column is used by the redefine command to restore the portion of dt_options that has been customized while leaving the remaining bits intact.