Connect CDC SQData Engine parts are all text files that can be created with a text editor of your choice. Large scripts can consist of multiple text files, so it is important to use a consistent naming standard that will allow you to identify certain script segments particularly those used for Datastore Descriptions and Mapping procedures. These script parts can be ‘included’ into the primary script file using the #INCLUDE parser directive.
The following table outlines a suggested convention for naming script parts. Please note that the names have been limited to eight (8) bytes for ease of transportability to the z/OS environment, where parts will most likely be maintained in partitioned datasets as discussed earlier. If your environment does not include z/OS platforms you may choose to use more descriptive parts but we still encourage you to maintain the relationship between part names and their respective content.
| Part/File Name | ./directory | PDS suffix | Part Type Description |
|---|---|---|
| <source_type>TO<target_type> | ENGINE | Main Engine Script - Naming Engines for their Source and Target datastore types, eg: IMSTODB2, DB2TOORA, ORATOMSQ make them instantly recognizable. Generally there will be only one Engine for any two source and target pairs. |
| <source_description_file_name> | <primary_function> | CDCPROC | LOADPROC | Called Procedures - Most called procedures exist to perform mapping and data transformations from one source to one or more target datastores. Often the first procedure called will contain logic that will call one or more second level procedure. Generally all such multi-level procedures are contained within a single #INCLUDE <part_name>. Other types of procedures may be simple initialization statements or perform a common function called by multiple called procedures. |
| <source_description_file_name> | <TYPE>DDL | IMSDBD | IMSSEG | <TYPE>COB | RDBMS specific DDL, IMS Database Description, IMS Segment copybook, other COBOL copybook |
| <target_description_file_name> | <TYPE>DDL | IMSDBD | IMSSEG | <TYPE>COB | XMLDTD | RDBMS specific DDL, IMS DBD source, IMS Segment copybook, other COBOL copybook, XML Document Type Definition |
| <object_description_file_name> | <target_description_file_name> | <TYPE>CSR | Relational (Only) Cursors are typically used to validate keys; retrieve keys to a target row when a source contains only a partial key; or retrieve data from an object not itself subject to CDC / Load processing. The #INCLUDE <part_name> typically contains all the structures and instructions required to perform the "lookup / retrieval" functions. Cursors are typically used for validation or to retrieve data required for a Target but not contained in a Source. The CURSOR "lookup" may be to an "object" not itself subject to CDC / Load processing |
| <target_description_file_name> | <TYPE>LOAD | Load Control statements use by RDBMS specific Loader utility |
Beyond the conventions for "types" of things and their directory names, Precisely also highly recommends using the existing names of things in your Engine scripts. This makes communication easier for everyone including subject matter experts (SME's), data architects, source and target DBA's, the developers that will use the target data and of course the Engine script developer in the middle. Source and target DESCRIPTIONS are a good place to start where the basic DESCRIPTION statement syntax looks like this:
DESCRIPTION <type> ./<path_to_thing>/<name_of_thing> AS <name_of_thing>;
BEGIN GROUP IMS_DBD;
DESCRIPTION IMSDBD ./IMSDBD/<dbd_name>.DBD AS <dbd_name>;
... One for each DBD that has segments captured
END GROUP
;
BEGIN GROUP IMS_SEG;
DESCRIPTION COBOL ./IMSSEG/<dbd_name>/<segment_name>.cob AS <segment_name>
FOR SEGMENT <segment_name>
IN DATABASE <dbd_name>;
... One for each segment in each DBD that is captured
END GROUP
BEGIN GROUP ORA_DDL;
DESCRIPTION ORASQL ./ORADDL/<dbd_name>/<segment_name>.ddl AS T_<segment_name>;
... One table ddl description for each segment in each DBD that is captured. Note two things. You should consider including the 8 character segment_name as part of the actual table name and then name the ddl the same as the segment_name but with a different file extension. Second, even though DBD's have nothing to do with RDBMS tables, mirroring the directory structure greatly simplifies maintenance of an IMS to "whatever" target, especially if there are large numbers of DBD's and or segments.
END GROUP
#INCLUDE ./CDCPROC/<dbd_name>/<segment_name>.sqd
... one mapping Proc for each segment/table pair again with the <dbd_name> in the directory structure.PROCESS INTO TARGET
SELECT
{
CASE IMSSEGNAME(CDCIN)
WHEN '<segment_name>'{ CALLPROC(M_<segment_name>) APPLY(TARGET,T_<segment_name>) }
...a WHEN for each segment/table pair
}
FROM CDCIN;