SQL changes in Db2 13
You can use this information to plan for SQL changes at migration to Db2 13 and in application compatibility levels.
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.
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.
Attempts 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.
For more information, see the following topics:
SQL changes in Db2 13 function levels 501 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.
| APPLCOMPAT level | SQL element | Change introduced | Incompatible change? |
|---|---|---|---|
| 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 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.
Attempts 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.
| 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 |
For more information about these changes, see Function level 501 (Db2 13 installation or migration - 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.
The SQL changes in the following table take generally take effect at any APPLCOMPAT level.
| SQL element | Change introduced | APAR | Incompatible change? |
|---|---|---|---|
| ALTER TRUSTED CONTEXT statement |
|
PH65634, PH64219 | No |
| 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 |
| CREATE TRUSTED CONTEXT statement |
|
PH65634, PH64219 | No |
| CURRENT APPLICATION COMPATIBILITY special register | The abbreviation APPLCOMPAT can be specified instead of APPLICATION COMPATIBITY in any reference to this special register. |
PH64377 | No |
USING APPLICATION COMPATIBILITY in the REGENERATE clauses of the following statements:
|
The abbreviation APPLCOMPAT can be specified instead of APPLICATION COMPATIBITY. |
PH64377 | No |
| LOCK TABLE | With the RELEASE(DEALLOCATE) option in effect, Db2 also releases a lock that was acquired for a LOCK TABLE statement when the thread is reused for another user. | PH66304 | |
| View privileges |
|
PH54863, PH54936 | No |
For more information about these changes, see Function level 100 (for migrating to Db2 13 - May 2022).
Db2-supplied stored procedure changes in Db2 13
Db2 13 introduces or changes the following Db2-supplied stored procedures:
| Stored procedure | Change introduced |
|---|---|
| DSNACCOX |
FL 501 The data types of the following columns of the second result set are changed to BIGINT:
FL 501 The data type of the TOTALEXTENTS column in the second result set is changed INTEGER. |
| XSR_COMPLETE | FL 100 The XSR_COMPLETE stored procedure can now run in a 64-bit JVM. |
