Db2 for z/OS as Source - DSS 6 | Data Source Solutions Documentation

Documentation: Db2 for z/OS as Source - DSS 6 | Data Source Solutions Documentation

Db2 for z/OS as Source

Capture

Data Source Solutions DSS supports capturing changes from Db2 for z/OS. This section describes the configuration requirements for capturing changes from Db2 for z/OS location. For the list of supported Db2 for z/OS versions, from which DSS can capture changes, see Capture changes from location in Capabilities.

Table Types

DSS supports capture from the following table types in Db2 for z/OS:

Regular Tables
Compressed Tables
Partitioned Tables
History Tables
Archive Tables

Capture Methods

DSS allows only the Logging Stored Procedures method for capturing (Capture) changes from Db2 for z/OS.

Logging Stored Procedures

In this capture method (Capture_Method=SQL), DSS uses IFI 306 via DSS's stored procedures to capture data from Db2 for z/OS location.

Grants for Capture

The following grants are required for capturing changes from Db2 for z/OS:

To create stored procedures, the Db2 for z/OS database user (who creates the stored procedures) must be granted createin privilege on the schema.
```
grant createin on schema myschema to authid;
```
To read information from the transaction log, the DSS database user must be granted monitor2 privilege.
```
grant monitor2 to username;
```
To execute stored procedures created by the authid (Db2 for z/OS database) user, the DSS database user must be granted execute on procedure privilege for the stored procedures - dss.dsscaplg and dss.dsscapnw.
```
grant execute on procedure dss.dsscaplg to username; 
grant execute on procedure dss.dsscapnw to username;
```

To fetch information about the Db2 for z/OS installation, the DSS database user must be granted select privilege for the following SYSIBM tables.

grant select on table sysibm.sysauxrels to <em>username</em>;

grant select on table sysibm.syscolumns to <em>username</em>;

grant select on table sysibm.sysdatabase to <em>username</em>;

grant select on table sysibm.sysforeignkeys to <em>username</em>;

grant select on table sysibm.sysindexes to <em>username</em>;

grant select on table sysibm.syskeys to <em>username</em>;

grant select on table sysibm.sysparms to <em>username</em>;

grant select on table sysibm.sysrels to <em>username</em>;

grant select on table sysibm.sysroutines to <em>username</em>;

grant select on table sysibm.syssynonyms to <em>username</em>;

grant select on table sysibm.systablepart to <em>username</em>;

grant select on table sysibm.systables to <em>username</em>;

Supplemental Logging

Supplemental logging can be enabled by executing Activate Replication with option Supplemental Logging (dssactivate with option -ol).

To enable supplemental logging, the DSS database User should be either owner of the replicated tables or have DBADM or SYSADM or SYSCTRL authority.

Alternatively, executing the following command on replicated tables has the same effect:

alter table <em>tablename</em> data capture changes;

Capturing DDL Changes

**Since** 6.1.5/7

DSS supports capturing DDL changes using the action AdaptDDL. For this, you must enable supplemental logging. To enable supplemental logging, set the DATA CAPTURE CHANGES attribute on the SYSIBM.SYSTABLES and SYSIBM.SYSINDEXES system catalog tables. However, this is not required for ALTER TABLE ADD COLUMN statement. For more information about the DDL statement behavior and requirements, see the section Behavior for Specific DDL Statements and Capture DBMSs.

Use the following SQL statements to set the DATA CAPTURE CHANGES attribute:

ALTER TABLE SYSIBM.SYSTABLES DATA CAPTURE CHANGES;
ALTER TABLE SYSIBM.SYSINDEXES DATA CAPTURE CHANGES;

Capture Limitations

This section describes the limitations for capturing changes from Db2 for z/OS using DSS.

DSS does not support Capture from tables updated using the following Db2 utilities - CHECK DATA with DELETE option, LOAD, RECOVER with point-in-time options, and Reorg/Discard.
DSS does not support Capture from tables with EDITPROC.
DSS does not support Capture from tables with FIELDPROC.
DSS does not support Capture from tables with columns encrypted using Db2 built-in functions

Compare and Refresh from Db2 for z/OS

DSS allows you to perform only Compare and Refresh from Db2 for z/OS database (without using Capture). This section describes the configuration requirements for performing Compare and Refresh from Db2 for z/OS location.

Grants for Compare and Refresh from Db2 for z/OS

This section lists the grants required for performing only Compare and Refresh from Db2 for z/OS database.

The DSS database User must be granted the select privilege to read from the replicated tables:
```
grant select on table myschema.mytable to username;
```