Mapping using IBM DataStage

There are two methods of mapping tables in CDC Replication for consumption by IBM® DataStage®: Flat File and Direct Connect.

Note: Starting with Version 11.4 of the CDC Replication Engine for InfoSphere® DataStage, the Direct Connect method is no longer supported. You can still use the Management Console in V11.4 to configure this method for the V11.3.3 engine.

In the Flat File method, CDC Replication produces one or more files containing information about one or more records and database operations, with each record occupying its own line followed by a delimiter if you use the Single Record option, or with each record occupying two lines if you choose the Multiple Records option when mapping your tables. Those files are saved to be retrieved by IBM DataStage and used by the Sequential File Reader stage of the IBM DataStage job. For update operations, the flat file will have both the before and after image in one line.

In the Direct Connect method, the data is not written to a file, but is sent instead over a TCP/IP connection directly to IBM DataStage to be processed by a specific IBM DataStage job that you have identified by specifying the matching Project Name, Job Name, and Connection Key in the IBM DataStage Properties dialog box in Management Console. The IBM DataStage Connector processes the data, then transforms and translates it into a format recognized by the IBM DataStage job.

With the Direct Connect connection method, you can enable the autostart feature to run in active mode, which allows you to start a IBM DataStage job from CDC Replication and begin to stream data to IBM DataStage. Running with autostart enabled requires both CDC Replication and IBM DataStage to be installed on the same server. If autostart is not enabled, you will be running in passive mode, and you must run jobs from IBM DataStage before the Direct Connect data stream can begin. Note that to use the full functionality of the Direct Connect option, you must have Management Console version 6.5 or later, Access Server version 6.5 or later installed as well as having CDC Replication version 6.5 or later installed on the same server as IBM DataStage, a component of IBM Information Server version 8.5.

Understanding the workflow

Depending on the connection method you choose, files are either saved for retrieval by IBM DataStage (Flat File), or data is streamed to IBM DataStage (Direct Connect), by CDC Replication when either when data limits are reached (determined the Batch Size Threshold settings you've indicated in the IBM DataStage Properties dialog box in Management Console after mapping your tables) or when a refresh or mirroring operation ends.

For the Flat File connection method, the process begins once a refresh or mirroring operation begins, and CDC Replication starts writing change information to temporary data files for only those tables in the subscription for which there are changes. Once the Batch Size Threshold limits are met, CDC Replication hardens the temporary data files at the subscription level with timestamps in the filenames. No data files are produced for tables that have no changes. Once the refresh or mirroring operation is ended, <TABLE_NAME>.STOPPED files, which server as status flags, are produced for each table in the subscription. These files are ready for consumption by the IBM DataStage job.

Attention: If you kill a refresh or mirroring operation using the dmterminate command, the temporary data files cannot be hardened at the subscription level, no <TABLE_NAME>.STOPPED status flag files are generated for the tables in the subscription, and the bookmark is not updated. You must restart the refresh or mirroring process. Be aware that restarting uses the last-saved bookmark and starts a new set of temporary data files to be hardened as thresholds are met. To ensure that the temporary data files are hardened, and the <TABLE_NAME>.STOPPED status flag files are created, use a Normal or Scheduled End shutdown in Management Console, or you can issue a dmshutdown command with the appropriate flags for the severity level. If you use the Abort or Immediate shutdown options, CDC Replication may opt not harden the temporary data files as a way of facilitating these more rapid shutdown requests.

For the Direct Connect connection method, the process is similar. The size and time limits set in the IBM DataStage Properties dialog box determine when data is sent, and the matching Project Name, Job Name, and Connection Key information set in the IBM DataStage Properties dialog box permit CDC Replication to send the data to IBM DataStage directly, without saving any of the data as flat files.

Additionally, with the Direct Connect connection method, you can enable the autostart feature to run in active mode, which allows IBM DataStage to start a job when appropriate and begin to stream data to IBM DataStage. Running with autostart enabled requires both CDC Replication and IBM DataStage to be installed on the same server. If autostart is not enabled, you must run jobs from IBM DataStage before the Direct Connect data stream can begin. For instructions on enabling autostart, see the Management Console documentation.

It is important to note that the Direct Connect connection method does not maintain the ordering of operations across tables, though it will maintain the transactional boundaries that existed on the source. For example, if the transaction on the source was

INSERT TABLE1 
INSERT TABLE2 
COMMIT

then both of the INSERT actions will be completed by IBM DataStage before it performs the COMMIT action, however, because the order of operations is not maintained, there is no way to determine the order in which the INSERT actions will be done prior to being committed to the target database.

Important: In order to use the autostart function in the Direct Connect method on a UNIX or Linux® system, ensure that you have correctly set the database library directory in the dsenv file for InfoSphere Information Server. See the topics on Ensuring thatIBM DataStage users have the correct localization settings (Linux, UNIX) and Configuring the dsenv file in the InfoSphere Information Server Planning, Installation, and Configuration Guide.