(580) Selected download

A partial download of non-journaled files can be accomplished by QMirror/DB through SQL WHERE clause definition in ZAMTBF.TBSQLW. A new table on the target database <library.file_member_SELECTED> will be created with selected records according to the SQL WHERE clause defined in ZAMTBF.TBSQLW.

Case study

Jack is the QMirror/DB administrator of company ABC who uses MySQL as the target database. A large file SALES/DAILY on AS400 keeps the daily sales report by product. The date of sales is written in field SALES_DATE. A daily statistic procedure on AS400 will start at 00:30 a.m. and completes before 2:00 a.m. He wishes QMirror/DB to download the new records from yesterday after the daily procedure is done and then merge it into the main table.

Following are what Jack should do for the QMirror/DB setting.

1. Take command option 4 to include physical file SALES/DAILY into ZAMTBF

2. Set file SALES/DAILY as a non-replication file:

ZAMTBF.TBREPL = ‘N’

3. Set ZAMTBF.TBJLIB and ZAMTBF.TBJNAM to blank if it is a journaled file and the journal file is not QMIRROR/JRN

4. Set download schedule at 2:00am daily:

ZAMTBF.TBDSCH = 9999, TBNDTM = 020000

5. Set selected download SQL WHERE clause according to AS400 SQL syntax:

ZAMTBF.TBSQLW = ‘SALES_DATE = CURDATE() – 1 DAY’

6. Add a record in ZTBSQL to tell QMirror/DB to delete duplicated records and merge it into table SALES.DAILY after the daily download from AS400 according to MySQL SQL syntax:

INSERT INTO QMIRROR.ZTBSQL (ZQNDX, ZQBOA, ZQKEY, ZQSEQ, ZQSQL)
VALUES (101, ‘A,’ ‘SALES.DAILY_SELECTED;’ 100,
‘DELETE FROM SALES.DAILY WHERE SALES_DATE = CURRENT_DATE – INTERVAL ”1 DAY”; INSERT INTO SALES.DAILY (SELECT * FROM SALES.DAILY_SELECTED);’)

7. Start QMirror/DB

8. QMirror/DB will download full table of SALES.DAILY for the first time

Previous (570) Install QMirror/DB as a Windows service
Next (585) Define mapping table in the PC database