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 relational database (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 relational database bulk loader 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 relational database 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

Oracle:
sqlld.table.ctl

This is a format/control file that contains the bulk loader parameters that describe the format of the data.

There is no equivalent file for DB2. The DATABridge Client relies on the order of the data delimited by the bcp_delim parameter to match the table. See Microsoft SQL Server bcp.

Windows:
load.table.cmd

UNIX:
load.table.sh

This is either a Windows command file or a UNIX shell script to run the relational database bulk loader (bcp for Microsoft SQL Server and SQL*Loader for Oracle).

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

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.table scripts 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.table is 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 recloned, 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 ds_options column of the DATASETS table entry for the data set.