What's new for IBM i 7.2

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

The major new features covered in this book include:

Allow period separator for qualification of objects in system naming
Support for INSERT and CREATE TABLE AS to run SELECT from a remote RDB
An RDB alias can be specified directly in an SQL statement using a three-part name
Row and column access control for tables
Increased precision for timestamp data type
Function resolution using casting rules
Enhancements to many datetime scalar functions
LPAD and RPAD scalar functions
The order of operands for the POSITION scalar function has been corrected
XMLTABLE table function
CONNECT BY for hierarchical queries
Dynamic compound statement
System name for CREATE TABLE, CREATE VIEW, CREATE INDEX, and DECLARE GLOBAL TEMPORARY TABLE
KEEP IN MEMORY for tables and indexes
VIOLATION clause for check constraints
Reference field (REFFLD) generation for CREATE TABLE AS and CREATE TABLE LIKE
A create table statement that has a single partitioning key column can have the range partitions defined in any order.
Program name for CREATE TRIGGER
Multiple event triggers
Expressions in PREPARE and EXECUTE IMMEDIATE
TRUNCATE statement
TRANSFER OWNERSHIP statement for tables, views, and indexes
USER and GROUP for GRANT and REVOKE statements
Built-in global variables
Obfuscation of statement text for procedures, functions, and triggers
Default values for function and procedure parameters
Named parameters for function and procedure invocation
Support for arrays in SQL scalar functions
Autonomous procedures
Constants in SQL routines
Unified debugger support for SQL scalar functions
Maximum index size increased to 1.7 terabytes
SYSPACKAGESTMTSTAT and SYSPROGRAMSTMTSTAT catalog views

What's new as of August 2018

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

What's new as of October 2017

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 FIRST, and OFFSET: DELETE and UPDATE
GRANT and REVOKE on schemas: GRANT (schema privileges) and REVOKE (schema privileges)

What's new as of March 2017

IS JSON and JSON_EXISTS predicates
LISTAGG aggregate function
LTRIM and RTRIM functions support a second argument
CREATE TRIGGER allows dynamic statements to use 3-part names

What's new as of October 2016

REPLACE function's third argument is optional
JSON_TABLE table function
Functions can be defined as STATEMENT DETERMINISTIC
INCLUDE statement supported for SQL procedures, functions, and triggers
SYSPARTITIONSTAT returns the TEXT value
SYSPROGRAMSTAT returns module name

What's new as of April 2016

Additional formatting options for TIMESTAMP_FORMAT and VARCHAR_FORMAT scalar functions
Enhancements to ROUND and TRUNCATE scalar functions
New built-in global variables: PROCESS_ID and THREAD_ID
SQL table functions are eligible to be inlined
Support for the EXTIND option in SET OPTION for SQL functions, procedures, and triggers
Required DB2 PTF Group level tracking for static statements added to SYSPROGRAMSTMTSTAT

What's new as of October 2015

LIMIT and OFFSET support for queries
OVERLAY and LOCATE_IN_STRING scalar functions
System name for CREATE VARIABLE

What's new as of April 2015

REPLACE option for CREATE TABLE
VARBINARY_FORMAT and VARCHAR_FORMAT_BINARY scalar functions
Maximum parameters for SQL functions increased to 1024

What's new as of October 2014

REGEXP_xxx scalar functions and REGEXP_LIKE predicate for regular expression handling
JOB_NAME and SERVER_MODE_JOB_NAME built-in global variables
Support for PIPE statement in SQL table functions

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.