Data Replication Via Triggers, Procedures, XML and WebSphere MQ

(1)

Data Replication Via

Triggers, Procedures, XML

and WebSphere MQ

DMC13SN

Database, Application Development,

and Information management

(2)

Abstract

This session focuses on a real-world application using Advantage™

CA-Datacom® DB SQL triggers and

procedures to push data changes to other database systems and platforms with XML and Websphere MQ. Learn how the data changes are tracked. See what methods are used to format the XML documents. Learn about using Websphere MQ

messaging. Attend this session to see how these pieces fit the need for a large

(3)

Biography

Owen Williams

QED Business Systems Ltd.

Owen is an independent consultant specializing in the Advantage CA-Datacom product family since 1986. For almost 10 years he was a member of Computer Associates’ European Technical

Support organization. For the last 5 years he has been a senior consultant at QED Business

Systems Ltd.

(4)

Agenda

The requirement

Options considered The pre-requisites

SQL Triggers and Procedures The Polling Started Task

(5)

The Requirement

“Islands of technology”

– “Master” Data resides on several mainframe databases

• Advantage™ CA-Datacom®

• Advantage™ CA-IDMS®

• DB2

Data is required to feed applications

residing on Unix and Windows platforms

– Heavy usage so requires a local copy of the data (ruling out ODBC etc.)

(6)

The Existing Solution

Home-grown batch applications

– Read all data in various tables

– Reformat data suitable for target platform

(7)

Problems With the Existing

Solution

Long-running batch process causing delays to batch window

Must be reworked for each new application/table

Unchanged data is currently re-sent on each refresh

(8)

Goals of the New Solution

Reduce the size of the batch application

– Eventually eliminate it

Only send new/changed/deleted data to target

Format of data exchange should be

industry standard and rDBMS independent Data delivery should be as close to

real-time as possible

– Only for committed tasks

Eventually a two-way replication of changes

(9)

Complications

Client has made use of non-relational data structures

– Redefines

– Occurs

(10)

Agenda

9 The requirement

Options considered The pre-requisites

(11)

Options Considered - RXX

Run a SPILL at regular intervals

Develop application to call CA supplied READRXX API

– Release independent data format Data transfer via WebSphere MQ

(12)

RXX - Disadvantages

Data is not processed until next SPILL interval

– Goal is to be real-time

High SPILL frequency may cause problems during quiet periods

– No data available to SPILL causes job failure

High “bursts” of activity for WebSphere MQ when large volumes of data to be

processed

Impossible to determine which redefinition to use in some cases

(13)

Options Considered - Replication

Former CA-Datacom replication product

– Replication was via Advantage™ Ingres®

– No longer supported/developed

– Overly complex for the requirement

– Difficulty in handling non-relational data structures

(14)

Options Considered – Triggers and

Procedures

Industry standard SQL implementation

Traps each maintenance request against the tables to be replicated

– SQL Insert/Update/Delete

– Record-At-A-Time

ADDIT/UPDAT/DELET

– Set-At-A-Time

FOR NEW/ UPDATE/DELETE

With Advantage CA-Datacom r11, it can handle non-relational data structures

(15)

Agenda

9 The requirement

9 Options considered The pre-requisites

(16)

The Pre-requisites

Advantage CA-Datacom r11 at SP1

The ability to obtain the current SQLCA-LUWID within the SQL procedure

– DB solution 293 (PTF TB30293)

– SQL solution 103 (PTF TS30103) Additional recommended PTFs

– DB solution 279 (PTF TB30279)

• A03 abend at MUF shutdown

– SQL solution 100 (PTF TS30100)

• Incorrect return codes in ML dumps

– DQ solution 3 (PTF QO64025)

(17)

The Pre-requisites (cont.)

MUF start-up procedure JCL changes

– //SYSOUT DD SYSOUT=*

– //CEEDUMP DD SYSOUT=* MUF start-up parameter changes

(18)

Agenda

9 The requirement

9 Options considered

9 The pre-requisites

(19)

Triggers and Procedures

Triggers and Procedures operate under the same LUW as the original request The Procedure stores details of the maintenance request in a “replication” table

When the underlying application reaches end of LUW

– Committed requests remain in the replication table

– Rolled Back requests are removed from the replication table

(20)

Trigger Definitions

Each table to be replicated requires three trigger definitions

– Insert – with “after” image of row

– Update – with “after” image of row

– Delete – with “before” image of row Three are required because there is no

way to tell within the procedure which type of maintenance command triggered it

The procedure will expect two parameters

– Maintenance command type

(21)

Sample Table to be Replicated

02 COMPLEX-SAMPLE. 03 PRIMARY-KEY PIC X(10). 03 GROUP-FIELD. 04 INTEGER-UNSIGNED PIC 9(7). 04 INTEGER-SIGNED PIC S9(6). 03 SUBJECT-OF-REDEF-GROUP. 04 TEXT-COLUMN-1 PIC X(5). 04 TEXT-COLUMN-2 PIC X(5).

03 REDEFINES-WITH-OCCURS REDEFINES SUBJECT-OF-REDEF-GROUP PIC 9(5) OCCURS 2 TIMES. 03 OCCURS-WITH-DECIMALS PIC S9(5)V9(2) OCCURS 4 TIMES. 03 REDEF-TYPE-CODE PIC X(1). 03 REDEF-TYPE-A.

04 TYPE-A-TEXT1 PIC X(5). 04 TYPE-A-INTEGER PIC S9(5). 04 TYPE-A-TEXT2 PIC X(5).

03 REDEF-TYPE-B REDEFINES REDEF-TYPE-A. 04 TYPE-B-GROUP.

(22)

Trigger Definition - Insert

CREATE TRIGGER SYSUSR.COMPLEX_SAMPLE_INS AFTER INSERT ON SYSUSR.COMPLEX_SAMPLE REFERENCING NEW ROW AS IMAGE_DATA FOR EACH ROW EXECUTE PROCEDURE

SYSUSR.COMPLEX_SAMPLE ('INS',

IMAGE_DATA.PRIMARY_KEY || CAST(IMAGE_DATA.INTEGER_UNSIGNED AS CHAR(7) WITHOUT CONVERSION)

||

CAST(IMAGE_DATA.INTEGER_SIGNED AS CHAR(6) WITHOUT CONVERSION) || IMAGE_DATA.TEXT_COLUMN_1 || IMAGE_DATA.TEXT_COLUMN_2 || IMAGE_DATA.OCCURS_WITH_DECIMALS || IMAGE_DATA.REDEF_TYPE_CODE || IMAGE_DATA.TYPE_A_TEXT1 || CAST(IMAGE_DATA.TYPE_A_INTEGER AS CHAR(5) WITHOUT CONVERSION) || IMAGE_DATA.TYPE_A_TEXT2);

(23)

Trigger Definition Notes - Insert

We need to pass the whole row image “un-edited”

– Group fields are ignored

– Occurs fields are passed as a block of data

• See OCCURS_WITH_DECIMALS

– Only the primary definition is passed

• Not the redefines

– Numeric fields are CAST WITHOUT CONVERSION

(24)

Trigger Definition - Update

CREATE TRIGGER SYSUSR.COMPLEX_SAMPLE_UPD AFTER UPDATE ON SYSUSR.COMPLEX_SAMPLE REFERENCING NEW ROW AS IMAGE_DATA FOR EACH ROW EXECUTE PROCEDURE SYSUSR.COMPLEX_SAMPLE ('UPD',

||

(25)

Trigger Definition Notes - Update

Same as for the insert except the command type is “UPD”

This client decided just to send the AFTER image

– To reduce volume of data transmitted

– OK in this case as mainframe is considered “master” of data

– Other sites might need to send BEFORE image for validation

(26)

Trigger Definition - Delete

CREATE TRIGGER SYSUSR.COMPLEX_SAMPLE_DEL AFTER DELETE ON SYSUSR.COMPLEX_SAMPLE REFERENCING OLD ROW AS IMAGE_DATA FOR EACH ROW EXECUTE PROCEDURE

SYSUSR.COMPLEX_SAMPLE ('DEL',

||

(27)

Trigger Definition Notes - Delete

The command type is “DEL”

This time we are REFERENCING OLD ROW

– The whole row is passed to the

procedure so we can share it between all three triggers

This client only wants to pass the primary key of the row (done within the procedure)

– OK in this case as mainframe is considered “master” of data

(28)

Procedure Definition

CREATE PROCEDURE SYSUSR.COMPLEX_SAMPLE (IN ORIGINAL_COMMAND CHAR(3),

IN IMAGE_DATA CHAR(77)) PARAMETER STYLE DATACOM SQL MODIFIES SQL DATA

LANGUAGE COBOL

(29)

Procedure Definition Notes

Single procedure shared between the three triggers

– If you want the BEFORE image on

UPDATE then you will need a separate procedure for that

Make sure your procedure name does not match the ENTITY-NAME of a table

– OK in this case as we have underscore instead of hyphen

– Otherwise your table could be destroyed by accident!

(30)

Procedure Program Functions

For the row passed by the trigger

– Determine which MUF we’re running against (select * from muf_identity)

• Also causes SQLCA-LUWID to be populated

– Assign a unique sequence number to this request

• Cannot use REQSEQNO as that might wrap

– Determine which REDEFINES group is in use for each storage area

– Insert details of request into replicator_data table

(31)

Procedure Compilation

Procedure must be available in MUF STEPLIB

– CUSLIB seems appropriate

– Or another APF authorized library Pre-processor *$DBSQLOPT options

– COBMODE=VSCOB2

– PROCSQLUSAGE=MODIFIES

– USRNTRY=NONE

(32)

Procedure LE Options

CEEUOPT CSECT CEEUOPT AMODE 31 CEEUOPT RMODE ANY CEEXOPT ABTERMENC=(ABEND), ALL31=(ON), ANYHEAP=(1K,1K,ANYWHERE,FREE), BELOWHEAP=(1K,1K,FREE), HEAP=(32K,32K,ANYWHERE,FREE,8K,4K), LIBSTACK=(1K,1K,FREE), RTEREUS=(ON), STACK=(4K,4K,ANY,KEEP), STORAGE=(00,NONE,00,0K), TERMTHDACT=(UADUMP), TRAP=(ON,NOSPIE) END

(33)

REPLICATOR-DATA Table

---Level Field-Name (Numeric Attrib Preci Type Len Repeat Just Rdf Null Fill Sign Typ-Num ========================== T O P ================================== 1 MUF-NAME 00008 CHAR 00008 00001 L N N 1 LUW-ID 00008 CHAR 00008 00001 L N N 1 SEQUENCE-NBR 00000 BIN 00004 00001 R N N Y P 1 TABLE-NAME 00032 CHAR 00032 00001 L N N 1 COMMAND-TYPE 00003 CHAR 00003 00001 L N N 1 REDEFINES-INDICATORS 00010 CHAR 00010 00001 L N N 1 BEFORE-AND-AFTER-IMAGES 04096 VARC 04098 00001 L N N ======================= B O T T O M ===============================

(34)

Table Definition Notes

We have allowed for up to 10 storage areas to be the subject of REDEFINES Image Data is 4k since that exceeds the maximum row length in use at this site

– May need to adjust buffer sizes Key is MUF-NAME, LUW-ID and SEQUENCE-NBR

Compression is on for this table Make sure allocation is sufficient

– Otherwise, underlying applications will receive RC 94(100) when table fills

(35)

Agenda

9 The requirement

9 SQL Triggers and Procedures The Polling Started Task

(36)

Started Task Application Structure

REPLSTC (Cobol SQL/Native) REPLCON (Assembly) REPLMSG (Cobol SQL) REPLDLY (Cobol) REPLD24 (Cobol) REPLDSF (Cobol DSF) REPLMQS (Cobol MQ)

(37)

Polling Started Task - REPLSTC

Controlled by SYSIPT parameters Wakes up every nnn seconds

Looks for data in the REPLICATOR-DATA table

– Using RAAT LOCxx commands to avoid exclusive control conflicts

Checks whether that LUW-ID is still active in MUF

– Must ignore the LUW until it has been committed successfully

(38)

Polling Started Task - REPLCON

The only Assembly module

– All others are COBOL for ease of maintenance by the client

Provides a console interface

– For orderly shutdown

– May be enhanced to provide ability to vary start-up parameters

(39)

Polling Started Task – REPLD24

Very small program

Converts 31-bit parameter to 24-bit

– AMODE 31

– RMODE ANY

– CBL DATA(24) Required because

– Calling programs use very large storage areas so must be 31-bit

– But DSF and VPE must be called from AMODE 24 RMODE 24

(40)

Polling Started Task – REPLDLY

Very small program

Calls LE-supplied module ILBOWAT0

– “Sleep” routine for COBOL Required because

– Calling programs use very large storage areas so must be 31-bit

– But ILBOWAT0 must be called with DATA(24) parameters (can be

(41)

Polling Started Task – REPLDSF

Calls the Advantage CA-Datacom Dictionary Service Facility

AMODE 24 and RMODE 24 required Connection established at STC startup Retrieves table structures upon first encounter

DSF Level is 4

– Documentation has only recently been updated

Table structures are cached in messaging module for performance

(42)

Polling Started Task – REPLMSG

Central Messaging module

Retrieves all rows from the REPLICATOR-DATA table for the selected LUW-Id

Calls DSF for each new table found

– Only if the table has not been encountered since STC start-up

– Maps the row image data layout

Builds an XML document for the complete LUW

– If document exceeds 4M, then it is split due to IBM WebSphere MQ limit

(43)

Polling Started Task – REPLMSG

(cont.)

For each row retrieved…

– XML “header” shows command type and table name

– Group fields are reflected in XML structure

– Repeating fields (occurs) are repeated XML elements

– Primary/Redefines structure reflects selection made in the SQL Procedure Requests are presented in the order in which the update originally occurred

(44)

Sample Table to be Replicated

(Reminder)

02 COMPLEX-SAMPLE. 03 PRIMARY-KEY PIC X(10). 03 GROUP-FIELD. 04 INTEGER-UNSIGNED PIC 9(7). 04 INTEGER-SIGNED PIC S9(6). 03 SUBJECT-OF-REDEF-GROUP. 04 TEXT-COLUMN-1 PIC X(5). 04 TEXT-COLUMN-2 PIC X(5). 03 REDEFINES-WITH-OCCURS REDEFINES SUBJECT-OF-REDEF-GROUP

PIC 9(5) OCCURS 2 TIMES. 03 OCCURS-WITH-DECIMALS PIC S9(5)V9(2) OCCURS 4 TIMES. 03 REDEF-TYPE-CODE PIC X(1). 03 REDEF-TYPE-A.

04 TYPE-A-TEXT1 PIC X(5). 04 TYPE-A-INTEGER PIC S9(5). 04 TYPE-A-TEXT2 PIC X(5). 03 REDEF-TYPE-B REDEFINES REDEF-TYPE-A.

04 TYPE-B-GROUP. 05 TYPE-B-TEXT1 PIC X(2). 05 TYPE-B-DECIMAL-OCCURS PIC S9(3)V9(2) OCCURS 2 TIMES.

(45)

(46)

XML Document Structure Note 1a

XML “document header” has control information

– MUF name and LUW-Id

– Flag to indicate whether this is the last document for this LUW-Id (for 4M limit)

– Sequence number of this document within the LUW-Id

<REPLR_Req> encapsulates each request in the LUW

“Request Header” shows command type and table name

XML Tag names are DataDictionary field names

(47)

XML Document Structure Note 1b

REDEFINES-WITH-OCCURS using redefinition

– Selected by the triggered SQL

procedure because fields contain numeric data

OCCURS-WITH-DECIMALS shows examples of

– Repeating group

– Sign has been unpacked

(48)

(49)

XML Document Structure Note 2

Different table this time

– CA Supplied SHIPTO sample

This one is a DELETE so only the primary key is generated

(50)

(51)

XML Document Structure Note 3

SUBJECT-OF-REDEFINES using primary definition

– Selected by the triggered SQL

procedure because fields contain non-numeric data

REDEF-TYPE-B selected by SQL

(52)

Agenda

9 The requirement

9 SQL Triggers and Procedures

9 The Polling Started Task The MQ Message interface

(53)

MQ Message Interface – REPLMQS

Once the XML document set is complete

– Pointer to document passed to REPLMQS

– MQ connection established on first MQPUT request

– MQ manager and queue/namelist defined as SYSIN parameters

– NAMELIST is expanded if necessary

• Allows transmission to multiple targets

(54)

MQ Message Interface –

REPLMQS (cont.)

Commands used – MQCONN – MQOPEN – MQINQ – MQPUT – MQCMIT – MQCLOSE – MQDISC

(55)

So What Happens at the Other End?

XML documents are retrieved from MQ by middleware solution

– In this case Aleri

– Data transformation is done here

Requests are repeated against the target rDBMS

(56)

Future Plans

Current solution is one-way replication

– Advantage CA-Datacom is “master” of the data

Plans to introduce two-way replication, but issues to consider

– Problem of “echoed” requests when replicator notices the Advantage CA-Datacom update

• Upward replicator must identify itself as source of data to prevent downward

replication

– How to deal with collisions

(57)

(58)

Session Evaluation Form

After completing your

session evaluation form ... ... place it in the basket at the back of

the room.

Please left justify the session number