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:
- GRANT MONITOR2 TO < sqdata_user>;
- GRANT EXECUTE ON PLAN SQDV4000 TO < sqdata_user>;
- GRANT SELECT ON SYSIBM.SYSTABLES TO < sqdata_user>;
- GRANT SELECT ON SYSIBM.SYSCOLUMNS TO < sqdata_user>;
- GRANT SELECT ON SYSIBM.SYSINDEXES TO < sqdata_user>;
- GRANT SELECT ON SYSIBM.SYSKEYS TO < sqdata_user>;
- GRANT SELECT ON SYSIBM.SYSTABLESPACE TO < sqdata_user>;
- 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:
- SYSIBM.SYSTABLES
- SYSIBM.SYSCOLUMNS
Db2 Dynamic Refresh requires Read access to any Table to be refreshed
- GRANT SELECT on <schema.tablename>TO < sqdata_user>; See Db2 Dynamic Capture Based Refresh.Note:
- 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> DATA CAPTURE CHANGES; - Db2 Version 12 also requires implementation of the 10 Byte Log Sequence Number (LSN). If that was not completed prior to implementing the Db2 Change Data Capture see the section Upgrading Db2 to 10 Byte LSN for details.
- The Db2 Dynamic Refresh functionality in Connect CDC SQData 4.1 requires Db2 V12 R1 M500 in order to support processing "slices" of the source table based on row count or From and to Key values. See Dynamic Capture Based Refresh for details.