Utility release incompatibilities
When you migrate to Db2 11, be aware of the utility release incompatibilities.
Plan for the following changes in Db2 11 that might affect your migration.
Changes to the mapping table for REORG
Explanation
In Db2 11, the format of mapping tables has changed to accommodate the new 10-byte format of the RBA and LRSN values. (Mapping tables are used by the REORG TABLESPACE utility when SHRLEVEL CHANGE is specified.) The LRSN column in the mapping table has changed to CHAR(10) instead of CHAR(6).
Possible impact to your Db2 environment
Beginning in Db2 11 new-function mode, if you use the DB2® 10 format for a mapping table and try to specify it in your REORG statement, REORG does not use it. Instead, the utility implicitly creates a mapping table in the Db2 11 format in the same database as the mapping table that you specified.
Actions to take
Either create your mapping tables according to the Db2 11 format or take advantage of the new feature where REORG creates the mapping tables for you.
Differences in page set format impacting DSN1COPY utility
Explanation
In Db2 11 new-function mode (NFM), you can convert page sets from basic format to extended format to support 10 byte log RBA and LRSN values. Such extended format page sets are not compatible with DB2 10 or earlier releases. The use of DSN1COPY to copy an extended format page set to a Db2 system running on a prior release of Db2 results in errors during subsequent access on that system.
Possible impact to your Db2 environment
Errors might occur on the Db2 system to which the extended format page set is copied if that system is running a release of Db2 prior to Db2 11 or if the system is running Db2 11 in conversion mode.
Actions to take
Prevent DSN1COPY or other moves of extended format page sets from a Db2 11 system to a system running a prior release of Db2. If DSN1COPY is used to copy page sets between systems, do not convert page sets to extended format until all relevant systems have been migrated to Db2 11.
Parallelism change for the COPY and RECOVER utilities
Explanation
In Db2 11 conversion mode, the degree of parallelism can decrease for the COPY and RECOVER utilities.
The new PARAMDEG_UTIL subsystem parameter now applies to COPY and RECOVER control statements that include the PARALLEL keyword.
If you specify the PARALLEL keyword, the number of subtasks for PARALLEL always overrides the specification of the PARAMDEG_UTIL subsystem parameter. Therefore, PARALLEL can be smaller or larger than the value of PARAMDEG_UTIL.
Possible impact to your Db2 environment
The maximum number of objects that are processed in parallel for the COPY and RECOVER utilities might be lower than in previous releases.
Actions to take
If you want to increase the number of objects that are processed in parallel, increase the value of the PARAMDEG_UTIL subsystem parameter.
Parallelism change for the CHECK INDEX, LOAD, REBUILD INDEX, and REORG TABLESPACE utilities
Explanation
In Db2 11, the following utilities can use larger degrees of parallelism:
- REORG TABLESPACE
- REBUILD INDEX
- CHECK INDEX
- LOAD
The main goal of the increased degree of parallelism is to reduce elapsed times. However, extra virtual storage might be needed. Sort operations that are invoked by the utilities are the main consumers of the storage. If the amount of available virtual storage is not sufficient for the extra demand, the increased parallelism might drive CPU consumption and elapsed times higher.
The region size of the utility job (as set by the REGION parameter of the job, or by an IEFUSI exit) controls the amount of virtual storage that might be used. The region contains the administrative portion of the sort work and the buffers, which are sorted in each subtask. This virtual storage requirement is usually in the range of a few megabytes per task, but it can grow to a few hundred megabytes for large sorts (in the terabyte range). This memory consumption is essential for good performance. In most cases, it does not have a large effect on real storage.
In addition, sort products, such as Db2 SORT and DFSORT, can use auxiliary storage to store intermediate results. The auxiliary storage is used as an alternative to writing the pre-sorted pieces to sort work data sets. The auxiliary storage use can easily grow into the multiple gigabyte range for large sorts.
Possible impact to your Db2 environment
The increased parallelism can reduce elapsed times. However, because more storage might be required, you must consider the trade-offs. If virtual storage for the region is constrained, CPU time and elapsed time might increase for large sort operations because the sort operations become inefficient. If real storage is constrained and more auxiliary storage is used by the extra, the increased paging might impact system performance.
The amount of extra storage that is used depends on the size of the data to be sorted, the sort product that is used, and the configuration of the sort product.
Actions to take
To take advantage of the increased parallelism and achieve improved elapsed times:
- Use the REGION parameter value of the utility job (or IEFUSI exit setting) to increase the amount of virtual storage that can be used. DFSORT invocations now use the all of the available storage in the region size. Db2 Sort for z/OS® has always used all of the available storage in the region size.
- Configure the sort products to write intermediate results to work files, instead of auxiliary storage, if real storage consumption is a problem.
If your system cannot tolerate the increased virtual storage and CPU consumption:
- Specify the PARALLEL n keyword in the utility control statement, or the value of the PARAMDEG_UTIL subsystem parameter, to restrict the degree of parallelism.
- Use the REGION parameter value (or IEFUSI exit setting) to limit the amount of storage that is used.
- Configure the sort products to write intermediate results to work files, instead of auxiliary storage, if real storage is a problem.
Changes to REORG default values
Explanation
In Db2 11 conversion mode, the following changes are made to the default values for the REORG utilities:
The following changes are made to the default values for the REORG TABLESPACE utility:
- The default DRAIN value is changed from WRITERS to ALL.
- The NOPAD keyword is now the default value in the UNLOAD EXTERNAL clause and the DISCARD clause.
The following changes are made to the default values for the REORG INDEX utility:
- The default DRAIN value is changed from WRITERS to ALL.
Possible impact to your Db2 environment
By default, Db2 drains all readers and writers during the log phase, instead of only writers.
By default, the variable-length columns in the unloaded or discarded records are to occupy the actual data length without additional padding.
Actions to take
If you want Db2 to drain only the writers during the log phase, specify DRAIN WRITERS in your utility control statement.
If you want the REORG processing to pad variable-length columns, specify NOPAD NO in your utility control statement.
Change to DSNU126I return code when running REORG on an LOB table space
Explanation
In Db2 11 conversion mode, the return code from message DSNU126I changed to 8.
Running REORG TABLESPACE SHRLEVEL NONE on an LOB table space was deprecated in a prior release and completed with return code 0. The return code of message DSNU126I has changed to return code 8 to indicate the function is no longer supported.
Possible impact to your Db2 environment
REORG jobs that previously completed with message DSNU126I return code 0 might now fail with DSNU126I return code 8.
Actions to take
Review your REORG jobs for instances of DSNU126I and remove any REORG TABLESPACE SHRLEVEL NONE on a LOB table space.
Changes to RECOVER utility
Explanation
In Db2 11 conversion mode, the following changes are made to the RECOVER utility:
- TOLOGPOINT, TORBA, and RESTOREBEFORE keywords can accept basic 6-byte format or extended 10-byte format based on the length of the RBA or LRSN value that is specified. Previously, any length was accepted and then extended or truncated as required.
Possible impact to your Db2 environment
Operands of 6 bytes or less are interpreted as being in basic 6-byte format. Operands greater than 6 bytes are interpreted as being in extended 10-byte format. In both cases, padding on the left with X'00' occurs to form complete 6-byte or 10-byte operands. Conversion between basic and extended format is performed as required for the recovery operation.
Actions to take
Update the RBA or LRSN values that are specified for the TOLOGPOINT, TORBA, or RESTOREBEFORE keywords to indicate either basic 6-byte format or extended 10-byte format.
Changes to DSNACCOX stored procedure default values
In Db2 11 new-function mode (NFM), the following changes are made to the defaults of the DSNACCOX stored procedure:
Explanation
RRTDataSpaceRat input parameter default value is -1. Previously, it was 2.0.
Possible impact to your Db2 environment
Review your calls to the DSNACCOX stored procedure. Look for NULL as the value of RRTDataSpaceRat. The new default turns off this criterion. Any positive values continue to be processed as in DB2 10.
Actions to take
To continue the DB2 10 behavior, change RRTDataSpaceRat input parameter values of NULL to 2.0 to match the DB2 10 default. To turn off this criterion, leave the default as NULL.
Changes to DSNACCOX stored procedure result set
In Db2 11 new-function mode (NFM), new values are possible in the OBJECTTYPE column of the DSNACCOX recommendation result set.
Explanation
’XS’ for XML table spaces and ’LS’ for LOB table spaces are now possible values of the OBJECTTYPE column results.
Possible impact to your Db2 environment
Review your processing of results from the DSNACCOX stored procedure. Unexpected values might be handled as invalid.
Actions to take
Update your process to handle the new possible values.
Changes to DSNACCOX stored procedure processing for REORG and COPY recommendations
In Db2 11 new-function mode (NFM), more information is evaluated when REORG or COPY is recommended.
Explanation
When the input parameter QueryType specifies REORG or COPY recommendations, DSNACCOX also checks the database exception table (DBET) entry for an exception state.
Possible impact to your Db2 environment
Review your processing of results from the DSNACCOX stored procedure. Unexpected values might be handled as invalid. Database exception table (DBET) states are added to the OBJECTSTATUS column of the result set.
Actions to take
Update your process to handle the new possible values.
Changes to DSNACCOX stored procedure processing for ChkLvl 8
In Db2 11 new-function mode (NFM), a new row is not inserted if the result set already has a recommendation for a utility operation.
Explanation
When the input parameter ChkLvl specifies level 8 processing, DSNACCOX adds the utility operation recommendation to an existing row if one exists for the object. If an existing row does not exist, DSNACCOX continues to add a row.
Possible impact to your Db2 environment
Review your processing of results from the DSNACCOX stored procedure. Unexpected values might be handled as invalid. New rows for objects are only added to the result set if the object is not already present.
Actions to take
Update your process to handle the new possible values.
ALTER statements that change limit keys are pending changes
Starting in Db2 11 new function mode, ALTER statements that modify limit key values result in pending data definition changes, which do not take effect until materialized by the REORG utility. They also block all subsequent immediate changes until materialized.
Explanation
In Db2 11, ALTER statements that change limit keys for the following types of table spaces result in pending data definition changes:
- partition-by-range spaces
- Partitioned (non-UTS) tables spaces with table-controlled partitioning
Affected partitions are placed in advisory REORG-pending (AREOR) status. In DB2 10, such ALTER statements resulted in immediate changes, which placed affected partitions in restrictive REORG-pending (REORP) status.
ALTER statements that alter the last partition are an exception if they change MAXVALUE to a value less than MAXVALUE for ascending, or from MINVALUE to a value greater than MINVALUE for descending. In such cases, the changes are immediate and affected partitions are placed in restricted REORG-pending status.
Possible impact to your Db2 environment
Affected partitions remain in advisory REORG-pending (AREOR) status, the old limit key values remain in effect, and the data remains available until the pending definition change is materialized. However, all subsequent immediate data definition changes, including in the same statement, remain blocked until the pending data definition changes are materialized. Table spaces with pending definition changes have an entry in SYSIBM.SYSPENDINGDDL.
Also, you can no longer materialize changes for ALTER statements that change limit keys by using the REORG TABLESPACE utility with SHRLEVEL NONE or the LOAD utility with REPLACE.
Actions to take
Modify any existing jobs that materialize limit key changes to run the REORG TABLESPACE utility with SHRLEVEL CHANGE or SHRLEVEL REFERENCE.
For jobs that use the LOAD utility with REPLACE, modify the job to run the REORG TABLESPACE utility with SHRLEVEL CHANGE or SHRLEVEL REFERENCE before running LOAD.