Overview

Skill Level: Intermediate

This recipe documents an asset which has been developed around the Q Replication technology included in IBM Data Replication. It uses a simple framework plus standard product capabilities such as ASNCLP to speed up the setup and rollout of Subscriptions.

Ingredients

InfoSphere Data Replication - Q Replication

IBM Data Replication is a data replication platform to replicate information between cross-platform heterogeneous data stores in near real time. Q Replication is one data replication discipline within this platform. Q Replication includes log based change capture for DB2 z/OS, DB2 LUW, and Oracle databases. Common usage scenarios are the delta provisioning of data warehouse and data lake systems including historization, the real-time maintenance of read-mirrors, zero-downtime data migrations, data synchronization between multiple active databases (continuous availability, active/actice), and others. Q Replication leverages IBM MQ as fast, reliable, and secure transport infrastructure.

A Replication Subscription

The mapping between a source table in a replication source database and a target table in a replication target database is called Replication Subscription. When a subscription is created, all replication options are stored, including target table type, target table name, load options, conflict and error options, and many more. Subscriptions can be created using

  • the UI of the DB2 Replication Center,
  • the browser based UI of the Q Replication Dashboard
  • scripts leveraging the Q Replication command syntax called ASNCLP

ASNCLP

ASNCLP is the Q Replication command syntax to create replication objects. ASNCLP commands are embeddable in scripts or can be interactively executed using the ASNCLP command processor. Comparing it to the replication UIs, all radio buttons, switches, fields which can be set when creating a replication subscription with the Replication Center or the Q Replication Dashboard can be expressed via ASNCLP command options.

Q Replication Control Tables

The Q Replication Control Tables are regular database tables in the replication source and replication target database. The tables are created during the Q Replication setup. Each ASNCLP command generates SQL to insert or modify information in the replication control tables about replication sources, targets, queues, and other options. Multiple commands can be used together to generate the SQL for an entire replication configuration.

IBM DB2

IBM DB2 is the number one enterprise database and the undisputed leader for storing and leveraging mission critical data. It is the "gold standard" for system availability, scalability, security and cost effectiveness. This recipe was tested with DB2 11 for z/OS and DB2 10.5 for LUW as replication sources.

Step-by-step

  1. Introduction of the ASNCLP Q Subscription Generator

    The ASNCLP Subscription Generator for Q Replication described in this recipe can be used to automate the setup of Q Replication subscriptions. The generator is especially useful if

    • a huge number of subscriptions has to be created, either unidirectionally or bidirectionally,
    • the effort of manually maintaining replication subscriptions either with the IBM DB2 Replication Center or ASNCLP commands is significant,
    • Q Replication setup skills are rare and an easy to use interface is required to declare the list of tables relevant for replication

    The ASNCLP Subscription Generator is not part of the IBM Data Replication product but was developed as a supplement or asset based on project experience. It may be reused, shared, modified. It is provided as is, without warranties of any kind.

    The ASNCLP Subscription Generator is a combination of standard software interfaces (ASNCLP commands, replication control tables), database objects (tables and views), and custom programming (SQL). The ASNCLP Subscription Generator can be individually extended if required. The source code consists of simple shell scripts and SQL scripts querying relational tables and views.

    The ASNCLP Subscription Generator generates and executes proper ASNCLP command syntax. The only difference compared to standard manual ASNCLP processing is that the commands are generated.

    Generic Replication Server setup using the ASNCLP Subscription Generator can be combined with individual replication administration using IBM DB2 Replication Center or manually created ASNCLP commands.

    Summarizing, the ASNCLP Subscription Generator eases the setup and maintenance of huge data replication applications with less administrative effort. Additionally, quality assurance mechanisms are embedded so that the replication administrator can be sure that subscriptions exist for all tables that ought to replicate.

  2. Package Content and Deliverables

    The ASNCLP Subscription Generator is delivered as a ZIP file. It can be extracted and used on any Linux, Unix, or Windows system with DB2 Client or DB2 Server. Replication Source and target can be any DB2 z/OS or DB2 LUW database supported by Q Replication.

    The following list displays the directory structure of the generator after unzipping it:

    ./asnclp_generator_q
    Main directory. Contains the Subscription Generator commands and environment-specific template files

    ./asnclp_generator_q/setup
    Scripts to set up database objects (tables, view, sequence, function) for the ASNCLP Subscription Generator at the Q Capture Server (source database). The control table setup is identical for both the unidirectional and bidirectional Subscription Generator.

    ./asnclp_generator_q/select_asn_commands/unidirectional
    SQL scripts reading the generator control view ASNCLP_Q_WORKLOAD and other DB2 catalog tables and Q Replication control tables to generate ASNCLP commands for unidirectional subscriptions

    ./asnclp_generator_q/select_asn_commands/bidi
    SQL scripts reading the generator control view ASNCLP_Q_WORKLOAD and other DB2 catalog tables and Q Replication control tables to generate ASNCLP commands for bidirectional subscriptions

    ./asnclp_generator_q/samples
    Scripts which demonstrate the usage of the generator, especially how to fill the setup tables.

    ./asnclp_generator_q/asnclp
    The ASNCLP commands generated by the Generator are stored here.

    ./asnclp_generator_q/output
    The SQL scripts created when the generated ASNCLP command files are being executed with asnclp -f are stored here.

    ./asnclp_generator_q/logs
    Logs of the ASNCLP command executions

    The following sections explain how to use the content of these directories to set up and run the ASNCLP Subscription Generator.

     

  3. Setup of the ASNCLP Subscription Generator in the source database

    To operate the Subscription Generator a set of database objects has to be created within the Q Capture Server (source database). The schema of these database objects has to be identical to the Q Capture schema. If you operate multiple Q Capture programs with different schemas within the same source database, create multiple sets of Generator control tables. DB2 z/OS and DB2 LUW Q Capture Servers are supported.

    To create the required database objects, refer to directory ./asnclp_generator_q/setup. The script asnclp_q_setup.sh (or asnclp_q_setup.bat) executes all SQL scripts to create the required database objects. Before executing the command, adopt the following settings to your environment:

    • name of the Q Capture Server (name of the source database)
    • name of the Q Capture Schema

    Optionally use the script optional_grant.sql to grant necessary user rights to the user executing the ASNCLP generator commands.

    The following list contains all database objects required for the ASNCLP Subscription Generator. The schema of the objects must be the same as the Q Capture Server control tables:

    ASNCLP_Q_DEFAULTS (Table): Contains one row with project defaults.

    ASNCLP_Q_SCHEMAS (Table): Optionally contains one row per schema (no wildcards allowed) which has to replicate (with options which can supersede the defaults). If not all tables of the schema have to replicate, tables can be expluded in ASNCLP_Q_TABLES.

    ASNCLP_Q_TABLES (Table): Optionally contains one row for each table which has to replicate (with options which can supersede the schema table or the defaults).

    ASNCLP_Q_WORKLOAD (View): A view which constructs the content of the 3 tables above to a list of all tables which have to replicate and for which no subscription does yet exist.

    ASNCLP_Q_VALIDATE_% (Triggers): A number of triggers which prevent invalid combinations of options while inserting into / updating the setup tables above

    GET_INT4 (UDF): Casts a character into an integer. Returns 0 if the character string contains non-numeric characters. Used to create subscription name suffixes (e.g. ‘0001’) the way the DB2 Replication Center does.

    ASNCLP_SEQ (Sequence): Required to generate SUBGROUP names for bidirectional subscriptions

    The following figure displays the replationship between the control tables of the Subscription Generator, the standard Q Replication Control Tables, and the DB2 catalog tables:

    ASNCLP_Q_WORKLOAD

    The figure describes how the view ASNCLP_Q_WORKLOAD selects and joins the ASNCLP Subscription Generator control tables, the system catalog table SYSIBM.SYSTABLES and the Replication Server Control table IBMQREP_SUBS. Examples:

    • If a tables is listed in ASNCLP_Q_TABLES and specific options (such as the conflict rule) are not specified here, the default for this option is taken from ASNCLP_Q_SCHEMAS. If the schema of the table is not listed there, the default for that option is taken from ASNCLP_Q_DEFAULTS.
    • If a schema is listed in ASNCLP_Q_SCHEMAS the Generator determines if dedicated tables are excluded by checking ASNCLP_Q_TABLES
    • The Generator checks if listed tables exist by joining SYSIBM.SYSTABLES
    • The Generator checks if a subscription has to be created by verifying that it does not yet exist (by joining IBMQREP_SUBS)

       

  4. Setup of the ASNCLP Subscription Generator Environment

    After the database objects of the Subscription Generator have been created and before the Generator can be used the Generator commands and the Generator’s template files have to be edited to set the replication environment. This is a one-time setup action. Remember that the Generator is run from LUW regardles of the Q Capture Server being on LUW or z/OS.

    1) Edit the command file “q_sub_create.sh” (or “q_sub_create.bat”). When executing the command later, the command connects to the Q Capture Server. Therefore, the name of the Q Capture Server (optionally with user/using) has to be set. Additionally, the name of the Q Capture Schema has to be set.

    cp -f template_q_sub.tpl ./asnclp/q_sub_create.asnclp

    # Change the database name to the name of the Q Capture Server database alias
    # Add user id and password if the Q Capture server is remote
    db2 -o- connect to sourcedb user myuser
    # Change the schema to the Q Capture Schema
    db2 -o- set current schema = 'ASN'

    db2 -xtf ./select_asn_commands/unidirectional/q_sub_create.sql >> ./asnclp/q_sub_create.asnclp

    db2 -o- set current schema = USER
    db2 -o- connect reset

    asnclp -f ./asnclp/q_sub_create.asnclp

    When the generator is invoked later, it executes the following steps:

    1. The template file (see step 2 – template file) is copied to a new file (./asnclp/q_sub_create.asnclp)
    2. DB2 command to connect to the Q Capture server. Optionally use ‘user’ and ‘using’ to specify a user id and a password. If the ‘using’ clause is omitted, the user is prompted for the password when the command will be executed. The option ‘-o-‘ means that the DB2 command wil produce no output
    3. DB2 command to set the current schema to the Q Capture Schema. All subsequent SQL SELECT statements will not qualify the control tables by schema.
    4. SQL statement to select all ASNCLP commands to create all required Q Subscriptions. The output of the SQL SELECT will be appended to the file created in step a. -x means that only the selected rows (no column headers) will be written to the output
    5. Resetting the schema to the current user
    6. Disconnect from the Q Capture Server
    7. Execution of the generated ASNCLP commands. If you just want to test the Generator, comment this final step. This will generate but not execute all ASNCLP commands

    2) Edit the template file “template_q_sub.tpl”. Again, the name of the Q Capture Server and the name of the Q Capture Schemas has to be set. If the ‘PASSWORD’ clause is omitted, the user is prompted for the password when the ASNCLP command will be executed.

    # ASNCLP COMMAND GENERATOR V2.0 - unidirectional subscription template 

    ASNCLP SESSION SET TO Q REPLICATION;

    SET OUTPUT CAPTURE SCRIPT ".\output\q_sub_capture.sql" TARGET SCRIPT ".\output\q_sub_target.sql";
    SET LOG ".\logs\q_sub.log";

    SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
    #SET RUN SCRIPT LATER;
    #SET TRACE ON;

    # >>>>> Adjust the Capture / Apply Server name, and <<<<<
    # >>>>> (if Capture and/or Apply Server are remote) <<<<<
    # >>>>> optionally add ID/PASSWORD to the SET SERVER clauses.
    # Example:
    # SET SERVER CAPTURE TO DB SOURCEDB ID myuid PASSWORD mypassword;

    SET SERVER CAPTURE TO DB SOURCEDB ID myuser;
    SET SERVER TARGET TO DB TARGETDB;

    # >>>>> Adjust the Capture / Apply Schema <<<<<
    SET CAPTURE SCHEMA SOURCE ASN;
    SET APPLY SCHEMA ASN;

    3) Ensure DB2 connectivity to the Q Capture Server (replicatoin source database) and the Q Apply Server (replication target database).

  5. Specification of tables that have to be replicated

    First of all check if the ASNCLP Subscription Generator defaults are appropriate for your environment. During setup, the following single row is inserted into ASNCLP_Q_DEFAULTS:

    INSERT INTO ASNCLP_Q_DEFAULTS
    (HAS_LOADPHASE, LOAD_TYPE, ERROR_ACTION,
    CONFLICT_ACTION,
    CONFLICT_RULE_BIDI_N1N2, CONFLICT_ACTION_BIDI_N1N2,
    CONFLICT_RULE_BIDI_N2N1, CONFLICT_ACTION_BIDI_N2N1,
    ALL_CHANGED_ROWS, SUPPRESS_DELETES, CAPTURE_LOAD,
    REPL_ADDCOL, START_AUTOMATICALLY)
    VALUES
    ('I', 0, 'D',
    'I',
    'C', 'F',
    'A', 'I',
    'N', 'N', 'W',
    'Y', 'NO');

    Next, you can chose

    • to declare a complete schema so that subscriptions for all tables of that schema will be generated
    • to declare a complete schema but to exclude dedicated tables of that schema
    • to declare no schema but just dedicated tables (of the same or of different schemas)

    In the first example all tables of schema ASNCLPTEST1 shall replicate unidirectionally, the Q Apply program shall deactivate the receive queue in case of an error, the queue map to be used is SOURCEDB_ASN_TO_TARGETDB_ASN:

    INSERT INTO ASNCLP_Q_SCHEMAS 
    (TABSCHEMA, PUBQMAPNAME, ERROR_ACTION)
    VALUES
    ('ASNCLPTEST1', 'SOURCEDB_ASN_TO_TARGETDB_ASN', 'Q')
    ;

    Next, if you want to exclude table ASNCLPTEST1.TABLE4 from replication although the schema ASNCLPTEST1 was declared as in the previous example insert the following row into ASNCLP_Q_TABLES:

    INSERT INTO ASNCLP_Q_TABLES 
    (TABSCHEMA, TABNAME, REPLICATE)
    VALUES
    ('ASNCLPTEST1', 'TABLE4', 'N')
    ;

    If you do not want to declare a schema, but dedicated tables, just insert a row for each table into ASNCLP_Q_TABLES. Different options can be used for different tables.

    Table ASNCLPTEST2.TABLE3 shall replicate unidirectionally, in case of an error the Q Apply program shall disable the Q Subscription only, the queue map to be used is SOURCEDB_ASN_TO_TARGETDB_ASN. The options overwrite the schema wide options of schema ASNCLPTEST2 (in case the schema is stored in ASNCLP_Q_SCHEMAS) and the defaults in ASNCLP_Q_DEFAULTS:

    INSERT INTO ASNCLP_Q_TABLES 
    (TABSCHEMA, TABNAME, PUBQMAPNAME, ERROR_ACTION)
    VALUES
    ('ASNCLPTEST2', 'TABLE3', 'SOURCEDB_ASN_TO_TARGETDB_ASN', 'D')
    ;

    Next, TABLE5 should replicate into a CCD table. The schema of the target table is different from the source schema and the name of the target table should be TABLE5_CCD. Other options also apply:

    INSERT INTO DB2IDR.ASNCLP_Q_TABLES 
    (TABSCHEMA, TABNAME, REPLICATE, PUBQMAPNAME, SUBTYPE,
    TARGET_TYPE, CCD_CONDENSED, CCD_COMPLETE, HAS_LOADPHASE, LOAD_TYPE,
    SPILL_MODELQ, ERROR_ACTION, CONFLICT_ACTION,
    ALL_CHANGED_ROWS, SUPPRESS_DELETES, CAPTURE_LOAD,
    START_AUTOMATICALLY, REPL_ADDCOL,
    TARGET_SCHEMA, TARGET_NAME)
    VALUES
    ('ASNCLPTEST2', 'TABLE5', 'Y', 'SOURCEDB_ASN_TO_TARGETDB_ASN', 'U',
    2, 'N', 'N', NULL, NULL,
    'DB2.QREP.SPILL.MODELQ', 'D', NULL,
    NULL, NULL, NULL,
    'NO', 'Y',
    'MYSCHEMA', 'TABLE5_CCD');

     

  6. Creation of the Replication Queue Maps

    Before the ASNCLP Subscription Generator can be used to create Q Subscriptions, the Replication Queue Map has to be created upfront. Use the DB2 Replication Center or ASNCLP to create the Q Map. The following eample can be used to create a Q Map with ASNCLP:

    ASNCLP SESSION SET TO Q REPLICATION;

    SET OUTPUT CAPTURE SCRIPT "create_replqmap_capture.sql" TARGET SCRIPT "create_replqmap_apply.sql";
    SET LOG "create_replqmap.log";
    #SET RUN SCRIPT LATER;
    SET RUN SCRIPT NOW STOP ON SQL ERROR ON;

    SET SERVER CAPTURE TO DB SOURCEDB ID myuser;
    SET SERVER TARGET TO DB TARGETDB;

    SET CAPTURE SCHEMA SOURCE ASN;
    SET APPLY SCHEMA ASN;

    SET QMANAGER "ASN" FOR CAPTURE SCHEMA;
    SET QMANAGER "ASN" FOR APPLY SCHEMA;

    CREATE REPLQMAP SOURCEDB_ASN_TO_TARGETDB_ASN USING
    ADMINQ "DB2.QREP.SOURCEDB.ADMINQ"
    RECVQ "DB2.QREP.SOURCEDB.TARGETDB.SENDQ"
    SENDQ "DB2.QREP.SOURCEDB.TARGETDB.RECVQ"
    NUM APPLY AGENTS 12
    HEARTBEAT INTERVAL 60000
    ;

     

  7. Invocation of the ASNCLP Subscription Generator

    The ASNCLP Q Subscription Generator is invoked through command q_sub_create.sh (q_sub_create.bat) from the command line without any parameters:

    [asnclp_generator_q]$ ./q_sub_create.sh

    If a complete schema was defined as replication object in table ASNCLP_Q_SCHEMAS the invocation of the command script q_sub_create.bat / q_sub_create.sh adds all tables (that exist at the capture server for that schema ) to the replication setup for which no subscription was defined yet. In the same manner the invocation of the script adds all tables to the replication setup which were defined in ASNCLP_Q_SCHEMAS with REPLICATE = ‘Y’. Existing subscriptions remain unchanged.

    After the invokation,

    • the generated ASNCLP commands can be reviewed in ./asnclp_generator_q/asnclp
    • the SQL statements generated by asnclp -f can be reviewed in ./asnclp_generator_q/output
    • the ASNCLP logs can be reviewed in ./asnclp_generator_q/logs

    If an error occurs during ASNCLP command execution, the generator stops and displays the error message. All subscriptions generated before the error occured are defined and could be started. After the error condiction has been corrected, the ASNCLP Subscription Generator can be invoked again. Only the subscriptions that do not yet exist will be generated.

    If you want to change or extend the ASNCLP Subcription Generator, review the SQL SELECT that generates the CREATE QSUB commands. You can find it here:

    • ./asnclp_generator_q/select_asn_commands/unidirectional/q_sub_create.sql

    The SELECT statements queries the view ASNCLP_Q_WORKLOAD and concats all subscription options to a valid CREATE QSUB command.

  8. Reviewing the Result

    The results of the subscription generation can be reviewed with any means:

    • using the DB2 Replication Center
    • using the Q Replication Dashboard
    • using SQL to query the Replication Control Tables

    With the options explained in the above sections, the subscriptions do not start automatically with the next start of the Q Capture program. The subscriptions have to be activated with a SIGNAL, which can be easily created using the Q Replication Dashboard (Subscriptions > Start).

  9. Maintenance of Replication Subscriptions

    If more tables should replicate over time, just add them to the list of tables in ASNCLP_Q_TABLES or even ASNCLP_Q_SCHEMAS and invoke the Subscription Generator again. All new subscriptions are added, existing subscription will remain as they are. You can invoke the Generator as often as you like (e.g., every day). It only adds subscriptions if new tables were added to ASNCLP_Q_TABLES or ASNCLP_Q_SCHEMAS. If nothing changed there, the Generator won’t do anything.

  10. Current Limitations

    The generator supports a lot, but not all options the ASNCLP command language offers.

    If general, schema, or table specific options of tables already being replicated have been changed and the generator is invoked again, options are not altered by the generator. It only can add or remove subscriptions.

    The Generator does not check if the target table exists already. It can be configured to always expect the existing target tables or always create the target table.

    ASNCLP cannot remove a subscription if the source table was dropped. When you call the generator after a source table has been dropped, the generator will create and execute the corresponding commands to drop the subscription (unidirectional or bidirectional) but the execution of the commands ends in error.

  11. Wrapping it up

    The ASNCLP Subscription Generator for Q Replication introduced in this recipe is an easy to use utility to speed up the setup of Q Replicatoin Subscriptions. Especially

    • if a lot of subscriptions have to be defined,
    • if different departments are responsible for the replication source and the target databases,
    • if replication skills are rare

    the ASNCLP Subscription Generator will prove very useful. The steps to get the generator going are simple, the scripts and commands are all open and are all using standard product features such as ASNCLP or SQL. The Generator can be extended or adopted to custom environments easily.

    The ASNCLP Subscription Generator can be downloaded here:

    https://ibm.box.com/s/3ffm4ydy3tfptprl3pygkxvfiujnsl0o

    If you are interested in more Q Replication related recipes, have a look here:

    Continuous high performance DB2 Q Replication plus repeatable query results (stable targets)
    https://developer.ibm.com/recipes/tutorials/continuous-high-performance-db2-q-replication-plus-repeatable-query-results-stable-targets/

    Extend your Information Governance Catalog with Q Replication Metadata
    https://developer.ibm.com/recipes/tutorials/extend-your-information-governance-catalog-with-q-replication-metadata/

Join The Discussion