Release Notes

SQL Reference

16.1 IDENTITY_VAL_LOCAL

>>-IDENTITY_VAL_LOCAL--(--)------------------------------------><

The schema is SYSIBM.

The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column, where the assignment occurred as a result of a single row INSERT statement using a VALUES clause. The function has no input parameters.

The result is a DECIMAL(31,0), regardless of the actual data type of the identity column to which the result value corresponds.

The value returned is the value assigned to the identity column of the table identified in the most recent single row INSERT statement with a VALUES clause for a table containing an identity column. Note that the INSERT statement must be issued at the same level ¹ (that is, the value is available locally at the level it was assigned, until it is replaced by the next assigned value).

The assigned value could be a value supplied by the user (if the identity column is defined as GENERATED BY DEFAULT), or an identity value generated by DB2.

The function returns the null value in the following situations:

when a single row INSERT statement with a VALUES clause has not been issued for a table containing an identity column at the current processing level
when a COMMIT or ROLLBACK of a unit of work has occurred since the most recent INSERT statement that assigned a value. ²

The result of the function is not affected by the following statements:

a single row INSERT statement with a VALUES clause for a table that does not contain an identity column
a multiple row INSERT statement with a VALUES clause
an INSERT statement with a fullselect
a ROLLBACK TO SAVEPOINT statement.

Notes:

Expressions in the VALUES clause of an INSERT statement are evaluated prior to the assignments for the target columns of the INSERT statement. Thus, an invocation of an IDENTITY_VAL_LOCAL function invoked in the VALUES clause of an INSERT statement uses the most recently assigned value for an identity column from a previous INSERT statement. The function returns the null value if no previous single row INSERT statement with a VALUES clause for a table containing an identity column has been executed within the same level as the IDENTITY_VAL_LOCAL function.
The IDENTITY_VAL_LOCAL function cannot be used in a trigger or an SQL function (SQLSTATE 42997).
The identity column value of the table for which the trigger is defined can be determined within a trigger, by referencing the trigger transition variable for the identity column.
Since the results of the IDENTITY_VAL_LOCAL function are not deterministic, the result of an invocation of the IDENTITY_VAL_LOCAL function within the SELECT statement of a cursor can vary for each FETCH statement.
The assigned value is the value actually assigned to the identity column (that is, the value that would be returned on a subsequent SELECT statement). This value is not necessarily the value provided in the VALUES clause of the INSERT statement, or a value generated by DB2. The assigned value could be a value specified in a SET transition variable statement within the body of a before insert trigger, for a trigger transition variable associated with the identity column.
The value returned by the function is unpredictable following a failed single row INSERT with a VALUES clause into a table with an identity column. The value may be the value that would have been returned from the function had it been invoked prior to the failed INSERT, or it may be the value that would have been assigned had the INSERT succeeded. The actual value returned depends on the point of failure and is therefore unpredictable.

Examples:

Set the variable IVAR to the value assigned to the identity column in the EMPLOYEE table. If this insert is the first into the EMPLOYEE table, then IVAR would have a value of 1.
```
CREATE TABLE EMPLOYEE 
(EMPNO   INTEGER GENERATED ALWAYS AS IDENTITY,
 NAME    CHAR(30),
 SALARY  DECIMAL(5,2),
 DEPTNO  SMALLINT)
```
An IDENTITY_VAL_LOCAL function invoked in an INSERT statement returns the value associated with the previous single row INSERT statement, with a VALUES clause for a table with an identity column. Assume for this example that there are two tables, T1 and T2. Both T1 and T2 have an identity column named C1. DB2 generates values in sequence starting with 1 for the C1 column in table T1, and values in sequence starting with 10 for the C1 column in table T2.
```
CREATE TABLE T1 (C1 INTEGER GENERATED ALWAYS AS IDENTITY,
                 C2 INTEGER),
CREATE TABLE T2 (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY
                                   (START WITH 10),
                 C2 INTEGER),
INSERT INTO T1 (C2) VALUES (5),
INSERT INTO T1 (C2) VALUES (6),
SELECT * FROM T1
 
      C1            C2
      -----------   ----------
                1            5
                2            6
 
VALUES IDENTITY_VAL_LOCAL() INTO :IVAR
```
At this point, the IDENTITY_VAL_LOCAL function would return a value of 2 in IVAR, because that was the value most recently assigned by DB2. The following INSERT statement inserts a single row into T2, where column C2 gets a value of 2 from the IDENTITY_VAL_LOCAL function.
```
INSERT INTO T2 (C2) VALUES (IDENTITY_VAL_LOCAL()),
SELECT * FROM T2
   WHERE C1 = DECIMAL(IDENTITY_VAL_LOCAL(),15,0)
 
      C1                  C2
      -----------------   ----------
                    10.            2
```
Invoking the IDENTITY_VAL_LOCAL function after this insert results in a value of 10, which is the value generated by DB2 for column C1 of T2.

16.2 OLAP Functions

The following represents a correction to the "OLAP Functions" section under "Expressions" in Chapter 3.

 
aggregation-function
 
|--column-function--OVER---(--+------------------------------+-->
                              '-| window-partition-clause |--'
 
>----+--------------------------------------------------------------------+>
     '-| window-order-clause |--+--------------------------------------+--'
                                '-| window-aggregation-group-clause |--'
 
>---------------------------------------------------------------|
 
window-order-clause
 
              .-,-------------------------------------------.
              V                       .-| asc option |---.  |
|---ORDER BY-----sort-key-expression--+------------------+--+---|
                                      '-| desc option |--'
 
asc option
 
         .-NULLS LAST--.
|---ASC--+-------------+----------------------------------------|
         '-NULLS FIRST-'
 
desc option
 
          .-NULLS FIRST--.
|---DESC--+--------------+--------------------------------------|
          '-NULLS LAST---'
 
window-aggregation-group-clause
 
|---+-ROWS--+---+-| group-start |---+---------------------------|
    '-RANGE-'   +-| group-between |-+
                '-| group-end |-----'
 
group-end
 
|---+-UNBOUNDED FOLLOWING-----------+---------------------------|
    '-unsigned-constant--FOLLOWING--'

In the window-order-clause description:

NULLS FIRST: The window ordering considers null values before all non-null values in the sort order.
NULLS LAST: The window ordering considers null values after all non-null values in the sort order.

In the window-aggregation-group-clause description:

window-aggregation-group-clause

The aggregation group of a row R is a set of rows, defined relative to R in the ordering of the rows of R's partition. This clause specifies the aggregation group. If this clause is not specified, the default is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, providing a cumulative aggregation result.

ROWS: Indicates the aggregation group is defined by counting rows.
RANGE: Indicates the aggregation group is defined by an offset from a sort key.
group-start: Specifies the starting point for the aggregation group. The aggregation group end is the current row. Specification of the group-start clause is equivalent to a group-between clause of the form "BETWEEN group-start AND CURRENT ROW".
group-between: Specifies the aggregation group start and end based on either ROWS or RANGE.
group-end: Specifies the ending point for the aggregation group. The aggregation group start is the current row. Specification of the group-end clause is equivalent to a group-between clause of the form "BETWEEN CURRENT ROW AND group-end".
UNBOUNDED PRECEDING: Includes the entire partition preceding the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.
UNBOUNDED FOLLOWING: Includes the entire partition following the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.
CURRENT ROW: Specifies the start or end of the aggregation group based on the current row. If ROWS is specified, the current row is the aggregation group boundary. If RANGE is specified, the aggregation group boundary includes the set of rows with the same values for the sort-key-expressions as the current row. This clause cannot be specified in group-bound2 if group-bound1 specifies value FOLLOWING.
value PRECEDING: Specifies either the range or number of rows preceding the current row. If ROWS is specified, then value is a positive integer indicating a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or value FOLLOWING.
value FOLLOWING: Specifies either the range or number of rows following the current row. If ROWS is specified, then value is a positive integer indicating a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

16.3 SQL Procedures/Compound Statement

Following is a revised syntax diagram for the Compound Statement:

                         .-NOT ATOMIC--.
>>-+---------+--BEGIN----+-------------+------------------------>
   '-label:--'           '-ATOMIC------'
 
>-----+-----------------------------------------------+--------->
      |  .-----------------------------------------.  |
      |  V                                         |  |
      '-----+-| SQL-variable-declaration |-+---;---+--'
            +-| condition-declaration |----+
            '-| return-codes-declaration |-'
 
>-----+--------------------------------------+------------------>
      |  .--------------------------------.  |
      |  V                                |  |
      '----| statement-declaration |--;---+--'
 
>-----+-------------------------------------+------------------->
      |  .-------------------------------.  |
      |  V                               |  |
      '----DECLARE-CURSOR-statement--;---+--'
 
>-----+------------------------------------+-------------------->
      |  .------------------------------.  |
      |  V                              |  |
      '----| handler-declaration |--;---+--'
 
      .-------------------------------.
      V                               |
>--------SQL-procedure-statement--;---+---END--+--------+------><
                                               '-label--'
 
SQL-variable-declaration
 
               .-,--------------------.
               V                      |
|---DECLARE-------SQL-variable-name---+------------------------->
 
                     .-DEFAULT NULL-------.
>-----+-data-type----+--------------------+-+-------------------|
      |              '-DEFAULT--constant--' |
      '-RESULT_SET_LOCATOR--VARYING---------'
 
condition-declaration
 
|---DECLARE--condition-name--CONDITION--FOR--------------------->
 
                 .-VALUE-.
     .-SQLSTATE--+-------+---.
>----+-----------------------+---string-constant----------------|
 
statement-declaration
 
             .-,-----------------.
             V                   |
|---DECLARE-----statement-name---+---STATEMENT------------------|
 
return-codes-declaration
 
|---DECLARE----+-SQLSTATE--CHAR (5)--+---+--------------------+-|
               '-SQLCODE--INTEGER----'   '-DEFAULT--constant--'
 
handler-declaration
 
|---DECLARE----+-CONTINUE-+---HANDLER--FOR---------------------->
               +-EXIT-----+
               '-UNDO-----'
 
      .-,-----------------------------------.
      V               .-VALUE-.             |
>---------+-SQLSTATE--+-------+--string--+--+------------------->
          +-condition-name---------------+
          +-SQLEXCEPTION-----------------+
          +-SQLWARNING-------------------+
          '-NOT FOUND--------------------'
 
>----SQL-procedure-statement------------------------------------|

A statement-declaration declares a list of one or more names that are local to the compound statement. A statement name cannot be the same as another statement name within the same compound statement.

16.4 LCASE and UCASE (Unicode)

In a Unicode database, the entire repertoire of Unicode characters is uppercased (or lowercased) based on the Unicode properties of these characters. Double-wide versions of ASCII characters, as well as Roman numerals, now case convert correctly.

16.5 WEEK_ISO

Change the description of this function to the following:

   The schema is SYSFUN.
 
   Returns the week of the year of the argument as an integer value
   in range 1-53. The week starts with Monday and always includes 7 days.
   Week 1 is the first week of the year to contain a Thursday,
   which is equivalent to the first week containing January 4.
   It is therefore possible to have up to 3 days at the beginning of a year
   appear as the last week of the previous year. Conversely, up to 3 days
   at the end of a year may appear as the first week of the next year.
 
   The argument must be a date, timestamp, or a valid character string
   representation of a date or timestamp that is neither
   a CLOB nor a LONG VARCHAR. 
 
   The result of the function is INTEGER. The result can be null;
   if the argument is null, the result is the null value. 
 
   Example:
 
   The following list shows examples of the result of WEEK_ISO and DAYOFWEEK_ISO.
 
   DATE       WEEK_ISO    DAYOFWEEK_ISO
   ---------- ----------- -------------
   1997-12-28          52             7
   1997-12-31           1             3
   1998-01-01           1             4
   1999-01-01          53             5
   1999-01-04           1             1
   1999-12-31          52             5
   2000-01-01          52             6
   2000-01-03           1             1

16.6 Naming Conventions and Implicit Object Name Qualifications

Add the following note to this section in Chapter 3:

   The following names, when used in the context of SQL Procedures,
   are restricted to the characters allowed in an
   ordinary identifier, even if the names are delimited:
 
   - condition-name
   - label
   - parameter-name
   - procedure-name
   - SQL-variable-name
   - statement-name

16.7 Queries (select-statement/fetch-first-clause)

The last paragraph in the description of the fetch-first-clause:

   Specification of the fetch-first-clause in a select-statement
   makes the cursor not deletable (read-only). This clause
   cannot be specified with the FOR UPDATE clause.

is incorrect and should be removed.

16.8 Libraries Used by the CREATE WRAPPER Statement on Linux

Linux uses libraries called LIBDRDA.SO and LIBSQLNET.SO, not LIBDRDA.A and LIBSQLNET.A as may have been documented previously.

Footnotes:

¹: A new level is initiated when a trigger, function, or stored procedure is invoked.
²: Interfaces that automatically commit after each statement will return a null value when the function is invoked in separate statements, unless the automatic commit is turned off.

[ Top of Page | Previous Page | Next Page | Table of Contents ]