Natural for DB2 Version 8.3 - Documentation Updates

Note:
The documentation updates provided here only cover the changes specific to Natural for DB2 Version 8.3.

• Using Natural Statements and System Variables under Natural for DB2 Version 8.3

• Select Expressions under Natural for DB2 Version 8.3

• Dynamic and Static SQL Support under Natural for DB2 Version 8.3

For the changes in installation, see Installing Natural for DB2 Version 8.3 in the Natural Installation documentation.

Using Natural Statements and System Variables under Natural for DB2 Version 8.3

Note:
This is an extract of the chapter Using Natural SQL Statements and System Variables and only describes the changes specific to Natural for DB2 Version 8.3.

• scalar-function

• special-register

scalar-function

A scalar function is a built-in function that can be used in the construction of scalar computational expressions. Scalar functions are specific to DB2 and belong to the Natural SQL Extended Set.

The scalar functions Natural for DB2 supports are listed below in alphabetical order:

Each scalar function is followed by one or more scalar expressions in parentheses. The number of scalar expressions depends upon the scalar function. Multiple scalar expressions must be separated from one another by commas.

Example:

SELECT NAME
  INTO NAME
  FROM SQL-PERSONNEL
  WHERE SUBSTR ( NAME, 1, 3 ) = 'Fri'
       ...

special-register

With the exception of USER, the following special registers do not conform to standard SQL. They are specific to DB2 and belong to the Natural SQL Extended Set:

CURRENT APPLICATION COMPATIBILITY
CURRENT APPLICATION ENCODING SCHEME
CURRENT CLIENT_ACCNTG
CURRENT CLIENT_APPLNAME
CURRENT CLIENT_CORR_TOKEN
CURRENT CLIENT_USERID
CURRENT CLIENT_WRKSTNNAME
CURRENT DATE
CURRENT_DATE
CURRENT DEBUG MODE
CURRENT DECFLOAT ROUNDING MODE
CURRENT DEGREE
CURRENT FUNCTION PATH
CURRENT GET_ACCEL_ARCHIVE
CURRENT_LC_CTYPE
CURRENT LC_CTYPE
CURRENT LOCALE LC_CTYPE
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
CURRENT_MEMBER
CURRENT OPTIMIZATION HINT
CURRENT PACKAGE PATH
CURRENT PACKAGESET
CURRENT_PATH
CURRENT PRECISION
CURRENT QUERY ACCELERATION
CURRENT REFRESH AGE
CURRENT ROUTINE VERSION
CURRENT RULES
CURRENT SCHEMA
CURRENT SERVER
CURRENT SQLID
CURRENT TEMPORAL BUSINESS_TIME
CURRENT TEMPORAL_SYSTEM_TIME
CURRENT TIME
CURRENT_TIME
CURRENT TIMESTAMP
CURRENT TIMEZONE
CURRENT_TIMEZONE USER
SESSION TIME ZONE
SESSION_USER
USER

A reference to a special register returns a scalar value.

Using the command SET CURRENT SQLID, the creator name of a table can be substituted by the current SQLID. This enables you to access identical tables with the same table name but with different creator names.

Select Expressions under Natural for DB2 Version 8.3

Note:
This is an extract of the chapter Select Expressions (Statements documentation) and only describes the changes specific to Natural for DB2 Version 8.3.

• Selection

• GROUP BY Clause

Selection

	ALL DISTINCT				scalar-expression [[AS] correlation-name] unpack-row		,
					*

A selection specifies the columns of the result set tables to be selected.

Syntax Element Description:

SELECT NAME, 65 - AGE 
  FROM SQL-PERSONNEL 
  ...

SELECT * 
  FROM SQL-PERSONNEL, SQL-AUTOMOBILES 
  ...

unpack-row

UNPACK (scalar-expression) .* AS ({field-name data-type} , )

An unpack-row specifies a row of unpacked binary values that are returned when the SQL UNPACK function is invoked. The number of field-names and data-types must match the number of fields returned by the UNPACK function.

GROUP BY Clause

GROUP BY

grouping-expression grouping-set super-group

,

The GROUP BY clause specifies a grouping of the result table. The result of GROUP BY is a set of groups of rows. Within each group of more than one row, all values defining the group are equal.

grouping-expression

A grouping expression is a scalar expression that defines the grouping of a result set.

grouping-set

GROUPING SETS	(				grouping-expression super-group					,	)
			(		grouping-expression super-group		,	)

A grouping-set is used to specify multiple grouping clauses in a single statement. A grouping-set combines two or more groups of rows into a single result set. It is the same as the union of multiple select expressions with a GROUP BY clause where each expression corresponds to one grouping-set. A grouping-set is a single element or a list of elements delimited by parentheses. An element is either a grouping–expression or a super-group. A grouping-set has the advantage that the groups are computed with a single pass over the base table.

super-group

	ROLLUP CUBE	(grouping-expression-list) (grouping-expression-list
		( )

A super-group is a more complex grouping-set.

A grouping-expression-list defines the number of elements used in a ROLLUP or CUBE operation. Elements with multiple grouping-expressions are delimited by parentheses:

	grouping-expression		,
	(grouping-expression, )

Grand total ( ):

ROLLUP and CUBE return a row which is the overall (grand total) aggregation. This can be specified with empty parentheses ( ) within the GROUPING SETS clause.

ROLLUP

A ROLLUP grouping is like a series of grouping-sets. In addition to the regular grouped rows, a ROLLUP grouping produces a result set that contains subtotal rows. Subtotal rows are "super-aggregate" rows which contain additional aggregates. The aggregate values are retrieved with the same column functions that are used to obtain the regular grouped rows.

In general, you specify a ROLLUP with n elements as

GROUP BY ROLLUP (c1, c2, ..., cn-1, cn)

which is the equivalent of:

GROUP BY GROUPING SETS ((c1, c2, ..., cn-1, cn),

                       (c1, c2, ..., cn-1),

                       ...

                       (c1, c2),

                       (c1),

                       ( ))

CUBE

A CUBE grouping is like a series of grouping-sets. In addition to the ROLLUP aggregation rows, CUBE produces a result set that contains cross-tabulation rows. Cross-tabulation rows are additional "super-aggregate" rows. The grouping-expression-list of a CUBE computes all permutations along with the grand total. As a result, the n elements of a CUBE translate to 2**n grouping-sets. For example:

GROUP BY CUBE (a, b, c)

is the equivalent of:

GROUP BY GROUPING SETS ((a, b, c),

                       (a, b),

                       (a, c),

                       (b, c),

                       (a),

                       (b),

                       (c),

                       ())

Dynamic and Static SQL Support under Natural for DB2 Version 8.3

Note:
This is an extract of the chapter Dynamic and Static SQL Support and only describes the changes specific to Natural for DB2 Version 8.3.

Plan Switching by CICS/DB2 Exit Routine

If #SWITCH-BY-TRANSACTION-ID is set to FALSE, the desired plan name is written to a temporary storage queue for a CICS/DB2 exit routine specified as PLANExit attribute of a DB2ENTRY or of the DB2CONN definition, the NATPLAN program must be invoked before the first DB2 access. Natural for DB2 provides NDBUEXT as CICS DB2 plan selection exit program. For additional information on CICS/DB2 exit routines, refer to the relevant IBM literature.

The name of the temporary storage queue is PLANsssstttt, where ssss is the remote or local CICS system identifier and tttt the CICS terminal identifier.

When running in a CICSplex environment, the CICS temporary storage queue PLANsssstttt containing the plan name must be defined with TYPE=SHARED or TYPE=REMOTE in a CICS TST.

For each new DB2 unit of recovery, the appropriate plan selection exit routine is automatically invoked. This exit routine reads the temporary storage record and uses the contained plan name for plan selection.

When no temporary storage record exists for the Natural session, a default plan name, contained in the plan exit, can be used. If no plan name is specified by the exit, the name of the plan used is the same as the name of the static program (DBRM) issuing the SQL call. If no such plan name exists, an SQL error results.