Incompatible changes summary for function levels 501 and higher

Function level 510 incompatible changes

Function level 510 introduces no incompatible changes.

Function level 509 incompatible changes

Function level 509 introduces no incompatible changes.

Function level 508 incompatible changes

Function level 508 introduces no incompatible changes.

Function level 507 incompatible changes

Function level 507 introduces no incompatible changes.

Function level 506 incompatible changes

Function level 506 introduces the following incompatible changes:

Changes in explicitly created table space behavior

When the application compatibility level is set to V12R1M506 or higher, the following changes become effective:

• Dropping a table that resides in an explicitly created universal table space no longer returns an error. Instead, the table space is implicitly dropped.
• Dropping an auxiliary table that resides in an explicitly created LOB table space no longer leaves the LOB table space in the database. Instead, the table space is implicitly dropped.

Actions required:

• Any existing applications that use the DROP TABLESPACE statement to drop a universal table space with a table can now be changed to use the DROP TABLE statement instead.
• If an auxiliary table is dropped, any applications or tools that expect the LOB table space to remain for reuse must be modified accordingly.

Function level 505 incompatible changes

Function level 505 introduces the following incompatible changes:

RUNSTATS does not update real-time statistics by default

Starting in function level 505, the RUNSTATS utility no longer updates real-time statistics (RTS) by default.

Action required: If you want RUNSTATS to update RTS, take one of the following actions:

• In the RUNSTATS statement, specify TABLESAMPLE SYSTEM NONE and do not specify SAMPLE.
• Set the STATPGSAMP subsystem parameter to NO and do not specify SAMPLE in the RUNSTATS statement.

Newly supported user-defined functions

If your environment contains existing user-defined functions with the same names and signatures as the new built-in functions, applications with unqualified references to the existing user-defined functions might start invoking the built-in function instead of the user-defined function in certain situations.

Action required: For information about avoiding such situations, see Ensuring that Db2 executes the intended user-defined function.

Function level 504 incompatible changes

The following changes might impact your Db2 environment and applications that run at application compatibility level V12R1M504 or higher, such as the following changes:

• If your Db2 applications have unqualified references to existing user-defined functions, they might start invoking new built-in functions instead if the names and signatures match, in certain situations. For information about avoiding such situations, see Ensuring that Db2 executes the intended user-defined function.
• If your Db2 environment contains existing columns or variables with names such as CURRENT_TIMEZONE or CURRENT_SERVER, you must modify your applications to delimit these names. For example, you can issue the following queries to identify columns and variables with these names. For columns, issue:

  SELECT * FROM SYSIBM.SYSCOLUMNS
    WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');

  For variables, issue:

  SELECT * FROM SYSIBM.SYSVARIABLES
  WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');

  Based on the result, you can then check for dependencies, for example by querying the SYSIBM.SYSPACKDEP catalog table, to identify any static applications that depend on the nondelimited forms of these names.

• CREATE TABLESPACE statements that previously created non-UTS segmented or partitioned table spaces, now always create partition-by-growth or partition-by-range table spaces.
  Tip: Although it is best to use the non-deprecated types for new objects, you can still use a lower application compatibility level to create the deprecated table spaces or higher if necessary, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).
• Errors can be returned for SQL statements that previously succeeded, as side effects of the tables now being created in universal table spaces instead of non-UTS types. For example:
  • CREATE AUX TABLE statements for LOB columns can start failing with SQLCODE -769 because the PART clause is required when the base table resides in a PBG table space.
  • DROP TABLE statements can start failing with SQLCODE -669 because the DROP TABLE statement is not supported for tables in PBG table spaces. The DROP TABLESPACE statement must be used instead. (Function level 506 resolves this situation. See Support for implicitly dropping explicitly created UTS and LOB table spacesSupport for implicitly dropping explicitly created UTS and LOB table spaces.)
  • CREATE TABLE statements can start failing with SQLCODE -646 if they attempt to create more than one table in a table space. Each universal table space contains only single table.
• The following SQL statements now return errors:
  • CREATE SYNONYM statements
  • CREATE TABLE statements that specify existing non-UTS table spaces
  • CREATE TABLE and ALTER TABLE statements that specify ORGANIZE BY HASH

  Action to take: Update your applications to use the non-deprecated object types.

Function level 503 incompatible changes

Function level 503 introduces the following incompatible changes.

Result change for system-period temporal tables defined with ON DELETE ADD EXTRA ROW

A query has incompatible behavior at application compatibility level V12R1M503 if all of the following conditions are met:

• It references a system-period temporal table (STT) with FOR SYSTEM_TIME FROM value1 TO value2 (or BETWEEN value1 AND value2) period specification. The STT can be directly referenced or indirectly referenced through views.
• The STT is defined with the ON DELETE ADD EXTRA ROW clause.
• The STT is defined with the DATA CHANGE OPERATION column.
• During migration to use the temporal auditing feature, the altered table contains existing rows.
• The DATA CHANGE OPERATION column is populated with the default NULL value for the existing rows. When the query is executed, the historical table contains records with a NULL value in DATA CHANGE OPERATION column.

Action required: Start a trace for IFCID 0376. In the trace output, function code 1215031 or 1202 identifies the affected queries.

The SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure is not supported when APPLCOMPAT is V12R1M503 or higher

Users will not be able to invoke the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure when running with APPLCOMPAT=V12R1M503 or higher. Existing applications that invoke the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure will fail starting with APPLCOMPAT=V12R1M503 or higher.

Action required: Start a trace for IFCID 0376. In the trace output, function code 1215032 or 1203 identifies applications that use the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure. Modify such applications to set value of the SYSIBMADM.REPLICATION_OVERRIDE built-in global variable to 'Y' instead. For more information, see SYSIBMADM.REPLICATION_OVERRIDE built-in global variable.

Function level 502 incompatible changes

If your environment contains existing user-defined functions named GRAPHIC or VARGRAPHIC, applications with unqualified references to the existing user-defined functions might start invoking the built-in function instead of the user-defined function in certain situations. For information about avoiding such situations, see Ensuring that Db2 executes the intended user-defined function.

Function level 501 incompatible changes

Function level 501 introduces no incompatible changes.