StarQuest Technical Documents

SQDR ir_keylog Table

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

Abstract

The ir_keylog control table can be used to drive downstream events instead of using
triggers. This reference document describes the format of the ir_keylog table.

See Using SQDR Key Logging Data and Using SQDR Change Data Processing for additional information.

The ir_keylog file 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.) The file is pruned based upon the number of statistics rows (a service level property.) The table refers to other SQDR Control Tables: subscriptions (identifying the source, destination ODBC DSNs, the source and destination tables), groups - identifying the containing "Group" and the statistics table which summarizes baseline (snapshot) results as well as summary incremental counters.

For a given subscription, the ir_keylog table will contain the details of the row to be inserted, deleted, or updated. The before, 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. The resulting number of rows affected by the operation is identified in the result_row_count column. If an ODBC error is reported, the "result_error" column contains the text message associated with the error condition. In the event that an unexpected number of rows (usually something other than "1") are affected by the operation, a subscription is "flagged". The "flagged_count" tracks the number of operations since the last baseline that resulted in an unexpected row_count_error.

The table has the following columns, where the associated text describes the source of the information (i.e. "SQDR Plus" denotes data supplied by SQDR Plus system, "SQDR" denotes data supplied by the client, etc.):

"COLUMN_NAME"	SQDR: FK: table.column relationships or attribute meaning
"run_id"	SQDR: FK: refers to statistic.run_id
"group_id"	SQDR: FK: refers to groups.id; statistic.group_id; ir_subscription.group_id
"subscription_id"	SQDR: FK: refers to ir_subscription.subscription_id
"resync_state" "transaction_id"	SQDR Plus: An enumerator used by SQDR Plus to order transactions
"change_row_counter" "change_row_sequence"	SQDR Plus: An enumerator used to order rows within a transaction
"change_row_timestamp"	SQDR Plus: Time data was logged on DB2 for i host DBMS (in seconds - host time) (or staged for DB2 for LUW host)
"change_row_nanosecond"	SQDR Plus: Time data was logged on DB2 for i host DBMS (fractional part - in nanoseconds)
"change_row_type"	SQDR Plus: see below
"change_row_log_detail"	SQDR Plus: log entry details from the journal  (if enabled in Staging Agent) in an XML-like format. Fields include job & user name,  IBM i journal code, RRN, and sequence number.  See below for examples.
"change_row_member"	SQDR Plus: member name of the source, when IBM i multi-member support is enabled and a member other than the default (*FIRST) is requested.
"action_taken"	SQDR: see below
"result_row_count"	Destination DBMS: Number of rows affected by action_taken-associated operation as returned by destination
"result_error"	Destination DBMS: Any ODBC error message associated with action_taken on the destination table
"flagged_count"	SQDR: Current count of number of rows flagged for this subscription
"before_key" "before_key_edited"	SQDR: for "Delete" and "Update" operations - used by WHERE CLAUSE. See below for details. "before_key_edited" is obsolete and subject to removal in future versions of SQDR.
"after_key" "after_key_edited"	SQDR: for some Update operations (partitioned subscriptions and open-window updates). See below for details. "after_key_edited" is obsolete and subject to removal in future versions of SQDR.
"keylog_sort"	SQDR: An increasing sequence number within the ir_keylog file
"keylog_ts"	SQDR: ir_keylog datetime of insertion (client platform datetime)
"insert_ts"	SQDR: ir_keylog row insertion timestamp
"after_image"	SQDR Plus: data from the change for updates and inserts. Contains the after-image and may include the delete before image, if available. See below for details.

change_row_type field

Here are the details of the change_row_type field:

opcode
"B"	Delete row - Horizontal Partition
"D"	Delete row
"U"	Update row
"I"	Insert row
"R"	Table reorganized
"S"	Snapshot required
"T"	Truncate table
"A"	Source table altered
"X"	Source table dropped
"C"	Close window signal

action_taken field

The result of the change_row_type is described in the action_taken column. The codes used for action_taken are a subset of the change_row_type. A special value of 0x00 means the change_row_type was ignored. Some change_row_type only change the state of the subscription and do not result in the changes on the destination table and are NULL.

Here are the details of the action_taken field:

action_taken
D	Delete row
M	Update row (Strict Apply Rules)
U	Update row (Allow UPSERT)
I	Insert row
T	Truncate table
S	Snapshot required
A	Source table Altered
X	Source table dropped
C	Close window signal
#	"Stale" Close window token
0x00	change_row_type ignored
?	Unrecognized change_row_type

Example

Here is an example of the change_row_log_detail field (DB2 for i source):

<detail> <receiver>MYLIB QSQJRN0014</receiver> <user>SQDR91</user> <job>QZRCSRVS
QUSER 511037</job> <type>U 70</type> <sequence>00000000000019012714</sequence> <rrn>0</rrn>
<program>RJRNLAPI</program> </detail>

before_key, after_key and after_image fields

This section describes the structure of the variable content columns for the before_key, after_key, and after_image fields.

Elements are encoded as name="value" pairs. Sequences of pairs are separated with a space character ( x'20').

Special character codepoints use an escape sequence consisting of the ampersand sign followed by one of:

• xsi:nil="true" - special value for NULL valued column
• #x (octect binary values, encoded using base 16, hexadecimal characters; these always occur as even number of hexadecimal digits)
• When these characters occur within the "value", a substitution occurs, as follows:

  &: &amp;
  ': &apos;
  ": &quot;
  <: &lt;
  >: &gt;

• Character data that is less than 0x20 (or 0x0020 if UNICODE) is encoded as binary values:

  &#xCC
  &#xUUUU

date, time and timestamps use an ISO format:

timestamp:
"YYYY-MM-DDTHH:MM:SS.fffffffff" (note the use of "T" as seperator between DD and HH). Precision of the timestamp is dependent upon the column characteristics.

date:
"YYYY-MM-DD"

time:
"HH:MM:SS.fffffffff"

Special considerations:

The after_image data is comprised of two delimited strings, using angle brackets to denote the string beginning and ending:

<Dest>"schema"."table"</Dest>
<row>[value-pairs]</row>