The OracleAS CDC Adapter for IMS/DB 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 IMS/DB. This chapter describes the configuration properties and how to edit them. It contains the following sections:
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.
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.
From the Start menu, select, Programs, Oracle, and then select Studio.
Expand the Machines folder.
Expand the server machine that you created when Setting up a Change Data Capture in Oracle Studio.
Expand the Bindings folder, and then expand the binding the name of the CDC solution with the suffix _ag added to it.
Expand the Adapter folder.
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.
Edit the adapter parameters as required.
For more information, see OracleAS CDC Adapter Configuration Properties for a description of the properties.
This section describes the configuration properties for the IMS/DB CDC adapter. You can edit the properties using Oracle Studio. The IMS/DB CDC has the following types of properties:
Logger Name: the name of the MVS logstream used for the data capture.
CDC$PARM is the name of DD card that defines a QSAM data set or PDS member that contains the parameters for a DFSFLGX0 user exit. For an explanation on how to create this and its syntax, see Creating and Configuring the CDC$PARM Data Set. The following list describes the CDC$PARM properties:
BUFFER_NUM: The logstream buffer number. The valid values are Default-30.
BUFFER_SIZE: The logstream buffer size. The valid values are Default-22550 bytes.
DEBUG: If this is ON the debug information is printed using WTO. The default value is OFF.
LOGSTREAM: The logstream name. The default value is ORACLE.IMS.DCAPDATA.
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 Properties in Oracle Studio. 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:
The adapter properties are configured if you want the CHECKPOINT to be sent to IMS/TM instance. If the checkpoint is not configured, the changes may be captured by DFSFLGX0 exit with a delay, if the IMS/TM Control Region executes a small amount of updates. For information on how to edit these properties, see Editing the OracleAS CDC Adapter Properties.
There are also additional properties that are common to all Oracle CDC adapters. For a description of the common Oracle CDC adapter properties, see Common CDC Adapter Properties. The following is a list of the adapter properties.
envImsBatch: Set to false to execute the CHECKPOINT command. The default value is true.
checkPointFrequency: The frequency for issuing checkpoints. The default value is 60 (seconds). The smallest time frequency supported is 10 seconds.
consoleCheckPoint: Set to true to use and extended MCS console. If false a reply to WTOR is used. The default value is true.
imsJobName: IMS job name for the IMS/TM for which the WTOR reply to the message DFS996I should be sent. This must be provided if be provided if multiple IMS/TM instances run on a Z/OS computer.
consoleCheckPointCommand: The command that should be sent to the MCS console. The default value is "/CHE."
returnLastContextOnIdle: If true, the precise computer time stamp is set as the last context if no relevant updates have occurred.
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 that can be viewed in the adapter's editor in Oracle Studio:
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 Editing XML Files in Oracle Studio.
The following table describes the common configuration properties for Oracle CDC adapters. For information on specific CDC adapter properties for the OracleAS CDC Adapter for IMS/DB, see OracleAS CDC Adapter for IMS/DB Properties.
Table C-1 CDC Adapter Configuration Properties
| Parameter | Type | Default | Description |
|---|---|---|---|
|
|
string |
The name of the data source for the OracleAS CDC Adapter . |
|
|
|
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 |
||
|
|
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. |
|
|
boolean |
|
When set to |
|
|
boolean |
|
When |
|
|
boolean |
|
When The change router asks for N events from the adapter. If the adapter finds fewer than N events in the database journal and |
|
|
The logging level. The following are the available log levels:
|
||
|
|
int |
|
The number of events that occur before the CDC adapter checks the |
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 Properties in Oracle Studio.
Table C-2 Change Router Configuration Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
|
|
string |
The OracleAS Change Data Source. |
|
|
|
Boolean |
false |
When set to |
|
|
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 A value of |
|
|
enum |
The logging level for the router. The following are the available log levels:
|
|
|
|
int |
|
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. |
|
|
int |
|
Controls the number of physical files opened by the router. |
|
|
int (in Kb) |
|
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. |
|
|
int (in Kb) |
|
Specifies how much memory in total can be used for storing active transactions (ones that have not yet committed or rolled back). |
|
|
Structure: string string string int int int boolean |
|
Connection information to the OracleAS CDC Adapter . |
|
|
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. |
|
|
|
int (in seconds) |
|
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. |
|
|
enum |
sqlBbased Cdc |
This parameter must be set to this value. |
|
|
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 |
||
|
|
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 |
||
|
|
Boolean |
False |
When set to |
|
|
string |
ID for a node when using multi-router mode. Each node represents a router. |
|
|
|
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. |
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.
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', '');
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.
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.
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:
| 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:
|
|
operation |
This column lists the operations available for the CDC adapter. The available operations are:
|
|
transactionID |
The operation's transaction ID. |
|
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 |
|
timestamp |
The date and time of the occurrence. |
The OracleAS CDC Adapter for IMS/DB supports transactions within IMS/DB transaction boundaries. However, no compensating records are available in the log in a rollback.
The IMS/DB CDC adapter connects to the MVS logstream with an authorization level of READ. The DFSFLGX0 user exit connects to the logstream with an authorization level of WRITE. To determine the proper security authorizations, see the MVS Auth Assm Services Reference ENF-IXG IBM manual.
Notes:
To access a logstream in an application with a READ authorization level, set the READ access to RESOURCE(<logstream name>) in SAF class CLASS(LOGSTRM).
To update a logstream in a program with a WRITE authorization level, set the ALTER access to RESOURCE(<logstream name>) in SAF class CLASS(LOGSTRM).