IBM Support

PH16629: IIDR SUPPORT TO CHANGE REPLICATION KEY - NEW REDEFINE_REPL_KEY SIGNAL

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • Q Capture will recognize a new signal that allows users to
    redefine the replication key.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: 1- Capture users                             *
    *                 2- All customers                             *
    *                 3- All Q Capture and Q Apply customers       *
    *                 4- All Event Publication customers           *
    *                 5- customers who need to replicate add       *
    *                 column DDL of special temporal columns       *
    *                 6- Q Apply                                   *
    *                 7- All Event Publication customers           *
    *                 8- All Capture who use PARALLEL_SENDQS       *
    *                 customers                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1- Important information about the DB2  *
    *                      environment is not recorded in the      *
    *                      Capture log messages.                   *
    *                      2- Q Apply gets sql error -311 when it  *
    *                      restarts and reloads the sub with       *
    *                      state='T' from IBMQREP_TARGETS          *
    *                      3- Q Capture will recognize a new       *
    *                      signal that allows users to redefine    *
    *                      the replication key.                    *
    *                      4- Q Capture builds delimited messages  *
    *                      in CCSID 1208 if the source table       *
    *                      CCSID is EBCDIC and uses character      *
    *                      conversion services to convert source   *
    *                      table data from CCSID 1208 to the       *
    *                      EBCDIC CCSID specified in the           *
    *                      IBMQREP_SENDQUEUES MESSAGE_CODEPAGE.    *
    *                      5- Q apply reports sql error -199 when  *
    *                      replicating add column DDL of special   *
    *                      temporal columns                        *
    *                      6- Q Apply Agent may not provide        *
    *                      proper diagnostics when it reports      *
    *                      unexpected non-SQL errors encountered   *
    *                      when applying row changes to target.    *
    *                      Without information on the exact Q      *
    *                      Subscription, source row LSN or MQ      *
    *                      MSGID of the problem row change, user   *
    *                      cannot determine the cause of the       *
    *                      error or steps to rectify it.           *
    *                      7- Q Capture does not rebuild the       *
    *                      publication search condition after it   *
    *                      detects that a publication source       *
    *                      table was altered to drop a column.     *
    *                      8- A Q Capture send queue has a         *
    *                      counter that indicates the number of    *
    *                      active subscriptions that write         *
    *                      messages to the send queue.  The        *
    *                      counter does not get incremented        *
    *                      correctly if all subscriptions that     *
    *                      use the send queue are inactive when Q  *
    *                      Capture warmstarts.                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1- Sometimes information about the DB2 environment could be
    very useful when analyzing a problem in Capture.
    2- When apply restarts and loads sub with state='T', it needs
    to generate a sql and update the state from 'T' to 'I'.
    However, the generated sql command is incorrect
    3- Q Capture will read the new REDEFINE_REPL_KEY signal and
    update the IBMQREP_SRC_COLS IS_KEY and COL_OPTIONS_FLAG columns
    to redefine the replication key.  Q Capture will put a REDEFINE
    REPL KEY message to the send queue.  Q Apply will read the
    REDEFINE REPL KEY message from the receive queue and update the
    IBMQREP_TRG_COLS IS_KEY column to redefine the replication key.
    4- Q Capture EP should build delimited messages in CCSID 37 or
    CCSID 500 if the IBMQREP_SENDQUEUES MESSAGE_CODEPAGE is the
    same as the source EBCDIC CCSID.
    5- For a temporal column whose DEFAULT column value is 'a','b'
    or 'd' in sysibm.syscolumns, their DEFAULTVALUE value is not
    supported in default clause. It causes apply to report sql
    error -199
    6- Q Apply Agent should provide full diagnostics to identify
    cause of non-SQL errors encountered while applying row changes
    for source.
    7- Q Capture needs to rebuild a publication search condition
    after it detects that the publication source table was altered
    to drop a column.
    8- Q Capture should send a SEQUENCE_RESET message when it
    activates the first subscription for a send queue.
    

Problem conclusion

Temporary fix

Comments

  • 1- Added information about DB2 data sharing and function level
    to the the Capture startup message.  With this PTF all
    replication tasks (Capture, Q Capture, SQL Apply, Q Apply,
    ASNMON) need GRANT SELECT onSYSIBM.SYSLEVELUPDATES table.
    2- With this fix, apply generates correct sql command to update
    sub state.
    3- The REDEFINE_REPL_KEY signal will provide function that
    allows users to dynamically add, drop, and reorder the columns
    that makeup the replication key.
    4- Q Capture EP will build delimited messages in CCSID 37 or
    CCSID 500 if the IBMQREP_SENDQUEUES MESSAGE_CODEPAGE is the
    same as the source EBCDIC CCSID.
    5- With this fix, Capture will send an empty default value when
    the value of DEFAULT column of sysibm.syscolumns is 'a', 'b' or
    'd', so that apply will not report sql error.
    6- Q Apply Agent will issue diagnostic messages giving full
    details of the row change that encountered the non-SQL error
    including Q Subscription name, source row change LSN, source
    transaction commit LSN, and MQ MSGID of the message in the
    RECVQ.
    7- Q Capture will update IBMQREP_SRC_COLS and rebuild the
    search condition after it detects that a publication source
    table was altered to drop a column.
    8- Q Capture will send a SEQUENCE_RESET message only when it
    activates the first subscription for a send queue.
    Note: ASNCLP has been updated from db2_v105fp11 s191020.
    

APAR Information

  • APAR number

    PH16629

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    B33

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-09-09

  • Closed date

    2019-11-08

  • Last modified date

    2019-11-30

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

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

    UI66346 UI66347 UI66348 UI66349

Modules/Macros

  •    ASNACMD  ASNADMSP ASNAPPLY ASNCAP   ASNCATM
    ASNCCMD  ASNCLPAP ASNCLPCL ASNCLPCM ASNCLPCO ASNCLPMS ASNCLPQA
    ASNCTLZD ASNDBCON ASNMCMD  ASNMON   ASNQACMD ASNQAPP  ASNQAROW
    ASNQASUB ASNQBRWZ ASNQCAP  ASNQCCMD ASNQDEP  ASNQEXRP ASNQLODZ
    ASNQMFMT ASNQP2PI ASNQXFMT ASNQ1140 ASNRBASE ASNSQLCZ ASNTDIFF
    ASNTDSP  ASNTRC   ASNZOSCN ASN2BASE ASN2DBCN ASN2SQLZ ASN2ZOSC
    

Fix information

  • Fixed component name

    WS REPLICATION

  • Fixed component ID

    5655L8800

Applicable component levels

  • RB33 PSY UI66346

       UP19/11/19 P F911

  • RB34 PSY UI66347

       UP19/11/20 P F911

  • RB35 PSY UI66348

       UP19/11/19 P F911

  • RB36 PSY UI66349

       UP19/11/19 P F911

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":"B33","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
30 November 2019