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.
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.
Any 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.
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.
Any 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.
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.
Any 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.
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.
Any 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.
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 |
|
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
Db2 13 introduces or changes the following Db2-supplied stored procedures:
- FL 100 XSR_COMPLETE stored procedure can now run in a 64-bit JVM.