PK27949: QCAPTURE AND SQL CAPTURE WILL BE ENHANCED TO IGNORE CERTAIN TRANSACTIONS. NOTE DDL CHG, SEE II11809

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • QCapture and SQL Capture will  be enhanced to ignore
    transactions performed by certain plan id, authid or job id.
    This functions will also be in future releases .
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: 1- asntdiff users                            *
    *                 2- SQL Apply                                 *
    *                 3- Q Capture users                           *
    *                 4- all SQL and Q Capture users               *
    *                 5- Q Replication.                            *
    *                 6- SQL and Q Capture users                   *
    *                 7- Q Replication.                            *
    *                 8- all SQL and Q Capture users               *
    *                 9- Q Capture users                           *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1- asntdiff fails when column number    *
    *                      is more than 46                         *
    *                      2- 'A' statement is not executed at     *
    *                      target server if no row is fetched      *
    *                      from source                             *
    *                      3- When a subscription is deactivated   *
    *                      because of an admin message the         *
    *                      state_info column the IBMQREP_SUBS      *
    *                      table has a garbage value.              *
    *                      4- Customer wishes to ignore certain    *
    *                      transactions                            *
    *                      5- Q Apply did not cleanup the          *
    *                      pendingLoadList when disabling          *
    *                      subscription. This resulted in          *
    *                      ASN0569E msg with state= 65.            *
    *                      6- The current_log_time column in the   *
    *                      IBMQREP_CAPMON or global register       *
    *                      synchtime was not reflecting the        *
    *                      timestamp of the latest commit seen by  *
    *                      capture. Also, the subseconds portion   *
    *                      of the timestamp was not correct.       *
    *                      7- Q Apply did not detect Q Alias when  *
    *                      monitoring qdepth.                      *
    *                      8- some installations have incorrect    *
    *                      commit time                             *
    *                      9- current_log_time has a 1969 value    *
    *                      at startup.                             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1- fix asntdiff code
    2- Apply should not skip to connect to target server to execute
    'A" statement
    3- Q Capture was not reporting the correct ASN message in the
    state_info column
    4- new function is required
    5- Q Apply did not cleanup the pendingLoadList when disabling
    subscription.
    6- Fix the capture log reader.
    7- Q Apply did not detect Q Alias when monitoring qdepth.
    8- the log time was not adjusted by the LRSN delta
    9- current_log_time should be the last commit time seen on
    warmstart or the current timestamp on coldstart.
    

Problem conclusion

Temporary fix

Comments

  • 1- asntdiff works correctly when column number is more than 46
    2- Apply connects to target server to execute 'A' stmt even no
    row is fetched from source
    3- The state_info column will contain ASN7006E if the
    subscription was deactivated by an admin message
    4- Capture will discard user specified transactions. Function
    was added to let Capture/QCapture skip transaction by
    Authorization ID, Authorization token (z/OS only) and Plan name
    (z/OS only). A new table is added to support transaction
    exclusion based on these criteria. This table will be common
    across SQL Replication, Q Replication and Event Publishing in
    the case where multiple replication products are installed on
    the same machine and the same Capture schema is used. In other
    words, only 1 table will be created under a Capture schema. A
    second table is also added to record the transactions ignored
    as an audit trail.
    
    New tables
    
    CREATE TABLE cap_schema.IBMQREP_IGNTRAN
           (AUTHID     CHAR(128),
            AUTHTOKEN  CHAR(30),
            PLANNAME   CHAR(8))
    
    Descriptions of the columns
    
    AUTHID: The transaction exclusion applies to this primary
            authorization ID.
    AUTHTOKEN: The transaction exclusion applies to this
               authorization token (job name), (z/OS only)
    PLANNAME: The transaction exclusion applies to this plan.
              (z/OS only)
    
    Examples
    
    Example 1. An empty table means transactions are published
    for all authids, authtokens and plans.
    AUTHID  AUTHTOKEN       PLANNAME
    
    Example 2. Transactions with authid "BADUSER" are not published.
    AUTHID  AUTHTOKEN       PLANNAME
    BADUSER
    
    Example 3. Transactions with authid "BADUSER", authtoken
    "BADJOB" and planname "BADPLAN", and transactions with planname
    "BADAPP" are not published.
    AUTHID  AUTHTOKEN       PLANNAME
    BADUSER BADJOB          BADPLAN
                            BADAPP
    
    Reporting:
    
    A new ASN message will be issued when the transaction is
    ignored.
    
    ASN0195I Capture "QALLTYPE" : "WorkerThread". The transaction
    with ID "0000:0000:3e58:184d:0000" was ignored as requested.
    
    A new trace table IBMQREP_IGNTRANTRC needs to be created to
    record all the transactions ignored as an audit trail.
    
    A row will be inserted when a transaction is ignored. This table
    is pruned according to the trace_limit in each prune interval.
    
    CREATE TABLE cap_schema.IBMQREP_IGNTRANTRC
           (IGNTRAN_TIME  TIMESTAMP NOT NULL WITH DEFAULT,
            AUTHID    CHAR(128),
            AUTHTOKEN CHAR(30),
            PLANNAME  CHAR(8),
            TRANSID   CHAR(10)       FOR BIT DATA NOT NULL,
            COMMITLSN CHAR(10)       FOR BIT DATA NOT NULL)
    
    Descriptions of the columns
    
    IGNTRAN_TIME: Time when the trace record was written to the
                IBMQREP_IGNTRANTRC table.
    AUTHID: The transaction exclusion applied to this primary
            authorization ID.
    AUTHTOKEN: The transaction exclusion applied to this
               authorization token (job name).
    PLANNAME: The transaction exclusion applied to this plan.
    TRANSID: The identifier of the transaction that Capture
             excluded.
    COMMITLSN: The commit log sequence number or time sequence
               that Capture excluded
    5- Q Apply will cleanup the pendingLoadList when disabling
    subscription.
    6- The current_log_time and global register synchtime will
    reflect the time of the last committed transaction. The
    subseconds portion will be correct.
    7- Q Apply automatically detects Q Alias and uses the base Q
    name to query qdepth for monitoring.
    8- The log time is adjusted by the LRSN delta. In some
    installations the LRSN is higher than the z/OS clock value,
    because a delta is added to the clock value to generate LRSN.
    With this fix Capture adjust the LRSN to get the correct LRSN.
    This fix requires DB2 APAR PK26126.
    9- current_log_time should always have a valid timestamp.
    

APAR Information

  • APAR number

    PK27949

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    820

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2006-07-11

  • Closed date

    2006-08-08

  • Last modified date

    2006-10-17

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

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

    UK17036 UK17037 UK17038 UK17039 PK33084

Modules/Macros

  • ASNACMD  ASNADMSP ASNAPPLY ASNAPRS  ASNCAP
    ASNCCMD  ASNCCPWK ASNMCMD  ASNMIGZD ASNMIG8  ASNMON   ASNPLXFY
    ASNPRUNE ASNQACMD ASNQADMT ASNQAHKT ASNQAPP  ASNQAROW ASNQASUB
    ASNQBRWZ ASNQCAP  ASNQCCMD ASNQDEP  ASNQMFMT ASNQWK   ASNRBASE
    ASNSQLCF ASNTDIFF ASNTRC   ASNTREP  ASNTXS   ASNUOW
    

Fix information

  • Fixed component name

    WS REPLICATION

  • Fixed component ID

    5655L8800

Applicable component levels

  • R820 PSY UK17036

       UP06/08/19 P F608

  • R821 PSY UK17037

       UP06/08/22 P F608

  • R822 PSY UK17038

       UP06/08/22 P F608

  • R823 PSY UK17039

       UP06/08/22 P F608

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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Replication Server

Software version:

820

Reference #:

PK27949

Modified date:

2006-10-17

Translate my page

Machine Translation

Content navigation