IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

 

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:

    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.

[{"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