Skip to main content

Using the Partial Record Policy to fetch unsupported columns

The integrated Partial Record Policy helps you to enable fetching column values from source database tables when the values are partially available or not available in the database transaction log. It allows the adapter to query and fetch supported/unsupported columns from the source database as needed.

The Partial Record Policy is useful when you do not want to capture the column value from the transaction log. You can use the PARTIALRECORDPOLICY property with a list of columns of interest from a specified table list. The Partial Record Policy fetches the column values of interest directly from the database.

You can integrate the Partial Record Policy using TQL with the MS Jet, Oracle Reader, OJet, and MS SQL Reader adapters.

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 which are present as part of 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. PRP 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.

PRP 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’

Sample Partial Record Policy

The following application fetches the columns Image from the 'orders' table.

CREATE OR REPLACE SOURCE APP1 USING Global.MSJet (   
  ConnectionURL:'<ip-address>:<port>',   
  startPosition: 'LSN:<start-position>',   
  Tables: TPCC.orders',   
  PartialRecordPolicy: '{ "Tables": "TPCC.orders (image)"}',  
  username:'test',   
  password:'test',   
  ignorableException:'MissingRowException,MissingColumnException',   
  connectionRetryPolicy: 'timeOut=30, retryInterval=30, maxRetries=5'  ) 
OUTPUT TO SRC_MS_CDC_MSJET_STREAM;
END FLOW AgentFlow;

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

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