Anmerkung:
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
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.3 in the Natural Installation documentation.
Anmerkung:
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.
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:
A - H | I - R | S - Z |
---|---|---|
|
|
|
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' ...
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.
Anmerkung:
This is an extract of the chapter
Select
Expressions (Statements
documentation) and only describes the changes specific to Natural for DB2
Version 8.3.
, | ||||||||
* |
A selection
specifies the
columns of the result set tables to be selected.
Syntax Element Description:
Syntax Element | Description |
---|---|
ALL|DISTINCT |
Elimination of Duplicate Rows:
Duplicate rows are not automatically eliminated from the result of
a The alternative to |
scalar-expression |
Scalar Expression:
Instead of, or as well as, simple column names, a selection can also include general scalar expressions containing scalar operators and scalar functions which provide computed values (see also the section Scalar Expressions). Example: SELECT NAME, 65 - AGE FROM SQL-PERSONNEL ... |
AS |
The optional keyword AS introduces
a correlation-name
for a column.
|
correlation-name |
Correlation Name:
A The |
unpack-row |
See unpack-row below. |
* |
Asterisk Notation:
All columns of the result table are selected. Example: SELECT * FROM SQL-PERSONNEL, SQL-AUTOMOBILES ... |
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 |
, |
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
( , ) ( , ) A
grouping-set
is used to specify multiple grouping clauses in a single statement. Agrouping-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 aGROUP BY
clause where each expression corresponds to onegrouping-set
. Agrouping-set
is a single element or a list of elements delimited by parentheses. An element is either agrouping–expression
or asuper-group
. Agrouping-set
has the advantage that the groups are computed with a single pass over the base table.- super-group
(grouping-expression-list)
(grouping-expression-list( )
A
super-group
is a more complexgrouping-set
.A
grouping-expression-list
defines the number of elements used in aROLLUP
orCUBE
operation. Elements with multiplegrouping-expressions
are delimited by parentheses:
grouping-expression , (grouping-expression, )
ROLLUP
andCUBE
return a row which is the overall (grand total) aggregation. This can be specified with empty parentheses( )
within theGROUPING SETS
clause.
- ROLLUP
A
ROLLUP
grouping is like a series ofgrouping-sets
. In addition to the regular grouped rows, aROLLUP
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
withn
elements asGROUP 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 ofgrouping-sets
. In addition to theROLLUP
aggregation rows,CUBE
produces a result set that contains cross-tabulation rows. Cross-tabulation rows are additional "super-aggregate" rows. Thegrouping-expression-list
of aCUBE
computes all permutations along with the grand total. As a result, then
elements of aCUBE
translate to 2**ngrouping-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), ())
Anmerkung:
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.
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.