Start of change

V13R1Mnnn application compatibility levels

When you set the application compatibility levels, applications that attempt to use new functions and features that are introduced in or later might behave differently or receive an error.

Important: Before you run applications at higher application compatibility (APPLCOMPAT) levels in Db2 13, you might need to make adjustments for certain incompatible behavior changes that are unrelated to use of new SQL capabilities. You can use a trace for IFCID 0376 to identify these incompatible changes. For more information, see Incompatible changes for APPLCOMPAT levels in Db2 13.

You can use the application compatibility (APPLCOMPAT) level of applications, and objects such as routines or triggers, to control the adoption and use of SQL capabilities that are introduced in function levels. Generally, applications, and routines or triggers, cannot use new or changed SQL capabilities unless the effective application compatibility level is equivalent to or higher than the function level that introduced the changes. The application compatibility level applies to most SQL statements, including data definition statements (such as CREATE and ALTER statements) and data control statements (such as GRANT and REVOKE statements).

Tip: Start of changeFor best results, configure your development environment to use the lowest application compatibility level that the application will run at in the production environment. For dynamic SQL, remember to consider the application compatibility levels of client and NULLID packages. If you develop and test applications at a higher application compatibility level and try to run them at a lower level in production, you are likely to encounter SQL code -4743 and other errors when you deploy the applications to production.End of change

SQL changes in V13R1Mnnn APPLCOMPAT levels

The SQL capabilities in the following tables are available starting in the specified Db2 13 function level for applications that run at the equivalent APPLCOMPAT level or higher.

Start of changeAttempts to use the new SQL syntax or behaviors at lower application compatibility (APPLCOMPAT) levels can result in error conditions, such as SQL code -4743, or sometimes the previous behavior continues as before.End of change

For more information, see the following topics:

SQL changes at APPLCOMPAT level V13R1M501 or higher

APPLCOMPAT level SQL element Change introduced Incompatible change?
V13R1M509 TRANSFER OWNERSHIP statement New keywords:
  • function-designator
  • PROCEDURE procedure-name
  • SEQUENCE sequence-name
 No
V13R1M508 BLOCKING_THREADS table function New parameters: tb_list, ix_list, sp_list, options No
V13R1M08 CREATE TABLESPACE statement New keywords: FOR SORT, FOR DGTT No
V13R1M508 New built-in scalar functions for generating and converting universally unique identifier (UUID) values. No
V13R1M507 ALTER TABLE statement The ALTER PARTITIONING clause can specify TO PARTITION BY GROWTH to convert a table from PBR to PBG partitioning. No
V13R1M507 ALTER TABLESPACE statement The primary space allocation quantity (PRIQTY) is increased to support up to 1 TB, and the secondary space allocation quantity (SECQTY) is increased to support up to 200 GB. No
V13R1M507 CREATE TABLESPACE statement The primary space allocation quantity (PRIQTY) is increased to support up to 1 TB, and the secondary space allocation quantity (SECQTY) is increased to support up to 200 GB. No
V13R1M507 SELECT statement A restriction previously enforced by SQLCODE -20555 reason code 1 is lifted for certain situations where the statement does not return historical data. No
V13R1M507 SYSTIME_PERIOD_ADJUST built-in global variable Applications can use this new global variable to instruct Db2 to adjust timestamp values instead of returning a -20528 SQLCODE error when concurrent updates to a system-period temporal table would otherwise cause a beginning timestamp value greater than the ending timestamp value in the history table. No
V13R1M506 Columns defined with column masks Restrictions previously enforced by SQLCODE -20478 reason codes 24 and 27 are lifted. No
V13R1M506 ALTER TABLE statement IMPLICLITY HIDDEN can be specified for a ROWID column in the ADD COLUMN clause of an ALTER TABLE SPACE statement. No
V13R1M506 INSERT statement Multiple rows can be specified with comma separators in the VALUES form of the INSERT statement. No
V13R1M505 INTERPRET scalar function The INTERPRET function returns a representation of its argument in a specified data type. No
V13R1M504 LISTAGG aggregate function The ORDER BY clause can now be specified in a fullselect that contains an invocation of the LISTAGG built-in function. No
V13R1M504 AI_COMMONALITY New built-in function No
V13R1M503 ALTER TABLE with ADD COLUMN for row change timestamp columns When processing ALTER TABLE statements that specify ADD COLUMN for ROW CHANGE TIMESTAMP columns, Db2 now sets the corresponding value in the DEFAULTVALUE column value in the SYSIBM.SYSCOLUMNS catalog table to the timestamp of the ALTER TABLE statement. Yes
V13R1M503 IN predicate Queries that specify IN list predicates with more than 32,767 (32K) elements can be run as accelerator-only queries in IBM® Db2 Analytics Accelerator for z/OS® version 7. No
V13R1M503 SELECT INTO statement The optimize-clause, such as OPTMIZE FOR 2 ROWS can be now specified in SELECT INTO statements. No
V13R1M501 DEADLOCK_RESOLUTION_PRIORITY New built-in global variable.  

SQL changes APPLCOMPAT level V13R1M500

SQL element Change introduced APAR Incompatible change?
ALTER FUNCTION statement (compiled SQL scalar function) New clauses: AS ROLE or AS USER on the PACKAGE OWNER clause None No
ALTER PROCEDURE statement (SQL - native procedure) New clauses: AS ROLE or AS USER on the PACKAGE OWNER clause None No
CREATE FUNCTION statement (compiled SQL scalar function) New clauses: AS ROLE or AS USER on the PACKAGE OWNER clause None No
CREATE PROCEDURE statement (SQL - native procedure) New clauses: AS ROLE or AS USER on the PACKAGE OWNER clause None No
ALTER TABLE statement

New clause: ALTER PARTITIONING TO PARTITION BY RANGE

New concurrency behavior when the DATA CAPTURE clause is specified.

 None No
ALTER TABLE statement with ALTER PARTITIONING TO PARTITION BY RANGE

Db2 no longer issues SQL code -20385 for certain pending data definition changes when you alter the partitioning scheme of a table from partition-by-growth (PBG) to partition-by-range (PBR).

 PH51359 No
CREATE TABLESPACE statement The default value for the MAXPARTITIONS clause is changed from 256 to 254. None Yes
SET CURRENT LOCK TIMEOUT Applications can issue this statement to specify their own resource timeout values that override the IRLMRWT subsystem parameter setting. Use of this statement can be limited by setting the SPREG_LOCK_TIMEOUT_MAX subsystem parameter. The default value is -1, which means no limit. None No
CURRENT LOCK TIMEOUT special register New special register None No
FETCH FIRST n ROWS Parallel child tasks for queries that use FETCH FIRST n ROWS clauses no longer write out EREP entries with the 00E50013 reason code when they stop processing. PH48183  
AI_ANALOGY New built-in function None No
AI_SEMANTIC_CLUSTER New built-in function None No
AI_SIMILARITY New built-in function None No
End of change