Prepare Db2/z for capture - connect_cdc_sqdata - Latest

The Db2 Log Reader Capture requires special user privileges and preparation to access and read the Db2 Recovery Logs using the Db2 Instrumentation Facility Interface (IFI) calls. Version 4 of Connect CDC (SQData) also requires some system tables to be captured to support Schema Evolution.

The following GRANTS are required :

1. GRANT MONITOR2 TO < sqdata_user>;
2. GRANT EXECUTE ON PLAN SQDV4000 TO < sqdata_user>;
3. GRANT SELECT ON SYSIBM.SYSTABLES TO < sqdata_user>;
4. GRANT SELECT ON SYSIBM.SYSCOLUMNS TO < sqdata_user>;
5. GRANT SELECT ON SYSIBM.SYSINDEXES TO < sqdata_user>;
6. GRANT SELECT ON SYSIBM.SYSKEYS TO < sqdata_user>;
7. GRANT SELECT ON SYSIBM.SYSTABLESPACE TO < sqdata_user>;
8. GRANT SELECT ON SYSIBM.SYSDATATYPES TO < sqdata_user>; Note: Required beginning with V4.1.36

Db2 Reorg and Load procedures may need to be updated :

• KEEPDICTIONARY=YES parameter must be used by all Db2 REORG and LOAD Utilities. If the CDC process is run asynchronously, for some reason gets behind or is configured to recapture older logs, the proper Compression Dictionary must be available.

Schema Evolution requires DATA CAPTURE CHANGES on Two (2) Catalog Tables :

1. SYSIBM.SYSTABLES
2. SYSIBM.SYSCOLUMNS

Db2 Dynamic Refresh requires Read access to any Table to be refreshed

• GRANT SELECT on <schema.tablename> TO < sqdata_user>;

Notes:

• A common database request module (DBRM) SQDDDB2D ships as part of the product distribution and a Bind must be performed on the SQDV4000 Package and Plan. Use the BINDSQD member in the CNTL Library to bind the Package and Plan to Db2.

• Each Db2 table to be captured also requires:

  ALTER TABLE <schema.tablename> DATACAPTURECHANGES;

//DB2GRANT JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID
//*
 //*--------------------------------------------------------------------
//* Grant Db2 Authorizations for SQDATA Userid(s)
 //*--------------------------------------------------------------------
//* Note: MONITOR2 for IFI Calls
//* Execute on the SQDATA PLAN SQDV4000
//* SELECT on Catalog Table SYSIBM.SYSTABLES
//* SELECT on Catalog Table SYSIBM.SYSCOLUMNS
//* SELECT on Catalog Table SYSIBM.SYSINDEXES
//* SELECT on Catalog Table SYSIBM.SYSKEYS
//* SELECT on Catalog Table SYSIBM.SYSTABLESPACE
 //*--------------------------------------------------------------------
//*
//DB2GRANT EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=DSNC10.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBCG)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA11) -
LIB('DSNC10.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
GRANT MONITOR2 TO <db2_user>;
GRANT EXECUTE ON PLAN SQDV4000 TO <db2_user>;
GRANT SELECT ON SYSIBM.SYSTABLES TO <db2_user>;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO <db2_user>;
GRANT SELECT ON SYSIBM.SYSINDEXES TO <db2_user>;
GRANT SELECT ON SYSIBM.SYSKEYS TO <db2_user>;
GRANT SELECT ON SYSIBM.SYSTABLESPACE TO <db2_user>;