Db2 utility release incompatibilities
When you migrate to or apply maintenance in Db2 12, be aware of the Db2 utility release incompatibilities that might affect your Db2 environment.
The following incompatible changes apply at any Db2 12 function level, including when you first migrate to Db2 12. For incompatible changes that might impact your Db2 12 environment when you activate function levels 501 and higher, see Incompatible changes summary for function levels 501 and higher.
RECOVER utility changed to issue new messages for errors during index log processing
Starting in Db2 12 at function level 100, the RECOVER utility will issue messages DSNU1577I and DSNU1578I for errors encountered during index log processing. DSNU599I will no longer be issued in these cases.
Actions to take
If you have a process that scans for error messages in RECOVER utility output, you might need to change the process to scan for the message DSNU1578I, which is now issued to report log processing errors on indexes. You might also need to change the process to scan for the message DSNU1577I which is now issued by utilities to report indexes in page set REBUILD-pending status when a utility finishes.
New LOAD utility behavior change for identity columns
Starting in Db2 12 at function level 100, the LOAD utility always maintains the value of MAXASSIGNEDVAL in SYSIBM.SYSEQUENCES for any loaded identity columns.
Prior to Db2 12, the LOAD utility did not maintain the value of MAXASSIGNEDVAL when the identity column value was provided by the user. The identity column value can be provided by the user if the column is defined as GENERATED BY DEFAULT or if the column is defined as GENERATED ALWAYS and the OVERRIDE(IDENTITY) option is specified in the LOAD statement.
Actions to take
If your existing applications have logic that relies on the behavior prior to Db2 12, you can disable this new behavior by specifying UPDMAXASSIGNEDVAL NO in your LOAD statements. To aid your migration to Db2 12, you can begin adding this new option to your LOAD jobs in Db2 11. Although UPDMAXASSIGNEDVAL NO is accepted in Db2 11, the option has no effect until Db2 12.
Changes to REORG TABLESPACE PART SORTNPSI YES behavior
Starting in Db2 12 at function level 100, when you specify the REORG TABLESPACE utility option SORTNPSI YES or set the REORG_PART_SORT_NPSI subsystem parameter to YES, REORG sorts the keys for all non-partitioned secondary indexes (NPSIs). Previously, when you specified these options, REORG sorted NPSIs only when certain internal thresholds were met. Therefore, some REORG SORTNPSI YES jobs that previously did not qualify for a sort operation will include a sort operation in Db2 12. Sorts can impact performance and might increase the elapsed time of those utility jobs.
Actions to take
If the extra sort operations cause a performance impact, set the subsystem parameter REORG_PART_SORT_NPSI to AUTO, or specify SORTNPSI AUTO in the REORG TABLESPACE utility statement.
Changes to the default behavior of REORG TABLESPACE and LOAD REPLACE
In Db2 12, the RRF subsystem parameter is removed. The default behavior is RRF=ENABLE, which means that:
- Newly created table spaces or newly added partitions on partition-by-growth table spaces are created in the reordered row format.
- If LOAD REPLACE or REORG TABLESPACE do not specify the ROWFORMAT BRF option, those utilities convert existing basic row format table spaces to reordered row format.
If the HONOR_KEEPDICTIONARY subsystem parameter is set to or defaults to NO, and LOAD REPLACE or REORG TABLESPACE converts an existing basic row format table space to reordered row format, a very large amount of sort work space is needed for decompression of the rows. The utilities fail if there is not enough sort work space available.
Actions to take
Set subsystem parameter HONOR_KEEPDICTIONARY to YES, and run LOAD REPLACE or REORG TABLESPACE with the KEEPDICTIONARY option. When you take these actions, Db2 does not decompress the data when the utilities run.
Changes to the mapping table for REORG
In Db2 12, the format of mapping tables has changed to accommodate the 7-byte RIDS that are needed for partition-by-range table spaces with relative page numbering. (Mapping tables are used by the REORG TABLESPACE utility when SHRLEVEL CHANGE is specified.) In the mapping table, the ORID column has changed to CHAR(7) instead of CHAR(5), and the NRID column has changed to CHAR(11) instead of CHAR(9).
After Db2 12 function level 500 or higher is activated, if you use the Db2 11 format for a mapping table, REORG TABLESPACE does not use it. Instead, the utility implicitly creates a mapping table in the Db2 12 format in the same database as the mapping table that you specified.
Actions to take
Either create your mapping tables according to the Db2 12 format or allow REORG to implicitly create the mapping tables for you.
Changes to the DSNACCOX stored procedure result set
In Db2 12, the DSNACCOX result set includes one or more new columns. This expanded result set is returned when the function level is function level 500 or higher. The result set includes the new column RRTPBGSPACEPCT. This column is reserved for future use.
Additionally, the result set always includes two columns that were optional in previous releases. In Db2 12, columns RRIEMPTYLIMIT and RRTHASHOVRFLWRAT are always returned when the function level is function level 500 or higher.
Applications that issue SELECT(*) against the DSNACCOX result set might fail or return unpredictable results.
Actions to take
Review any applications that process the results of the DSNACCOX stored procedure. If necessary, update them to handle the new result set.
Changes to allocation of image copy data sets on tape for RECOVER
In Db2 12, in RECOVER jobs, you can no longer include DD statements in the JCL to allocate image copy data sets that are on tape. In Db2 12, you must let RECOVER dynamically allocate image copy data sets that are on tape.
RECOVER jobs that contain DD statements for image copy data sets that are on tape fail with error DSNU030I.
Actions to take
If a RECOVER job fails with error DSNU030I, find DD statements for image copy data sets that are on tape, and remove the DD statements.
Changes to the behavior of the RESTORE SYSTEM utility
In Db2 12, RESTORE SYSTEM fails if either of the following situations occur:
- The most recent system-level backup before the recovery point is not registered in the DFSMShsm repository.
- An error is returned by DFSMShsm during the restore of the production volumes.
In previous releases, if RESTORE SYSTEM encountered a problem with the system-level backup, the utility automatically used the previous system-level backup. The new behavior in Db2 12 gives you control over the situation and allows you to determine the best course of action.
RESTORE SYSTEM jobs might fail.
Actions to take
Determine how you want to handle these failures. If you want to use an older system-level backup, use the new RESTOREBEFORE option in the RESTORE SYSTEM utility statement.
Changes to the invalidation of cached dynamic SQL statements by utilities that collect statistics
In Db2 12, the RUNSTATS utility and other utilities that collect inline statistics do not invalidate dynamic statements in the dynamic statement cache by default. In previous releases, the collection of statistics for an object always invalidated cached dynamic statements that referred to the objects. In Db2 12, the INVALIDATECACHE option is added to the RUNSTATS utility and to the following utilities that collect inline statistics:
- LOAD
- REBUILD INDEX
- REORG INDEX
- REORG TABLESPACE
The default option INVALIDATECACHE NO means that the utility does not invalidate statements in the dynamic statement cache because statistics are collected. Db2 might continue to use access paths that are based on stale statistics for dynamic SQL statements that remain in the dynamic statement cache.
Actions to take
Add the INVALIDATECACHE YES option to any existing utility jobs that collect statistics and must invalidate SQL statements in the dynamic statement cache.
Changes to the behavior of the UNLOAD utility
In Db2 12, the UNLOAD utility includes the REGISTER option, which specifies whether, in a data sharing environment, pages that are read by the UNLOAD utility are registered with the coupling facility. REGISTER NO reduces data sharing overhead. In previous releases, the UNLOAD utility had only REGISTER YES behavior. In Db2 12, the original default for UNLOAD was REGISTER NO, but APAR PI99075 changes the default to REGISTER YES. REGISTER NO causes pages to be read directly from disk, and not from local or group buffer pools.
Actions to take
Apply APAR PI99075 to restore Db2 11 default behavior. Then update UNLOAD utility jobs with REGISTER NO, when appropriate to reduce data sharing overhead.
Changes to the authorization check of the UNLOAD utility
After the activation of function level 500 or higher, Db2 checks for the new UNLOAD privilege, instead of the SELECT privilege, for executing the UNLOAD utility and UNLOAD jobs might fail due to authorization errors.
Db2 checks the new UNLOAD privilege for authorization of the UNLOAD utility. You must use a privilege set that includes one of the following to execute the UNLOAD utility:
- Ownership of the tables
- UNLOAD privilege on the tables
- SELECT privilege on the tables (if the AUTH_COMPTIBILITY subsystem parameter is set to SELECT_FOR_UNLOAD)
- DBADM authority for the database. If the object on which the utility operates is in an implicitly created database, DBADM authority on DSNDB04 or the implicitly created database is sufficient.
- DATAACCESS authority
- SYSADM authority
- SYSCTRL authority (catalog tables only)
- SQLADM authority (catalog tables only)
- System DBADM authority (catalog tables only)
- ACCESSCTRL authority (catalog tables only)
- SECADM authority (catalog tables only)
When an UNLOAD job is run by a user with only the SELECT privilege on the target table, the UNLOAD job fails with message DSNU1253I.
Actions to take
Take any of the following actions:
- Before migration to Db2 12, run job DSNTIJPM, and follow the steps in premigration report 19 to determine which authorization IDs might need the UNLOAD privilege.
- In Db2 11 or Db2 12, start a trace for IFCID 404 to identify the users who need the UNLOAD privilege. Then, in Db2 12 at function level 100, grant the UNLOAD privilege to those users.
- As a temporary solution, set the AUTH_COMPATIBILITY subsystem parameter to SELECT_FOR_UNLOAD so that the SELECT privilege continues to be checked when the UNLOAD utility runs. For more information, see AUTH_COMPATIBILITY in macro DSN6SPRM.