A fix is available
APAR status
Closed as program error.
Error description
The command to ALTER a column of type CHAR to size 255 was failing and instead modifying to 254.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: 1- customers looking to migrate or create * * control tables for the first time. * * 2- All SQL Apply users * * 3- ALL SQL Capture users * * 4- ALL SQL Capture users * * 5- Customers who want to ALTER a column of * * type CHAR to the maximum size of 255 on * * z/OS. * * 6- ALL Capture users * * 7- All SQL Apply users * * 8- All users * * 9- Customers that make changes on their * * target tables to change the unique indexes * * on certain tables. * * 10- Customers who encounter the "subID * * mismatch" error. * * 11- All z/OS Q Capture users * * 12- All EP users * **************************************************************** * PROBLEM DESCRIPTION: 1- ASNCATM does not handle new columns * * in IBMQREP_SRC_COLS and * * IBMQREP_CAPPARMS and indexes in the * * IBMQREP_DELAYIN AUXILIARY tables. * * 2- SQL Apply uses global variable * * SYSIBMADM.REPLICATION_OVERRIDE * * 3- SQL Capture retention limit pruning * * failed * * 4- CD/UOW rows are not pruned correctly * * 5- The command to ALTER a column of * * type CHAR to size 255 was failing and * * instead modifying to 254. * * 6- Db2 V13 EDITPROC support * * 7- SQL Apply failed in updateAny * * scenario * * 8- For the Q capture server control * * table IBMQREP_CAPMON, In 10.2.1 * * version, the MAX_TRANS_SIZE is integer * * data type, In 11.4.0 version, this * * column data type was changed to * * BIGINT, When one value stores this * * column in v11.4.0, The asnmon Not * * suited to its needs. * * 9- Customers are not warned if they * * make changes to target tables that * * affect unique indexes. * * 10- Customers would only get an error * * stating "subID mismatch" without any * * kind of diagnostic information to help * * them understand the cause of this * * error. * * 11- Q Capture reports `SQL0206N` when * * inserting into `IBMQREP_CAPMON`. * * 12- Invalid data in XML message from * * EP for Db2 table with float data * **************************************************************** 1- ASNCATM needs to handle new columns and indexes. 2- Currently, SQL Apply will invoke the system stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY so it can update the temporal table generated always columns and disable recording of history rows for system-period temporal tables at the target side. As part of Db2 V12R1M503 APAR (PH00506) a new built-in global variable SYSIBMADM.REPLICATION_OVERRIDE is introduced to allow the override of special temporal columns in system-period temporal tables or generated expression columns. The variable will also disable the recording of history rows for system-period temporal tables. 3- SQL Capture retention limit pruning failed because too many rows are pruned in one DELETE statement. 4- If there are two subscription sets for the same CD table and one of the set is deactivated (i.e. the PRUNE_SET.SYNCHPOINT is set to zeros), the PRUNEPOINT is not set correctly and rows in the CD/UOW are not deleted. 5- The ALTER command for CHAR(255) should work successfully. 6- In Db2 V13 non-proxy mode, Capture program must change the input and set WQALLOPT='07'X when calling IFI306 so it can use EDITPROC support. 7- SQL Apply failed when referencing the register table information control block of a subscription that has been disabled. 8- Asnmon need to be fixed with the retrieving requirement that the MAX_TRANS_SIZE column is changed to BIGINT data type of IBMQREP_CAPMON table in v11.4.0. 9- Customers will see a warning message in QApply logs after startup if unique indexes in the target table differ from those in the control tables. 10- Customers should get additional diagnostic information to help them find the root cause of the "subID mismatch" error. 11- Q Capture was not treating certain columns as optional in `IBMQREP_CAPMON`. If the table is missing any of those columns, Q Capture will report `SQL0206N` when inserting into the table. 12- The EP output data for Db2 float data type is incorrect.
Problem conclusion
1- ASNCATM has been updated to handle new columns and indexes. 2- SQL Apply is changed to use the SYSIBMADM.REPLICATION_OVERRIDE built-in global variable SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure for system-period temporal tables, if the storproc is not available or the temporal target has the new temporal columns like USER_ID, OP_CODE etc that are not supported by the storproc. 3- Changed SQL Capture to use cursor based delete for retention limit pruning and commit every 500 rows to prevent lock escalation. 4- Modified the query used to fetch the PRUNEPOINT. 5- The ALTER command now works for CHAR(255). 6- Capture is changed to support Db2 V13 EDITPROC 7- SQL Apply is changed to check if the register table pointer is NULL before referencing the control block. 8- Asnmon need to be satisfy with the retrieving requirement that the MAX_TRANS_SIZE column is changed to BIGINT data type of IBMQREP_CAPMON table in v11.4.0 9- A warning message is printed in the QApply logs if unique indexes in the target table differ from those in the control tables. 10- Additional diagnostic information has been added to the "subID mismatch" error. 11- Q Capture correctly treats the following columns as optional: 12- Capture is changed to correct the output for 8-byte floating point (double) value in the convertColValToString() function. ASNCLP was not updated. Function Level: 1140.103
Temporary fix
Comments
APAR Information
APAR number
PH39026
Reported component name
WS REPLICATION
Reported component ID
5655L8800
Reported release
B33
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2021-07-15
Closed date
2021-09-17
Last modified date
2022-06-10
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI77218 UI77219 UI77220 UI77221
Modules/Macros
ASN2BASE ASNAAPP ASNACMD ASNADMSP ASNAPPLY ASNAPRS ASNCAP ASNCATM ASNCCMD ASNLOAD ASNMCMD ASNMON ASNMONIT ASNPRUNE ASNQACMD ASNQAPP ASNQASUB ASNQCAP ASNQCCMD ASNQDEP ASNQEXRP ASNQMFMT ASNQSPIL ASNQXFMT ASNRBASE ASNTDIFF ASNTDSP ASNTRC
Fix information
Fixed component name
WS REPLICATION
Fixed component ID
5655L8800
Applicable component levels
RB33 PSY UI77218
UP21/09/22 P F109
RB34 PSY UI77219
UP21/09/22 P F109
RB35 PSY UI77220
UP21/09/22 P F109
RB36 PSY UI77221
UP21/09/22 P F109
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B33"}]
Document Information
Modified date:
11 June 2022