The OracleAS CDC Adapter for VSAM 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 VSAM. 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 OracleAS CDC Adapter for VSAM. You can edit the properties using Oracle Studio. The OracleAS CDC Adapter for VSAM has the following types of properties:
LoggerName: 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 the JRNAD exit and Logical Transaction Management. For more information on the creation and syntax, see Configuring the Logger (VSAM Batch Only).
Note:
The Oracle Connect CDC VSAM Batch solution automatically sets its own JRNAD user exit routine during the open process of a VSAM cluster (if another JRNAD exit is in use, the solution cannot work).Make changes to the corresponding jobs to:
Provide the OracleAS CDC Adapter for VSAM Batch hook for automatic JRNAD definition
Manage Logical Transactions (for more information, see Logical Transaction Manager)
| Name | Valid Values | Default | Comment |
|---|---|---|---|
|
|
|
|
Write before image to logstream |
|
|
|
|
Use blocking write |
|
|
|
|
Print debug info using WTO |
|
|
*/ |
* |
VSAM cluster that should be captured; An asterisk (*) indicates that all the VSAM clusters opened with Oracle JRNAD should be captured. Each |
|
|
|
|
When |
|
|
|
|
Allows the use of the Logical Transaction Manager |
|
|
|
|
The name of the used MVS logstream |
|
|
|
All |
Defines the type of operations that are written to the logstream. |
|
|
|
|
Ignore dummy records used for empty KSDS cluster access. |
|
|
|
|
Use synchronize logstream write. |
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 :
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.
The following table describes the common configuration properties for Oracle CDC adapters. The OracleAS CDC Adapter for VSAM uses only these properties.
Table C-2 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 the OracleAS CDC Adapter Properties.
Table C-3 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. In some rare cases, it may be necessary to lower this value 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-4 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. |
|
terminalID |
The terminal ID that originated the change. |
|
taskID |
The task ID originating the change. |
|
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. |
|
jobName (VSAM Batch only) |
The name of the job that caused the VSAM update. |
|
programName (VSAM Batch only) |
The name of the program that changed the VSAM data. |
|
userName (VSAM Batch only) |
The name of the user running the job. |
|
stepName (VSAM Batch only) |
The name of the step in the job. |
|
procedureStepName (VSAM Batch only) |
The name of the procedure name run by the step. |
|
programStartTimestamp (VSAM Batch only) |
The time when the program started to be executed. |
Footnote 1 DELETEALL is relevant for VSAM Batch only.
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>
The OracleAS CDC Adapter for VSAM supports transactions in their CICS boundaries.
For the OracleAS CDC Adapter for VSAM Batch, there are two types of transaction management:
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.
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.
The OracleAS CDC Adapter for VSAM 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).