StarQuest Technical Documents

Using SQDR Change Data Processing

Last Update: 23 September 2019
Product: StarQuest Data Replicator
Version: SQDR 4.5 or later
Article ID: SQV00DR030

Abstract

The primary purpose of SQDR is incremental replication - replicating changes from a source system to a destination database. However, SQDR can also be configured to invoke a user-supplied stored procedure to process the Change Data information for purposes of auditing or "push" notifications in addition to, or instead of, updating a destination table. The stored procedure can reside either in the SQDR control database or the destination database, and can be used to perform a business process task such as inserting a message into a message system such as RabbitMQ, IBM WebSphere MQ or Microsoft Message Queuing (MSMQ), sending an email, or logging changes in a database table for auditing purposes.

The name and location of the stored procedure is configured in the Group Properties Advanced tab; you can choose whether to receive complete data (with the exception of large LOB fields) or just the keys associated with the change.  If Errors-Only is selected, then data is provided when an error occurs.

Change Data Processing is available in addition to other Apply functions such as maintaining the destination tables with change data.

If wish to perform only Change Data Processing and not maintain a destination table, set the I/R Apply option Only Stream Change Data and the Baseline Replication Option Null synchronization - DDL Only on the destination panel of a subscription. You can also configure the Subscription Wizard Apply Defaults option Only Stream Change Data on the Advanced panel of the I/R Group properties to indicate the default value to use when creating new subscriptions in that group.

The StarQuest-supplied SQL stored procedure SQLOGGER can be used as a model for your own stored procedure. Be sure to use a different name for your custom stored procedure. The source of SQDR.SQLOGGER for DB2 LUW is included below; the source of dbo.SQLOGGER for SQL Server is similar and can be extracted from a SQDR control database with SQL Server Studio.

For another sample stored procedure, see the technical document Change Data Processing: Java example.

SQLOGGER resides in the SQDR control database and is used to populate the ir_keylog control table, which is a log of the communication between the SQDR Plus system and the client system (running SQDR) as it relates to the application of changes to the destination table (DDL and DML). See the technical documents SQDR ir_keylog Table and Using SQDR Key Logging Data for details about the ir_keylog table and the parameters of the stored procedure.

Here are some details about the values passed to the stored procedure:

• Data and Key values are labeled with the name of the column followed by an equal sign.
• Values are always enclosed in double quotes.
• Null-valued columns are omitted.
• If the value contains special characters (double quote, less than, greater than, apostrophe, and ampersand), those characters are "escaped" using XML XQUERY conventions (&quot, &lt, &gt, &apos, and &amp).
• Any character with a hex code of less than 0x20 is "escaped" using the &#xNN format.
• In Timestamp values, the letter "T" is used to delimit date and time i.e "yy-mm-ddThh:mm:ss.fff".

SQDR.SQLOGGER for a DB2 LUW control database:

CREATE OR REPLACE PROCEDURE SQDR.SQLOGGER (
@group_id CHAR (16) FOR BIT DATA,
@subscription_id CHAR (16) FOR BIT DATA,
@resync_state CHAR(1),
@transaction_id CHAR(10) FOR BIT DATA,
@change_row_counter BIGINT,
@change_row_sequence BIGINT,
@change_row_log_detail VARCHAR(512),
@change_row_member VARCHAR(10),
@change_row_timestamp TIMESTAMP,
@change_row_nanosecond INTEGER,
@change_row_type CHAR(1),
@action_taken CHAR(1),
@result_row_count INTEGER,
@flagged_count BIGINT,
@keylog_ts TIMESTAMP,
@result_error CLOB(64000),
@before_key CLOB(64000),
@before_key_edited CLOB(64000),
@after_key CLOB(64000),
@after_key_edited CLOB(64000),
@after_image CLOB(64000)
)

INSERT INTO SQDR.ir_keylog
(
group_id
,subscription_id
,resync_state
,transaction_id
,change_row_counter
,change_row_sequence
,change_row_log_detail
,change_row_member
,change_row_timestamp
,change_row_nanosecond
,change_row_type
,action_taken
,result_row_count
,result_error
,flagged_count
,keylog_ts
,before_key
,before_key_edited
,after_key
,after_key_edited
,after_image)
VALUES
(
@group_id ,
@subscription_id,
@resync_state ,
@transaction_id ,
@change_row_counter ,
@change_row_sequence ,
@change_row_log_detail ,
@change_row_member ,
@change_row_timestamp ,
@change_row_nanosecond ,
@change_row_type ,
@action_taken ,
@result_row_count ,
@result_error ,
@flagged_count ,
@keylog_ts ,
@before_key ,
@before_key_edited ,
@after_key ,
@after_key_edited ,
@after_image
);
.