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

Obtain the fix for this APAR.

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