PM75150: ALLOW MAXIMUM NUMBER OF PIECES FOR AN NPI OF LARGE TABLE SPACE ,INDEPENDENT OF TABLE SPACE MAXIMUM NUMBER OF DATASETS

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Allow maximum number of pieces for an NPI of large table space ,
  independent of table space maximum number of datasets .
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of Non-Partitioned Index       *
  *                 (NPI) on a large table space.                *
  ****************************************************************
  * PROBLEM DESCRIPTION: For a large table space, the maximum    *
  *                      number of pieces for an NPI is          *
  *                      incorrectly inherited from the          *
  *                      maximum number of partitions of the     *
  *                      table space. When inserting data to a   *
  *                      table or creating the NPI, the customer *
  *                      may receive the following message,      *
  *                                                              *
  *                      DSNT501I                                *
  *                      DMBC DSNKINSL RESOURCE UNAVAILABLE      *
  *                      CORRELATION-ID=QFSBTC036                *
  *                      CONNECTION-ID=SERVER                    *
  *                      LUW-ID=                                 *
  *                      GAF263F9.C741.120818101839=561408       *
  *                      REASON 00C9009D                         *
  *                      TYPE 00000201                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  For an NPI on a LARGE(5-byte RID) table space, the index
  maximum number of pieces is independent of the table space
  maximum number of partitions, it is calculated from index page
  size and piece size as the following:
  
  Maximum NPI number of pieces =
  MINIMUM( 4096, 2 32 / (index piece size/index page size) )
  

Problem conclusion

Temporary fix

Comments

• DB2 code has been changed to calculate the correct maximum
  number of pieces for an NPI.
  
  Applying the apar does not correct the indexes that were created
  before the apar. ALTER INDEX CLOSE can fix the maximum number of
  pieces for the NPI.
  
  additional keyword: SQLALTER,SQLCREATE, RC00C9009D, MSGDSNT501I
  
  ++HOLD COMMENT FOR RELEASE A10, TYPE DOC:
  After APAR PM75150 changes, for an Non-Partitioned Index (NPI)
  on a LARGE (5-byte RID) table space, the index maximum number
  of pieces is independent of the table space maximum number of
  partitions, it is calculated from index page size and piece
  size as the following:
  
  Maximum NPI number of pieces =
  MINIMUM( 4096, 2 32 / (index piece size/index page size) )
  
  Information about this change will be updated in the Information
  Management Software for z/OS Solutions Information Center
  (http://publib.boulder.ibm.com/infocenter/imzic) at
  DB2 10 for z/OS > DB2 reference information > DB2 SQL >
  Statements > CREATE INDEX.
  
  ++HOLD COMMENT FOR RELEASE A10, TYPE ACT:
  After applying APAR PM75150, the following steps may help find
  the affected Non-Partitioned Indexes (NPIs) and the incorrect
  index maximum number of pieces can be fixed by ALTER INDEX ...
  CLOSE.
  
  1. run a query, such as the sample query below, to identify
     the databases, table spaces and indexes,
  
  SELECT TS.DBNAME AS DB_NAME,
         TS.NAME AS TS_NAME,
         IX.NAME AS INDEX_NAME,
         IX.CREATOR AS IX_CREATOR,
         HEX(IX.OBID) AS INDEX_OBID,
         IX.CLOSERULE AS CLOSERULE
    FROM SYSIBM.SYSINDEXES IX,
         SYSIBM.SYSTABLES TB,
         SYSIBM.SYSTABLESPACE TS
    WHERE TB.NAME = IX.TBNAME AND
          TB.CREATOR = IX.TBCREATOR AND
          TB.TSNAME = TS.NAME AND
          TB.DBNAME = TS.DBNAME AND
          (TS.PARTITIONS > 254 OR
           TS.TYPE = 'L' OR
           TS.TYPE = 'K' OR
           TS.DSSIZE > 0) AND
          TS.PARTITIONS <> 0 AND
          (TS.STATUS = 'C' OR
           NOT ((IX.INDEXTYPE = 'P' OR
               IX.INDEXTYPE = 'D') AND
            TB.PARTKEYCOLNUM <>0)) AND
          TS.DBNAME <> 'DSNDB06'
    ORDER BY IX.OBID;
  
  +------------------------------------------------------------+
  |DB_NAME |TS_NAME |INDEX_NAME|IX_CREATOR|INDEX_OBID|CLOSERULE|
  +------------------------------------------------------------+
  |DB161410|XTB10000|I_NODEIDXT|SC161410  |0056      |N        |
  |DB161410|TU161410|I_DOCIDTB1|SC161410  |0058      |N        |
   ...
  
   2. run REPAIR DBD DIAGNOSE against all databases from the
      above query.
  
  For example REPAIR DBD DB161410. By searching OBDINNUM in th
  REPAIR DBD output, it has the following mismatch,
  
  DSNU916I DSNUEDFT - OBDS DIFFER AT OFFSET X'00000052'
                      OBDINNUM - INFORMATION NOT IN DB2 CATALOG
  DSNU904I DSNUEDFT - EXISTING VALUE  X'0100'
  DSNU905I DSNUEDFT - REBUILT  VALUE  X'1000'
  DSNU913I DSNUEDDR - OBD X'0056' COMPARISON COMPLETE -- 0 ERRORS
  
  which confirms the index index I_NODEIDXT with OBID X'0056' has
  incorrect maximum number of index pieces.
  
  3. fix these indexes found in the above repair DBD output by
     altering the CLOSE attribute of the identified indexes
     from their current value and then altering the CLOSE attribut
     back to their original value. The index name and index
     attribute information can be found in the query result at
     step 1.
  
  for example, the index SC161410.I_NODEIDXT has the CLOSE
  attribute CLOSERULE = N. To correct the maximum number of index
  pieces for I_NODEIDXT, run the following DDL
  
     ALTER INDEX index SC161410.I_NODEIDXT CLOSE YES
     ALTER INDEX index SC161410.I_NODEIDXT CLOSE NO
  

APAR Information

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

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

  UK91979

Modules/Macros

• DSNGDADP DSNGDAIP DSNGDAIX DSNGDBIX DSNUEDFT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK91979

     UP13/03/13 P F303

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.