Natural for DB2 Version 8.4 - Documentation Updates

Anmerkung:
The documentation updates provided here only cover the changes specific to Natural for DB2 Version 8.4 and above.

Most changes have been implemented in the SQL statements of Natural for DB2 Version 8.4.1 in support of IBM DB2 Version 12.

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


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

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.4.

UPDATE with FIND/READ

When a Natural program contains a DML UPDATE statement, this statement is translated into an SQL UPDATE statement and a FOR UPDATE OF clause is added to the SELECT statement.

Be aware that a primary key field is not part of a FOR UPDATE OF list except that the compiler option DB2PKYU is set to ON. If DB2PKYU is set to OFF (default), a primary key field can only be updated by using a non-cursor UPDATE operation (see also Natural SQL UPDATE statement in the section Using Natural SQL Statements).

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
GENERATE_UNIQUE_BINARY
GETHINT
GETVARIABLE
HASH_CRC32
HASH_MD5
HASH_SHA1
HASH_SHA256
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
VARCHAR9
VARCHAR_BIT_FORMAT
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'
       ...

column-function

The following column functions do not conform to standard SQL. They are specific to DB2 and belong to the Natural SQL Extended Set.

COUNT_BIG
CORRELATION
COVARIANCE
COVARIANCE_SAMP
MEDIAN
PERCENTILE_CONT
PERCENTILE_DISC
STDDEV
STDDEV_POP
STDDEV_SAMP
VAR
VAR_POP
VAR_SAMP
VARIANCE
VARIANCE_SAMP
XMLAGG

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.4

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

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.4

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.4.

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.

SELECT under Natural for DB2 Version 8.4

Anmerkung:
This is an extract of Syntax 1 - Extended Set and Syntax Element Description in the section SELECT (SQL) (Statements documentation) and only describes the changes specific to Natural for DB2 Version 8.4.

Syntax 1 - Extended Set

[WITH_CTE common-table-expression, ../graphics/dot3.gif]
SELECT selection into-clause table-expression

UNION
EXCEPT
INTERSECT

../graphics/sbo1.gif

DISTINCT
ALL

../graphics/sbc1.gif

../graphics/sbo1.gif

(SELECT selection table-expression)
SELECT selection table-expression

../graphics/sbc1.gif

 
 

../graphics/dot3.gif

[ORDER BY criteria]

OPTIMIZE FOR integer

ROW
ROWS

     
[WITH isolation-level]
[SKIP LOCKED DATA]
[QUERYNO integer]
[OFFSET row-count]
[FETCH FIRST row-limit]
[WITH HOLD]
[WITH RETURN]
[WITH scroll-mode]
[WITH ROWSET POSITIONING FOR max-rowsets]
[IF NO RECORDS FOUND instruction]
statement ../graphics/dot3.gif

END-SELECT
LOOP

   
OFFSET row-count
OFFSET [offset-row-count]

../graphics/cbo2.gif

ROW
ROWS

../graphics/cbc2.gif

The OFFSET clause specifies the number of rows to skip in the result table before retrieving any rows from there. A limited number of rows at the end of a result set can improve the performance of queries with potentially large result sets.

offset-row-count is a numeric variable or constant which determines the number of rows to be skipped. The number must be zero (0) or a positive integer.

FETCH FIRST row-limit
FETCH FIRST

../graphics/sbo2.gif

1
row-count

../graphics/sbc2.gif

../graphics/cbo2.gif

ROW
ROWS

../graphics/cbc2.gif

The FETCH FIRST clause limits the number of rows to be fetched. A limited number of rows can improve the performance of queries with potentially large result sets.

row-count specifies a numeric variable or constant which determines the number of rows to be fetched. The number must be zero (0) or a positive integer.

MERGE under Natural for DB2 Version 8.4

Anmerkung:
This is an extract of the chapter MERGE ( SQL) (Statements documentation) and only describes the changes specific to Natural for DB2 Version 8.4.

MERGE INTO table-name [[AS] correlation-name]
  [include-columns] USING source-table
  ON search-condition
{WHEN matching-condition THEN modification-operation} ...
[ELSE IGNORE]
  [NOT ATOMIC CONTINUE ON SQLEXCEPTION]
  [QUERYNO integer]
Syntax Element Description
MERGE INTO
MERGE INTO Clause:

MERGE INTO initiates an SQL MERGE statement, which is a combination of an SQL INSERT and an SQL Searched UPDATE statement.

table-name
Table Name:

Identifies the target of the INSERT or UPDATE operation of the MERGE statement.

[AS] correlation-name
[AS] correlation-name Clause:

Specifies an alternate name for the target table. The alternate name can be used as qualifier when referencing columns of the intermediate result table.

include-columns
Include Columns Clause:

Specifies a set of columns that are included, along with the columns of the target table, in the result table of the MERGE statement if it is nested in the FROM clause in a SELECT statement. The included columns are appended to end of the column list identified by the target table.

USING source-table
USING source-table Clause:

Specifies the values for the row data to merge into the target table.

ON search-condition
ON search-condition Clause:

Specifies join conditions between the source-table and the target table. Each column name in the search condition must name a column of the target table or source-table.

WHEN matching-condition
WHEN matching-condition Clause:

Specifies the condition for which to perform the modification operation defined in the following THEN clause. See matching-condition.

THEN modification-operation
THEN modification-operation Clause:

Specifies the operation to perform on the matches of the condition defined in the preceding WHEN clause. See modification-operation.

ELSE IGNORE Specifies that no action is taken on source columns that do not match the condition specified in the WHEN clause.
NOT ATOMIC CONTINUE ON SQLEXCEPTION
NOT ATOMIC CONTINUE ON SQLEXCEPTION Clause:

Specifies whether merge processing continues in case an error occurred during processing one row of a set of source rows.

QUERYNO integer
QUERYNO integer Clause:

Specifies the number for this SQL statement that is used in EXPLAIN output and DB2 trace records.

source-table

table-reference
(VALUES

values-single-row
values-multiple-row

)
  [AS] correlation-name (column-name,...)
Syntax Element Description
table-reference Specifies the source table to merge into the target table.
VALUES VALUES introduces the specification of values for the row data to merge into the target table.
values-single-row Specifies a single row of source data.
values-multiple-row Specifies multiple rows of source data.
[AS] correlation-name Specifies a correlation name for the source table.
column-name Specifies a column name to associate the input data to the UPDATE SET assignment clause for an UPDATE operation or the VALUES clause for an INSERT operation.

matching-condition

[NOT] MATCHED [AND search-condition]
Syntax Element Description
[NOT] MATCHED Specifies the modification-operation to perform when an ON search-condition evaluates to true or not true (NOT can be specified optionally).
[AND search-condition] Specifies an (optional) additional condition to evaluate to true before the modification-operation performs.

modification-operation

update-operation
DELETE
signal-operation
insert-operation

Syntax Element Description
update-operation Specifies that the matching target row is updated with the values assigned in the UPDATE SET assignment clause.

An UPDATE operation is only allowed if the matching-condition evaluates to true.

DELETE Specifies that the matching target row is deleted.

A DELETE operation is only allowed if the matching-condition evaluates to true.

signal-operation Specifies the SQL error to raise.

A SIGNAL operation is only allowed if the matching-condition evaluates to true.

insert-operation Specifies the rows to insert into the target table.

An INSERT operation is only allowed if the matching-condition evaluates to not true.

signal-operation

SIGNAL SQLSTATE [VALUE] sqlstate [SET MESSAGE_TEXT = scalar-expression]
Syntax Element Description
SIGNAL Specifies the SIGNAL operation to perform when the matching-condition evaluates to true.

DB2 sets an SQLCODE -438 if an error is raised by the SIGNAL statement.

SQLSTATE [VALUE] sqlstate Specifies the SQLSTATE to be set by DB2.

sqlstate is a 5-character alphanumeric constant or an alphanumeric variable.

sqlstate values are assigned to SQLSTATE by DB2. See the appropriate DB2 documentation for recommended values.

[SET MESSAGE_TEXT = scalar-expression] This optional clause specifies an error or warning message which is placed into the SQLEERRMC field of the SQLCA or which can be retrieved with the GET DIAGNOSTICS statement.

Examples - Example 3:

Merge sales data from the MSALES table into the MPRODUCT table. Demonstrate the MERGE operation with DELETE, UPDATE, INSERT and SIGNAL statements.

DEFINE DATA                         
LOCAL USING DEMSQLCA                
LOCAL                               
1 V1 VIEW OF MPRODUCT           
2 ID                                
2 NAME                              
2 INVENTORY                         
1  #M_TEXT  (A10) INIT <'Oversold: '>
   END-DEFINE
...
MERGE INTO MPRODUCT AS T                                            
      USING (SELECT MSALES.ID        ,SUM(MSALES.SOLD) AS SOLD, 
                     MAX(MCATALOG.NAME) AS NAME                     
               FROM MSALES, MCATALOG                            
               WHERE MSALES.ID = MCATALOG.ID                    
               GROUP BY MSALES.ID) AS S                             
               (ID,SOLD,NAME)                                           
      ON S.ID = T.ID                                                    
      WHEN MATCHED AND T.INVENTORY = S.SOLD                             
                   THEN DELETE                                          
      WHEN MATCHED AND T.INVENTORY < S.SOLD                             
                   THEN SIGNAL SQLSTATE '78000'                         
                        SET MESSAGE_TEXT =:#M_TEXT     || S.NAME        
      WHEN MATCHED                                                      
                   THEN UPDATE SET T.INVENTORY = T.INVENTORY - S.SOLD   
      WHEN NOT MATCHED                                                  
                   THEN INSERT VALUES(S.ID, S.NAME, -S.SOLD)
END TRANSACTION   
END

Searched DELETE under Natural for DB2 Version 8.4

Anmerkung:
This is an extract of Syntax 1 - Searched DELETE in the chapter DELETE (SQL) (Statements documentation) and only describes the changes specific to Natural for DB2 Version 8.4.

Syntax 1 - Extended Set

DELETE FROM table-name [period-clause] [correlation-name]
[include-columns [SET assignment-clause]]
[WHERE search-condition]
[FETCH FIRST row-limit

WITH

RR
RS
CS

[SKIP LOCKED DATA] [QUERYNO integer]

The FETCH FIRST clause limits the effects of the DELETE statement to a subset of qualifying rows. It corresponds to the FETCH FIRST clause of the SELECT statement described in FETCH FIRST row-limit.

Search Conditions under Natural for DB2 Version 8.4

Anmerkung:
This is an extract of the chapter Search Conditions (Statements documentation) and only describes the changes specific to Natural for DB2 Version 8.4.

Comparison Predicate

scalar-expression comparison scalar-expression

row-value-expression comparison row-value-expression

A comparison predicate compares two values or a set of values with another set of values.

In the syntax diagram above, comparison can be one of the following operators:

= equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to
<>   not equal to