IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

 

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 number

    PH45910

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    B33

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2022-04-25

  • Closed date

    2022-06-15

  • Last modified date

    2022-07-01

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

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B33"}]

Document Information

Modified date:
02 July 2022