New-function APARs for Db2 12 in 2017

The following enhancements are introduced in the Db2 12 - initial release. That is, they are not controlled by the activation of function level 501 or higher. However, some of these enhancements might be controlled by the activation of function level 500, which is comparable to new-function mode in previous Db2 releases. They are listed roughly in order of availability, beginning with the most recent.

Tip: Depending on how when and how you order the product code for Db2 12 or Db2 13, you might find that the external changes from any of the following APARs are already built-in when you install a new Db2 environment or migrate to Db2 13. Also, you can expect that external changes from the following APARs with availability dates earlier than 2022-06 are likely to be already built-in when you migrate to Db2 13, regardless of whether you apply the PTFs in Db2 12.

LOAD RESUME SUPPORT FOR INLINE COPY

To avoid unavailability of the table space or table space partition, the LOAD syntax has been enhanced with APAR PI81724 (December 2017) to allow the COPYDDN or RECOVERYDDN option to be specified when RESUME YES is specified. A full image copy is taken at the end of LOAD processing, after potential BACKOUT processing.

For more information, see the following related topics:

MQListener automatic restart after Db2 restart

APAR PI84698 (December 2017) introduces automatic restart of MQListener processing after Db2 is stopped and restarted, with the -reconnectDB2 option of the db2mqln1 and db2mqln2 run commands. Before this enhancement, you must restart the MQlistener after stopping and restarting Db2, which can cause service interruptions.

If you specify -reconnectDB2 Y, MQListener automatically reconnects and resumes processing after Db2 is stopped and restarted.

You can specify -reconnectDB2 N to continue with the previous behavior.

For more information, see the following related topics:

New console message for remote client information

APAR PI89903 (December 2017) introduces message DSNL076I, which is issued to the z/OS® system console for SQL statements that are routed to a server that does not support the requested function.

It identifies the remote client user and environment, the clientApplCompat value supplied by the remote client, and the name and APPLCOMPAT value of the associated Db2 package.

For more information, see the following related topics:

DSNTIJSG installation job no longer creates SYSIBM EXPLAIN tables

APAR PI86450 (December 2017) changes the DSNTIJSG installation job to remove CREATE statements for EXPLAIN tables with the SYSIBM qualifier. Db2 no longer uses these tables.

For more information, see the following related topics:

CATMAINT utility LEVEL supports function level values

APAR PI88058 (December 2017) introduces support for Db2 12 function level values for the CATMAINT utility LEVEL option. When you run the CATMAINT utility to tailor the catalog for a new function level, you can specify either the target function level or the corresponding catalog level.

If you specify a function level value, Db2 determines the appropriate catalog level to tailor, and message DSNU777I indicates the result. If the catalog is already at the appropriate level, message DSNU766I indicates that no catalog update is required.

For more information, see the following related topics:

DSNTIJUZ split into task-specific installation jobs

APAR PI85657 (November 2017) introduces several new jobs for various activities that were previously completed by the single DSNTIJUZ installation and migration job:

Before this APAR, the DSNTIJUZ job completes all of these activities, which often happen separately from the others, and for some only rarely. For example, updating the subsystem parameter module is a frequent task when new subsystem parameters are delivered in the service stream, or to update the default application compatibility (APPLCOMPAT) level. Continuous delivery and function levels in Db2 12 are likely to make this a more frequent activity. A similar more frequent activity with continuous delivery is rebuilding the Db2 application defaults module, such as to change the default SQL processing level. However, a rebuild of Db2 offline message generator CCSID module has never yet been required at migration, nor for applying Db2 service.

With the separate jobs, you can choose the jobs that you need for specific activities, without tailoring DSNTIJUZ to remove or disable unneeded job steps.

This APAR also modifies installation panels used by the z/OSMF installation and migration processes for Db2 12. In the following panels, the step for defining Db2 initialization parameters is split to use the new jobs.

For more information, see the following related topics:

Reduced cost for collecting Db2 frequency statistics

With APAR PI76730 (November 2017), your enterprise now has more control over performance when you collect frequency statistics for single-column column groups through RUNSTATS or inline statistics jobs. By adjusting the value of a new subsystem parameter, STATCLGSRT, you can potentially improve performance and reduce costs.

The STATCLGSRT subsystem parameter specifies the amount of memory that Db2 can use to avoid a sort operation for RUNSTATS or other inline statistics utility jobs when FREQVAL is specified on a COLGROUP that identifies one or more single-column column groups. By increasing the value of STATCLGSRT to allocate more memory, you enable Db2 to collect the statistics you need without performing a costly sort.

For more information, see the following related topics:

Changes to address problems after Db2 table definition changes

Db2 for z/OS database administrators often need to alter table definitions to support evolving requirements of applications. For table spaces with multiple tables, the following problems can occur:

In certain situations, such as when a table is copied from one subsystem to another, the table definitions in the catalog might not match the data in the table space, which can lead to data integrity issues. This situation occurs most frequently when alterations are made to multiple tables in a table space.
If a table space is at the maximum version level of 255, and at least one table in the table space is at version 0, you cannot recycle any table space versions. Until you recycle some table space versions, you cannot perform any more version-generating ALTER operations on tables in the table space.

APARs PI86880 and PI88940 (November 2017) provide a number of changes that improve your ability to synchronize the table definitions in the catalog with the data in a table.

Db2 processing changes:

During UPDATE, INSERT, REORG, or LOAD processing on tables that have had no version-changing ALTER operations, Db2 adds information to the table spaces to make them self-describing. This action eliminates the need to rely on catalog and directory information to determine column definitions.
After CREATE TABLE processing, and as soon as a table definition is complete, Db2 adds information to the table space to make the table self-describing. The new table has the version number of the table space.

Important: The previously mentioned Db2 processing changes can result in the addition of system pages to a table space, which increases the total size of the table space. You might also see an increase in the processing time for data definition statements, due to the allocation of additional pages in the table space.

Actions that you can take:

You can run the REPAIR utility with the INSERTVERSIONPAGES and SETCURRENT options to insert missing metadata into a table space, or update table version numbers.
INSERTVERSIONPAGES adds information to a table space or partition that has not had a version-changing ALTER operation, to make the table space self-describing. SETCURRENTVERSION, which must be specified with INSERTVERSIONPAGES and SHRLEVEL NONE, synchronizes the table version numbers for tables in a table space with the table space version number. The table space version number is the highest version number for any table in the table space. After you use SETCURRENTVERSION to update the versions of any tables that are at version 0, you can run the REORG and MODIFY RECOVERY utilities to recycle version numbers that are now unused.
You can run the REORG or LOAD utility to insert missing metadata into a table space.

For more information, see the following related topics:

Support for collection of more accurate real storage usage statistics

With APAR PI78979 (August 2017), IFCID 0225 records contain new fields for collection of statistics about the number of discarded pages that are eligible for page steal. The IFCID 0225 record that is written at the end of the first one-minute statistics interval after midnight local time contains those statistics if the following conditions are true:

Subsystem parameter REALSTORAGE_MANAGEMENT is set to ON or AUTO.
A trace for IFCID 0503 is started.
There are discarded frames.
The PTF for z/OS APAR OA50366 is applied.

You can use those statistics in conjunction with existing real storage usage fields to calculate accurate storage usage once each day. By examining daily storage statistics for multiple weeks, you can determine whether there is an unusual increase in real storage usage.

See the description of IFCID 0225 in prefix.SDSNIVPD(DSNWMSGS) for details.

Important: You might see a performance impact during the time that the new statistics are collected. To avoid unexpected side effects, you might want to enable this enhancement in collaboration with IBM Support.

For more information, see the following related topics:

New LOAD utility IGNORE options

APAR PI77159 (August 2017) introduces support for the following new IGNORE options for the LOAD utility.

PART: Specifies that records that do not satisfy any partition being loaded are ignored.
CONV: Specifies that records that cause a conversion error are ignored.
VALPROC: Specifies that records that fail a validation procedure are ignored.
IDERROR: Specifies that records that have an identity column value that is out of range are ignored.
DUPKEY: Specifies that records that cause a duplicate key error are ignored.

For more information, see the following related topics:

Real-time statistics collection of RUNSTATS-related columns starts at object creation

APAR PI79234 (July 2017)enhances real-time statistics collection for RUNSTATS-related columns in the SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS catalog tables. With this APAR applied, the STATSLASTTIME column indicates the timestamp of the last time that the RUNSTATS utility is run on the table space or partition, or the time that table space or partition was created. The following columns are also updated to indicate the value since the object was created:

STATSINSERTS
STATSUPDATES
STATSDELETES
STATSMASSDELETES

For more information, see the following related topics:

Explicitly-defined hidden ROWID columns

APAR PI77310 (June 2017) removes a restriction against explicitly defining a ROWID column with the IMPLICITLY HIDDEN attribute. Columns with the IMPLICITLY HIDDEN attribute are not returned for statements that do not explicitly list the column name in the select-clause. For example, the result for SELECT * does not include any implicitly hidden columns. To be included in the result, implicitly hidden columns must be explicitly specified in the select list.

For more information, see the following related topics:

Db2 Native Rest Services

APAR PI70652 (May 2017) introduces support for connecting RESTful web, mobile, and cloud applications to DB2 for z/OS by providing an environment for service, management, discovery, and invocation. Mobile and cloud application developers can use efficiently created, consumable, scalable, and RESTful services by using any REST client. They can use these services to securely interact with business-critical data and transactions, without special Db2 for z/OS expertise.

For more information, see the following related topics:

Db2 12 function level 501 activation

Db2 12 function level 501 (PI70535 - May 2017) introduces support for the LISTAGG built-in function.

For more information, see the following related topics:

ADMIN_INFO_SQL stored procedure enhanced to collect UDF information

APAR PI73268 (April 2017) enhances the ADMIN_INFO_SQL stored procedure and the DSNADMSB program to support the collection of information about user-defined functions (UDFs).

For more information, see the following related topics:

LOAD utility support for more date and time formats

With APAR PI69064 (November 2016), the LOAD utility supports a wider range of data and time formats. It introduces the following new DATE EXTERNAL (date-format) and TIME EXTERNAL (time-format) specifications.

(date-format)

The format of the date representation, as shown in the following table.

Table 1. Values for date-format
`date-format` value	Format	Length
DATE_A	`mm-dd-yyyy` ¹	10 bytes
DATE_B	`mm-dd-yy` ^{1, 2}	8 bytes
DATE_C	`yyyy-mm-dd` ¹	10 bytes
DATE_D	`yy-mm-dd` ^{1, 2}	8 bytes
DATE_E	`dd-mm-yyyy` ¹	10 bytes
DATE_F	`dd-mm-yy` ^{1, 2}	8 bytes
DATE_G	`yyyy-ddd` ¹	8 bytes
DATE_H	`yy-ddd` ^{1, 2}	6 bytes
DATE_I	`mmddyyyy`	8 bytes
DATE_J	`mmddyy` ¹	6 bytes
DATE_K	`yyyymmdd`	8 bytes
DATE_L	`yymmdd` ¹	6 bytes
DATE_M	`ddmmyyyy`	8 bytes
DATE_N	`ddmmyy` ¹	6 bytes
DATE_O	`yyyyddd`	7 bytes
DATE_P	`yyddd` ¹	5 bytes
Notes: If the format includes separators, the separator can be any single-byte character that can be converted to a single-byte EBCDIC character. However, if the input file has a delimited format, you cannot specify the same character that is used for a delimiter, including the COLDEL, CHARDEL, and DECPT delimiters. For more information about delimiter restrictions, see Loading delimited files. If a two-digit year is specified, it is expanded to a four-digit year. If the two-digit year is less than the sum of the two-digit current year plus 50, then the current century is used in the four-digit year. For example, assume that the current year is 2017. If the two-digit year is 67, 1967 is used. However, if the two-digit year is 66, 2066 is used. If a date format is specified for a field that is used in a field specification, the field specification must also use the specified date format.

(time-format)

The specific format of the time representation, as shown in the following table.

Table 2. Values for time-format
`time-format` value	Format	Length
TIME_A	`hh.mm.ss` ¹	8 bytes
TIME_B	`hh.mm` ¹	5 bytes
TIME_C	`hh.mm AM` or `hh.mm PM` 1	8 bytes
TIME_D	`hhmmss`	6 bytes
TIME_E	`hhmm`	4 bytes
Notes: If the format includes separators, the separator can be any single-byte character that can be converted to a single-byte EBCDIC character. If a time format is specified for a field that is used in a field specification, the field specification must also use the specified time format.

For more information, see the following related topics: