A fix is available
APAR status
Closed as program error.
Error description
Qapply can issue sql0803n because qcapture incorrectly processed a global transaction causing qapply to process the rows out of sequence.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: 1- User issues REINITQ to reduce * * num_apply_agents to 1 * * 2- Capture * * 3- Q Apply * * 4- SQL Capture * * 5- Q Capture users * * 6- SQL Capture * **************************************************************** * PROBLEM DESCRIPTION: 1- Race condition during REINITQ * * prevents Q Apply browser from reading * * the queue * * 2- Transactions not being capture. * * 3- Q Apply should report additional * * source side information like row * * intentseq, jobname and plan name in * * the diagnostic log when following any * * conflict_action / error_action since * * such information is missing in * * Exceptions table. * * 4- SQL -310 when a DECIMAL NOT NULL is * * alter added to the CD table. * * 5- Q Capture appears to be processing * * admin messages slowly * * 6- Data loss when lock timeout when * * updating the REGISTER table. * **************************************************************** * RECOMMENDATION: * **************************************************************** 1- Race condition when all 4 conditions are met causes browser to suspend incorrectly - the four conditions are: all txs in memory have been applied + browser is reading tx spanning multiple messages in recvq + browser has not yet read the last message of this transaction + User issues reinitq command to reduce num_apply_agents to 1 2- In a high volume system with global transactions, capture was not capture the correct commit point for the global transaction. 3- Customers require additional source side information like row intentseq, jobname and plan name to be reported in Q Apply diagnostic log when conflicts or sql errors are encountered when applying replicated rows. Such information is not currently available in IBMQREP_EXCEPTIONS table. 4- The logic to insert into the CD table did not handle default DECIMAL values correctly. 5- An incorrect sleep cycle in the admin thread meant unnecessary sleeping when messages remain unprocessed on the ADMINQ 6- The lock timeout retry lock updates the synchpoint too early and SQL Apply make read a bad value.
Problem conclusion
1- The above race has been fixed and browser should not be suspending reading from the receive queue for above scenario 2- Capture will properly capture and process transactions when seeing the first commit rather than the final commit from all the members. 3- Q Apply will report row intentseq, job name and plan name in the diagnostic log for any conflict / sql error reported in Exceptions table. 4- DECIMAL NOT NULL columns will be correctly populated with the DB2 default. 5- Q Capture admin thread will not sleep if messages remain unprocessed on the ADMINQ 6- Capture will not update the synchpoint to a bad value
Temporary fix
Comments
********************* *HIPER INFORMATION* ********************* SQL capture can issue SQL code -310 after decimal columns are added to a source and CD table. PM70033 corrects the problem.
APAR Information
APAR number
PM70033
Reported component name
WS REPLICATION
Reported component ID
5655L8800
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2012-08-01
Closed date
2012-09-26
Last modified date
2012-11-19
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
PM70987 UK82147 UK82148 UK82149 UK82150
Modules/Macros
ASNACMD ASNADMSP ASNAPPLY ASNCAP ASNCCMD ASNCCPWK ASNCDINS ASNMCMD ASNMIG8 ASNMON ASNPLXFY ASNQACMD ASNQAPP ASNQAROW ASNQBRWZ ASNQCAP ASNQCCMD ASNQDEP ASNQMFMT ASNQXFMT ASNRBASE ASNTDIFF ASNTRC ASN2BASE
Fix information
Fixed component name
WS REPLICATION
Fixed component ID
5655L8800
Applicable component levels
R910 PSY UK82147
UP12/10/05 P F210
R911 PSY UK82148
UP12/10/09 P F210
R912 PSY UK82149
UP12/10/05 P F210
R913 PSY UK82150
UP12/10/05 P F210
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":"910","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
19 November 2012