Utility Enhancements in DB2 12 ESP for z/OS

There are several enhancements to Utilities in DB2 12 for z/OS, including, but not limited to:

  • New UNLOAD Privilege
  • New catalog field COMPRESSRATIO
  • New REORG option to control the deletion of empty PBG partitions
  • REORG on partitions can overflow to newly created PBG partition   
  • LOAD RESUME YES and new keyword BACKOUT YES
  • MODIFY RECOVERY Enhancements
  • Now option “LOB” for the standalone utility DSN1COMP

 There is also a significant amount of Utility enhancements in DB2 12 for z/OS which contribute to the “out of the box” savings realized when migrating from DB2 11 for z/OS.

Here is just a few of them coming in our white paper due out this fall!

New Unload Privilege

In DB2 12, you can set the new AUTH_COMPATIBILITY zparm to specify whether UNLOAD or SELECT privilege is required for running the UNLOAD privilege. AUTH_COMPATIBILITY in macro DSN6SPRM is set to NULL or SELECT_FOR_UNLOAD.

When it is set to NULL, the UNLOAD utility checks if the user has the UNLOAD privilege on the target table. When SELECT_FOR_UNLOAD is specified, the UNLOAD utility checks if the user has the SELECT privilege on the target table. The default is the checking of the UNLOAD privilege. Grants and Revokes can be executed in DB2 V12 CM already, but new authority checking - depending on zparm setting for 'auth_compatibility' - will not be done before activation of NFM. 

Keep in mind, zparm default setting is the new behavior, which is an incompatible change. If you like to keep V11 behavior, you have to set 'auth_compatibility=select_for_unload'.

New catalog field COMPRESSRATIO

This new catalog column is maintained by REORG and LOAD REPLACE utilities independent of KEEPDICTIONARY setting. It helps DB2 utilities to more accurately estimate the sort work data set size. COMPRESSRATIO is placed in SYSIBM.SYSTABLESPACE and SYSTABLEPART catalog tables only if COMPRESS YES is set

New REORG option to control the deletion of empty PBG partitions

New Keyword DROP_PART specifies if the REORG TABLESPACE utility will delete the highest numbered partitions in PBG Tablespace, if they are empty, when the full tablespace is being reorganized. The keyword has no effect on LOB tablespaces that are not defined as PBG and PBGs with MAXPARTITIONS set to 1. When the keyword is not used, the value is taken from the zparm REORG_DROP_PBG_PARTS.

When NO is specified, REORG TABLESPACE will not delete any of the highest numbered partitions in a PBG Tablespace if they are empty on successful completion.

When YES is specified, REORG TABLESPACE will delete any of the highest numbered partitions in a PBG Tablespace if they are empty on successful completion.

This option is only in effect when REORG TABLESPACE is run on the complete object covering all partitions. This new keyword is active after new function activation in V12.

REORG on partitions can overflow to newly created PBG partition

Historically, if the data did not fit to the existing partitions of PBG during reorg because of various reasons, REORG was abending. The most common use case was reformatting the physical structure of tablespace with FREESPACE and PCTFREE values. The workaround was using the REORG_IGNORE_FREESPACE zparm. It helped us for a while until V12.

REORG will no longer fail during a reorganization of a PBG when it is unable to extend partition range.

Now, if the existing partitions do not have enough space to hold the data, new partitions are automatically created up to MAXPARTITIONS and if REORG is terminated the new partitions are not removed. This change is only relevant for Partition level REORG.


This new combination specifies that all rows loaded by the current LOAD should be deleted if DISCARDS input record have violations. The table space is available at the completion of the LOAD and the records deleted are not treated as discarded as a result of the BACKOUT process. The discarded dataset is empty, and you cannot see which records were discarded.


There are occasions where you want to clean up recovery information without having a COPY-Pending status. Before V12, if you use MODIFY RECOVERY to delete all image copy entries for a tablespace or data set, MODIFY RECOVERY places the object in COPY-pending restrictive status and issues message DSNU572I. This process deletes recovery information from DB2 Catalog/Directory but all the physical datasets remain available unless you delete them manually or with automation outside of DB2 Control.

With V12, there is a new option called NOCOPYPEND with MODIFY RECOVERY Utility. When all the Recovery Information is erased from SYSCOPY and SYSLGRNX, it protects COPY-Pending status for tablespace.

And new option called DELETEDS, deletes all backup datasets as well.

Now option “LOB” for the standalone utility DSN1COMP

Problem Statement

Every DBA wants to know exactly what he or she is doing and as such an important prerequisite for taking on new features is a way to estimate what benefit they may bring. It’s even better when such estimation brings new insight that can help to optimize the page size for a LOB tablespace to get even higher level of space savings.

You can now specify the option LOB when the input data set is a LOB table space and DSN1COMP will estimate the space savings and compression ratio for a LOB table space using the zEnterprise data compression (zEDC) hardware.

If the LOB table space uncompressed space savings will be calculated based on collecting data up to the average LOB size (or 1 MB as the maximum size) and pass the collected data to the zEDC card provided such cards are installed in order to produce the DSN1COMP report.

If the LOB table space is already compressed, DSN1COMP will simply collect statistics of the individual LOB metadata from the LOB map page without actually performing any compression.

It should be noted that in order to specify COMPRESS YES for a LOB table space, the base table space must be a universal table space a.k.a UTS.


There are many new functionality and performance enhancements coming with DB2 12 for z/OS Utilities. A lot of these enhancements will improve the performance of your utility window without any changes, while other enhancements enable functionality with a minimal amount of JCL or Code changes.

1 Like
Recent Stories
Techniques to move Db2 data from PROD to TEST (and related considerations)

Autonomic computing in DB2 for z/OS : myth or reality ?

How to innovate your DB2 for z/OS utility environment