C Editing Properties for the OracleAS CDC Adapter for VSAM

Editing the OracleAS CDC Adapter Properties

To edit the CDC adapter properties, open the Oracle Studio Design perspective and find the binding for the CDC solution on the server computer. Then open the adapter, which contains the name of the CDC solution with the suffix _ag added to it. Changes to adapter properties are reset when the CDC solution is redeployed, therefore these changes must be reapplied following solution deployment. For information on deploying a solution, see Deploying a Change Data Capture.

Do the following to edit the CDC adapter properties.

1. From the Start menu, select, Programs, Oracle, and then select Studio.

2. Expand the Machines folder.

3. Expand the server machine that you created when Setting up a Change Data Capture in Oracle Studio.

4. Expand the Bindings folder, and then expand the binding the name of the CDC solution with the suffix _ag added to it.

5. Expand the Adapter folder.

6. Right-click the adapter the adapter with the name of the solution and the suffix _ag and select Open.

   The adapter configuration editor opens in the editor, which displays the properties for the adapter.

7. Edit the adapter parameters as required.

   For more information, see OracleAS CDC Adapter Configuration Properties for a description of the properties.

CDC Logger Properties

LoggerName: the name of the MVS logstream used for the data capture.

CDC$PARM Properties (VSAM Batch Only)

CDC$PARM is the name of DD card that defines a QSAM data set or PDS member that contains the parameters for the JRNAD exit and Logical Transaction Management. For more information on the creation and syntax, see Configuring the Logger (VSAM Batch Only).

OracleAS CDC Adapter Configuration Properties

This section describes the common configuration properties for Oracle CDC adapters and the change router configuration properties, which is located on the staging area computer.

To edit the CDC adapter properties, open the Oracle Studio Design perspective and find the binding for the CDC solution you created. The binding contains the name of the CDC solution with the suffix _ag added to it. Open the adapter with the name of the solution and the suffix _ag to edit the properties. For information on how to edit adapter properties in Oracle Studio, see Editing the OracleAS CDC Adapter Properties. Changes to adapter properties are reset when the CDC solution is redeployed, therefore these changes must be reapplied following solution deployment.

For a description of the configuration properties see the following topics :

• CDC Queue Adapter Properties

• Common CDC Adapter Properties

Change Router Properties

The following table describes the SQL-based change event router configuration parameters.

To edit the router properties, open the Oracle Studio Design perspective and find the binding for the CDC solution you created. The binding contains the name of the CDC solution with the suffix _router added to it. Open the adapter with the name of the solution and the suffix _router to edit the properties. For information on how to edit adapter properties in Oracle Studio, see Editing the OracleAS CDC Adapter Properties.

Referential Integrity Considerations

Some related tables have referential integrety (RI) constraints enforced on them. For example, with OrderHeader and OrderLines one cannot have OrderLines without an associated OrderHeader.

When processing change events by the table (which is how an OracleAS CDC works) as opposed to by transaction, referential integrity cannot be maintained properly. For example, when first handling all OrderHeader records and then all OrderLines records then a deleted OrderHeader may be applied long before the required delete of the associated OrderLines records.

In order to reduce the potential referential integrity to a known time frame after which referential integrity is restored, a somewhat different process is needed (compared with Reading the Change Tables').

A special SYNC_POINTS table should be added to maintain a common sync-point for use with multiple related tables. The table is defined as follows:

This table's primary unique key is the concatenation of application_name + sync_name. The use of this table is not mandatory but it is part of the recommended use pattern of SQL-based CDC.

The SYNC_POINTS table is created with the following definition (where filename is changed into an actual path):

<?xml version='1.0' encoding='UTF-8'?><navobj>  <table name='SYNC_POINTS'          fileName='<staging-directory-path>SYNC_POINTS' organization='index'>    <fields>      <field name='application_name' datatype='string' size='64'/>      <field name='sync_name' datatype='string' size='64'/>      <field name='context' datatype='string' size='32'/>    </fields>    <keys>      <key name='Key0' size='128' unique='true'>        <segments>          <segment name='application_name'/>          <segment name='sync_name'/>        </segments>      </key>    </keys>  </table></navobj>

The following procedure describes how to ensure RI is regained after a group of ETL rounds. It is an extension of the procedure described earlier for consuming change records. Here we assume that tables T1, T2 and T3 are related with RI constraints and that A is the application we are working under.

To create a stream position

1. This is a one-time setup step aimed to create a stream position record for T [1/2/3] + A in the STREAM_POSITIONS table. The following SQL statement creates that record:

   insert into STREAM_POSITIONS values ('A', 'T1', '');insert into STREAM_POSITIONS values ('A', 'T2', '');insert into STREAM_POSITIONS values ('A', 'T3', '');
   

2. This step is performed at the beginning of a group of ETL rounds processing (that is before starting to process change events for T1, T2 and T3). The goal here is to get a shared sync point for retrieval of T1, T2 and T3. This is done by sampling the 'context' column of the SERVICE_CONTEXT table. This value is the stream position of the last change record in the most recently committed transaction. This is done as follows:

   insert into SYNC_POINTS     select 'A' application_name, 'T123' sync_name, context from SERVICE_CONTEXT;
   

   Here, T123 is the name chosen for the synchronization [points of tables T1, T2, and T3.

3. This step is where change data is actually read. It occurs on each ETL round.

   select n.* from T t, STREAM_POSITIONS sp, SYNC_POINTS sy where    sp.application_name = 'A' and    sp.table_name = 'T' and    sy.application_name = sp.application_name and    sy.sync_name = 'T123' and    n.context > sp.context and n.context <= sy.context order by n.context;
   

   Note that “n.context <= sy.context” is used because the context represents a change record to be processed and processing should include the change record associated with sy.context, too.

   This query retrieves change records starting from just after the last handled change record but stopping at a common sync point. “n.*” can be replaced with an explicit list of columns, however it is important that the 'context' column must be selected as this is the change record stream position which is required for the next step.

   This step occurs after each ETL round when all change records were retrieved and processed for a table Ti. Let's assume that the value of the 'context' column of the last change record was 'C'. This value must be stored back into the STREAM_POSITION table for the next ETL round. This is done with:

   update STREAM_POSITIONS set context='C' where application_name 'A' and table_name = 'Ti';
   

   This value can be stored more frequently during the ETL process as needed. The general guideline is that when change record data has been committed to the target database, the stream position should be updated as well.

Single Program Transaction Manager

By default, changes that are made by a single program (PGM) are designated as a transaction. In this case, the OracleAS CDC Adapter for VSAM BAtch hook sets its own LE termination routine to get the program severity and user return codes.

The return codes determine whether the transaction is terminated with COMMIT or ROLLBACK. By default, any severity code less than 2 and any return code less than or equal to 4 (that is a warning return code or a successful return code) result in COMMIT. All other values result in ROLLBACK. Use the CDC agent properties commitMaxTerminationSeverityCode and commitMaxTerminationUserCode to adjust the default behavior.

Logical Transaction Manager

It is common practice to set up nightly batch jobs to update VSAM clusters to ensure consistency. This is done by maintaining a copy of the VSAM data before the job is run and restoring the previous copy if the batch job is terminated abnormally for any reason. This practice can be viewed as an implementation of a logical transaction that ensures that an entire batch job runs as a unit of work.

With a OracleAS CDC Adapter for VSAM Batch solution, it is important to maintain the same work unit. In this case, the changes should not be delivered to the client application until the entire logical transaction successfully completes. Failure to maintain such a work unit may result in inconsistencies between the VSAM data, that was restored to the original version, and the change consumer.

The ATYLTRAN program, which is supplied with the OracleAS CDC Adapter for VSAM provides complete control over the transactional boundaries of captured changes. ATYLTRAN should be called as a separate STEP when:

• The logical transaction is started

• The logical transaction is terminated using both COMMIT and ROLLBACK

• The logical transaction is delayed (and should be continued later in another JOB)

• The logical transaction continued in another JOB

ATYLTRAN receives the following parameters:

• The logical transaction operation:

  BEGIN: Indicates a new logical transaction. If a logical transaction with the same name exists, the old transaction is terminated using ROLLBACK.

  COMMIT

  ROLLBACK: Terminates the logical transaction

  CONTINUE: The default. Use this after each JOB that does not terminate the current logical transaction, and at the beginning of each JOB that continues a logical transaction initiated by another JOB.

• The logical transaction name:

  By default, the BEGIN operation initiates single job logical transaction with the same name as the JOB. If the logical transaction is continued to another JOB, or the transaction name is changed, the transaction name must be provided explicitly with the BEGIN operation. The same name should be provided with the CONTINUE operation at the beginning of the other JOB to continue the transaction.

  The transaction name can be up to 15 characters long.