Connect CDC SQData

Using a tool to replicate and transform data exposes invalid source data and provides an opportunity to ensure that such data is either corrected at the source or at a minimum not duplicated. Downstream applications should not be required to duplicate invalid data handling business rules. Fortunately relational databases, which are frequently the target for the captured / replicated data will not accept the entry of truly invalid data.

Connect CDC SQData was designed to ensure that invalid data is never propagated to a target datastores. The Apply Engine will terminate the instant it encounters a bad source record for which no exception handling has been specified. Because it is inevitable that bad data will be encountered and it is often impractical to correct the source data, the Apply Engine provides two means of dealing with invalid data:

1.INVALID Command - Sets the default value to use when invalid data is found in a source field.

2.Exception Datastore - Used to capture exception data as it is encountered so that human or programmatic intervention can be taken to either correct the source data or more often, determine how the script should be modified to accommodate the bad data and enable associated source data to pass on to target datastores. An Exception Datastore can be assigned to either the Source or the Target Datastore.

Testing will generally identify most of the common invalid data conditions and prompt the development of data correction business rules. New exception conditions are however, created as source datastores and applications evolve. For this reason Precisely recommends the use of exception datastores during all stages of testing. Generally Precisely does not recommend the use of Source exception datastores in a production environment. Invalid source data conditions, particularly in legacy IMS databases, can generate very large amounts of data that must be re-processed, creating unnecessary operational exceptions and other potential downstream issues. Given the volume of invalid data and its age, it may not be possible to correct the data in the source database. It is generally better to allow a production Apply Engine to fail while continuing any active capture processing; modify the Apply Engine script to "handle" the Invalid Data and simply restart the Engine.

While the most commonly used exception handling mechanism is the INVALID Command, the Exception Datastore should not be overlooked for handling exceptions, particularly during development and testing of new Apply Engine scripts. The exception datastore can be used to hold source datastore records for further analysis, processing or recycling, while a target exception datastore, particularly useful for relational targets will contain the actual SQL that failed to apply. Target exception datastores are particularly useful since the SQL can be applied directly once an issue, which most often affects only a single Target datastore, is resolved. This can be much simpler than scheduling a special Engine that reprocesses the Exception Datastore.