Highlights of Db2 12.1

AI Query Optimizer

The Db2® AI Query Optimizer is an evolution of the traditional Db2 cost-based optimizer. The traditional optimizer is infused with AI to enhance the key functions of the optimizer that are suited to using AI techniques. The initial functionality in Db2 12.1 focuses on one of the most consequential functions in a cost-based optimizer, cardinality estimation, or the number of rows flowing in and out of low-level operators in the query execution plan.

When the AI Query Optimizer feature is enabled and the table is not excluded by an automatic runstats or model policy, an AI model is automatically discovered and trained. This model will be used by the optimizer for supported predicates to estimate the number of rows that satisfy the set of eligible predicates covered by the AI model. It is enabled by default for new databases and can be configured using the auto_ai_optimizer parameter. For more information, see AI Query Optimizer.

New Db2 Starter Edition

Db2 Starter Edition is designed for users needing core data management capabilities for new applications and services. Advanced functionality can be obtained seamlessly by upgrading the Db2 license. Db2 Starter Edition is subject to capacity restrictions of up to 4 cores and 16 GB of memory per physical or virtual server. For more information, see Db2 Starter Edition.

Bulk insert for Snowflake data sources in Db2 Federation ODBC wrappers

You can now enable bulk insert operations for a Snowflake data source in an Db2 Federation ODBC wrapper. This setting can improve performance when running insert operations

Improvements to recovery and replication through drop member topology change events in Db2 pureScale environments

With the release of Db2 12.1, in Db2 pureScale® environments, offline backups are no longer required when an offline drop member topology change event happens that involves a cataloged database. After restarting the Db2 instance, any first connect, including utility operations, can be driven on the database, which updates the database topology to reflect the new member topology.

In addition, database or table space backup images can now be restored when the member topology differs between the backup image and the target database in the instance. This applies to both snapshot and non-snapshot recovery scenarios. An ensuing database or table space rollforward is also allowed through a drop member topology change event.

Lastly, replication using the db2ReadLog API can now read log record data from before a drop member topology change event.

For more information, see Topology changes (add or drop members).

LOB insert for optimized data sources in Db2 Federation ODBC wrappers

The number of processed units of work (UOW) allowed before an offline REORG now increased.

With the release of Db2 12.1, the number of Units of work (UOWs) that can be processed before an offline REORG TABLE operation is required is increased from 3 to 31. This increase applies to UOWs that contain reorg-recommended operations.

Intra-table-space Parallelism (ITP) for backup operations now available

Intra-table-space Parallelism (ITP) enables Db2 to use multiple threads when backing up each table space. In cases where one or more table spaces are much larger than the others, the overall elapsed time to run the backup operation is substantially reduced.

History File Enhancements

Automatic history file pruning for automatic Db2 backup operations

When a database is configured to run automatic Db2 backup operations, the value of the auto_del_rec_obj database configuration parameter affects how the automatic backup path is managed. With the release of Db2 12.1, setting the auto_del_rec_obj database configuration parameter to OFF retains only the latest backup image in the automatic backup directory. When the parameter is set to ON, the pruning of the automatic backup path adheres to the rules of the standard database pruning.

New HADR upgrade procedure that allows read only access to HADR standby database during the upgrade procedure

Running upgrade procedures mentioned in Upgrade Db2 High Availability Disaster Recovery (HADR) environments can result in the database not being available to applications during the upgrade procedure. With Db2 12.1, a new HADR upgrade procedure provides read only access to the HADR standby database while upgrading the primary database. For more information, see Upgrading a Db2 server in an HADR environment with a down-level standby for Reads on Standby.

New registry variable for controlling how an HADR standby reacts when running into a disk full condition in the active log path

The DB2_HADR_DISKFULL_NONBLOCKING registry variable is now renamed to DB2_HADR_BLOCK_ON_DISKFULL. This registry variable controls the behavior of an HADR standby that is running into a disk full condition in the active log path.

By default, when an HADR standby runs into a disk full condition in the active log path, the standby stays up, with the STANDBY_LOG_DEVICE_FULL flag set in the HADR_FLAGS field, as reported by the db2pd -hadr / HADR monitor table function. This can cause transactions on primary to be blocked.

When you set the new DB2_HADR_BLOCK_ON_DISKFULL registry variable to OFF, the standby database is brought down in a disk-full situation. Taking the standby down takes HADR out of PEER state and unblocks the transactions on the primary.

Namespace isolation by using the Db2 tenant object

With the release of Db2 12.1, you can now add a level of data isolation for users within the same Db2 database. By using the new Db2 tenant object, you can create unique and independent namespaces for user-defined objects within a database. Multiple schemas and database tables can be defined within a tenant without concern about possible name conflicts with similar objects in other database tenants. For more information, see Tenants.

Amazon S3-compatible storage for automatic backup operations

Automatic Db2 backup operations now support remote storage targets.

New registry variable for reserving memory for caching log records produced for the db2ReadLog() API

The DB2_DPS_RLOG_SHR_MEMBYTES registry variable takes a size in bytes to reserve memory for caching log records produced for the db2ReadLog() API. By using a larger size, Db2 is able to read more log records into memory from each read of a log file. Operations that read the log files can see improved performance when using a larger value for DB2_DPS_RLOG_SHR_MEMBYTES.

Q Capture enhancements to inline LOB replication

The InfoSphere Data Replication Q Capture program reads the Db2 recovery log looking for changes to Db2 source tables and translates committed source data into WebSphere® MQ messages. These messages can be published in XML format to a subscribing application, or replicated in a compact format to the Q Apply program. When LOB_SEND_OPTION=I, Q Capture automatically uses separate MQ messages when the LOB value is too large to be sent inline. This new behavior for LOB_SEND_OPTION=I makes using LOB_SEND_OPTION=S obsolete.