PM70033: SQLCODE803 FROM QAPPLY CAN RESULT FROM QCAPTURE INCORRECTLY PROCESSING A GLOBAL TRANSACTION.

Obtain the fix for this APAR.

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 is sysrouted FROM one or more of the following:

  PM69982

• 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.