Recent enhancements in Db2 11

A number of other enhancements to Db2 11 after general availability are introduced through APARs.

The enhancements described here are listed roughly in order of availability, beginning with the most recent.

Improved reliability and contention relief for online migration (APAR PH10826 - May 2020)

This APAR introduces the capability to identify applications that might be incompatible with catalog migrations and updates.

Before scheduling an online migration, you can issue the new DISPLAY BLOCKERS command or invoke the new BLOCKING_THREADS built-in function to identify locks and claims that might interfere with migration processing. This information allows you to take appropriate actions beforehand to minimize the possibility of a failed migration.

The new command and function can be used for both system and non-system databases. For more information, see Identify applications that are incompatible with online migration.

New job DSNTIJPM premigration report 23: SQL routines created in DB2 9 (PH07660 - November 2019)

APAR PH07660 adds report 23 to the premigration job DSNTIJPM. Report 23 identifies native SQL procedures and compiled SQL scalar functions created in DB2® 9. Such routines are subject to automatic binds after migration to Db2 12. You can use the report to regenerate these routines before migration to avoid performance problems that can result from too many concurrent automatic bind service tasks.

Ability to control workload balancing of accelerated queries based on accelerator version (PH05477 - September 2019)

In a Db2 for z/OS environment that includes a mixture of IBM Db2 Analytics Accelerator for z/OS V5 and V7 accelerator servers, PH05477 enables the ability to control workload balancing such that queries can be accelerated to either a V5 accelerator or a V7 accelerator based on workload volume. Prior to PH05477, workload balancing was limited to a single version of an accelerator.

To control this workload balancing behavior, a new option (10 - BALANCE WORKLOAD BETWEEN VERSIONS) has been added to the QUERY_ACCEL_OPTIONS subsystems parameter.

New job DSNTIJPM premigration report 24: active log data sets larger than 4 GB (PH11514 - July 2019)

APAR PH11514 adds report 24 the to the premigration job DSNTIJPM. Report 24 identifies active log data sets greater than 4 GB in size. Before function level 500 is activated in Db2 12, the maximum supported active log data set size is 4 GB. Consequently, during migration to Db2 12, if the size of an active log data set in Db2 11 is greater than 4 GB, Db2 12 abnormally terminates during startup and issues message DSNJ158I. You can use the report to avoid this situation.

Multiple captures on a single proxy for GDPS Continuous Availability with zero data loss (PI80787 - June 2019)

The GDPS® Continuous Availability with zero data loss solution provides disaster recovery with continuous application availability for enterprise businesses using the z/OS operating system. When Db2 is in a GDPS Continuous Availability with zero data loss environment, IFI applications issue READS calls for IFCID 0306 to a proxy data sharing group, and the proxy data sharing group captures log records from a source data sharing group.

Before this APAR, the GDPS Continuous Availability with zero data loss solution supported only a single IFCID 306 reader on the proxy member. To minimize the capture latency during a maintenance window, Db2 is enhanced to support multiple IFCID 306 readers on the proxy.

New environment variable to control the MQListener log size (PH07230 - March 2019)

This APAR lets you control the maximum size of the MQListener HFS log file using new environment variable MQLSNLWR.

Before this APAR, the HFS log file into which MQListener writes diagnostic information could grow until it reached the HFS limit. For many users, that file size was much too big. After this APAR is applied, users can specify the maximum size for the log file. MQListener creates a copy of and reinitializes the log file when it reaches the maximum size.

Avoid external sorts for single-column column groups at the utility job level (PH03678 - February 2019)

When collecting statistics for a single-column column group, avoiding external data sorts can improve utility performance. Previously, you could control the sort behavior by using the STATCLGRST subsystem parameter. STATCLGRST specifies the amount of memory a utility can use for sorting for single-column groups instead of invoking an external sort program. However, STATCLGRST is a global setting, and if the value is large, excessive amounts of memory might be allocated for all utility jobs. Now, you can control this behavior at the utility level by using the new keyword STATCLGMEMSRT. This keyword applies to RUNSTATS, LOAD, and REORG TABLEPSACE.

User-defined function with MODIFIES SQL DATA from a subselect (PI93887 - January 2019)

This APAR introduces support in Db2 11 for invoking a user-defined function that is defined with MODIFIES SQL DATA in a subselect. Previously, Db2 issued SQLCODE -740 for this case.

The documentation of the MODIFIES SQL DATA option is also changed to include the following note:
Recommendation: If you invoke a function defined with the MODIFIES SQL DATA option in a subselect, make sure that the SQL statements inside the function do not modify any object that is referenced by that subselect.

IBM zHyperLink: database synchronous read I/O exploitation (PH01123 - December 2018)

This APAR introduces support for performance improvements derived from IBM® zHyperLink support. In a well-tuned environment, this feature can improve average response latencies for database page reads in online transactions.

The ZHYPERLINK subsystem parameter is introduced for enabling this feature, along with instrumentation to estimate the potential benefit of using the zHyperLink read support, and instrumentation to report the results of zHyperLink requests.

Allow ROTATE PARTITION for materialized query tables or tables having dependent materialized query tables (PH00194 - October 2018)

This APAR removes a restriction against rotating partitions when altering materialized query tables (MQTs) or tables with MQTs defined on them. Before this APAR, the ROTATE PARTITION clause of ALTER TABLE statements was restricted if the tables being altered were MQTs or tables with dependent MQTs.

Db2 command line processor (CLP) support for user name and credentials for running SQL files (PH00335 - September 2018)

This APAR introduces support for the Db2 command line processor to accept username and credential parameters when it is invoked to run SQL files. The credentials can be a password or RACF® PassTicket and you can specify them in the new -u option flag when you start the Db2 command line processor.

Example: Specifying a user ID and password or RACF PassTicket to run an SQL file
Suppose that you want to use the Db2 command line processor to connect to a server and your system administrator provides the following connection URL for the server: syszos1.abc.com:5021/ABCLOC1

You can create a file named script.sql that contains the following statement:

CONNECT TO syszos1.abc.com:5021/ABCLOC1

Also suppose that the alias db2 has been set for the Db2 command line processor name, and your user ID is myid01. If your password is mypw01, you can use the following command to connect to the server:

db2 -f script.sql -u myid01/mypw01

If your credential is the RACF PassTicket phrase TMOK73SJ, you can use the following command to connect to the server:

db2 -f script.sql -u myid01/TMOK73SJ

Suppress SQL warning messages for OPEN and FETCH from SPUFI, DSNTEP2, DSNTEP4, and DSNTIAUL (PI79053 - June 2018)

This APAR introduces the QUIET option for the TOLWARN parameter of SPUFI and the DSNTEP2, DSNTEP4, and DSNTIAUL programs. QUIET is the same as YES, except that the program suppresses all SQL warning messages from OPEN or FETCH statements if the SQLCODE is 0 or greater.

Automatic remigration binds are removed in release coexistence and fallback (PI87675 - January 2018)

This APAR removes the possibility of disruptive repeating rebinds during release coexistence and fallback and remigration scenarios. The ABIND subsystem parameter is changed so that COEXIST behavior is used if YES is specified.

During release coexistence, or during fallback and re-migration, running a plan or package for the first time on the lower Db2 release results in an automatic bind. However, no automatic bind is triggered when a plan or package first runs again on the higher Db2 release.

When you are certain the Db2 is stable on the higher release and coexistence is no longer and issue, you can explicitly rebind the packages on the higher release.

DSNTIJSG installation job no longer creates SYSIBM EXPLAIN tables (PI86450 - December 2017)

This APAR changes the DSNTIJSG installation job to remove CREATE statements for EXPLAIN tables with the SYSIBM qualifier. Db2 no longer uses these tables.

MQListener automatic restart after Db2 restart (PI84698 - December 2017)

This APAR 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.

LOAD RESUME SUPPORT FOR INLINE COPY (PI81723 - December 2017)

To avoid unavailability of the table space or table space partition, the LOAD syntax has been enhanced 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.

Hybrid Transactional Analytical Processing support for accelerated queries (PI83286 and PI83288 - November 2017)

APARs PI83286 and PI83288, which are packaged by a single PTF, UI51280, introduce Db2 for z/OS support for Hybrid Transactional Analytical Processing (HTAP) of accelerated dynamic queries.

With this new functionality, accelerated analytical dynamic queries have access to the latest transactional data that has been committed in Db2. Db2 analytical dynamic queries that are run on the analytics accelerator can now return results based on real-time transactional data with no data replication latency, so both your transactional and analytical queries run on a single system where it seems that real-time analytical processing on real-time data occurs even for queries that run on an accelerator. Data replication latency now no longer impacts Db2 dynamic query result consistency when using an analytics accelerator.

This HTAP functionality involves Db2 11 for z/OS as the database for the real-time transactional data, IBM Db2 Analytics Accelerator for z/OS Version 5.1 as the data repository for the analytical data, and IBM InfoSphere® Data Replication Change Data Capture for z/OS V10.2.1 to replicate the real-time transactional data from the Db2 database to the analytics accelerator. Additional PTFs are required for IBM Db2 Analytics Accelerator for z/OS and for IBM InfoSphere Data Replication Change Data Capture for z/OS.

z/OS DFSMS data set encryption (PI81900 - November 2017)

This APAR introduces enhancements that provide a simple, transparent, and consumable approach to enabling pervasive encryption of data at rest.

To implement the new encryption features, your security or storage administrator enables z/OS DFSMS data set encryption on your Db2 data sets. z/OS DFSMS data set encryption is a new hardware and software solution that is introduced in z/OS V2R3, and is also available through z/OS V2R2 APARs.

z/OS DFSMS data set encryption uses a key label to encrypt and decrypt the data. The key label is a string from 1 to 64 bytes that identifies a protected data key in the ICSF key repository.

You can use z/OS DFSMS data set encryption to protect all of your Db2 system-managed and user-managed objects, such as Db2-managed table space and index space data sets, data sets that are used by Db2 utilities, and sequential input and output data sets.

Support for collection of more accurate real storage usage statistics (PI82191 - August 2017)

Begin program-specific programming interface information.

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.
End program-specific programming interface information.

Explicitly-defined hidden ROWID columns (PI77719 - June 2017)

This APAR 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.

New LOAD utility IGNORE options (PI79716 - June 2017)

This APAR 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.

ADMIN_INFO_SQL stored procedure enhanced to collect UDF information (PI45103 - April 2017)

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

LOAD utility support for more date and time formats (PI68139 - November 2016)

With this APAR 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 1 10 bytes
DATE_B mm-dd-yy 1, 2 8 bytes
DATE_C yyyy-mm-dd 1 10 bytes
DATE_D yy-mm-dd 1, 2 8 bytes
DATE_E dd-mm-yyyy 1 10 bytes
DATE_F dd-mm-yy 1, 2 8 bytes
DATE_G yyyy-ddd 1 8 bytes
DATE_H yy-ddd 1, 2 6 bytes
DATE_I mmddyyyy 8 bytes
DATE_J mmddyy 2 6 bytes
DATE_K yyyymmdd 8 bytes
DATE_L yymmdd 2 6 bytes
DATE_M ddmmyyyy 8 bytes
DATE_N ddmmyy 2 6 bytes
DATE_O yyyyddd 7 bytes
DATE_P yyddd 2 5 bytes

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.

Notes:
  1. 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.
  2. 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.
(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 1 8 bytes
TIME_B hh.mm 1 5 bytes
TIME_C hh.mm AM or hh.mm PM 1 8 bytes
TIME_D hhmmss 6 bytes
TIME_E hhmm 4 bytes

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.

Note:
  1. If the format includes separators, the separator can be any single-byte character that can be converted to a single-byte EBCDIC character.

Reduced cost for collecting Db2 frequency statistics (PI74408 - August 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.

LOAD REPLACE SHRLEVEL REFERENCE (PI67793 - November 2016)

This APAR introduces support for LOAD REPLACE SHRLEVEL REFERENCE, which enabled concurrent read application access to the target table while new data is being loaded into a set of shadow data sets. A new SWITCH phase is introduced to the LOAD utility to switch access between the original and shadow data sets.