Stelo Technical Documents

How to Enable SQDR Logging for an Incremental Replication Subscription

Last Update: 9 October 2022
Product: StarQuest Data Replicator
Version: 3.30 or later
Article ID: SQV00DR016

Abstract

If an SQDR Incremental Replication (IR) Group is flagged due to a polling error encountered by a particular IR Subscription, Stelo Technical Support may request that you enable Logging of this particular IR Subscription in order to capture the activity causing the error. An error that might require this type of logging is:

Subscription is active, but has had unexpected errors or row counts for INSERT, UPDATE, or DELETE on the target table

This document explains how to enable logging at the level recommended by Stelo Technical Support, and how to collect the log data after the error condition has been captured. Stelo recommends that you enable logging only when instructed to do so by a Stelo Technical Support Engineer.

NOTE: Much of the information in this document is obsolete, as EnableKeyLog.vbs (for enabling logging for an individual subscription) is no longer supported. Only enabling logging for an incremental group (though the Advanced panel of the group properties) is now supported. If you need to log a particular subscription or group of subscriptions, you can create a "Debug" group and temporarily move the subscriptions of interest to that group.

System Requirements

• This example uses the Windows Script Host, which cannot be disabled or blocked on the SQDR system.

Solution

You can enable keylogging in the Change Data section of the I/R Group Properties Advanced tab; make sure that the name of the stored procedure is SQLOGGER and that Control DBMS is selected.

In addition, you can select the level of logging and restrict logging to a particular subscription with the Windows script, EnableKeyLog.vbs. This script can be found in the \Program Files\StarQuest\SQDR directory. The logging commands should all be executed from a Windows Command prompt. To open a Windows command prompt, click on Start-->Run, enter 'CMD' in the Open text box and click OK. The usage of the EnableKeyLog.vbs script is as follows:

EnableKeyLog.vbs <IRGroupName> <SubscriptionName> [off | low | high]

The low level of logging captures only event notifications from the SQDR Plus Capture Agent that result in flagged subscriptions, such as those which require that a new baseline snapshot be run (e.g. if the source table has been reorganized/dropped/altered or if row count or data errors have occurred). The high logging level captures all data that is replicated from the source table to the destination table plus all the events that are captured with the low level of logging.

To Set Logging Level or to limit logging to a particular subscription:

• Enter the following command to enable logging at a high level, where <IRGroupName> is the name of the IR Group and <SubscriptionName> is the name of the IR Subscription you wish to log:

  cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> <SubscriptionName> high

• Enter the following command to enable logging at a low level, where <IRGroupName> is the name of the IR Group and <SubscriptionName> is the name of the IR Subscription you wish to log:

  cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> <SubscriptionName> low

Note: Use the wildcard "%" character to enable logging for all of the subscriptions in a particular group:

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> % high

To Disable Logging

• Disable logging in the Change Data section on the Advanced tab of the Group properties.
• Also, enter the following command to disable logging, where <IRGroupName> is the name of the IR Group and <SubscriptionName> is the name of the subscription for which activity is being logged:

  cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> <SubscriptionName> off 

Note: Use the wildcard "%" character to disable logging for an entire group:

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> % off 

To View the Current Logging Activity

• Enter the following command to view the subscriptions that are currently being logged:

  cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs"

• You can also use any SQL tool to view the table (ir_keylog); the format is documented in the technical document SQDR ir_keylog Table.

To Collect the Log Data

• The log data is stored in the SQDR control database. You can use any query tool to export the contents of the control database, or create a backup of the control database, to send to StarQuest support.

Managing the Log Data

The ir_keylog table will continue to grow until you turn off logging or delete the subscription. If logging is to be enabled for an extended period of time, we recommend using a scheduled snapshot subscription to manage the log data. The example below uses criteria to copy rows to another table (for example, to the SQL Server tempdb database) and then runs a post-processing step to delete rows older than one day. The destination table is always dropped and recreated for each run (the default behavior for a snapshot subscription).

SQL Server example: Specify the criteria on the insert_ts column and use a date -1 day function to determine which rows should be deleted.

Criteria:
cast(insert_ts as date) < dateadd(day,-1,getdate())

Source post-processing:
delete from [ControlDB].[dbo].[ir_keylog]
where
cast(insert_ts as date) < dateadd(day,-1,getdate()) ;

Db2 LUW example:

Criteria:
cast(insert_ts as DATE) < (current_date - 1 DAYS)

Source post-processing:
delete from SQDR.IR_KEYLOG
where
cast(insert_ts as DATE) < (current_date - 1 DAYS)