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 your cloned data. The resulting data tables are based on information from the Client control tables and any additional customizations you make.

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 menu 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 Customizing with User Scripts, in this chapter.

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:

1. Make sure that the database software and the appropriate Windows services or UNIX processes are running. For more information, see your relational database documentation.

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

3. 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 ] PostgreSQL: [-U userid] [-P password] [-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.

4. (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 the `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.

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.

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 clicking Actions > Define/Redefine.

To define a data source

1. Because the following dbridge.cfg parameters are difficult to change later without redefining and re-cloning, 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
   enable_extended_types (Oracle only)
   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 and PostgreSQL)
   use_binary_aa (SQL Server and Oracle)
   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 (SQL Server and Oracle)
   use_time (SQL Server and PostgreSQL)
   use_varchar
   

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

2. 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] PostgreSQL: [-U userid] [-P password] [-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.

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

• (SQL Server and Oracle) 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".

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.

Types of User Scripts

The Databridge Client supports the following types of user scripts:

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.

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

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

2. Run define to populate the Client control tables.

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

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

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

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

7. Repeat step 3 at this point to view the effect of your data set mapping customization.

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

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

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

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

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

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:

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.

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:

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

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

1. Sets the DIOPT_CollapseGroup (67,108,864) and the DIOPT_Clone_as_Date (2) bits in the di_options column.

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

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

2. Sets the DIOPT_Clone_as_Date (2) bit in di_options.

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

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 and PostgreSQL, which have 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:

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 also use a data type of datetime2 instead of datetime by setting the di_options bit DIOPT_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:

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:

1. Sets the DIOPT_Clone_as_Date (2) and the DIOPT_VarFormat_Date (2048) bits in di_options.

2. Sets the dms_subtype column in DMS_ITEMS to indicate the hexadecimal string, in the same order as the host item layout, as follows:

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.

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) SQL Server and RAW(6) Oracle; 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.

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.

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.

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.

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.

Values for Non DMSII Columns

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

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.

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.

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 a list of the scripts that are generated, see Summary of Script Files.

You can also perform this action from the Administrative Console by clicking 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.

1. If you plan to use the dbridge.cfg file for signon parameters, set them before you continue. (See the Databridge Installation Guide.)

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

3. 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 specify the relational database signon parameters: Oracle: [-U userid] [-P password] [-D database] SQL Server: [-U userid] [-P password] [-W] [-O ODBCdatasource] PostgreSQL: [-U userid] [-P password] [-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.

4. To check on the results of the generatecommand, see Summary of Script Files. For information on when to run generatenext, 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
pgpipe.customer.ctl (PostgreSQL only)
pgpipe.products.ctl (PostgreSQL only)
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
pgpipe.customer.ctl (PostgreSQL only)
pgpipe.products.ctl (PostgreSQL only)
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 (Oracle only)
sqlld.products.ctl (Oracle only)

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.

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.