Sample Asynchronous Oracle to DB2 Data Replication
|
| produced and supported by |
| DRMT |
Data Resource
Management Technology |
| DoIT |
Division of
Information
Technology
UW - Madison |
|
Advantages:
- It’s pretty easy. Having done one, the rest are cookie-cutter projects. The 3 triggers are always the same. The ‘move-log-recs-to-mainframe’ procedure is always the same.
- It’s reliable. It’s designed to survive any outage of Oracle, UNIX, TCP/IP, MVS, or DB2. If any resource becomes unavailable, the queueing approach resumes replication as soon as the resource becomes available.
- It's got built-in unit-of-work integrity. (The Oracle Transparent Gateway has built-in two-phase commit to ensure unit-of-work integrity.)
- In one sense, scheduling of data transfers is not an issue. The data is always available on MVS -- in fully relational form -- even when the master tables in Oracle are not available.
- Scheduling is easy. The sample used Oracle’s DBMS_JOB package. UNIX cron could be used just as easily (and actually more flexibly).
- Scheduling is tune-able. Based on the needs of an application, replication can occur at different intervals -- every minute, every hour, not on weekends, etc.
- The API is completely familiar -- SQL and PL/SQL
- Replicated tables are fully recoverable with no great complication. Even in the case of some unknowable queueing catastrophe -- say, a programming error -- it’s easy to see how to recreate the entire replicated table. Just copy it whole, then fix and restart the queueing.
Demo Code (also accessible to PUBLIC in the UWT1 'ORASAMP' schema)
The Oracle tables:
1. A little demo table:
'ORASAMP.Q' table
Columns
*Q_KEY A unique key.
STRING_O_DATA A variable length string of data.
NUMERIC_DATA Some number... sample numeric data.
2. A log table designed to capture every insert, update and delete performed
against table Q. By adding triggers to table Q, we automatically insert
data into Q_log as follows:
- for an insert to Q, all new Q data
- for a delete from Q, all old Q data
- for an update to Q, both old and new Q data images
'ORASAMP.Q_LOG' table
Columns
*LOG_SEQ Unique key for each 'event' logged - one log_seq for each
INSERT, UPDATE, or DELETE affecting table Q.
*LOG_TYPE Second part of unique key.
Valid values ('Isrt', 'Dlet', 'Updt,before','Updt, after')
An UPDATE to Q inserts TWO rows into Q_LOG.
REPLICATED Y or N flag.
Q_KEY Q_KEY from base table Q.
STRING_O_DATA STRING_O_DATA from base table Q.
NUMERIC_DATA NUMERIC_DATA from base table Q.
2a. There’s also an Oracle sequence ('orasamp.q_log_seq') which is used by
the triggers to generate unique, ordered sequences of numbers for
partially keying Q_log (see column LOG_SEQ above).
These are the 3 triggers on table Q:
1. CREATE OR REPLACE TRIGGER orasamp.q_aftr_isrt
AFTER INSERT
ON orasamp.q
FOR EACH ROW
BEGIN
INSERT
INTO orasamp.q_log
VALUES (orasamp.q_log_seq.NEXTVAL,
'Isrt',
'N', -- No, not replicated yet
:NEW.q_key,
:NEW.string_o_data,
:NEW.numeric_data);
END;
2. CREATE OR REPLACE TRIGGER orasamp.q_aftr_dlet
AFTER DELETE
ON orasamp.q
FOR EACH ROW
BEGIN
INSERT
INTO orasamp.q_log
VALUES (orasamp.q_log_seq.NEXTVAL,
'Dlet',
'N', -- No, not replicated yet
:OLD.q_key,
:OLD.string_o_data,
:OLD.numeric_data);
END;
3. CREATE OR REPLACE TRIGGER orasamp.q_aftr_updt
AFTER UPDATE
ON orasamp.q
FOR EACH ROW
BEGIN
INSERT
INTO orasamp.q_log
VALUES (orasamp.q_log_seq.NEXTVAL,
'Updt, before',
'N', -- No, not yet replicated
:OLD.q_key,
:OLD.string_o_data,
:OLD.numeric_data);
INSERT
INTO orasamp.q_log
VALUES (orasamp.q_log_seq.CURRVAL,
'Updt, after',
'N', -- No, not yet replicated
:NEW.q_key,
:NEW.string_o_data,
:NEW.numeric_data);
END;
The mainframe table:
On the mainframe, there is a DB2 table called 'UW.Q_Replicated'.
It’s structure is identical to Q. The whole intent of the demo
application is to maintain UW.Q_Replicated as an accurate copy of
the Oracle table Q, with a small propagation delay.
The gateway view:
Within Oracle, there is an updateable view of the mainframe table.
This view is like any other transparent gateway view of DB2
-- whereas the DB2 table is owned by 'UW', the Oracle view of it
is owned by 'ORA2DB2'.
The data transfer application:
The code to transfer the captured data from Q_log to mainframe is found
in the Oracle stored procedure 'orasamp.move_logrecs_to_mf'.
This is the code:
--------------------------------------------------
PROCEDURE move_logrecs_to_mf IS
--------------------------------------------------
CURSOR q_log_csr IS -- retrieves every non-replicated log record in log sequence
SELECT log_seq,
log_type,
q_key,
string_o_data,
numeric_data
FROM orasamp.q_log
WHERE replicated = 'N'
ORDER BY log_seq ASC, log_type DESC;
old_q_key NUMBER;
---------------------------------------------------------------------
BEGIN
FOR x IN q_log_csr
LOOP
IF x.log_type = 'Isrt' THEN
INSERT INTO ora2db2.q_replicated
VALUES (x.q_key,
x.string_o_data,
x.numeric_data);
UPDATE q_log /* in actual practice, you might prefer to DELETE the log rec */
SET replicated = 'Y'
WHERE log_seq = x.log_seq;
ELSIF x.log_type = 'Dlet' THEN
DELETE FROM ora2db2.q_replicated
WHERE q_key = x.q_key;
UPDATE q_log
SET replicated = 'Y'
WHERE log_seq = x.log_seq;
ELSIF x.log_type = 'Updt, before' THEN
old_q_key:= x.q_key; /* just save the old key */
ELSE /* must be 'Updt, after' */
UPDATE ora2db2.q_replicated
SET q_key = x.q_key,
string_o_data = x.string_o_data,
numeric_data = x.numeric_data
WHERE q_key = old_q_key;
UPDATE q_log
SET replicated = 'Y'
WHERE log_seq = x.log_seq; /* sets both rows
| (‘Updt, before’ & ‘Updt, after’)
| to 'Y' [ Yes, replicated]
*/
END IF;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
/* Just trap any error here. Rollback to any previous commit point */
/* and exit. Don't panic; */
ROLLBACK;
RETURN;
END move_logrecs_to_mf;
The transfer job schedule:
The above procedure is executed every 5 minutes
using one Oracle background process
and the Oracle builtin package 'DBMS_JOB'.
The submission program is as follows:
DECLARE
job# BINARY_INTEGER;
BEGIN
dbms_job.submit(job#, 'orasamp.move_logrecs_to_mf;', SYSDATE, 'SYSDATE + 1/288');
END;
This executes the 'move_logrecs_to_mf' procedure beginning today (SYSDATE)
repeating at an interval of 5 minutes (1/288th of a day = 5 minutes).
The status of the submitted job can be monitored with standard Oracle views.
The shortest possible DBMS_JOB interval is one second.
Note that a 5-minute interval means a maximum propagation delay of 5 minutes.
The average delay would be 2.5 minutes.