StarQuest Technical Documents

Using SQDR Key Logging Data

Last Update: 27 March 2015
Product: StarQuest Data Replicator
Version: SQDR 3.6 or later
Article ID: SQV00DR021

Abstract

SQDR provides a key logging feature that captures the incremental primary key data replicated to the destination database. This data may be helpful to dictate work flow operations. This document describes how to use SQDR to replicate the newly added records in the key logging table to another SQL Server database and present the data in a user-friendly format.

Background

The ir_keylog table is a log of the communication between the host system (running SQDR Plus) and the client system (running SQDR) as it relates to the application of changes to the destination table. For a given subscription, the ir_keylog table will contain the details of the row to be inserted, deleted, or updated.  The before and after key fields illustrate the source ODBC type, the local program data type and destination ODBC types used to effect the transfer, in addition to the string representation of the value(s) used to identify the row. Refer to the SQDR ir_keylog Table technical document for details on the ir_keylog table fields.

Solution

The ir_keylog table is intended to be a "read-only " resource and its data is regularly pruned as the SQDR service processes incremental replication subscriptions. The pruning rate is determined by the SQDR service History Logging settings. Using the technique described in this document, SQDR can replicate the new ir_keylog records to an "archive" table which allows the user to maintain a complete record of key data changes.

This document is intended for users who are familiar with the techniques of creating tables, views and indices, and altering tables on a SQL Server database. All of the SQL Server SQL statements included in the instructions below can be executed in the SQL Server Management Studio New Query window.

Summary

Following are the general steps that you need to perform. Each step is described in more detail in the Detailed Procedures section.

1. Enable key logging for the SQDR incremental replication group or subscription of interest.
2. Create a view of the key log table, which will be replicated by SQDR in lieu of the actual table.
3. To replicate only the new records, create a tracking table on the destination database to store the row identification value of the last record replicated to the destination table and the last record inserted into the source table at the time of replication.
4. Modify the SQDR service properties to allow the source processing to occur even if the replication fails.
5. Create the SQDR subscription with BEFORE and AFTER processing, and with a criteria that limits the result set to be replicated.

Detailed Procedures

These procedures assume the name of the SQDR control database is ControlDB with schema dbo.

1. Enable key logging for the SQDR incremental replication group or subscription of interest.

   Follow the instructions in the StarQuest technical document How to Enable SQDR Logging for an Incremental Replication Subscription to enable key logging for the group or subscription. The logging data will be stored in the SQDR control database table ir_keylog.

2. Create a view of the SQDR control database source table ir_keylog table.

   Create a view of the ir_keylog table that contains only the columns with pertinent data and that joins other control database tables to include the group name, subscription name, source table schema, and source table name in the result set. This ir_keylogv view, instead of the ir_keylog table, will be replicated by SQDR. Execute the following command against the SQDR control database to create the view.

CREATE VIEW ir_keylogv
AS
SELECT g.group_name, s.subscription_name, s.source_owner, s.source_table,
i.resync_state, i.change_row_counter, i.change_row_sequence, i.change_row_timestamp, i.change_row_nanosecond, i.change_row_type, i.action_taken, i.result_row_count, i.result_error,
i.flagged_count, i.before_key, i.before_key_edited, i.after_key,
i.after_key_edited, i.keylog_sort, i.keylog_ts, i.insert_ts
FROM dbo.ir_keylog i JOIN dbo.subscription s
ON i.subscription_id = s.id JOIN dbo.group_subscriptions gs
ON s.id = gs.subscription_id JOIN dbo.groups g
ON gs.group_id = g.id

3. Create a tracking table on the destination database to store row identification values.

1. Create a tracking table subtrack in the destination database. This table should be created with three columns: one as a varchar (128), called subname and two as binary (8) types, called curmax and lastmax.

   CREATE TABLE subtrack
   (subname VARCHAR (128) NOT NULL,
   curmax binary(8) DEFAULT 0,
   lastmax binary(8) DEFAULT 0,
   PRIMARY KEY (subname))

   The subname column will store the name of the SQDR subscription. The curmax column will store the maximum timestamp of the rows in the source table to be replicated to the destination host at the start of the replication. The lastmax column will hold the timestamp value of the last row successfully replicated to the destination host.

2. Create a unique index on the subtrack table.

   CREATE UNIQUE INDEX subtrackix ON subtrack (subname)

3. The row must be first initialized with the subscription name and afterwards the timestamp values will be maintained by the SQDR subscription process. This example assumes that the subscription name will be KEYLOG.

   INSERT INTO subtrack (subname) VALUES ('KEYLOG')

4. Modify the SQDR service properties.
1. In the StarQuest Data Replicator Manager, select the name of the computer that represents the service in the left pane, right-click, and select Properties.
2. Under the Global tab, uncheck the box "Use single transaction for source processing" and click OK.
3. Stop and restart the service by right-clicking the service in the left pane and selecting Stop Service. Restart the service by right-clicking the service and selecting Start Service.
5. Create the SQDR subscription.

1. In the StarQuest Data Replicator Manager, right-click the SQL Server source you want to use and select Insert Subscription.
2. On the Source pane of the Subscription Wizard, select the control database (e.g., ControlDB) and click Refresh. Select the ir_keylogv view and click Next.
3. On the Destination pane, select the destination database and modify the destination object schema and object name as desired. For the Destination Options, choose "Create object when subscription is saved" and "Append replicated rows to existing data". For the Replication Options, select "Insert using ODBC". Click Next until the Processing dialog is reached.
4. On the Processing panel:
1. Enter the following SQL in the SOURCE processing field to be executed BEFORE the replication, modifying [destination_database] and [schema] to use the destination database name and table schema, and changing [control_database] to the name of the source SQDR control database.

   UPDATE [destination_database].[schema].subtrack
   SET curmax = (select convert (binary(8), max(insert_ts))
   FROM [control_database].dbo.ir_keylogv) WHERE subname = 'KEYLOG'

2. Enter the following SQL in the DESTINATION processing field to be executed AFTER the replication, modifying [destination_database] and [schema] to use the destination database name and table schema:

UPDATE [destination_database].[schema].subtrack SET lastmax=curmax WHERE subname='KEYLOG'

3. Click Next until the Schedule dialog is reached.
5. On the Schedule pane, set the subscription to run on a regular interval. Be sure to allow enough time for the replication and the processing to complete before the subscription attempts to run again.
6. Name the subscription with the same name as used for the subname value that was inserted into the subtrack table. In this example, the subscription name is KEYLOG.
7. After the subscription has been created, right-click the subscription and select Run Subscription. This will replicate the complete source table to the destination table and initialize the timestamp values in the subtrack table.
8. After the subscription has successfully completed, double-click on the subscription to open the properties. On the Destination panel, modify to select the "Use existing table" option.
9. On the Criteria pane, enter the following SQL clause changing [destination_database] and [schema] to use the destination database name and table schema:

   WHERE (insert_ts > (
   SELECT lastmax FROM [destination_database].[schema].subtrack where subname = 'KEYLOG'))
   and (insert_ts <= (
   SELECT curmax FROM [destination_database].[schema].subtrack where subname = 'KEYLOG'))

10. Click the Verify button to verify that the SQL statement does not contain any syntax errors. It is normal at this stage for the result set returned using this criteria to be zero.
11. Click OK in the subscription to save the changes.