What's new for IBM i 7.1

Read about new or significantly changed information for the SQL programming topic collection.

Result sets returned to programs

You can return a result set from a stored procedure to a program and have the program consume the result set. For more information, see Writing a program or SQL procedure to receive the result sets from a stored procedure.

XML

The XML data type has been added to SQL. This includes internal handling of XML values and XML schema decomposition. For more information, see SQL statements and SQL/XML functions.

Field procedures

A field procedure is a user-written exit routine to transform values in a single column. When values in the column are changed, or new values inserted, the field procedure is invoked for each value, and can transform that value (encode it) in any way. The encoded value is then stored. When values are retrieved from the column, the field procedure is invoked for each value, which is encoded, and must decode it back to the original value. For more information, see Defining field procedures.

MERGE statement

The MERGE statement can be used to either insert new rows or update existing rows in a table or view, depending on whether they already exists in the table or view. For more information, see Merging data.

Global variables

Global variables can be created and used in SQL. For more information, see Creating and using global variables.

Arrays in SQL procedures

An array data type has been added for use in SQL procedures. For more information, see Array support in SQL procedures and Debugging an SQL routine.

WebSphere MQ with DB2

DB2 provides an application programming interface to the WebSphere® MQ message handling system through a set of external user-defined functions, which are called DB2 MQ functions. You can use these functions in SQL statements to combine DB2 database access with WebSphere MQ message handling. For more information, see WebSphere MQ with DB2.

Other functional changes to the SQL programming information

An option to replace an existing object has been added to many DDL CREATE statements. See Replacing existing objects .

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.

What's new as of February 2013

Multiple event triggers

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

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.

What's new as of October 2012

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.

CREATE TABLE referencing a remote table

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

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.

RUNSQLSTM OPTION parameter

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

What's new as of April 2012

Obfuscation

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

XMLTABLE

The XMLTABLE table function provides a way to work with XML content as a relational table. For more information, see Using XMLTABLE to reference XML content as a relational table. In addition, SQL XML Programming contains all the new and existing XML information that is related to SQL.

Insert from a remote table

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

RUNSQL

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

What's new as of April 2011

Connect by

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

How to see what's new or changed

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

The image to mark where new or changed information begins.
The image to mark where new or changed information ends.

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.