Configure a new table for Datapump

The following section illustrates how to create a table for Datapump. For every table, you can insert, update (edit), or delete records. Every table must use one of two options to datapump data to the key table: a DPfill call or a trigger on a table.

A DPFill is a stored procedure that populates the KeyTable DPFill<tableName>. The call means that all modifications (inserts, updates, and deletes) must also call DPFill<tableName> after the modification. The advantage here is that you can specify non-unique keys, and a single key entry can represent a large number of records. The performance will be better than with a trigger.

A trigger on the table will fire every time a row is edited. It is strongly recommended that only tables that have the ownSysId column are used with triggers. If you choose this option, when you insert a record, the ownSysId must match the system Id, otherwise this could cause a ping-pong effect (that is, data changed will be sent to another system and then sent back to the originating system). If you update a record (that is, change a field), you must update the ownSysId field as well. If you want to delete a record, you need to update the ownSysId before you delete the record.

Note: Before you make your edits, set the ownSysId to the current system (order is especially important when doing a delete).

Before you begin

1. To create a new table for Datapump, the table must have the following:

   • A unique index.

   • Contain a datasetId if you want to replicate based on the individual record's dataset. Otherwise, use a global dataset.

     For example:

     USE [xosapp]

     GO

     CREATE TABLE [dbo].[DKnote](

     [noteId] [int] NOT NULL,

     [time] [dbo].[GMTtime] NOT NULL,

     [offset] [dbo].[GMToffset] NOT NULL,

     [message] varchar(200) NULL,

     [datasetID] int NOT NULL,

     [ownSysId] int NOT NULL)

     GO

     create unique index idTime on DKNote(noteId, time)

     go

2. If any project custom table has an IDENTITY type column, you must grant ALTER permission to the DNA HDBData Editors group. This permission allows Datapump to activate/deactivate IDENTITY type columns, which enables Datapump to send rows to a destination system with the same identity value.

To configure datapump for a new table

Note: The following examples specify the <sql instance>. To determine this value, type select @@servername in Microsoft SQL Server Management Studio.

3. On the desktop, double-click the Windows PowerShell for DNA shortcut.

   Windows PowerShell for DNA opens.

4. Execute the following command:

   set-silo Historical

5. Do one of the following:

   • For a distributed system, execute the following command:

     DPutil-gl -S<service name>

   • For a non-dataset-aware system, execute the following command:

     DPutil-gl -S<sql instance> [-T noDataset]

   This will regenerate the installdp1Config3.sql file. This is the file where the generic datapump configuration tables are defined and the datapump timezone configuration is adjusted to match your system setting.

6. Load the file into Microsoft SQL Server.

7. Configure the Datapump table for the new table. The following example illustrates how by specifying -t, only one table will be datapumped from the xosapp database.

   DPutil -S<sql instance> -d xosapp -t DKnote -g 2

   Creating file <Install Directory>\DNA\Datapump\config\auto\installdp2xosapp..DKnote.sql

   Writing datapump config for DKnote.

8. Configure the datapumpDPSysKeyTable parameters in the installdp2<dbname> scripts. For example:

   datapumpDPSysKeyTable 'xosapp', 'DKnote', -3,

   'noteId', 'time', 'ownSysId', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DPkeyDKnote',

   @priority=2, @quote=1, @separator='\t', @bulkload=0, @catchUp=100

   go

   For more information, refer to the "KeyTable Parameters" table at the end of this procedure.

9. Load into Microsoft SQL Server.

   Note: The DPSystem must already be populated or no entries will be added to the Datapump table.
   
   For information on how to populate DPSystem, refer to Configure the installdp1Config2_DOAS SQL file.

10. Generate KeyTables by executing a similar command to the one in the following example in Windows PowerShell:

    DPutil -S<sql instance> -d xosapp -t DKnote -g 3

    Creating file <Install Directory>\DNA\Datapump\config\auto\installdp3xosapp..DKnote.sql

    Writing DPkeyDKnote definition

11. Load into Microsoft SQL Server.

12. Select one of the following two options:

    1. The record is replicated using DPFill stored procedures. All inserts, updates, and deletes done by an application must also call the DPFill stored procedure in order to insert datapump keys into the key table. If you choose this option, then perform the following steps:

       1. Generate DPFill stored procedures by executing a similar command to the one in the following example in Windows PowerShell:

          DPutil -S<sql instance> -d xosapp -t DKnote -g 4

          Creating file <Install Directory>\DNA\Datapump\config\auto\installdp4xosapp..DKnote.sql

          Writing DPFillDKnote definition

       2. Load into Microsoft SQL Server.

       3. Generate data hopping stored procedures by executing a similar command to the one in the following example in Windows PowerShell:

          DPutil -S<sql instance> -d xosapp -t DKnote -g 5

          Creating file <Install Directory>\DNA\Datapump\config\auto\installdp5xosapp..DKnote.sql

          Writing DPFill10DKnote definition

       Note: If you are replicating global data (that is, no dataset ID specified), then you must explicitly use the -n parameter.

       4. Load into Microsoft SQL Server.

    2. Changes in a table with ownSysId will trigger replication if the ownSysId of the updated record corresponds to one of the systems defined in the DPOwningSystems table. Every modification must set the ownSysId to the system Id of the system making the change. If you choose this option, then perform the following steps:

       5. On the desktop, double-click the Windows PowerShell for DNA shortcut.

          Windows PowerShell for DNA opens.

       6. Execute the following commands:

          set-silo Historical

          DPutil -S<sql instance> -d xosapp -t DKnote -g 6

          Creating file <Install Directory>\DNA\Datapump\config\auto\installdp6xosapp..DKnote.sql

          Writing DKnote Trigger definition

       7. Load into Microsoft SQL Server.

       8. Return to Windows PowerShell.

       9. Execute the following command:

          DPutil -S<sql instance> -d xosapp -t DKnote -g 7

          Creating file <Install Directory>\DNA\Datapump\config\auto\installdp7xosapp..DKnote.sql

          Writing DKnote Delete Trigger definition

       10. If you already had triggers, then merge the Datapump trigger code into the existing table (that is, copy the code into the existing file, change it to a stored procedure with parameters that are key values, and then call it from the existing trigger).

       Note: Make sure that you update the table's definition file and add comments regarding the trigger code.

       11. Load into Microsoft SQL Server.

13. Move the install files you generated to %DNADataRoot%\Historical\config\applicationsql\DataPump\MASTER_dpx\xosappInstall.

14. Create the following two SEQ files by copying a baseline SEQ file from the baseline directory and editing it to load the files created in the previous steps. For example:

    3. xosappSP_Step1.seq:

       ###################################################################

       # xosapp configuration files for Historical 7.5

       #

       [xosapp_ConfigureDatapump] installdp2xosapp..DKnote.sql

    4. xosappDP_Step2.seq:

       Using DPFill Stored Procedures:

       ###################################################################

       # xosapp configuration files for Historical 7.5

       #

       [xosapp_CreateKeytable] installdp3xosapp..DKnote.sql

       [xosapp_CreateKeytable] installdp4xosapp..DKnote.sql

       [xosapp_CreateKeytable] installdp5xosapp..DKnote.sql

       Using Triggers:

       ###################################################################

       # xosapp configuration files for Historical 7.5

       #

       [xosapp_CreateKeytable] installdp3xosapp..DKnote.sql

       [xosapp_CreateKeytable] installdp6xosapp..DKnote.sql

       [xosapp_CreateKeytable] installdp7xosapp..DKnote.sql

15. Edit DPStartup and installdp8DPdirectConfig.sql. This is not required unless you made a separate DPdirect to handle this table. This step depends on how large of a load this table will be.

16. Re-install all Datapump scripts using sqlInstallTool.

    Note: You will need to edit the sqlInstallTool configuration files if you have added a new database. The following procedure will show you how to add the xosapp database file as an example.

    5. Navigate to %DNADataRoot%\Historical\config\applicationsql\DataPump\<SystemName>.ste.

    6. Add in the [<dbname> Pre-DPUTIL] and [<dbname> Post-DPUTIL] entries for your new database. For example:

       ###########################################################################

       # dp2 config

       ###########################################################################

       [CommStats Pre-DPUTIL] MASTER_dpX\CommStatsInstall\CommStatsDP_7.5_Step1.Seq

       [Event Pre-DPUTIL] MASTER_dpX\eventInstall\EventDP_7.5_Step1.Seq

       [Accum Pre-DPUTIL] MASTER_dpX\accumInstall\AccumDP_7.5_Step1.Seq

       [Timeline Pre-DPUTIL] MASTER_dpX\timelineInstall\TimelineDP_Step1.Seq

       [TimeSeries Pre-DPUTIL] MASTER_dpX\TimeSeriesInstall\TimeSeriesDP_Step1.Seq

       [OperatorNotes Pre-DPUTIL] MASTER_dpX\OperatorNotesInstall\OperatorNotesDP_7.5_Step1.Seq

       [xosapp Pre-DPUTIL] MASTER_dpX\xosappInstall\xosappDP_Step1.Seq

       ###########################################################################

       # install dp 3,4,5

       ###########################################################################

       [CommStats Post-DPUTIL] MASTER_dpX\CommStatsInstall\CommStatsDP_7.5_Step2.Seq

       [Event Post-DPUTIL] MASTER_dpX\eventInstall\EventDP_7.5_Step2.Seq

       [Accum Post-DPUTIL] MASTER_dpX\accumInstall\AccumDP_7.5_Step2.Seq

       [Timeline Post-DPUTIL] MASTER_dpX\timelineInstall\TimelineDP_Step2.Seq

       [TimeSeries Post-DPUTIL] MASTER_dpX\TimeSeriesInstall\TimeSeriesDP_Step2.Seq

       [OperatorNotes Post-DPUTIL] MASTER_dpX\OperatorNotesInstall\OperatorNotesDP_7.5_Step2.Seq

       [xosapp Post-DPUTIL] MASTER_dpX\xosappInstall\xosappDP_Step2.Seq

KeyTable Parameters