PH39194: NEW LOAD OPTION TO MANAGE FREE SPACE ON NPSI

APAR status

• Closed as new function.

Error description

• A new option for the LOAD utility to manange free space on the
  nonpartitioned index (NPSI)
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of LOAD SHRLEVEL                   *
  * NONE PART REPLACE utility using                              *
  * nonpartitioned secondary indexes (NPSI)                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * New function APAR for LOAD SHRLEVEL                          *
  * NONE PART REPLACE utility which                              *
  * introduces a new keyword                                     *
  * KEEP_EMPTY_PAGES, to support space                           *
  * reclamation on nonpartitioned                                *
  * secondary indexes (NPSI).                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR introduces a new keyword, KEEP_EMPTY_PAGES YES|NO, to
  the LOAD SHRLEVEL NONE PART REPLACE utility. This new keyword
  will allow the user to request the removal of empty index leaf
  pages for NPSIs for better space reclamation.
  While KEEP_EMPTY_PAGES NO will reclaim empty index leaf pages
  of the NPSI when keys are deleted from the logical partition,
  performance may be affected on other utilities and SQL
  operations.
  

Problem conclusion

Temporary fix

Comments

• Code has been modified to support new keyword
  KEEP_EMPTY_PAGES for LOAD SHRLEVEL NONE PART REPLACE.
  Documentation for the new keyword will be added to the
  Utility Guide and Reference manual:
  For LOAD utility syntax diagram:
           |--DATA--|
  >--LOAD--|--------|---
        |--KEEP_EMPTY_PAGES YES--|
  >-----|---------------------------
        |--KEEP_EMPTY_PAGES NO---|
  KEEP_EMPTY_PAGES
  Specifies whether the LOAD utility keeps the empty index leaf
  pages of the nonpartitioned secondary indexes (NPSIs) when
  keys are deleted from the logical partitions.
  This option is applicable to only LOAD SHRLEVEL NONE PART
  REPLACE utility executions and does not affect any
  partitioned indexes.
  YES
  Specifies that the LOAD utility keeps the empty index leaf
  pages of the nonpartitioned secondary indexes. This option
  helps improve LOAD utility performance and allows new index
  keys inserted by LOAD or later applications to reuse the kept
  empty index leaf pages
       The default value is YES.
  NO
  Specifies that the LOAD utility removes the empty NPSI leaf
  pages created when keys are deleted from the logical
  partitions being replaced. This option enables space
  reclamation on the NPSIs but can increase the performance
  cost of LOAD utility execution.
  

APAR Information

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

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

  UI80572

Modules/Macros

• DSNURBXE DSNURRST DSNUGDDF DSNUGUCA DSNURBXA DSNUGPRT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels