Subsystem parameter release incompatibilities in Db2 12

When you migrate to or apply maintenance in Db2 12, be aware of and plan for subsystem parameter release incompatibilities that might affect your Db2 environment.

Start of changeThe following incompatible changes apply at any Db2 12 function level, including when you first migrate to Db2 12. For incompatible changes that might impact your Db2 12 environment when you activate function levels 501 and higher, see Incompatible changes summary for function levels 501 and higher.End of change

Removal of the COMPRESS_SPT01 subsystem parameter

Removal of the COMPRESS_SPT01 parameter will cause Db2 12 to operate as if that parameter were set to YES, a switch from the current default behavior of NO. In data sharing, all members of the group need to use the same setting for COMPRESS_SPT01. If your SPT01 table space is not compressed, Db2 will always compress it the next time you reorganize it.

Actions to take

Use an IFCID 106 trace record formatter such as the SYSPROC.ADMIN_INFO_SYSPARM stored procedure to check the current setting of COMPRESS_SPT01. If the setting is YES then the change to remove COMPRESS_SPT01 is compatible with your environment and no further action is required. Otherwise, proceed as follows:

  • If you currently run Db2 12: before applying the fixing PTF, modify your subsystem parameter module to specify COMPRESS_SPT01=YES. In data sharing, ensure that all members of the group use COMPRESS_SPT01=YES before applying the fixing PTF to any member of the group.
  • If you currently run Db2 11: before migrating to Db2 12, modify your subsystem parameter module to specify COMPRESS_SPT01=YES. In data sharing, ensure that all members of the group use COMPRESS_SPT01=YES before applying the fixing PTF to any member of the group.

Removal of the SPT01_INLINE_LENGTH subsystem parameter

Removal of SPT01_INLINE_LENGTH causes Db2 12 to operate as if that parameter were set to 32138, the maximum and current default setting. In data sharing, all members of the group need to use the same setting for SPT01_INLINE_LENGTH.

If your SPT01 table space has an inline LOB length of less than 32138, Db2 will always place it in AREO* the next time the -SET SYSPARM command is issued.

Actions to take

Use an IFCID 106 trace record formatter such as the SYSPROC.ADMIN_INFO_SYSPARM stored procedure to check the current setting of SPT01_INLINE_LENGTH. If the setting is 32138, the change to remove SPT01_INLINE_LENGTH is compatible with your environment and no further action is required. Otherwise, proceed as follows:

  • If you currently run Db2 12: before applying the fixing PTF, modify your subsystem parameter module to specify SPT01_INLINE_LENGTH=32138. In data sharing environments, ensure that all members of the group use SPT01_INLINE_LENGTH=32138 before applying the fixing PTF to any member of the group.
  • If you currently run Db2 11: before migrating to Db2 12, modify your subsystem parameter module to specify SPT01_INLINE_LENGTH=32138. In data sharing environments, ensure that all members of the group use SPT01_INLINE_LENGTH=32138 before migrating any member of the group.

Removal of the OBJECT_CREATE_FORMAT subsystem parameter

Removal of OBJECT_CREATE_FORMAT subsystem parameter causes Db2 12 to operate as if that parameter were set to EXTENDED, the current default setting. In data sharing, all members of the group need to use the same setting for OBJECT_CREATE_FORMAT. With this setting, Db2 always creates new table spaces and indexes to use the extended 10-byte log record format.

Actions to take

Use an IFCID 106 trace record formatter such as the SYSPROC.ADMIN_INFO_SYSPARM stored procedure to check the current setting of OBJECT_CREATE_FORMAT. If the setting is EXTENDED, the change to remove OBJECT_CREATE_FORMAT is compatible with your environment and no further action is required. Otherwise, proceed as follows:

  • If you currently run Db2 12: before applying the fixing PTF, modify your subsystem parameter module to specify OBJECT_CREATE_FORMAT=EXTENDED. In data sharing environments, ensure that all members of the group use OBJECT_CREATE_FORMAT=EXTENDED before applying the fixing PTF to any member of the group.
  • If you currently run Db2 11: before migrating to Db2 12, modify your subsystem parameter module to specify OBJECT_CREATE_FORMAT=EXTENDED. In data sharing environments, ensure that all members of the group use OBJECT_CREATE_FORMAT=EXTENDED before migrating any member of the group.

Removal of the UTILITY_OBJECT_CONVERSION subsystem parameter

With APAR PH26317, removal of UTILITY_OBJECT_CONVERSION causes Db2 12 to operate as if that parameter were set to NOBASIC, a switch from the current default setting of NONE. In data sharing, all members of the group need to use the same setting for UTILITY_OBJECT_CONVERSION. With this setting, Db2 utilities that accept the RBALRSN_CONVERSION option always convert existing table spaces and indexes from the traditional 6-byte page (BASIC) format to the 10-byte page (EXTENDED) format.

Important: When the NOBASIC setting of the UTILITY_OBJECT_CONVERSION subsystem parameter is in effect, any LOAD, REORG, and REBUILD INDEX utility job that specifies RBALRSN_CONVERSION BASIC always fails. Similarly, if the target object is still in the 6-byte page format, these utility jobs also fail if they specify RBALRSN_CONVERSION NONE. Plan to remove the RBALRSN_CONVERSION option from these utility jobs before applying the PTF for PH26317.

Actions to take

Use an IFCID 106 trace record formatter such as the SYSPROC.ADMIN_INFO_SYSPARM stored procedure to check the current setting of UTILITY_OBJECT_CONVERSION. If the setting is NOBASIC then the change to remove UTILITY_OBJECT_CONVERSION is compatible with your environment and no further action is required. Otherwise, proceed as follows:

  • If your LOAD, REORG, and REBUILD INDEX utility jobs specify RBALRSN_CONVERSION BASIC or RBALRSN_CONVERSION BASIC: Remove the RBALRSN_CONVERSION keyword from the utility jobs. Otherwise, these jobs are expected to begin failing with message DSNU1145I.
  • If you currently run Db2 12: before applying the fixing PTF, modify your subsystem parameter module to specify UTILITY_OBJECT_CONVERSION=NOBASIC. In data sharing environments, ensure that all members of the group use UTILITY_OBJECT_CONVERSION=NOBASIC before applying the fixing PTF to any member of the group.

    Any existing table space or index having the traditional 6-byte page (BASIC) format is converted to the 10-byte page (EXTENDED) format the next time it is processed by a Db2 utility that accepts the RBALRSN_CONVERSION option. Also, existing table spaces and indexes having the 10-byte page (EXTENDED) format can no longer be converted to the deprecated 6-byte page (BASIC) format.

  • If you currently run Db2 11: before migrating to Db2 12, modify your subsystem parameter module to specify UTILITY_OBJECT_CONVERSION=NOBASIC. In data sharing environments, ensure that all members of the group use UTILITY_OBJECT_CONVERSION=NOBASIC before migrating any member of the group.

    Any existing table space or index in the deprecated 6-byte page (BASIC) format is converted to the 10-byte page (EXTENDED) format the next time it is processed by a Db2 utility that accepts the RBALRSN_CONVERSION option. Also, existing table spaces and indexes having the 10-byte page (EXTENDED) format can no longer be converted to the deprecated 6-byte page (BASIC) format.

Important: After migration to Db2 12, the first time that REORG runs on an XML table that supports multiple versions, Db2 alters the data type of the START_TS and END_TS columns in the XML table from BINARY(8) to BINARY(10). That column alteration causes invalidation of dependent packages. As a result of package invalidation, Db2 performs an automatic rebind on application packages that access the XML table. If the first run of the REORG utility on an XML table in Db2 12 occurs at the same time that applications with invalidated packages are running, resource contention can occur, and the REORG or the applications might time out.

Changes to REORG TABLESPACE PART SORTNPSI YES behavior

Starting in Db2 12 at function level 100, when you specify the REORG TABLESPACE utility option SORTNPSI YES or set the REORG_PART_SORT_NPSI subsystem parameter to YES, REORG sorts the keys for all non-partitioned secondary indexes (NPSIs). Previously, when you specified these options, REORG sorted NPSIs only when certain internal thresholds were met. Therefore, some REORG SORTNPSI YES jobs that previously did not qualify for a sort operation will include a sort operation in Db2 12. Sorts can impact performance and might increase the elapsed time of those utility jobs.

Actions to take

If the extra sort operations cause a performance impact, set the subsystem parameter REORG_PART_SORT_NPSI to AUTO, or specify SORTNPSI AUTO in the REORG TABLESPACE utility statement.

Start of change

Maximum number of user-defined external scalar functions running in a Db2 thread is no longer unlimited (APAR PH44833)

Starting at application compatibility level V12R1M100 (if the PTF for APAR PH44833 is applied), Db2 12 introduces the MAX_UDF subsystem parameter. MAX_UDF controls the maximum number of user-defined external scalar functions that can run concurrently in a Db2 thread. The maximum value of MAX_UDF is 99999. Before the introduction of MAX_UDF, the maximum number of user-defined external scalar functions that could run concurrently in a Db2 thread was unlimited.

Actions to take

If an application contains SQL statements that invoke user-defined external scalar functions, and one of those SQL statements is rejected with SQLCODE -904 and reason code 00E70082, increase the MAX_UDF subsystem parameter value, or change the application to run fewer functions concurrently in a Db2 thread.

End of change