PH39026: alter add column char(255) fails with ASN7621E on z/OS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The command to ALTER a column of type CHAR to size 255 was
  failing and instead modifying to 254.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- customers looking to migrate or create    *
  *                 control tables for the first time.           *
  *                 2- All SQL Apply users                       *
  *                 3- ALL SQL Capture users                     *
  *                 4- ALL SQL Capture users                     *
  *                 5- Customers who want to ALTER a column of   *
  *                 type CHAR to the maximum size of 255 on      *
  *                 z/OS.                                        *
  *                 6- ALL Capture users                         *
  *                 7- All SQL Apply users                       *
  *                 8- All users                                 *
  *                 9- Customers that make changes on their      *
  *                 target tables to change the unique indexes   *
  *                 on certain tables.                           *
  *                 10- Customers who encounter the "subID       *
  *                 mismatch" error.                             *
  *                 11- All z/OS Q Capture users                 *
  *                 12- All EP users                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- ASNCATM does not handle new columns  *
  *                      in IBMQREP_SRC_COLS and                 *
  *                      IBMQREP_CAPPARMS and indexes in the     *
  *                      IBMQREP_DELAYIN AUXILIARY tables.       *
  *                      2- SQL Apply uses global variable       *
  *                      SYSIBMADM.REPLICATION_OVERRIDE          *
  *                      3- SQL Capture retention limit pruning  *
  *                      failed                                  *
  *                      4- CD/UOW rows are not pruned correctly *
  *                      5- The command to ALTER a column of     *
  *                      type CHAR to size 255 was failing and   *
  *                      instead modifying to 254.               *
  *                      6- Db2 V13 EDITPROC support             *
  *                      7- SQL Apply failed in updateAny        *
  *                      scenario                                *
  *                      8- For the Q capture server control     *
  *                      table IBMQREP_CAPMON, In 10.2.1         *
  *                      version, the MAX_TRANS_SIZE is integer  *
  *                      data type, In 11.4.0 version, this      *
  *                      column data type was changed to         *
  *                      BIGINT, When one value stores this      *
  *                      column in v11.4.0, The asnmon Not       *
  *                      suited to its needs.                    *
  *                      9- Customers are not warned if they     *
  *                      make changes to target tables that      *
  *                      affect unique indexes.                  *
  *                      10- Customers would only get an error   *
  *                      stating "subID mismatch" without any    *
  *                      kind of diagnostic information to help  *
  *                      them understand the cause of this       *
  *                      error.                                  *
  *                      11- Q Capture reports `SQL0206N` when   *
  *                      inserting into `IBMQREP_CAPMON`.        *
  *                      12- Invalid data in XML message from    *
  *                      EP for Db2 table with float data        *
  ****************************************************************
  1- ASNCATM needs to handle new columns and indexes.
  2- Currently, SQL Apply will invoke the system stored procedure
  SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY so it can
  update the temporal table generated always columns and disable
  recording of history rows for system-period temporal tables at
  the target side. As part of Db2 V12R1M503 APAR (PH00506) a new
  built-in global variable SYSIBMADM.REPLICATION_OVERRIDE is
  introduced to allow the override of special temporal columns in
  system-period temporal tables or generated expression columns.
  The variable will also disable the recording of history rows
  for system-period temporal tables.
  3- SQL Capture retention limit pruning failed because too many
  rows are pruned in one DELETE statement.
  4- If there are two subscription sets for the same CD table and
  one of the set is deactivated (i.e. the PRUNE_SET.SYNCHPOINT is
  set to zeros), the PRUNEPOINT is not set correctly and rows in
  the CD/UOW are not deleted.
  5- The ALTER command for CHAR(255) should work successfully.
  6- In Db2 V13 non-proxy mode, Capture program must change the
  input and set WQALLOPT='07'X when calling IFI306 so it can use
  EDITPROC support.
  7- SQL Apply failed when referencing the register table
  information control block of a subscription that has been
  disabled.
  8- Asnmon need to be fixed with the retrieving requirement that
  the MAX_TRANS_SIZE column is changed to BIGINT data type of
  IBMQREP_CAPMON table in v11.4.0.
  9- Customers will see a warning message in QApply logs after
  startup if unique indexes in the target table differ from those
  in the control tables.
  10- Customers should get additional diagnostic information to
  help them find the root cause of the "subID mismatch" error.
  11- Q Capture was not treating certain columns as optional in
  `IBMQREP_CAPMON`. If the table is missing any of those columns,
  Q Capture will report `SQL0206N` when inserting into the table.
  12- The EP output data for Db2 float data type is incorrect.
  

Problem conclusion

• 1- ASNCATM has been updated to handle new columns and indexes.
  2- SQL Apply is changed to use the
  SYSIBMADM.REPLICATION_OVERRIDE built-in global variable
  SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored
  procedure for system-period temporal tables, if the storproc is
  not available or the temporal target has the new temporal
  columns like USER_ID, OP_CODE etc that are not supported by the
  storproc.
  3- Changed SQL Capture to use cursor based delete for retention
  limit pruning and commit every 500 rows to prevent lock
  escalation.
  4- Modified the query used to fetch the PRUNEPOINT.
  5- The ALTER command now works for CHAR(255).
  6- Capture is changed to support Db2 V13 EDITPROC
  7- SQL Apply is changed to check if the register table pointer
  is NULL before referencing the control block.
  8- Asnmon need to be satisfy with the retrieving requirement
  that the MAX_TRANS_SIZE column is changed to BIGINT data type
  of IBMQREP_CAPMON table in v11.4.0
  9- A warning message is printed in the QApply logs if unique
  indexes in the target table differ from those in the control
  tables.
  10- Additional diagnostic information has been added to the
  "subID mismatch" error.
  11- Q Capture correctly treats the following columns as
  optional:
  12- Capture is changed to correct the output for 8-byte
  floating point (double) value in the convertColValToString()
  function.
  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:

  UI77218 UI77219 UI77220 UI77221

Modules/Macros

• ASN2BASE ASNAAPP  ASNACMD  ASNADMSP ASNAPPLY ASNAPRS  ASNCAP
  ASNCATM  ASNCCMD  ASNLOAD  ASNMCMD  ASNMON   ASNMONIT ASNPRUNE
  ASNQACMD ASNQAPP  ASNQASUB ASNQCAP  ASNQCCMD ASNQDEP  ASNQEXRP
  ASNQMFMT ASNQSPIL ASNQXFMT ASNRBASE ASNTDIFF ASNTDSP  ASNTRC
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RB33 PSY UI77218

     UP21/09/22 P F109

• RB34 PSY UI77219

     UP21/09/22 P F109

• RB35 PSY UI77220

     UP21/09/22 P F109

• RB36 PSY UI77221

     UP21/09/22 P F109

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.