Transient data set.

Proposal: add a new, non-standard SQL construct that functions like a
table and is specific to a SQL session. This object is called, for lack
of a better term, a transient data set.

Purpose: to allow the incremental definition of private, intermediate
data sets.

Creation:

A transient data set is created with a SQL select statement that
includes a new keyword YIELDING. For a singleton select or an
interactive select, YIELDING is an alternative to INTO.

SELECT <select list> FROM <query expression> YIELDING <tds name>

Example: select a, b, c from table1 yielding my_data

The columns of transient data set correspond in name and data type with
the elements in the select list of the statement that created the set.
No other attributes of the elements of the source data are assigned to
the transient data set columns.

Destruction:

A transient data set is destroyed when the session ends or with the drop
transient data set statement: DROP TRANSIENT DATA SET <name>

DML usage:

A transient data set can be used in any DML statement where a table can
be used, including SELECT, INSERT, UPDATE, and DELETE. In the case of
SELECT specifically, a transient data set may be used to redefine itself.

Projection:
SELECT a, c FROM my_data YIELDING my_data;

Redefinition:
SELECT x.a, y.b, x.c
FROM table1 x LEFT OUTER JOIN my_data y ON y.a = x.a
YIELDING my_data;

Combination
SELECT x.a, x.b, x.c, x.d FROM table2 x
UNION
SELECT y.a, y.b, y.c, NULL FROM my_data y
YIELDING my_data;

DDL usage:

The only standard DDL statement that can be used with a transient data
set is the extended drop statement: DROP TRANSIENT DATA SET <name>;

The non-standard DDL statements CREATE INDEX and DROP INDEX can also be
used on transient data sets. The indexes created are destroyed
automatically when the transient data set is destroyed.

Name scope:

Transient data set names are not visible outside the scope of the SQL
session in which they were created. An error is raised if the name of a
transient data set conflicts with the name of a permanent base table or
view.