Using the Partial Record Policy with CDC Readers
Partial Record Policy enables Striim CDC applications to retrieve column values from the source database when those values are not fully available in the database transaction log. For example, Partial Record Policy can be used to populate XML, LOB, or other columns that are not fully captured in change data events. The implementation of Partial Record Policy varies by source: for Oracle and Microsoft SQL Server CDC readers, it is configured as a property on the CDC source adapter; for IBM Db2 for z/OS CDC via RDRS, it is configured using an OpenProcessor component in the application flow. In all cases, Partial Record Policy performs additional queries to retrieve the required values and adds them to the output event (WAEvent).
Supported readers and Partial Record Policy modes
Partial Record Policy is supported with the following CDC readers:
Oracle CDC readers — using Integrated Partial Record Policy
Microsoft SQL Server CDC readers — using Integrated Partial Record Policy
IBM Db2 for z/OS CDC via RDRS — using OpenProcessor-based Partial Record Policy
See below for details on how to configure Partial Record Policy for each type of reader.
Configuring Partial Record Policy for Db2 for z/OS
For Db2 for z/OS, Partial Record Policy is configured using an OpenProcessor component. The CDC source uses RDRS, and the OpenProcessor component is added to the Striim application between the CDC source and the target. The OpenProcessor establishes a separate connection to the source database to retrieve values for columns such as XML and LOB types that are not fully captured in the transaction log.
The following example shows how to configure Partial Record Policy for Db2 for z/OS:
CREATE OR REPLACE OPEN PROCESSOR db2_prp_openprocessor USING Global.PartialRecordPolicy (
DatabaseType: 'Db2',
ConnectionURL: 'jdbc:db2://<hostname>:5045/<dbname>',
Username: '<Username>',
Password: '<Password>',
Password_encrypted: 'true',
connectionRetryPolicy: 'retryInterval=30,maxRetries=3',
Tables: 'EXAMPLE_SCHEMA.example_table_one(config); EXAMPLE_SCHEMA.example_table_two(config)',
PartialRecordPolicy: '{ "Tables" : "EXAMPLE_SCHEMA.example_audit_table(config);" }' )
INSERT INTO db2xmlstream
FROM example_cdc_stream;
Supported operations: INSERT and UPDATE. Partial Record Policy for Db2 for z/OS ignores DELETE and DDL operations.
Configuring Partial Record Policy for Oracle and SQL Server
For Oracle CDC readers and Microsoft SQL Server CDC readers, Partial Record Policy is configured by adding the PARTIALRECORDPOLICY property to the CDC source adapter. The adapter uses this property to retrieve column values from the source database.
The following example shows how to configure Partial Record Policy in a Microsoft SQL Server CDC source adapter:
CREATE OR REPLACE SOURCE ExampleApp USING Global.MSJet (
ConnectionURL: '<ip-address>:<port>',
startPosition: 'LSN:<start-position>',
Tables: 'EXAMPLE_SCHEMA.orders',
PartialRecordPolicy: '{ "Tables": "EXAMPLE_SCHEMA.orders (image)" }',
Username: '<Username>',
Password: '<Password>',
IgnorableException: 'MissingRowException,MissingColumnException',
ConnectionRetryPolicy: 'timeOut=30, retryInterval=30, maxRetries=5' )
OUTPUT TO ExampleCDCStream;
END FLOW ExampleFlow;
Supported features
The Partial Record Policy supports the following features.
PARTIALRECORDPOLICY property
You can use the PARTIALRECORDPOLICY property to fetch specific column types of tables when those columns are not fully available in the transaction log.. The PARTIALRECORDPOLICY property accepts a JSON string containing a table name, and a column name or a comma-separated list of column names to be fetched from the database.
PARTIALRECORDPOLICY : '{ "Tables" : "TPCC.Orders (orderid, image);" }'In the above example, the policy fetches the latest values of the columns "orderid" and "image" from the database.
You can add the PARTIALRECORDPOLICY property to one or more tables contained in the Tables property.
PARTIALRECORDPOLICY : '{ "Tables" : "TPCC.Orders (orderid, image);TPCC.Invoice (invoice_doc) " }' The Partial Record Policy does not distinguish between supported or unsupported columns for the specific CDC readers. It fetches the latest value of the column specified in the PARTIALRECORDPOLICY from the database. This helps when you want to read the specific column directly from the database not from the transaction log.
If the specified column is already present in the transaction log supported by the reader and the user specified the column in the PARTIALRECORDPOLICY property, the Partial Record Policy will overwrite the value of the column with the latest value fetched from the database.
LookupUsing parameter
You can use the LookupUsing parameter to fetch the record from the flashback or from the latest snapshot of the table.
MS SQL Server
The PKEY should be used if you need to fetch the record based on the primary key. If you do not specify a LookupUsing parameter, the Partial Record Policy automatically takes the PKEY into account. This is the default setting for MS SQL Server.
For example:
PARTIALRECORDPOLICY : '{"Tables":"TPCC.Orders (orderid, image);", "LookupUsing" : "PKEY"}'
or
PARTIALRECORDPOLICY : '{"Tables":"TPCC.Orders (orderid, image);"}'Oracle
The SCN should be used if Flashback is enabled in the Oracle database. The Partial Record Policy fetches the record from the flashback database based on the SCN value. It uses the "AS OF SCN'' clause in the SELECT query created with the primary key column in the WHERE clause. This is the default behavior of the Partial Record Policy for Oracle.
If no SCN is found for the table in the flashback database because the retention period has expired, the Partial Record Policy automatically switches to PKEY and fetches the record using the primary key. If you do not specify a LookupUsing parameter, the Partial Record Policy automatically takes the SCN into account.
For example:
PARTIALRECORDPOLICY : '{"Tables":"TPCC.Orders (orderid, image);", "LookupUsing" : "SCN"}'
or PARTIALRECORDPOLICY : '{"Tables":"TPCC.Orders (orderid, image);"}'It may happen that Oracle Flashback is not configured. Then you can use PKEY with LookupUsing to fetch the column value based on the primary key.
For example:
PARTIALRECORDPOLICY : '{"Tables":"TPCC.Orders (orderid, image);", "LookupUsing" : "PKEY"}'Authentication
The Partial Record Policy uses the same connection information specified as part of the connection to connect to a database. No separate property is required to connect to the database. The existing connection properties used are the ConnectionURL, UserName, Password and ConnectionRetryPolicy.
The Partial Record Policy creates a new named database connection to fetch records from the desired table. The Partial Record Policy establishes its own connection for concurrent access to the database. This is necessary because the Partial Record Policy executes its own set of queries to fetch and process the column value. Each connection is assigned its own set of resources; the Partial Record Policy does not use the existing connection established by the reader.
Connection retry
The Partial Record Policy creates a named connection to the database. There is a possibility that only the Partial Record Policy connection drops due to an internal or external issue. There is also a possibility that the connection will be terminated by the database administration. To avoid an immediate crash or halt, the Partial Record Policy will attempt to re-establish the connection depending on the retry policy you specify as part of the adapter configuration. If no retry policy is specified, the default retry policy adopted by the adapter is used to retry the connection.
In case of a connection failure, it follows the retry policy rules to halt the application. The connection retry policy determines the behavior of the Partial Record Policy when a connection drop is encountered. It uses maxRetries, retryInterval and connection timeout.
If the connection drops, the policy automatically reconnects to the database based on the retry policy that you specify.
Column and row validations
The Partial Record Policy ignores columns that occur more than once in the comma-separated list. In the following example, it fetches the orderid and image columns, but ignores the other orderid column. For example:
PARTIALRECORDPOLICY : "{ Tables : ‘TPCC.Orders (orderid, image, orderid);"The Partial Record Policy does not check the PK columns of the table of interest from the database. It retrieves the PK column names from the WAevent and uses them in the WHERE clause to construct the SELECT query.
If the WAevent does not contain PK, it issues an error and HALT the application. The Partial Record Policy uses the key column value of the table. If the table does not have any key column, use the KEYCOLUMNS property for the table to be able to use the Partial Record Policy.
If you use the KEYCOLUMNS property and a specific column was dropped in the latest snapshot of the table, the Partial Record Policy causes a halt and issues a “MissingPrimaryKeyException” message to ignore the PK column with the IgnorableException property of the adapter.
In the case of an IgnorableException used for the MissingPrimaryKeyException, the Partial Record Policy does not use the same PK in the WHERE clause. If the WHERE clause becomes empty, PRP issues a warning and ignores the WAevent. In such a situation, the WAevent may not contain the latest column value present in the database table.
There is a possibility that the PK constraint drop in the latest snapshot of the table, the Partial Record Policy fetch query may return more than one row. Partial Record Policy causes a halt and issues a 'TooManyRowsException' message to ignore the error with the IgnorableException property. After ignoring the exception, the column value from the first row of the fetch result is taken into account.
Partial Record Policy initiates a halt if the interested column is a PK column. This scenario cannot be ignored.
It updates the column value only for the operations INSERT and UPDATE.
Special characters
The Partial Record Policy supports special characters (without wildcard) in schema, container, table and the respective column names. It follows the rules of the source adapter. If the source adapter supports special characters in any of the objects, it also supports those characters.
Wildcard scenarios
The Partial Record Policy requires explicit table or column specification in case of wildcard scenarios. It does not support wildcard characters in the PARTIALRECORDPOLICY property for the columns of interest. It immediately initiates the halt with an error message.
Handling failures
Failures are handled as follows:
If a column of interest is not present in the latest snapshot of the table during execution, the SELECT query fails. The Partial Record Policy initiates a halt with the message MissingColumnException. The exception can be ignored with the "IgnorableException" property.
For example:
IgnorableException: ‘MissingColumnException’
If the row is not located because it was deleted between the time the change was created and when the fetch was triggered, this results in a MissingRowException. The exception can be ignored with the “IgnorableException” property.
For example:
IgnorableException: ‘MissingRowException’
If the PK column does not exist in the latest snapshot of the table, the Partial Record Policy initiates a halt with a MissingPrimaryKeyException. The exception can be ignored with the "IgnorableException" property.
For example:
IgnorableException: ‘MissingPrimaryKeyException (oldOrderId)’
If the fetch query returns more than one row, the Partial Record Policy initiates a halt with a TooManyRowsException. The exception can be ignored with the "IgnorableException" property.
For example:
IgnorableException: ‘TooManyRowsException’
The combination of "MissingColumnException", "MissingRowException", "TooManyRowsException" and "MissingPrimaryKeyException" can be applied for all the tables.
For example:
IgnorableException: ‘MissingColumnException, MissingRowException, TooManyRowsException, MissingPrimaryKeyException’
Supported database versions
Database name and version | Initial release version |
|---|---|
Oracle Database 11g Release 2 | 11.2.0.4 |
Oracle Database 12c Release 1 | 12.1.0.1 |
Oracle Database 12c Release 2 | 12.2.0.1 |
Oracle Database 18c | 18.1.0 |
Oracle Database 19c | 19.1.0 |
Oracle Database 21c | 21.0 |
MS SQL Server | 2008 and later |
IBM Db2 for z/OS | 12.0 |
Supported data types
Adapter name | Data type |
|---|---|
OJet | ROWID, VirtualColumn, SDO_GEOMETRY |
Oracle Reader | XML, VirtualColumn, SDO_GEOMETRY |
MS SQL Reader | SQLVariant, Computed Column, XML, GEOMETRY, HIERARCHYID, GEOGRAPHY |
MS Jet | SQLVariant, Computed Column, XML, GEOMETRY, HIERARCHYID, GEOGRAPHY |
OpenProcessor for Db2 for z/OS | XML, LOB |