Getting Started

Use the topics in this section to prepare to clone a DMSII database.

Before you clone a DMSII data source, you must create Client control tables. Once they're created, you can populate them with DMSII information sent by the Databridge Engine and create the actual data tables that will store the cloned data. The resulting data tables are based on information from the Client control tables and any additional customizations made.

To customize how DMSII data sets get mapped to their corresponding relational database tables, you can use the Administrative Console's Customize command in the Settings drop-down button or you can write user scripts in SQL. The Customize command is compatible with command-line operations and can help make reorganizations easier to handle. For more information on the Customize command, see the Help in the Administrative Console. Writing and testing user scripts is typically time-consuming and requires a bit of trial and error. For instructions, see the Customizing with User Scripts section below.

Creating Client Control Tables

Use this procedure to create a set of control tables in a new installation when using the command-line Client. If you are using the Administrative Console, the Client control tables are created automatically when you define a new data source, unless the tables already exist.

When you upgrade your Databridge Client software, the dbfixup program updates your control tables to the current version. It first alters the control tables and performs any required updates to them. Then, it unloads the Client control tables to a file, recreates them using a configure command, and restores them from the unload file. If a problem occurs and you need to revert to the older version of the Client, the unload file will let you reload the Client control tables using the older version of the Client.

Client control tables contain information about the data sets in a DMSII database and information about the corresponding tables and their layout in the relational database. These tables are stored and maintained in the relational database.

To run a configure command

Make sure that the database software and the appropriate Windows services or UNIX processes are running. For more information, see your relational database documentation.
Make sure that the current directory is the working directory for the data source (DMSII database) that you are cloning. For details about the working directory, see the Databridge Installation Guide.

From a Windows Command Prompt or UNIX terminal session, enter the following:

dbutility [signon_options misc_options] configure

Where Is

signon_options For each Databridge Client type, the following command-line options specify the relational database signon parameters:

Oracle: [-U userid] [-P password] [-D database]

SQL Server: [-U userid] [-P password] [-W] [-O ODBCdatasource ]

misc_options Any of the following miscellaneous command-line options:

-T forces the Client to use a new trace file for this run, if tracing is enabled.

-f filename lets you specify a configuration file other than the default "dbridge.cfg" file in the config subdirectory of the Client's working directory.

-L forces the Client to use a new log file for this run.

-u, if you want to override conditions that dbutility would otherwise interpret as a possible user error. For example, you may want to create a second set of control tables within one relational database (these must be owned by a different user), or you might want to drop and re-create the control tables, removing all of the state information associated with the user tables.

In most cases you do not need the -u option.

You only need to run dbutility configure once, unless you drop your tables via the dbutility dropall command and have to start over.

(Optional) To display the tables created by dbutility configure, use a utility appropriate for your relational database. For example, for Microsoft SQL Server you can use the Query window of SQL Server Management Studio to enter the following:
```
select name from sysobjects where type = "U" order by name
```
For Oracle, you can use the SQL*Plus utility and enter the following:
```
select table_name from user_tables order by table_name
```

Creating a Second Set of Tables

Occasionally, sites create a test environment that includes a second set of Client control tables. Even though the second set of tables may coexist with the production environment, this type of test environment can negatively impact the performance of your production database and is not recommended.

Creating a second set of Client control tables in the same relational database requires a separate database user ID. You must keep the working directories for the two user IDs separate. Because table name duplications are allowed when using different user IDs, this can lead to script files with the same names. If you create a set of Client control tables by running a configure command under the user ID "usera", those tables will be owned by usera (for example, USERA.DATASOURCES). If you later use the user ID "userb" to run a configure command.

Databridge Client creates a new set of Client control tables owned by userb (for example, USERB.DATASOURCES). Usera and userb tables are treated independently of each other.

Dropping and Re-Creating Client Control Tables

If you drop and create the Client control tables, you must re-clone everything. All Databridge tables that contain DMSII data will remain in the relational database. However, all of the information required from the Client control tables to request updates from the Databridge Engine will be lost.

Under normal circumstances, use the dropall command before running another configure command. This ensures the following:

Tables and stored procedures created by the Databridge Client are dropped from the relational database
All scripts created by the Databridge Client are deleted from the dbscripts subdirectory of the working directory

In some rare cases where the dropall command cannot drop all the tables, you may want to drop and create the Client control tables directly by running dbutility configure as follows:

dbutility -u configure

The -u option bypasses the checks that prevent you from doing this when the Client control tables exists.

If you attempt to execute a dbutility configure command after the Client control tables have been populated, without adding the -u option, you get the following message:

ERROR: Databridge control tables are not empty, use dropall command first
- To bypass this test use the 'u' option for the configure command

This message is provided as a safeguard so that you do not inadvertently drop and create the Client control tables.

Updating Client Control Tables

You can update some values in the Client control tables. For best results, use the Administrative Console's Customize command to customize the table layout for the relational database instead of using user scripts. Avoid using tools like SQL Server Management Studio or SQL*Plus to directly update the control tables.

Note

If you previously used user scripts to update control tables and want to switch to using the Customize command, you'll need to update your Client control tables first. For instructions, see the Next Steps section in the Databridge Installation Guide.

Where	Is
Values in the `active` column of all tables	Set cloning on or off for data sets or DMS items.
Values in the `dms_subtype` column of the DMS_ITEMS tables	Specify the format of a field that is to be cloned as a date.
The `set_name` column in the DATASETS table and the `item_key` columns in the DMS_ITEMS table	Create a composite key.
The columns `ds_options` in the DATASETS table, `di_options` and `di_options2` in DMS_ITEMS, `dt_options` in DATATABLES and `da_options` in DATAITEMS	Set a value. Make sure that you do not clear any existing bits. You should use the logical OR and AND operator (BITOR or BITAND for the Oracle Client)
The `sql_type`, `sql_length` and `sql_scale` columns in the DATAITEMS table	Force the `define` command to remap values.
The `dms_concat_num` column in the DMS_ITEMS table	Set the value of the `dms_item_number` you want concatenated to this item.
The `table_name` and `index_name` columns in the DATATABLES table	Rename.
The `item_name` column in the DATAITEMS table	Rename.
The `item_number` column in the DATAITEMS table	Reorder columns.

Note

BITOR and BITAND are functions needed to set and clear bits in user scripts used by the Oracle Client in the various xx_options columns of the Client control tables. When you run a define or redefine command, the Client creates the BITOR function, while the BITAND function is part of SQL language of Oracle. BITAND (a,b) returns the bitwise AND of a and b while BITOR (a,b) returns the bitwise OR of a and b. This means that you can use the BITOR function as if it was part of the Oracle SQL functions.

The following example shows BITOR setting a bit:

update DATASETS set ds_options=BITOR(ds_options, 4096) where dataset_name='CUSTOMER

SQL Server Transact-SQL uses & and | to perform these functions. In the case of SQL Server the above example would look like:

update DATASETS set ds_options=ds_options | 4096 where dataset_name='CUSTOMER'

All scripts generated by the Oracle Client using the createscripts command use the BITOR function for settings bits and the BITAND function for clearing bits.

Primary and Secondary Data Tables

The Databridge data tables hold the cloned DMSII data. You will see two types of Databridge data tables:

Primary data tables, which are the relational database equivalent of the DMSII data sets. Primary table names are derived from the corresponding DMSII data set name by converting it to lowercase and replacing hyphens (-) with underscores (_).
Secondary data tables, which are additional tables that need to be generated to represent a DMSII structure that does not have a relational database equivalent (for example, items with OCCURS clauses that are not flattened). Secondary table names are constructed using the primary table name with an appropriate suffix.

Defining a Data Source

A data source is the DMSII database or FileXtract file that you want the Client to replicate. The DBServer control file (on the host) identifies each data source by name in the section that uses the key word SOURCE. A SOURCE has a FORMAT, FILTER and SUPPORT specification.

If you use Enterprise Server, each data source will be associated with a SOURCE in the Enterprise Server configuration file. This SOURCE is based on a base data source that matches a SOURCE in DBServer. If you use the base source without any additional filtering applied by Enterprise Server, the DBServer and Enterprise Server sources are identical and completely interchangeable.

Each data source has an entry in the DATASOURCES Client control table. The hostname column identifies the Databridge server by the domain name or IP address. The hostport column identifies the port on which the server listens for incoming connections. You can switch the server from DBServer to Enterprise Server simply by changing the values of these two columns.

Note

You may use Databridge FileXtract sources for Client operations. These sources are made to look like data sources for DMSII databases.

Using the Define Command

Follow these steps to define a data source and populate the Client control tables. You can also perform this action from the Administrative Console by navigating to the data sources page for the Client Manager in question and selecting Actions > Define/Redefine.

To define a data source

Note

This procedure assumes that Databridge Server is running and the signon parameters are configured appropriately.

Because the following dbridge.cfg parameters are difficult to change later without redefining and recloning, make sure that they're set appropriately before you run the define command:

allow_nulls
automate_virtuals
auto_mask_columns (SQL Server only)
bracket_tabnames (SQL Server only)
clr_dup_extr_recs
convert_ctrl_char
default_user_columns
dflt_history_columns
enable_dms_links
enable_dynamic_hist
external_column
extract_embedded
flatten_all_occurs
force_aa_only
history_tables
inhibit_required_opt
maximum_columns
min_varchar
minimize_col_updates
miser_database
optimize_updates
read_null_records
reorg_batch_size
sec_tab_column_mask
split_varfmt_dataset
strip_ds_prefixes
suppress_dup_warnings
suppress_new_columns
suppress_new_datasets
use_bigint (SQL Server only)
use_binary_aa
use_clob (Oracle only)
use_clustered_index (SQL Server only)
use_column_prefixes
use_date (SQL Server only)
use_datetime2 (SQL Server only)
use_decimal_aa
use_internal_clone
use_nullable_dates (Miser databases only)
use_primary_key
use_stored_procs
use_time (SQL Server only)
use_varchar

For information on setting these parameters, see Appendix C: Client Configuration.

Enter the following command:

dbutility [signon_opts misc_opts] define datasource hostname portnum

Note

If you previously used user scripts to update control tables and want to switch to the Customize command, you'll need to update your Client control tables first. For instructions, see the Next Steps section in the Databridge Installation Guide.

Where	Is
signon_opts	For each Databridge Client type, the following command-line options specify the relational database signon parameters: Oracle:`[-U userid] [-P password][-D database]` SQL Server: `[-U userid] [-P password] [-W] [-O ODBCdatasource]`
misc_opts	Any of the following miscellaneous command-line options: `-L` forces the Client to use a new log file for this run. `-T` forces the Client to use a new trace file for this run, if tracing is enabled. `-f` filename to specify a configuration file other than the default dbridge.cfg file in the working directory. `-u` allows the command to delete Client control table entries for a data source that already exists.
datasource	For DBServer: The name that matches the entry for SOURCE in the DBServer control file. You can enter the data source name in uppercase or lowercase. For DBEnterprise: The name of a source (base or filtered) defined in Enterprise Server.
hostname	The domain name or IP address of the Databridge server.
portnum	The TCP/IP port number on which the appropriate Databridge server listens for incoming calls.

For DMSII databases that have a large number of data sets and data items, the process of retrieving the layout information may take several minutes.

Read the following section, Results of the Define Command, and then specify which data sets and data items you do not want to be cloned or updated, as explained in Customizing with User Scripts.

Example

Assuming the DBServer control file contains SOURCE ORDDB and PORT=5001 on the host "OURHOST.CIN.AAA.COM", you would enter the following:

dbutility define ORDDB OURHOST.CIN.AAA.COM 5001

The Databridge Client makes remote procedure calls to DBServer to get DMSII database layout information. DBServer returns the DMSII layout information to the Client. The Client populates the control tables with the DMSII layout information and creates the corresponding relational database table layout.

The empty control tables (that were built during the dbutility configure command) are now populated.

For example, this SQL statement

select data_source, hostname, hostport from DATASOURCES

would yield a table similar to the following. Only the selected columns are shown.

data_source   hostname               hostport   
------------  ---------------------  ------
ORDDB         OURHOST.CIN.AAA.COM    5001

Results of the Define Command

The define command automatically does the following with table names and column names:

(Typically) converts data set, data item, and set names to lowercase and changes dashes to underscores for their equivalent relational database table, column, and index names. For more details on how this actually occurs, see Relational Database Table and Column Names.
Constructs secondary table names by appending an underscore followed by the lowercase data item name (for which the table is constructed) to the primary table name. For example, if a DMSII data item named SALES, which has an OCCURS clause, appears in a data set named CUSTOMERS, the relational database table generated for the OCCURS item is named customers_sales. For more details, see Handling DMSII OCCURS.
Appends the suffix _x to all object names that are relational database reserved words. For example, if a DMSII data set is named ORDER, which is a relational database reserved word, the table generated for the ORDER data set is named order_x. Likewise, for a DMSII data item named COUNT, which is also a relational database reserved word, the corresponding column would be named count_x.
Adds two-character prefixes to table names (i_, u_, and d_) when constructing the names of the stored procedures it uses to insert, update, and delete records from these tables. The result is that table names are limited to 28 characters, even though some relational databases limit table and index names to 30 characters.
Checks table and index names to see if they duplicate existing table and index names that Databridge previously created. Databridge recognizes only those relational database objects that it has created. When the Databridge Client finds a duplicate name, it makes the name unique in one of the following ways:
- Appending a numeric suffix. For a data set named ITEM that must be split into three tables, the resulting table names would be as follows: item, item1, item2.
- If the name is too long to add a suffix, overwriting as many of the last characters as necessary with numeric characters to make the name unique.

Cloning from Multiple Data Sources

If you are cloning multiple data sources to the same relational database and you have duplicate data set names, Databridge modifies the table name for those duplicates to avoid creating multiple tables with the same name.

For example, if you have two data sources (DSA and DSB), both of which include a data set named PRODUCTS, Databridge clones the data set from DSA into a table named "products". When Databridge clones DSB, it clones DSB's data set PRODUCTS into a table named "products1".

Important

To avoid potential errors, rename any tables that have duplicate names. For example, rename the "products" table to "products_a" for data source DSA and to "products_b" for data source DSB. You can rename tables during the relational database customization phase of the definecommand using the script.user_define. primary_tablename. For a sample script, see Renaming a Table.

The Databridge Client renames duplicate table names across data sources as a precaution against accidentally removing a table that contains good data. If you do not drop either of the data sources, rerunning the define command for either data source does not cause any problems.

For example, if you execute another define command for DSA because DMSII database A was reorganized, the define command looks for the table name "products" in the DATATABLES Client control table that belongs to data sources other than DSA. Because the name "products" belongs to DSA only, the definecommand does not find "products" as a table name under any other data source. Thus the table corresponding to the data set PRODUCTS will be named "products", as was the case earlier.

Similarly, if you execute a define command for DSB, the define command looks for the name "products" in the DATATABLES Client control table that belongs to data sources other than DSB. Because the name "products" belongs to DSA, the definecommand will find "products" as a table name used by another data source and it will resolve the conflict by renaming the table. Thus the table corresponding to the data set PRODUCTS will be named "products1" as was the case before the define command was run.

If you drop either of the data sources, however, the results may be different because the table name is no longer a duplicate. For example, if you drop DSA and then execute a define command for data source DSB, the table will be named "products", not "products1", because it is no longer a duplicate.

Similarly, if you do a dropall command and then execute a define command for data source DSB first, the tables will be named "products" for data source DSB and "products1" for data source DSA.

Add a Prefix to Duplicate Data Set Names

If you replicate two or more databases, which have many data set names in common, you can make the program add a prefix to all the table names for a data source. You must define the prefixes, which can be 1--8 characters long, before you create the relational database layout. To do this, assign a value, such as X1, to the tab_name_prefix column of the corresponding entry in the DATASOURCES Client control table using the script script.user_datasets.datasource. Using different prefixes for each data source makes the table names unique and eliminates the need to rename tables.

If you are using multiple data sources that have data sets or indexes that have the same name, we strongly recommend that you write user scripts to resolve this issue by forcing such a table to use a different name for one (or more if the name occurs in more than two data sources). This will ensure that you have a consistent naming convention. Without this, you could run into problems if you reorganize these data sets.

Example script

script.user_define.customer:
update DATATABLES set table_name='customer_demodb'
where data_source='DEMODB' and dataset_name='CUSTOMER'
/***/
update DATAITEMS set table_name='customer_demodb'
where data_source='DEMODB' and table_name='customer'

This example script forces the table 'customer' in data source DEMODB to always be renamed. If another data source also has a data set named CUSTOMER, it will then be able to always use the name 'customer' for the corresponding table. It also makes sure that all the items in the renamed table point to the renamed table. The line /***/, which separates the two SQL statements in the script, tells the Client to execute the first SQL statement before moving on to the second one.

Customizing with User Scripts

User scripts are files that contain SQL statements for modifying the Client control tables. They provide a convenient way of automating the customization changes that are applied to the control tables. The Databridge Client looks for user scripts in the directory specified by the configuration file parameter user_script_dir. If you do not set the user_script_dir parameter in the configuration file, the Databridge Client uses the scripts directory. It automatically executes user scripts when certain commands are run, provided they exist.

The main purpose of user scripts is to preserve changes to the control tables by having the program run these scripts to restore the changes whenever necessary. To view sample data set layout and data table customization scripts, see Appendix D: Customization Scripts.

Note

You can customize the Client control tables easily by using the Customize command instead of writing user scripts.

Customizing a DMS item is very simple. Click on the data set name in the data sets view of the Customize command to open the DMS item view. Then click the wrench to the left of the DMS item that you want to customize. This open up the properties page for the item where you can then click on the appropriate radio button option (e.g. "Replicate as date"). You may also need to change additional properties of the item when the dms_subtype needs to be set.

You can find a complete description of the additional requirements for user scripts that are compatible with the Customize command in Appendix D: Customization Scripts. For information about using the Customize command to customize your data source, see the Databridge Administrative Console Help.

Types of User Scripts

The Databridge Client supports the following types of user scripts:

Script/Filename	Description
Session initialization script	`script.user.session` This script allows you to change session parameters without changing the database settings. Use this script to alter session parameters whose default values are not suitable for Client operations. For example, when the NLS_LANGUAGE for Oracle is a European language, ALTER the NLS_LANGUAGE parameter to set the language to AMERICAN and the NLS_TERRITORY to AMERICA. The Databridge Client executes these scripts when running any command that connects to the relational database. NOTE: The Oracle Client will automatically execute the SQL to ALTER the SESSION when the language is not AMERICAN or when the character set of the database is UTF8. However, it will only do this if there is no session script present in the user scripts directory. This allows you to override the actions taken by the Client by providing a session script to use instead.
Data set global mapping customization script	`script.user_datasets.datasource` where datasource is the name of the data source ( in lowercase) as defined in the DATASOURCES Client control table. Use this script to disable mapping for unwanted data sets or to enable mapping for data sets that are not mapped by default. Create only one of these scripts for each data source. The Client processes the user script `script.user_datasets.datasource` before the DMSII mapping phase of both the `define` and `redefine` commands. This script can contain global script commands that allow you to make changes to multiple columns in the DATASETS and DMS_ITEMS layouts with a single SQL statement. For instance, if a DMSII database has a time value item called TS in almost every data set, you can use a single SQL statement to update the dms_subtype value for every occurrence of TS. For an example script, see Sample Data Set Global Mapping Customization Script.
Data set mapping customization script	`script.user_layout.primary_tablename` where datasource is the name of the data source (in lowercase) as defined in the DATASOURCES Client control table. This script is run after the relational database layout has been created during a `define` or `redefine` command. It allows you to make global changes to DATATABLES and DATAITEMS. You can use this script to insert common scripts into a single file rather than having to duplicate the SQL in each of the define scripts for the individual data sets. For an example, see Sample Data Table Global Customization Script.
Data table customization script	`script.user_define.primary_tablename` where primary_tablename is the name of the primary table mapped from the data set. These scripts make changes to the DATATABLES and DATAITEMS tables for changing table or column names, changing SQL data types, and so on. Create one of these scripts for each data set that has one or more tables which need to be customized. When you change the name of the table within the script, you must use the original primary table name in the script filename. All changes related to tables mapped from a data set are contained in the data table customization script for the primary table specified by tablename. The Databridge Client runs these scripts after the relational database layout has been created by the `define` and `redefine` commands.
Data table creation user script	`script.user_create.tablename` where tablename is the name of the relational database table. Use this script for the following: To define default values for non DMSII columns To alter the table and add a column that the Databridge Client does not need to be aware of, add a "ALTER TABLE xxx ADD COLUMN yyy" SQL statement to these scripts instead of adding SQL statements to the table creation scripts. CAUTION: Do not use this script to create columns for specific types of data generated by the Client. This script creates a type of user column that the Client is unaware of. To create user columns that the Client is aware of, see Adding a Non DMSII Column.
Index creation user script	`script.user_index.tablename` where tablename is the name of the relational database table. Use this script to add SQL statements to the index creation scripts (`script.index.`tablename) created by the dbutility `generate` command. Do not modify the scripts created by the generate command, as your changes will be lost the next time a generate command is run. These scripts are executed immediately after the related Databridge Client script named `script.index`.tablename during the `process` or `clone` command. If you set the `check_user_scripts` parameter, the Databridge Client returns a warning if it cannot find the script.
Data table cleanup user script	`script.user_cleanup.tablename` where tablename is the name of the relational database table Use these scripts to undo any actions, such as creating a secondary index, that are done in the `script.user_index.table` user script. These scripts are run during the `process` or `clone` command, prior to executing the cleanup scripts created by the `generate` command. The scripts are only used in cases where the relational database tables are not dropped when a data set is re-cloned, such as when deleted records are to be preserved.
Stored procedure creation user script	`script.user_create_sp.`tablename where tablename is the name of the relational database table. This new type of script allows the user to split updates to stored procedures from other actions taken when a table is created by the Client. Unlike the user scripts `script.user_create.`tablename, this script is also run when the table is refreshed during a `refresh` or `reorg` command. This allows the user to alter the stored procedures without requiring any manual intervention.

User Script Syntax

Use the syntax you would typically use for SQL statements; however, separate each statement with the following separator:

/***/

In addition, be aware of the following:

You must begin the separator line with the characters /***/ and no leading spaces . Trailing blanks or carriage returns are ignored.
Do not end the script with the /***/ separator.
Do not use a semicolon or GO as you would if you were using a relational database query tool.
You can add comments to the end of any line (including a blank line) by using " //" to start a comment. This causes the Client to ignore the rest of the line, including these two characters. If you add a comment to a separator line, the separator must be followed by at least one space.

Writing and Testing User Scripts

Following is a recommended method for creating user scripts. Typically, you would start writing your user scripts after you have run configure and define for the first time. This procedure does not cover the data table creation user script or the index creation user script.

Caution

If you have already used the Databridge Client to clone a database, we highly recommend that you test your scripts using a test version of the Client control tables, not your production version of the Client control tables.

Follow these guidelines as you develop your user scripts:

Store your user scripts in the directory pointed to by the user_script_dir parameter of the Client configuration file (by default, the scripts subdirectory of the data source's working directory). Storing them in the global working directory ensures that they are protected by file security, if enabled.
Use the runscript command to test each script. This command executes the scripts as a transaction. If an error occurs in a script, the Databridge Client rolls back all changes. You then have the opportunity to fix the error and rerun the script.
If you make a mistake and change the Client control tables in a way you did not intend to, remove or rename the offending script and then run dbutility define again. This creates a fresh set of Client control tables.

To write and test user scripts

Do one of the following:
- If you are already using Client control tables in production, run configure to create a test version of the Client control tables or unload to create a backup copy of the tables.
- If you haven't created Client control tables yet, run configure.
Run define to populate the Client control tables.
Run display to create a report of your Client control tables. This report gives you a record of table names, column names, and so on, that you can use as a reference as you write your user scripts.
Create your data set mapping customization scripts, as follows:
- Create the data set selection script for selecting/deselecting data sets. See Sample Data Set Selection Script.
- Create a data set mapping customization script for each data set that requires that its mapping be customized. These user scripts can contain several SQL statements that perform different types of mapping customizations (for example, flatten OCCURS clauses, specify that items should be cloned as dates, and disable the cloning of some DMSII items). See Tips for More Efficient Cloning.
- Test each script as follows:
  
  dbutility [-n] runscript scriptfilename
where scriptfilename is the name of the script you're testing and -n is a command-line option that overrides your entry for user_script_dir by allowing you to specify a complete path for the script.

Note

The runscript command runs the script in transaction mode. If an error occurs during script execution, the Databridge Client rolls back all changes. This allows you to safely rerun the script after correcting it.
Fix any errors uncovered by running the scripts, and rerun the script until it is correct.

If the script gets corrupted beyond repair, rerun the define command as described in step 2. You must add the -u command-line option to force the program to allow you to rerun the define command.
When you are satisfied with the script, repeat the define command.

You can also set bit 8 of the status_bits column of the DATASETS Client control table to inform dbutility that the data set needs to be redefined. To set this value, run the following within a relational database query tool:
```
update DATASETS set status_bits = 8
where dataset_name = 'DSNAME' and data_source = 'SOURCE'
```
Then execute a define command to refresh the mapping.
Repeat step 3 at this point to view the effect of your data set mapping customization.
Create a data table customization script for each data set whose tables need to be customized.

These user scripts can contain several SQL statements that perform different types of customizations for any of the tables mapped from the data set (for example, renaming a table, renaming a column, changing the sql type column of a data item, inserting a non DMSII item into a tables). See Sample Data Table Customization Scripts.
Test each script as described in step 6.

Caution

Include all changes that affect the tables derived from a data set in that data set's script. For example, after a reorganization, the Databridge Client runs your data table customization user scripts after the relational database layout has been created by a define command. If some scripts are missing, or if a data table customization script does not include all the changes for its tables, the Databridge Client creates tables that have different layouts than the original ones.
Fix any errors uncovered by running the scripts, and rerun the script until it is correct.

If the script gets corrupted, rerun the define command as described in step 2. You must add the -u command-line option to force the program to allow you to rerun the define command.
Run dbutility define again, using the -u option. If you don't use the -u option, the define command will tell you the data source already exists. Enter the following:
```
  dbutility -t0x801 -u datasource hostname portnumber
```
The Databridge Client automatically runs your user scripts and updates the Client control tables accordingly. The -t 0x801 option produces a trace of all SQL commands that execute as part of user scripts. These are followed by row counts for update or insert statements. If you do not enable tracing, you will only see the row counts in the log file.

The next phase of the define command executes the mapping of the DMSII data sets to relational database tables for data sets whose active column is set to 1. Finally, the Databridge Client runs the data table customization scripts for all the data sets whose active column is set to 1. The -t 0x801 options also produce a trace of all SQL commands in these scripts.

The Databridge Client runs the data set selection scripts and all the data set mapping customization scripts as well as the data table customization scripts in a single transaction group. If there is an error, the Databridge Client does not commit any of the changes; instead, it rolls back all changes and the command terminates.

Note

If you created table creation or index creation user scripts, the Databridge Client runs those immediately after running its own table creation or index creation scripts.
If you decide to clone a data set or data item that you did not previously clone or if a DMSII reorganization occurs, you will need to update your scripts.

Using Scripts to Disable Data Sets

To disable cloning by writing user scripts, do the following:

Disable data set cloning via script.user_datasets.datasource
Disable DMSII item cloning via script.user_layout.primary_tablename
Once you are familiar with the concepts in this section, see Customizing with User Scripts.

When using the Administrative Console or Customize command you can simply uncheck the checkbox for the active column of the data sets you want to disable. The Client will remember the changes unless you drop the data source and start from scratch.

Decoding DMSII Dates, Times, and Date/Times

This section explains the following:

How to decode DMSII dates, times, and date/time formats into appropriate relational database types by modifying the DMS_ITEMS Client control table via the script.user_layout.primary_tablename user script
How to change the SQL data type of the resulting relational database column

Note

You can make the same types of customizations to the Client control tables using the Customize command as you can by writing user scripts. You can find a complete description of the additional requirements for user scripts that are compatible with the Client Customizer in Appendix D: Customization Scripts. For information about the Customize command, see the Databridge Administrative Console Help.

After you are familiar with the concepts in this section, see Appendix D: Customization Scripts.

DMSII Dates

Even though DMSII did not have a date data type until the advent of DMSII 57.1, most DMSII sites use several common methods to store dates. This section includes ways to decode these types of date representations into a relational database date data type. A DMSII 57.1 date is stored as a REAL in DMSII and represents the number of days since 12/31/1600. The Client automatically converts DMSII 57.1 dates to relational database dates making it unnecessary to do any customization

The Databridge Client supports the following DMSII date encoding methods:

Script/Filename	Description	See
DMSII GROUPS	Three numbers for year, month, and day	DMSII Dates Represented as a GROUP of Numbers- - approach #1 and DMSII Dates Represented as a GROUP of Numbers - approach #2
DMSII NUMBER values	Any of the following: MISER database dates, usually NUMBER(5) LINC database dates Month, day, and year represented by a 6- or 8-character alpha string containing only digits Delimited dates such as (03/10/10) Dates with three-character month names (MAR102005) Julian dates represented as a five-digit number (06905) or seven-digit number (0692005)	Decoding DMSII Dates Represented as ALPHA or NUMBER
DMSII ALPHA values	Any of the following: LINC database dates LINC database dates Month, day, and year represented by a 6- or 8-character alpha string containing only digits Delimited dates such as (03/10/10) Dates with three-character month names (MAR102005)
DMSII Times		DMSII Times Represented as ALPHA, NUMBER, or REAL
Custom DMSII dates	Any of the following: Month/year without day or other unique variations Non-Standard dates Month/year without day or other unique variations	Custom DMSII Date/Time Represented as ALPHA or NUMBER

Choosing the SQL Data Type of the Relational Database Column {#b1jbb5sf}

Regardless of the original DMSII date structure, the resulting relational database column has a default sql_type of 12 (smalldatetime) in the case of SQL Server and a sql_type of 10 (date) in the case of Oracle.

To make the Client map a DMS item to a column that is a date data type, you must set the bit DIOPT_Clone_as_Date (2) in the di_options column of the corresponding DMS_ITEMS entry using the user script script.user_layout.dataset. Setting the configuration parameter use_date to true makes the Client use the date data type for all dates that have no time part, regardless of whether this bit is set or not.

SQL Server supports multiple date data types. You can make the Client generate different types of dates by using the script.user_layout.dataset user script to set the following bits in the di_options column of the corresponding DMS_ITEMS table entry:

DIOPT_UseLongDate (128) causes the Client to a use a data type of 10 (datetime) instead of smalldatetime.
DIOPT_UseLongDate2 (65536) causes the Client to use the datetime2 data type. If both this bit and the DIOPT_UseLongDate bit are set, datetime2 is used. Setting the configuration parameter use_datetime2 to true makes the Client use the datatime2 data type regardless of whether this bit is set or not when the DIOPT_UseLongDate bit is set.
DIOPT_Clone_as_DateOnly (32768) causes the Client to use the date data type which is 3-bytes long and contains no time.

Relational Database Date Data Type	Value for sql_type Column
Microsoft SQL Server: datetime (8 bytes)	10
Microsoft SQL Server: smalldatetime (4 bytes)	12
Oracle: date (7 bytes)	10
Microsoft SQL Server: int Oracle: number(10) NOTE: The date is formatted according to the numeric_date_format configuration parameter, whose default value is 23 (mmddyyyy).	13
Microsoft SQL Server: datetime2 (8 bytes)	19
Microsoft SQL Server: date (3 bytes)	20

For an example script, see Changing SQL Data Types.

DMSII Dates Represented as a GROUP of Numbers- - approach #1

The DMSII GROUP must always contain a year and a month; the day can be omitted, in which case it defaults to 1.

To clone a DMSII date (represented as a group of numbers) as a relational database date

Write a user script (script.user_layout.primary_tablename) that does the following:

Sets the DIOPT_Clone_as_Date (2) bit in the di_options column for the GROUP

Sets the dms_subtype column of the group members in DMS_ITEMS to indicate which part of the date they represent, as follows:

Part of Date in GROUP	Value for dms_subtype Column
Year (assumes a 1900 base)	1
Month	2
Day	3
Year By default, yy values < 50 are 21st century years (20yy) and yy values > 50 are 20th century years (19yy).*	4
Absolute year This is a 4-digit year specification (for example, 2010).	5

The following SQL statements cause the Databridge Client to clone the DMSII group INV_DATE as a relational database date type.

Filename: script.user_layout.inv

update DMS_ITEMS set di_options=2
where dataset_name='INV' and dms_item_name='INV_DATE'
/***/
update DMS_ITEMS set dms_subtype=1
where dataset_name='INV' and dms_item_name='INV_DATE_YEAR'
/***/
update DMS_ITEMS set dms_subtype=2
where dataset_name='INV' and dms_item_name='INV_DATE_MONTH'
/***/
update DMS_ITEMS set dms_subtype=3
where dataset_name='INV' and dms_item_name='INV_DATE_DAY'

The Customize command does not support this method of handling GROUP dates. However, it does support the equivalent method described in the next section. When converting old scripts to a format compatible with the Customize command, the dbscriptfixup utility converts the changes made by this type of script to the format described below.

DMSII Dates Represented as a GROUP of Numbers - approach #2

This version of Databridge Client now supports a new method of handling DMSII dates represented as a GROUP (or a nested GROUP). The Client redefines a group of like items, that can either be unsigned numbers or alpha items, as a single item having the common type and encompassing the entire GROUP. This operation is referred to as collapsing (or redefining) a GROUP). By collapsing a GROUP of numbers that represent a date, we effectively make the operation of cloning it as a relational database date equivalent to that of cloning a number that represents a date.

For example, this technique can collapse the year, month, and day in the following DMSII GROUP in the data set named EMPLOYEE into a single item that acts as a NUMBER(8) :

EMP-HIRE-DATE-YMD GROUP
(
   EMP-HIRE-YEAR NUMBER(4);
   EMP-HIRE-MONTH NUMBER(2);
   EMP-HIRE_DAY NUMBER(2);
)

The method described in the next section can then customize this column as needed. This technique also applies to date/time quantities represented as a group of like items.

To clone a DMSII date (represented as a group of numbers) as a relational database date

Sets the DIOPT_CollapseGroup (67,108,864) and the DIOPT_Clone_as_Date (2) bits in the di_options column.
Sets the dms_subtype column of the GROUP item in DMS_ITEMS to indicate the format in which the resulting date is encoded. See the section below for a list of date formats (the above date group is represented by a dms_subtype of 21).

The script to perform this action is:

Filename: script.user_layout.employee

update DMS_ITEMS set di_options=67108866, dms_subtype=21
where dataset_name='EMPLOYEE' and dms_item_name='EMP-HIRE-DATE-YMD'

Decoding DMSII Dates Represented as ALPHA or NUMBER

Use the following procedure to decode DMSII dates represented as NUMBER or ALPHA items to relational database data types.

To decode dates represented as NUMBER or ALPHA items

Write a script ( script.user_layout.primary_tablename) that does the following:
Sets the DIOPT_Clone_as_Date (2) bit in di_options.

Sets the dms_subtype column in DMS_ITEMS to indicate the type of date encoding method used on the host, as follows:

Date Encoding Scheme	Value for dms_subtype Column
NUMBER(n) for MISER dates—days since 12/31/1899	1
NUMBER(n) for LINC dates—days since 1/1/baseyear (default 1957)	3
ALPHA(6) or NUMBER(6) with two-digit year yy (1900–1999) yymmdd yyddmm mmddyy mmyydd ddmmyy ddyymm	11 12 13 14 15 16
ALPHA(5) or NUMBER(5) with two-digit year yy (1900–1999) and with days DDD where DDD is a number between 1–366 for Julian dates DDDyy yyDDD	17 18
ALPHA(8) or NUMBER(8) with four-digit year yyyy yyyymmdd yyyyddmm mmddyyyy mmyyyydd ddmmyyyy ddyyyymm	21 22 23 24 25 26
ALPHA(7) or NUMBER(7) with four-digit year yyyy and with days DDD where DDD is a number between 1–366 for Julian dates DDDyyyy yyyyDDD	27 28
ALPHA(6) or NUMBER(6) with two-digit year yy (1950–2049) where yy values < 50 are 21st century years (20yy) and yy values > 50 are 20th century years (19yy) yymmdd_2000 yyddmm_2000 mmddyy_2000 mmyydd_2000 ddmmyy_2000 ddyymm_2000	31 32 33 34 35 36
ALPHA(5) or NUMBER(5) with two-digit year yy (1950–2049) where yy values < 50 are 21st century years (20yy) and yy values > 50 are 20th century years (19yy) and with days DDD where DDD is a number between 1–366 for Julian dates. DDDyy_2000 yy* DDD_2000	37 38
ALPHA(8) with two-digit year yy (1900–1999) and with delimiter characters where / represents forward slash (/), hyphen (-), or period (.). yy/mm/dd yy/dd/mm mm/dd/yy mm/yy/dd dd/mm/yy dd/yy/mm	41 42 43 44 45 46
ALPHA(10) with four-digit year yyyy and with delimiter characters where / represents forward slash (/), hyphen (-), or period (.). yyyy/mm/dd yyyy/dd/mm mm/dd/yyyy mm/yyyy/dd dd/mm/yyyy dd/yyyy/mm	51 52 53 54 55 56
ALPHA(8) with two-digit year yy (1950–2049) where yy values < 50 are 21st century years (20yy) and yy values > 50 are 20th century years (19yy) and with delimiter characters where / represents forward slash (/), hyphen (-), or period (.).* yy/mm/dd_2000 yy/dd/mm_2000 mm/dd/yy_2000 mm/yy/dd_2000 dd/mm/yy_2000 dd/yy/mm_2000	61 62 63 64 65 66
ALPHA(7) with two-digit year yy (1900–1999) and three-character month abbreviation (mon). Month abbreviations are JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, and DEC unless specified otherwise by the months parameter in the Databridge Client configuration file. yymondd yyddmon monddyy monyydd ddmmyy ddyymon	71 72 73 74 75 76
ALPHA(9) with four-digit year (yyyy) and three-character month abbreviation (mon). Month abbreviations are JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, and DEC unless specified otherwise by the months parameter in the Databridge Client configuration file. yyyymondd yyyyddmon monddyyyy monyyyydd ddmonyyyy ddyyyymon	81 82 83 84 85 86
ALPHA(7) with two-digit year yy (1950–2049) where yy values < 50 are 21st century years (20yy) and yy values > 50 are 20th century years (19yy) and with three-character month abbreviations (mon). Month abbreviations are JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, and DEC unless specified otherwise by the months parameter in the Databridge Client configuration file. yymondd_2000 yyddmon_2000 monddyy_2000 monyydd_2000 ddmonyy_2000 ddyymon_2000	91 92 93 94 95 96

The configuration parameter century_break allows you to adjust the range for the year. The default value for century_break is 50. A value of -1 causes the Client to automatically set the century break based on the year in the audit timestamp.

For example scripts, see Cloning a Numeric Field as a Date and Cloning an Alpha Field as a Date.

Note

If your DMSII date format includes mmyy or yymm without a position for days, see Custom DMSII Date/Time Represented as ALPHA or NUMBER.

DMSII Times

The Databridge Client supports several DMSII ALPHA, NUMBER, or TIME encoding methods for time of day and elapsed time.

Choosing the SQL Data Type of the Relational Database Column

The relational database column---regardless of the original DMSII time structure---has a default sql_type of 17, which is a Microsoft SQL Server int or Oracle number(6), except for TIME(12) and TIME(14), which are stored as a number (10). TIME(12) and TIME(14) are formatted as ddddhhmnss, where dddd is the number of days.

All other TIME types are formatted as hhmnss. To make the Client map a DMS item to a column that is a numeric time, you need to set the bit DIOPT_Clone_as_Time (256) in the di_options column of the corresponding DMS_ITEMS entry using the user script script.user_layout.dataset.

In the case of SQL Server, which has a time data type, the Client can store these values using the time data type. You can do this by setting the di_options bit DIOPT_Use_Time (131072) in the corresponding entry in the DMSII_ITEMS table using the script.user_layout.dataset user script. If you set both the DIOPT_Clone_as_Time bit and the DIOPT_Use_Time bit, the latter takes precedence.

DMSII Times Represented as ALPHA, NUMBER, or REAL

You can decode DMSII times represented as ALPHA, NUMBER, or REAL items to relational database data types using the Databridge host or the Databridge Client. To do this on the host (versus the Databridge Client), you must redefine the DMSII item using an ALTER REDEFINE. For more information, see Chapter 5 of the Databridge Programmer's Reference.

To decode those data types using the Databridge Client

Write a script (script.user_layout.primary_tablename) that does the following:

Sets the DIOPT_Clone_as_Time (256) bit in di_options.
Sets the dms_subtype column in DMS_ITEMS to indicate the type of time encoding method used on the host, as follows:

Time Encoding Scheme	Value for dms_subtype Column
ALPHA(6) or NUMBER(6) time of day in hhmnss format	1
REAL containing a TIME(1) value, which represents the time of day in 1/60th of a second	2
REAL containing a TIME(11) value, which represents the time of day in ticks (2.4 microseconds)	3
REAL containing a TIME(12) or TIME(14) value, which represents the elapsed time in ticks	4
REAL containing a DMSII 57.1 TIME, which represents the number of 100’th of seconds since midnight. These are automatically converted to TIME data types if the database supports it. Otherwise, they are stored as integer values of the form "hhmmss".	5
NUMBER(12) containing the time of day in hhmnssmmmmmm format where mmmmmm represents fractions of seconds.	6

For an example script, see Cloning an Alpha or Number Field as a Time.

Decoding DMSII Date/Times

The Databridge Client implements a set of dms_subtype values to decode DMSII items that include the date and time in a single item. Specifically, the Databridge Client contains values for DMSII ALPHA or NUMBER values that represent the date/time in a variety of ways, such as:

Month, day, year, and time of day combined into a twelve-digit (031005112501) or fourteen-digit (03102005112501) number
Julian dates and time of day represented as an eleven-digit number (06905112501) or a thirteen-digit number (0692005112501)
DMSII item of type REAL that are 48-bits long and represent TIME(6), TIME(7), or TIME(60) type data which encode a date and a time. A new data type in DMSII 57.1, named TIMESTAMP, represents TIME(6) values. The Databridge Client automatically converts these items to the appropriate relational database date/time data type, thus eliminating the need to do any special customization.

To decode these types of date/time representations into a relational database date/time data type, see Decoding DMSII Date/Time Represented as ALPHA or NUMBER. When using these with SQL Server, you should set the di_options bit DIOPT_Use_LongDate to force the Client to use a data type of datetime rather than smalldatetime. When a data type of smalldatetime is used, the Client sets the values of seconds to zero (0), as SQL Server rounds the value to increments of .000, .003, or .007 seconds. You can aslo use a data type of datetime2 instead of datetime by setting the di_options bitDIOPT_UseLongDate2 (65536). Setting the configuration parameter use_datetime2 to true makes the Client use the datatime2 data type regardless of whether this bit is set or not when the DIOPT_UseLongDate bit is set.

Decoding DMSII Date/Time Represented as ALPHA or NUMBER

You can decode DMSII date/time formats represented as NUMBER or ALPHA items to relational database date/time data types using the Databridge host or the Databridge Client. To do this on the host, you must redefine the DMSII item using an ALTER REDEFINE. For more information, see Chapter 5, "Alter Data Sets" in the Databridge Programmer's Reference.

To decode DMSII date/time formats represented as NUMBER or ALPHA items, write a script ( script.user_layout.primary_tablename) that does the following:

Sets the DIOPT_Use_Long_Date (128) bit in di_options.
Sets the dms_subtype column in DMS_ITEMS to indicate the type of date/time encoding method used on the host, as follows:

Note

If your DMSII date/time encoding scheme is not listed in the following table, see the next section.

Date/Time Encoding Scheme	Value for dms_subtype Column
ALPHA(14) or NUMBER(14) with four-digit year followed by a six-digit time yyyymmddhhmnss yyyyddmmhhmnss mmddyyyyhhmnss mmyyyyddhhmnss ddmmyyyyhhmnss ddyyyymmhhmnss	121 122 123 124 125 126
ALPHA(13) or NUMBER(13) with four-digit year yyyy and with days DDD where DDD is a number between 1–366 for Julian dates followed by a six-digit time DDDyyyyhhmnss yyyyDDDhhmnss	127 128
ALPHA(12) or NUMBER(12) with two-digit year representing dates in both the 20th and 21st centuries followed by a six-digit time yymmddhhmnss yyddmmhhmnss* mmddyyhhmnss mmyyddhhmnss ddmmyyhhmnss ddyymmhhmnss	131 132 133 134 135 136
ALPHA(11) or NUMBER(11) with two-digit year representing dates in both the 20th and 21st centuries where days DDD is a number between 1–366 for Julian dates followed by a six-digit time DDDyyhhmnss yyDDDhhmnss	137 138
ALPHA(12) or NUMBER(12) with two-digit year yy (1900–1999) preceded by a six-digit time hhmnssyymmdd hhmnssyyddmm hhmnssmmddyy hhmnssmmyydd hhmnssddmmyy hhmnssddyymm	211 212 213 214 215 216
ALPHA(11) or NUMBER(11) with two-digit year yy (1900–1999) and with days DDD where DDD is a number between 1–366 for Julian dates preceded by a six-digit time hhmnssDDDyy hhmnssyyDDD	217 128
ALPHA(14) or NUMBER(14) with four-digit year preceded by a six-digit time hhmnssyyyymmdd hhmnssyyyyddmm hhmnssmmddyyyy hhmnssmmyyyydd hhmnssddmmyyyy hhmnssddyyyymm	221 222 223 224 225 226
ALPHA(13) or NUMBER(13) with four-digit year yyyy and with days DDD where DDD is a number between 1–366 for Julian dates preceded by a six-digit time hhmnssDDDyyyy hhmnssyyyyDDD	227 228
ALPHA(12) or NUMBER(12) with two-digit year representing dates in both the 20th and 21st centuries preceded by a six-digit time hhmnssyymmdd hhmnssyyddmm hhmnssmmddyy hhmnssmmyydd hhmnssddmmyy hhmnssddyymm	231 232 233 234 235 236
ALPHA(11) or NUMBER(11) with two-digit year representing dates in both the 20th and 21st centuries where days DDD is a number between 1–366 for Julian dates preceded by a six-digit time hhmnssDDDyy hhmnssyyDDD	237 238

The configuration parameter century_break allows you to adjust the range for the year.

For example scripts, see Cloning an Alpha or Number Field as a Date/Time.

Custom DMSII Date/Time Represented as ALPHA or NUMBER

You may be able to decode DMSII date/time formats represented as NUMBER or ALPHA items, and convert them to relational database date/time format even if you could not find the correct encoding scheme in the previous sections. For instance, if the DMSII date item has no day (mmyy or yymm), dms_subtype of 0x32 or 0x23 converts this to relational database date/time with a day as "1" and the time as all zeros. For this to work, the DMSII item cannot include any ALPHA data (such as slashes, dashes, or month names). Therefore, 01-FEB-14 would not convert, but 0214 would.

To decode these custom date or date/time layouts using the Databridge Client, write a script (script.user_layout.primary_tablename) that does the following:

Sets the DIOPT_Clone_as_Date (2) and the DIOPT_VarFormat_Date (2048) bits in di_options.
Sets the dms_subtype column in DMS_ITEMS to indicate the hexadecimal string, in the same order as the host item layout, as follows:

Date/Time Encoding Scheme	Description	Hexadecimal Value for dms_subtype Column
yyyy	Four-digit year	1
yy	Two-digit year within 1950-2049 To adjust this range, use the century_break configuration parameter. See century_break.	2
mm	Two-digit month	3
dd	Two-digit day	4
hh	Two-digit hour	5
mn	Two-digit minutes	6
ss	Two-digit seconds NOTE: The Databridge SQL Server Client stores all host values for seconds (ss) as zero unless you add the DIOPT_Use_LongDate (128) bit to `di_options` in step one of the layout script. See "di_options" in DMS_ITEMS.	7
mmm	Three-digit fractions of seconds (milliseconds)	8
mmmmmm	Six-digit fractions of seconds (nanoseconds)	9
mm	Two-digit fractions of seconds (centiseconds)	10

As stated previously, the format can be as short as yymm (dms_subtype 0x23 or 35 decimal). Formats like mmhhyy are supported (dms_subtype of 0x253 or 850 decimal) as well as longer ones. For example, a mainframe date/time layout of mmsshhmnddyy uses the dms_subtype value of 0x375642 or 3626562 decimal.

Numeric Date and Time in Non-Contiguous Columns

When a DMSII date and time are in contiguous column, you can easily make the Client handle the combined columns as a single date/time quantity by merging the two columns. You can do this by setting the bit 16777216 in di_options of the first item to make the define command merge the two items when it maps them to the relational database table. You can then mark the item to be cloned as a date and set the appropriate value for its dms_subtype column. For example, if you have an item that is a NUMBER(8) representing a date which is immediately followed by an item that is NUMBER(6) representing a time, you can make the Client treat the first item as if it were a NUMBER(14) ignore the second one. This can also be done by using an ALTER REDEFINE in DBGenFormat.

When the two columns are not contiguous, use the dms_concat_num column to append the time part of the combined item to the date part. This column must be set to the item number of the item containing the time value. The Client will effectively treat these two items as if the second one were concatenated to the first one. You must also set the di_options bit 524288 (0x80000) to make the Client include the second item in DATAITEMS with its active column set to 0. This is a lot more efficient than using DBGenFormat to perform this operation.

See a sample script and its explanation here, Concatenating Two Items and Cloning the Result as a Date/Time

Creating Indexes for Tables

This section explains how the Databridge Client creates indexes for tables mapped from a DMSII data set.

Ideally, the Databridge Client uses the optimum SET among the various sets defined for the data set in the DASDL. Only SETs that have the NO DUPLICATES ALLOWED attribute (SETs with unique keys) qualify for this selection.

Keys Derived from the DMSII Database

First, the Databridge Engine decides whether any SETs meet this requirement. If more than one SET does, the Databridge Engine uses the SET with the least number of keys. In case of a tie, it uses the SET with the smallest-sized keys.

In addition, the DBGenFormat utility allows you to declare a primary key without modifying the DASDL. The Databridge Engine is responsible for passing information about DBGenFormat primary keys to the Databridge Client. The Databridge Client sometimes uses these keys for VIRTUAL data sets or any other types of data sets that do not have a SET that meets the requirements mentioned above. If you have both a qualified SET and a PRIMARY KEY defined in the GenFormat file, the Client uses the PRIMARY KEY.

Note

If a DMSII SET with the NO DUPLICATES ALLOWED attribute exists, we recommend that you use it as the source of the index rather than declaring a DBGenFormat primary key.

When the Databridge Engine uses a DMSII SET as the index for tables derived from the data set, the name of the DMSII SET is stored in the set_name column of the DATASETS Client control table. Alternatively, when the Databridge Engine uses a DBGenFormat primary key as the index for tables derived from the data set, the name "pk_set" is stored in the set_name column.

Using Sets with the KEYCHANGEOK Attribute

Some DMSII SETs have the KEYCHANGEOK attribute, which indicates that it is legal for the value of items that are members of the SET (that is, keys) to change. When the SET being used as the index has the KEYCHANGEOK attribute, this is reflected by bit 4096 (0x1000) in the ds_options columns of the corresponding row in the DATASETS control table. This causes the Client to register the keys it is using with the Databridge Engine, which then compares the keys in the before and after images of an update to determine if the update should be sent to the Client as a MODIFY when the keys are unchanged or as a MODIFY BI/AI pair when a key change occurs. This allows the Client perform the update by deleting the old record and inserting the new one when a key change occurs.

If the Client used a MODIFY when a key change occurred, the update statement would fail and the Client would then recover by doing an insert instead. This would result in the old record and the new record both being present in the database resulting in an incorrect replication.

RSNs and AA Values as Keys

If the Databridge Engine does not find a suitable index, the Client tries to use the RSN (record sequence number) or the AA Value (absolute address) of the records as the key. Both of these items are A-Series words (48-bit quantities). They are passed to the Client as part of the record header. Both use the same entry in the header, and the Databridge Engine informs the Client about what this item represents, as explained below. If the Client decides to use one of these quantities as the key, the set_name column is set to "aa_set" in the DATASETS Client control table. Otherwise, this column is left blank, indicating that there is no set usable as an index.

The Databridge Client can represent AA Values (or RSNs) the following ways:

CHAR(12), where each character is the hexadecimal representation of the correspond digit (half-byte) in the A-Series word. This is the default.
BINARY(6), a binary quantity that uses 48-bits where each byte in the A-Series word is represented by a byte in the relational database. See use_binary_aa.
Using numeric fields to hold the AA Values (or RSNs). In this case the Databridge Client uses an appropriate numeric data type to hold the AA Values (or RSN), mainly, BIGINT for SQL Server and NUMBER(15) for Oracle. See use_decimal_aa.

Note

If a DMSII SET with the NO DUPLICATES ALLOWED attribute exists or the data set has an RSN, we recommend that you use one of these keys rather than declaring a DBGenFormat primary key.

RSNs are unique serial numbers that get assigned to records when they get created and remain associated with the record for the life of the record. You must have DMSII XE to be able to use RSNs. Furthermore, you must explicitly enable RSNs in the DASDL by adding the EXTENDED attribute to the data set. If you explicitly add a column to a data set whose value is the RSN, the Databridge Client will allow you to use this column as an RSN rather than a REAL. In such cases, the Databridge Engine automatically sets the di_options bit DIOPT_Clone_as_RSN in the corresponding DMS_ITEMS table entry to make the Client treat this item (which will be a REAL) as an RSN. See DMS_ITEMS.

AA Values are the absolute address (that is, the file address --- offset within the file --- of the records in the data set). They do not remain constant over time; however, in the following cases, AA_values are required to implement foreign keys to link records in related data sets:

Any data set that contains one or more embedded data sets must always use AA Values as the key. Embedded data sets use Parent_AA Values to implement the link to their parent structures.
When an active data set has links to another data set, the latter must use AA Values as the key.

In both of these cases, the Databridge Engine will use AA Values for the data set in question regardless of whether there is a SET that qualifies for being used as an index, or whether an RSN exists.

Not all data sets have valid AA Values; for example, ORDERED and COMPACT data sets do not have valid AA Values. When AA Values are used as the key, the set_name column of the DATASETS Client control table is set to the name "aa_set". The name "aa_set" causes the RSN or the AA Value to be used as part of the index using a column named my_rsn or my_aa depending on whether this is an RSN or an AA Value.

To find out if a data set has an RSN or a valid AA Value, you need to look at the misc_flags column of the entry for the data set in the DATASETS Client control table. The bit DSFLG_Static_AA (bit mask 64) is used to indicate whether the Client is using an RSN or an AA Value (1 indicates RSN and 0 indicates AA Value). The bit DSFLG_Valid_AA (bit mask 128) is used to indicate whether or not the data set has a valid AA Value (1 indicates a valid AA Value). The Client has no control over the selection of RSNs versus AA Values. This decision is made by the Databridge Engine.

The advantage of using the AA Value to generate a unique key is that it makes updates possible for data sets that could not otherwise be updated; however, this value is not an absolute constant. Any DMSII reorganization (record conversion, file format, or garbage collection) changes these values. You must re-clone a data set that uses AA Values as keys whenever the AA Values change. Therefore, we recommend that you consider creating a unique composite key rather than using AA Values.

The Databridge Client recognizes the names "aa_set", "user_set", and "pk_set" as special names (the use of the underscore is not allowed in DMSII names).

Forcing the Client to Use RSN or AA Values as Keys

You can force the Client to use the RSN or AA Value as the key for a specific data set by setting the ds_options bit, DSOPT_Use_AA_Only (bit mask 16384) in the DATASETS table entry for the data set in question.

Note

You can also do this from the Customize command by using the checkbox Use AA Values (or RSNs) As Keys in the Options section of properties of the data set.

To perform this action globally, use the parameter force_aa_value_onlywith one of the following values. (For more details about this parameter, see force_aa_value_only. Note that this does not have any effect until you run a redefine command (with the -R option) to get the global setting applied to all the data sets.

Value	Description
0	Globally disables the parameter
1	Globally enables the parameter
2	Only applies to data sets that have an RSN; using a SET as the source for the index is always preferable to using AA Values that are volatile.

User Defined Keys in GenFormat

You can create a user-defined SET for a data set by using the PRIMARY KEY construct in GenFormat. When a PRIMARY KEY exists, it is used instead of a SET that would otherwise qualify as the source for the index on the table. To properly identify the source of such an index, the Databridge Client sets the set_name to "pk_set" when it originates from a PRIMARY KEY construct. The Databridge Client recognizes "pk_set" as a special name, just like "aa_set" and "user_set". The only difference between "user_set" and "pk_set" is their origin.

Composite Keys

Composite keys use several columns in a relational data table to form a unique index. The entries you make (via a user script) in the item_key column of the DMS_ITEMS Client control table determine the order in which the columns are used in the key.

Note

If you specify a member of a DMSII GROUP as part of a composite key, you must also set the corresponding item_key column for the GROUP to a value of 1 so that the define (or redefine) command picks it up.

To avoid this step, define the composite key in the DBGenFormat parameter file on the host.

When to Use Composite Keys

We recommend that you create a composite key for data sets that do not have a unique key. Creating a composite key is required for the following data sets:

Data sets that do not have valid RSNs or AA Values, such as COMPACT, ORDERED, and VIRTUAL data sets
Data sets that use AA Values and for which garbage collection reorganizations are frequently performed.
Caution

If the composite key that you create is not unique, the following can occur:
- If a duplicate record is encountered after you clone the data set, the index creation for the resulting table fails. The SQL query we use to eliminate duplicate records will get rid of all copies of the duplicate record.
- If a duplicate record is encountered while attempting to insert a record during an update, the original record is deleted and replaced with the new copy of the record.

When you create a composite key, make sure that you enter the value "user_set" into the set_name column. If you do not, one of two things happens, as follows:

If the set_name value is "aa_set", a column named my_aa, which contains the AA Value of the record is automatically included in the table.
If the set_name value is blank, the program does not create an index, regardless of the values of the item_key column of the various DMS_ITEMS Client control table entries.

Once you are familiar with the concepts in this section, and you determine which data sets require composite keys, you must include the SQL statements in the data set mapping customization script for the data set ( script.user_layout.primary_tablename).

Composite Keys Defined by the User

If the Databridge Engine does not find a suitable SET or DBGenFormat primary key, the Databridge Client allows you to create a composite key. You can also create a composite key when the Databridge Client decides to use AA Values as the primary key.

Note

If the added column is named "my_rsn," this indicates that it is an RSN, which makes an excellent key. Do not use composite keys when this is the case.
You must not create a composite key for a data set that contains embedded data sets or for a data set that has other active data sets linking to it when the handling of DMSII links is enabled.

If a data set does not have a DBGenFormat primary key or a DMSII set that qualifies for use as an index, and the AA Values are not valid, the set_name column in the DATASETS Client control table is left blank. In this case, you can clone the data set, but you cannot track updates.

When the DMSII data set does not have a key, we recommend that you create a composite key using the data set mapping customization script (script.user_layout.primary_tablename). See When to Use Composite Keys for more details about when to use a composite key.

Creating a Composite Key

Modify script.user_layout.primary_tablename to do the following:
- If you don't use the Customize command, set the set_name column of the DATASETS Client control table entry for the data set in question to "user_set". If you use the Client Customizer, this is done automatically.
- Specify which items should be part of the composite key by assigning the appropriate values to the corresponding entries for the item_key column of the DMS_ITEMS Client control table. Such entries are identified by the values of the dms_item_name and the dataset_name columns.
After you create the composite key, do one of the following:
- If you have not cloned any tables, run the define command again.
- If you have cloned tables, set the status_bits column for the corresponding entry in the DATASETS Client control table to 8, and run a redefine command.
If you ran a define command (or if the redefine command prompts you to run a generate command) run the generate command from the working directory that for the data source. Otherwise, you'll be prompted to run the reorg command, which fixes the index for the table.
From the data source's working directory, run a process command. This clones or re-clones the data set, if needed, and resumes tracking.

Adding a Non DMSII Column

Non DMSII columns (also called user columns) are generally used to store the audit file timestamp so that you can keep track of when the data was last updated. You can add non DMSII columns to your relational tables in any of the following ways:

To add a non DMSII column to every data set, set the corresponding bit in the configuration file parameter default_user_columns; this parameter then assigns the appropriate value to the external_columns column of the DATASETS Client control table. The bits in this column determine which non DMSII columns are added to your data table.
To prevent the Client from adding some of the non DMSII columns to secondary tables (for example, DMSII items that have an occurs clause), set the corresponding bit in the configuration file parameter sec_tab_column_mask. This parameter is used in conjunction with the external_columns column in the DATASETS table entry.
To add a non DMSII column to most, but not all, of your data sets, use the script script.user_layout.primary_tablename to set the external_columns column of the DATASETS Client control table back to 0 for the data sets that you want to keep unchanged.
To add a non DMSII column to only a few data sets, do not set the default_user_columns parameter. Instead, use the script script.user_layout.primary_tablename to modify the external_columns column of the DATASETS Client control table for the data sets you want to change.

Types of Non DMSII Columns

The Databridge Client offers several default non DMSII columns (user columns). You can add user columns to the relational database tables either by using user scripts, as described in this section, or by using the Customize command. For more information about the Client Customizer, see the Databridge Administrative Console Help.

Note

The value for the Bit column in this table is equal to the value in the dms_subtype column of the DATAITEMS Client control table. The exception is bit 14, which results in a dms_subtype of 0. Bits are numbered from right to left; the right-most bit is 1.

Bit	Value	User Column Name	Description
1	1	update_type	Database update type, as follows: 0 for extract1 for create 2 for delete (bit 10 must also be enabled) 3 for modify NOTE: This value cannot be used at the same time as bit 11.
2	2	update_time	Time the update was applied to the relational database (PC time)
3	4	update_ts	(SQL Server Clients only) SQL Server timestamp data type. (The timestamp is a data type that exposes automatically-generated unique binary numbers within a database. It is not a true timestamp that contains a date and time value.)
4	8	audit_ts	DMSII audit file timestamp. This column is set to NULL during the initial clone. NOTE: This bit cannot be used at the same time as bit 13.
5	16	audit_filenum	Audit file number NOTE: If you use a decimal number, its precision must be at least 4. Otherwise, the value may be too large and result in a SQL error.
6	32	audit_block	Audit block serial number (ABSN) NOTE: If you use a decimal number, its precision must be at least 10. Do not use a data type of int, as the ABSN is a 32-bit unsigned number. Otherwise, the value may be too large and result in an overflow, which will result in a SQL error.
7	64	source_name	Data source name
8	128	source_id	Data source identifier as defined in the DATASOURCES Client control table
9	256	my_id	SQL SERVER IDENTITY column. Updates have no effect on this number. NOTE: For Windows Clients only: This column won't appear on Clients other than SQL Server, even if requested. The Oracle database provides the equivalent functionality with the ROWID pseudo-column, which is always present.
10	512	deleted_record	Delete indicator (key item). A nonzero value indicates that the record is deleted. This is actually the value of the Client machine’s clock at the time of the deletion. Making this column part of the index allows multiple instances of a deleted record to coexist without being considered duplicate records. This bit cannot be used at the same time as bit 11. These types are compared in Preserving Deleted Records. The granularity of this column is in seconds. If you have applications that perform many delete/insert operations, you may want to add a delete_seqno column to prevent the Client from getting duplicate deleted records. The Client recovers from this by waiting one second and retrying the operation, which can significantly slow the Client's performance.
11	1024	update_type	Expanded database update type as follows: 0 for extract 1 for create 2 for delete 3 for modify If the key for this record is reused, the key is removed when the new, duplicate record is inserted. This value cannot be used at the same time as bit 1 or bit 10. Bits 10 and 11 are compared in Preserving Deleted Records. This bit and bit 1 work in the same way, except that this bit preserves the deleted image.
12	2048	source_id	Data source identifier as defined in the DATASOURCES Client control table (key item)
13	4096	audit_ts	Expanded audit file time. This column contains the DMSII audit file timestamp during updates and the starting time of the data extraction during extraction. NOTE: This bit cannot be used at the same time as bit 4.
14	8192	user_column1	Generic user column whose entry is left as NULL
15	16384	sequence_no	A sequence number used in history tables to determine the order of updates when they have the same update_time values
16	32768	delete_seqno	Augments the `deleted_record` column with a sequence number to provide higher granularity and avoid creating duplicate deleted records.
17	65536	create_time	Time when the record was created in the relational database (PC time).
18	131072	user_column2	Generic user column whose entry is left as NULL.
19	262144	user_column3	Generic user column whose entry is left as NULL.
20	524288	user_column4	Generic user column whose entry is left as NULL.

Values for Non DMSII Columns

The bit numbers, decimal values, and hexadecimal values for the user column names are shown in the following table.

Default Name	Bit Number	Decimal Calue	Hex Value
update_type	1	1	0x00000001
update_time	2	2	0x00000002
update_ts	3	4	0x00000004
audit_ts	4	8	0x00000008
audit_filenum	5	16	0x00000010
audit_block	6	32	0x00000020
source_name	7	64	0x00000040
source_id	8	128	0x00000080
my_id	9	256	0x00000100
deleted_record	10	512	0x00000200
update_type	11	1024	0x00000400
source_id_key	12	2048	0x00000800
audit_ts	13	4096	0x00001000
user_column1	14	8192	0x00002000
update_seqno	15	16384	0x00004000
delete_seqno	16	32768	0x00008000
create_time	17	65536	0x00010000
delete_seqno	18	131072	0x00020000
delete_seqno	19	262144	0x00040000
delete_seqno	20	524288	0x00080000

Setting Up History Tables

The primary data tables use the CREATE, MODIFY, and DELETE records from the mainframe to build an exact duplicate of DMSII data sets.

A history table, on the other hand, treats these records as new records to insert, even though a history table is structured similarly to a primary data table. In effect, the history table becomes a log or record of mainframe changes. History tables are usually enabled as a device to feed data warehouse applications. History tables will continue to grow as Databridge replicates data, so you should purge them regularly after successful updates to the data warehouse.

To enable history tables, set DSOPT_Save_Updates (bit mask 8 of ds_options in the DATASETS Client control table). You must enable history tables before you generate Databridge Client scripts, as explained in the next section. If you want to set this bit for all data sets, you can set the configuration parameter history_tables to 1.

Each history table has the same name as the corresponding primary data table with a "_h" suffix.

It is also possible to create only history tables for a data set or for all data sets. To do this for all data sets, simple set the history_tables parameter to 2 in the configuration file. This will cause the ds_options bit DSOPT_History_Only (8192) to be set for all data sets. If you only want to do this for a few data sets, then you can use the user script script.user_layout.dataset to do this.

Caution

When setting bits in ds_options, beware that some bits may already be set. You should use the "|" operator for SQL Server and the BITOR function for Oracle to set a bit rather than setting the column to that value.

Modifying Non DMSII Column Names

The configuration file parameter external_column[n] allows you to tailor attributes, such as the column name, of individual non DMSII columns. For details and a list of allowable sql_type values, see external_column[n].

Preserving Deleted Records

Both the deleted_record column (bit 10) and the update_type column (bit 11 only) may be used to preserve deleted records, which is useful when trying to recreate updates to the database.

Be aware of the following when using these bits:

Bit 11 preserves only the last instance of the deleted record. For example, if the key value of the deleted record is reused, the deleted record is replaced when the duplicate (new) record is inserted.
Bit 10 results in the deleted_record column being included in the index. The value in this column is a time value, which makes the values in the index unique; therefore, you can keep multiple instances of the deleted record. The granularity of this column is in seconds, if you need coarser granularity you should add the delete_seqno column described in Values for Non DMSII Columns.

In addition, you must clean up deleted images when they are no longer needed.

Note

If you use the first method (bit 11) to preserve deleted records, the deleted records will only survive during a re-clone if you set the preserve_deletes parameter to True. If you use the second method (bit 10), the deleted records will always be preserved during a re-clone.

Generating Databridge Client Scripts

In this phase, the Databridge Client generates script files that are used to create the Databridge data tables in the relational database and run the database bulk loader utility to populate those tables during the data extraction phase.

The generate command creates scripts only for those data sets that have an active column set to 1 in the corresponding entry in the DATASETS Client control table. The Databridge Client keeps track of the data sets that have been generated. These scripts will only be generated again if a define command is executed or if a redefinecommand determines that the layout of a table has changed. If you need to force the Databridge Client to generate the scripts for all data sets that have a corresponding active column value of 1 in the DATASETS Client control table, you can specify the -u option on the command-line for the generate command.

To view the list of scripts that are generated, see Summary of Script Files.

You can also perform this action from the Administrative Console by selecting Actions > Generate Scripts. If you use the Customize command and have a new data source, you will need to perform this step after you exit from the Customize command.

If you plan to use the dbridge.cfg file for signon parameters, set them before you continue. (See the Databridge Installation Guide.)
Make sure that the following parameters, which affect the generate command, are set correctly in the appropriate section of the Client configuration file:

[params]
global_table_suffix
create_table_suffix
create_index_suffix

[bulk_loader]
bcp_batch_size
bcp_packet_size
bcp_code_page
bcp_copied_message
sqlld_rows
sqlld_bindsize
inhibit_direct_mode
enable_parallel_mode
max_errors

Note

For your changes to take effect, you must run the generate command again and specify the -u option to force the program to regenerate the scripts.

Enter the following command:

dbutility [signon_options misc_options] generate datasource

Where	Is
signon_options	For each Databridge Client type, the following command-line options to specify the relational database signon parameters: Oracle: [`-U` userid] [`-P` password] [`-D` database] SQL Server: [`-U` userid] [`-P` password] [-W] [`-O` ODBCdatasource]
misc_options	Any of the following miscellaneous command-line options: `-T` forces the Client to use a new trace file for this run, if tracing is enabled. `-f` filename to specify a configuration file other than the default `dbridge.cfg` file in the working directory. `-L` forces the Client to use a new log file for this run. `-u` unconditionally generates scripts for all tables mapped from data sets that have a corresponding `active` column value of 1 in the DATASETS Client control table. See dbutility Command-Line Options.
datasource	The name that matches the entry in the DATASOURCES Client control table. You can enter the data source name in uppercase or lowercase.

Status messages indicate the progress of the command.

To check on the results of the generatecommand, see Summary of Script Files. For information on when to run generate, see When to Run dbutility generate.

At this point, you are ready to run a process or clone command to create and populate the Databridge tables in the relational database with DMSII data. See Populating the Databridge Data Tables.

Example of Script Files

In this example, scripts are generated for the CUSTOMER data set and the PRODUCTS data set, as follows:

Windows Script Files

> dir /on dbscripts
bcp.customer.fmt(Microsoft SQL Server only)
bcp.products.fmt(Microsoft SQL Server only)
load.customer.cmd
load.products.cmd
script.clrduprecs.customer
script.clrduprecs.products
script.create.customer
script.create.products
script.drop.customer
script.drop.products
script.index.customer
script.index.products
sqlld.customer.ctl(Oracle only)
sqlld.products.ctl(Oracle only)

UNIX Script Files

> ls dbscripts
load.customer.sh
load.products.sh
script.clrduprecs.customer
script.clrduprecs.products
script.create.customer
script.create.products
script.drop.customer
script.drop.products
script.index.customer
script.index.product
sqlld.customer.ctl
sqlld.products.ctl

The script files are stored in the dbscripts subdirectory of the working directory, which is the directory from which you run the dbutility generatecommand.

Summary of Script Files

The generate command produces the following script files:

SQL script files that create data tables and stored procedures to update them in the target relational database (script.create.tablename)
SQL script files that remove selected records from a data table in the SQL*Loader (script.cleanup.tablename). See the table that follows for details about the conditions under which these scripts are generated
SQL script files that remove false duplicate records that can occur during a long clone process of an active DMSII database, if clr_dup_extr_recs is set to True (script.clrduprecs.tablename)
SQL script files that drop data tables from the target relational database (script.drop.tablename)
SQL script files that create indexes for data tables in the target relational database (script.index.tablename)
Windows command (or UNIX shell script) files to run the utility (load.tablename.cmd or load.tablename.sh). The bulk loader is used during the data extraction phase of a cloning operation of a data set.
SQL*Loader control files for Oracle (sqlld.tablename.ctl) and bcp format files for Microsoft SQL Server (bcp.tablename.fmt).

The following table summarizes the scripts that are created for each Oracle table. Each DMSII data set that is cloned is mapped to one or more tables. The Databridge Client creates one set of files for each of these tables that have a corresponding active column value of 1 in the DATATABLES Client control table.

File	Description
SQL Server: `bcp.table.fmt`	This is a control file that contains the bcp parameters that describe the format of the data.
Oracle: `sqlld.table.ctl`	This is a control file that contains the SQL*Loader parameters that describe the format of the data.
Windows: `load.table.cmd` UNIX: `load.table.sh`	This is a Windows command file used to run the relational database bulk loader (bcp for Microsoft SQL Server and SQLLoader for Oracle). This is a UNIX shell script used to run SQLLoader.
`script.create.table`	This is a script that contains SQL statements to create the relational database table named table. It also contains the SQL statements to create the associated stored procedures for updating this table. Before starting the data extraction phase of a `process` or `clone` command, this script is executed to create the table and its associated stored procedures. The following stored procedures are used during the `process` and `clone` commands for updating the table (specified by table): i_table stored procedure for inserting a record d_table stored procedure for deleting a record u_table stored procedure for updating a record z_table stored procedure for deleting all rows for all occurrences of key in secondary tables using a single SQL statement
script.drop.table	This is a script that contains SQL statements to drop the relational database table named table and to drop the stored procedures associated with this table. `script.drop.tablescripts` are used by the process, `clone`, `drop`, and `dropall` commands to drop a specified table and its associated stored procedures. If a table to be cloned (ds_mode=0) already exists during a `process` or `clone` command, this script is executed to drop both the table and its stored procedures before recreating them. During a `process` or `clone` command, if the Databridge Client receives a message from the Databridge Engine indicating that a DMSII data set has been purged, this script is executed to drop the table. Immediately after the table is dropped, the script to recreate the table is executed.
script.cleanup[2].table	This script contains SQL statements to delete selected records from the relational database table. This script is typically called script.cleanup.table, except when both of the conditions below are true. In that case, an additional cleanup script named script.cleanup2.tableis also created to remove all records except the deleted records from the table. NOTE: This script is generated under rare conditions where tables are not fully re-cloned, as in the following cases: The data set is set up to preserve deleted records. The data set is a virtual data set that gets its input from more than one DMSII data set.
`script.index.table`	This is a script that contains SQL statements to create an index for the given table. NOTE: This script is created only when the table has an index.
`script.clrduprecs.table`	This script removes records with false duplicate key values when the bit DSOPT_Clrdup_Recs (32768) is set in the `ds_options` column of the DATASETS table entry for the data set.

When to Run dbutility generate

Run dbutility generate when you need to create a new set of scripts for a data source. For example, you would run dbutility again in the following circumstances:

If you accidentally delete one or more script files, repeat the dbutility generate command with the -u option. Make sure that the current directory is the working directory for the data source where you want dbutility generate to write the script files.
If you disable cloning (set the active column to 0 in the DATASETS Client control table) for one or more data sets prior to running the dbutility generate command, no scripts are created for these data sets. If you later decide that you want one or more of these data sets to be cloned, set the active column back to 1, run the redefine command, and then run the generate command. The missing scripts are created and you can then run the clone command to clone the data set.