Mod Pack and Fix Pack Updates

version 11.1 Mod Pack and Fix Pack updates contain important technical changes and new functionality that might affect your product use.

For more information on what Mod Packs are, see Modification Packs.

As Fix Packs are released they will be available for Db2® products at the Download Db2 Fix Packs by version page.

Mod Pack 4 and Fix Pack 6

Refer to System requirements update for IBM Db2 11.1 Releases for changes on platform support, software, and any other system requirements for this release.

Mod Pack 4 and Fix Pack 6 contains the following enhancements:

Performance enhancements:
  • Faster completion of ACTIVATE DATABASE operations is possible through deferred memory committing. When database activation occurs and deferred memory commit is enabled (see DB2_DEFER_MEMORY_COMMIT), the initialization and committing of buffer pool and lock list memory is performed asynchronously in the background. This asynchronous initialization allows database activation to complete and the database to be available for application connections sooner.
Update path for older fix pack levels running on pureScale instances:
Discontinued functionality:
  • The db2updserv utility is discontinued. It is not available to users of Db2 v11.1.4.6 and later versions. Going forward, users will be notified of upcoming releases.

Mod Pack 4 and Fix Pack 5

Refer to System requirements update for IBM Db2 11.1 Releases for changes on platform support, software, and any other system requirements for this release.

Mod Pack 4 and Fix Pack 5 contains the following enhancements:

Database Administration:
Serviceability enhancements:
  • If a Db2 check password daemon process (db2ckpwd) encounters a problem and crashes, or the process is closed abnormally, the watchdog will now automatically re-spawn the daemon. Db2 will no longer require an instance recycle to re-spawn check password daemons.

    To prevent the re-spawning from occurring automatically, you can add the NORESPAWN parameter to the DB2_NUM_CKPW_DAEMONS registry variable . See DB2_NUM_CKPW_DAEMONS in the topic Miscellaneous variables for more information.

  • The SSL_SVR_LABEL database manager configuration parameter can now be updated dynamically. There is no longer a need to restart the instance to change this parameter. Db2 is now able to change the SSL server certificate used for incoming connections while the instance is running. To enable the dynamic configuration of the SSL_SVR_LABEL configuration parameter, the DB2_DYNAMIC_SSL_LABEL registry variable must be set to "ON". See SSL_SVR_LABEL for more information.
Performance enhancements:

  • A new system environment variable is available for the DB2_ALTERNATE_GROUP_LOOKUP system environment variable that can help to improve performance of unhealthy systems. See System environment variables for more information.

Encryption enhancements:
  • When configuring an IBM Data Server Driver file, the SSLServerCertificate keyword will now be accepted, even when binary encoded certificates, such as .crt files, are specified.
Db2 pureScale®:
  • Now available for Red Hat Enterprise Linux (RHEL) Version 7.6 and SUSE Linux Enterprise Server (SLES) Version 12 SP4 P9 platforms
  • Native support now available for AIX 7.2 TL2 and AIX 7.2 TL3 UNIX platforms

Mod Pack 4 and Fix Pack 4

Mod Pack 4 and Fix Pack 4 contain the following enhancements:

Performance:
  • Improved performance, especially on new hardware (Intel CPUs supporting AVX-512).
High Availability:
  • In HADR Reads on Standby (ROS) environments, the availability of the standby database to perform SQL queries is significantly enhanced through the avoidance of the replay-only window. See DB2_HADR_ROS_AVOID_REPLAY_ONLY_WINDOW for details.
  • For databases configured with a mirrored log path (ie. MIRRORLOGPATH), a performance improvement can be achieved by writing log data to both files asynchronously in parallel. See DB2_USE_ASYNC_FOR_MIRRORLOG for details.
  • Monitoring the progress and performance of backup and restore operations can be achieved using the new db2pd -barstats option. See Monitoring backup and restore performance with db2pd -barstats for details.
  • The performance of online backup operations can be improved through a reduction in changed-page flushing behaviour. See DB2_REDUCE_FLUSHING_DURING_BACKUP for details.
  • An enhancement to the ADMIN_MOVE_TABLE procedure allows for more granular control over the creation of non-unique and modification-state indexes on the target table. See the WITHOUT_NONUNIQUE_INDEXES option, and the ADD_MODSTATE_INDEX, REMOVE_MODSTATE_INDEX and SYNC_MODSTATE_INDEX options of the ADMIN_MOVE_TABLE procedure.
  • A significant performance improvement for rollback processing of large units of work, through the autonomous use of buffered I/O when reading transaction log file data. See DB2_USE_BUFFERED_READ_FOR_ACTIVE_LOG for details.
  • The Disk Tiebreaker and Majority Node Set quorum mechanisms can now be configured through the db2haicu environment. For more information, see Quorum devices.
  • A new ADMIN_MOVE_TABLE option, ESTIMATE, allows users to estimate the size of a table, prior to moving the table.
  • An enhancement to the ADMIN_MOVE_TABLE procedure allows for more granular control over the creation of non-unique and modification-state indexes on the target table. For more information, see the descriptions for the WITHOUT_NONUNIQUE_INDEXES option and the ADD_MODSTATE_INDEX, REMOVE_MODSTATE_INDEX and SYNC_MODSTATE_INDEX options, in the ADMIN_MOVE_TABLE procedure topic.
  • A new db2fmtlog tool can be used to extract and display information from transaction log files. Information extracted consists of encryption or compression status, log chain information, range of log records, and other metadata. This information is useful in problem determination scenarios. This information can also assist with identifying which log records can trigger 'replay only windows' in HADR Reads-on-Standby environments. For more information, see db2fmtlog – Format and display log file information command
Administrative routines:
  • Support for ADMIN_GET_TAB_DICTIONARY_INFO table function in column-organized tables.
Application Development:
  • A new set of built-in JSON SQL functions are included with both new installations and upgrades to Db2 version 11.1.4.4. These JSON SQL functions better support SQL interaction with JSON data. With these functions, you can store, retrieve, and query JSON and BSON data directly, using SQL. You can also create JSON documents using SQL. These new functions follow the grammar and semantics outlined in the ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission) SQL Technical Report "Part 6: SQL support for JavaScript Object Notation (JSON)" (TR 19075-6:2017). Part 6 of the report outlines a set of SQL language for storing, querying, and publishing JSON data. As built-in functions, the Db2 JSON SQL functions reside in the SYSIBM schema and do not require users to hold any privilege on a function to invoke it. The new built-in JSON SQL functions replace the original set of JSON SQL functions provided in the SYSTOOLS schema. For more information, see JSON scalar functions, JSON_TABLE table function, and JSON_EXISTS predicate.
Federation:
  • Federation enhancement offers better support for SAP HANA in data type mapping and function pushdown.
  • SAP Adaptive Server Enterprise (ASE) 16.0 is now supported by Federation
  • Federation server now supports HDFS parquet files stored on hadoop file system.
  • Federation server now supports access to CouchDB data source through Rest Service interface.
  • Federation enhancement offers better support for Greenplum through ODBC in data type mapping and function pushdown.
  • Federation enhancement offers better support for Microsoft SQL Server/Microsoft Azure through ODBC in data type mapping and function pushdown.
  • Federation server now supports Datetime2 data type mapping in Microsoft SQL Server/Microsoft Azure.
4K Sector Support:
  • Support for storage devices that use 4KB sector sizes is available as a technical preview, but not supported for production environments until further notice. For details, see the DB2_4K_DEVICE_SUPPORT registry variable.
Db2 pureScale:
  • Enhanced multi-tenants high availability support is now provided at the database mount resource level. By allowing an individual mount point at each host to be offline for disk replacement or other maintenance operations, maintenance can be completed without affecting workloads running on databases residing on other mount points.
  • Reducing the size of table spaces through extent movement has been available in non-pureScale® environments since Db2 9.7 for Linux, UNIX, and Windows. Extent movement in IBM® Db2 pureScale now available in IBM® Db2 version 11.1 Mod Pack 4 Fix Pack 4 for Linux, UNIX, and Windows.
    In order to enable the feature, perform the following steps:
    • Install IBM® Db2 version 11.1 Mod Pack 4 Fix Pack 4 for Linux, UNIX, and Windows
    • db2set DB2_ENABLE_PS_EXTENT_RECLAIM=ON
    Once enabled, you can run ALTER TABLESPACE with REDUCE MAX or LOWER HIGH WATER MARK in Db2 pureScale environments. Note these steps do not apply to non-pureScale® environments where extent movement is always available. You can monitor the progress of the extent movement operation by using the MON_GET_EXTENT_MOVEMENT_STATUS table function or db2pd -extentmovement.
  • The db2pd -cfinfo command prints additional information regarding the cluster caching facility's (CF) workload. Cluster caching facility-driven castout for the group buffer pool (GBP) can also be viewed for a specific database using –cfinfo gbp –db <database name>.
  • The MON_GET_CF_CMD table function now includes additional cluster caching facility commands. See MON_GET_CF_CMD table function.
  • Support for Db2 pureScale on SUSE Linux Enterprise Server (SLES) 12 SP2 starting from Db2 V11.1.4.4. If a ConnectX-3 based card is used, then firmware 2.40.7004 or higher must be used on the card.
  • For new and upgraded Db2 pureScale® instances, the default configuration repository type for IBM® Spectrum Scale (Previously known as General Parallel File System or GPFS) is changed to Cluster Configuration Repository (CCR). This repository type provides better high availability and simpler cluster configuration setup with lower administration expenses.
  • Default pagepool value have been increased from 512MB to 2GB.
  • Simplified steps for pureScale deployment on TCP/IP and RoCE networks on all supported platforms by removing the requirement of configuring a local loopback IP interface on high speed interconnect switches. Faster cluster up & running by eliminating manual steps of configuring the RSCT network monitoring configuration file (netmon.cf). See Restrictions.

Mod Pack 3 and Fix Pack 3

Mod Pack 3 and Fix Pack 3 contain the following enhancements:

Database Administration:
Db2 for z/OS®:
  • For improved compatibility with Db2 for z/OS, the fetch-clause can now be specified in a select-statement. For example, the following statement, which failed with SQLSTATE 42601 in previous releases, is now supported:
    SELECT * FROM T ORDER BY 1 OPTIMIZE FOR 2 ROWS FETCH FIRST 2 ROWS ONLY
  • For improved compatibility with Db2 for z/OS, the offset-clause can now be specified without the fetch-clause:
    SELECT * FROM T ORDER BY 1 OFFSET 20 ROWS
  • A new database configuration parameter, dec_arithmetic, provides more control over minimum DECIMAL division scale. It also provides improved compatibility with Db2 for z/OS. The dec_arithmetic database configuration parameter replaces the min_dec_div_3 database configuration parameter. After applying Mod Pack 3 and Fix Pack 3, dec_arithmetic is initialized to its default value of "" (empty string) and min_dec_div_3 continues to function. If dec_arithmetic is set to a non-default value, then min_dec_div_3 is ignored.
Federation:
  • Kerberos authentication is now supported by Federation. With Kerberos authentication, users logged into Kerberos can now connect to a database without providing a user ID or password. For more information see, Enabling kerberos authentication in Federation server for ODBC wrapper.
  • Federation installation enhancement offers better support for MariaDB in data type mapping and function pushdown.
  • Federation server now supports access to MongoDB data source through MongoDB's Rest Service interface.
JDBC:
High Availability:
  • SSL support for the transmission of transaction log data between the HADR primary and standby database servers, previously limited to the Linux® Amd64/Intel operating system, is now supported on all Unix and Windows operating systems, excluding Db2 pureScale environments. Setup instructions are available at Configuring SSL for the communication between primary and standby HADR servers.
  • The archival of log files (LOGARCHMETH1/2) by using VENDOR or Tivoli® Storage Manager (TSM) methods can now be configured with a timeout on Unix environments. If the transmission of log data between Db2 and the Tivoli Storage Manager (TSM) or vendor API is unresponsive for the specified timeout period, then Db2 interrupts the archive log attempt and follows normal log archive failure protocol. For more information, see "--VENDOR_ARCHIVE_TIMEOUT" in Configuration parameters for database logging.
  • In HADR Standby databases with Reads On Standby (ROS) enabled, diagnostic improvements allow for easier identification of operations which cause replay-only windows. For details on the new db2diag.log messaging see DB2_HADR_REPLAY_ONLY_WINDOW_DIAGLEVEL in Miscellaneous variables. For more information on the new transaction flag of db2pd -transactions, see db2pd - Monitor and troubleshoot Db2 database command.
  • In HADR environments, integrity checking of transaction log data during network transmission between the primary and standby database servers is improved. When an integrity check failure is detected, seamless retransmission of the log data is performed to auto-correct the condition, with no user intervention required.
  • db2haicu HA configurations can be exported or backed up via a new '-o' option.
Chrony NTP:
  • Chrony NTP support - the new preferred choice by various Linux distributions, as the implementation of the Network Time Protocol on RHEL 7.2 or higher and SLES 12 or higher. It will be set up automatically by the Db2 installer if the Chrony package is pre-installed.
Db2 pureScale:
  • Add and Drop CF operations can now be performed online, without having to bring down the Db2 pureScale cluster. For more information, see ../../com.ibm.db2.luw.licensing.doc/doc/c0070362.html#c0070362.
  • Even faster pureScale member crash recovery - building on the momentum started in V11.1.2.2 where the re-design of global lock manager recovery helped boost the overall recovery time, an enhanced global buffer pool recovery algorithm further reduced the recovery time, especially with a large cluster.
  • Improved moderate to heavy write workload performance in TCPIP pureScale systems - in single and multi-site Db2 pureScale environments on TCP/IP networks, moderate to heavy write workload performance is improved with multiple cross-invalidation connections between a CF and a member.
  • CREATE INDEX operations in a Db2 pureScale environment can now allow concurrent write access to the table. Enable this option by setting DB2_INDEX_CREATE_ALLOW_WRITE register variable to YES. See this section for more details: DB2_INDEX_CREATE_ALLOW_WRITE. This feature can only be enabled on a recoverable database (where logarchmeth1 or logarchmeth2 database configuration parameters is not set to OFF).
  • Db2 pureScale host maintenance enhancement offers support for multiple hosts to enter and exit maintenance mode while maintaining the quorum. This enhancement allows hosts to be down for an extended period of time while performing other maintenance tasks. Specify the instance name by setting the DB2INSTANCE environment variable before executing the db2cluster -cm -enter/-exit -maintenance commands.
  • Options -host and -netname can now be used along with db2cluster -verify -req -rdma_ping that allows users to test specific connections between hosts.
  • Add support of “Click & Go” performance data collection in Db2 pureScale with new "-perf" option to db2cluster -verify.

Mod Pack 2 and Fix Pack 2

Mod Pack 2 and Fix Pack 2 contain the following enhancements:
  • An enhancement to allow parallel inserts into column-organized tables is enabled in V11.1.2.2.
  • Faster pureScale member crash recovery. Improve hashing algorithm and increase parallelism in global lock manager recovery design to reduce the recovery window, benefiting pureScale deployment of various sizes, especially with large clusters configured with huge lock list, global lock manager, and global buffer pool.
  • Databases can now be configured to allow connectivity during crash recovery. See Database accessibility during backward phase of crash recovery or HADR takeover.
  • Developers can now store and retrieve JSON data directly by using SQL. The current Db2 11.1 release includes a client-based JSON solution that embraces the flexibility of the JSON data representation within a relational database. The JSON data is stored in a binary-encoded format called BSON (Binary JSON). SQL-based routines are now available to developers. Interactions with JSON data within Db2 happen through various user-defined functions (UDFs).
  • pureScale environments now support Database Rebuild functionality.
  • An HADR Standby tablespace can now be recovered without a full database re-initialization, and a new STANDBY_TABLESPACE_ERROR flag for HADR_FLAGS monitor element has been added. See How to recover from tablespace errors on an HADR standby database.
  • Databases can now be configured to avoid lock-escalation. See DB2_AVOID_LOCK_ESCALATION.
  • Crash recovery rollback and Rollforward performance improvements through optimized memory management and DGTT handling.
  • Transaction rollback performance improvements.
  • A new db2cluster option allows you to specifically check RDMA communication among all pureScale members and CFs adapters. Developers can now verify RDMA connectivity between hosts without having to run the entire cluster verification workflow, allowing for quicker diagnosis of issues and verification for RDMA communication.
  • The db2sampl command now supports the creation of an encrypted sample database using the -encrypt parameter.
  • A new db2trc option allows developers to control the logging level of the information dumped to CLI trace for CLI/ODBC driver and CLI applications
  • A new db2trc option allows developers to log information into the CLI trace based on search patterns.
  • CLPPlus supports setting the value of the Session Global Variable from the db2dsdriver.cfg file.
  • A new check box element in GUI labeled "Enable SSL security" is now added in the security tab of the Microsoft ODBC Administrator window. This element allows users to set the security configuration parameter value to SSL in Db2 Instance-based client.
  • A new functionality of federation server allows developers to configure Secure Socket Layer (SSL) support to connect to Db2 family data sources.
  • Federation installation enhancement offers better support for PostgreSQL and MySQL in data type mapping and function pushdown.
  • Add support for Db2 pureScale on POWER and Intel Linux on RHEL 7.3 starting from Db2 V11.1.2.2 iFix001. If a ConnectX-3 based card be used, then firmware 2.42.5000 or higher must be used on the card.

Mod Pack 1 and Fix Pack 1

Mod Pack 1 and Fix Pack 1 contain the following enhancements:
  • Improved synopsis table management. The synopsis enhancements (enabled by default) improve performance when a few rows are inserted or updated into column-organized tables, in a single transaction. In comparison to previous releases, as significant amounts of columnar-organized table data are inserted by using small transactions, query performance is improved while storage requirements are reduced. Maximum improvements are achieved with single-row insert or update transactions. However, any transaction that inserts or updates fewer than 100 rows usually benefit from synopsis enhancements. Larger insert or update transactions are not affected by synopsis enhancements.
  • Improved BLU performance, especially on new hardware (Power8, z13® and Intel CPUs supporting AVX2).
  • The db2_install command now must have the user accept the license agreement by specifying the -y parameter to proceed with the installation. If the user is unaware of this new parameter, the user is prompted through command line to either accept or decline the license agreement. A description of the -y parameter can be found in the db2_install command, or by typing db2_install -h or db2_install -? in the command line processor (CLP).
  • Support is added for PKCS #11 keystores, such as hardware security modules (HSM) to store native encryption master keys. A single PKCS #11 keystore can manage encryption keys for many databases.
  • The db2convert command now converts tables in a partitioned database environment, and tables that have generated columns, into column-organized tables.
  • New DISTINCT predicate. The addition of the DISTINCT predicate allows users to compare two expressions. The predicate evaluates to TRUE if the values of the two expressions are not identical. The result of a DISTINCT predicate depends on whether either or both of its input expressions are NULL. For more information, see ../../com.ibm.db2.luw.sql.ref.doc/doc/r_predicate_distinct.html#r_predicate_distinct
  • For Db2 pureScale users who want to upgrade from supported Db2 version 10.5 fix packs, or later, refer to the FAQ technote for supported 10.5 fix pack levels. High availability disaster recovery (HADR) environments can now be upgraded without the need to reinitialize the standby database after you upgrade the primary database.
  • The Db2 workload manager (WLM) dispatcher is a built-in Db2 technology that developers can use to allocate CPU resources for work that is running on a database server. Starting with Db2 version 11.1 Mod Pack 1 and Fix Pack 1, CPU resource entitlements and CPU limits can be defined for the entire database by using new configuration parameters: wlm_cpu_shares, wlm_cpu_share_mode, and wlm_cpu_limit. In previous versions of Db2, CPU resource entitlements and CPU limits were defined only on WLM service class objects.
  • New WLM thresholds, ACTIVITYTOTALRUNTIME and ACTIVITYTOTALRUNTIMEINALLSC, can be used to control the maximum amount of time that an activity is allowed to run. The time that is governed by this threshold does not include any time that is spent while queued. In previous versions of Db2, developers had to use the ACTIVITYTOTALTIME WLM threshold, which measures both execution time and queue time, to control long running activities.
  • Improved locking serialization for Automatic Dictionary Creation and earlier availability of the new dictionary for Ingest/Insert/Import results in improvements with large insert jobs such as insert with subselect. Those improvements are higher PCTENCODED (percent encoded) values in SYSCAT.COLUMNS, better query performance, and increased throughput for high concurrency workloads.
  • If applying a Fix Pack to the Db2 version 11.1 GA level (Db2 11.1.0.0), see

    Applying a Fix Pack to Db2 version 11.1 GA (Db2 11.1.0.0).

  • Heavy workload on the source table might prevent ADMIN_MOVE_TABLE from completing the SWAP phase (SQL0911N reason code 2). In other words, if the source table is heavily used by other applications during the SWAP phase, ADMIN_MOVE_TABLE might rollback due to a deadlock (SQL0911N reason code 2). Use the FORCE_ALL option to force all applications having workload on the source table. Forced applications will receive SQL1224N on all subsequent database manager requests. Most of the time this option is not needed. Use FORCE_ALL with care. Developers must have SYSADM, SYSCTRL or SYSMAINT authority to perform SWAP with FORCE_ALL option.
  • SSL is now supported between the HADR primary and standby servers on Linux. However, this SSL support is only present in the Linux Amd64/Intel environment, not in the Linux PPCLE or Linux Z environment. Setup instructions are available at Configuring SSL for the communication between primary and standby HADR servers.
  • The BOOLEAN data type can now be used for table columns and for expression results. This enhances compatibility with other relational database management systems.
  • ALTER COLUMN <column name> SET DATA TYPE (increase VARCHAR/VARGRAPHIC column length only) is now a supported option to altering a column-organized table definition.
  • The m4 package is now added on both RHEL and SLES and the perl-Sys-Sylog package is added on RHEL7.1 and up.
  • HADR Upgrade to Db2 11.1.1.1 from Db2 10.5 without having to reinitialize the standby with a new backup. Further information refer to High availability, backup, logging, resiliency, and recovery enhancements.
  • Aggregation enhancements for improved memory usage and performance:
    • Improved decisions to pre-partition the aggregation input stream:
      • Pre-partitioning on the GROUP BY columns of the aggregation input stream allows each agent to process a subset of groups and uses significantly less memory.
    • Better memory and performance for single/unique group results:
      • If a group contains a single row, then the column value itself is the aggregation result. The result is sent directly out and the hash table uses less memory. This saving is significant when the GROUP BY column or set of columns is nearly unique.
    • MPP GROUP BY improvements:
      • Better decisions to avoid a "Partial-Final" GROUP BY when the partial group by does not reduce the stream by much.
    • New and improved Hashing algorithm:
      • A New 64 bit hash function is used to reduce the number of collisions in the hash table and better utilize the memory required for aggregation.
  • Support for Windows Server 2016.
  • Increase of limit on LOGFILSIZ database configuration parameter from 1000[4 - 1 048 572] to 1000[4 - 16 777 152]
  • Federation installation enhancement offers an easy way to access the data sources through ODBC driver. Mod Pack 1 and Fix Pack 1 release is integrated with ODBC driver installation and configuration. The ODBC driver integration enhancement eliminates the need to download , install and configure the ODBC driver separately for accessing the data sources. Using the ODBC driver, you can now directly access data sources, such as, Hive, Impala, Spark, Netezza®, and SQL Server.
  • Six new features for Db2 Connect in Version 11.1.1.1 that support z/OS v12 (supported for T4 driver type):
    1. Session Token Support in client reroute.
    2. XA WLB support for global transactions.
    3. Preserve statement cache at rollback.
    4. ClientApplcompat support for continuous delivery.
    5. API to reutrn function level of server.
    6. Fast Load for Db2 for z/OS.
  • Extended row size support extended to include system temporary table spaces of any page size. Previously, extended row size did not apply to system temporary table spaces with a page size less than 32K.