What's new for IBM i 7.4
This topic highlights the changes made to this topic collection for IBM® i 7.4.
The major new features covered in this book include:
- New HASH functions using the MD5, SHA1, SHA-256 and SHA-512 algorithms: HASH_MD5, HASH_SHA1, HASH_SHA256, and HASH_SHA512. The function previously named HASH has been renamed to HASH_VALUES: HASH_VALUES
- New trigger attribute for Db2® Mirror: CREATE TRIGGER, ALTER TRIGGER
- CREATE INDEX allows index columns to be renamed: CREATE INDEX
- INSERT a row containing only default values: INSERT
- The SET transition-variable statement has been merged into the assignment-statement: assignment-statement
In addition, the following enhancements are new since the initial release of 7.3:
- New OLAP function: PERCENT_RANK: OLAP specifications
- IS JSON and JSON_EXISTS predicates: IS JSON predicate and JSON_EXISTS predicate
- LISTAGG aggregate function: LISTAGG
- DECFLOAT_FORMAT scalar function: DECFLOAT_FORMAT
- JSON publishing and scalar functions: JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_ARRAY, JSON_OBJECT, JSON_QUERY, and JSON_VALUE
- EXTRACT function has more options: EXTRACT
- LTRIM and RTRIM functions support a second argument: LTRIM and RTRIM
- NOW function allows optional precision: NOW
- REPLACE function's third argument is optional: REPLACE
- VARCHAR_FORMAT supports 2 new timestamp format string elements for microseconds and milliseconds: VARCHAR_FORMAT
- JSON_TABLE table function: JSON_TABLE
- LIMIT and OFFSET restrictions removed: fetch-clause and offset-clause
- The table correlation name for nested table expressions, table functions, JSON_TABLE, XMLTABLE, and UNNEST is optional: table-reference
- Functions can be defined as STATEMENT DETERMINISTIC: CREATE FUNCTION (external scalar), CREATE FUNCTION (external table), CREATE FUNCTION (SQL scalar), CREATE FUNCTION (SQL table)
- Unqualified functions, variables, and types within an SQL procedure or function will record a value of CURRENT PATH in SYSROUTINEDEP: CREATE PROCEDURE (SQL), CREATE FUNCTION (SQL scalar), and CREATE FUNCTION (SQL table)
- CREATE TRIGGER allows dynamic statements to use 3-part names: CREATE TRIGGER
- DELETE and UPDATE allow ORDER BY, FETCH, and OFFSET: DELETE and UPDATE
- GRANT and REVOKE on schemas: GRANT (schema privileges) and REVOKE (schema privileges)
- INCLUDE statement supported for SQL procedures, functions, and triggers: INCLUDE statement
- SYSPARTITIONSTAT returns the TEXT value and information about partial transactions: SYSPARTITIONSTAT
- SYSPROGRAMSTAT returns module name: SYSPROGRAMSTAT
What's new as of November 2023
- ENCRYPT_AES256 function uses a 256 bit encryption key: ENCRYPT_AES256
- HTML_ENTITY_DECODE and HTML_ENTITY_ENCODE provide conversion of characters in an HTML string: HTML_ENTITY_DECODE and HTML_ENTITY_ENCODE
- The HTTP functions support two new headers options for returning cookies: HTTP options
- SYSCOLUMNS2 returns BUFFER_LENGTH and BUFFER_POSITION columns: SYSCOLUMNS2
What's new as of May 2023
- ADD_DAYS, ADD_HOURS, ADD_MINUTES, ADD_SECONDS, and ADD_YEARS scalar functions provide date arithmetic capability: ADD_DAYS, ADD_HOURS, ADD_MINUTES, ADD_SECONDS, and ADD_YEARS
- The HTTP functions in QSYS2 support BLOB data: HTTP_DELETE and HTTP_DELETE_BLOB, HTTP_GET and HTTP_GET_BLOB, HTTP_PATCH and HTTP_PATCH_BLOB, HTTP_POST and HTTP_POST_BLOB, HTTP_PUT and HTTP_PUT_BLOB, HTTP_DELETE_VERBOSE and HTTP_DELETE_BLOB_VERBOSE, HTTP_GET_VERBOSE and HTTP_GET_BLOB_VERBOSE, HTTP_PATCH_VERBOSE and HTTP_PATCH_BLOB_VERBOSE, HTTP_POST_VERBOSE and HTTP_POST_BLOB_VERBOSE, HTTP_PUT_VERBOSE and HTTP_PUT_BLOB_VERBOSE
What's new as of December 2022
- Support for geospatial data and functions is added to Db2 for i. See Geospatial Analytics.
- REPLICATION_OVERRIDE allows user-provided values to be specified for all generated columns for insert, update, and merge statements: REPLICATION_OVERRIDE
- FIRST_DAY scalar function returns the first day of a month: FIRST_DAY
- The HTTP functions support a new headers option, passing the values as a JSON object: HTTP options
- JSON_UPDATE scalar function allows insertion or modification of a value within a JSON document: JSON_UPDATE
- TIMESTAMPDIFF_BIG returns a BIGINT result data type: TIMESTAMPDIFF_BIG
- REMOTE TABLE allows a query containing a table function to run on a remote IBM i: table-reference
- Comments can be added to the return columns for a table function: COMMENT
- SYSCOLUMNSTAT returns varying length statistics: SYSCOLUMNSTAT
What's new as of May 2022
- TRY_CAST is similar to CAST, but returns the null value if the conversion fails: TRY_CAST specification
- HTTP_PATCH and HTTP_PATCH_VERBOSE functions have been added in QSYS2: HTTP_PATCH and HTTP_PATCH_BLOB and HTTP_PATCH_VERBOSE and HTTP_PATCH_BLOB_VERBOSE
What's new as of September 2021
- New HTTP functions in QSYS2 can make HTTP requests that use web services. These functions use the HTTP Transport support provided by the AXISC APIs: HTTP_DELETE and HTTP_DELETE_BLOB, HTTP_GET and HTTP_GET_BLOB, HTTP_POST and HTTP_POST_BLOB, HTTP_PUT and HTTP_PUT_BLOB, HTTP_DELETE_VERBOSE and HTTP_DELETE_BLOB_VERBOSE, HTTP_GET_VERBOSE and HTTP_GET_BLOB_VERBOSE, HTTP_POST_VERBOSE and HTTP_POST_BLOB_VERBOSE, HTTP_PUT_VERBOSE and HTTP_PUT_BLOB_VERBOSE
What's new as of April 2021
- SYSCOLUMNS2 view returns attributes for DDS created files: SYSCOLUMNS2
- SYSPACKAGESTAT returns an indication that the package contains single use statements: SYSPACKAGESTAT
What's new as of October 2020
- For CREATE FUNCTION, an external name that is a service program name does not require a library qualifier when the string form of the name is used. Naming conventions
- The SELECTIVITY clause allows user hints for individual predicates: Search conditions
- CREATE SEQUENCE supports the FOR SYSTEM NAME clause: CREATE SEQUENCE
- DROP supports the IF EXISTS clause: DROP
- The WHENEVER statement can call an internal procedure when a monitored condition is encountered: WHENEVER
- SYSCOLUMNS2_SESSION view returns columns in QTEMP: SYSCOLUMNS2_SESSION
What's new as of April 2020
- The language used to define SQL functions, procedures, and triggers is referred to as SQL PL (SQL Procedural Language)
- Synonyms for existing functions: ARRAY_MAX_CARDINALITY and ARRAY_TRIM
- HASH_ROW function performs a hash of all the column values for a row: HASH_ROW
- INTERPRET function assigns a data type to binary data: INTERPRET
- JSON_TABLE, JSON_QUERY, JSON_VALUE, JSON_TO_BSON, and BSON_TO_JSON have been modified to support a JSON expression that is an array: JSON_TABLE, JSON_QUERY, JSON_VALUE, JSON_TO_BSON, BSON_TO_JSON
- Weakly typed distinct types have been implemented: CREATE TYPE (distinct)
- Extended indicators can be used in OPEN and EXECUTE to allow omitting host variables from the USING list: OPEN, EXECUTE
- TAG statement added to complement WHENEVER statement for fully free form ILE RPG: TAG
- Extended indicators supported in SQL PL: assignment-statement
- Documented table SYSDUMMY1: SYSDUMMY1
- Variable length segment and blocked insert information added to SYSPARTITIONSTAT and SYSTABLESTAT views: SYSPARTITIONSTAT, SYSTABLESTAT
What's new as of October 2019
- ISNULL and NOTNULL are synonyms for the IS NULL and IS NOT NULL predicates: NULL predicate
- Synonyms for existing functions: NVL, POW, RANDOM, STRLEFT, STRRIGHT, STRPOS, TO_CLOB. NVL, POWER or POW, RANDOM or RAND, STRLEFT, STRRIGHT, STRPOS, and TO_CLOB
- CREATE FUNCTION (SQL scalar), CREATE_FUNCTION (SQL table), and CREATE PROCEDURE (SQL) support the PROGRAM NAME clause to allow the program or service program name to be specified: CREATE FUNCTION (SQL scalar), CREATE FUNCTION (SQL table), and CREATE PROCEDURE (SQL)
- CREATE FUNCTION (external scalar), CREATE_FUNCTION (external table), and CREATE PROCEDURE (external) removed the limit of 32 routine attributes that can be saved in the associated program or service program object.
- Additional index invalidation information added to SYSINDEXSTAT, SYSPARTITIONINDEXES, and SYSPARTITIONINDEXSTAT: SYSINDEXSTAT, SYSPARTITIONINDEXES, and SYSPARTITIONINDEXSTAT
How to see what's new or changed
To help
you see where technical changes have been made, this information uses:
- The
image
to mark where new or changed information begins. - The
image
to mark where new or changed information ends.
To find other information about what's new or changed this release, see the Memo to users.