PM55656: QAPPLY MAY GET SQL0104N FAILURE WHEN USING CCSIDS SUCH AS 1143 RUNNING SELECT QUERY ON SYSIBM.SYSRELS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Qapply may fail with SQLCODE104 SQL0104N if running with
  certain CCSID such as 1143.  A Db2 query with the operator
  != in the where clause can have a variant representation
  on different CCSIDs.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Users getting unexpected ASN7646E when Q  *
  *                 Apply is inserting into IBMQREP_EXCEPTIONS   *
  *                 table                                        *
  *                 2- z/OS customers using DB2 version 9 and    *
  *                 up and codepage 1143                         *
  *                 3- z/OS users using DB2 V10 IDENTIFYING      *
  *                 QAPPLY AS A LONG RUNNING READER              *
  *                 4- Replication Alert Monitor  user that      *
  *                 uses the QCAPTURE_SUBSINACT condition.       *
  *                 5- Q Capture/ Q Apply                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- Codepage Conversion error when       *
  *                      inserting into exceptions table causes  *
  *                      Apply to stop                           *
  *                      2- Select query against SYSIBM.SYSRELS  *
  *                      fails with -104 sqlcode                 *
  *                      3- DSNB260I -HT1K DSNB1PCK WARNING - A  *
  *                      READER HAS BEEN RUNNING FOR xxxx        *
  *                      MINUTES                                 *
  *                      4- Monitor server continously reports   *
  *                      ASN5157W (subs inactive warning), even  *
  *                      though the sub is stopped a long time   *
  *                      ago.                                    *
  *                      5- LOB/XML columns are updated at the   *
  *                      target even if they were not changed    *
  *                      at the source in P2P subscriptions and  *
  *                      Bidir/Unidir subscriptions with         *
  *                      conflict_action F.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- Q Apply browser treated the codepage conversion error during
  writing to exceptions table as fatal
  2- ENFORCED != 'N' used in the select query is not portable
  across EBCDIC
  3- Q Apply does not commit often thereby appearing as long
  running reader
  4- The SUBSINACT condition does not check the state_time when
  the sub's state is changed. That cause it continue report the
  subs inactive warning.
  5- LOB/XML columns are unnecessarily updated at the target even
  if the actual data was not updated with the same change at the
  source. For example, a non-key non-LOB/XML update at the source
  would result in an update of non-key non-LOB/XML column as well
  as all LOB/XML columns in the table. This is caused by Apply's
  inability to determine if the LOB/XML value was changed at the
  source. LOB/XML values are always sent by Capture to resolve
  possible conflicts. For non-LOB/XML columns Apply can determine
  whether or not these columns have been updated at the source.
  

Problem conclusion

• 1- This has been fixed and codepage conversion error when
  inserting into exceptions table will be tolerated
  2- The query has been changed to use <>. The problem has been
  fixed.
  3- Problem has been fixed. Q Apply agent has an additional
  commit at the appropriate place.
  4- Problem is fixed. Now monitor server will check the
  state_time of the sub, and if the sub is not inactived in the
  last monitor cycle it will not report the subs inactive warning
  ASN5157W.
  5- Apply is able to determine if a LOB/XML column was updated
  at the source. If it was not updated the LOB/XML column is not
  included in the update at the target.
  

Temporary fix

Comments

• *********
  * HIPER *
  *********
  Qapply will stop when there is a codepage conversion error
  inserting a row into the exceptions table.
  The conversion error has been corrected and Qapply will
  tolerate conversion errors rather than stopping.
  

APAR Information

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

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

  UK75638 UK75639 UK75640 UK75641

Modules/Macros

•    ASNAAPP  ASNACMD  ASNACMP  ASNADMSP ASNAFET
  ASNAISO  ASNAMAN  ASNAPPLY ASNAPRS  ASNCAP   ASNCCMD  ASNMCMD
  ASNMIG8  ASNMON   ASNMONIT ASNPLXFY ASNQACMD ASNQAPAG ASNQAPP
  ASNQAROW ASNQASUB ASNQBRWZ ASNQCAP  ASNQCCMD ASNQDEP  ASNQMFMT
  ASNQSPIL ASNQXFMT ASNRBASE ASNSQLCZ ASNTDIFF ASNTRC   ASN2BASE
  ASN2SQLZ
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• R910 PSY UK75638

     UP12/01/31 P F201

• R911 PSY UK75639

     UP12/01/31 P F201

• R912 PSY UK75640

     UP12/01/31 P F201

• R913 PSY UK75641

     UP12/01/31 P F201

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.