What's new for IBM i 7.3

This topic highlights the changes made to this topic collection for IBM® i 7.3.

The major new features covered in this book include:

System-period temporal tables
Attach and detach partitions for a table
Additional OLAP functions
Additional aggregate functions
PROCESS_ID and THREAD_ID built-in global variables
Enhancements to ROUND, TRUNCATE, TIMESTAMP_FORMAT, and VARCHAR_FORMAT scalar functions
Inlining for SQL table functions
Support for the EXTIND option in SET OPTION for SQL functions, procedures, and triggers
Required Db2 PTF Group level tracking added for static statements to SYSPROGRAMSTMTSTAT
Increase maximum number of parameters for scalar functions and table functions to 2000
Increase number of return columns for table functions to 8000

In addition, the following enhancements are new since 7.2:

JOB_NAME and SERVER_MODE_JOB_NAME built-in global variables
REGEXP_xxx scalar functions and REGEXP_LIKE predicate for regular expression handling
OVERLAY and LOCATE_IN_STRING scalar functions
VARBINARY_FORMAT and VARCHAR_FORMAT_BINARY scalar functions
LIMIT and OFFSET support for queries
REPLACE option for CREATE TABLE
System name for CREATE VARIABLE
Support for PIPE statement in SQL table functions
Increase maximum number of indexes on a table to approximately 15000

What's new as of May 2022

HTTP_PATCH and HTTP_PATCH_VERBOSE functions have been added in QSYS2: HTTP_PATCH and HTTP_PATCH_VERBOSE

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, HTTP_GET, HTTP_POST, HTTP_PUT, HTTP_DELETE_VERBOSE, HTTP_GET_VERBOSE, HTTP_POST_VERBOSE, HTTP_PUT_VERBOSE

SYSCOLUMNS2 view returns attributes for DDS created files: SYSCOLUMNS2
SYSPACKAGESTAT returns an indication that the package contains single use statements: SYSPACKAGESTAT

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

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
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
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

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

The table correlation name for nested table expressions, table functions, JSON_TABLE, XMLTABLE, and UNNEST is optional: table-reference
DECFLOAT_FORMAT scalar function: DECFLOAT_FORMAT
JSON_OBJECT and JSON_OBJECTAGG have a new option for duplicate key behavior. The default is to allow duplicate keys: JSON_OBJECT and JSON_OBJECTAGG
JSON_TABLE, JSON_QUERY, and JSON_VALUE tolerate duplicate keys: JSON_TABLE, JSON_QUERY, and JSON_VALUE
EXTRACT function has more options: EXTRACT
NOW function allows optional precision: NOW
VARCHAR_FORMAT supports 2 new timestamp format string elements for microseconds and milliseconds: VARCHAR_FORMAT
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)
SYSPARTITIONSTAT returns information about partial transactions: SYSPARTITIONSTAT

JSON publishing and scalar functions: JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_ARRAY, JSON_OBJECT, JSON_QUERY, and JSON_VALUE
LIMIT and OFFSET restrictions removed: fetch-clause and offset-clause
DELETE and UPDATE allow ORDER BY, FETCH, and OFFSET: DELETE and UPDATE
GRANT and REVOKE on schemas: GRANT (schema privileges) and REVOKE (schema privileges)

To help you see where technical changes have been made, this information uses:

To find other information about what's new or changed this release, see the Memo to users.