Appendix D: Customization Scripts

This appendix is intended as a quick reference for writing user scripts. For more information about user scripts, see Customizing with User Scripts.

The user scripts described in this Appendix differ significantly from program-generated user scripts (that is, user scripts created by the Create Scriptscommand in the Administrative Console or the dbutility createscriptscommand). Program-generated user scripts set additional bits in the control tables. These bits allow the redefinecommand and the Administrative Console's Customize command to restore changes to the Client control tables. The code in DBClntCfgServer that support the Customize command is fully compatible with the redefinecommand.

If you use the Administrative Console's Customize command and want the ability to restore the Client control tables, you'll need to set some additional fields whenever you make a change.

Customization Rules for Client Configurator

All of the Client control tables except DATASOURCES have a column named xx_user_bmask (where xx is "ds", "di", "dt" or "da", depending on the table where it resides). This column, which parallels xx_options, is used to indicate whether the bits were changed by the user script or by the Client Configurator. Additionally, some of the bits in the xx_options columns are set by the Client or are set by changing an item to a special Client data type, such as a date.

The redefinecommand, when run in the Administrative Console's Customize command mode (use_dbconfig = True), will restore the bits in xx_options that are referenced by xx_user_bmask, while leaving the remaining bits unchanged. Several bits in xx_options that were previously unused are now used to indicate that a specific field in the record was modified by a user script or the Administrative Console's Customize command.

Parameters that affect ds_options

The global parameters that affect ds_options settings are as follows:

 ds_options=BITOR(ds_options,64)

Sample script for setting a ds_options bit in DATASETS

This script sets the ds_options bit DSOPT_Ignore_Dups (32) for the data set SVHIST without changing any of the other bits in the column. We provide both a SQL Server version and Oracle version of this script.

Filename: script.user_layout.svhist:

SQL Server version:

update DATASETS set ds_options = ds_options | 32
where dataset_name = 'SVHIST'

Oracle version:

update DATASETS set ds_options = BITOR(ds_options, 32)
where dataset_name = 'SVHIST'

Changes By Table

DATAITEMS Control Table Changes

Besides the addition of the column da_user_bmask, several da_options bits are used to indicate that a specific field in the record was changed by the Client Configurator or a user script. These new da_options bits are described in the following table.

DATASETS Control Table Changes

Besides the addition of the column ds_user_bmask, some ds_options bits are used to indicate that a specific field in the record was changed by the Client Configurator or a user script. These new ds_options bits are described in the following table.

DATATABLES Control Table Changes

Besides the addition of the column dt_user_bmask, several dt_options bits are used to indicate that a specific field in the record was changed by Client Configurator or a user script. These new dt_optionsbits are described in the following table.

DMS_ITEMS Control Table Changes

Besides the addition of the column di_user_bmask, several di_options bits are used to indicate that a specific field in the record was changed by Client Configurator or a user script. These new di_options bits are described in the following table.

Sample Scripts for Customizing Data Set Mapping

This section is intended as a quick reference for writing data set mapping customization user scripts. Therefore, it lists sample scripts without background explanation. If you are unfamiliar with the Databridge Client, refer to the indicated sections for more information.

Sample Data Set Global Mapping Customization Script

The following example updates the dms_subtype value for every occurrence of the time value TS in the DMSII database whose data source name is CMDB. Create only one of these scripts for each data source.

File name: script.user_datasets.cmdb

update DMS_ITEMS set dms_subtype = 6
where dms_item_name = 'TS'

For more information about the dms_subtype column of the DMS_ITEMS Client control table, see DMS_ITEMS Client Control Table

Sample Data Set Selection Script

This script selects the data sets that we want to clone. Following is a sample user script for a DMSII customer database whose data source name is CMDB. This script turns cloning off (by setting the active column value to 0) for two data sets. We used the data set global customization script rather than the scripts for individual data sets in this example.

File name: script.user_datasets.cmdb

update DATASETS set active = 0
where data_source = 'CMDB'
/***/
update DATASETS set active = 0
where dataset_name = 'EMPLOYEE' and data_source='CMDB'
/***/
update DATASETS set active = 0
where dataset_name = 'CUSTOMER' and data_source='CMDB'
/***/
update DATASETS set active = 0
where dataset_name = 'INVENTORY' and data_source='CMDB'
/***/
update DATASETS set active = 0
where dataset_name = 'BILLING' and data_source='CMDB'

For a complete explanation of specifying data sets for cloning, see Tips for Efficient Cloning.

Selecting DMSII Items

The following script disables the cloning of two DMSII items in the data set named ORDER by setting the value of the active column to 0 in the corresponding DMS_ITEMS table entries.

File name: script.user_layout.order

update DMS_ITEMS set active=0
where dms_item_name = 'SPECIAL-ORDER-DATE' or
      dms_item_name = 'SPECIAL-ORDER-AMOUNT'
      and dataset_name = 'ORDER'

Multiple data sets can contain items with the same name. Adding the data set name to the WHERE clause ensures that you update only the items in question.

For more information, see Tips for Efficient Cloning.

Cloning a Numeric Field as a Date

The following script causes the define command to map a DMSII item of type NUMBER(8) to a relational database date data type where the number contains a date in the mmddyyyy format.

File name: script.user_layout.payments

update DMS_ITEMS set dms_subtype=23,di_options=2
where dms_item_name = 'PAYMENT-DATE' and dataset_name='PAYMENTS'

Cloning an Alpha Field as a Date

The following script causes the define command to map three DMSII items of type ALPHA(10) to a relational database date data type, where those items contain a date in the mm/dd/yyyy format.

File name: script.user_layout.order

update DMS_ITEMS set dms_subtype=53,di_options=2
where dms_item_name = 'ORDER-DATE' or
      dms_item_name = 'DUE-DATE' or
      dms_item_name = 'DATE-SENT'
      and dataset_name = 'ORDER'

Cloning an Alpha or Number Field as a Time

The following script causes the define command to map a DMSII ALPHA or NUMBER time item as a relational database time item.

File name: script.user_layout.payment

update DMS_ITEMS set di_options=256, dms_subtype=3
where dms_item_name='TIME11' and dataset_name = 'BILLING'

Cloning an Alpha or Number Field as a Date/Time

The following script causes the define command to map a DMSII ALPHA or NUMBER date/time item as a relational database date/time item.

File name: script.user_layout.payment

update DMS_ITEMS set di_options=128, dms_subtype=121
where dms_item_name='PAY_REC_TIME' and dataset_name = 'PAYMENTS'

Flattening OCCURS Clause

The following script causes the define command to map an item with an OCCURS clause as a series of columns in the corresponding relational database table instead of mapping each occurrence of the items to a separate column in an OCCURS (secondary) table.

File name: script.user_layout.billing

update DMS_ITEMS set di_options=1
where dms_item_name = 'MONTHLY-BILLS' and dataset_name='BILLING'

For details see Flattening OCCURS Clauses.

Flattening OCCURS Clause for Item Cloned as Dates

The following script directs the define command to map an item with an OCCURS clause as a series of columns, whose data type is a relational database date type, in the corresponding primary table. Furthermore, it specifies that the DMSII item, which is of type NUMBER(8), contains a date in the mmddyyyy format.

File name: script.user_layout.billing

update DMS_ITEMS set di_options=3, dms_subtype=23
where dms_item_name = 'BILLING-DATES' and dataset_name = 'BILLING'

Flattening OCCURS Clause for Three Bit Numeric Flags

MISER systems store certain flags as arrays of single-digit numbers, where each number is used to hold three Boolean values. The Databridge Client can be directed to map these items as a series of Booleans data items (bit in SQL Server). This requires the setting of the DIOPT_Flatten_Occurs bit (1) and the DIOPT_Clone_as_Tribit bit (16) in the di_options column of the corresponding DMS_ITEMS record.

Following is an example for the item L-LOCK-FLAG in the data set LOAN.

File name: script.user_layout.loan

update DMS_ITEMS set active=1, di_options=17, dms_subtype=0
where dataset_name = 'LOAN' and rectype=0 and dms_item_name = 'L-LOCK-FLAG'

In this example, if the L-LOCK_FLAG has an OCCURS 20 TIMES clause, 60 items of type bit named l_lock_flag_01 to l_lock_flag_60 are created.

Splitting an Unsigned Number Item into Two Items

If you have NUMBER(12) items whose first two digits represent an account type and the remaining ten digits represent the account number, you might want to split this item into two columns. You can then rename the two columns as described in Renaming Columns.

In the following scripts, the NUMBER(12) item is named L_APPL_ACCT and is part of the data set LOAN. This item is mapped into two columns, the first of which contains 2 digits while the second one contains 10 digits. When the Client splits an item it appends "x1" and "x2" to the column names it creates to avoid having to deal with duplicate names.

File name: script.user_layout.loan

update DMS_ITEMS set di_options = 1048576, dms_subtype = 2
where dms_item_name = 'L-APPL-ACCT' and dataset_name = 'LOAN'

For SQL Server, this results in columns l_appl_acct_x1(data type tinyint) and l_appl_acct_x2(data type bigint).

You can also make the Client convert the first column to CHAR by setting bit 1024 in di_options to force the data to be stored using a data type of CHAR(2) in the relational database.

File name: script.user_layout.loan

update DMS_ITEMS set di_options = 1049600, dms_subtype = 2
where dms_item_name = 'L-APPL-ACCT' and dataset_name = 'LOAN'

Merging Two Neighboring Items

The following example merges the items SHIPPING-DATE and the item SHIPPING-TIME (which immediately follows it) in the data set SHIPMENTS.

File name: script.user_layout.shipments

update DMS_ITEMS set di_options = 0x1000000
where dms_item_name = 'SHIPPING-DATE' and dataset_name = 'SHIPMENTS'

The Client automatically skips the second item after it performs the merge, so you do not need to set its active column to 0.

Merging a Date and Time to Form a Date/Time

We extend the previous example to map the result to a relational database date/time data type. Assuming that these items have data types of NUMBER(8) and NUMBER(6) respectively in DMSII, we then treat the resulting value as a date/time of the form "yyyymmddhhmiss" (a date format value of 121).

File name: script.user_layout.shipments

update DMS_ITEMS set di_options = 0x1000080, dms_subtype = 121
where dms_item_name = 'SHIPPING-DATE' and dataset_name = 'SHIPMENTS'

The Client automatically skips the second item after it performs the merge, so you do not need to set its active column to 0.

Concatenating Two Items and Cloning the Result as a Date/Time

This script allows you to combine numeric date and time data in non-contiguous columns. 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.

Filename: script.user_layout.dttest:

update DMS_ITEMS
   set dms_concat_num =(select dms_item_number from DMS_ITEMS
                         where dms_item_name='SALE-TIME' and dataset_name='DTTEST'),
      di_options = 0x82,
      dms_subtype = 111
where dms_item_name='SALE-DATE' and dataset_name ='DTTEST'
/***/
update DMS_ITEMS set di_options = 0x80000
where dms_item_name='SALE-TIME' and dataset_name='DTTEST'

This script combines the columns SALE-DATE and SALE-TIME into a column that effectively replaces SALE-TIME and is to be cloned as a long date with a date format of 111. The column sales_time needs to be present in the DATAITEMS control table, as the Client needs to access the DMSII data for the corresponding DMS item when performing the concatenation.

The second SQL statement in the script sets an option bit that tells the Client to map this item to DATAITEMS with its active column set to 0.

Adding a Composite Key to Tables Mapped from a Data Set

The following example inserts a composite key named user_set_shipping_detail into the data set SHIPPING-DETAIL, which does not have a SET defined in DMSII.

File name: script.user_layout.shipping_detail

update DATASETS set set_name='user_set'
where dataset_name = 'SHIPPING-DETAIL'
/***/
update DMS_ITEMS set item_key=1
where dms_item_name = 'SD-PO-NUMBER' and dataset_name = 'SHIPPING-DETAIL'
/***/
update DMS_ITEMS set item_key=2
where dms_item_name = 'SD-LINE-ITEM' and dataset_name = 'SHIPPING-DETAIL'

Specifying How to Handle Alpha Items That Are Too Long

The following script splits the item NOTES in the data set EMPLOYEE into multiple columns rather than truncating it at 4000 characters. The item is declared as ALPHA(4095) in DMSII. This script applies to Oracle.

File name: script.user_layout.employee

update DMS_ITEMS set di_options=4
where dms_item_name = 'NOTES' and dataset_name = 'EMPLOYEE'

Sample Data Table Customization Scripts

This section is intended as a quick reference for writing data table customization user scripts. Therefore, it lists sample scripts without any background explanation. If you are unfamiliar with the Databridge Client, make sure that you refer to the indicated sections for more information.

Sample Data Table Global Customization Script

The following example shows how to use one statement to rename all occurrences of the column name ts to time_stamp in the item_name column of the DATAITEMS Client control table for the DMSII database whose data source name is CMDB. Create only one of these scripts for each data source.

File name: script.user_datatables.cmdb

update DATAITEMS set item_name = 'time_stamp' where item_name = 'ts'

Disabling the Cloning of Secondary Tables

The following script disables the cloning of the secondary table, order_amounts for the data set named ORDER, by setting the active column value to 0 in the corresponding DATATABLES entry. In the case of an OCCURS table, the same result can be achieved by disabling the DMSII item instead. This is much more efficient because it does not create numerous unnecessary entries in DATATABLES and DATAITEMS.

File name: script.user_define.order

update DATATABLES set active=0 where table_name='order_amounts'

For more information, see Tips for Efficient Cloning.

Renaming a Table

Use the DATATABLES Client control table to rename tables in the relational database. The dataset_name column shows the DMSII data set name and the table_name column shows the name of the table as it appears in the relational database. For an explanation of how the DMSII data set and data items are mapped to the relational database, see Relational Database Table and Column Names.

You can change one or more relational database table names before you clone DMSII data sets. If you use the clone command, keep in mind that you must specify the DMSII data set name with the clone command, not the relational database table name. This means that if a DMSII data set is named ORD-YEAR-TOTAL and you rename the equivalent relational database table to total, you must still reference the DMSII data set by its name ORD-YEAR-TOTAL.

When you rename a table, make sure to do the following:

• The new table name must not be used by any other table. After the relational database has been created by the define or redefine command, the Databridge Client does not verify that renamed tables have unique names.

• The table name is no longer than 28 characters. Using table names longer than 28 characters causes SQL syntax errors when the Databridge Client executes the corresponding stored procedures.

Example

The following script changes the name of the table derived from the data set named EMPLOYEE to be full_time_employees. Both the DATATABLES and DATAITEMS Client control tables must be updated as all data items have a column that points back to the table to which they belong.

File name: script.user_define.employee

update DATATABLES set table_name='full_time_employees'
where table_name='employee'
/***/
update DATAITEMS set table_name='full_time_employees'
where table_name='employee'

Renaming Columns

Use the DATAITEMS Client control table to rename the columns that appear in the relational database. The data_item column shows the DMSII data item (column) name and the item_name column shows the name of the column as it will appear in the relational database. For an explanation of how the DMSII data set and data items are mapped to the relational database, see Relational Database Table and Column Names.

You can change one or more column names before or after cloning, as follows:

• If you change the relational database column name immediately after you run a definecommand, continue with the remaining commands. Keep in mind, however, that the DMSII data item retains its original name in the DMSII database. We recommend that you make this change via user scripts during the define and redefine command to ensure that your changes are not lost.

• If you change the column name after you have already cloned a DMSII database, you must mark the table to be re-cloned and then rerun the generate command to create new scripts that contain the new column name.

  Note

  Column names in Oracle are limited to 28 characters. Using a column name longer than 28 characters results in a SQL syntax error when the Databridge Client executes the corresponding stored procedures.

Example

The following script changes the names of two columns in the table derived from the data set named ORDERS.

File name: script.user_define.orders

update DATAITEMS set item_name='order_amount'
where item_name='order_amt' and table_name='orders'
/***/
update DATAITEMS set item_name='order_date'
where item_name='order_dt' and table_name='orders'

Changing SQL Data Types

The following user script changes the sql_type for a packed decimal (sql_type of 11) data item named order_amount to be a floating point number (sql_type of 6).

File name: script.user_define.transaction

update DATAITEMS set sql_type=6
where item_name='order_amount' and table_name='orders'

Cloning a Number as a Character Type

This operation requires that you set the DAOPT_Store_as_Char bit (512) in the da_options column of the corresponding DATAITEMS record. Additionally, you must change the value of the sql_type column to the appropriate character type (such as 1 for char, 2 for varchar, and so on). Finally, in the case of SQL Server, you must also change the value of the sql_length column, as this column has a value of zero for the int and smallint data types. An example for the item l_appl_code in the table loan follows.

File name: script.user_define.loan

update DATAITEMS set sql_type=1, sql_length=2, sql_scale=0, da_options=512
where item_name='l_appl_code' and table_name='loan'

Adding a Non DMSII Column

The following script demonstrates how to add a non DMSII column to a relational database table.

This script adds three non DMSII columns (update_type, audit_ts, and deleteD_record) to the ORDERS data set and preserves all deletes, including multiple deletes with the same key value, since bit column 10 becomes a new key item with a unique value.

File name: script.user_layout.orders

update DATASETS set external_columns = 521 where dataset_name ='orders'