Adding a configuration script is an important step in defining the behavior and settings of your mainframe replication pipeline. This process allows you to specify parameters, establish workflows, and manage the execution of tasks within the pipeline. By following the steps outlined in this section, you will learn how to create and integrate a configuration script effectively, ensuring that your replication processes operate smoothly and efficiently.
Tip:
- Develop a naming convention based on existing names in source and target. For example, use table name as DESCRIPTION alias (optionally with prefix).
- Use named parameters for values that will change when moving between environments (dev, test, prod). For example, database name, schema name, host name, port number, publisher name.
- It is recommended to use version control for apply engine script.
- Do NOT alter the script source code after testing.
- Obtain relevant DDL and/or record layouts for datastores.
- For Db2 and other RDBMS sources and targets, this is the CREATE TABLE statements.
- For IMS, VSAM and mainframe files, COBOL copybooks is needed.
- For Messaging system targets, record layouts are normally created automatically.
- Determine requirements for mapping to target records and filtering / transformations (if any).
- Develop the apply engine script.
- Parse the apply engine script and resolve any syntax, naming or mapping errors.
- Do a “stand-alone” test of the script.
- The apply engine will connect to the publisher, then start receiving change records and applying them to the target datastore.
- Resolve any connection or run-time errors.
- Configure the daemon to control the apply engine.
- Test operation of the apply engine through the daemon.
Note: For the syntax elements of the script statements, refer to the SQData Help.
Description section
- The DESCRIPTION command defines the structures/layouts of source and target datastores and is used in conjunction with the DATASTORE command to associate a data structure/layout with a physical datastore.
- DESCRIPTION commands must be specified prior to any DATASTORE commands that reference fields within a DESCRIPTION structure/layout.
- It is common for a DESCRIPTION to be used by more than one DATASTORE (such as when the source and target record layouts are the same).
- Record or segment layouts are normally defined by metadata in files which are external to the script.
| Metadata type | <description_type> | Used for |
|---|---|---|
| SQL DDL (data definition language) | SQLDDL | Relational database tables |
| COBOL Copybook | COBOL |
VSAM, IMS*, mainframe file |
|
DBD (database definition) |
IMSDBD | IMS* |
| C header file | CSTRUCT |
Binary data (in a file or messaging system) |
| PL1 | PL1 |
Mainframe file |
| XML DTD (document type definition) | XMLDTD | XML data |
Syntax
DESCRIPTION <description_type> |COBOL|CSTRUCT|IMSDBD|DB2SQL|ORASQL|MSSQL|SQLDDL|XMLDTD|PL1
<file_name> | /+in line syntactically correct description on as many lines as required+/
AS <description_alias
[KEY IS <key1> [,<key2>...,<keyn>]]
[FOR SEGMENT <segment_name> IN DATABASE <dbname>]
[TOPIC <topic>]
[SUBJECT <subject>]Datastore section
- The DATASTORE command is used to specify the characteristics of source and target datastores. An Engine script may reference only one primarysource DATASTORE but may write or "apply" to multiple target datastores. There may also be secondary Prior to specifying the DATASTORE command in the script, one or more DESCRIPTION commands must be provided to describe the structure of each record in the datastore.
- A CDC Datastore may contain all the changed data for multiple physical Tables or all the different type of segments in an IMS database. Depending on the nature of the Engine processing the CDC Datastore one or more physical target Datastores may be affected.
- While a Relational Source datastore can contain only a single Table, an Engine can create multiple physical target datastores.
- A hierarchical or flat file source datastore may contain many different segments or record layouts which could result in one or more physical target datastores.
- <alias> is the name used to refer to the datastore in other sections of the apply engine script.
- One or more descriptions must be associated with the datastore.
| <datastore_type> | Used for |
|---|---|
| AVRO | Kafka |
| Binary | Files, messaging systems |
| Delimited | Files, messaging systems |
| IMSCDC | IMS CDC publisher |
| IMSDB | IMS database |
| JSON | Files, messaging systems |
| ORACLECDC | Oracle CDC publisher |
| Relational | RDBMS |
| UTSCDC | Db2 CDC publisher |
| VSAM | VSAM dataset |
| VSAMCDC | VSAM CDC publisher |
Syntax
DATASTORE <file_name>
| file://[<relative_path.> | <full_path.>] <file_name>
| <DD:ddname>[* | (<member_name>)]
| <table_name>
| RDBMS
| cdc://[<host_name_or_address>[:<port_number>]]/<agent_name>/<engine_name>
| cdcs://[<host_name_or_address>[:<port_number>]]/<agent_name>/<engine_name>
| tcp://<hostname>/<tcpip_port>
| mqs://[<qmgr_name>]/<queue_name> [:<correl_id>]
| kafka://[<hostname>[:<port_number>]] / [<kafka_topic_id>][/<partition> | key | root_key]
| hdfs://<hostname>[:<port_number>]/<hdfs_file_name>
OF <DATASTORE_TYPE> AVRO | BINARY | DB2LOAD | DELIMITED | HSSUNLOAD | IMSCDC | IMSDB | JSON | ORACLECDC | RELATIONAL | UTSCDC | VSAM | VSAMCDC | XMLCDC
[CHRDEL(‘<delchr>’)]
[COLDEL(‘<delchr>’)]
[RECDEL(‘<delchr>’)]
[CCSID <code> | ASCII | EBCDIC ]
[STAGING SIZE <nG>] -- file size in Gigabytes
[STAGING DELAY <mm>] -- rotate every mm minutes
AS <datastore_alias>
DESCRIBED BY <description_alias1> [,<description_alias2>,…, <description_aliasn>] | DUMMY
DESCRIBED BY GROUP <group_name>
[ACCEPT ALL]
[BYPASS CHGCHECK]
[CHECK FULL BEFORE IMAGE]
[COMPENSATE]
[COMPENSATE WITH WARNING]
[NO COMPENSATION]
[EXCEPTION <exception_datastore>]
[FOR CHANGE|INSERT]
[FORCE QUALIFIER <high_level>
[FORMAT [CONFLUENT | CONTAINER | PLAIN]
[IMSPATHDATA]
[KEEP NULL FIELDS]
[KEY IS <key1> [,<key2>...,<keyn>]]
[OPERATION IS <change_op_field>]
[PROCESS COMMIT]
[QUERY </+ <SQL Select and Where clause> +/>]
[RECONNECT]
[RRS]
[SINGLE IMAGE]
[STRIP TRAILING SPACES]
[WTO ON COMPENSATION EVERY <n> MINUTES]
[WTO ON EXCEPTION ONCE]Main processing section
- The Main body of the processing portion of the script.
- Initiates a processing loop beginning with the first record of the source datastore until the last record has been processed.
- The main processing section has three (3) commands:
- PROCESS INTO - The PROCESS INTO Command is used only once in the Apply Engine script. It initiates a processing loop beginning with the first record of the source datastore that continues until the last record from the source datastore has been processed.
- SELECT … FROM- Pair of commands (always used together) that defines the processing loop. Contains source-to-target mapping and transformation logic (if any), often implemented by calling named procedures (CALLPROC).
- UNION- Allows for “chaining” of multiple SELECT-FROM pairs [optional]
PROCESS INTO
PROCESS INTO identifies the target datastore(s) that will be acted upon by the script and is used in conjunction with the FOR <action> parameter specified in the DATASTORE command for the target datastore which explicitly states how the data will be applied to the target.
The PROCESS INTO command normally contains a single SELECT command, using this syntax:
PROCESS INTO <target_datastore(s)>
SELECT
{
Mapping logic and/or procedure calls go here
}
FROM <source_datastore> where <target_datastore(s)>
Syntax
PROCESS INTO <target_datastore> [, <target_datastore>]
SELECT [UNION];SELECT
The SELECT sub-command performs the primary processing within the Apply Engine. This sub-command has the ability to perform the following:
- Reads records from a source datastore, returning the fields listed within the sub-command. This functionality is similar to the SQL SELECT command.
- Specifies the target fields that will be updated in one or more target datastores.
- Specifies the source to target field mapping where the results will be written to one or more target datastores.
- Enables the use of Apply Engine functions that can be used for business logic, data transformation, filtering, etc.
Syntax
SELECT
SELECT *
SELECT <source_field> [, <source_field>]
SELECT <source_field> [, <source_field>] <target_field> [, <target_field>]
SELECT <source_field> [, <source_field>] <field_alias> [, <field_alias>]
SELECT <function_set> [, <function_set>] <target_field> [, <target_field>]
SELECT <function_set> [, <function_set>] <field_alias> [, <field_alias>]
FROM source_datastore | NOTHING
;UNION
The UNION sub-command chains multiple SELECT sub commands together within a the main Process Command to perform ‘multi-step’ processing within an Apply Engine script. When the UNION sub-command is used, the second SELECT sub-command is executed after the first SELECT sub-command has completed.
Syntax
UNION
SELECT [UNION];