When replication requires table space REORG after ALTER TABLE (z/OS)

Db2 Version 12

If the source database is Db2 12, no REORG is required for altered tables because Db2 sends the before and after versions of the log record. In rare cases you might need a REORG if Q Capture or Capture is behind in reading the Db2 logs and stopped, and updates to the table are followed by a REORG of the table space. In this case the subscription or registration fails with a decode error because Db2 cannot provide the before values in the current version, and the capture program did not record any information about the old version because the subscription or registration was not active when the table was altered.

Db2 pre-Version 12

Under certain situations before V12, ALTER TABLE statements prevent replication from maintaining an accurate schema history for the table in the IBMQREP_TABVERSION and IBMQREP_COLVERSION control tables. As a result, the Q Capture or Capture program prevents activation of the Q subscription or registration for the altered table and issues error message ASN176E. Performing a REORG on the table space remedies the situation.

For Db2 pre-V12 tables, the rules for table space REORG after schema alterations differ depending on whether the table space uses basic row format (BRF) or reordered row format (RRF). If a partitioned table space includes both BRF and RRF formats, replication treats the entire table space as RRF.

BRF table spaces

Table 1 summarizes the situations where a REORG is required for BRF table spaces.

Table 1.

Operation	Table actively being replicated?	REORG required?
ALTER TABLE ADD COLUMN	No	No
	Yes
ALTER TABLE ALTER COLUMN SET DATATYPE	No	Yes
	Yes	No

You can use the following queries to determine which BRF table spaces require a REORG. Replace capture_schema with the schema of the Q Capture or Capture control tables:

Q Replication

SELECT DISTINCT TAB.CREATOR,TAB.NAMEFROM
SYSIBM.SYSCOLUMNS COL, SYSIBM.SYSTABLEPART TS,
capture_schema.IBMQREP_SUBS SUBS,SYSIBM.SYSTABLES TAB
WHERE (TAB.CREATOR = SUBS.SOURCE_OWNER) AND
(TAB.NAME = SUBS.SOURCE_NAME) AND
(TAB.DBNAME = TS.DBNAME) AND
(TAB.TSNAME = TS.TSNAME) AND
(TAB.CREATOR = COL.TBCREATOR) AND
(TAB.NAME = COL.TBNAME) AND
(TS.FORMAT = ' ') AND
(COL.ALTEREDTS > COL.CREATEDTS)AND
(COL.ALTEREDTS > TS.REORG_LR_TS)
GROUP BY TAB.CREATOR,TAB.NAME;

SQL Replication

SELECT DISTINCT TAB.CREATOR,TAB.NAMEFROM
SYSIBM.SYSCOLUMNS COL, SYSIBM.SYSTABLEPART TS,
capture_schema.IBMSNAP_REGISTER REG ,SYSIBM.SYSTABLES TAB
 WHERE (TAB.CREATOR = REG.SOURCE_OWNER) AND
 (TAB.NAME = REG.SOURCE_TABLE) AND
(TAB.DBNAME = TS.DBNAME) AND
(TAB.TSNAME = TS.TSNAME) AND 
(TAB.CREATOR = COL.TBCREATOR) AND
(TAB.NAME = COL.TBNAME) AND
(TS.FORMAT = ' ') AND
(COL.ALTEREDTS > COL.CREATEDTS)AND
(COL.ALTEREDTS > TS.REORG_LR_TS)
GROUP BY TAB.CREATOR,TAB.NAME;

RRF table spaces

Table 2 summarizes the situations where a REORG is required for RRF table spaces.

Table 2.

Operation	Table actively being replicated?	REORG required?
ALTER TABLE ADD COLUMN	No	Yes
	Yes	No
ALTER TABLE ALTER COLUMN SET DATATYPE	No	Yes
	Yes	No

You can use the following queries to determine which RRF table spaces require a REORG. Replace capture_schema with the schema of the Q Capture or Capture control tables:

Q Replication

SELECT DISTINCT SUBSTR(TAB.CREATOR,1,10) AS CREATOR,
SUBSTR(TAB.NAME,1,35) AS NAME,
SUBSTR(TAB.DBNAME,1,7) AS DBNAME,
SUBSTR(TAB.TSNAME,1,8) AS TSNAME,
TS.REORG_LR_TSFROM SYSIBM.SYSTABLES TAB,
SYSIBM.SYSCOLUMNS COL, SYSIBM.SYSTABLEPART TS,
capture_schema.IBMQREP_SUBS SUBS
 WHERE (TAB.CREATOR = SUBS.SOURCE_OWNER) AND
 (TAB.NAME = SUBS.SOURCE_NAME) AND
 (TAB.DBNAME = TS.DBNAME) AND
(TAB.TSNAME = TS.TSNAME) AND
 (TAB.CREATOR = COL.TBCREATOR) AND
 (TAB.NAME = COL.TBNAME) AND
 (TS.FORMAT = 'R') AND(
 ( (COL.ALTEREDTS > TAB.CREATEDTS)AND
(COL.ALTEREDTS > TS.REORG_LR_TS))OR
 ( (COL.CREATEDTS > TAB.CREATEDTS)AND
(COL.CREATEDTS > TS.REORG_LR_TS)) );

SQL Replication

SELECT DISTINCT SUBSTR(TAB.CREATOR,1,10) AS CREATOR,
SUBSTR(TAB.NAME,1,35) AS NAME,
SUBSTR(TAB.DBNAME,1,7) AS DBNAME,
SUBSTR(TAB.TSNAME,1,8) AS TSNAME,
TS.REORG_LR_TSFROM SYSIBM.SYSTABLES TAB,
SYSIBM.SYSCOLUMNS COL, SYSIBM.SYSTABLEPART TS,
	capture_schema.IBMSNAP_REGISTER REG
WHERE (TAB.CREATOR = REG.SOURCE_OWNER) AND
 (TAB.NAME = REG.SOURCE_TABLE) AND
 (TAB.DBNAME = TS.DBNAME) AND
 (TAB.TSNAME = TS.TSNAME) AND
 (TAB.CREATOR = COL.TBCREATOR) AND
 (TAB.NAME = COL.TBNAME) AND
 (TS.FORMAT = 'R') AND(
 ( (COL.ALTEREDTS > TAB.CREATEDTS)AND
(COL.ALTEREDTS > TS.REORG_LR_TS))OR
 ( (COL.CREATEDTS > TAB.CREATEDTS)AND
(COL.CREATEDTS > TS.REORG_LR_TS)) );

Usage notes

• To enable replication of ADD COLUMN and SET DATA TYPE operations, you must turn on the DATA CAPTURE CHANGES attribute on the SYSIBM.SYSTABLES and SYSIBM.SYSCOLUMNS system catalog tables.
• For ALTER TABLE ALTER COLUMN in SQL Replication, do not alter the changed-data (CD) table. The Capture program automatically alters the CD table column to match the source table column.
• If you add an identity column to the source table, when replication adds the column to the target table it uses the underlying data type. For example, if you add an identity column to the source table with an underlying type of INTEGER, replication adds an INTEGER column at the target. For more information about identity columns and replication, see Replication of tables with identity columns.