What's new for IBM i 7.2

TRUNCATE statement

The TRUNCATE statement can be used to delete all rows from a table. For more information, see Removing rows from a table using the TRUNCATE statement.

System name for tables, views, and indexes

When creating a table, view, or index, the system name for the object can be specified on the create statement. For more information, see Creating a table and Creating and using views.

Connect by

Hierarchical queries can be defined using the CONNECT BY syntax. For more information, see Using recursive queries.

Insert from a remote table

You can insert into a local table with data retrieved from a non-local table. For more information, see Inserting data from a remote database.

CREATE TABLE referencing a remote table

You can create a local table with the definition and data retrieved from a non-local table. For more information, see Creating a table with remote server data.

Defaults for procedure parameters and using parameter names in CALL

You can define parameters for SQL and external procedures to have default values. Parameters with default values can be omitted when calling the procedure. The CALL statement can specify parameter names for any arguments. For more information, see Defining a procedure with default parameters.

Defaults for function parameters and using parameter names for function invocation

You can define default parameters for SQL and external functions. For more information, see Defining UDFs with default parameters. You can invoke a function by specifying parameter names for arguments. For more information, see Invoking UDFs with named arguments.

Array support for SQL functions

You can use arrays in SQL scalar functions. For more information, see Array support in SQL procedures and functions.

Dynamic compound statement

SQL routine logic can be used in a compound statement that is dynamically run. For more information, see Dynamic compound statement.

Multiple event triggers

A trigger can be defined for more than one event. For more information, see Multiple event SQL triggers.

Obfuscation

The content of an SQL procedure, function, or trigger can be obfuscated. For more information, see Obfuscating an SQL routine or SQL trigger.

SQL routine step debug

An SQL routine can be debugged by stepping directly through the SQL debug view. For more information, see Debugging an SQL routine.

SQL and external routine management

Procedures and functions are tied to system objects that can be administered with CL commands. For more information, see Managing SQL and external routine objects.

TRANSFER OWNERSHIP

The TRANSFER OWNERSHIP statement assigns a new owner for a database object. For more information, see Security for SQL objects.

Column masks and row permissions

Column masks and row permissions can be used to restrict data from being seen by certain users. For more information, see Column masks and row permissions.

RUNSQLSTM OPTION parameter

The RUNSQLSTM command does not need to generate a listing. For more information, see Using the SQL statement processor.

RUNSQL

This new CL command runs a single SQL statement. For more information, see Using the RUNSQL CL command.

SQL XML Programming

All of the information about using XML with SQL has moved to SQL XML Programming.

What's new as of August 2018

External procedures and functions set the number of parameters in the operational descriptor when calling an ILE service program. For more information, see Varying length parameter lists for external procedures and functions.

What's new as of October 2017

SQL can generate and consume JSON. For more information, see Working with JSON data.

What's new as of October 2016

SQL procedures, functions, and triggers can use the INCLUDE statement to share common code. For more information, see Using the INCLUDE statement.

What's new as of April 2015

The OR REPLACE option has been added to the CREATE TABLE statement. For more information, see Using CREATE OR REPLACE TABLE.

What's new as of October 2014

Pipelined table functions

A pipelined SQL table function is a more flexible version of a table function. For more information, see Example: SQL table UDFs.

SQL variable debug for SQL routines

Debugging of SQL routines allows you to display values of SQL variables. For more information, see Debugging an SQL routine.

Listing option available for RUNSQL command

A listing can be requested for the RUNSQL command. For more information, see Using the RUNSQL CL command.

How to see what's new or changed

In PDF files, you might see revision bars (|) in the left margin of new and changed information.

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