PM00834: SQL APPLY RECEIVES SQLCODE924 RC00F30018 TRYING TO RECONNECT TO DB2 WHEN TERM=N IS USED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQL apply receives sqlcode924 trying to reconnect after Db2 is
  stopped and restarted.
  

Local fix

• restart apply task and connection will be successful.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- SQL Capture DB2 V9 NFM                    *
  *                 2- SQL Capture DB2 V9 NFM                    *
  *                 3- SQL Capture DB2 V9 NFM                    *
  *                 4- Customers who use SQL Apply.              *
  *                 5- Customers who use Q Apply and SQL Apply   *
  *                 to automatically load target tables.         *
  *                 6- Q Capture                                 *
  *                 7- Q Capture                                 *
  *                 8- Q Capture                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  *                      1- SQL Capture pruning receives a SQL   *
  *                      -501 or -913 and then stops pruning of  *
  *                      SIGNAL, CAPTRACE, CAPMON table stops.   *
  *                      Pruning resumes when capture is         *
  *                      stopped and started.                    *
  *                      2- Warmstart fails with ASN0071E.       *
  *                      Source table contains BIT DATA columns  *
  *                      3- This occurs when there are multiple  *
  *                      altered tables in one tablespace.       *
  *                      After the user reorgs the tablespace,   *
  *                      SQL capture does not capture rows of a  *
  *                      transaction.                            *
  *                      4-  SQL APPLY does not display message  *
  *                      ASN1045I on system console.             *
  *                      5-  Q Apply and SQL Apply automatic     *
  *                      load do not collect inline statistics   *
  *                      for all indexes that are defined on     *
  *                      the tables they load.                   *
  *                      6- ASN0543E was unexpectedly issued     *
  *                      during the loading of a subscription    *
  *                      into memory (either after CAPSTART or   *
  *                      program start)                          *
  *                      7- Unnecessary memory allocation in     *
  *                      each subscription object                *
  *                      8- Q Capture suffers high CPU           *
  *                      consumption when source database        *
  *                      transactions are small                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- SQL Capture might not handle some SQL errors properly.  The
  error is not deterministic and has only been observed on the
  SIGNAL and CAPTRACE tables.
  2- SQL Capture did not handle BIT DATA columns correctly on
  warmstart.
  3- SQL capture was not decoding rows after a reorg.  Inserts
  into the CD table included garbage.
  4-  SQL APPLY should display message ASN1045I on system console
  as previous editions have done.
  5-  Q Apply and SQL Apply automatic load should specify the DB2
  LOAD utility STATISTICS INDEX ALL option.
  6- A fetch buffer of 132 kilobytes for LOB data was allocated
  for every subscription regardless of whether LOBs were present
  for that subscription.
  7- A LOB fetch buffer of 132KB (required for EP containing
  LOBs) was allocated for every subscription
  8- By batching multiple transactions into a single MQ message,
  CPU consumption is reduced
  

Problem conclusion

• 1- SQL capture will handle pruning SQL errors and retry
  properly.
  2- SQL capture will start properly.
  3- SQL capture will capture transactions properly after reorg.
  4- Modify SQL Apply to correct this problem.
  5- Modify Q Apply and SQL Apply automatic load to specify the
  DB2 LOAD utility STATISTICS INDEX ALL option.
  6- This buffer was optimized to be allocated once.
  7- The buffer is now allocated once
  8- Q Capture now has the capability to batch multiple
  transactions to increase performance (for compact messages
  only) and reduce CPU consumption
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK52878 UK52879 UK52880 UK52881

Modules/Macros

•    ASNACMD  ASNADMSP ASNAPPLY ASNCAP   ASNCCMD
  ASNCDINS ASNLOAD  ASNMCMD  ASNMIG8  ASNMON   ASNPLXFY ASNPRUNE
  ASNQACMD ASNQAPP  ASNQCAP  ASNQCCMD ASNQDEFQ ASNQDEP  ASNQLODZ
  ASNQMFMT ASNQMSTP ASNQSPS  ASNQSTRA ASNQSTRC ASNQXFMT ASNRBASE
  ASNTDIFF ASNTRC   ASN2BASE
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• R910 PSY UK52878

     UP09/12/23 P F912

• R911 PSY UK52879

     UP09/12/24 P F912

• R912 PSY UK52880

     UP09/12/23 P F912

• R913 PSY UK52881

     UP09/12/23 P F912

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.