Natural for DB2 Version 8.4 - Documentation Updates

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

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

• Select Expressions under Natural for DB2 Version 8.4

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

• SELECT under Natural for DB2 Version 8.4

• MERGE under Natural for DB2 Version 8.4

• Searched DELETE under Natural for DB2 Version 8.4

• Search Conditions under Natural for DB2 Version 8.4

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

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

• UPDATE with FIND/READ

• scalar-function

• column-function

• special-register

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:

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

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

Syntax 1 - Extended Set

[WITH_CTE common-table-expression, ]

SELECT selection into-clause table-expression

UNION EXCEPT INTERSECT

DISTINCT ALL

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

[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

END-SELECT LOOP

OFFSET row-count

OFFSET [offset-row-count]

ROW ROWS

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

1 row-count

ROW ROWS

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 a positive integer.

MERGE under Natural for DB2 Version 8.4

source-table

matching-condition

modification-operation

signal-operation

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

Syntax 1 - Extended Set

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

Comparison Predicate

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: