A fix is available
APAR status
Closed as new function.
Error description
The Q Apply load function calls the Db2 SYSPROC.DSNUTILS stored procedure to run Db2 utilites (LOAD, CHECK DATA, REPAIR).
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: 1- Q Apply Monster processing. * * 2- All Capture customers. * * 3- Q Apply with system period temporal * * table targets * * 4- All Q Apply customers. * * 5- Q Apply COMMIT_COUNT Users * * 6- All Q Capture customers. * * 7- Q Apply for System Temporal Table * * targets * * 8- Q Capture with subscriptions using * * SEARCH_CONDITION * * 9- All customers running Q Capture in proxy * * mode using proxy Db2 12. * **************************************************************** * PROBLEM DESCRIPTION: 1- Q Apply may try to read entire * * monster transaction into memory * * instead of limiting memory usage * * within MEMORY_LIMIT settings. This * * could lead to memory exhaustion with * * ASN0543E reported. * * 2- Capture cannot advance its restart * * LSN while it processes a global * * transaction that does not commit or * * rollback. * * 3- Q Replication cannot support system * * period temporal tables that contain * * generated expression columns. DB2 V12 * * APPCOMPAT503 now provides ability to * * replicate to such tables with new * * global variable * * SYSIBMADM.REPLICATE_OVERRIDE='Y' * * 4- The Q Apply load function calls the * * Db2 SYSPROC.DSNUTILS stored procedure * * to run Db2 utilities (LOAD, CHECK * * DATA, REPAIR). * * 5- Batch and online processing are * * often running at the same time for * * some customers. A COMMIT_COUNT value * * that works well for online processing * * does not work for batch processing. * * 6- The Q Capture IBMQREP_CAPMON * * MAX_TRANS_SIZE column value is invalid * * if the maximum transaction size is * * larger than 2,147,483,647 bytes. * * 7- Q Apply may not correctly replicate * * UPDATE row operations for system * * temporal table having columns that use * * generated always expressions on * * special register values (like * * SESSION_USER). * * 8- Q Capture may fail to activate a * * subscription with a valid * * SEARCH_CONDITION and reports ASN7200E * * stating that the SEARCH_CONDITION is * * using $OPERATION variable even though * * it does not. * * 9- Q Capture running in proxy mode * * using proxy Db2 12 displays Db2 reason * * code X'00E60870'. * **************************************************************** * RECOMMENDATION: * **************************************************************** 1- Q Apply monster processing may use excessive memory during monster processing that may lead to memory exhaustion and ASN0543E errors. 2- Capture users need a command that can prompt capture to advance the restart LSN beyond the start of a global transaction. 3- Q Replication should support replication to system period temporal tables that contain generated expression columns. 4- The Q Apply load function needs to be changed to call either the Db2 SYSPROC.DSNUTILU stored procedure or the Db2 SYSPROC.DSNUTILS stored procedure to run Db2 utilities (LOAD, CHECK DATA, REPAIR). 5- Clients need a way to control whether COMMIT_COUNT commits by transactions or commits by rows. 6- The Q Capture IBMQREP_CAPMON MAX_TRANS_SIZE column is defined with data type INTEGER and can hold up to 2,147,483,647 bytes. 7- Q Apply may not correctly replicate UPDATE row operations for system temporal table having columns that use generated always expressions on special register values like SESSION_USER. Specifically, if the generated expression special register value does not change on source in the UPDATE operation, Q Apply replays the UPDATE without the column in the SET clause which results in target DB2 using the local special register value for the row which may have been different from source. 8- Q Capture may fail to activate a subscription with a valid SEARCH_CONDITION. The spurious failure is reported as ASN7200E message stating that the variable $OPERATION is not valid in the SEARCH_CONDITION column of the IBMQREP_SUBS table. 9- Q Capture set the IFCID 0306 READS WQALLOPT field to X'03' (convert data format and decompress data).
Problem conclusion
Temporary fix
Comments
1- Q Apply is fixed to keep memory usage within memory_limit settings even when processing monster transactions. 2- The IGNORETRANS command has been changed to advance the restart LSN beyond the start of a global transaction. 3- Q Replication now sets new DB2 global variable SYSIBMADM.REPLICATE_OVERRIDE=Y to support replication to system period temporal table with generated expression columns when DB2 V12 is at APPCOMPAT 503. 4- The Q Apply load function has been changed to call either the SYSPROC.DSNUTILS or SYSPROC.DSNUTILU stored procedure to run Db2 utilities (LOAD, CHECK DATA, REPAIR). Q Apply will accept IBMQREP_TARGETS LOAD_TYPE options 6 (DSNUTILU LOAD REPLACE) and 106 (DSNUTILU LOAD RESUME). 5- The user can set COMMIT_COUNT_UNIT to T to commit by transactions which matches COMMIT_COUNT previous behavior or set COMMIT_COUNT_UNIT to R which will allow apply agents to commit by the number of rows. 6- Q Capture has been changed to support a IBMQREP_CAPMON MAX_TRANS_SIZE column with data type BIGINT or data type INTEGER. ALTER the IBMQREP_CAPMON table to change the MAX_TRANS_SIZE column data type to BIGINT if Q Capture publishes transactions larger than 2,147,483,647. 7- Q Apply is fixed to always specify the system temporal table columns that use generated always expression on special register in the UPDATE operations SET clause. If the column did not change on the source, Q Apply lets the UPDATE specify that the column be updated with its current value in the row so that it does not get overwritten by Q Apply's local special register value. 8- Q Capture SEARCH_CONDITION validation during subscription activation is fixed. 9- Q Capture running in proxy mode using proxy Db2 12 has been changed to set the IFCID 0306 READS WQALLOPT field to X'01' (decompress data). Note: ASNCLP has been updated from db2_v105fp11 n180819.
APAR Information
APAR number
PI99238
Reported component name
WS REPLICATION
Reported component ID
5655L8800
Reported release
B33
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-06-15
Closed date
2018-09-27
Last modified date
2018-11-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI58789 UI58790 UI58791 UI58792
Modules/Macros
ASNACMD ASNADMSP ASNAPPLY ASNBNDDR ASNBNDSR ASNCAP ASNCATM ASNCCMD ASNCCPWK ASNCLPAP ASNCLPB ASNCLPCL ASNCLPCM ASNCLPCO ASNCLPMS ASNCLPQA ASNCTLZD ASNDBCON ASNLOAD ASNMCMD ASNMON ASNQACMD ASNQAHKT ASNQAPAG ASNQAPP ASNQAROW ASNQASUB ASNQBNDL ASNQBNDR ASNQBRWZ ASNQCAP ASNQCCMD ASNQCTLZ ASNQDEP ASNQEXRP ASNQLODZ ASNQMFMT ASNQSPIL ASNQXFMT ASNQ1021 ASNQ1140 ASNRBASE ASNSQLCZ ASNS1021 ASNTDIFF ASNTDSP ASNTRC ASNVSQL ASNV1021 ASNV1140 ASN2BASE ASN2DBCN ASN2SQLZ
Fix information
Fixed component name
WS REPLICATION
Fixed component ID
5655L8800
Applicable component levels
RB33 PSY UI58789
UP18/10/06 P F810
RB34 PSY UI58790
UP18/10/09 P F810
RB35 PSY UI58791
UP18/10/06 P F810
RB36 PSY UI58792
UP18/10/06 P F810
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B33","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
01 November 2018