SQL changes in Db2 13 application compatibility levels

You can use this information to plan for SQL changes at migration to Db2 13 and in continuous delivery.

Tip: The changes in the highest available function levels are listed first. If you are preparing for migration to Db2 13, start with SQL changes in Db2 13 function level 100.
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 Db2 13 function levels 502 and higher

The following SQL changes take effect for applications that run at the specified application compatibility (APPLCOMPAT) level or higher. The changes are listed with the highest available APPLCOMPAT level at the top.

Start of changeAny attempt to use SQL capabilities in the following table at a lower application compatibility than specified results in an error condition, such as SQL code -4743 or others.End of change

APPLCOMPAT level SQL element Change introduced Incompatible change?
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. No
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 V7 for z/OS. No
V13R1M503 SELECT INTO statement The optimize-clause, such as OPTMIZE FOR 2 ROWS can be now specified in SELECT INTO statements. No

SQL changes in Db2 13 function level 501

The following SQL changes take effect in Db2 13 for applications that run at application compatibility V13R1M501 or higher. Function level 501 (V13R1M501) is the first opportunity after migration to Db2 13 for applications to use new features and capabilities that depend on catalog changes in Db2 13.

Start of changeAny attempt to use SQL capabilities in the following table at a lower application compatibility than V13R1M502 results in an error condition, such as SQL code -4743 or others.End of change

SQL element Change introduced Incompatible change?
DEADLOCK_RESOLUTION_PRIORITY built-in global variable New built-in global variable No

For more information about these changes, see Function level 500 (for migrating to Db2 13 - May 2022).

SQL changes in Db2 13 function level 500

The following SQL changes take effect in Db2 13 for applications that run at application compatibility V13R1M500 or higher. Activating function level 500 (V13R1M500) prevents coexistence with and fallback to Db2 12. Function level 500 is also the first opportunity for applications to use many of the new capabilities in Db2 13. However, new capabilities that depend on Db2 13 catalog changes remain unavailable.

Start of changeAny attempt to use SQL capabilities in the following table at a lower application compatibility than V13R1M500 results in an error condition, such as SQL code -4743 or others.End of change

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 No
SET CURRENT LOCK TIMEOUT New statement None No
CURRENT LOCK TIMEOUT special register New special register None No
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

For more information about these changes, see Function level 500 (for migrating to Db2 13 - May 2022).

SQL changes in Db2 13 function level 100

The following SQL changes take effect in Db2 13 for applications that run at application compatibility V13R1M100 or higher. Db2 starts at function level 100 (V13R1M100) during migration to Db2 13, and fallback and coexistence with Db2 12 in data sharing remain possible. Many new capabilities in Db2 13 remain unavailable.

Start of changeAny attempt to use the capabilities in the following table at a lower application compatibility level than V13R1M100 results in an error condition, such as SQL code -4743 or others.End of change

SQL element Change introduced Incompatible change?
Column names longer than 30 bytes

Function level 100 extends the maximum length of a column name from 30 bytes of EBCDIC, up to 128 bytes with limited support for using the longer column names. The longer column names can be used when the TABLE_COL_NAME_EXPANSION subsystem parameter setting is ON. Although you can now define a column with a name up to 128 bytes, column names with a length greater than 30 bytes of EBCDIC might be truncated on a character boundary. Column names returned in an SQLDA contain 30 bytes at most. APIs that do not use the SQLDA to obtain a column name might return complete column names.

No
CREATE TABLE statement The PAGESET_PAGENUM subsystem parameter specifies the default for the PAGENUM option, and its default value is changed from ABSOLUTE to RELATIVE. see PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter). No
CREATE TABLESPACE statement The PAGESET_PAGENUM subsystem parameter specifies the default for the PAGENUM option, and its default value is changed from ABSOLUTE to RELATIVE. see PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter). No
View privileges
  • DBAs with DBADM authority on databases can drop a view created for another user and select from a view without always requiring system level authority (PH54863, PH54936).
No

For more information about these changes, see Function level 100 (for migrating to Db2 13 - May 2022).

New and changed Db2-supplied stored procedures

Begin general-use programming interface information.Db2 13 introduces or changes the following Db2-supplied stored procedures:

End general-use programming interface information.