C Editing Properties for the OracleAS CDC Adapters for Adabas

The OracleAS CDC Adapter for Adabas has several configuration properties. You can edit the properties in Oracle Studio after Setting Up a Change Data Capture with the OracleAS CDC Adapter for Adabas. This chapter describes the configuration properties and how to edit them. It contains the following topics:

Editing Properties in Oracle Studio

After you create the OracleAS CDC solution, you can also edit the properties. The solution create two adapters, the adapter and the CDC Queue adapter. The adapter is created on the server computer and the CDC Queue adapter is created on the staging area computer. For more information, see Setting up a Change Data Capture in Oracle Studio.

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 a description of the properties, see OracleAS CDC Adapter Configuration Properties.

Configuration Properties

This section describes the configuration properties for the OracleAS CDC Adapter for Adabas. You can edit the properties using Oracle Studio. The OracleAS CDC Adapter for Adabas has the following types of properties:

Data Source Properties

The following are the Data Source Properties:

  • dbNumber: The Adabas database number.

  • predictFileNumber: (Predict only) The Predict file number.

  • predictDbNumber: (Predict only) When the Predict file resides in a different database than the data indicate the database number in which the Predict file resides.

CDC Logger Properties

Tracking File name: The name of the mainframe file that is used to register the Adabas archive files. This must be the same name that is defined in the UE2 procedure. For more information, see The Tracking File.

Adabas Version: the version of Adabas you are using. All versions earlier than version 8 are supported.

The OracleAS CDC Adapter for Adabas also supports the standard CDC agent configuration properties. For more information, see Set up the CDC Service.

The Tracking File

The OracleAS CDC Adatpter for Adabas CDC solution uses a tracking file to register the archive files. These files should be created on DASD directly by the UE2 procedure or restored from tapes or cartridges. The tracking file specifies the following information regarding each registered archived PLOG file:

  • The data set name.

  • The time stamp indicating the starting time of each archived PLOG.

  • The Adabas session number.

  • The starting block counter.

For more information, see Setting up the Tracking File.

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.

See the following topics for a description of the configuration properties:

CDC Queue Adapter Properties

The CDC Queue adapter is a data base adapter that is found in the staging area. It is created automatically when Setting up a Change Data Capture in Oracle Studio. It has three properties:

  • connectString

  • defaultDatasource

  • multipleResults

To ensure that the queue adapter works properly with your CDC solution, do not change the values for these properties.

The CDC Queue adapter may have additional properties that can be viewed in the adapter's XML schema. For infomation on how to view the XML, see Adapter Metadata XML.

The following property, which is viewed in the XML only can be edited:

  • maxRecords: The maximum number of records that can be returned.

For information on how to edit XML records in Oracle Studio, see Advanced Tuning of the Metadata.

Common CDC Adapter Properties

The following table describes the common configuration properties for Oracle CDC adapters. The OracleAS CDC Adapter for Adabas uses only these properties.

Table C-1 CDC Adapter Configuration Properties

Parameter Type Default Description

datasource

string

  

The name of the data source for the OracleAS CDC adapter.

routers

    

A list of users who can connect to the adapter and get change events from it for processing. If no routers are specified, any valid user for the workspace can get change events from the Oracle Connect adapter.

To add the list of users in Oracle Studio, expand the router property then right-click users. A new entry called Item(#) is added to the Property column. In the Value column, enter the User Name for this router.

retryInterval

int

2

The polling interval for the database journal When no events are received in the database journal, the adapter waits for the amount of time (in seconds) that is indicated for this property.

getTransactionInfo

boolean

true

When set to true, transaction information (begin, commit, rollback) is returned.

getBeforeImage

boolean

false

When true, before image information is returned.

realTime

boolean

true

When true, this reduces latency in getting change events, however it also increases the polling of the database journal.

The change router asks for N events from the adapter. If the adapter finds fewer than N events in the database journal and realTime is true, these events are immediately returned to the change router. If realTime is false, the adapter polls the journal again after waiting for the number of seconds indicated in the retryInterval.

logLevel

    

The logging level. The following are the available log levels:

  • none

  • api

  • internalCalls

  • info

  • debug

checkTimeoutEveryNEvents

int

100

The number of events that occur before the CDC adapter checks the timeout value. For example, if this property is set to 100, the adapter checks the timeout value after 100 events have taken place. If the amount of time set in the timeout property is past, the adapter times out.

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.

Table C-2 Change Router Configuration Parameters

Parameter Type Default Description

cdcDatasource

string

  

The OracleAS Change Data Source.

eliminateUncommittedChanges

Boolean

false

When set to true, only committed change records are moved to the Change Table. If false, all change records are moved to the change tables (in which case, memory usage is minimal) hence the change table may contain rolled back data. For most adapters, following the RI considerations (see Referential Integrity Considerations) results in rolled-back changes eliminated naturally by compensating change records generated by the adapter in case of a rollback. Consult the respective CDC adapter documentation for details.

eventExpirationHours

int

48

Indicates how long change records are kept in change tables within the staging area. After the indicated time, change records are deleted.

You can set a value between 0 and 50000.

A value of 0 means that the records are never deleted. A value of 1 indicates that the records are kept for one hour.

logLevel

enum

  

The logging level for the router. The following are the available log levels:

  • none

  • api

  • internalCalls

  • info

  • debug.

maxDeletedEventsInBatch

int

500

Controls how many expired change records are delete in a single pass. This number may need to be lowered in some rare cases to reduce latency when a large number of change events is continuously being received.

maxOpenfiles

int

200

Controls the number of physical files opened by the router.

maxTransactionMemory

int (in Kb)

1000

Specifies how much memory can be stored in memory per transaction before it is off-loaded to disk. This number should be higher than the average transaction size so that the slower-than-memory disk is not used too often.

maxStagingMemory

int (in Kb)

100000

Specifies how much memory in total can be used for storing active transactions (ones that have not yet committed or rolled back).

sourceEventQueue

  • server

  • workspace

  • adapter

  • eventWait

  • maxEventsAsBlocks

  • reconnectWait

  • fixedNat

Structure:

string

string

string

int

int

int

boolean

30

250

15

false

Connection information to the OracleAS CDC adapter.

stagingDirectory

string

  

Specifies the directory where the staging area change files are stored. This directory also stores off-loaded transactions, timed-out transactions, and error files.

transactionTimeout

int (in seconds)

3600

Specifies how long can a transaction be active without getting new events. This parameter should be set according to the corresponding setting of the captured database. In particular, this setting must not be lower than the database's transaction time out setting as this may lead to the loss of transactions.

useType

enum

sqlBbased Cdc

This parameter must be set to this value.

routers

    

A list of users who can connect to the change event router and get change events from it for processing. If no routers are specified, any valid Oracle Connect user for the workspace may get change events from the adapter.

To add the list of users in Oracle Studio, expand the router property then right-click users. A new entry called Item(#) is added to the Property column. In the Value column, enter the User Name for this router.

senders

    

A list of users who can connect to the change event router and send change events to it for processing. If no routers are specified, any valid Oracle Connect user for the workspace may get change events from the adapter.

To add the list of users in Oracle Studio, expand the sender property then right-click users. A new entry called Item(#) is added to the Property column. In the Value column, enter the User Name for this router.

subscribeAgentLog

Boolean

False

When set to true, the change router writes the contents of the OracleAS CDC adapter's log in to its own log. Do not set this property to true if the logLevel property is set to debug because the large amount of information that is sent in this mode causes performance delays.

nodeID

string

  

ID for a node when using multi-router mode. Each node represents a router.

alternativeOwnerSeparator

string

..

This is the value of the separator that is used to separate the name of the owner and the suffix table. In Oracle Studio, the default separator for the staging area is an underscore (_). For example, owner.table. When using SSIS to configure a CDC solution, an underscore causes an error. When you change the default separator in the Oracle Studio Preferences window, the new value is entered in this property.

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:

Table C-3 SYNC_POINTS Table Structure

Column Name Data Type Description

application_name

string (64)

The application for which the processing is done.

table_name

string (64)

The name of the synchronization point

context

String (32)

A stream position that can be safely used as an upper bound for event retrieval of all related tables

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 once change record data has been committed to the target database, the stream position should be updated as well.

Access to Change Events

Changes are captured and maintained in a change table. The table contains the original table columns and CDC header columns. The header columns are described in the following table:

Table C-4 Header Columns

Column Name Description

context

The change record stream position in the staging area. The column is defined as primary unique index. It is a 32-bytes string with the following structure:

<yyyymmdd>T<hhmmss>.<nnnn><cccccc>

Where:

  • <yyyymmdd>T<hhmmss> is the commit processing time stamp as generated in the staging area when starting to process the Commit event.

  • <nnnn> is a unique number to differentiate between transactions committed during the same second (up to 99,999 are assumed).

  • <cccccc> is a counter for the change events in the transaction making every stream position unique (up to 9,999,999 are assumed).

operation

This column lists the operations available for the CDC adapter. The available operations are:

  • INSERT

  • DELETE

  • UPDATE

  • BEFOREIMAGE

  • COMMIT

  • ROLLBACK

    Note: All operations for Adabas Mainframe appear as committed in the PLOG. In a rollback, delete appears before commit and no rollback event is generated.

transactionID

The operation's transaction ID.

fullTransactionID

The untruncated full transaction ID of the operation, with all 64 bytes..

tableName

The name of the table where the change was made.

For INSERT, UPDATE, and BEFOREIMAGE operations, the owner name and then the table name are displayed.

For COMMIT and ROLLBACK operations, this value is the same as the OPERATION value.

timestamp

The date and time of the last occurrence in an Adabas block.

sequence

The Adabas input record sequence.

BEFZ

This is an Adabas PLOG header field.

indicator

This is an Adabas PLOG header field.

recordType

The Adabas record type (INCLUDE or EXCLUDE).

userID

The Adabas user ID number.

fileNumber

The Adabas file number.

RABN

The Relative Adabas Block Number.

imageType

The captured image type (BEFORE or AFTER).

workRabChain

This is an Adabas PLOG header field.

The data portion is an exact copy of the back-end table layout.

Each change in the journal is captured as an event with the following format:

<event name='table_name' timestamp='...'>  <table_name>    <header ...></header>    <data ...></data>  </table_name></event>

Transaction Support

The OracleAS CDC Adapter for Adabas supports transactions.

The rollback event is not supported, instead compensating records are supplied.

Security

The user profile for Oracle Connect must have read privileges for the archive files.