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.
For 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.
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? |
|---|---|---|---|
| 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 | APAR | 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. |
None | 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). | None | 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). | None | 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).
Db2 12 application compatibility levels
For newer SQL capbilities introduced