Skip to content

Databridge Client Control Tables

This chapter describes the six Client control tables and the properties in each table that can be customized. For best results, use the Administrative Console's Customize command and other commands to customize your Client control tables.

Control tables do not contain replicated DMSII data. To store replicated data, the relational database uses data tables, which are created using information from the control tables. The control tables hold the layout information of the DMSII database (from the DMSII DESCRIPTION file) and the layout of the corresponding relational database tables. Each relational database has one set of Client control tables that includes the following tables: DATASOURCES, DATASETS, DATATABLES, DMS_ITEMS, DATAITEMS and AF_STATS. The AF_STATS table, which was added in version 7.0, is used to optionally store the audit file statistics that allow users to look at the audit file processing statistics for the last 9999 audit files.

The Databridge Client uses several columns of the control tables to determine how DMSII database objects are represented in the relational database layout. While Databridge makes many of these decisions, some properties can be customized by using the Administrative Console's Customize command or user scripts. For example, you can rename columns, combine like items, and flatten OCCURS.

For instructions on backing up the Client control tables, see Backing Up and Maintaining Client Control Tables.

Changes in Databridge Client 7.0 Control Tables

When you run the dbfixup utility it automatically updates your existing Client control tables to ensure compatibility with previous releases. For more information, see the Databridge Installation Guide.

Besides the addition of the AF_STATS tables, the only other changes to the client control tables are the additions of the dflt_options column to the DATASETS, DMS_ITEMS, DATATABLES and DATAITEMS tables and the addition of the column dflt_options2 to the DMS_ITEMS table.

DATASOURCES Client Control Table

The DATASOURCES Client control table contains the data sources defined for the Databridge Client. Each data source represents both a connection to a Databridge Server on the MCP (or Enterprise Server) and a DMSII database. You can define more than one data source within the DATASOURCES Client control table as long as each data source name is unique. All of the data sources you define within the DATASOURCES table apply to one relational database.

Note

Do not modify Client control tables directly. Instead, use the commands in the Administrative Console's Settings menu to customize data sources.

The following table contains descriptions of each column, in the order in which it appears in the DATASOURCES table.

Column Description
data_source This value is the name you give the data source when you use the Databridge Client define command. The name can be a maximum of 30 characters, and it must match one of the following:
  • The entry for SOURCE in the DBServer control file on the host.
  • A base or filtered source as defined for Enterprise Server.
    hostname This column specifies the host name or IP address of the Databridge Server.
    hostport This column specifies the TCP/IP port number used to connect to the Databridge Server.
    hostprot Reserved
    stop_time This column specifies the start of the Databridge Client blackout period expressed as an integer value representing 24-hour time (hhmm format).

    At a few key points during execution, the Databridge command-line Client (dbutility) tests this column to determine whether or not it should continue processing. The configuration file parameter controlled_execution enables this feature while the parameter min_check_time specifies the minimum delay time (for example, a typical time value may be 5 minutes) between checks of stop_time. The program checks at the start of a process or clone command and after commits, provided enough time has elapsed since the last check.

    NOTE: Service-based operations ignore the value of this column, as the service takes over this functionality.
    end_stop_time This column specifies the end of the blackout period for the Databridge command-line Client (dbutility). It is expressed as an integer value representing 24-hour time (hhmm format).

    For example, if stop_time is 2000 and end_stop_time is 200, the Databridge Client refrains from running between 8:00 p.m. and 2:00 a.m.

    NOTE: Service-based operations ignore the value of this column, as the service takes over this functionality.
    update_level This column contains the update level of the DMSII database at the time the last define or redefine command was run.
    status_bits This column contains a set of bits that the Databridge Client sets. Some of these bits contain state information that is useful. Modifying this column can disrupt the Client operations. The following list covers the columns in the DATASOURCES control table that can be modified in user scripts.

    Bit and Description
    • 1-256: For internal use only.
    • 512: SRC_NotBackedUp - When this bit is set, an unload file is created to ensure that the data source backup is not overwritten; the bit is then cleared. After the Client resumes audit file processing and a transaction group is successfully processed, this bit is set.
    • 1024: SRC_FileXtract - This bit indicates that the data source is a FileXtract file rather than a DMSII database.
    • 2048: SRC_ITRANS - This bit echoes the value of the DMSII database's INDEPENDENTTRANS flag.
    • 4096: Reserved
    • 8192: SRC_DBEnterprise - When this bit is set, it indicates that the data source is an Enterprise Server data source, versus a DBServer data source.
    • 16, 384: Reserved
    • 32, 768: Reserved
    • 65, 536: Reserved
    • 131, 072: SRC_Upgraded - This bit is set by the dbfixup utility, when there are OCCURS clauses that are not flattened, to indicate that we have just done an upgrade to the 6.6 (or newer) Client software. Upon seeing this bit set the Client automatically executes a refresh command to create the z_tablename stored procedures that are used to do deletes in OCCURS tables. The Client clears this bit if the refresh command is successful. This avoids getting SQL errors when the Client tries to use these procedures, which did not previously exist.
    • 266, 144: SRC_RequiredAware - This bit is set by the 6.6 (or newer) Clients when a define command is executed. It indicates that the Client should honor the REQUIRED property in the DASDL and set the corresponding items not to allow nulls (NOT NULL). The purpose of this bit is preserve backward compatibility by preventing data sources created by older Clients from having all the REQUIRED items changed to not allow nulls.
    tab_name_prefix This column holds an optional one to eight-character prefix which is added to all table names in the data source. This prefix, which you must supply, allows you to distinguish between identically named data sets in multiple data sources, without having the define and redefine commands rename tables to eliminate name conflicts. The configuration file parameter use_column_prefixes extends this prefix to all column names.
    data_source_id This column allows you to provide a numeric identifier to distinguish records that belong to a particular data source from other records in a multi-source environment using a user script, or a relational database query tool. In addition, you must set the external_columns column to 128 or 2048 for all the data sets belonging to this data source.
    last_run_status This column holds the exit code of the last Databridge Client process or clone command that was run. When the exit status is not available (such as when the Databridge Client is running or abended), the entry has a value of 9999.
    stop_afn This column specifies the AFN value when the configuration file parameter stop_after_given_afn is enabled.

    NOTE: Service-based operations ignore the value of this column, as the prefered way of doing this is use a stopper program combined with the stop_task parameter in the Client.
    af_origin This column specifies the origin of the current audit file being processed. The following values are defined for this column:

    • 0: Audit file processed by Databridge Engine
    • 1: Reserved
    • 2: Audit file processed by Enterprise Server using Databridge Engine to access regions. This is referred to as "remote regions" (or "indirect disk").
    • 3: Audit file processed by Enterprise Server using direct disk I/O. This is referred to as "direct disk" and is the most economical way to process audit files in terms of host resource utilization.
    • 4: Cached audit file processed by Enterprise Server.
    server_version This column indicates the version of DBServer last used by the Client.
    engine_version This column indicates the version of Databridge Engine last used by the Client.
    support_version This column indicates the version of the Support Library last used by the Client.
    dbe_version This column indicates the version of Enterprise Server last used by the Databridge Client.
    client_version This column indicates the version of the last dbutility or DBClient that was run for this data source
    cfgsrvr_version This column indicates the version of DBClntCfgServer that was last used by the service to access the data source.
    service_version This column indicates the version of the DBClntControl service that launched a Client run for the data source.
    old_update_level This column holds the previous value of update level when running when a redefine command is run. This value is used to name the reorg scripts that contain the DMSII database’s update level.
    db_timestamp This column contains the timestamp of the DMSII database, which is the time when the database was created. It is used by the Client to verify that the Client is using the same database as it originally was. If this column is set to zeroes, then this test is not performed.

    CAUTION: This column contains a DMSII TIME(6) value, which is binary and 6-bytes long.

    For SQL Server, set db_timestamp=0.
    For Oracle, set db_timestamp ='000000000000'.
    reader_info This column contains the name and version of the reader used to read audit files (or flat files in the case of a FileXtract data source).
    dms_dbase_name This column contains the name of the DMSII database, which is not always the same as the data source name.

    Note

    The data source CTLTAB_VERSION in the DATASOURCES table is a special entry created by the Databridge Client. It indicates the version of the Client control tables. Do not try to process this data source, and do not remove it from the table.

    DATASETS Client Control Table

    The DATASETS table contains information about each DMSII data set as permitted by the Databridge host support library filters. The DATASETS table contains state information for each data set visible to the Client, including the current replication phase of the data set. When the data has been successfully extracted, this table includes the location in the audit trail from which the last group of updates for the data set were read, including the audit file number, the audit block sequence number, the segment number, and the index that identify the physical location of the block in the audit file, and a timestamp.

    The active column of the DATASETS table controls the selection of all tables mapped from a DMSII data set. (The SQL statements in your user scripts use the active column in this table to specify data sets you do not want to clone.) If you use the DATASETS table to disable cloning for a data set, you disable cloning for all tables related to that data set.

    For example, one DMSII data set with a nested OCCURS item can generate multiple tables. If you do not want to clone any of these tables, use the active column in the DATASETS Client control table to turn off cloning. For more information on selectively cloning data sets, Tips for More Efficient Cloning.

    The following table contains descriptions of each column in the DATASETS Client control table. Included is the abbreviated column name that the displaycommand writes to the log file.

    Column name Display Description
    data_source This column contains the name of the data source that identifies the DMSII database from which the data was taken. The name must match one of the following:
    • The entry for SOURCE in the DBServer control file on the host.
    • A base or filtered source as defined for Enterprise Server.
      dataset_name ds This column contains the name of the DMSII data set.
      rectype /type This column, which is zero for all fixed-format data sets, contains the record type of a DMSII variable-format data set as follows:

      Record Type and Description
      • 0: For a variable-format data set, this represents records that have no variable part.
      • 1–254: Represents the variable-format record type as defined in the DASDL.
      set_name set This column contains the name of the DMSII set that Databridge Engine uses as an index source for tables mapped from the data set. The names "aa_set", "user_set", and "pk_set" are special set names that the Databridge Client uses when a DMSII set is not available. The name "aa_set" indicates that AA Values (or RSNs) will be used as the source for the index. The name "user_set" indicates that the set is user-defined. The name "pk_set" indicates that the set is defined in GenFormat using the PRIMARY KEY statement.
      active A During a define or redefine command, this column determines whether or not a data set is mapped. During a process command, the value of this column determines if the data set is to be selected for cloning or updating. The default, 1, indicates that the data set will be mapped (cloned or updated). A value of 0 indicates that the data set will not be mapped (cloned or updated). The define and redefine commands change the value in the active column to 0 for the global data set, the restart data set, and remaps (unless the base structure is filtered out) in the DMSII database.

      NOTE: When you change the DATASETS active column value to 0 to disable cloning, all tables related to the data set are disabled. For example, if a DMSII data set is represented by three relational database tables, none of the three relational database tables will be cloned.
      strnum ST# This column contains the DMSII data set's structure number.
      audit_filenum AFN This column contains the current DMSII audit file number. DMSII audit files are created and stored on the host; they contain a record of all updates to the DMSII database and are named as follows:

      databasename/AUDITnnnn

      where databasename is the name of the DMSII database, AUDIT is a literal, and nnnn is the AFN (audit file number) whose value is a number between 1 and 9999. Before you run a process command to clone a DMSII database, the audit file number (and all the other audit file location information) is zero; subsequent process commands fill these records with the ending audit file location.
      audit_block ABSN This column contains the audit block serial number in the audit file. Because DMSII uses 32-bit audit block serial numbers, the data type for this column is binary (raw in Oracle). All displays in the log file show this value as a 10-digit unsigned number. If you access this column via a relational database query tool, the hexadecimal value appears instead, as the column's data type is binary(6) in SQL Server and raw(6) in Oracle).
      audit_seg SEG This column contains the segment number within the audit file.
      audit_inx INX This column contains the index within the audit file segment.
      audit_ts Time Stamp This column contains the audit file timestamp represented as a relational database date/time data type, which 6-bytes in length.
      ds_mode M There are a few instances where you may need to change the mode. The ds_mode value provides the following information about the data set:

      Value and Description
      • 0: The data set is ready to be cloned; all tables and stored procedures are dropped and recreated.
      • 1: The data set is in the fixup phase; data extraction is complete and the table is being updated with changes that occurred during the extraction. The integrity of the data in the tables mapped from the data set is not guaranteed to be correct until the fixup phase is complete.
      • 2: The data set is ready to be updated. This implies that it has already been cloned and the fixup has been completed. This is the most common mode.
      • 10: An error occurred during the dat extraction.
      • 11: An error occurred during index creation or the tables mapped from this data set do not have an index defined.
      • 12: The data set is using AA Values as keys, and the AA Values are no longer valid because the data set has been reorganized.
      • 31: The data set must be reorganized and the redefine or the Administrative Console's Customize command has created scripts to make the relational database table match the DMSII data set. You must run the reorganize command in order to run the reorganization scripts created by the redefine command.
      • 33: The reorganize command failed for this data set. In this case, you must manually update the table by trying to fix the failed script. Then, set ds_mode to 31 and repeat the reorganize command. If that fails, you must re-clone it.
      host_fmtlevel This column contains the format level as seen by the host. The value is the update level received from Databridge Engine in the last STATEINFO record.
      client_fmtlevel FMT This column contains the format level as seen by the Databridge Client. The value is determined by the define and redefine commands. Typically, the host and Client format levels are the same until a DMSII reorganization is detected.
      recsz_bytes RSZ This column contains the size of the record in bytes.
      parent_strnum P# This column contains the parent structure number. This column is used for embedded data set information.
      num_children #C This column contains the number of child structures for the parent structure. This column is used for embedded data set information.
      base_strnum B# This column contains the base structure number. If the value in this column is not equal to the value in the strnum column, this data set is a remap of the data set whose structure number is base_strnum.
      subtype ST This column contains the structure subtype of the DMSII data set:

      Value and Description
      • 0: Standard data set
      • 1: Random data set
      • 2: Ordered data set
      • 3: Unordered data set
      • 4: Global data set
      • 5: Direct data set
      • 6: Compact data set
      • 16: Restart data set
      • 17: Virtual data set
      in_sync The in_sync column tracks data sets whose stateinfo is synchronized with the stateinfo stored in the Global_DataSet row for the data source in the DATASETS Client control table.

      Global_DataSet is a dummy data set that holds the common stateinfo for data sets whose ds_mode is 2. When the Databridge Client is finished updating, the stateinfo in the Global_DataSet is copied to all data sets that need to be updated with the stateinfo. Values in this column indicate the following:

      Value and Description
      • 0: The data set stateinfo is current
      • 1: The data set stateinfo must be corrected at the end of update processing to reflect the stateinfo as it is stored in the Global_DataSet
      item_count ICNT The value in this column represents the number of items in the DMSII data set and is used by Databridge Engine to detect filler substitutions and changes in DBGenFormat.
      audit_time6 The value in the audit_time6 column is the DMSII timestamp stored as 6 binary characters. The Client uses this value when it sends state information (stateinfo) to Databridge Engine at the beginning of a process command. The Client does not use the value in the audit_ts column, as it is not accurate enough to use in communications with the Databridge Engine. Instead, the original DMSII timestamp is used. It is much more accurate and has a much smaller granularity than relational database date/time values.

      CAUTION: When you enter values for the stateinfo, you must set the audit_time6 column to 0 because the Databridge Engine uses this value to detect DMSII rollbacks. If the value of the timestamp is 0, Databridge Engine bypasses this test.

      For SQL Server, set audit_time6=0.
      For Oracle, set audit_time6='000000000000'.
      host_info The information in the host_info column is provided by the Databridge Engine during data extraction. It enables the Databridge Client to recover fixups if the command is aborted. This information is stored as 6 binary characters.
      ds_options OP The following bits (which can be set through customization user scripts or by using the Administrative Console's Customize command) control how data sets are mapped:

      Bit and Description
      • 1: DSOPT_Use_bi_ai - This bit is set by the define command for data sets that have OCCURS clauses that were not flattened when the configuration file parameter optimize_updates was set to True. This bit causes the program to request that the Databridge Engine send all updates the data set, involving a key change, as BI/AI pairs. You can set this bit to 0 via user scripts if you want to disable optimization of updates for this data set.
      • 2: DSOPT_No_Loader - This bit causes the Databridge Client not to use the bulk loader during the data extraction phase of this data set. It is effectively a localized form of the /s option (which applies to all data sets).
      • 4: DSOPT_No_StoredProcs – This bit causes the Databridge Client not to use stored procedures when doing updates. Updates still use host variables, but instead of generating a stored procedure call, the Client generates the actual SQL statement to do the update.
      • 8: DSOPT_Save_Updates - This bit causes the Databridge Client to generate history tables for all tables that are mapped from the data set.
        To determine whether the history tables are populated with clone data only or clone and update data, see history_tables.
      • 16: DSOPT_Include_AA - This bit is deprecated and should not be used to force the Client to use AA Values (RSNs) as the source for the index. Use the bit DSOPT_Use_AA_Only instead.
      • 32: DSOPT_Ignore_Dups - When set, this bit has exactly the same effect as the configuration parameter suppress_dup_warnings, except that it only applies to the individual data sets for which it is set.
      • 64: DSOPT_Select_Only - This bit inhibits the creation of tables and stored procedures for the data set. It is used for data sets that provide input to virtual data sets and are not otherwise mapped to any tables.
      • 128: DSOPT_Keep_Null_Alpha_Keys - This bit indicates that the program should treat NULL alpha keys as blanks instead of discarding such records.
      • 256: DSOPT_Supp_New_Columns - This bit, which is initially set to reflect the value of the suppress_new_columns parameter for the corresponding data set, can be modified via user scripts or the Administrative Console's Customize command. The redefine command uses this bit when determining how to handle new columns.
      • 512: DSOPT_MultiInput - When the automate_virtuals and miser_database parameters are enabled, this bit indicates that data for the virtual data set comes from more than one real data set.
        When this bit is set, the Client tests the res_flag column (identified by a dms_subtype value of 255) before executing the stored procedure i_tablename. If the flag is set, the insert is done normally; otherwise, the stored procedure r_tablename is called to update the res_flag. If the update fails, an insert is performed instead.
      • 1024: DSOPT_MultiSource - This bit indicates that the tables generated from the data set get their input from more than one data source and ensures that the Databridge Client doesn't drop the tables. The Databridge Client uses the cleanup scripts, which it generates for such tables, to remove the records that belong to the data source. The most common use of this option is to add DMSII data to a set of tables whose data comes from some other source.
      • 2048: DSOPT_MergedTables - This bit allows the Databridge Client to replicate multiple DMSII databases, which have the same layout, into a single relational database. Each data source is given a unique ID and a unique prefix (in the data_source_id column of the corresponding DATASOURCES table entries) that the program uses when it constructs the merged tables (though the stored procedures for each data source are separate). This prefix serves as an alias to the actual table.
        These tables cannot be dropped during a DMSII reorganization. They must be altered in such a way that they can continue to be reused.

        NOTE: This bit must be used with the DSOPT_MultiSource bit.
      • 4096: DSOPT_CheckKeyChanges - This bit represents the value of the KEYCHANGEOK attribute of the DMSII SET used as an index for the tables. It is a copy of the DSFLG_KeyChg_Allowed bit in the misc_flags column of the DATASETS table. This bit is used to determine whether the Client needs to register the keys that are being used as the index with the Databridge Engine. This causes the Databridge Engine to compare the values in the before and after images to determine if any of the keys have changed. If they haven't changed, the Databridge Engine sends the update to the Client as a MODIFY record. If they have changed, it sends the update to the Client as a BI/AI pair, which enables the Client to delete the old record and insert the new one when a key change occurs.
      • 8192: DSOPT_HistoryOnly - Causes the define and redefine commands to generate only history tables for the data set in question. This makes it possible to generate history tables for data sets that cannot be tracked because they lack a unique index. This bit implements the parameter history_tables=2 for individual data sets. See history_tables.

        NOTE: If you set this bit, you must also set bit 8 (DSOPT_Save_Updates).
      • 16,384: DSOPT_Use_AA_Only - This bit causes the define and redefine commands to use AA Values or RSNs as the index, even if the data set has a SET that qualifies for use as an index. This bit overrides the DSOPT_Include_AA bit, which has been deprecated.
      • 32,768: DSOPT_Clrdup_Recs - This bit determines whether the Client runs the script script.clrduprecs.dataset_name when the creation of an index fails at the end of the data extraction phase. You can customize this bit.
      • 65,536: DSOPT_Split_Vrfmt_ds - This bit causes the define and redefine commands to split variable format data set records (of types other than 0) into two parts. The fixed part is placed in the base table, which normally holds only type 0 records. The variable part is placed in the secondary table name <ds_name>_type<nn> after the keys, which must also be included.
      • 131,072: DSOPT_ExtCols_Set – This bit indicates that the external_columns column has been customized by the Administrative Console's Customize command or by a user script. You must set this bit whenever you change the value of external_columns for a data set. Otherwise, the Client Configurator won't retain your change.
      • 262,144: DSOPT_SetNameChange – This bit indicates that the set_name was changed to “user_set”. The Client sets this bit when the user defines a composite key using the Client Configurator. Its only purpose is to ensure that this change will be remembered.
      • 524,288: DSOPT_Optimize_4_CDC – The initial value of this bit comes from the parameter minimize_col_updates. It causes the Client to create update statements that only assign values to columns whose values are changed. In place of stored procedures, pure SQL is used without the use of host variables. This significantly slows the Client’s update speed, but the amount of replicated data sent to the remote database (SQL Server and Oracle only) is significantly less, which speeds up the overall process.
      • 1,048,576: DSOPT_Item_Pref_Set – The item_name_prefix column was assigned a value externally. When this column is assigned a value using the Client Configurator, this bit is used to indicate that the prefix should be preserved when redefining the data set or running the Administrative Console's Customize command
      • 2,097,152: DSOPT_Clone_First – This bit is now deprecated. The extract_priority column provides the ability to control the order in which data sets are extracted.
      • 4,194,304: DSOPT_Internal_Clone - This bit allows the user to customize the use of the configuration parameter use_internal_clone on a data set by data set basis. The initial value of the bit is derived from the configuration parameter use_internal_clone.
      • 8,388,608: DSOPT_FilteredTable - This bit is set by the makefilter utility to indicate that the data set has secondary tables which have filters.

        IMPORTANT: Do not change this bit, as it will cause the Client not to operate correctly.
      changes These bits are used by the redefine and the Administrative Console's Customize command to indicate the changes that the command detected.

      Bit and Description
      • 0: Description
      • 1: CHG_new - New entry
      • 2: CHG_modified - Modified entry
      • 4: CHG_del_before - One or more entries before this one were removed.
      • 8: CHG_del_after - One or more entries after this one were removed.
      • 16: CHG_format_level - The data set’s format level changed (that is, a DMSII structural reorganization that affects this data set has occurred).
      • 32: CHG_item_count - The data set’s item count has changed (that is, a filler substitution reorganization has occurred).
      • 64: CHG_user_change - There were user changes to the DMS_ITEMS or the DATAITEMS tables (that is, the layout has changed) as a result of actions by the user rather than a DMSII reorganization.
      • 128: CHG_links_change - DMSII links changed for the data set.
      • 256: CHG_AA_values_changed - This bit indicates that the data sets AA Values are no longer valid. The bit is set by the redefine or the Administrative Console's Customize command but is otherwise not used by the Client.
      • 1024: CHG_deleted - The item was deleted.
      status_bits SB The following bits are used by this column:

      Bit and Description
      • 1: Description
      • 1: DS_Needs_Mapping - This bit indicates that the data set has not been mapped. All data sets that have their corresponding active column set to 0 in the data set selection script script.user_datasets.datasource also have this bit set. If you decide to clone such a data set, you must set the active column to 1 and run a redefine or the Administrative Console's Customize command to perform the mapping.
      • 2: DS_Needs_Generating - This bit indicates to the generate command that the scripts for the data set need to be generated. Note that the generate command only generates scripts for data sets that have this bit set. The define, redefine and the Administrative Console's Customize command automatically set this bit.
      • 4: DS_Needs_Remapping - This bit forces the redefine or the Administrative Console's Customize command to refresh the mapping. After you make changes to the data table user_define customization scripts, you may want to set this bit before you execute a redefine command.
      • 8: DS_Needs_Redefining - This bit is automatically set by the process and clone commands when Databridge Engine detects a structural reorganization or a filler substitution for the data set. You can set this bit to force the redefine command to refresh the DMSII layout.
      • 16: reserved
      • 32: reserved
      • 64: This bit indicates that the AA Values are invalid. Do not modify this value.
      • 128: This bit indicates that the index creation failed. Do not modify this value.
      • 256: This bit indicates that the data set is in fixup mode. Do not modify this value.
      misc_flags MISC This column contains an integer that holds a series of flags set by Databridge to reflect some characteristics of the individual data sets.

      NOTE: Do not change these bits.

      Bit and Description
      • 1-8: Description
      • 16: DSFLG_Links - This flag, set by the Databridge Engine in response to a DB_DataSets remote procedure call (RPC), indicates that the data set has DMSII links to other data sets.
      • 32: DSFLG_Altered - This flag, set by the Databridge Engine in response to a DB_DataSets RPC, indicates that the data set was altered by the support library.
      • 64: DSFLG_Static_AA - This flag, set by the Databridge Engine in response to a DB_DataSets RPC, indicates that the Databridge Engine is using RSNs (record serial numbers) in place of AA Values. RSNs are only available in a DMSII XE system where each record in a data set is assigned a unique serial number. Using the RSN in place of AA Values eliminates the need to re-clone tables after a DMSII garbage collection reorganization.
      • 128: DSFLG_Valid_AA - This flag, set by the Databridge Engine in response to a DB_DataSets RPC, indicates that the data set has valid AA Values. Not all data sets have valid AA Values. For details, see Composite Keys.

        NOTE: This bit does not apply to RSNs, which are always valid; it applies to the AA Values.
      • 256: DSFLG_Has_Occurs - This flag indicates that the data set contains items with unflattened OCCURS clauses. The program uses this bit in conjunction with the optimize_updates parameter to determine whether the DSOPT_Use_bi_ai bit in the ds_options column should be set. The DSOPT_Use_bi_ai bit can be reset by the user to prevent the use of before/after images for data sets where this action offers no significant performance improvements (for example, an OCCURS 2 TIMES clause is probably not worth optimizing).
      • 512: DSFLG_Uses_AA_values - This flag indicates that the data set uses AA Values as keys. The program uses this flag to avoid having to look at the table's columns to determine whether AA Values are used.

        NOTE: This bit is not set when the Databridge Client uses RSNs instead of AA Values.
      • 1024: DSFLG_Has_Links - This flag indicates that the data set has active DMSII links. This bit can be zero if all the links have their active columns set to 0 in DMS_ITEMS.
      • 2048: DSFLG_Is_LinkedTo - This flag indicates that one or more data sets have active DMSII links that use AA Values as foreign keys to point to this data set. The program uses this information to force the AA Values to be used as the keys for the tables derived from this data set.
      • 4096: DSFLG_Occ_Depends - This flag indicates that the data set contains items with unflattened OCCURS DEPENDING ON clauses. The program uses this bit to request that the Databridge Engine send updates to this data set as before/after images, regardless of the value of DSOPT_Use_bi_ai bit in ds_options for this data set.
      • 8192: DSFLG_Uses_Parent_AA - This flag indicates that the data set uses Parent_AA Values as foreign keys. The program uses this to avoid having to look at the table columns to determine if the Parent_AA Values are used.
      • 16,384: DSFLG_Data_Extracted - This flag indicates that the data set was successfully cloned. The program uses this flag to determine if a table is being re-cloned.

        NOTE: This information is vital when preserving deleted records.
      • 32,768: DSFLG_Key_Chg_Allowed - This flag represents the value of the KEYCHANGEOK attribute of the DMSII SET used as an index for the tables. This value is copied to the DSOPT_CheckKeyChanges bit (in the ds_options column of this table). You can modify the DSOPT_CheckKeyChanges bit via user scripts or the Administrative Console's Customize command.
      • 65,536: DSFLG_Data_Dirty - This flag is only meaningful for virtual data sets that get data from more than one DMSII data set. It indicates that phase two of the data extraction process is under way. This flag indicates that the appropriate cleanup script must be invoked when the table is re-cloned (such tables can be partially re-cloned).

        NOTE: This information is vital to being able to partially re-clone such tables.
      • 131,072: DSFLG_MiserDateKey - This flag indicates the index used for the data set contains a Miser date that allow nulls.
      • 262,144: DSFLG_VLinks - This flag indicates that the virtual data set has links
      • 524,288: DSFLG_HasVisibleRSN - This flag indicates that the data set contains a data item of type REAL that holds the value of the RSN.
      • 1,048,576: DSFLG_VarFmt_DataSet - This flag indicates that this data set is a variable format data set.
      • 2,097,152: DSFLG_Valid_Parent_AA - This flag indicates that the parent structure of an embedded data set has a valid AA Value.
      • 4,194,304: DSFLG_Phase1_Done - This flag is used in the data extraction row verification for MISER database tables that get cloned in two phases. It indicates that phase 1 of the data extraction has been completed and instructs the Client to get the initial rows count so that the row verification works correctly in the case where the Client get aborted before phase of the data extraction for such tables completes.
      max_records MAXRECS This column contains an integer that holds the maximum row count of the data set as estimated by the Databridge Engine. This is the exact number that appears in DBLister reports. The Databridge Engine computes this estimate by dividing the file size by the record size. This value is very inaccurate in the case of variable-format data sets because it is impossible to determine how many records of a given type exist in the data set without doing a full scan of the data set.
      virtual_ds_num VDS This column contains an integer value that holds the structure number of the virtual data set to which the DMSII data set is linked. This column is used by the parent data set to point to the associated virtual data set. When more than one virtual data set is derived from a real data set, these data sets are chained together using this column.
      real_ds_num RDS/type

      NOTE: This display name is combined with the real_ds_rectype value.      		 This column contains an integer that holds the structure number of the primary real data set from which the virtual data set is derived. When more than one virtual data set is derived from a real data set, these data sets all point back to the real data set through their real_ds_num column. These real data sets are chained together, starting with the primary data set, by using the otherwise unused real_ds_num columns of the actual data sets.
      real_ds_rectype The integer in this column represents the record type of the variable-format data set. This information serves to further identify a variable-format data set when it is cloned as a virtual. In addition, the variable-format data set is linked to the virtual data set through the virtual_ds_num and real_ds_num columns.
      external_columns EXTC This column contains an integer value that determines which predefined non-DMSII columns are automatically added to this data set. For a description of these bits, see Numeric Date and Time in Non-Contiguous Columns.
      ds_user_bmask This column, which shadows the ds_options column, contains a bit mask that represents the columns in ds_options that were customized. This column is used by the redefine or the Administrative Console's Customize command to restore the portion of ds_options that has been customized while leaving the remaining bits intact.
      links_sz_bytes This column contains the size of the link data, in bytes. Link data is no longer stored in the actual record, instead the record is extended by the size of the link data where the link data is placed during data extraction. These areas are not necessarily contiguous in the DMSII record; the DMSII offsets have been adjusted to make them to look contiguous in the Client.
      links_offset This column is used by the Client to determine where the link area for the record starts.
      vp_link_offset Variable format data sets have links in both the fixed part and the variable part, causing the Client to receive two LINK_AI records. This offset value indicates where the second part of the links area starts. By comparing the offset received from the Engine, the Client can tell where the link data should be stored.
      item_name_prefix This column is used by the Client to automatically strip fixed size prefixes from data item names. One frequently finds DMSII databases where the data set names (or a shortened form of these names) is used as a prefix for every item name. The Client has the ability to get rid of these prefixes without requiring any complex actions other than putting the prefix to be stripped in this column, without the trailing dash.
      rows_extracted This column is used by the Client to save the number of DMSII records that were received during the data extraction phase.
      client_discards This column, which is currently only used by the Flat File Client, is used to record the number of records discarded during data extraction phase.
      extract_priority This column is used to affect the order in which data sets are extracted. The data extraction is now ordered by extract_priority (highest value first) and strnum (lowest value first). By setting this column to a positive number you can change the order in which data sets are extracted.
      dflt_options This column is used to keep track of the initial state of the ds_options bits before any customizations are performed. It allows the Client to clear the ds_user_bmask bits that were set by customizations that were later undone.

      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 corresponding 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. Included is the abbreviated column name that the display command or the Administrative Console's Log Control Tables command that can be found in the data source's Advanced menu.

      Column Display Description
      data_source This column contains the name of the SOURCE name that identifies the DMSII database from which this data was taken. The data source name is defined when you run a define command. It must match the data source name in the DBServer control file on the host.
      dataset_name ds This column contains the name of the DMSII data set from which this table was mapped.
      table_name table name This column contains the name of the table as it appears in the relational database. DMSII data sets correspond to relational tables.
      index_name index 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.
      rectype /type 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.

      Record Type and Description
      • 0: For a variable-format data set, this represents records that have no variable part.
      • 1-254: Represents the variable-format record type as defined in the DASDL.
      occurs_level occ 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.

      NOTE: The Client does not support occurs levels that are greater than 2.
      table_number T# This number is used by the SQLLoader 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 and the Administrative Console's Customize* command assign numbers to new tables starting with the highest table number plus 1. Existing tables get their old table numbers restored.
      active A 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.
      create_suffix suf 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.
      index_suffix 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.
      original_name original_name The Databridge Client saves the original name of the renamed tables in this column so they can be identified during redefine and the Administrative Console's Customize commands.
      prim_table P This column indicates whether or not this is a primary table.
      dt_options OP The dt_options column uses the following bits:

      Bit and Description
      • 1: DTOPT_Table_Renamed - The table was renamed by the user. This bit is used by the Client configurator to preserve the name change.
      • 2: DTOPT_Index_Renamed - The table’s index was renamed by the user. This bit is used by the Client configurator to preserve the name change.
      • 4: DTOPT_User_Table - This table was created by the user. Not supported in Databridge Client 7.0.
      • 8: 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 100) is not zero.
      • 16: DTOPT_Occ_Depends - This option, automatically set by the Client during a define or a redefine command or the Administrative Console's Customize 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.
      • 32: DTOPT_All_Keys - Indicates that all columns in the table are keys. Do not change this value.
      • 64: DTOPT_No_Unique_Key - Indicates that the table does not have a unique index and therefore it cannot be tracked unless a composite key is created. Do not change this value.
      • 128: DTOPT_Preserve_Deletes - Do not change this value.
      • 256: DTOPT_HistoryTable - This option, which is set by the define and redefine commands or the Administrative Console's Customize command, indicates to the Client that this table is a history table and that all records should be treated as inserts into 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.
      • 512: 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 used in Miser databases.
      • 1024: DTOPT_Clustered_Index - This option, which only applies to the SQL Server Client, tells the Databridge Client to create a clustered index for this table. You can globally set this option via the use_clustered_index parameter. See use_clustered_index.
      • 2048: 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 set this option globally via the use_primary_key parameter.
      • 4096: DTOPT_Delete_Seqno - This option is automatically set by the define or redefine command or the Administrative Console's Customize command when the delete_seqno mask is set in the default_user_columns parameter value.
      • 8192: DTOPT_Table_Split - This option is automatically set by the define or redefine command or the Administrative Console's Customize command when the table is part of a split table. Do not modify this value.
      • 16,384: DTOPT_ConcatItems - This bit is automatically set by the define or redefine command orthe Administrative Console's Customize command; it indicates that the table contains concatenated items. Do not modify this bit.
      • 32,768: DTOPT_Clob_in_Table - This bit, which is only used by the Oracle Client, indicates that the table contains an item whose data type is CLOB. Do not modify this bit.
      • 65,536: DTOPT_OrigNameFixed - Internal use only - do not modify. This bit is used to convey whether the original table name was ever changed.
      • 131,072: DTOPT_ContainsLinks - Do not change this value.
      • 266,144: DTOPT_LinksOnly - Do not change this value.
      • 524,288: DTOPT_PreserveAllDel - Do not change this value.
      • 1,048,576: DTOPT_UseBrackets - This bit, which only applies to the SQL Server Client, is set by the define and redefine commands to indicate that table in question has a name that is a SQL Server reserved word. Such names must be enclosed in square bracket in all SQL statement to avoid getting SQL errors.

        IMPORTANT: Do not change this bit, as it will cause the Client not to operate correctly.
      • 2,097,152: DTOPT_HasOccurs - This bit is set by the define and redefine commands and the Administrative Console's Customize command to indicate that the table is an OCCURS table. The dbfixup utility sets it for all such tables during an upgrade, as it was not used in older releases. This bit is critical to proper Client operations when you have items with OCCURS clauses that are not flattened. This was necessary to distinguish such tables from tables that result from flattening an OCCURS clause into a secondary tables (both these tables have a non-zero value in the occurs_level column).

        IMPORTANT: Do not change this bit, as it will cause the Client not to operate correctly.

        NOTE: If any of the last 5 bits gets accidentally changed, run a redefine command with the -R option to correct this situation. If using the Administrative Console's Customize command Data Source > Advanced > Redefine (with options) then click on the check box for the -R option.
      • 4,194,304: DTOPT_LoadPending - This bit is set by the Client when doing multi-threaded extracts upon receiving a State Info record from the Databridge Engine indicating that the data extraction is complete. This bit stays set until the EOF buffer that is queued for the corresponding update thread is processed.
      changes These bits are used by the redefine command and the Administrative Console's Customize command.

      1: CHG_new - New entry
    • 2: CHG_modified - Modified entry
    • 4: CHG_del_before - One or more entries before this one were removed.
    • 8: CHG_del_after - One or more entries after this one were removed.
    • 32: CHG_index_changed - This bit indicates that the table’s index changed. The reorganize command uses this bit as an indication that it must drop the index for the table and recreate it.
    • 64: CHG_IndexType_changed - The index type changed primary key versus unique index or in the case of SQL Server unique index versus clustered index.
    • 128: 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 or the Administrative Console's Customize command. The reorganize command deletes this file once the new index is successfully created
    • 256: CHG_new_hist_tab - History tables were added for the data set. The redefine command ot the Administrative Console's Customize command sets this bit when they find a new history table. The reorganize command then can create these tables and we can continue processing without re-cloning the data set.
      		•
      update_count The update_count column represents the smoothed average number of updates for the table over the specified period of time.
      update_interval 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.
      dt_user_bmask 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 and the Administrative Console's Customize commands to restore the portion of dt_options that has been customized while leaving the remaining bits intact.
      dflt_options This column is used to keep track of the initial state of the dt_options bits before any customizations are performed. It allows the Client to clear the dt_user_bmask bit that were set by customizations that were later undone.

      DMS_ITEMS Client Control Table

      The DMS_ITEMS table contains entries for each DMSII item that is visible to the Client after column filtering in the Support Library on the host. The DMS_ITEMS table also contains the name of the DMSII data set of which the item is a member, as well as other DMSII layout information.

      The following table contains descriptions of each column in the DMS_ITEMS Client control table. Included is the abbreviated column name that the displaycommand writes to the log file.

      Column Display Description
      data_source This column contains the name of the data source that identifies the DMSII database from which this data was taken.
      dataset_name ds This column contains the name of the data set in the DMSII database to which this DMSII item belongs.
      rectype /type 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. For more information on variable-format data sets, see Variable-Format Data Sets.

      Record Type and Description
      • 0: For a variable-format data set, this represents records that have no variable part.
      • 1-254: Represents the variable-format record type as defined in the DASDL.
      dms_item_name item_name This column contains the name of the data item for the listed data set. This column is limited to 128 characters.
      active A This column specifies whether or not the item will be mapped. A value of 1 (default) indicates that the item will be mapped (if this is possible) to an entry in the DATAITEMS Client control table. A value of 0 indicates that the item will not be mapped. The define and redefine commands and the Administrative Console's Customize command change the value in the active column to 0 for the global database unless you set the active column to True in the corresponding entry in the DATASETS control table.
      item_key K This column contains a numeric value which specifies the order of the item in the DMSII set (1, 2, 3, and so on). If the item is not a key, this value is 0.

      NOTE: You can edit this column to create a composite key or change the order of the keys in the index. See Creating Indexes for Tables.
      dms_item_number # This column contains the item number, which indicates the relative position of the item in the original DMSII record.
      dms_parent_item P# This column contains the dms_item_number of the parent item for an item that is a member of a GROUP item. For example, if dms_item_number 12 is a DMSII GROUP containing items 13, 14, 15, and 16, the dms_parent_item of the last four items will be 12.
      dms_item_type T Values 10, 14, 21, 27, 29, and 30 through 37 are DMSII data types.

      This column indicates the type of data item, as follows:

      Type and Description
      • 10: DMSII link
      • 14: Image (alpha data to be stored as binary)
      • 21: variable-format record type
      • 27: Field of Booleans
      • 29: Group
      • 30: Boolean
      • 31: Field
      • 32: Alpha
      • 33: Number (n)
      • 34: Number (n,m)
      • 35: Real (n)
      • 35: Real (n)
      • 36: Real (n,m)
      • 37: Real
      dms_decl_length DL This column contains the user-declared length of the item in the DMSII DASDL. This length changes according to the data item type selected (alpha, boolean, field, number, or real).
      dms_scale S This column contains the numeric scaling factor, which is the number of digits to the right of the decimal point, if any.
      dms_offset O This column contains the item’s offset value which indicates where, within the DMSII record, this item begins. This is the location Databridge uses to extract data from DMSII records. For example, in a 400-byte record with an offset of 200, the first 100 bytes are used by other items. This number is in digit size, which is equal to one-half of a byte (four bits).
      dms_length L This number is the size, in digits, of the data. Digit size is equal to one-half of a byte (four bits).
      dms_signed s This column contains a boolean value specifying whether the item is signed or unsigned as follows: 0 = unsigned and 1 = signed.
      dms_num_occurs #O This column indicates the number of times this data item occurs (is present) within the data set. If the item does not have an OCCURS clause, this value is 0.
      dms_num_dims #D This column contains the number of dimensions for the data item, which is the number of subscripts required to access the item.
      dms_depends_num dep This column contains the dms_item_number value of the item that specifies the number of occurrences in use for an item with an OCCURS DEPENDING ON clause.
      dms_subtype ST For items mapped to relational database date types, this column contains the format of the date as it is stored in the DMSII database. These are not actual DMSII data types; rather, they represent the formats of dates that might be stored as a DMSII GROUP, a NUMBER, or an ALPHA item. For non-DMSII columns this column identifies the type of the non-DMSII column. For split data items, this column determines the offset of the split. This column is also used to identify columns in tables that pose unique characteristics. For example, MISER databases use this column to identify special columns in history virtual data sets which indicate if this is a resident history record.
      di_options OPTIONS The following bits, which can be set through data set mapping customization user scripts or the Administrative Console's Customize command, enable you to control how the item is mapped.

      Bit and Description
      • 1: DIOPT_Flatten_Occurs - This bit specifies that the OCCURS clause of the item should be flattened; it is ignored if the item does not have an OCCURS clause.
      • 2: DIOPT_Clone_as_Date - This bit specifies that the item should be mapped to a relational database short date (smalldatetime on SQL Server and date in Oracle). The format for the encoded date is specified in the dms_subtype column. If you set this bit at the same time as bit 128, bit 128 takes precedence.
      • 4: DIOPT_Split_Item - This bit indicates that the item should be split into smaller chunks if it cannot be accommodated using a relational database data type (for example, ALPHA(4000) in Oracle). The default is to truncate the item.
      • 8: Reserved
      • 16: DIOPT_Clone_as_Tribit - This bit is used in Miser databases to map DMSII number(1) items to a field of three Booleans.
      • 32: DIOPT_Clone_as_Binary - For ALPHA items, this bit indicates that items should be mapped to a relational database binary data type, rather than a character type. Items too large to fit in the corresponding binary type are truncated, unless the DIOPT_Split_Item bit is also set, which then maps the item to multiple binary type columns.

        For REAL items that contain visible RSNs, this bit indicates that the items should be mapped to a relational database binary data type -- BINARY(6) for SQL Server and RAW(6) for Oracle.
      • 64: DIOPT_Xlate_Binary - When this bit is set, EBCDIC data is translated to ASCII before being stored as binary.

        NOTE: This bit only affects the program when the DIOPT_Clone_as_Binary bit (32) is also set.
      • 128: DIOPT_Use_LongDate - This bit, which applies to Microsoft SQL Server only, tells the Client to use a datetime data type instead of smalldatetime for the corresponding column in the relational database.
        If the you are cloning timestamps that include seconds as explained in Decoding DMSII Date/Times, set this bit.
      • 256: DIOPT_Clone_as_Time - Indicates to the Client that the DMSII items should be interpreted as a time and stored on the relational database as an int in the SQL Client or number(10) in Oracle Client in the form hhmnss except for ticks, which are stored in the form ddddhhmnss.
      • 512: DIOPT_Numeric_Data - This bit, which applies to DMSII ALPHA types only, indicates to the Client that the item contains numeric data and should be mapped to a numeric type on the relational database.
      • 1024: DIOPT_AlphaNumData - This bit, which applies to DMSII NUMBER types only, indicates to the Client that the item should be mapped to a character type on the relational database.
      • 2048: DIOPT_VarFormat_Date - This bit specifies that the item should be mapped to a relational database date (smalldatetime, datetime, datatime2 or date on SQL Server and date on Oracle), using a unique encoding scheme. This bit requires that you also set DIOPT_Clone_as_Date (2).
        The format for the encoded date is specified in the dms_subtype column, using the instructions for Unique DMSII Date/Time Formats Represented as Alpha or Number Items.
        If you use the SQL Server Client and are cloning a value for seconds (hexadecimal value 7) from the host, also set bit 128 to get a data type of datatime.
      • 4096: DIOPT_FlatSecondary - This bit specifies whether occurring items in the secondary table are flattened into a single row, or placed in multiple rows for each parent record.
      • 8192: DIOPT_Clone_as_RSN - This bit indicates whether the item should be treated as an RSN. This bit only applies to items of type REAL. When this bit is set, the Client treats the A-Series word (represented as a REAL) in the same way it treats AA Values and RSNs supplied by the Engine. In this case, REAL items are mapped to a column of type CHAR(12) in the relational database. Note that the configuration file parameter use_binary_aa has no effect on such items. Instead, the DIOPT_Clone_as_Binary bit in di_options must be used to cause this RSN to map to a column of type BINARY(6) in the relational database.
      • 16,384: DIOPT_Clone_as_Number - This bit causes columns containing RSNs to be handled as numeric AA Values.
      • 32,768: DIOPT_Clone_as_DateOnly - This bit causes the define and redefine commands or the Administrative Console's Customize command to use the data type of date instead of smalldatetime for dates that have no time values. This bit is ignored if either the bit DIOPT_UseLongDate or the bit DIOPT_UseLongDate2 is set in di_options.
      • 65,536: DIOPT_Use_LongDate2 - This bit causes the define, redefine and the Administrative Console's Customize commands to use the data type datetime2 instead of smalldatetime. If both this bit and DIOPT_UseLongDate are set, this bit takes precedence.
      • 131,072: DIOPT_Use_Time - If DIOPT_Clone_as_Time bit is specified, this bit causes the define, redefine and the Administrative Console's Customize commands to use the data type of time instead of a numeric time type.
      • 262,144: DIOPT_Subtype_Modified – The Client Configurator uses this bit to mark items whose dms_subtype column was modified. This is necessary as the Engine sets the dms_subtype of some items automatically. This bit allows the Client to preserve these values when the data set is redefined or the Administrative Console's Customize command is run.
      • 524,288: DIOPT_ResetActive - This bit is used by the Client Configurator to mark items whose active column in the DATAITEMS table is reset by the Client. This is used for items that need to be included in DATAITEMS but do not have corresponding columns in the relational database table. The second part of a concatenated item is marked with this bit, as it needs to be present to fetch the value to be used in the concatenation but it does not have a corresponding column in the table.
      • 1,048,576: DIOPT_Split_In_Two – This bit indicates that the Client should split the item into two parts using the value in dms_subtype as the offset of the split. A NUMBER(14) can thus be mapped to a NUMBER(2) item and a NUMBER(12) item by specifying a dms_subtype value of 2.
      • 2,097,152: DIOPT_NumericData2 - This bit allows the second part of a split item to be stored as a numeric type.
      • 4,194,304: DIOPT_AlnumData2 - This bit allows the second part of a split item to be stored as an alpha type (i.e. CHAR or VARCHAR).
      • 8,388,608: DIOPT_CloneasNumDate – This bit indicates that the item should be cloned as a numeric date.
      • 16,777,216: DIOPT_MergeNeighbors - This bit indicates that the item should merged with the neighboring item that follows it.
      • 33,554,432: DIOPT_StripPadChars – This bit allows numeric data that uses high value padding to be interpreted as a valid number by stripping off the trailing pads (i.e. digits with all bits set to high values).
      • 67,108,864: DIOPT_CollapseGroup - This bit is used to make the Client treat a GROUP of items, that are all unsigned NUMBER or ALPHA, as a single item. For example the Client would treat a GROUP consisting of 4 NUMBER(2) items as a NUMBER(8) item, which can then be cloned as a date. The Administrative Console's Customize command uses this method to handle dates that are represented as a GROUP of NUMBER items.
      • 134,217,728: DIOPT_Clone_as_GUID – This bit is used to make the SQL Server Client store an ALPHA(36) item containing a GUID as a UNIQUEIDENTIFIER data type.
      • 268,435,456: DIOPT_Value_Required - This bit is set internally to reflect the presence of the REQUIRED option for the item in the DASDL.
      • 536,870,912: DIOPT_VF_Keep_Item - This bit is used in conjunction with the parameter split_varfmt_ds to force a non-key column in the fixed part of the record to be preserved in the variable part tables.
      • 1,073,741,824: DIOPT_Flatten2String - This bit is used to indicate that an unsigned NUMBER or an ALPHA item with an OCCURS clause is to be flattened to a CHAR or VARCHAR column. The dms_subtype column determines the format to be used. If the value is 0, fixed format is used. In this case the Client does not strip leading zeroes from numbers or trailing spaces from alpha data. NULL values are represented by blanks. If the value is non-zero, CSV format is used. In this case the dms_subtype also represents the value of the ASCII character to be used as the delimiter (this is limited to punctuation characters such as a comma or a semicolon). Leading zeroes for numeric data and trailing spaces for alpha data are stripped and NULL values are represented by empty fields (i.e. two consecutive delimiters or a delimiter at the end of the string for the last column).
      dms_concat_num Using this column, the Client supports concatenating two non-contiguous columns of the same data type (that is, treat two columns as one). You can concatenate two ALPHA items, or two unsigned NUMBER items. You can also use the Client to store a numeric item as ALPHA and then use the item in a concatenation. You can also store an ALPHA item that contains numeric data as an unsigned NUMBER and concatenate it with an unsigned number.
      Concatenation is also supported for unsigned numeric columns that represent a date and a time. The date can be any of the supported date formats, while the time must be a NUMBER(6) containing the time as HHMISS. The combined NUMBER can then be interpreted by the Client as date/time. For an example of the layout scripts, see Concatenating Two Items and Cloning the Result as a Date/Time.
      changes These bits are used by the Client Configurator (not by the redefine command).

      • 1: CHG_new - New entry
      • 2: CHG_modified - Modified entry
      • 4: CHG_del_before - One or more entries before this one were removed.
      • 8: CHG_del_after - One or more entries after this one were removed
      • 16: CHG_dms_item_key - This bit indicates that value in the item_key column of the entry has changed.
      • 32: CHG_dms_item_type - This bit indicates that the DMSII data type of the item changed.
      • 64: CHG_dms_decl_length - This bit indicates that the value in the dms_decl_length column of the entry has changed.
      • 128: CHG_dms_scale - This bit indicates that the value in the dms_scale column of the entry has changed.
      • 256: CHG_dms_signed - This bit indicates that the value in the dms_signed column of the entry has changed.
      dms_link_ds_num lnk This column holds the structure number of the data set to which a LINK item points. Thus a nonzero value in this column identifies a DMSII LINK. Links that use AA Values have a dms_item_type value of (10).
      di_user_bmask This column, which shadows the di_options column, contains a bit mask that represents the bits in di_options that were customized. This column is used by the redefine command to restore the portion of di_options that has been customized while leaving the remaining bits intact.
      redef_item_type This column is used by the Client to redefine a DMSII GROUP, consisting of items that have the same data types (e.g. a GROUP of 4 unsigned NUMBER items), as a single item of the given type.
      redef_decl_len This column is used by the Client to specify the resulting length when redefining a DMSII GROUP consisting of items that have the same data types.
      di_options2 The following bits, which can be set through data set mapping customization user scripts, enable you to control how the item is mapped.

      Bit and Description
      • 1: DIOPT_Item_Masked - This bit specifies that the item in question is masked in DMSII.
      • 2: DIOPT_Item_Encrypted - This bit specifies that the item in question is encrypted in DMSII.
      • 4: DIOPT_Split_Table – This bit forces the define and redefine and the Administrative Console's Customize commands to split the table before mapping this item. This gives the user more control in handling split tables when the splitting of the table in the middle of an OCCUR clause is undesirable.
      • 8: DIOPT_End_Split_TABLE - This bit is used in conjunction with the DIOPT_Split_Table bit to make the Client return to the parent table following a forced split. It must follow an item with the DIOPT_Split_Table bit set and there can be only one outstanding split (i.e. you cannot have two table splits followed by two end table splits).
      da_user_bmask2 This column, which shadows the di_options2 column, contains a bit mask that represents the bits in di_options2 that were customized. This column is used by the redefine and the Administrative Console's Customize commands to restore the portion of di_options2 that have been customized while leaving the remaining bits intact.
      dflt_options This column is used to keep track of the initial state of the di_options bits before any customizations are performed. It allows the Client to clear the di_user_bmask bits that were set by customizations that were later undone.
      dflt_options2 This column is used to keep track of the initial state of the di_options2 bits before any customizations are performed. It allows the Client to clear the di_user_bmask2 bits that were set by customizations that were later undone.

      DATAITEMS Client Control Table

      This table duplicates the DMSII information in the DMS_ITEMS table and contains the layout information for the tables in the relational database. This table is not directly linked to the DATASETS table. Instead, it is linked to the DATATABLES Client control table using the table_name column as a foreign key.

      You can use the DATAITEMS Client control table to specify the data items you do not want to clone by setting their corresponding active column to 0. However, we recommend that you accomplish this by setting the active column to 0 in the DMS_ITEMS table. Using the DATAITEMS table can lead to unnecessary table splits. Unused columns cause the column count and record size computations to be too high.

      If data set mapping is already complete, this table can be temporarily used to disable a new column after a DMSII reorganization to avoid recloning. (This is done automatically if the configuration file parameter suppress_new_columns is set to True.)

      If you want to disable cloning for every data item in a data set (every column in a table), disable cloning for the data set instead of disabling cloning for each individual data item. For details, see DATATABLES Client Control Table.

      The following table contains descriptions of each column in the DATAITEMS Client control table. Included is the abbreviated column name that the displaycommand writes to the log file.

      Column Display Description
      data_source This column contains the name of the data source that identifies the DMSII database from which the data was taken.
      table_name table This column contains the name of the table in the relational database to which this item belongs.
      item_number # This column contains an internal number that gives each item within a table a unique number. Numbers are assigned consecutively in increments of 10, starting with 10, making it easier to change the order of items using data table customization user scripts.
      item_name item_name This column contains the name of the item (column) in the relational database table. Typically, this is the same as the lowercase form of the DMSII item name with all dashes changed to underscores. To modify, see Appendix D: Customization Scripts.
      active A The value in this column specifies whether or not this data item will be cloned. The default is 1, which indicates that the data item will be cloned. 0 indicates that the data item will not be cloned. The define, redefine and the Administrative Console's Customize commands change the value in the active column to 0 if the data set is the global dataset, unless the active column is set to true in the corresponding DMS_ITEMS entry.

      NOTE: If the active value for the data set to which this item belongs is 0 (off), this item will not be cloned even if its active value is 1 (on).
      item_key iK This column contains a numeric value specifying the order of the item in the DMSII set (10, 20, 30, and so on). You can modify this column to make it part of a composite key or change the order of the keys in the index. For details, see Creating Indexes for Tables. If the item is not a key, the value is zero (0).
      virtual_key VK Do not change this value.

      This column contains a boolean value specifying if this item is a virtual key; however, it is created only for mapping DMSII items with unflattened OCCURS clauses. When an item is a virtual key, the corresponding value for item_key is a positive number that is one greater than the item_key value of the last key for the data set. The virtual key is not a DMSII key -- its value in the data table is the occurrence number in the occurs clause (starting at 1).
      dms_item_number I# This column contains the item number, which indicates the relative position of the item in the original DMSII record.
      dms_parent_item P# This column contains the dms_item_number of the parent item for an item that is a member of a GROUP item. For example, if dms_item_number 12 is a DMSII GROUP containing items 13, 14, 15, and 16, the dms_parent_item columns of the last four items will be 12. This column contains a copy of the dms_subtype in the DMS_ITEMS table.
      dms_item_type TYP For a description of this column, see "dms_item_type" in DMS_ITEMS Client Control Table.

      In addition to the types defined in DMS_ITEMS, this column contains the following values:

      Type and Description
      • 256: AA Value or RSN, which the Databridge Client generates using the AA Value or RSN of the record in the DMSII database as received from Databridge Engine. You can tell them apart by looking at the item_name column, which is my_aa for AA Values and my_rsn for RSNs.
      • 257: Parent AA, which the Databridge Client generates using the AA Value of the parent record of an embedded data set in the DMSII database as received from Databridge Engine.
      • 258: External type, which indicates that the data comes from some place other than the DMSII database.
      dms_decl_length DL This column contains the user-declared length of the item in the DMSII DASDL. This length changes according to the data item type selected (alpha, boolean, field, number, or real).
      dms_scale SC This column contains the numeric scaling factor, which is the number of digits to the right of the decimal place, if any.
      dms_offset OFF This column contains the item’s offset value which indicates where, within the DMSII record, this item begins. This is the location Databridge uses to extract data from DMSII records. For example, in a 400-byte record with an offset of 200, the first 100 bytes are used by other items. This number is in digit size, which is equal to one-half of a byte (four bits).
      dms_length LEN This number is the digit size of the data. Digit size is equal to one-half of a byte (four bits).
      dms_signed s This column contains a Boolean value specifying whether the item is signed or unsigned as follows: 0 = unsigned and 1 = signed.
      dms_num_occurs OCC This column indicates the number of times this data item occurs (is present) within the data set. If the item does not have an OCCURS clause, this value is 0.
      sql_type TY This column contains the relational database data type that corresponds to the DMSII data types. See DMSII and Relational Database Data Types.
      sql_length LEN If the data type for the column has a length specification in the relational database, this column specifies the length to be used. For example, in the case of char(5) the sql_length is 5.
      Conversely, if the data type for the column does not have a length specification in the relational database (for example, int in SQL Server or date in Oracle) this column has a value of 0.
      occurs_level OLV This column contains the nesting level of OCCURS in the DMSII database. For example, an OCCURS table created from an in item with an OCCURS clause contained in in a GROUP with an OCCURS clause has an occurs_level of 2. The original OCCURS table has an occurs_level of 1.
      NOTE: The Client does not support nested OCCURS that are more than 2 levels deep.
      dms_subtype STY For items mapped to relational database date types, this column contains the format of the date in the DMSII database. These are not actual DMSII date types; rather, they represent the formats of dates that might be stored as a DMSII GROUP, a NUMBER, or an ALPHA item. For non-DMSII columns this column identifies the type of the non-DMSII column. For split items it represents the offset of the split.
      sql_scale SC This column contains a copy of the dms_scale that you can edit. This value is used in the relational database to specify the scale for columns of whose data type is DECIMAL(p,s) on SQL Server or NUMBER(p,s) on Oracle.
      dms_depends_num dep This column contains the dms_item_number of the item that specifies the number of occurrences in use for an item with an OCCURS DEPENDING ON clause.
      da_options OP The following bits, which you can set through data table customization user scripts or the Administrative Console's Customize command, allow you to specify additional properties of the data items:

      Bit and Description
      • 1: DAOPT_Nulls_Allowed - This bit is set by the define, redefine and the Administrative Console's Customize commands based on the value of the configuration parameter allow_nulls. You can later change this value via user scripts or customization. A value of 1 indicates that the item will be created with the attribute of NULL (except in Oracle where this is the default attribute of a column). A value of 0 indicates that the item will be created with the attribute of NOT NULL (except in SQL Server where this is the default attribute of a column).
      • 2: DAOPT_Column_Renamed - The column was renamed by the user. This column is used by the Administrative Console's Customize command to restore changes.
      • 4: DAOPT_Type_Changed - The SQL type of the column was changed by the user. This column is used by the Administrative Console's Customize command to restore changes.
      • 8: DAOPT_Length_Changed - The SQL length of the column was changed by user. This column is used by the Administrative Console's Customize command to restore changes.
      • 16: DAOPT_Scale_Changed - The SQL scale changed by user. This column is used by the Administrative Console's Customize command to restore changes.
      • 32: Reserved
      • 64: Reserved
      • 128: DAOPT_Item_Renumbered - The item number (that is, the location of the column) was changed by the user. This column is used by the Administrative Console's Customize command to restore changes.
      • 256: Reserved
      • 512: DAOPT_Store_as_Char - This bit indicates that the item, which is numeric, should be stored in the relational database as a character data type.
      • 1024: DAOPT_Xlate_Binary - This bit determines whether or not character data gets translated from EBCDIC to ASCII before being stored as binary. This bit is copied from the DIOPT_Xlate_Binary bit in the di_options column of the DMS_ITEMS table as the process and clone commands do not load the DMS_ITEMS table.
      • 2048: DAOPT_Store_as_Number - Indicates that the Client is storing the corresponding ALPHA data using the appropriate numeric data type.
      • 4096: DAOPT_VarFormat_Date - Indicates that the dms_subtype column contains a mask describing the date format.
      • 8192: DAOPT_FixAlphaChar - This bit applies to data items whose data type is ALPHA, and it indicates that the Client will scan the data for control characters and replace each control character with a space.
        You can set this bit via a user define script or by using the Administrative Console's Customize command, or you can set it globally via the convert_ctrl_char parameter. See convert_ctrl_char.

        CAUTION: Do not set the convert_ctrl_char parameter to True unless you are absolutely certain that eliminating control characters will have no adverse effect on the data. For example, eliminating control characters can cause some fields to be misinterpreted.
      • 16,384: DAOPT_ActiveReset – Internal use only. This bit indicates that the active column of items was set to zero by the Client. This happens for concatenated items, which must be present to access the data and are otherwise not processed.
      • 32,768: DAOPT_Clone_as_RSN - This bit is set internally to indicate that the item is being cloned as an RSN, which requires special processing. This bit shadows the corresponding bit in DMS_ITEMS.
      • 65,536: DAOPT_Clone_as_GUID - This bit is set internally by the Client to indicate that an ALPHA item is being cloned as a UNIQUEIDENTIER (SQL Server data type) that is designed to hold GUIDS as a binary quantity. This bit shadows the corresponding bit in DMS_ITEMS.
      • 262,144: DAOPT_Value_Required - This bit is set internally to indicate that item has the REQUIRED option in the DASDL. It shadows the corresponding bit in DMS_ITEMS.
      • 524,288: DAOPT_Flatten2String - This bit is a copy of the DIOPT_Flatten2String bit in the di_options column of DMS_ITEMS. It indicates that the column is the result of flattening the corresponding DMS item to a string (the DMS item must have an OCCUR clause when this bit is set). The Client also copies the dms_subtype value from DMS_ITEMS to the column with the same name in DATAITEMS during a define, redefine or the Administrative Console's Customize commands, as the Client does not use the DMS_ITEMS table during process and clone commands.
      • 1,048,576: Reserved
      • 2,097,152: Reserved
      • 4,194,304: DTOPT_ItemKey_Modified - This bit allows the Client to know that the item_key value of the item in the DATAITEMS table was modified.
      changes
      These bits are used by the redefine and the Administrative Console's Customize commands.
      • 1: CHG_new - New entry
      • 2: CHG_modified - Modified entry
      • 4: CHG_del_before - One or more entries before this one were removed.
      • 8: CHG_del_after - One or more entries after this one were removed
      dms_link_ds_num This column holds the structure number of the data set to which a LINK item points. Thus a nonzero value in this column identifies a DMSII LINK. Links that use AA Values have a dms_item_type value of (10).
      dms_concat_num This column is a copy of the DMS_ITEMS column of the same name and is automatically set by the define and redefine commands since the DMS_ITEMS table is not loaded during a process or clone command. Do not modify this column in your user scripts.
      da_user_bmask This column, which shadows the da_options column, contains a bit mask that represents the columns in da_options that were customized. This column is used by the redefine and the the Administrative Console's Customize commands to restore the portion of da_options bits that has been customized while leaving the remaining bits intact.
      masking_info This column is used by the SQL Server Client to do data masking. This integer value contains the masking function type (none, default, email, random, partial) and the index of the corresponding parameter data for masking function that have parameters.

      The define command create entries with default mask for columns that have a datamask specification in DMSII DASDL. You can then change the masking type using the Administrative Console's Customize command.
      dflt_options This column is used to keep track of the initial state of the da_options bits before any customizations are performed. It allows the Client to clear the da_user_bmask bits that were set by customizations that were later undone.

      AF_STATS Client Control Table

      This table is used to hold the audit file statistics for the last 9999 audit files processed by the client.

      Column Description
      data_source This column contains the name of the data source to which the record belongs.
      audit_filenum The audit file number (1 to 9999).
      no_stat_available This column is set by Client to while processing an audit file to indicate that the record does not contain any statistics.
      audit_start_time First timestamp in the audit file (datetime2(7) for SQL Server and date for Oracle).
      audit_end_time Last time stamp in the audit file (datetime2(7) for SQL Server and date for Oracle).
      client_start_time Time when the Client first started processing the audit file (datetime2(7) for SQL Server and date for Oracle).
      client_end_time Time when the Client finished processing the audit file (datetime2(7) for SQL Server and date for Oracle).
      n_threads Number of threads configured in the Client
      elapsed Elapsed time (in milliseconds) processing the audit file. This is not always equal to client_end_time - client_start_time as the audit is sometimes processed in multiple Client runs.
      dms_rec_count Number of DMSII records received.
      sql_op_count Number of SQL updates executed.
      sql_rb_op_count Number of rolled back SQL updates.
      sql_suppressed Number of SQL updates that were suppressed because there were no changes in the BI and AI images for the columns involved in the update.
      sql_filtered Number of SQL updates that were eliminated by occurs table row filtering.
      recs_discarded Count of discarded records.
      recs_in_error Count of records in error.
      bytes_received Number of DMSII data bytes received from the server.
      total_bytes_received Number of DMSII data and protocol overhead bytes received from the server.
      bi_bytes_received Number of MODIFY_BI data bytes received from the server.
      create_count Number of CREATE records received from the server.
      delete_count Number of DELETE records received from the server.
      modify_count Number of MODIFY records received from the server.
      modify_bi_count Number of MODIFY_BI records received from the server.
      modify_ai_count Number of MODIFY_AI records received from the server.
      link_ai_count Number of LINK_AI records received from the server.
      state_count Number of STATE info records received from the server.
      doc_count Number of DOC records received from the server.
      commit_count Number of COMMITS.
      rollback_count Number of ROLLBACKS.