Writing and Testing User Scripts

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

Caution: If you have already used the DATABridge Client to clone a database, it is highly recommended that you test your scripts using a test version of the client control tables, not your production version of the client control tables.

Follow these guidelines as you develop your user scripts:

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

To write and test user scripts

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

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

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

  8. Repeat step 3 at this point to view the effect of your data set mapping customization.
  9. 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 of column, inserting a non DMSII item into a tables). See Sample Data Table Customization Scripts.

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

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

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

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