IBM Support

PI42263: SQL APPLY REPLICATES FROM 16-BYTE LSN SOURCE SERVER TO 10-BYTE L SN CCD TARGET --AI-- COL ADDED TO IBMQREP_CAPPARMS SEE AI BELOW

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • Apply does not allow to insert a 16-byte LSN value from the
    source server to a target CCD table with 10-byte LSN value.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: 1- Q Apply                                   *
    *                 2- SQL Capture                               *
    *                 3- Any user of the confmt option for asnmon  *
    *                 4- Any user of RRS                           *
    *                 5- Any user who runs status commands from    *
    *                 the console.                                 *
    *                 6- SQL Replication                           *
    *                 7- ASNTDIFF (z/OS)                           *
    *                 8- Any user who monitors capture warning     *
    *                 messages.                                    *
    *                 9- SQL Replication                           *
    *                 10- SQL Replication                          *
    *                 11- All SQL Capture customers.               *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1- Q Apply modify 'STATUS SHOW          *
    *                      DETAILS' command output shows           *
    *                      incomplete information when there are   *
    *                      more than one active receive queues.    *
    *                      2- 1. The capture program may dump      *
    *                      (OC4) when converting a table or        *
    *                      column name from UTF-8 to EBCDIC        *
    *                      during DDL log record processing.       *
    *                      Specifically, this occurs if the        *
    *                      conversion using UCS returns an error   *
    *                      and ICU is attempted to be used. 2.     *
    *                      The capture program stops with          *
    *                      conversion error if column or table     *
    *                      name from DDL operations cannot be      *
    *                      converted from UTF-8 to EBCDIC. Such    *
    *                      tables or columns cannot be subscribed  *
    *                      to.                                     *
    *                      3- Several customers have requested     *
    *                      that we add the server name to the      *
    *                      specific CONFMT console messages        *
    *                      written by asnmon.                      *
    *                      4- When running asnmon over RRS, and    *
    *                      RRS goes down and then comes back up,   *
    *                      asnmon hangs and has to be killed.      *
    *                      5- When running a QSTATUS or STATUS     *
    *                      command from the console, several of    *
    *                      the informational messages are written  *
    *                      to the console twice.                   *
    *                      6- Currently Apply reloads all the      *
    *                      members in the subscription set even    *
    *                      the members who were successfully       *
    *                      loaded in the previous set full         *
    *                      refresh.                                *
    *                      7- When the parameter value of          *
    *                      SOURCE_SELECT or TARGET_SELECT is       *
    *                      longer than 1040 characters, ASNTDIFF   *
    *                      job exits in a very early stage with a  *
    *                      single error message ASN0661E.          *
    *                      8- A recent change was put in to write  *
    *                      the ASN8059W message if it appeared     *
    *                      that the admin thread encountered a     *
    *                      delay. With the current limits, this    *
    *                      message can be written under normal     *
    *                      running circumstances, and in at least  *
    *                      one case caused asnmon to               *
    *                      issue an alert.                         *
    *                      9- Apply does not allow to insert a     *
    *                      16-byte LSN value from the source       *
    *                      server to a target CCD table with       *
    *                      10-byte LSN value.                      *
    *                      10- SQL Apply can not update system     *
    *                      temporal table.                         *
    *                      11- SQL Capture can replicate a source  *
    *                      table that is defined with an edit      *
    *                      procedure (EDITPROC) only it the SQL    *
    *                      Capture DB2 PLAN name starts with       *
    *                      "ASNTC".                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1- When Q Apply has more than one active receive queues, the
    STATUS SHOW DETAILS command output shows incomplete information
    with missing reporting fields / details for all the receive
    queues except for the very first reported receive queue.
    2- 1. The capture program does not handle the switch from UCS
    to ICU correctly that may result in a dump (OC4) if conversion
    errors occur in UCS. 2. The capture program ignores any
    conversion errors during DDL processing as such objects cannot
    be subscribed.
    3- A new value for the confmt option (S) needs to be added to
    allow us to print the server name. Also, we must preserve the
    formatting used when the user specifies a confmt of Y.
    4- If it goes down and comes back up, RRS does not allow
    clients to reconnect. Therefore, if asnmon gets error -925, it
    must terminate.
    5- When QCapture returns certain messages to the console, there
    is a defect that causes some of them to be printed twice.
    6- User requests to reload the members who were failed to be
    loaded in the previous cycle and skip reloading the already
    successfully loaded members.
    7- The parser in common library has a limit on the parameter
    string length: 1040 characters, and thus ASNTDIFF exits with
    ASN0661E when the length of any parameter is longer than the
    limit.
    8- Any user that monitors warning messages in the capture log
    may see the message ASN8059W, and though it indicates a
    potential problem with the admin thread, is not a condition
    that merits a warning message.
    9- Add a new Apply startup parm TOLERATE_LSN_TRUNC to support
    replicating from a 16-byte LSN source server to a 10-byte LSN
    CCD target.
    10- Stored procedure
    SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY needs to be
    called to be able to make change to a system temporal table.
    11- SQL Capture can replicate a source table that is defined
    with an edit procedure (EDITPROC) only it the SQL Capture DB2
    PLAN name starts with "ASNTC".
    

Problem conclusion

Temporary fix

Comments

  • 1- The STATUS SHOW DETAILS command output has been fixed to
    report all informational fields and details for every active
    receive queue.
    2- The capture program has been fixed.
    3- The console messages should now include the server name if
    confmt=S is specified, and will retain the old format if
    confmt=Y is specified.
    4- When the RRS connection error (-925) is returned on a
    dbCommit, asnmon terminates.
    5- A fix has been put in to ensure that these messages are only
    written once.
    6- Apply is changed to internally mark the failing member and
    reload the previously failed member only in the new cycle.
    7- The length limit of a single parameter value string is
    increased to 32000 and the problem should no longer occur after
    this APAR is applied.
    8- The message has been changed to ASN8059I, and while it will
    still be written to the log, will not cause any alerts.
    9- SQL Apply will provide a run time option to tolerate 10
    bytes or 16 bytes IBMSNAP_COMMITSEQ and IBMSNAP_INTENTSEQ for
    CCDs. If the CCD is 10 bytes, Apply will truncate the last 6
    bytes of IBMSNAP_COMMITSEQ and IBMSNAP_INTENTSEQ for the CCD
    target.
    10- SQL Apply is changed to call stored procedure
    SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY if it detects
    any member with a target table defined as system temporal
    table. Currently no admin support for temporal table in SQL
    rep. Customer have to request a special build on top of 10.5
    FP5 or 10.4 FP5 to get the admin support for temporal table in
    SQL Replication. With this fix: Admin will register both base
    and history and create 2 registrations (example)
    CREATE REGISTRATION INCLUDE HISTORY (ADMF001.POLICY_INFO)
    DIFFERENTIAL REFRESH STAGE ADMF001.CDPOLICY_INFO;
    Create member example (If target is temporal, target table
    must exist). Admin will create 2 members
    CREATE MEMBER IN SETNAME TEMPORAL APPLYQUAL AQCOPY2 activate
    yes source ADMF001.POLICY_INFO target name
    ADMF001.TG_POLICY_INFO INCLUDE HISTORY EXIST HIST_TARGET NAME
    ADMF001.TG_HIST_POLICY_INFO LOADX TYPE NO LOAD ;
    Admin will also set ASN.IBMSNAP_SUBS_SET SET OPTION_FLAGS to
    'NNTN' (The 3rd byte to T)
    11- Add the PLAN_ASNTC102 SQL Capture startup parameter. The
    PLAN_ASNTC102 specifies that SQL CAPTURE will use DB2 PLAN
    ASNTC102 when establishes a DB2 connection. Users must create a
    new Capture plan called ASNTC102(same as ASNCAP). Run SQL
    Capture with run time parm PLAN_ASNTC102=Y, until the DB2 APAR
    is available with a fix to also check for ASNCA*
    --AI--
    New column IN_MEM_FILTER_EVAL has been added to
    the IBMQREP_CAPPARMS table.  SASNSAM(ASNQ1021) has
    the alter statement:
        ALTER TABLE !CSH.IBMQREP_CAPPARMS
         ADD COLUMN IN_MEM_FILTER_EVAL CHAR(1)
         WITH DEFAULT 'Y' ;
    This option is documented at https://ibm.biz/BdHuFR
    

APAR Information

  • APAR number

    PI42263

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    A21

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-06-02

  • Closed date

    2015-07-02

  • Last modified date

    2016-01-20

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

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

    UI29074 UI29075 UI29076 UI29077

Modules/Macros

  •    ASNAAPP  ASNACMD  ASNACMP  ASNADMSP ASNAFET
    ASNAISO  ASNAMAN  ASNAPPLY ASNAPRS  ASNAWPN  ASNCAP   ASNCCDD
    ASNCCMD  ASNCCPWK ASNCLPB  ASNCTLZD ASNLOAD  ASNMCMD  ASNMDATA
    ASNMON   ASNMONIT ASNMPROC ASNMUPDT ASNQACMD ASNQAPP  ASNQCAP
    ASNQCCMD ASNQCTLZ ASNQDEP  ASNQEXRP ASNQFALL ASNQMFMT ASNQXFMT
    ASNQ1021 ASNRBASE ASNS1021 ASNTDIFF ASNTDSP  ASNTRC   ASNVCD
    ASNVMON  ASNVREOR ASNVSQL  ASNV1021 ASN2BASE
    

Fix information

  • Fixed component name

    WS REPLICATION

  • Fixed component ID

    5655L8800

Applicable component levels

  • RA21 PSY UI29074

       UP15/07/10 P F507

  • RA24 PSY UI29075

       UP15/07/10 P F507

  • RA25 PSY UI29076

       UP15/07/10 P F507

  • RA26 PSY UI29077

       UP15/07/10 P F507

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

Document Information

Modified date:
20 January 2016