PH57843: values for XML columns not sent causing Q Apply to fail with sqlcode -407

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The values for XML columns were not sent causing Q Apply to
  fail with sqlcode -407
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Users need ASNMON to send encrypted       *
  *                 emails on z/OS.                              *
  *                 2- All user for Q Capture with               *
  *                 OVERRIDE_RESTARTQ=Y                          *
  *                 3- All users for Q Apply with a target       *
  *                 Stored Procedure sub trying to drop a        *
  *                 column from source table                     *
  *                 4- All SQL Apply users                       *
  *                 5- All user for Q Apply with monster         *
  *                 transaction                                  *
  *                 6- all Q Capture users                       *
  *                 7- ASNMON users                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- ASNMON is a program that can         *
  *                      monitor changes in the status of        *
  *                      replication environment. It sends       *
  *                      alert emails to alert about events and  *
  *                      conditions in replication environment.  *
  *                      The current email sent by ASNMON is     *
  *                      not TLS encrypted.                      *
  *                      2- OVERRIDE_RESTARTQ=Y may fail if ddl  *
  *                      happened                                *
  *                      3- If a user drops a column, Q Apply    *
  *                      will complain with ASN7805E error for   *
  *                      a target Stored Procedure               *
  *                      4- SQL Apply should retry with DELETE   *
  *                      for any TRUNCATE error                  *
  *                      5- It's hard to get transaction ID to   *
  *                      use in skip trans if it's monster trans *
  *                      6- The values for XML columns were not  *
  *                      sent causing Q Apply to fail with       *
  *                      sqlcode -407                            *
  *                      7- ASNMON program got the NULL value    *
  *                      for the suspend stop time when set a    *
  *                      value greater than 2038 for the STOP    *
  *                      column of IBMSNAP_SUSPENDS table.       *
  ****************************************************************
  1- ASNMON creates emails and sends to JES2 where CSSMTP could
  read emails from. When AT-TLS is set up on z/OS platform,
  CSSMTP is able to establish a TLS session and sends those
  emails created by ASNMON via encrypted communication.
  2- Q Capture reports errors or not working if
  OVERRIDE_RESTARTQ=Y and have ddl like drop column, create
  column, recreate source table/qsub, alter table set data type
  3- ASN7805E error is reported for a Drop Column even if the
  column is not subscribed
  4- Currently, SQL Apply will retry TRUNCATE errors only for
  sqlcode -428. However, the sqlcode may be different depending
  on the platform.
  5- Transaction ID is missing in the Q Apply log when processing
  monster trans
  6- Q Apply failed with sqlcode -407 because Q Capture did not
  send the values for XML columns when changed_cols_only=N
  7- ASNMON program can get one normal value for the suspend stop
  time when set a value greater than 2038 for the STOP column of
  IBMSNAP_SUSPENDS table.
  

Problem conclusion

• 1- ASNMON is able to send TLS encrypted emails to alert the
  conditions in replication environment as long as CSSMTP and
  AT-TLS is set up in z/OS platform.
  2- Q Capture should recapture logs with ddl changes when
  OVERRIDE_RESTARTQ=Y
  3- Drop Column will be tolerated if the column is not
  subscribed, and error will not be logged
  4- SQL Apply is changed to retry with DELETE for any TRUNCATE
  error
  5- Provide transaction ID in the Q Apply log message when
  processing monster transactions
  6- Correct the logic in Q Capture to fetch the XML value as
  needed and send it to Q Apply
  7- when set a value is greater than or equal to year 2038 for
  the STOP column of IBMSNAP_SUSPENDS table, ASNMON program can
  get one value with "2037-12-31-23:59:59". For a value of the
  time_t data type on 32-bit OS, it must be no later than
  23:59:59 on December 31, 2037, Otherwise an overflow will be
  occurred.
  ASNCLP was not updated.
  Function Level: 1140.103
  

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:

  UI94749 UI94750 UI94751 UI94752

Modules/Macros

• ASN2BASE ASN2DB2Q ASN2DBCN ASN2SQLZ ASN2ZOSC ASNAAPP  ASNACMD
  ASNADMSP ASNAPPLY ASNCAP   ASNCATM  ASNCCMD  ASNCCPWK ASNCDINS
  ASNDB2SQ ASNDBCON ASNMCMD  ASNMDATA ASNMON   ASNMPROC ASNMUPDT
  ASNPRUNE ASNQACMD ASNQAHKT ASNQAPAG ASNQAPP  ASNQAROW ASNQASUB
  ASNQBRWZ ASNQCAP  ASNQCCMD ASNQDEP  ASNQEXRP ASNQLODZ ASNQMFMT
  ASNQP2PA ASNQP2PI ASNQSPIL ASNQSQLZ ASNQXFMT ASNRBASE ASNSQLCZ
  ASNTDIFF ASNTDSP  ASNTRC   ASNUOW   ASNZOSCN
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RB33 PSY UI94749

     UP23/12/08 I 1000

• RB34 PSY UI94750

     UP23/12/09 I 1000

• RB35 PSY UI94751

     UP23/12/09 I 1000

• RB36 PSY UI94752

     UP23/12/08 I 1000

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.