SQL
This chapter describes the SQL features and enhancements that are delivered with DB2 11 for z/OS. The audience for this chapter is the application developers and database administrators.
This chapter includes the following topics:
•DGTT
 
 
Good news for application managers and developers:
DB2 11 has mechanisms in place to limit potential SQL (and XML) incompatibilities on application DML statements by allowing you to complete the following tasks:
•Identify applications affected by incompatible SQL (and XML) changes through trace records
•Control the compatibility level to DB2 10 at an application (package) level
6.1 Introduction
This section provides a summary of SQL features and enhancements to existing SQL statements.
Table 6-1 lists the key new SQL features which are discussed in other parts of this chapter. This table contains DML, DCL, and DDL statements that are new to DB2 11 for z/OS.
Table 6-1 Summary of SQL statements/features in DB2 11 for z/OS
SQL statement/feature
Description
CREATE VARIABLE
Defines Global variables.
CREATE TYPE (array)
(DROP TYPE clause can be used to drop the array data type)
Defines an array data type.
SET CURRENT APPLICATION COMPATIBILITY
Sets application compatibility level for dynamic/distributed applications. Works similar to the static equivalent APPLCOMPAT bind parameter.
SET CURRENT TEMPORAL BUSINESS_TIME
Sets the business time for use with temporal tables.
SET CURRENT TEMPORAL SYSTEM_TIME
Sets the system time for use with temporal tables.
SET assignment-statement (this is not truly new)
Sets the assignment-statement (DB2 SQL) The SET assignment-statement is a reclassification of the documentation of the SET host-variable and SET transition-variable statements into a single statement.
ALTER FUNCTION (SQL scalar) and
CREATE FUNCTION (SQL scalar)
New clauses:
•BUSINESS_TIME SENSITIVE
•SYSTEM_TIME SENSITIVE
•ARCHIVE SENSITIVE
•APPLCOMPAT
 
Changed clauses:
•data-type
•data-type2, which can include array-type-name
ALTER PROCEDURE (SQL native) and
CREATE PROCEDURE (SQL native)
New clauses:
•BUSINESS_TIME SENSITIVE
•SYSTEM_TIME SENSITIVE
•ARCHIVE SENSITIVE
•APPLCOMPAT
 
Changed clause:
•data-type, which can include array-type-name
CREATE PROCEDURE (external)
Changed clause:
•data-type, which can include array-type-name
ALTER TABLE DROP COLUMN
New clause
ALTER TABLE ENABLE ARCHIVE
New clauses:
•ENABLE ARCHIVE
•DISABLE ARCHIVE
ALTER TABLESPACE and
CREATE TABLESPACE
Changed clause:
•PCTFREE, which can now include FOR UPDATE smallint
COMMENT
Changed clause:
•data-type, which can include array-type-name
CREATE INDEX
New clauses:
•INCLUDE NULL KEYS
•EXCLUDE NULL KEYS
DECLARE GLOBAL TEMPORARY TABLE
New clause:
•LOGGED
•NOT LOGGED
EXECUTE
Changed clauses:
The object of the USING clause can be an SQL variable, SQL parameter,
global variable, or host variable.
FETCH
Changed clauses:
The object of the INTO clause can be a host variable, a global variable, an SQL parameter, an SQL variable, a transition variable, or an array element.
GRANT (function or procedure privileges) and corresponding REVOKE
Changed clauses:
•data-type, which can include array-type-name
GRANT (type or JAR privileges) and
corresponding REVOKE
Changed clauses:
The object of the TYPE clause can be a distinct type or an array type
SELECT INTO
Changed clauses:
The object of the INTO clause can be a host variable, a global variable, an SQL parameter, an SQL variable, a transition variable, or an array element.
SET PATH
Changed clauses:
The SYSTEM PATH now includes the following schemas:
•SYSIBM
•SYSFUN
•SYSPROC
•SYSIBMADM
SQL statement with subselect
Changed clauses:
The collection-derived-table clause is added to table-reference in the FROM clause of a subselect.
VALUES INTO
Changed clauses:
The object of the INTO clause can be a host variable, a global variable, an SQL parameter, an SQL variable, a transition variable, or an array element.
Refer to DB2 11 for z/OS What's New?, GC19-4068 for an alphabetical listing of the summary of changes to existing and new SQL statements.
6.2 Global variables
Traditionally within a relational database system, most interactions between an application and the DBMS are in the form of SQL statements within a connection. To share information between SQL statements within the same application context, the application that issued the SQL statements has to do this work by copying the values from the output arguments, such as host variables, of one statement to the input host variables of another. Similarly, when applications issue host-language calls to another application, host variables need to be passed among applications as input or output parameters for the applications to share common variable. Furthermore, SQL statements that are defined and contained within the DBMS, such as the SQL statements in the trigger bodies, cannot access this shared information.
These restrictions limit the flexibility of relational database systems and, thus, the ability of users of such systems to implement complex, interactive models within the database itself. Users of such systems are forced to put supporting logic inside their applications to access and transfer user application information and internal database information within a relational database system. Ensuring the security of the information that is transferred and accessed is also left to the user to enforce in their application logic.
To overcome this restriction and to maximize the flexibility of a DBMS, global variables are introduced in DB2 11 for z/OS. A global variable can be created, instantiated, accessed, and modified by the applications. Global variables are named memory variables that you can access and modify through SQL statements. Global variables enable you to share relational data between SQL statements without the need for application logic to support this data transfer. You can control access to global variables through the GRANT (global variable privileges) and REVOKE (global variable privileges) statements.
A global variable is associated with a specific application context, and contains a value that is unique to that application scope. A created global variable is available to any active SQL statement running against the database on which the variable was defined. A global variable can be associated with more than one application scope, but its value will be specific to each application scope.
SQL statements sharing the same connection (that is, under the same application scope) can create, access, and modify the same global variables. This enhancement includes the following functional additions to DB2.
6.2.1 DDL and catalog information
A new DDL statement allowing the application to create global variable to be shared among SQL statements using the same connection. A sample CREATE statement is depicted in Example 6-1.
Example 6-1 Sample create global variable statement
CREATE VARIABLE BATCH_START_TS TIMESTAMP
DEFAULT CURRENT TIMESTAMP;
The new SYSIBM.SYSVARIABLES table includes one row for each global variable that is created.
The new SYSIBM.SYSVARIABLEAUTH table includes one row for each privilege of each authorization ID that has privileges on a global variable.
The SYSIBM.SYSVARIABLES_TEXT table is an auxiliary table for the DEFAULTTEXT column of the SYSIBM.SYSVARIABLES table.
6.2.2 Qualifying global variables
Global variable names are qualified two-part names. For unqualified global variables, the implicit qualifier facilitates the naming resolution of global variables. DB2 determines the implicit qualifier for global variables as follows:
The schemas in the SQL PATH are searched in order from left to right for a matching global variable. If a global variable matches the global variable name in reference, resolution is complete. If no matching global variable is found after completing this step, an error is returned.
6.2.3 Global variable’s scope
The scope of global variable’s definition is similar to that of DB2 special register’s, in that, when created, the definitions of global variables are shared across different DB2 connections. However, each connection maintains its own instance of the global variable, such that the variable’s content is only shared among SQL statements within the same connection.
For example, if you use a Global variable, which was created using the DDL in Example 6-1 in a program, the first invocation of this Global variable has the same value as the current time stamp. The subsequent use of this global variable retains the initial instantiated value for the duration of the connection.
Example 6-2 shows how a Global variable’s value of 2013-08-02-14.59.46.423414 remains the same in different SQL statements (when referenced) within the same DB2 connection.
Example 6-2 Scope of global variable: Different SQL statements on the same DB2 connection
-- Initial execution of the SQL
SELECT BATCH_START_TS, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
;
-- Result set from the initial execution
 
BATCH_START_TS CURRENT TIMESTAMP
2013-08-02-14.59.46.423414 2013-08-02-14.59.46.423414
 
-- Second execution of the same SQL statement in the same SPUFI session
 
SELECT BATCH_START_TS, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
;
-- Result set from the second execution
BATCH_START_TS CURRENT TIMESTAMP
 
2013-08-02-14.59.46.423414 2013-08-02-14.59.46.424678
 
-- Third execution of the same SQL statement in the same SPUFI session
 
SELECT BATCH_START_TS, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
;
-- Result set from the third execution
BATCH_START_TS CURRENT TIMESTAMP
 
2013-08-02-14.59.46.423414 2013-08-02-14.59.46.425282
If you rerun this set of SQL statements at a different point in time (for example, in a different SPUFI session another time), it results in a different instantiated value for the global variable. That value remains in effect until the end of that connection.
6.2.4 Global variable’s naming resolution
DB2 naming resolution precedence rule is modified to include global variable references. If at the time of naming resolution, the definition of the referenced global variable does not exist, message DSNX200I or DSNX100I will be issued during BIND if VALIDATE(BIND) or VALIDATE(RUN) were specified, respectively, on the BIND command.
 
Note: It is also possible that during static bind time, the checked objects preceding global variables in naming resolution might not exist yet, resulting in the object name being resolved to global variables, provided that the variable definitions exist. In this case, the resolved name remains as global variables, even if the object becomes available before the execution of this statement, because the naming resolution was already done at static bind time.
6.3 Array data type
An array type is a user-defined data type that is an ordinary array or an associative array. The elements of an array type are based on one of the existing built-in data types.
 
Note: The array data type can only be used as one of the following data types:
•An SQL variable
•A parameter or RETURNS data-type of an SQL scalar function
•A parameter of a native SQL procedure
•The target data type for a CAST specification
Currently, the array data type is not supported in other contexts, such as columns of tables and views, triggers, and client interfaces that are not essential for migrating applications.
The CREATE TYPE (array) statement defines an array data type. The SYSIBM.SYSDATATYPES table contains one row for each array data type defined.
DROP TYPE array_name statement drops an array data type created using the CREATE TYPE array_name statement.
6.3.1 Ordinary arrays
An array with a user-defined upper bound on the number of elements, which are referenced by their ordinal position in the array.
After the execution of the assignment statement in Example 6-3, the cardinality of mySimpleA is set to 100. The elements with array indexes with values 1 to 99 are implicitly initialized to NULL.
Example 6-3 Sample - Ordinary Array definition
CREATE TYPE simple AS INTEGER ARRAY[];
BEGIN
SET mySimpleA[100] = 123;
END
6.3.2 Associative arrays
An array with no user-defined upper bound on the number of elements, which are ordered by and can be referenced by an array index value. Array index values are unique and do not have to be contiguous.
After the execution of the assignment statement in Example 6-4, the cardinality of the array is set to 1.
Example 6-4 Associative array data type - sample CREATE, DECLARE, and SET statements
CREATE TYPE assoc AS INTEGER ARRAY[INTEGER];
BEGIN
SET myAssocA[100] = 123;
END
6.3.3 ARRAY_EXISTS predicate
The ARRAY_EXISTS predicate tests for the existence of an array element with the specified index in an array. Example 6-5 shows the syntax of this new predicate.
Example 6-5 ARRAY_EXISTS predicate syntax
ARRAY_EXISTS (array-expression,array-index)
The ARRAY_EXISTS predicate produces the following results:
•True if array-variable includes an array index that is equal to the result of casting array-index to the data type of the array index of array-variable.
•False under either of the following conditions:
 – The array-variable does not include an array index that is equal to the result of casting array-index to the data type of the array index of array-variable.
 – Either argument is null.
•Never unknown.
6.4 Aliases and public aliases for SEQUENCES
DB2 10 for z/OS supports aliases for tables, views, and aliases. The definition of these aliases is recorded in the SYSIBM.SYSTABLES catalog table, with a value of A for the TYPE column. IBM DB2 for Linux, UNIX, and Windows supports aliases on aliases, tables, views, nicknames (federated related), module names (related to competitive database product with PL/SQL), and sequence objects. Competitive database products also allow synonyms to be created for sequence objects.
DB2 11 extends the support for SEQUENCE objects so that you can now create:
•A private ALIAS for a SEQUENCE
•A public ALIAS for a SEQUENCE
This section describes how to create and use these aliases for SEQUENCES and provide related considerations.
6.4.1 Private ALIAS for a SEQUENCE
The CREATE ALIAS syntax is extended as shown in Figure 6-1.
Figure 6-1 CREATE ALIAS statement syntax
Assuming that your CURRENT SQLID is user2, to create a private ALIAS user2.SEQ1 for user1.SEQ1 sequence, you can use either of the following DDL statements:
CREATE ALIAS user2.SEQ1 FOR user1.SEQ1
CREATE ALIAS SEQ1 FOR user1.SQ1
Both statements result in the creation of a private ALIAS user2.SEQ1. However, you cannot issue both statements. The second CREATE ALIAS fails with -601.
The information for table aliases was recorded in the SYSIBM.SYSTABLES table, and the information about the existence of the alias for user1.SEQ1 is recorded in the SYSIBM.SYSSEQUENCES table.
For this example, the SYSIBM.SYSSEQUENCES table include the following information:
SCHEMA user2
OWNER user2
NAME SEQ1
SEQTYPE A
SEQID an ID, for example 453
CREATEDBY user2
INCREMENT 0
START 0
MAXVALUE 0
….
Other columns include characteristics of the SEQUENCE 0 or N, as follows:
…..
SEQSCHEMA user1 (schema of the sequence the alias depends on)
SEQNAME SEQ1 (name of the sequence, the alias depends on)
….
You can use this alias with any of the SEQUENCE related statements, such as NEXT VALUE or PREVIOUS VALUE. Thus, for example, user2 can successfully execute the following statement, assuming that the user1.TAB1 table is an existing table:
INSERT INTO user1.TAB1 VALUES(NEXT VALUE FOR SEQ1, 'AAA'),
This INSERT command inserts one row into user1.TAB1 table and inserts the next available value for the user1.SEQ1 sequence, using characteristics such as INCREMENTS, MAXVALUE, and so on, of the user1.SEQ1 sequence.
6.4.2 Public ALIAS for a SEQUENCE
As mentioned previously, in addition to the ability of creating private aliases for sequences, DB2 11 allows you to create public aliases. All public aliases are created in the new SYSPUBLIC schema. This creation happens implicitly or explicitly, meaning that DB2 automatically assigns schema SYSPUBLIC to the ALIAS if you omit the schema name. As a consequence, the following statements create a SYSPUBLIC SEQ2 public alias for a user1.SEQ1 schema:
CREATE PUBLIC ALIAS SEQ2 FOR SEQUENCE user1.SEQ1
CREATE PUBLIC ALIAS SYSPUBLIC.SEQ2 for SEQUENCE user1.SEQ1
Both statements lead to the same information inserted into the SYSIBM.SYSSEQUENCES table, as follows:
SCHEMA SYSPUBLIC
OWNER user2
NAME SEQ1
SEQTYPE A
SEQID a ID, for example 453
CREATEDBY user2
INCREMENT 0
START 0
MAXVALUE 0
….
Other columns include characteristics of the SEQUENCE 0 or N, as follows:
…..
SEQSCHEMA user1 (schema of the sequence the alias depends on)
SEQNAME SEQ1 (name of the sequence, the alias depends on)
….
A public alias can always be referenced without qualifying its name with a schema name. The implicit qualifier of a public alias is SYSPUBLIC, which can also be specified explicitly.
When DB2 resolves an unqualified name, private aliases are considered before public aliases.
When there is a reference to a sequence, DB2 must resolve the reference to one of the following sequences:
•A private alias for a sequence
•A public alias for a sequence
•A sequence
For example, assume that a sequence named orders_seq exists, defined as follows, and an alias is named orders_seq_A is defined for this sequence. The orders_seq sequence generates odd values starting with 1, as follows:
CREATE SEQUENCE orders_seq AS INT
START WITH 1
INCREMENT BY 2
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER ;
 
CREATE ALIAS orders_seq_A FOR SEQUENCE orders_seq;
Another sequence named orders_seq2 exists as follows, and a public alias named orders_seq_A is defined for this sequence. The orders_seq2 sequence generates even values starting with 2, as follows:
CREATE SEQUENCE orders_seq2 AS INT
START WITH 2
INCREMENT BY 2
MINVALUE 2
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER ;
 
CREATE PUBLIC ALIAS orders_seq_A FOR SEQUENCE orders_seq2;
Note that both of these aliases have the same name, but different schemas. The first alias defined is a private alias, and it is qualified with the default schema. The second alias was defined as a public alias, which means that it is qualified by SYSPUBLIC.
The following customer_orders_t table demonstrates the use of sequence aliases:
CREATE TABLE customer_orders_t
( order_id INT NOT NULL ,
order_date DATE NOT NULL)
A NEXT VALUE sequence reference provides the value for the ORDER_ID column of the table in the following INSERT statement. The sequence reference specifies ORDERS_SEQ_A for the sequence. This name can represent a sequence itself, or it can be a reference to a private alias for a sequence or a public alias for a sequence. DB2 goes through a process of name resolution to determine the sequence to be used.
INSERT INTO customer_orders_t
VALUES (NEXT VALUE FOR orders_seq_A, CURRENT DATE) ;
Issuing a select statement shows the value that was generated for the sequence (the value of the ORDER_ID column) and that determined which sequence alias was used.
SELECT * FROM customer_orders_t;
Returns:
ORDER_ID ORDER_DATE
----------- ---------- -----------
1 07/11/2012
The value of 1 for the ORDER_ID column indicates that the ORDERS_SEQ sequence generated the value for the column. DB2 used the ORDERS_SEQ sequence, because the unqualified reference to ORDERS_SEQ_A resolved to the private alias ORDERS_SEQ_A, which is defined for the sequence ORDERS_SEQ.
6.4.3 Dropping an alias for sequence
Dropping a sequence alias (private or public) is restricted if any of the following dependencies exist:
•A trigger that uses the sequence in a NEXT VALUE or PREVIOUS VALUE expression exists.
•An inline SQL function1 that uses the sequences in a NEXT VALUE or PREVIOUS VALUE expression exists.
When an alias for a sequence is dropped, all packages that refer to the sequence alias are invalidated.
6.4.4 Security considerations
To create an alias for a sequence, the privilege set must include at least one of the listed authorities or privileges:
•The CREATEIN privilege on the schema
•SYSADM or SYSCTRL authority
•System DBADM
6.4.5 Considerations regarding application compatibility setting
Public aliases or private aliases can only be created and used in New Function Mode (NFM). In addition to that, to make use of private or public aliases, the value of special register CURRENT APPLICATION COMPATIBILITY must implicitly or explicitly be set to V11R1.
Assume that you have multiple sequences defined maybe to generate only odd or even numbers as discussed earlier. If these sequences, or public or private aliases, all have the same name, DB2 resolves the names as also discussed before. However, the results differ, depending on the value that is currently set for the CURRENT APPLICATION COMPATIBILITY special register.
Refer to the next two figures to explore the differences. Figure 6-2 represents V11R1 compatibility.
Figure 6-2 Application compatibility V11R1
The behavior for APPLICATION COMPATIBILITY set to V11R1 is as described before, but if you set it to V10R1, as shown in Figure 6-3, you receive negative SQL codes in all cases in which the new aliases for sequences are used.
Figure 6-3 Application compatibility V10R1
In two of these cases you receive the following message because you try to explicitly request the use of aliases for sequences:
DSNT408I SQLCODE = -4743, ERROR: ATTEMPT TO USE A FUNCTION WHEN THE
APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL
The -204 code received in the example where you implicitly try to use the public alias for the sequence occurs because DB2 does not even consider the existence of the sequence definition 4.
For the sequence, if APPLCOMPAT='V10R1', and the sequence is not qualified, there is no attempt to resolve it in the SYSPUBLIC schema. If it cannot be resolved at the first try through the private schema, DB2 issues -204.
If APPLCOMPAT='V11R1', and the sequence is not qualified, DB2 tries to resolve it in the SYSPUBLIC schema if it cannot be resolved at the first try through the private schema.
If the sequence is qualified, DB2 only tries to resolve in that specified qualifier.
After a sequence is resolved, if it is resolved to a public alias, it must be in V11R1. Otherwise, a -4743 error is issued.
CURRENT PATH does not include SYSPUBLIC; however, the public aliases that exist in this schema are found.
This behavior needs to be thoroughly checked when you start using aliases for sequences in NFM, while still running application with an application compatibility setting other than V11R1.
6.5 New built-in functions
DB2 11 for z/OS includes new built-in functions that improve the power of the SQL language. The schema is SYSIBM. Refer to DB2 11 for z/OS SQL Reference, SC19-4066 for syntax alternatives and additional examples.
6.5.1 ARRAY_AGG
The ARRAY_AGG function returns an array in which each value of the input set is assigned to an element of the array. ARRAY_AGG can be invoked in the following situations:
•Select list of a SELECT INTO statement
•Select list of a fullselect in the definition of a cursor that is not scrollable
•Select list of a scalar fullselect as a source data item for a SET assignment-statement (or SQL PL assignment-statement)
•A RETURN statement in an SQL scalar function
Example 6-6 shows a sample CREATE array data type followed by a sample UDF where the array data type is used in a RETURN statement of an SQL scalar function. This sample UDF uses the ARRAY_AGG (aggregate) function and returns an array data type to the caller.
Example 6-6 Array data type create statement and sample use case in a scalar function
CREATE TYPE PHONELIST AS CHAR(4) ARRAY[];
 
CREATE FUNCTION PHONELIST_UDF (LOWSAL DECIMAL(9,2))
RETURNS PHONELIST
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
RETURN
(SELECT ARRAY_AGG(PHONENO ORDER BY SALARY)
FROM DSN81110.EMP WHERE SALARY > LOWSAL)
6.5.2 ARRAY_DELETE
The ARRAY_DELETE function deletes elements from an array. This function can be specified only in the following specific contexts:
•As a source value for a SET assignment-statement (or SQL PL assignment-statement) or VALUES INTO statement.
•As the value to be returned in a RETURN statement in an SQL scalar function.
6.5.3 ARRAY_FIRST
The ARRAY_FIRST function returns the minimum array index value of an array. The data type of the result is the data type of the array index, which is INTEGER for an ordinary array. If array expression is not null and the array is not empty, the value of the result is the minimum array index value, which is 1 for an ordinary array.
6.5.4 ARRAY_LAST
The ARRAY_LAST function returns the maximum array index value of an array. The data type of the result is the data type of the array index, which is INTEGER for an ordinary array. If the array expression is not null and the array is not empty, the value of the result is the maximum array index value, which is the cardinality of the array for an ordinary array.
6.5.5 ARRAY_NEXT
The ARRAY_NEXT function returns the next larger array index value, relative to a specified array index value.
6.5.6 ARRAY_PRIOR
The ARRAY_PRIOR function returns the next smaller array index value, relative to a specified array index value.
6.5.7 CARDINALITY
The CARDINALITY function returns the number of elements in an array. The data type of the result is BIGINT.
The result of the CARDINALITY function is as follows:
•For an ordinary array, the result is the highest array index for which the array has an assigned element. Elements that have been assigned the null value are considered to be assigned elements.
•For an associative array, the result is the actual number of unique array index values that are defined in array-expression.
•For an empty array, the result is 0.
6.5.8 MAX_CARDINALITY
The MAX_CARDINALITY function returns the maximum number of elements that an array can contain. This value is the cardinality that was specified in the CREATE TYPE statement for an ordinary array type.
The result of the MAX_CARDINALITY function is as follows:
•For an ordinary array, the result is the maximum number of elements that an array can contain.
•For an associative array, the result is the null value.
6.5.9 TRIM_ARRAY
The TRIM_ARRAY function deletes elements from the end of an ordinary array. It can be invoked only in the following contexts:
•A source value for SET assignment-statement or SQL PL assignment-statement, or a VALUES INTO statement
•The value that is returned in a RETURN statement in an SQL scalar function
6.5.10 UNNEST (table function)
Treat an array like a table to fetch data (that is, rows) from the array. You can use the UNNEST construct (collection-derived table), which returns a result table that contains a row for each element of an array. For example, using the UNNEST operation, you can retrieve a list of the phone numbers from the array returned by the PHONELIST_UDF as shown in Example 6-7.
Example 6-7 Sample invocation of UNNEST table function
SELECT * FROM dsn81110.emp WHERE phoneno = ANY (SELECT T.PHONE FROM unnest(phonelist_udf(30000)) AS T(PHONE))
The WITH ORDINALITY clause in Example 6-8 indicates that the result table is to include an additional column that reflects the ordinal position of each array element within the array. This additional column is the last column of the result table from the UNNEST operation.
Example 6-8 Sample invocation of UNNEST table function with ORDINALITY clause
SELECT T.ARRAY_IX_SEQ, T.PHONE
FROM UNNEST(PHONELIST_UDF(20000)) WITH ORDINALITY AS T(PHONE, ARRAY_IX_SEQ);
In Example 6-8, the correlation clause following the WITH ORDINALITY clause, specifies that the additional column is named ARRAY_IX_SEQ, and the array element column is named PHONE. These column names can be explicitly referenced in the select list of the query.
6.5.11 Arrays in MERGE statement
With the introduction of limited support for arrays, an array value (that is, “whole array”) can be specified in MERGE statements in a context that allows for an array value. For example, an array can be referenced in a predicate of a merge statement.
Note that a value to be assigned to a column with a MERGE statement must not be an array value, because a column cannot be defined as an array. However, an array value can be referenced in an expression that provides the source value to be assigned, as long as the result of the expression is assignable to the target column.
6.6 SET CURRENT APPLICATION COMPATIBILITY
This special register is applicable only to dynamic SQL. CURRENT APPLICATION COMPATIBILITY specifies the DB2 release level that the dynamic SQL is compatible with. The data type is VARCHAR(10). A routine environment cannot inherit this special register value from the caller's environment, even if the routine was created with the INHERIT SPECIAL REGISTER option.
You can change the value of this special register by executing the SET CURRENT APPLICATION COMPATIBILITY statement as shown in Example 6-9.
Example 6-9 APPLICATION COMPATIBILITY - Setting the special register values
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1'
SET CURRENT APPLICATION COMPATIBILITY = 'V10R1'
As shown in Example 6-9, possible values for the APPLICATION COMPATIBILITY special register are V10R1 and V11R1.
•V10R1: Dynamic SQL statements are executed as they were in V10R1.
•V11R1: Dynamic SQL statements are executed with the new functionality of V11R1, which is not necessarily compatible with V10R1.
The value of V11R1 cannot be specified until DB2 11 for z/OS is in New Function Mode (NFM). By the same token, new options offered in V11 can be used in dynamic SQL statements only when this special register value has the V11R1 value (or implicitly inherited it from the DSNZPARM value default). For example, the SYSTIMESENSITIVE, BUSTIMESENSITIVE, and ARCHIVESENSITIVE options cannot be explicitly specified with the value of YES in NFM if this special register is set to V10R1. However, the IFCID 376 trace record can be used to identify those applications that can observe V11 incompatible changes when this special register is set to V11R1.
Additional details about application compatibility feature is discussed in 12.7, “Controlling application compatibility” on page 373.
See also DB2 11 for z/OS Installation and Migration, SC19-4056.
6.7 Temporal special registers
The SET statement in Example 6-10 sets the CURRENT TEMPORAL BUSINESS_TIME special register to last month. Assume that temporal table is an application-period temporal table with a BUSINESS_TIME period. The setting of the special register CURRENT TEMPORAL BUSINESS_TIME affects the update of temporal table that follows.
Example 6-10 shows sample set statements.
Example 6-10 Sample SET CURRENT TEMPORAL BUSINESS_TIME statement
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1'
SET CURRENT TEMPORAL BUSINESS_TIME = CURRENT TIMESTAMP - 1 MONTH;
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP + 5 DAYS;
Example 6-11 shows a setting of compatibility.
Example 6-11 SET CURRENT TEMPORAL SYSTEM_TIME to past time period
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
WHERE EMPNO = 10;
---------+---------+---------+---------+-----
EMPNO BONUS
---------+---------+---------+---------+-----
000010 1000.00
 
 
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 2 DAYS
WHERE EMPNO = 10;
---------+---------+---------+---------+-----
EMPNO BONUS
---------+---------+---------+---------+-----
000010 1000.00
 
 
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 2 DAYS;
UPDATE DB2R4.EMP_TEMPORAL SET BONUS = 777 WHERE EMPNO = 10;
--DSNE615I NUMBER OF ROWS AFFECTED IS 1
 
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP
WHERE EMPNO = 10;
---------+---------+---------+---------+---------
EMPNO BONUS
---------+---------+---------+---------+---------
000010 777.00
 
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 5 DAYS
WHERE EMPNO = 10;
---------+---------+---------+---------+---------+
EMPNO BONUS
---------+---------+---------+---------+---------+
000010 1000.00
 
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 2 DAYS
WHERE EMPNO = 10;
---------+---------+---------+---------+--------
EMPNO BONUS
---------+---------+---------+---------+--------
000010 1000.00
 
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 2 DAYS;
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
WHERE EMPNO = 10;
---------+---------+
EMPNO BONUS
---------+---------+
000010 777.00
Example 6-12 is not exactly working like a time machine.
Example 6-12 SET CURRENT TEMPORAL SYSTEM_TIME to future time period
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP + 2 MONTHS;
UPDATE DB2R4.EMP_TEMPORAL SET BONUS = 2222 WHERE EMPNO = 10;
--DSNE615I NUMBER OF ROWS AFFECTED IS 1
 
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP + 1 MONTH
WHERE EMPNO = 10
---------+---------+---------+---------+---------+
EMPNO BONUS
---------+---------+---------+---------+---------+
000010 2222.00
 
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP
WHERE EMPNO = 10
---------+---------+---------+---------+----
EMPNO BONUS
---------+---------+---------+---------+----
000010 2222.00
 
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 2 DAYS;
SELECT EMPNO, BONUS FROM DB2R4.EMP_TEMPORAL
WHERE EMPNO = 10
---------+---------+---------+---------+----
EMPNO BONUS
---------+---------+---------+---------+----
000010 2222.00
This behavior is not expected and can be explained by browsing the underlying tables directly, as shown in Table 6-2 and Table 6-3.
Table 6-2 EMP_TEMPORAL_HIST table contents
EMPNO
BONUS
START_TS
END_TS
10
1000
2013-08-26-00.21.00.753406376000
2013-09-09-00.59.10.635906699000
10
777
2013-09-09-00.59.10.635906699000
2013-09-09-01.36.34.073591227000
Table 6-3 EMP_TEMPORAL table contents
EMPNO
BONUS
START_TS
END_TS
10
2222
2013-09-09-01.36.34.073591227000
9999-12-30-00.00.00.000000000000
6.7.1 Scope of session-level special registers
For temporal query, it implicitly adds “FOR SYSTEM_TIME AS OF Current Temporal System_Time” for system-period temporal table, and “FOR BUSNESS_TIME AS OF Current Temporal Business_Time” for application-period temporal table.
6.7.2 SYSTIMESENSITIVE and BUSTIMESENSITIVE
Two new bind options are available in DB2 11 for z/OS so that applications can choose whether to be sensitive to the Current Temporal System_Time and Current Temporal Business_Time special registers.
Default is YES for these two BIND parameters.
6.8 Temporal support on VIEWs
In DB2 11 for z/OS, the PERIOD specification is extended to CREATE VIEW statements to provide the temporal support to users of views. The period specification is ignored if the view does not reference relevant type of temporal tables.
Example 6-13 contains the DDL used to define the temporal tables referenced in this section.
Example 6-13 Sample temporal table DDL statements
CREATE TABLE DB2R4.EMP_TEMPORAL
(EMPNO CHAR(6) FOR SBCS DATA NOT NULL,
FIRSTNME VARCHAR(12) FOR SBCS DATA NOT NULL,
MIDINIT CHAR(1) FOR SBCS DATA NOT NULL,
LASTNAME VARCHAR(15) FOR SBCS DATA NOT NULL,
WORKDEPT CHAR(3) FOR SBCS DATA WITH DEFAULT NULL,
PHONENO CHAR(4) FOR SBCS DATA WITH DEFAULT NULL,
HIREDATE DATE WITH DEFAULT NULL,
JOB CHAR(8) FOR SBCS DATA WITH DEFAULT NULL,
EDLEVEL SMALLINT WITH DEFAULT NULL,
SEX CHAR(1) FOR SBCS DATA WITH DEFAULT NULL,
BIRTHDATE DATE WITH DEFAULT NULL,
SALARY DECIMAL(9, 2) WITH DEFAULT NULL,
BONUS DECIMAL(9, 2) WITH DEFAULT NULL,
COMM DECIMAL(9, 2) WITH DEFAULT NULL,
START_TS TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW BEGIN,
END_TS TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW END,
TRANS_ID TIMESTAMP (12) WITHOUT TIME ZONE
GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME (START_TS, END_TS),
CONSTRAINT EMPNO
PRIMARY KEY (EMPNO))
PARTITION BY (EMPNO ASC)
(PARTITION 1 ENDING AT ('099999'),
PARTITION 2 ENDING AT ('199999'),
PARTITION 3 ENDING AT ('299999'),
PARTITION 4 ENDING AT ('499999'),
PARTITION 5 ENDING AT ('999999'))
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
COMMIT;
 
 
CREATE TABLE DB2R4.EMP_TEMPORAL_HIST
(EMPNO CHAR(6) FOR SBCS DATA NOT NULL,
FIRSTNME VARCHAR(12) FOR SBCS DATA NOT NULL,
MIDINIT CHAR(1) FOR SBCS DATA NOT NULL,
LASTNAME VARCHAR(15) FOR SBCS DATA NOT NULL,
WORKDEPT CHAR(3) FOR SBCS DATA WITH DEFAULT NULL,
PHONENO CHAR(4) FOR SBCS DATA WITH DEFAULT NULL,
HIREDATE DATE WITH DEFAULT NULL,
JOB CHAR(8) FOR SBCS DATA WITH DEFAULT NULL,
EDLEVEL SMALLINT WITH DEFAULT NULL,
SEX CHAR(1) FOR SBCS DATA WITH DEFAULT NULL,
BIRTHDATE DATE WITH DEFAULT NULL,
SALARY DECIMAL(9, 2) WITH DEFAULT NULL,
BONUS DECIMAL(9, 2) WITH DEFAULT NULL,
COMM DECIMAL(9, 2) WITH DEFAULT NULL,
START_TS TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL,
END_TS TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL,
TRANS_ID TIMESTAMP (12) WITHOUT TIME ZONE
WITH DEFAULT NULL )
PARTITION BY (EMPNO ASC)
(PARTITION 1 ENDING AT ('099999'),
PARTITION 2 ENDING AT ('199999'),
PARTITION 3 ENDING AT ('299999'),
PARTITION 4 ENDING AT ('499999'),
PARTITION 5 ENDING AT ('999999'))
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
COMMIT;
 
ALTER TABLE
EMP_TEMPORAL
ADD VERSIONING USE HISTORY TABLE
EMP_TEMPORAL_HIST ;
Example 6-14 shows a sample CREATE VIEW statement defined on a temporal table and a sample SELECT statement that can query the view as of certain point in time.
Example 6-14 Sample VIEW statement on a temporal table along with a temporal Query
CREATE VIEW v0 (EMPNO, SALARY, COMM)
AS SELECT EMPNO, SALARY, COMM
FROM EMP_TEMPORAL ;
 
-- The following is a sample temporal query on the above view
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SELECT * FROM V0
FOR SYSTEM_TIME AS OF TIMESTAMP '2013-08-25 23:55:00' ;
For illustration purposes, if the bi-temporal table contains rows with the START_TS and END_TS column values shown in Table 6-4, none of the rows are picked up by the query coded in the Example 6-14. However, the result set includes all the rows if the same query is run without the FOR SYSTEM_TIME AS OF TIMESTAMP clause.
Table 6-4 Sample time stamp values
START_TS
END_TS
2013-08-26-00.21.00.753406376000
9999-12-30-00.00.00.000000000000
The second sample SELECT statement as shown in Example 6-15 returns all the rows for which the timestamp value specified in the AS OF clause (that is, 2013-10-10 00:22:00) lies between the START_TS and END_TS values on the rows involved. For example, if all the rows pertaining to the view had the same set of values tabulated in Table 6-4, all the rows are returned by the query coded in Example 6-15.
Example 6-15 Selecting with AS OF
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SELECT * FROM V0
FOR SYSTEM_TIME AS OF TIMESTAMP '2013-10-10 00:22:00' ;
Both the sample temporal queries are coded with the SET CURRENT APPLICATION COMPATIBILITY = 'V11R1 statement to emphasize the fact that this is a new function and it will not work if your Application Compatibility level is set to V10R1.
 
Note: DB2 11 for z/OS removes DB2 10 restrictions that period specification and period clause can only be specified with base table references and appropriate type of temporal tables.
6.9 DGTT
The DECLARED GLOBAL TEMPORARY TABLE statement now includes a clause to specify logging behavior. The logging attribute for the DGTT is at the table level as opposed to the logging attribute that is at the table space level for base tables.
A DGTT has the following different logging options:
•LOGGED, which is the default and the current behavior. In this case, DB2 logs all changes and during ROLLBACK or ROLLBACK TO SAVEPOINT, the changes to the DGTT are undone.
•NOT LOGGED ON ROLLBACK DELETE ROWS, which specifies no logging and during ROLLBACK or ROLLBACK TO SAVEPOINT, all rows in the DGTT are deleted if any change was made in the duration.
•NOT LOGGED ON ROLLBACK PRESERVE ROWS, which specifies no logging and during ROLLBACK or ROLLBACK TO SAVEPOINT, the rows in the DGTT will be preserved as they are.
In the case of an error situation during an SQL statement, where an SQLCODE or message is issued, if an update was made to a DGTT and LOGGED is specified, the changes to the DGTT are undone.
In the case of an error situation during an SQL statement, where an SQLCODE or message is issued, if an update was made to a DGTT and NOT LOGGED is specified, all rows in that DGTT are deleted, regardless of the DELETE/PRESERVE ROWS qualification.
DB2 can provide full incremental bind avoidance when used in a loop by switching to short prepare with RELEASE(DEALLOCATE).
6.10 CUBE, ROLLUP and GROUPING SETS
Grouping-sets and super-groups are two new options under GROUP BY clause (of the SELECT statement). A super-group stands for ROLLUP, CUBE or grand-total clause. ROLLUP is helpful in providing subtotaling along a hierarchical dimension such as time or geography. CUBE is helpful in queries that aggregate based on columns from multiple dimensions.
With support for rollup, cube, and grouping-sets specifications, the SQL coding complexity can be reduced greatly and the SQL performance can be improved dramatically.
6.10.1 GROUPING SETS
The GROUPING SETS option can be thought of as the union of two or more groups of rows into a single result set. It is logically equivalent to the union of multiple subselects with the group by clause in each subselect corresponding to one grouping set. This is similar to the DB2 for Linux, UNIX, and Windows and DB2 for IBM System i® support for grouping-sets and super-group specifications.
Example 6-16 shows a sample SQL statement by using the GROUP BY clause with the GROUPING SETS option. Figure 6-4 shows the result set of this SQL statement.
Example 6-16 Sample SQL statement utilizing GROUP BY GROUPING SETS
SELECT WORKDEPT, EDLEVEL, SEX, SUM(SALARY) as SUM_SALARY, AVG(SALARY) as AVG_SALARY, COUNT(*) as COUNT
FROM DSN81110.EMP WHERE SALARY > 20000
GROUP BY GROUPING SETS (WORKDEPT, EDLEVEL, SEX)
The result set is logically equivalent to the union all of three subselects with the group by clause in each subselect corresponding to one column each from the three columns on the grouping sets specification (while the other two column values are shown as NULLs).
Figure 6-4 Result of sample query using GROUPING SETS (WORKDEPT, EDLEVEL, SEX)
6.10.2 ROLLUP
A ROLLUP grouping is an extension to the GROUP BY clause that produces a result set containing sub-total rows in addition to the “regular” grouped rows. Subtotal rows are “super-aggregate” rows that contain further aggregates whose values are derived by applying the same column functions that were used to obtain the grouped rows. These rows are called sub-total rows, because that is their most common use; however, any column function can be used for the aggregation.
A ROLLUP grouping is a series of grouping-sets. The general specification of a ROLLUP with n elements:
GROUP BY ROLLUP(C1,C2,...,Cn-1,Cn)
is equivalent to
GROUP BY GROUPING SETS((C1,C2,...,Cn-1,Cn),
(C1,C2,...,Cn-1),
...
(C1,C2),
(C1),
() )
For example, SUM and AVG are used in Example 6-17 is similar to Example 6-16 on page 121.
Example 6-17 Sample ROLLUP construct
SELECT WORKDEPT, EDLEVEL, SEX, SUM(SALARY) as SUM_SALARY,
AVG(SALARY) as AVG_SALARY, COUNT(*) as COUNT
FROM DSN81110.EMP WHERE SALARY > 20000
GROUP BY ROLLUP (WORKDEPT, EDLEVEL, SEX)
Example 6-18 shows the result set for the query in Example 6-17.
Example 6-18 Sample ROLLUP result set
WORKDEPT EDLEVEL SEX SUM_SALARY AVG_SALARY COUNT
-------- ------- ---- ---------- -------------- -----
A00 14 M 29250.00 29250.00000000 1
A00 18 F 52750.00 52750.00000000 1
A00 19 M 46500.00 46500.00000000 1
B01 18 M 41250.00 41250.00000000 1
C01 16 F 23800.00 23800.00000000 1
C01 18 F 28420.00 28420.00000000 1
C01 20 F 38250.00 38250.00000000 1
D11 16 M 130400.00 26080.00000000 5
D11 17 F 43590.00 21795.00000000 2
D11 18 F 29840.00 29840.00000000 1
D21 14 M 22180.00 22180.00000000 1
D21 15 F 27380.00 27380.00000000 1
D21 16 F 36170.00 36170.00000000 1
D21 17 M 28760.00 28760.00000000 1
E01 16 M 40175.00 40175.00000000 1
E11 16 F 29750.00 29750.00000000 1
E11 17 F 26250.00 26250.00000000 1
E21 14 M 51520.00 25760.00000000 2
E21 16 M 23840.00 23840.00000000 1
A00 14 NULL 29250.00 29250.00000000 1
A00 18 NULL 52750.00 52750.00000000 1
A00 19 NULL 46500.00 46500.00000000 1
B01 18 NULL 41250.00 41250.00000000 1
C01 16 NULL 23800.00 23800.00000000 1
C01 18 NULL 28420.00 28420.00000000 1
C01 20 NULL 38250.00 38250.00000000 1
D11 16 NULL 130400.00 26080.00000000 5
D11 17 NULL 43590.00 21795.00000000 2
D11 18 NULL 29840.00 29840.00000000 1
D21 14 NULL 22180.00 22180.00000000 1
D21 15 NULL 27380.00 27380.00000000 1
D21 16 NULL 36170.00 36170.00000000 1
D21 17 NULL 28760.00 28760.00000000 1
E01 16 NULL 40175.00 40175.00000000 1
E11 16 NULL 29750.00 29750.00000000 1
E11 17 NULL 26250.00 26250.00000000 1
E21 14 NULL 51520.00 25760.00000000 2
E21 16 NULL 23840.00 23840.00000000 1
A00 NULL NULL 128500.00 42833.33333333 3
B01 NULL NULL 41250.00 41250.00000000 1
C01 NULL NULL 90470.00 30156.66666666 3
D11 NULL NULL 203830.00 25478.75000000 8
D21 NULL NULL 114490.00 28622.50000000 4
E01 NULL NULL 40175.00 40175.00000000 1
E11 NULL NULL 56000.00 28000.00000000 2
E21 NULL NULL 75360.00 25120.00000000 3
NULL NULL NULL 750075.00 30003.00000000 25
Note that the n elements of the ROLLUP translate to n+1 grouping sets. Note also that the order in which the grouping-expressions is specified is significant for ROLLUP. For example:
GROUP BY ROLLUP(a,b)
Is equivalent to
GROUP BY GROUPING SETS((a,b),
(a),
() )
While
GROUP BY ROLLUP(b,a)
Is the same as
GROUP BY GROUPING SETS((b,a),
(b),
() )
The SQL code in Example 6-19 is the equivalent of Example 6-17 on page 122.
Example 6-19 Selecting with grouping sets
SELECT WORKDEPT, EDLEVEL, SEX, SUM(SALARY) as SUM_SALARY, AVG(SALARY) AS AVG_SALARY, count(*) as COUNT
FROM DSN81110.EMP WHERE SALARY > 20000
GROUP BY GROUPING SETS ((WORKDEPT, EDLEVEL, SEX),(WORKDEPT, EDLEVEL), (WORKDEPT),())
Grand total for the result of this query is the last row on the query result set shown in Example 6-18 on page 122, which can also be identified by the row containing null values for all the three columns on the ROLLUP clause (that is, WORKDEPT, EDLEVEL, SEX).
6.10.3 CUBE
A CUBE grouping is an extension to the GROUP BY clause that produces a result set that contains all the rows of a ROLLUP aggregation and, in addition, contains crosstabulation rows. Cross-tabulation rows are additional super-aggregate rows that are not part of an aggregation with sub-totals.
Like a ROLLUP, a CUBE grouping can also be thought of as a series of grouping-sets. In the case of a CUBE, all permutations of the cubed grouping-expression-list are computed along with the grand total. Therefore, the n elements of a CUBE translate to 2**n (2 to the power n) grouping-sets. For example, a specification of
GROUP BY CUBE(a,b,c)
is equivalent to
GROUP BY GROUPING SETS((a,b,c),
(a,b),
(a,c),
(b,c),
(a),
(b),
(c),
() )
Notice that the three elements of the CUBE translate to eight grouping sets. The order of specification of elements does not matter for CUBE.
Example 6-20 shows a sample SQL statement using CUBE in the GROUP BY clause with similar code used in the ROLLUP description in Example 6-17 on page 122.
Example 6-20 Sample SQL statement using CUBE construct in a GROUP BY clause
SELECT WORKDEPT, EDLEVEL, SEX, SUM(SALARY) as SUM_SALARY, AVG(SALARY) AS AVG_SALARY, count(*) as COUNT
FROM DSN81110.EMP WHERE SALARY > 20000
GROUP BY CUBE (WORKDEPT, EDLEVEL, SEX);
Example 6-21 shows the result set for the query in Example 6-20.
Example 6-21 Result set from the sample CUBE construct
WORKDEPT EDLEVEL SEX SUM_SALARY AVG_SALARY COUNT
-------- ------- ---- ---------- -------------- -----
A00 14 M 29250.00 29250.00000000 1
A00 18 F 52750.00 52750.00000000 1
A00 19 M 46500.00 46500.00000000 1
B01 18 M 41250.00 41250.00000000 1
C01 16 F 23800.00 23800.00000000 1
C01 18 F 28420.00 28420.00000000 1
C01 20 F 38250.00 38250.00000000 1
D11 16 M 130400.00 26080.00000000 5
D11 17 F 43590.00 21795.00000000 2
D11 18 F 29840.00 29840.00000000 1
D21 14 M 22180.00 22180.00000000 1
D21 15 F 27380.00 27380.00000000 1
D21 16 F 36170.00 36170.00000000 1
D21 17 M 28760.00 28760.00000000 1
E01 16 M 40175.00 40175.00000000 1
E11 16 F 29750.00 29750.00000000 1
E11 17 F 26250.00 26250.00000000 1
E21 14 M 51520.00 25760.00000000 2
E21 16 M 23840.00 23840.00000000 1
NULL 14 M 102950.00 25737.50000000 4
NULL 15 F 27380.00 27380.00000000 1
NULL 16 F 89720.00 29906.66666666 3
NULL 16 M 194415.00 27773.57142857 7
NULL 17 F 69840.00 23280.00000000 3
NULL 17 M 28760.00 28760.00000000 1
NULL 18 F 111010.00 37003.33333333 3
NULL 18 M 41250.00 41250.00000000 1
NULL 19 M 46500.00 46500.00000000 1
NULL 20 F 38250.00 38250.00000000 1
A00 NULL F 52750.00 52750.00000000 1
A00 NULL M 75750.00 37875.00000000 2
B01 NULL M 41250.00 41250.00000000 1
C01 NULL F 90470.00 30156.66666666 3
D11 NULL F 73430.00 24476.66666666 3
D11 NULL M 130400.00 26080.00000000 5
D21 NULL F 63550.00 31775.00000000 2
D21 NULL M 50940.00 25470.00000000 2
E01 NULL M 40175.00 40175.00000000 1
E11 NULL F 56000.00 28000.00000000 2
E21 NULL M 75360.00 25120.00000000 3
A00 14 NULL 29250.00 29250.00000000 1
A00 18 NULL 52750.00 52750.00000000 1
A00 19 NULL 46500.00 46500.00000000 1
B01 18 NULL 41250.00 41250.00000000 1
C01 16 NULL 23800.00 23800.00000000 1
C01 18 NULL 28420.00 28420.00000000 1
C01 20 NULL 38250.00 38250.00000000 1
D11 16 NULL 130400.00 26080.00000000 5
D11 17 NULL 43590.00 21795.00000000 2
D11 18 NULL 29840.00 29840.00000000 1
D21 14 NULL 22180.00 22180.00000000 1
D21 15 NULL 27380.00 27380.00000000 1
D21 16 NULL 36170.00 36170.00000000 1
D21 17 NULL 28760.00 28760.00000000 1
E01 16 NULL 40175.00 40175.00000000 1
E11 16 NULL 29750.00 29750.00000000 1
E11 17 NULL 26250.00 26250.00000000 1
E21 14 NULL 51520.00 25760.00000000 2
E21 16 NULL 23840.00 23840.00000000 1
NULL NULL F 336200.00 30563.63636363 11
NULL NULL M 413875.00 29562.50000000 14
NULL 14 NULL 102950.00 25737.50000000 4
NULL 15 NULL 27380.00 27380.00000000 1
NULL 16 NULL 284135.00 28413.50000000 10
NULL 17 NULL 98600.00 24650.00000000 4
NULL 18 NULL 152260.00 38065.00000000 4
NULL 19 NULL 46500.00 46500.00000000 1
NULL 20 NULL 38250.00 38250.00000000 1
A00 NULL NULL 128500.00 42833.33333333 3
B01 NULL NULL 41250.00 41250.00000000 1
C01 NULL NULL 90470.00 30156.66666666 3
D11 NULL NULL 203830.00 25478.75000000 8
D21 NULL NULL 114490.00 28622.50000000 4
E01 NULL NULL 40175.00 40175.00000000 1
E11 NULL NULL 56000.00 28000.00000000 2
E21 NULL NULL 75360.00 25120.00000000 3
NULL NULL NULL 750075.00 30003.00000000 25
In Example 6-20, CUBE (WORKDEPT, EDLEVEL, SEX) and CUBE (EDLEVEL, WORKDEPT, SEX) yield the same result sets. The use of the word same applies to content of the result set, not to its order.
6.10.4 Grand total
Both CUBE and ROLLUP return a row that is the overall (grand total) aggregation, which can be separately specified with empty parentheses within the GROUPING SET clause. It can also be specified directly in the GROUP BY clause, although there is no effect on the result of the query.
6.10.5 Grouping expression
When used in conjunction with grouping-sets and super-groups, the GROUPING function returns a value that indicates whether a row returned in a GROUP BY result is a row that is generated by a grouping set that excludes the column represented by expression. The result of the function is a small integer value, such as 1 or 0.
For details, see:
6.11 ALTER TABLE DROP COLUMN
This function drops the identified column from the table. Any privileges that are associated with the column are revoked.
A column cannot be dropped if any of the following conditions are true:
•The containing table space is not a universal table space.
•The table is a created global temporary table.
•The table is a system-period temporal table.
•The table is a history table.
•The table is an archive-enabled table.
•The table is an archive table.
•The table has an edit procedure or a validation exit procedure.
•The table contains check constraints.
•The table is a materialized query table.
•The table is referenced in a materialized query table definition.
•The column is defined as a security label column.
•The column is an XML column.
•The column is a DOCID column.
•The column is a hidden ROWID column.
•The column is defined as ROWID GENERATED BY DEFAULT, and the table contains a hidden ROWID column.
•The column is a ROWID column on which there is a dependent LOB column.
•The column is part of the table partitioning key.
•The column is part of the hash key.
•All of the remaining columns in the table are hidden.
•A view that is dependent on the table has INSTEAD OF triggers.
•A trigger is defined on the table.
•Any of the following objects are dependent on the table:
 – Extended indexes
 – Row permissions
 – Column masks
 – Inline SQL table functions
ALTER TABLE DROP COLUMN is considered a pending definition change, at the time that the ALTER statement is executed, semantic validation and authorization checking are performed as usual. However, the drop is not applied to the current definition or data at the time of the ALTER (that is, catalog and data are untouched). An entry is recorded in the SYSIBM.SYSPENDINGDDL catalog table for the pending drop column, and the table space is placed in an advisory REORG-pending (AREOR) state.
6.12 LIKE_BLANK_INSIGNIFICANT DSNZPARM
The LIKE_BLANK_INSIGNIFICANT DSNZPARM value specifies whether blanks are significant when applying the LIKE predicate to a string. If set, the blank insignificant behavior applies.
This DSNZPARM value provides a new behavior for the LIKE predicate that treats trailing blanks within fixed length character strings as insignificant. This behavior is “more compatible” with the results for variable length strings.
This system parameter is off after a fresh install of DB2 for z/OS. If the DB2 system is migrated to DB2 11, the LIKE_BLANK_INSIGNIFICANT behavior is disabled by default. The system parameter can be enabled in conversion mode (CM).
This option can significantly impact the behavior of SQL statements if you have a LIKE predicate in your SQL statement and if the column referred in the LIKE predicate includes undesirable trailing blanks.
Before the LIKE predicate is applied, any trailing blanks in a CHARACTER or GRAPHIC column are stripped to the last non-blank character. If the column contains all blanks, the blank in character position 1 is not stripped. After stripping occurs, the LIKE predicate is applied against the stripped column data. Example 6-22 illustrates this behavior.
Example 6-22 LIKE BLANK INSIGNIFICANT DSNZPARM behavior with trailing blanks
CREATE TABLE BINSIGNIFICANT (C1 CHAR(10));
INSERT INTO BINSIGNIFICANT VALUES(' AA '),
INSERT INTO BINSIGNIFICANT VALUES('A AA A'),
INSERT INTO BINSIGNIFICANT VALUES('AA '),
INSERT INTO BINSIGNIFICANT VALUES('AAA A '),
 
SELECT * FROM BINSIGNIFICANT
WHERE C1 LIKE '%AA'
 
---------+---------+---------+---------
C1
---------+---------+---------+---------
AA
AA
 
 
Trailing blanks note: Although trailing blanks in the column data are insignificant, trailing blanks in the LIKE predicate are significant.
Example 6-23 illustrates the situation when the LIKE predicate contains one or more of the “match any character” (usually underscore) in the last position. The “blank significant” (pre-V11 behavior), might have resulted in a match if the last character in the column contained the blank character. The “blank insignificant” behavior no longer results in a match when the column data contains trailing blanks (because the trailing blanks are being stripped during predicate evaluation).
Example 6-23 Sample LIKE predicate to illustrate the stripping of trailing blanks
SELECT C1
FROM BINSIGNIFICANT
WHERE C1 LIKE '%AA_';
The LIKE predicate in Example 6-23 does not even match the two fixed length strings it matched in Example 6-22, although there is a trailing blank in those two rows immediately after the string AA.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset