The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 UDB Version 6.1. As a result, the following new statements are not supported: ALTER NICKNAME, ALTER SERVER, ALTER USER MAPPING, CREATE FUNCTION(Template), CREATE FUNCTION MAPPING, CREATE NICKNAME, CREATE SERVER, CREATE TYPE MAPPING, CREATE USER MAPPING, CREATE WRAPPER, GRANT(Server Privileges), REVOKE (Server Privileges), SET PASSTHRU, and SET SERVER OPTION.
In Chapter 3 ("Expressions"), the syntax diagram has been changed to:
.-operator------------------------------------.
V |
>>----+-----+---+-function--------------------+--+-------------><
+- + -+ +-(expression)----------------+
'- - -' +-constant--------------------+
+-column-name-----------------+
+-host-variable---------------+
+-special-register------------+
+-(scalar-fullselect)---------+
| (1) |
+-labeled-duration------------+
| (2) |
+-case-expression-------------+
| (3) |
+-cast-specification----------+
| (4) |
+-dereference-operation-------+
| (5) |
'-OLAP-function---------------'
operator:
(6)
|---+-CONCAT------+---------------------------------------------|
+- / ---------+
+- * ---------+
+- + ---------+
'- - ---------'
Notes:
1.See Labeled Durations for more information.
2.See CASE Expressions for more information.
3.See CAST Specifications for more information.
4.See Dereference Operations for more information.
5.See OLAP Functions for more information.
6.|| may be used as a synonym for CONCAT.
Following is a new subsection, "OLAP Functions", to be added to the end of "Expressions":
OLAP-function:
>--+--| ranking-function |----+------------------------------------>
'--| numbering-function |--'
ranking-function:
|--+--RANK()-------+--OVER--(--+-------------------------------+-->
'--DENSERANK()--' '--| window-partition-clause |--'
>---| window-order-clause |--)-------------------------------------|
numbering-function:
|--ROWNUMBER()-----OVER--(-+-------------------------------+------>
'--| window-partition-clause |--'
>---+---------------------------+--)-------------------------------|
'--| window-order-clause |--'
window-partition-clause:
.---,----------------------.
V |
|----PARTITION BY------partitioning-expression--+------------------|
window-order-clause:
.---,--------------------------------.
| |
V .--ASC-----. |
>----ORDER BY-----sort-key-expression--+----------+--+------------|
'--DESC----'
OnLine Analytical Processing (OLAP) functions provide the ability to return ranking and row numbering information in a query result. An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used as an argument of a column function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP-function.
The ranking function computes the ordinal rank of a row within the partition of the row according to the ordering of the rows in the partition. Rows that are not distinct with respect to the ordering within their partition are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values.
If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus if two or more rows are not distinct with respect to the ordering, there will be one or more gaps in the sequential rank numbering.
If DENSERANK is specified, the rank of a row is defined as 1 plus the number of rows preceding the row that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering.
The ROWNUMBER function computes the sequential row number, starting with 1 for the first row, of the row within its partition according to the ordering. If the ORDER BY clause is not specified in the window specification, the row numbers are assigned to the rows in arbitrary order as returned by the subselect (not according to any ORDER BY clause in the select-statement).
The data type of the result is big integer. The result cannot be null.
PARTITION BY (partitioning-expression,...)
Defines the partition within which the ranking or numbering is applied.
A partitioning-expression is an expression used in defining the partitioning
of the result set. Each column-name referenced in a partitioning-expression
must unambiguously reference a column of the result set of the subselect
of the OLAP function (SQLSTATE 42702 or 42703). The length of each
partitioning-expression must not be more than 255 bytes (SQLSTATE 42907).
A partitioning-expression cannot include a scalar-fullselect (SQLTATE 42822)
or any function that is not deterministic or has an external action (SQLSTATE 42845).
ORDER BY (sort-key-expression,...)
Defines the ordering of rows within a partition that helps determine
the value of the OLAP functions (it does not define the ordering of the result set,
although it may appear that way in some situations). A sort-key-expression
is an expression used in defining the ordering of the result set. Each column-name
referenced in a sort-key-expression must unambiguously reference a column
of the result set of the subselect of the OLAP function (SQLSTATE 42702 or 42703).
The length of each sort-key-expression must not be more than 255 bytes (SQLSTATE 42907).
A sort-key-expression cannot include a scalar-fullselect (SQLTATE 42822) or any function
that is not deterministic or has an external action (SQLSTATE 42845). This clause
is required for the RANK and DENSERANK functions (SQLSTATE 42601).
ASC
Uses the values of the sort-key-expression in ascending order.
Null values are considered last in the order.
DESC
Uses the values of the sort-key-expression in descending order.
Null values are considered first in the order.
Examples:
1. Display the ranking of employees, in order by surname, according to their total salary
(based on salary plus bonus) that have a total salary more than $30,000.
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE WHERE SALARY+BONUS > 30000
ORDER BY LASTNAME
Note that if the result is required to be ordered by the ranking,
replace the ORDER BY clause with:
ORDER BY RANK_SALARY
or
ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)
2. Rank the departments according to their average total salary.
SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
RANK() OVER
(ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY RANK_AVG_SAL
3. Rank the employees within a department according to their education level.
Having multiple employees with the same rank in the department should not
increase the next ranking value.
SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL,
DENSERANK() OVER
(PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME
4. Provide row numbers in the result of a query.
SELECT ROWNUMBER() OVER (ORDER BY WORKDEPT,LASTNAME) AS NUMBER,
LASTNAME, SALARY
FROM EMPLOYEE
ORDER BY WORKDEPT,LASTNAME;
5. List the top five wage earners.
SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY
FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER ORDER BY (SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE) AS RANKED_EMPLOYEE
WHERE RANK_SALARY < 6
ORDER BY RANK_SALARY;
Note that a nested table expression was used to first compute the result
including the rankings before the rank could be used in the where clause.
A common table expression could also have been used.
In the "Supported Functions" table in Chapter 4, the row for RAISE_ERROR should reference table footnote 3 to indicate that the function cannot be used as a source function.
Add the following syntax element to the syntax diagram, after the FENCED clause:
.--MODIFIES SQL DATA--.
--*--+---------------------+---
+--NO SQL-------------+
+--CONTAINS SQL-------+
'--READS SQL DATA-----'
Note: The * represents a large bullet in the diagram.
Add the following description:
MODIFIES SQL DATA, NO SQL, CONTAINS SQL, or READS SQL DATA
Indicates whether the stored procedure can execute any SQL statements,
and if so, what type of SQL statements. See Table xx for a detailed list of SQL statements
that can be executed under each data access indication.
MODIFIES SQL DATA
Indicates that the stored procedure can execute any SQL statement except statements
that are not supported in stored procedures (SQLSTATE 38003).
NO SQL
Indicates that the stored procedure cannot execute any SQL statements (SQLSTATE 38001).
CONTAINS SQL
Indicates that SQL statements that neither read nor modify SQL data can be executed
by the stored procedure (SQLSTATE 38004). Statements that are not supported
in any stored procedure return a different error (SQLSTATE 38003).
READS SQL DATA
Indicates that some SQL statements do not modify SQL data can be included
in the stored procedure (SQLSTATE 38002). Statements that are not supported
in any stored procedure return a different error (SQLSTATE 38003).
Add the following bullet with the table to the Notes section:
o The following table indicates whether or not an SQL statement (specified in the first column)
is allowed to execute in a stored procedure with the specified SQL data access indication.
If an executable SQL statement is encountered in a stored procedure defined with NO SQL,
SQLSTATE 38001 is returned. For other executions contexts, SQL statements that are not supported
in any context return SQLSTATE 38003. For other SQL statements not allowed in a CONTAINS SQL
context, SQLSTATE 38004 is returned, and in a READS SQL DATA context, SQLSTATE 38002 is returned.
Table xx. SQL Statement and SQL Data Access Indication
SQL Statement NO SQL CONTAINS SQL READS SQL DATA MODIFIES SQL DATA
---------------------- ------ ------------ -------------- -----------------
ALTER... N N N Y
BEGIN DECLARE SECTION Y (1) Y Y Y
CALL N N N N
CLOSE CURSOR N N Y Y
COMMENT ON N N N Y
COMMIT (2) N N N N
Compound SQL N Y Y Y
CONNECT N N (3) N (3) N (3)
CREATE ... N N N Y
DECLARE CURSOR Y (1) Y Y Y
DELETE N N N Y
DESCRIBE N N Y Y
DISCONNECT N N (3) N (3) N (3)
DROP .... N N N Y
END DECLARE SECTION Y (1) Y Y Y
EXECUTE N Y (4) Y (4) Y
EXECUTE IMMEDIATE N Y (4) Y (4) Y
EXPLAIN N N N Y
FETCH N N Y Y
FREE LOCATOR N Y Y Y
FLUSH EVENT MONITOR N N N Y
GRANT ... N N N Y
INCLUDE Y (1) Y Y Y
INSERT N N N Y
LOCK TABLE N Y Y Y
OPEN CURSOR N N Y Y
PREPARE N Y Y Y
REFRESH TABLE N N N Y
RELEASE CONNECTION N N (3) N (3) N (3)
RENAME TABLE N N N Y
REVOKE ... N N N Y
ROLLBACK (2) N N N N
SELECT INTO N N Y Y
SET CONNECTION N N (3) N (3) N (3)
SET INTEGRITY N N N Y
SET special register N Y Y Y
UPDATE N N N Y
VALUES INTO N N Y Y
WHENEVER Y (1) Y Y Y
Table notes:
o Although the SQL option implies that no SQL statements can be specified,
non-executable statements are not restricted.
o Implicit commits or rollbacks issued by utilities (such as REORG TABLE)
are also not supported in stored procedures.
o Connection management statements are not allowed in all stored procedure
execution contexts.
o It depends on the statement being executed. The statement specified
for the EXECUTE statement must be a statement that is allowed in the context
of the particular SQL access level in effect. For example, if the SQL access level
in effect is READS SQL DATA, the statement must not be an INSERT, UPDATE, or DELETE.
The following restriction is missing from the description of CREATE TABLE when defining a summary table:
When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include expressions that are a LOB type (or a distinct type based on a LOB type).
In the syntax diagram, replace the fragment "summary-table-options" with the following:
summary-table-options:
|--+--DEFINITION ONLY--+----------------------+----------+----|
| '--INCLUDING DEFAULTS--' |
'--DATA INITIALLY DEFERRED--REFRESH--+--DEFERRED---+--'
'--IMMEDIATE--'
Under summary-table-definition, fullselect description, delete the last paragraph which incorrectly states:
Furthermore, for REFRESH IMMEDIATE, the base table must have at least one unique index defined and the SELECT clause must include all of the columns of this unique index.
Replace the following text under "DEFINITION ONLY":
DEFINITION ONLY
The query is used only to define the table. The table is not populated
using the results of query and the REFRESH TABLE statement cannot be used.
When the CREATE TABLE statement is completed, the table is no longer
considered a summary table.
The columns of the table are defined based on the definitions of the columns
that result from the fullselect. If the fullselect references a single table
in the FROM clause, select list items that are columns of that table are defined
using the column name, data type, and nullability characteristic of the referenced table.
INCLUDING DEFAULTS
The defaults for any updatable columns of the fullselect are defined
for the corresponding columns of the created table. Columns that are not updatable
in the fullselect will not have a default defined in the corresponding column
of the created table.
Replace the following text in the description of "LIKE table-name1 or view-name or nickname":
LIKE table-name1 or view-name or nickname
Specifies that the columns of the table have exactly the same name
and description as the columns of the identified table (table-name1),
view (view-name) or nickname (nickname). The name specified after LIKE must
identify a table, view or nickname that exists in the catalog. A typed table
or typed view cannot be specified.
The use of LIKE is an implicit definition of n columns, where n is the number of columns
in the identified table, view or nickname. The implicit definition includes the column name,
data type, nullability characteristic and column default value of each of the columns
of table-name1. The implicit definition includes the column name, data type,
and nullability characteristic of each of the result columns of fullselect
defined in view-name. The implicit definition includes the column name, data type,
and nullability characteristic of each column of nickname.
The implicit definition does not include any other attributes of the identified table,
view or nickname. Thus the new table does not have any unique constraints,
foreign key constraints, triggers, or indexes. The table is created in the table space
implicitly or explicitly specified by the IN clause, and the table has any other optional
clause only if the optional clause is specified.
Replace "Data Type" column entry for the row in the table where the first column (Untyped Parameter Marker Location) entry is "Any or all operands of the IN list of the IN predicate" with the following text:
Results of applying the Rules for Result Data Types on all operands of the IN predicate (operands to the left and right of the IN predicate) that are other than untyped parameter markers.
Note that this may change the data type determined for the parameter marker by the previous versions, since it will consider the data types of operands on both sides of the IN keyword, instead of using the data type of the first operand (the operand to the left of the IN keyword).
Replace the sixth bullet in the "Notes" section with the following:
o Situations in which the system must check the whole table for integrity (INCREMENTAL option
cannot be specified), for the statement SET INTEGRITY for T IMMEDIATE CHECKED, are:
1. when new constraints have been added to T itself, or to any of its parents
which are in check pending state
2. when a Load Replace has taken place into T, or the NOT LOGGED INITIALLY WITH
EMPTY TABLE option has been activated after the last integrity check on T
3. (cascading effect of full processing) when any parent of T has been Load Replaced or
checked for integrity non-incrementally
4. if the table was in check pending state before migration,
full processing is required the first time the table is
checked for integrity after migration
5. if the table space containing the table or its parent has been
rolled forward to a point in time.
Under the description of "CURRENT OF cursor-name", add the following restriction:
This form of UPDATE cannot be used if target of the update is a view that includes an OLAP function in the select list of the fullselect that defines the view (SQLSTATE 42828).