PH45910: Replicating CHAR to VARCHAR should automatically use RTRIM EXPRESSION in the subscription to avoid performance degradation

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Replicating data with CHAR to VARCHAR column mapping requires
  users to define column sql expressions for it. This may cause
  performance degradation due to overhead of pre-evaluating sql
  expressions if those columns are part of the replication key.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Q Apply                                   *
  *                 2- All Q Capture users                       *
  *                 3- Q Apply                                   *
  *                 4- Users who want to use nmi_enable=y        *
  *                 5- all Q Capture users                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- In Q Replication environment when    *
  *                      unique indexes are created with         *
  *                      include clause, Q Apply should exclude  *
  *                      those columns for its check for unique  *
  *                      dependencies.                           *
  *                      2- Q Capture is not respecting the      *
  *                      setting of SEND_RI_COLUMN_VALUES        *
  *                      3- Replicating data with CHAR to        *
  *                      VARCHAR column mapping requires users   *
  *                      to define column sql expressions for    *
  *                      it. This may cause performance          *
  *                      degradation due to overhead of          *
  *                      pre-evaluating sql expressions if       *
  *                      those columns are part of the           *
  *                      replication key.                        *
  *                      4- F jobname, status show details       *
  *                      should show NMI socket and connection   *
  *                      information or INSERT INTO              *
  *                      !ASH.IBMQREP_APPLYCMD (CMD_TEXT)        *
  *                      VALUES ('STATUS SHOW DETAILS'); should  *
  *                      show NMI socket and connection          *
  *                      information                             *
  *                      5- Wrong column delimiter generated in  *
  *                      MQ messages in Finnish DB2 subsystem    *
  ****************************************************************
  1- When unique indexes are created with include clause, Q Apply
  does not ignore those extra columns in unique dependency checks
  potentially leading to unique constraint violations.
  2- Q Capture should select the RI columns from the source
  database catalogs only based on the setting of
  SEND_RI_COLUMN_VALUES in the send queue.
  3- Replicating columns with CHAR to VARCHAR mappings will no
  longer require explicit sql expressions to RTRIM data. Q Apply
  will automatically RTRIM data replicated for a CHAR to VARCHAR
  column mapping without incurring the overhead of pre-evaluating
  key expressions. This will avoid performance degradation for
  CHAR to VARCHAR column mappings.
  4- F jobname, status show details should show NMI socket and
  connection information or INSERT INTO !ASH.IBMQREP_APPLYCMD
  (CMD_TEXT) VALUES ('STATUS SHOW DETAILS'); should show NMI
  socket and connection information
  5- Qcapture event publisher system generates messages with an
  exclamation sign as the delimiter instead of using the vertical
  bar as specified in IBMQREP_SENDQUEUES
  

Problem conclusion

• 1- Q Apply will now ignore the extra columns from the INCLUDE
  clause of unique indexes to do correct unique dependency
  analysis.
  2- Q Capture may not perform well during startup when it does
  not skip selecting the RI columns from the source database
  catalogs according to the SEND_RI_COLUMN_VALUES setting.
  3- Q Apply will automatically RTRIM data replicated for a CHAR
  to VARCHAR column mapping without requiring a column sql
  expression for it or incurring the performance overhead of
  pre-evaluating key expressions.
  4- F jobname, status show details will show NMI socket and
  connection information or INSERT INTO !ASH.IBMQREP_APPLYCMD
  (CMD_TEXT) VALUES ('STATUS SHOW DETAILS'); will show NMI socket
  and connection information
  5- Changed the appCodepage for Q Capture so the column
  delimiter is properly converted.
  ASNCLP was not updated.
  Function Level: 1140.103
  

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:

  UI81018 UI81019 UI81020 UI81021

Modules/Macros

• ASN2BASE ASNACMD  ASNADMSP ASNAPPLY ASNCAP   ASNCATM  ASNCCMD
  ASNMCMD  ASNMON   ASNQACMD ASNQAHKT ASNQAPAG ASNQAPP  ASNQAROW
  ASNQASUB ASNQBRWZ ASNQCAP  ASNQCCMD ASNQDEP  ASNQEXRP ASNQLODZ
  ASNQMFMT ASNQP2PA ASNQP2PI ASNQSPIL ASNQSQLZ ASNQXFMT ASNRBASE
  ASNTDIFF ASNTDSP  ASNTRC
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RB33 PSY UI81018

     UP22/06/17 P F206

• RB34 PSY UI81019

     UP22/06/18 P F206

• RB35 PSY UI81020

     UP22/06/18 P F206

• RB36 PSY UI81021

     UP22/06/17 P F206

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.