Database changes

Release to release Database changes that should be considered

New SQL reserved words and schema names
.
Using reserved words as correlation names
Native database access behavior changes
SQL Query Engine (SQE) versus Classic Query Engine (CQE) behavior differences for native database access
QDBRTVFD API changes
Retrieve Database File Description (QDBRTVFD) API might return 0 offsets and lengths for a CREATE TRIGGER statement.
Print SQL Information (PRTSQLINF) will not display obfuscated SQL statements
Copy file (CPYF) command change
CURRENT_USER special register
.
CURRENT SCHEMA special register changes
QSYS2.SYSROUTINEDEP column content changes
Changes to the QSYS2.OBJECT_STATISTICS table function
.
UPDATE using OVERRIDING USER VALUE
Position function argument changes
SQL Array assignment rules
Timestamp built-in functions in a view
Certain built-in functions in a view might prevent a restore to a previous release.
Create Table Like and format level IDs
CREATE TABLE LIKE might generate a different format level ID as compared to previous releases.
ICU sort table changes
GET DIAGNOSTICS modifies SQLSTATE and SQLCODE variables
RRN, PARTITION, and NODENUMBER functions with Common Table Expression (CTE) argument changes
RRN, PARTITION, and NODENUMBER functions with Common table expressions might trigger a CPD43AD.
Fixes to the SQL ILE RPG precompiler might cause SQL5011 messages
RPG application and OPNQRYF key mismatches might produce different results
.
SQL Call Level Interface Changes
Restore of logical files over existing files with different file/member level ids
DISPLAY_JOURNAL() table function changes
SYSTOOLS.GROUP_PTF_CURRENCY/SYSTOOLS.GROUP_PTF_DETAIL changes
TIMESTAMP_FORMAT scalar function can return SQLSTATE '22007' and SQLCODE -20448
FULL OUTER JOIN change
CREATE FUNCTION (SQL scalar) or CREATE FUNCTION (SQL table) with a data access classification of CONTAINS SQL may fail with SQLSTATE '2F004' and SQLCODE -579
Workload Capping and SMP degree *OPTIMIZE change
SQE changed to hold jobs when query related temporary storage exceeds MAXTMPSTG
SQL JSON functions duplicate key handling change
OBJECT_STATISTICS() table function changes for *USRPRF objects

Parent topic: IBM i operating system