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.

For the changes in installation, see Installing Natural for DB2 Version 8.3.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

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

ABS
ABSVAL
ACOS
ADD_MONTHS
ASIN
ASCII
ASCII_CHR
ASCII_STR
ATAN
ATAN2
ATANH
BIGINT
BINARY
BITAND
BITANDNOT
BITNOT
BITOR
BITXOR
BLOB
CCSID_ENCODING
CEIL
CEILING
CHAR
CHARACTER_LENGTH
CLOB
COALESCE
COLLATION_KEY
COMPARE_DECFLOAT
CONCAT
CONTAINS
COS
COSH
DATE
DAY
DAYOFMONTH
DAYOFWEEK
DAYOFWEEK_ISO
DAYOFYEAR
DAYS
DBCLOB
DEC
DECFLOAT
DEDCFLOAT_FORMAT
DECFLOAT_SORTKEY
DECIMAL
DECODE
DECRYPT_BIT
DECRYPT_CHAR
DECRYPT_DB
DEGREES
DIFFERENCE
DIGITS
DOUBLE
DOUBLE_PRECISION
DSN_XMLVALIDATE
EBCDIC_CHR
EBCDIC_STR
ENCRYPT_TDES
ENCRYPT
EXP
EXTRACT
FLOAT
FLOOR
GRAPHIC
GENERATE_UNIQUE
GETHINT
GETVARIABLE
HEX
HOUR

IDENTITY_VAL_LOCAL
IFNULL
INSERT
INTEGER
JULIAN_DAY
LAST_DAY
LCASE
LEFT
LENGTH
LN
LOCATE
LOCATE_IN_STRING
LOG
LOG10
LOWER
LPAD
LTRIM
MAX
MICROSECOND
MIDNIGHT_SECONDS
MIN
MINUTE
MOD
MONTH
MONTHS_BETWEEN
MQPUBLISH
MQPUBLISHXML
MQREAD
MQREADCLOB
MQREADXML
MQRECEIVE
MQRECEIVECLOB
MQRECEIVEXML
MQSEND
MQSENDXML
MQSENDXMLFILE
MQSENDXMLFILECLOB
MQSUBSCRIBE
MQUNSUBSCRIBE
MULTIPLY_ALT
NEXT_DAY
NORMALIZE_DECFLOAT
NORMALIZE_STRING
NULLIF
NVL
OVERLAY
PACK
POSSTR
POWER
QUANTIZE
QUARTER
RADIANS
RAISE_ERROR
RAND
REAL
REPEAT
REPLACE
RID
RIGHT
ROUND
ROUND_TIMESTAMP
ROWID
RPAD
RTRIM

SCORE
SECOND
SIGN
SIN
SINH
SMALLINT
SOAPHTTPC
SOAPHTTPV
SOAPHTTPNC
SOAPHTTPNV
SOUNDEX
SPACE
SQRT
STRIP
SUBSTR
SUBSTRING
TAN
TANH
TIME
TIMESTAMP
TIMESTAMPADD
TIMESTAMPDIFF
TIMESTAMP_FORMAT
TIMESTAMP_ISO
TIMESTAMP_TZ
TO_CHAR
TO_DATE
TO_NUMBER
TOTALORDER
TRANSLATE
TRIM
TRUNC
TRUNC_TIMESTAMP
TRUNCATE
UCASE
UNICODE
UNICODE_STR
UNISTR
UNPACK
UPPER
VALUE
VARBINARY
VARCHAR
VARCHAR_FORMAT
VARGRAPHIC
WEEK
WEEK_ISO
XMLATTRIBUTES
XMLCONCAT
XMLCOMMENT
XMLDOCUMENT
XMLELEMENT
XMLFOREST
XMLMODIFY
XMLNAMESPACES
XMLPARSE
XMLPI
XMLQUERY
XMLSERIALIZE
XMLTEXT
XMLXSROBJECTID
YEAR

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

ALL
DISTINCT

../graphics/cbo3b.gif

scalar-expression [[AS] correlation-name]
unpack-row

, ../graphics/dot3.gif

../graphics/cbc3b.gif

  *  

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 select-expression. To request this, specify the keyword DISTINCT.

The alternative to DISTINCT is ALL. ALL is assumed if neither is specified.

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 correlation-name can be assigned to a scalar-expression as an alias name for a result column.

The correlation-name need not be unique. If no correlation-name is specified for a result column, the corresponding column-name will be used (if the result column is derived from a column name; if not, the result table will have no name). The name of a result column may be used, for example, as column name in the ORDER BY clause of a SELECT statement.

unpack-row See unpack-row below.
*
Asterisk Notation:

All columns of the result table are selected.

Example:

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

unpack-row

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

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

../graphics/sbo3b.gif

GROUP BY

../graphics/cbo3.gif

grouping-expression
grouping-set
super-group

../graphics/cbc3.gif

, ../graphics/dot3.gif

../graphics/sbc3b.gif

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 (

../graphics/cbo5.gif

 

../graphics/cbo2.gif

grouping-expression
super-group

../graphics/cbc2.gif

   

../graphics/cbc5.gif

, ../graphics/dot3.gif )
(

../graphics/cbo2.gif

grouping-expression
super-group

../graphics/cbc2.gif

, ../graphics/dot3.gif )

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

../graphics/cbo3.gif

ROLLUP
CUBE

(grouping-expression-list)
(grouping-expression-list

../graphics/cbc3.gif

  ( )

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:

../graphics/cbo2.gif

grouping-expression

../graphics/cbc2.gif

, ../graphics/dot3.gif
(grouping-expression, ../graphics/dot3.gif)

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.