Utility changes in Db2 12

You can use this information to plan for utility changes in Db2 12.

Important: Db2 12 introduces continuous delivery of new capabilities and enhancements in function levels. Most new capabilities become available only after activation of the Db2 12 function level that introduces them, or when applications run with the corresponding application compatibility level. For more information, see Activating Db2 12 function levels.

Utility changes in function level 501 and higher

The following changes take effect when you activate function level 501 or higher in Db2 12. The changes are listed in function level order with the highest at the top.

Function level	Utility	Change introduced	Incompatible change?
FL 507	LOAD	If you specify the USE PROFILE option with inline statistics, Db2 deletes statistics that are not specified in the profile.	No
FL 507	RUNSTATS	If you specify the USE PROFILE option, Db2 deletes statistics that are not specified in the profile.	No

Utility changes in function level 500

The following changes take effect when you activate function level 500 in Db2 12.

Utility	Change introduced	Incompatible change?
MODIFY RECOVERY	The following new options are added: DELETEDS FLASHCOPY ONLY NOCOPYPEND When MODIFY RECOVERY is run at the table space level, a SYSIBM.SYSCOPY row with ICTYPE='M' and STYPE='R' is inserted for each partition of the table space and any partitioned indexes with the COPY YES attribute that the MODIFY utility processes. This differs from previous versions where only one SYSCOPY record was inserted for the entire table space, regardless of partitions.	No
RECOVER	FROM	No
REORG TABLESPACE	The following new options are added: DROP_PART NO DROP_PART YES ICLIMIT_DASD ICLIMIT_TAPE	No
TEMPLATE	The BLKSZLIM option is added.	No
UNLOAD	The authorization checking is changed, so that Db2 no longer checks for the SELECT privilege on the tables being unloaded.	Yes

Utility changes in function level 100

The following changes take effect when you migrate to function level 100 in Db2 12.

Utility	Change introduced	Incompatible change?
BACKUP SYSTEM	New options: ALTERNATE_CP DBBSG LGBSG	No
CATMAINT	New option: LEVEL. (Specify a function level or catalog level. If you specify a function level Db2 determines the appropriate catalog level.)	No
CATENFM	With the introduction of single-phase migration in Db2 12, the CATENFM utility is not needed for the migration process and is removed.	No
DSN1COMP	New options: COMPTYPE LOB	No
DSNJU008 (print CDDS)	New utility.	No
DSN1LOGP	New option: PART.	No
DSN1PRNT	New option: PART.	No
LOAD	The following new options are added: CCSID in a field specification DEFINEAUX DRAIN_WAIT FORCE IGNORE INVALIDATECACHE KEEP_EMPTY_PAGES NOCHECKPEND OVERRIDE(ROWCHANGE) RETRY PRESORT RETRY_DELAY SWITCHTIME STATCLGMEMSRT STATCLGMEMSRT UPDMAXASSIGNEDVAL The following options are changed: You can now request that the LOAD utility take partition-level sequential image copies by specifying the copy options only once, at the table space level. If the LOAD_RO_OBJECTS subsystem parameter setting is YES, you can load data into read-only objects. You can specify multiple SYSREC data sets by providing multiple `ddname` values in a list. The data sets represented by those `ddname` values are dynamically concatenated to a single `ddname`, which becomes the input to LOAD. A restriction against use of the COLGROUP keyword in conjunction with PARALLEL keyword is removed. In Db2 11, you can collect cardinality, frequency, and histogram statics for column groups when the PARALLEL keyword is specified. DEFAULTIF is enhanced to accept <> (not equal) and CONV_ERROR (conversion error). FREQVAL with MOST, LEAST, or BOTH is supported for indexes. Support is added for new IGNORE options: PART, CONV, VALPROC, IDERROR, DUPKEY Support is added for concurrent access to target tables during LOAD utility processing, with REPLACE SHRLEVEL REFERENCE. Support for a wider range of external date formats is added with DATE EXTERNAL (`date-format`) Support for a wider range of external time formats is added with TIME EXTERNAL (`time-format`) For inline statistics, if you specify the FREQVAL keyword and omit the COUNT keyword, Db2 automatically determines the number of frequently occurring values to collect so that the data distribution is no longer skewed. The LOAD utility now always maintains the value of MAXASSIGNEDVAL in SYSIBM.SYSEQUENCES for any loaded identity columns. The RRF subsystem parameter is removed, and 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 does not specify the ROWFORMAT BRF option, those utilities convert existing basic row format table spaces to reordered row format.	Yes
REBUILD INDEX	The following new options are added: BOTH LEAST MOST INVALIDATECACHE For inline statistics, if you specify the FREQVAL keyword and omit the COUNT keyword, Db2 automatically determines the number of frequently occurring values to collect so that the data distribution is no longer skewed.	No
RECOVER	The following new options are added: ALTERNATE_CP FLASHCOPY_PPRCP INLCOPY NOSYSCOPY KEEP SCOPE	Yes
REORG INDEX	The following new options are added: BOTH LEAST LASTLOG MOST NOSYSUT1 PARALLEL INVALIDATECACHE For inline statistics, if you specify the FREQVAL keyword and omit the COUNT keyword, Db2 automatically determines the number of frequently occurring values to collect so that the data distribution is no longer skewed.	No
REORG TABLESPACE	The following new options are added: INITCDDS INVALIDATECACHE LASTLOG NOCHECKPEND SEARCHTIME STATCLGMEMSRT For inline statistics, if you specify the FREQVAL keyword and omit the COUNT keyword, Db2 automatically determines the number of frequently occurring values to collect so that the data distribution is no longer skewed. The REORG utility processes now partition-by-growth table spaces differently than in prior versions: REORG no longer sets COPY-pending status on newly created LOB table spaces during the LOG phase. Instead, REORG will create inline image copy data sets for those LOB table spaces. REORG will no longer fail during a reorganization of a partition-by-growth table space when it is unable to extend the partition range. Instead, REORG will now create a new partition for the partition-by-growth table space as long as the maximum number of partitions have not been reached. REORG TABLESPACE has a new keyword, DROP_PART NO or DROP_PART YES, that can be specified to delete the highest numbered partitions if they are empty when REORG successfully completes. In prior versions, this behavior could only be accomplished using the REORG_DROP_PBG_PARTS subsystem parameter was set to ENABLE. In addition to the REORG changes for processing partition-by-growth table spaces, REORG in Db2 12 will fail if it is using a FlashCopy and the creation of the online FlashCopy image fails. Also, for REORG TABLESPACE, when SHRLEVEL REFERENCE or SHRLEVEL CHANGE is specified and blocking claimers are encountered, the DISPLAY DATABASE command output will now identify the blocking claimers on each and every drain attempt failure. In prior versions, only the most recent drain attempt failure was identified. Also, 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. Also, the RRF subsystem parameter is removed, and the new 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 does not specify the ROWFORMAT BRF option, those utilities convert existing basic row format table spaces to reordered row format. SORTNPSI YES is changed so that when you specify this option, non-partitioned secondary index (NPSI) keys are sorted.	Yes
REPAIR	The following new options are added: WRITELOG INSERTVERSIONPAGES SETCURRENTVERSION Changed option: The VERSIONS option is no longer supported, and is replaced by the CATALOG option.	No
RESTORE SYSTEM	The following new options are added: ALTERNATE_CP FLASHCOPY_PPRCP RESTOREBEFORE RESTORE SYSTEM now 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.	Yes
RUNSTATS	The following new options are added: INVALIDATECACHE REGISTER STATCLGMEMSRT RUNSTATS now collects frequency and histogram statistics for XML indexes in the following catalog table columns: KEYVALUE, FREQUENCYF, and TYPE in SYIBM.SYSINDEXES CARDF, FREQUENCYF, HIGHVALUE, LOWVALUE, TYPE, QUANTILENO in SYSIBM.SYSKEYTGTDIST Db2 can use frequency and histogram statistics for XML indexes to estimate the filter factor of XMLEXISTS predicates when the following conditions are true: The predicate is as equality (=) or range (>, >=, >=, <=) predicates The right side of the predicate is a literal value. Frequency or histogram statistics are collected for the XML index that matches the XMLEXISTS predicate. For more information, see Collection of statistics on XML objects.	No
UNLOAD	The following new options are added: CCSID in a field specifications REGISTER	Yes

For information about the new options, see the information for the utility. For information about an option with a changed default value, see Utility release incompatibilities.