Generate Clean Up Scripts, instead of Drop Scripts to prevent loss of existing data tables

Technical Note 1.0.10261342.3423648
Created 01-Nov-2001

Applies To
DATABridge Client 4.0

Goal

Generate Clean Up Scripts, instead of Drop Scripts to prevent loss of existing data tables

Cause

When DATABridge writes to data tables that are owned by other users (not recommended) or write to tables where the data comes from more than one DMSII source, drop table scripts are replaced by table clean up scripts. These are automatically generated for DATABridge MiserDB software.

Fix

To manually replace drop table scripts with clean up scripts use the following steps:

1. Alter the data table and add a column named source_id. This would be done by executing the following two SQL statements for every table involved.

ALTER TABLE name ADD source_id TINYINT NULL
UPDATE TABLE name SET source_id = 0

2. Verify the current value in the DATASETS Client Control table column ds_options. Add 1024 (0x0400) to this value to set the DSOPT_MultiSource bit, using script.user_layout.table_name file. For instance, if the ds_options = 0, add the following command to script file:

update DATASETS set ds_options = 1024 where dataset_name = 'name'

3. Verify the current value in the DATASETS Client Control table external_columns. Add 128 (0x0080) to this value to include source_id as a non_DMS column., using script.user_layout.table_name file. For instance, if external_columns = 0 add the following command to the script file:

update DATASETS set external_columns = 128 where dataset_name = 'name'

4. Do the following command manually and also, for future use, add it to the script file "script.user_datasets.data_source".

update DATASOURCES set data_source_id = 1 where data_source = 'data_source'

5. If running the Redefine command, use the /R option. Note: This is a capital "R".

6. The Generate command should create scripts named "script.cleanup.table_name" that will be used in place of "script.drop.table_name". These scripts clean up the data table by only removing the records where the entries that have 1 in the source_id column.