JR33912: REPLICATION ENGINE FIXES FOR DB2 LUW V9.7 FP1

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Repl Engine APAR for V97FP1
  
  240205 is important if you are on little endian platforms like
  linux amd/intel, windows, solaris amd.
  --------------------------
  240205:QCAPTURE/SQLCapture on Little Endian sends little endian
          format LSN, problem when target is a CCD
  Capture will treat all LSN values as big endian regardless of
  the source system. The little endian representation was
  a problem for the intentseq column of a CCD.
  Also, with CHG_UPD_TO_DEL_INS = Y, the  CD table intentseq
  column caused Delete/Insert pairs to be in the wrong order with
  "insert" row before the "delete" row making a rework of the
  "insert" and a missing row on target.
  --------------------------
  

Local fix

• V97FP1 and POC fixes
  

Problem summary

• The following Replication defects have been fixed in
  DB2 LUW V97 FP1.
  .
  577225:QCAPTURE:engine issues encountered during win32
         system test
  Fixes required for 32bit mode of Oracle Capture.  Also
  updated the occi build libraries to versions compatible
  with the DB2 build environment.
  .
  571109:QCAPTURE:The updated data are NOT able to be
         replicated for bidirectional replication after
         inserting a addcol signal
  The col_options_flag column was incorrectly set for the
  added column upon receiving an ADDCOL signal in
  bidirectional replication.  Now Capture correctly sets it
  to 'YNNNNNNNNN'.
  .
  562234:QCAPTURE:fix the commitSeq for Oracle Capture
  User Affected: QCAPTURE for Oracle
  Problem Description: The QCAPTURE program used the data in
  the TIMESTAMP column for a COMMIT row in the
  V$LOGMNR_CONTENTS as COMMIT time of a transaction. The
  data type of the TIMESTAMP column is a DATE which lacks
  sub second precision. Since V9.5 FP3 each transaction is
  checked if it needs to be published based on the COMMIT
  time. If multiple transactions occurs within one second
  only the first transaction is published and subsequent
  transactions are ignored. QCAPTURE assumes that these
  don't need to be published since it's comparing the COMMIT
  time.
  Problem Summary: Data loss may occur if multiple
  transactions are performed within one second. QCAPTURE
  will only publish the first of such a batch of
  transactions. The remaining transactions in that second
  are ignored. The source and target become out of synch.
  Problem Conclusion: The QCAPTURE program does not rely on
  the TIMESTAMP information. Instead it relies on the SCN of
  the COMMIT row to determine if transactions have been
  published. This requires migration from V9.5FPX (X<5) to
  V9.7FP1 because the V9.5FPX restart information is not
  valid in V9.7FP1.
  .
  240205:QCAPTURE on Little Endian sends little endian
         format LSN, problem when target is a CCD
  Capture will treat all LSN values as big endian regardless
  of the source system. The little endian representation was
  a problem for the intentseq column of a CCD.
  .
  238071:fix several issues with QCAPTURE for Oracle
  User Affected: QCAPTURE for Oracle
  Problem Description: QCAPTURE contained a list of
  problems: SIGSEGV on Linux/Windows during startup,
  incorrect timestamp format when populating structures for
  a SQL statement against the database may lead to SQL
  errors, slow performance during contraints gathering (on
  subscription activation) and during log information
  gathering, ability to run QCAPTURE without check for
  database archive logging and database supplemental
  logging, LogMiner error OCI_NO_DATA during fetching from
  V$LOGMNR_CONTENTS.
  Problem Summary: Due to the issues the QCAPTURE program
  may come down and has to be restarted or inhibits a slow
  performance during startup.
  Problem Conclusion: All issues have been fixed. For the
  OCI_NO_DATA the log reader thread will be restarted and
  QCAPTURE continues mining the log from where it left of
  after restarting the LogMiner session.
  .
  241489:QCAPTURE:Subscriptions should be deactivated upon
         Compression Dictionary error
  QCAPTURE comes down if a compression dictionary error
  is encountered. Instead, QCAPTURE should deactivate the
  subscriptions belonging to the table that had a
  compression dictionary error instead of shutting down.
  QCAPTURE is now deactivating the subscriptions instead of
  shutting down.
  .
  238495:QCAPTURE:the signal "ADDCOL" can't be processed
         when RS97 try to replicate V95 datasource
  Fix to handle the ADDCOL signal when V9.7 capture is
  remote capturing from a lower level (<V9.7) DB2 source.
  .
  234969:QCAPTURE:trans_published monitoring counter is not
         updated for published transactions on queues in the
         delimited format.
  Fix to increment the trans_published monitoring counter
  for EP delimited publications.
  .
  555987:QCAPTURE:on linuxamd64 can't process the loaddone
         signal correctly when replicating
         V91, V97 DB2 datasource
  Fix to handle signals due to alignment when remotely
  capturing from a 64 bit system and the source DB2 is on a
  32 bit system.
  .
  245946:QAPPLY:Load Resume support for load_types
         101,102,103 in LUW and fixing some qtest failures
  QAPPLY LUW users who wish to use LOAD INSERT functionality
  can now make use of load types 101, 102 and 103.
  .
  558539:QAPPLY:Where possible, Q and SQLAPPLY should
         inforce WFO semantics during load or full-refresh
         phases
  The code enforces 'Wait For Outcome' cursor stability
  semantics when data is exported from a source table during
  load or full refresh to prevent data loss.
  .
  241043:QAPPLY:Multiple fixes
  - Orphan entires in SPILLEDROW table will be deleted for
    all subscriptions that are Active (or Inactive) at each
    PRUNE_INTERVAL.
  - All retry counters, the APPLY_SLEEP_TIME counter and the
    MONSTER_TRANS counter are now reported immediately
    without waiting for transaction to COMMIT.
  .
  242218:QAPPLY:Unsubscribed identity columns should be
         tolerated and no MSGASN7533E should be issued in
         this case
  MSGASN7533E was issued when un-subscribed generated
  always/generated by default columns were detected at the
  target. This has been fixed.
  .
  333379:QAPPLY:HH.MM is considered as invalid value for
         param "applyupto" on Linux/IA32
  QAPPLY applyupto parameter did not accept HH.MM on
  Linux/IA32 and Windows. This has been fixed.
  .
  551882:QAPPLY:add load restrictions for XML to LOB and LOB
         to XML mapping due to insufficient support from
         available load utilities
  The Q Replication mappings DBCLOB->XML, XML->DBCLOB are
  restricted in the engine and ASN7621 Reason 1 is issued.
  XML->CLOB and XML->BLOB cannot be used with
  HAS_LOADPHASE = 'I' and ASN7532 Reason 10 is issued.
  CLOB->XML can be used however, source and target database
  should have the same code page. If this is not the case
  only LOAD FROM CURSOR must be used as LOAD_TYPE for the
  affected subscription in the IBMQREP_TARGETS table.
  Otherwise, data loss may occur due to conversion problems
  of the IMPORT/LOAD utility after an EXPORT. QAPPLY will
  not notice any problems since the utility does not report
  an error during the load by QAPPLY.
  .
  236080:QAPPLY: Multiple fixes
  - SQLCODE134 on the DELETE of a row from a table with a
    LONG VARCHAR field (DB2 z V8 CM). QAPPLY will now skip
    LONG VARCHAR columns greater than 255 from WHERE clauses
    only while running with DB2 V8 CM and below versions.
  - For target Stored Procedures, if parameters are string
    like (CHAR/VARCHAR/...) the codepage info is incorrectly
    recorded in QAPPLY catalogs as '0'.  This causes no
    codepage conversions to occur for these parms.
  - Capture side LOADDONE after manual load, could get
    stalled behind other messages on Queue to QAPPLY,
    delaying activation of sub. QAPPLY side LOADDONESUB
    command has been added to indicate LOADDONE after manual
    load.
  .
  235942:QAPPLY:STATE_INFO should not show ASN7597E when sub
         is deactivated by a QCAPTURE message
  Previously an error message MSGASN7597E was present in
  IBMQREP_TARGETS.STATE_INFO when a sub was deactivated.
  This has been replaced by MSGASN7700I, which indicates
  that a subscription was deactivated by a sub deactivation
  message.
  .
  User Affected: QAPPLY customers who run the
                 STATUS SHOW DETAILS command.
  Problem Description: The STATUS SHOW DETAILS command
  reports "Down" when if it times out waiting for a reply
  message from QAPPLY.
  Problem Summary: The STATUS SHOW DETAILS command should
  report "Timed out waiting for reply" if it times out
  waiting for a reply message from QAPPLY.
  Problem Conclusion: This problem has been fixed in V97FP1.
  .
  243105:SQLAPPLY does not replicate 'D'eleted
         rows from complete CCD source to noncondensed CCD
         target during full refresh or load through ASNLOAD.
  SQLAPPLY should replicate Deleted row during full refresh
  if target is noncondensed CCD.  SQLAPPLY replicates
  Deleted row to noncondensed CCD during full refresh.
  .
  237628:SQLAPPLY fails with SQL0313N errcode 9C010E when
         source is CCD
  SQLAPPLY did not build sqlda correctly when it tried to
  retrieve upper bound for the cycle from CCD table and
  caused SQL0313N.  Correct SQLAPPLY to use correct sqlda to
  retrieve upper bound from CCD table.
  .
  235940:SQLAPPLY fails with SQL0803N when updating a
         condensed CCD target if TARGET_KEY_CHG=Y
  SQLAPPLY failed with SQL0803N due to a previously deleted
  row when target is a condensed CCD.  SQLAPPLY removes the
  deleted row from CCD before it updates the table if
  SQL0803N was encountered during the update.
  .
  540884:SQLAPPLY improves error message processing for RI
         failures.
  SQLAPPLY inserts related RI violation information to
  apply_qual.RI file
  .
  540879:SQLAPPLY missed rows when COMMIT_COUNT > 0, and RI
         violation was encountered during transactional mode
         processing
  SQLAPPLY retry logic caused rows missing if RI violation
  error can not be resolved in current cycle.  SQLAPPLY does
  not advance subscription synchpoint/synchtime if RI
  violation occurred in the cycle.
  .
  539183:SQLAPPLY:ASNLOAD does not turn on "copy yes" when
         logretain and userexit are off
  logretain and userexit are changed to logatchmeth1.
  ASNLOAD does not check logretain and userexit
  .
  549676:MONITOR:Replication monitor reporting too many
         alerts for QCAPTURE latency threshold under
         rollback condition
  Due to performance issues, the DDL for the index on the
  IBMQREP_CAPMON table was changed to a DESC sort order.
  This had an impact on the monitor logic for the
  QCaptureLatency alert which depends on a ASC sort order.
  The defect fixes the query and ensures that monitor reads
  the rows in ASC MONITOR_TIME order.
  .
  235977:MONITOR:Indexes not used due to non optimal SQL in
         asnmon
  The queries used in for pruning the tables IBMSNAP_ALERTS,
  IBMSNAP_MONTRACE, IBMSNAP_MONTRAIL apply a range predicate
  which some mathematical expressions. The way the queries
  were written, prevented the optimizer from using the index
  to evaluate the predicate. This defect rewrites the
  queries so that the index can be used and the queries are
  executed much faster.
  .
  575496:STORED_PROC:SQL0104N during BIND DBRM on z/OS when
         Japanese codepage is involved
  The cursor names were written in lower case letters, which
  cause the SQL0104N during the DBRM BIND when Japanese
  codepage was involved.  This defect converts all the lower
  case cursor names to upper case names.
  .
  580406:UTILITIES:ASNTDIFF/ASNTREP failed to deal with some
         specific qsubs on Linuxamd64
  ASNTDIFF ran into a storage overlay when a schema and
  table name combination that was 128 characters long.
  Testing encountered an unpredictable ASNTDIFF behavior
  only on Linuxamd64. This defect allows ASNTDIFF to handle
  very long schema names and table names, and avoids that
  these long names cause a buffer overflow.
  .
  579871:UTILITIES::on nt32, ASNTDIFF will expose an
         assertion failure
  ASNTDIFF performed an assertion on a timestamp format.
  This assertion however was not aware of the different
  TIMESTAMP(0) format and the missing separator between
  seconds and milliseconds.  This defect adds a special
  assertion case for the presence of a TIMESTAMP(0) column
  value.
  .
  571081:UTILITIES: Multiple code problems were fixed in
         this defect:
  .
  Asntrep abends on Solaris when a SQL Error is encountered.
  The M_ERROR constant gets overridden on the Solaris which
  causes a negative index access in case ASNTREP runs into
  a SQL error. This defect ensures that ASNTREP does not
  override systems constants, but defines non-conflicting
  program constants.
  .
  ASNTDIFF supports up to 65 key columns. The ORDER BY
  clause of the source/target select statements can cause a
  memory overlay problem when a lot of key columns are
  present, i.e. in the case when no replication index is
  available. This fix increases the program's data
  structures to allows for up to 65 columns specified in
  the ORDER BY CLAUSEs of the source and select statement.
  .
  ASNTDIFF support for LOB columns in SQL Replication.
  ASNTDIFF invoked in the context of SQL Replication did not
  consider column with coltype='L' (LOB indicator) in the
  IBMSNAP_SUBS_COLS table. Therefore although part of the
  replication subscription, LOB column content was not
  compared during the ASNTDIFF execution. This defect
  implements the support for LOB columns, so that they
  considered and properly compared assuming they are part of
  the replication subscription.
  .
  Asnmail program did not properly separate the mail subject
  from the body. This caused SMTP header fields to be
  present in the mail subject at the receiver. Furthermore
  some special characters in the mail subject removed parts
  of the mail body. This defect implements proper mail
  subject and body separation as required by the SMTP RFCs.
  .
  Asnmon program could run into an ABEND when there is no
  row in the trace table and the MONITOR_ERROR parameter is
  specified. This was due to a problem in the no row found
  error handling routine.
  .
  Asnscrt program allowed for user names of only 20
  characters to be specified in the account parameter. This
  can easily result in a problem when you specify long users
  that are also part of a domain. This fix increases the
  supported account parameter length to support up to 36
  chars and furthermore prevents buffer overflows now.
  .
  Asnmon was inconsistent in the point in time when it
  resumed from a suspension period.
  .
  The following conditions resumed checking for alerts at
  the point in time when they have been suspended. Therefore
  these conditions will report any alerts that have been
  ignored during the suspension period after the resume.
  .
   - CAPTURE_ERRORS
   - CAPTURE_WARNINGS
   - APPLY_ERRORS
   - APPLY_WARNINGS
   - APPLY_SUBS_FAILING
   - QCAPTURE_ERRORS
   - QCAPTURE_WARNINGS
   - QAPPLY_ERRORS
   - QAPPLY_WARNINGS
   - CAPTURE_HLATENCY
   - QCAPTURE_LATENCY
  .
  The following conditions will resume checking for alerts
  at the point in time when they have been resumed:
  .
   - ApplyFullRefresh
   - ApplyRejTrans
   - ApplyReworked
   - ApplyEELatency
   - commonMonTab
   - qApplyException
  .
  This fix implements consistent behavior among all alerts:
  Alert checking restarts at the point in time when asnmon
  resumes from a suspension period.
  

Problem conclusion

• The Replication Serrver defects described
  in the above section have been fixed in DB2 LUW V9.7 FP1.
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  WS Q-REPLIC LUW

• Fixed component ID

  5724N9801

Applicable component levels

• R970 PSY

     UP