PI94066: V12 NEW FUNCTION : ENHANCED INSERT ALGORITHM 2 PROCESS TO INCLUDE PERCENT FREE FOR UPDATE FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• V12 new function : Enhanced Insert Algorithm 2 process to
  include percent free for update function
  Additional keywords: IAG2 DB2IAG2/K
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Db2 12 for z/OS users of both Insert                         *
  * Algorithm 2 and PCTFREE FOR UPDATE                           *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * 1. Inadequate free space was reserved                        *
  * on each data page when Insert                                *
  * Algorithm 2 and PCTFREE FOR UPDATE                           *
  * function are used.                                           *
  * 2. SQLCODE -904 (resource not                                *
  * available) with the reason code                              *
  * 00C9009C when object is Universal                            *
  * Table space with Member Cluster                              *
  * and Insert Algorithm 2 is used.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  1. Db2 11 for z/OS implemented the feature PCTFREE FOR UPDATE,
  which indicates the amount of free space reserved on each page
  for subsequent UPDATE operations when data is added to a table
  by an insert operation (an SQL INSERT statement or a utility).
  However, in some cases, when a record is inserted using Db2
  12 insert algorithm 2 into a table that resides in a universal
  table space with the MEMBER CLUSTER attributes, the amount of
  free space that is reserved on each data page might not be
  adequate. As a result, PCTFREE FOR UPDATE may not benefit
  UPDATE performance.
  2. When a universal table space with the MEMBER CLUSTER
  attribute is in a gross locking state, either at the partition
  level or table space level, an INSERT operation might fail
  with SQLCODE -904 (resource not available) with the reason
  code 00C9009C (maximum partition is reached or partition is
  full), when insert algorithm 2 is used to insert the row
  into the table. The failure occurs because the gross lock
  state prevents insert algorithm 2 from preparing available
  pages to be used for insert.
  

Problem conclusion

• This APAR contains the following fixes:
  1. Db2 reserves adequate free space on each data page when
  insert algorithm 2 is used to insert records into a table that
  resides in a  universal table space with the MEMBER CLUSTER
  attribute.
  2. When a universal table space with the MEMBER CLUSTER
  attribute is in a gross locking state, either at the partition
  level or table space level, and an insert operation that uses
  insert algorithm 2 fails, Db2 tries the insert operation with
  insert algorithm 1. If the insert operation also fails with
  insert algorithm 1, Db2 issues SQLCODE -904
  (resource not available) with reason code 00C9009C
  (maximum partition is reached or partition is full).
  

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:

  UI55940

Modules/Macros

• DSNICMT2 DSNISGSC DSNITCUS DSNISFRC DSNISGRT DSNISGSU DSNISGSP
  DSNISGNS DSNISGSS DSNISUPI DSNISFAS DSNISFPI DSNIFOD  DSNIASFP
  DSNISMRT DSNISRTI DSNISCHV
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI55940

     UP18/06/02 P F806

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.