Overview

Skill Level: Intermediate

Q Replication, DB2, SQL, MQ

Many replication use cases desire real time replication. But sometimes the replication target has to represent just the previous end of business or another exact timestamp. This recipe discusses how to achieve that in the most efficient manner with Q Rep.

Ingredients

The strength of IBM DB2 Q Replication - low latency real time replication with minimal impact on transactional source systems - is required by many replication use cases, such as read mirrors or long distance active / active DB2. But some use cases - especially in the traditional analytics space - benefit from a replication target that does not change over day, so that repeated queries always deliver the same result.

This recipe explains an approach to combine continuous real time replication and stable targets - using IBM DB2 Q Replication as well as efficient ELT processing based on DB2 SQL. No additional ETL tooling or custom programming required. This list of ingredients has everything needed:

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 as replication source and DB2 10.5 with BLU acceleration as replication target. For DB2 for z/OS as replication target, this approach requires DB2 12 for z/OS due to the enhanced capabilities of the MERGE SQL statement in that release.

InfoSphere Data Replication - Q Replication

InfoSphere 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.

Q Replication CCD tables

CCD tables are a documented target table type (in some scenarios also a source table type) for Q Replication and SQL Replication. One CCD target table is defined for each source table in the approach documented in this recipe. CCD tables can contain a full history or a condensed history of changes replicated from a source. CCD target tables are widely used as staging area between the transactional source and the analytic warehouse or data lake. Other scenarios leverage CCD tables as vehicle for a complete historization of source data.

SQL MERGE statement

The MERGE statement is used to update a target table using data from a source (result of a table reference). Rows in the target that match the source can be deleted or updated as specified, and rows that do not exist in the target can be inserted.

All ingredients above are taken from the "DBA" shelf -- no ETL tooling, no programming, no custom programs required. All is open, easy adoptable, easy to maintain.

If you are interested in how to document and govern this Q Replication based ELT processing in IBM InfoSphere Information Governance Catalog and data lineage, we recommend the following recipe: https://developer.ibm.com/recipes/tutorials/extend-your-information-governance-catalog-with-q-replication-metadata/

Step-by-step

  1. Overview

    The following figures depict three alternative approaches to achieve a stable Q Replication target with the following characteristics:

    • Stable means that the replication target tables do not change over day so that repeated queries always deliver the same result set.
    • The tables are being refreshed only at a dedicated point in time.
    • An additional goal is to refresh the tables up to an exact certain source transaction, which could for example mark the end of the previous business day.

    Approach 1: Stop Send Queue with a Capture SIGNAL

    source_signal

    The first approach stops replication at a dedicated point in time. This is achieved by stopping the publication for a dedicated send queue which is the transmission infrastructure for multiple Q Replication subscriptions. A SIGNAL is used to stop the publication activity for the send queue; the user has full control to stop replication after the final transaction has been committed at the source system. All transactions that committed before the signal are securely published. All transactions committing after the signal won’t unless the send queue is reactivated. Optionally, after all transactions have been processed at the target site the receive queue is stopped and the send queue can be restarted so that the Capture process can catch up to DB2’s current log position. Until the receive queue is restarted during the next replication window all changes queue up in the MQ infrastructure.

    Approach 2: Pausing Apply processing with APPLYUPTO parameter

    applyupto3

    The second approach stops the Apply process when it replicated all transactions up to a dedicated source timestamp. After Apply stopped when the applyupto timestamp was reached, changes accumulate in the receive queue until Apply is started with the next applyupto timestamp. Because the parameter applyupto is set at Apply process level, the approach has to separate the workload between different Apply processes if different groups of tables have to replicate up to different timestamps.

    Approach 3: Continuous data replication with subsequent ELT MERGE processing

    merge

    The final approach replicates data continuously without the need to stop replication tasks at any time. In contrast to approach 1. and 2. the data is not replicated into the ODS / data warehouse tables directly. Instead, the data is replicated into a staging area within the target database which in fact is built upon Q Replication CCD tables. From there the data can be moved to the final destination with in database ELT processing. With a small framework, the user also has full control to move over all data up to a dedicated transaction boundary.

    This recipe discusses the third approach in detail. Comparing pros and cons the third approach is the most appropriate way to achieve the described goal.

    Pros:

    • Changed data is replicated to the target platform with minimal latency and is stored there safely
    • Replication tasks and processes do not have to be stopped / started temporarily. No need to synchronize the stop and start of send or receive queues or other replication tasks.
    • Replication does not queue up in the transport layer (MQ)
    • The transaction high water mark to which the target tables are refreshed can be controlled granularly and easily (different high water mark for different groups of tables)
    • It lends itself very naturally to batch updates / mini batches / micro batches which are advantageous for DB2 BLU and other potential targets (such as Hadoop or Spark)

    Cons:

    • Additional table layer (CCD staging) – but this layer only contains the buffered changed data for a limited time interval (at least the interval between refreshes of the target tables)
    • Additional ETL/ELT process: This article describes how to process the changed data from the CCD staging layer to final target – with in-database ETL processing which can be processed very efficiently by modern in-memory databases like IBM DB2 with BLU Acceleration or database appliances like IBM pureData or the DB2 Analytics Accelerator

     

  2. Design patterns

    This section introduces the chosen architecture and design patterns. The following overview figure displays how two exemplary tables (S1, S2) are being synchronized from the source database on the left to the target database on the right.

    architecture

    The Q Replication processes (Q Capture, Q Apply) efficiently and continuously determine changed source records of subscribed tables via the DB2 log interface, transfer these changes to the target platform with minimal latency, and apply the changes to the orange replication target tables in CCD format.

    For each table a SQL MERGE statement is used to periodically refresh the final target tables (T1, T2) from the CCD tables.

    The REFRESH_SYNCH tables (RS, RS_CCD) are metadata tables which record log sequence numbers (LSN) such as the point of the last target table refresh. The REFRESH_SYNCH table is also a replicated table with CCD target table type (to transport the exact log sequence number of source events to the target). Besides the replicated columns it contains columns only available at the target. This way it is possible to record a certain cut-off like an end-of-day transaction at the source, which is available at the target immediately through replication, and which can be leveraged at the target as upper limit for the next refresh of the tables T1 and T2.

    The next figure visualizes the underlying workflow (sequence of steps) which can be automated easily.

    stepbystep

    All starts with a source table (or usually many source tables) at the source database. These tables are being updated continuously by source applications. The orange triangle beneath the source marks the usual table groth.

    All changes replicate to the target platform continuously.

    The red diamond <1> marks the day and processing, executed as simple UPDATE of the REFRESH_SYNCH table at the source. This update of the REFRESH_SYNCH devides all source changes (inserts, updates, deletes) into changes before <1> and changes after <1>.

    Nonetheless, all changes (before <1> and after <1>) replicate to thet target platform, visualized by the thin arrows (the dashed arrows simply refer to all changes that happened after <1>).

    <2> marks the timestamp when the update of the REFRESH_SYNCH tables arrives at the target. Because the replicated target table of the REFRESH_SYNCH table is a Q Replication CCD table, the REFREH_SYNCH target table contains not only the values updated at the source, but also the source log sequence number (LSN) of the according log record.

    Sometime thereafter marked by red diamond <3> the refresh of target tables is performed as SQL MERGE (one SQL MERGE statement per table). The MERGE only takes all source changes committed before <1> into account. <1> is the upper limit. The lower limit is the synch LSN (upper limit) of the previous refresh. The final target tables are not changed continuously, but only at certain points in time with the SQL MERGE.

    After all target tables have been refreshed, the synch LSN (upper limit of the refresh cycle) is saved in the REFRESH_SYNCH table (red diamond <4>). This marks the lower bound for the next cycle.

     

  3. The REFRESH_SYNCH table

    Now getting to the details. As explained above the REFRESH_SYNCH table is necessary to mark a cut-off at the source (e.g. the source day-end transaction) and to replicate this cut-off to the target. At the target, the table is additionally used to track the last successful refresh which then marks the lower bound LSN for the next refresh.

    refresh_synch

    The REFRESH_SYNCH table at the source is a standard DB2 table with 3 columns:

    CREATE TABLE LSN.REFRESH_SYNCH (
    -- optional column to support multi-tenancy
    TENANT VARCHAR(12) NOT NULL,
    -- optional column to support different SYNCH points for different queue maps
    REPQMAPNAME VARCHAR(128) NOT NULL,
    -- column to be updated to signal the cut-off, e.g. the day-end
    SOURCE_SIGNAL_TIMESTAMP TIMESTAMP NOT NULL);

    A day-end processing simply updates the table and sets the SOURCE_SIGNAL_TIMESTAMP to current timestamp.

    At the target, the table REFRESH_SYNCH_CCD is created in the Q Replication CCD format (condensed CCD). This means that the table in this case contains all source columns plus some Q Replication specific columns. These columns are prefixed with ‘IBMSNAP_’.

    Additionally, the table contains four columns to track the last refresh point of the final target tables (MERGE refresh) plus some statistics.

    create table LSN.REFRESH_SYNCH (
    -- replication specific IBMSNAP columns (CCD-Format)
    -- source log record LSN
    IBMSNAP_INTENTSEQ VARCHAR(16) FOR BIT DATA NOT NULL WITH DEFAULT X'00000000000000000000000000000000',
    -- source commit log record LSN
    IBMSNAP_COMMITSEQ VARCHAR(16) FOR BIT DATA NOT NULL WITH DEFAULT X'00000000000000000000000000000000',
    -- I: INSERT, U: UPDATE, D:DELETE
    IBMSNAP_OPERATION CHARACTER(1) NOT NULL WITH DEFAULT 'I',
    -- source ommit Timestamp
    IBMSNAP_LOGMARKER TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,

    -- source columns
    -- optional column to support multi-tenancy
    TENANT VARCHAR(12) NOT NULL,
    -- optional column to support different SYNCH points for different queue maps
    REPQMAPNAME VARCHAR(128),
    -- timestamp column to be updated at the source
    SOURCE_SIGNAL_TIMESTAMP TIMESTAMP not null,

    -- additional target columns
    -- last refresh LSN, set after a successful MERGE refresh, copied from IBMSNAP_COMMITSEQ
    -- lower bound for the next refresh
    LAST_REFRESH_COMMITSEQ VARCHAR(16) FOR BIT DATA NOT NULL WITH DEFAULT X'00000000000000000000000000000000',
    -- timestamp for the above LSN, copied from IBMSNAP_LOGMARKER
    LAST_REFRESH_LOGMARKER TIMESTAMP not null with default '1900-01-01-00.00.00.000001',
    -- timestamp when Q Apply updated this record last time (set by a before trigger, optionally)
    REPL_TIMESTAMP TIMESTAMP not null with default '1900-01-01-00.00.00.000001',
    -- timestamp of last MERGE refresh
    REFRESH_TIMESTAMP TIMESTAMP not null with default '1900-01-01-00.00.00.000001'

    ) ORGANIZE BY ROW;

    Optionally, all updates of the the REFRESH_SYNCH_CCD table can be saved (as log) in a refresh synch history table. This can be accomplished easily using an after update trigger.

     

  4. Creating Q Subscriptions into CCD staging targets

    To create subscriptions from the source tables to the CCD tables in the staging area the following options can be used:

    • Replication Center (DB2 admin clisnt)
    • ASNCLP command language

    For each source table a CCD staging table was created in the DB2 target database upfront as row-organized table. CCD tables are created with all source columns of interest defined with the same data types as used for the source plus 4 replication specific columns. Here’s how the replication specific CCD columns have to be defined:

    IBMSNAP_INTENTSEQ VARCHAR(16) FOR BIT DATA NOT NULL 
    WITH DEFAULT X'00000000000000000000000000000000',
    IBMSNAP_COMMITSEQ VARCHAR(16) FOR BIT DATA NOT NULL
    WITH DEFAULT X'00000000000000000000000000000000',
    IBMSNAP_OPERATION CHARACTER(1) NOT NULL WITH DEFAULT 'I',
    IBMSNAP_LOGMARKER TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,

    In this sample scenario the ASNCLP replication command processor was used to create the subscriptions from the source tables to the CCD tables. It is important to create CCD subscriptions into non-condensed and non-complete CCD tables (CCD CONDENSED OFF COMPLETE OFF):

    • Completeness is not required. The initial load of the final target tables can be accomplished from the source tables (e.g., using LOAD FROM CURSOR)
    • Condensedness explicitly has to be set to NO. Condensedness determines, if the CCD table will contain all changed rows, or if changed rows are being pre-aggregated by the business key. To be able to process scenarios like (insert row, update row, delete row, re-insert row with the same key – before or after the cut-off transaction – the uncondensed set of all changes has to be available in the CCD table

    For each subscription, an ASNCLP CREATE QSUB command like this one for source table ‘ACCOUNT’ was added to the setup script:

    CREATE QSUB SUBTYPE U USING REPLQMAP DB2T_LSN_TO_BLUTGT_LSN ( 
    SUBNAME "ACCOUNT" TENANT_001.ACCOUNT
    OPTIONS
    ALL CHANGED ROWS N
    SUPPRESS DELETES N
    REPLICATE ADD COLUMN Y
    START AUTOMATICALLY NO
    EXIST TARGET NAME TENANT_001.ACCOUNT_CCD
    TYPE CCD CONDENSED OFF COMPLETE OFF
    ERROR ACTION D );

    Finally, a Q subscription for the REFRESH_SYNCH table was created. Again ASNCLP was used. The target table was created upfront as described in the previous section (condensed and complete CCD).

    CREATE QSUB SUBTYPE U USING REPLQMAP SOURCEDB_LSN_TO_BLUTGT_LSN (
    SUBNAME "REFRESH_SYNCH0001" LSN.REFRESH_SYNCH OPTIONS ALL CHANGED ROWS N
    SUPPRESS DELETES N START AUTOMATICALLY YES
    EXIST TARGET NAME LSN.REFRESH_SYNCH
    TYPE CCD CONDENSED ON COMPLETE ON
    TRGCOLS EXCLUDE (LAST_REFRESH_COMMITSEQ, LAST_REFRESH_LOGMARKER, REPL_TIMESTAMP, REFRESH_TIMESTAMP)
    ERROR ACTION D );

     

  5. Creating final target tables

    As explained Q Replication was defined between the source tables and the CCD tables in the staging layer. To acheve the goal of this recipe for all tables a final target table has to be created. The DDL of this table is identical with the DDL of the source table. With DB2 BLU, these final target tables were created as in-memory tables ORGANIZED BY COLUMN.

     

  6. MERGE ELT to update the target tables from the CCD staging tables

    Now to the unique approach of the recipe. A MERGE SQL statement is used to refresh each target table from the Q Replication CCD table very efficiently. With only one in database ELT statement

    • all new rows are moved into the target table from the CCD staging table
    • all rows that already existed in the target table and that were changed since the last refresh are updated in the target table
    • all rows that were deleted at the source are deleted from the target table

    To do so, the MERGE SQL statetement takes the Q Replication CCD table as source and the final BLU table as target. With a staged approach the number of rows actually moved is reduced to the max:

    1. Only those rows of the CCD table are selected that were changed after the last refresh and before the current cut-off (e.g. day-end). All other CCD table rows are ignored. The relevant rows are highlighted as orange part of the CCD table in the below figure
    2. If a row (identified by the business key) was changed multiple times within the interval identified by 1. only the most current change is merged into the target table. The remaining rows are represented by the dashed lines in the below figure

    In an optimistic example lets assume the CCD table holds 6 million rows and the Q Replication continuously adds more changes to the table. Forthermore lets assume that the filter described in 1. reduces the number of rows to 3 million, because some changes are too old (and have not been deleted yet) and some rows are too new for the current processing window. The older rows could have been deleted, the newer rows will be processed within the next cycle.

    Now lets assume that there are some hotspots in the data, which means that a lot of rows have been updated multiple times. The CCD table, because it was defined as non-condensed, contains a row for every change. Because the MERGE statement only moves the most current change for a business key, the number of rows to be merged into the target is reduced significantly, to now lets assume to 500k rows (from 6 million originally). 

    Regardless of how many rows were updated how many times – condensing the changes like described above the MERGE does not cause more update or delete log records than the number of rows in the target table (plus number of inserts). But of course, because all rows are being merged within one SQL statement the log of the target database has to have the capacity to to process a significant number of rows within one transaction. With state of the art DB2 databases ELT processing large transactions are best practice if not even advantageous compared to millions of single insert or update operations.

    condensing

    The code below displays the complete MERGE statement for one table. It moves all relevant records from staging table TENANT_001.ACCOUNT_CCD (MERGE source) to target table TENANT_001.ACCOUNT (MERGE target). The statement implements the algotithm explained above.

    A significant part of the statement is identical for all tables to be processed. Only the sections highlighted in blod are unique for each table. The unique sections contain

    • the schema and name of the ccd table and the target table
    • the list of all columns of the table (same for CCD and target table)
    • the list of the key columns (business key) of the target table

    This means the MERGE statement can be easily generated from a template. Considering this the rollout of such a solution is quite simple.

    MERGE INTO TENANT_001.ACCOUNT target USING 
    (

    -- CCD Staging table: Only the most current change per business key
    -- within the current processing window
    select x.* from
    (

    select
    CCD.*,
    RANK() OVER(PARTITION BY ACCT_NO
    ORDER BY IBMSNAP_COMMITSEQ DESC, IBMSNAP_INTENTSEQ DESC) AS R
    from TENANT_001.ACCOUNT_CCD CCD

    -- filtering the current processing window
    where CCD.IBMSNAP_COMMITSEQ >=
    (SELECT LAST_REFRESH_COMMITSEQ
    FROM LSN.REFRESH_SYNCH WHERE TENANT = 'TENANT_001')
    and CCD.IBMSNAP_COMMITSEQ <
    (SELECT IBMSNAP_COMMITSEQ
    FROM LSN.REFRESH_SYNCH WHERE TENANT = 'TENANT_001')
    ) x
    -- only the row with the highest rank
    WHERE x.R = 1

    ) source

    -- join of 'source' and 'target' using business key
    ON target.ACCT_NO = source.ACCT_NO

    -- INSERT (when not matched and IBMSNAP_OPERATION I / U)
    WHEN NOT MATCHED and source.IBMSNAP_OPERATION IN ('I', 'U') THEN
    INSERT (ACCT_NO, CUST_TYPE, ACCT_CUST_NO, ACCT_BALANCE)
    VALUES (source.ACCT_NO, source.CUST_TYPE, source.ACCT_CUST_NO,
    source.ACCT_BALANCE)

    -- UPDATE (when matched and IBMSNAP_OPERATION I / U)
    WHEN MATCHED and source.IBMSNAP_OPERATION IN ('I', 'U') THEN
    UPDATE SET (CUST_TYPE, ACCT_CUST_NO, ACCT_BALANCE) =
    (source.CUST_TYPE, source.ACCT_CUST_NO, source.ACCT_BALANCE)

    -- DELETE (when matched and IBMSNAP_OPERATION D)
    WHEN MATCHED and source.IBMSNAP_OPERATION = 'D' THEN
    DELETE;

    Remark: This recipe was tested with DB2 10.5 with BLU acceleration. For DB2 for z/OS as replication target, this approach requires DB2 12 for z/OS due to the enhanced capabilities of the MERGE SQL staretement in that release.

    The chosen MERGE approach has proven to be very fast, easy to generate, easy to automate and all together well suited for the chosen in-memory target platform of DB2 with BLU acceleration. Tested alternatives were:

    • Load from Cursor
      • Disadvantages
        • Only valid for Inserts into the target table, Updates and Deletes have to be processed seperately
    • INGEST
      • Advantages
        • Commit count can be specified to limit the transaction size
        • Also supports MERGE approach
      • Disadvantages
        • data to be ingested has to be exported to delimited files, even when the source and target table are within the same database
        • two-step approach for export and ingest required. step 1 for insert & updates (MERGE), step2 for deletes

     

  7. Post processing

    After the MERGE SQL has been successfully executed for all tables, the synchpoint has to be advanced in the REFRESH_SYNCH table – to store the new lower bound for the next cycle.

    Before the synchpoint is advanced, the MERGE SQL statement can be optionally executed multiple times (e.g., after resolving an error condition).

    To advance the synchpoint the following SQL statement has to be executed:

    UPDATE LSN.REFRESH_SYNCH set 
    -- last_refresh_commitseq will be the lower bound for the next cycle
    LAST_REFRESH_COMMITSEQ = IBMSNAP_COMMITSEQ,
    -- optional: timestamp related to COMMITSEQ
    LAST_REFRESH_LOGMARKER = IBMSNAP_LOGMARKER,
    -- optional: only informational
    REFRESH_TIMESTAMP = current timestamp
    where TENANT = 'TENANT_001'
    -- updates all rows for that tenant in case multiple queue maps were registered
    ;

    Remark: All tables with the same synchpoint are transactionally consistent (refreshed up the the same source LSN) after the MERGE SQL has been executed for all tables of that group.

  8. Summary

    Many replication use cases desire real time replication. But sometimes the replication target has to represent just the previous end of business or another exact timestamp so that repeated queries always deliver the same result. This recipe discussed how to achieve that in the most efficient manner with IBM Q Replication.

    The chosen approach used standard and robust Q Replication techniques such as CCD target tables, which are ment to contain the history of changes. In Q Replication terms, non-condensed non-complete CCD tables were used.

    The CCD tables were used as staging area to buffer all changes replicated continuously from the source database to the target database. The actual target tables were refreshed with a single in-database SQL MERGE statement which moved all inserts, updates, and deletes of a certain time window from the CCD layer to the target tables. No ETL tooling or custom programming was required.

    The chosen approach turned out to be very fast and efficient, was easy to generate and to set up, and easy to automate. It was easy to manage with DBA skills.

    Furthermore, the solution lends itself very naturally to batch updates into DB2 with BLU Acceleration, which delivers best performance. The batch size can be a whole day as in the described example, but also could be reduced to mini / micro batches if more frequent updates are required.

    Looking ahead, the architecture consisting of CCD tables and (mini / micro) batch updates can be adopted easily to extend the reach of Q Replication to data lake repositories not natively supported today, such as Hadoop or Spark.

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

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

    IBM Data Replication: A Q Subscription Generator
    https://developer.ibm.com/recipes/tutorials/ibm-data-replication-a-q-subscription-generator/

1 comment on"Continuous high performance DB2 Q Replication plus repeatable query results (stable targets)"

  1. Olaf.Stephan November 06, 2017

    Great article

Join The Discussion