This section contains special considerations when using Natural native DML statements and Natural system variables with Natural SQL statements and Db2.
It mainly consists of information also contained in the Natural basic documentation set where each Natural statement and variable is described in detail.
For an explanation of the symbols used in this section to describe the syntax of Natural statements, see Syntax Symbols in the Statements documentation.
For information on logging SQL statements contained in a Natural program, refer to DBLOG Trace Screen for SQL Statements in the DBLOG Utility documentation.
This section covers the following topics:
NDB refreshes the following Db2 special registers automatically to the values, which applied to the least previous executed transaction.
CURRENT SQLID
CURRENT SCHEMA
CURRENT PATH
CURRENT PACKAGE PATH
NDB refreshes the following Db2 special registers only automatically to the values, which
applied to the least previous executed transaction, if the parameter
REFRESH=ON
is set.
CURRENT PACKAGESET
CURRENT SERVER
Those special registers are refreshed regardless whether the previously executed transaction was rolled back or was committed.
All other special registers are not implicitly manipulated by NDB.
This section summarizes particular points you have to consider when using Natural data manipulation language (DML) statements with Db2. Any Natural statement not mentioned in this section can be used with Db2 without restriction.
Below is information on the following Natural DML statements:
The Natural native DML statement BACKOUT
TRANSACTION
undoes all database modifications made since the
beginning of the last logical transaction. Logical transactions can start either after
the beginning of a session or after the last SYNCPOINT
, END TRANSACTION
, or BACKOUT
TRANSACTION
statement.
How the statement is translated and which command is actually issued depends on the TP-monitor environment:
If this command is executed within a Natural stored procedure or Natural
user-defined function (UDF), Natural for Db2 executes the underlying rollback
operation. This sets the caller into a must-rollback state. If this command is
executed within a Natural stored procedure or UDF for Natural error processing
(implicit ROLLBACK
), Natural for Db2 does not execute the underlying
rollback operation, thus allowing the caller to receive the original Natural
error.
Under CICS, the BACKOUT TRANSACTION
statement is translated into an
EXEC CICS ROLLBACK
command. However, in
pseudo-conversational mode, only changes made to the database since the last
terminal I/O are undone. This is due to CICS-specific transaction processing, see
Natural for Db2 under
CICS.
Note:
Be aware that with terminal input in database loops, Natural switches to
conversational mode if no file server is used.
In batch mode and under TSO, the BACKOUT TRANSACTION
statement is
translated into an SQL ROLLBACK
command.
Note:
If running in a DSNMTV01 environment, the BACKOUT TRANSACTION
statement is ignored if the used PSB has been generated without the
CMPAT=YES
option.
Under IMS TM, the BACKOUT TRANSACTION
statement is translated into an
IMS Rollback (ROLB
) command. However, only changes made
to the database since the last terminal I/O are undone. This is due to IMS
TM-specific transaction processing, see Natural for Db2 under IMS TM.
As all cursors are closed when a logical unit of work ends, a BACKOUT
TRANSACTION
statement must not be placed within a database loop; instead, it
has to be placed outside such a loop or after the outermost loop of nested loops.
If an external program written in another standard programming language is called from
a Natural program, this external program must not contain its own ROLLBACK command if
the Natural program issues database calls, too. The calling Natural program must issue
the BACKOUT TRANSACTION
statement for the external program.
If a program tries to backout updates which have already been committed, for example by a terminal I/O, a corresponding Natural error message (NAT3711) is returned.
The Natural native DML statement DELETE
is used to delete a row from a table which has been
read with a preceding FIND
,
READ
, or SELECT
statement. It corresponds to
the SQL statement DELETE WHERE CURRENT OF
cursor-name
, which means that only the row which was
read last can be deleted.
Example:
FIND EMPLOYEES WITH NAME = 'SMITH' AND FIRST_NAME = 'ROGER' DELETE
Natural would translate the above Natural statements into SQL and assign a cursor name
(for example, CURSOR1
) as follows:
DECLARE CURSOR1 CURSOR FOR SELECT FROM EMPLOYEES WHERE NAME = 'SMITH' AND FIRST_NAME = 'ROGER' FOR UPDATE OF NAME DELETE FROM EMPLOYEES WHERE CURRENT OF CURSOR1
Both the SELECT
and the DELETE
statement refer to the same
cursor.
Natural translates a Natural native DML DELETE
statement into a Natural
SQL DELETE
statement in the same way it translates a Natural native DML
FIND
statement into a
Natural SQL SELECT
statement.
A row read with a FIND SORTED BY
cannot be deleted due to Db2 restrictions
explained with the FIND
statement. A row read with a READ
LOGICAL
cannot be deleted either.
If a row rolled out to the file server is to be deleted, Natural rereads automatically
the original row from the database to compare it with its image stored in the file
server. If the original row has not been modified in the meantime, the
DELETE
operation is performed. With the next terminal I/O, the
transaction is terminated, and the row is deleted from the actual database.
If the DELETE
operates on a scrollable cursor, the row on the file server
is marked as DELETE
hole and is deleted from the base table.
However, if any modification is detected, the row will not be deleted and Natural issues the NAT3703 error message for non-scrollable cursors.
If the DELETE
operates on a scrollable cursor, Natural for Db2 simulates
SQLCODE -224 THE RESULT TABLE DOES NOT AGREE WITH THE BASE TABLE
USING for Db2 compliance.
If the DELETE
operates on a scrollable cursor and the row has become a
hole, Natural for Db2 simulates SQLCODE -222 AN UPDATE OR DELETE OPERATION
WAS ATTEMPTED AGAINST A HOLE.
Since a DELETE
statement requires that Natural rereads a single row, a
unique index must be available for the respective table. All columns which comprise the
unique index must be part of the corresponding Natural view.
The Natural native DML statement END
TRANSACTION
indicates the end of a logical transaction and
releases all Db2 data locked during the transaction. All data modifications are
committed and made permanent.
How the statement is translated and which command is actually issued depends on the TP-monitor environment:
If this command is executed from a Natural stored procedure or user defined function (UDF), Natural for Db2 does not execute the underlying commit operation. This allows the stored procedure or UDF to commit updates against non Db2 databases.
Under CICS, the END TRANSACTION
statement is translated into an
EXEC CICS SYNCPOINT
command. If the file server is
used, an implicit end-of-transaction is issued after each terminal I/O. This is due
to CICS-specific transaction processing in pseudo-conversational mode, see Natural for Db2 under
CICS.
In batch mode and under TSO, the END TRANSACTION
statement is
translated into an SQL COMMIT WORK
command.
Note:
If running in a DSNMTV01 environment the END TRANSACTION
statement
is ignored if the used PSB has been generated without the CMPAT=YES
option.
Under IMS TM, the END TRANSACTION
statement is not translated into an
IMS CHKP call, but is ignored. Due to IMS TM-specific transaction processing (see
Natural for Db2 under IMS
TM), an implicit end-of-transaction is issued after each
terminal I/O.
Except when used in combination with the SQL WITH HOLD
clause (see SELECT -
SQL
in Using Natural SQL
Statements), an END TRANSACTION
statement must not
be placed within a database loop, since all cursors are closed when a logical unit of
work ends. Instead, it has to be placed outside such a loop or after the outermost loop
of nested loops.
If an external program written in another standard programming language is called from
a Natural program, this external program must not contain its own
COMMIT
command if the Natural program issues database
calls, too. The calling Natural program must issue the END TRANSACTION
statement on behalf of the external program.
Note:
With Db2, the END TRANSACTION
statement cannot be used to store
transaction data.
The Natural native DML statement FIND
corresponds to the Natural SQL statement SELECT
.
Example:
Natural native DML statements:
FIND EMPLOYEES WITH NAME = 'BLACKMORE' AND AGE EQ 20 THRU 40 OBTAIN PERSONNEL_ID NAME AGE
Equivalent Natural SQL statement:
SELECT PERSONNEL_ID, NAME, AGE FROM EMPLOYEES WHERE NAME = 'BLACKMORE' AND AGE BETWEEN 20 AND 40
Natural internally translates a FIND
statement into an SQL SELECT
statement as described in
Processing of SQL Statements
Issued by Natural in the section Internal Handling of Dynamic
Statements. The SELECT
statement is executed by an
OPEN CURSOR
statement followed by a FETCH
command. The FETCH
command is executed repeatedly until
either all records have been read or the program flow exits the FIND
processing loop. A CLOSE CURSOR
command ends the
SELECT
processing.
The WITH
clause of a
FIND
statement is converted to the WHERE
clause of the
SELECT
statement. The basic search criterion for a Db2 table can be
specified in the same way as for an Adabas file. This implies that only database fields
which are defined as descriptors can be used to construct basic search criteria and that
descriptors cannot be compared with other fields of the Natural view (that is, database
fields) but only with program variables or constants.
Note:
As each database field (column) of a Db2 table can be used for searching, any
database field can be defined as a descriptor in a Natural DDM.
The WHERE
clause of
the FIND
statement is evaluated by Natural after the rows have
been selected via the WITH
clause. Within the WHERE
clause,
non-descriptors can be used and database fields can be compared with other database
fields.
Note:
Db2 does not have sub-, super-, or phonetic descriptors.
A FIND NUMBER
statement is translated into a SELECT
statement containing a
COUNT(*)
clause. The number of rows found is returned in the Natural
system variable *NUMBER
as described in the Natural
System Variables documentation.
The FIND UNIQUE
statement can be used to ensure that only one record is selected for processing. If the
FIND UNIQUE
statement is referenced by an UPDATE
statement,
a non-cursor (Searched) UPDATE
operation is generated instead of a
cursor-oriented (Positioned) UPDATE
operation. Therefore, it can be used if
you want to update a Db2 primary key. It is, however, recommended to use the Natural SQL
Searched UPDATE
statement to update a primary key.
In static mode, the FIND NUMBER
and FIND UNIQUE
statements
are translated into a SELECT
SINGLE
statement as described in the section Using Natural SQL
Statements.
The FIND FIRST
statement cannot be used. The PASSWORD
, CIPHER
, COUPLED
and RETAIN
clauses cannot be used either.
The SORTED BY
clause of a FIND
statement is translated into the SQL SELECT ... ORDER
BY
clause, which follows the search criterion. Because this produces
a read-only result table, a row read with a FIND
statement that contains a
SORTED BY
clause cannot be updated or deleted.
A limit on the depth of nested database loops can be specified at installation time. If this limit is exceeded, a Natural error message is returned.
Notes:
FIND (5)
, the limitation value will be translated into a FETCH
FIRST integer ROWS ONLY
clause in the generated
SQL string.
MULTIFETCH
clause of
the FIND
statement.
As far as the file server is concerned, there are no programming restrictions with selection statements. It is, however, recommended to make yourself familiar with its functionality considering performance and file server space requirements.
The Natural DML statement HISTOGRAM
returns the number of rows in a table which have
the same value in a specific column. The number of rows is returned in the Natural
system variable *NUMBER
as described in the Natural
System Variables documentation.
Example:
Natural native DML statements:
HISTOGRAM EMPLOYEES FOR AGE OBTAIN AGE
Equivalent Natural SQL statement:
SELECT COUNT(*), AGE FROM EMPLOYEES WHERE AGE > -999 GROUP BY AGE ORDER BY AGE
Natural translates the HISTOGRAM
statement into an SQL SELECT
statement, which means that the control flow is similar to the flow explained for the
FIND
statement.
Note:
With Universal Database Server for z/OS Version 8, Natural for Db2 supports Db2
multiple row processing on behalf of the MULTIFETCH
clause of the HISTOGRAM
statement.
The Natural native DML statement READ
can also be used to access Db2 tables. Natural
translates a READ
statement into an SQL SELECT
statement.
READ PHYSICAL
and
READ LOGICAL
can be
used; READ BY ISN
,
however, cannot be used, as there is no Db2 equivalent to Adabas ISNs. The PASSWORD
and CIPHER
clauses cannot be
used either.
Since a READ LOGICAL
statement is translated into a SELECT ... ORDER
BY
statement, which produces a read-only table, a row read with a
READ LOGICAL
statement cannot be updated or deleted (see Example 1). The
start value can only be a constant or a program variable; any other field of the Natural
view (that is, any database field) cannot be used.
A READ PHYSICAL
statement is translated into a SELECT
statement without an ORDER BY
clause and can therefore be updated or
deleted (see Example 2).
Example 1:
Natural native DML statements:
READ PERSONNEL BY NAME OBTAIN NAME FIRSTNAME DATEOFBIRTH
Equivalent Natural SQL statement:
SELECT NAME, FIRSTNAME, DATEOFBIRTH FROM PERSONNEL WHERE NAME >= ' ' ORDER BY NAME
Example 2:
The Natural native DML statements:
READ PERSONNEL PHYSICAL OBTAIN NAME
Equivalent Natural SQL statement:
SELECT NAME FROM PERSONNEL
If the READ
statement contains a WHERE
clause, this clause is evaluated by the Natural
processor after the rows have been selected according to the descriptor
value(s) specified in the search criterion.
If a processing limit is specified as a constant integer number, for example,
READ (5)
, in the SQL string generated, the value that defines the
limitation will be translated into the clause
FETCH FIRST
integer
ROWS ONLY |
Natural for Db2 uses insensitive scrollable cursors to process the following READ
statement:
READ
.. [IN] [LOGICAL] VARIABLE/DYNAMIC
operand5
[SEQUENCE] |
Natural for Db2 uses insensitive scrollable cursors to process the READ
statement below. If relating to a Positioned
UPDATE
or Positioned
DELETE
statement, Natural for Db2 uses insensitive static
cursors.
READ .. [IN] [PHYSICAL] DESCENDING/VARIABLE/DYNAMIC
operand5
[SEQUENCE] |
operand5
Value A
will be translated into a FETCH FIRST/NEXT
Db2
access, and value D
into a FETCH LAST/PRIOR
Db2 access.
Note:
Natural for Db2 supports Db2 multiple row processing on behalf of the MULTIFETCH
clause of the
READ
statement.
As far as the file server is concerned there are no programming restrictions with selection statements. It is, however, recommended to make yourself familiar with its functionality considering performance and file server space requirements.
The Natural native DML statement STORE
is used to add a row to a Db2 table. The
STORE
statement corresponds to the SQL statement INSERT
.
Example:
The Natural native DML statement:
STORE RECORD IN EMPLOYEES WITH PERSONNEL_ID = '2112' NAME = 'LIFESON' FIRST_NAME = 'ALEX'
Equivalent Natural SQL statement:
INSERT INTO EMPLOYEES (PERSONNEL_ID, NAME, FIRST_NAME) VALUES ('2112', 'LIFESON', 'ALEX')
The PASSWORD
, CIPHER
and USING/GIVING NUMBER
clauses of the STORE
statement cannot be used.
The Natural native DML statement UPDATE
updates a row in a Db2 table which has been read with
a preceding FIND
, READ
, or SELECT
statement. It corresponds to
the SQL statement UPDATE WHERE CURRENT OF
cursor-name
(Positioned
UPDATE
), which means that only the row which was read last
can be updated.
If a row rolled out to the file server is to be updated, Natural automatically
rereads the original row from the database to compare it with its image stored in the
file server. If the original row has not been modified in the meantime, the
UPDATE
operation is performed. With the next terminal I/O, the
transaction is terminated and the row is definitely updated on the database.
If the UPDATE
operates on a scrollable cursor, the row on the file
server and the row in the base table are updated. If the row no longer qualifies for
the search criteria of the related SELECT
statement after the update, the row is marked as
UPDATE
hole on the file server.
However, if any modification is detected, the row will not be updated and Natural issues the NAT3703 error message for non-scrollable cursors.
If the UPDATE
operates on a scrollable cursor, Natural for Db2 simulates
SQLCODE -224 THE RESULT TABLE DOES NOT AGREE WITH THE BASE TABLE
USING for Db2 compliance.
If the UPDATE
operates on a scrollable cursor and the row has become a
hole, Natural for Db2 simulates SQLCODE -222 AN UPDATE OR DELETE OPERATION
WAS ATTEMPTED AGAINST A HOLE.
Since an UPDATE
statement requires rereading a single row by Natural, a
unique index must be available for this table. All columns which comprise the unique
index must be part of the corresponding Natural view.
As explained with the Natural native DML statement FIND
, Natural translates a
FIND
statement into an SQL SELECT
statement. 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.
Example:
FIND EMPLOYEES WITH SALARY < 5000 ASSIGN SALARY = 6000 UPDATE
Natural would translate the above Natural statements into SQL and assign a cursor name (for example, CURSOR1) as follows:
DECLARE CURSOR1 CURSOR FOR SELECT SALARY FROM EMPLOYEES WHERE SALARY < 5000 FOR UPDATE OF SALARY UPDATE EMPLOYEES SET SALARY = 6000 WHERE CURRENT OF CURSOR1
Both the SELECT
and the UPDATE
statement refer to the same
cursor.
Due to Db2 logic, a column (field) can only be updated if it is contained in the
FOR UPDATE OF
clause; otherwise updating this column (field) is
rejected. Natural includes automatically all columns (fields) into the FOR
UPDATE OF
clause which have been modified anywhere in the Natural program or
which are input fields as part of a Natural map.
However, an Db2 column is not updated if the column (field) is marked as "not
updateable" in the Natural DDM. Such columns (fields) are removed from the
FOR UPDATE OF
list without any warning or error message. The columns
(fields) contained in the FOR UPDATE OF
list can be checked with the
LISTSQL
command.
The Adabas short name in the Natural DDM determines whether a column (field) can be updated.
The following table shows the ranges that apply:
Short-Name Range | Type of Field |
---|---|
AA - N9 | non-key field that can be updated |
Aa - Nz | non-key field that can be updated |
OA - O9 | primary key field |
PA - P9 | ascending key field that can be updated |
QA - Q9 | descending key field that can be updated |
RA - X9 | non-key field that cannot be updated |
Ra - Xz | non-key field that cannot be updated |
YA - Y9 | ascending key field that cannot be updated |
ZA - Z9 | descending key field that cannot be updated |
1A - 9Z | non-key field that cannot be updated |
1a - 9z | non-key field that cannot be updated |
Be aware that a primary key field is never part of a FOR UPDATE OF
list
except when the compiler option DB2PKYU
is set to ON
. If
DB2PKYU
is set to OFF
, which is its default value, you can
only update a primary key field by using a non-cursor UPDATE
operation.
For more information, see also Natural SQL UPDATE
statement in the section Using Natural
SQL Statements.
A row read with a FIND
statement that contains a SORTED BY
clause
cannot be updated (due to Db2 limitations as explained with the FIND
statement). A row read
with a READ LOGICAL
statement cannot be updated either (as explained with the READ
statement).
If a column is to be updated which is redefined as an array, it is strongly
recommended to update the whole column and not individual occurrences; otherwise,
results are not predictable. To do so, in reporting mode you can use the OBTAIN
statement, which must be
applied to all field occurrences in the column to be updated. In structured mode,
however, all these occurrences must be defined in the corresponding Natural view.
The data locked by an UPDATE
statement are released when an END TRANSACTION
(COMMIT
WORK
) or BACKOUT
TRANSACTION
(ROLLBACK WORK
) statement is executed by
the program.
Note:
If a length indicator field or NULL
indicator field is updated in a
Natural program without updating the field (column) it refers to, the update of the
column is not generated for Db2 and thus no updating takes place.
In general, the Natural native DML statement UPDATE
can be used in both
structured and reporting mode. However, after a SELECT
statement, only
the syntax defined for Natural structured mode is allowed:
UPDATE [RECORD ] [IN ] [STATEMENT ]
[(r)]
|
This is due to the fact that in combination with the SELECT
statement,
the Natural native DML UPDATE
statement is only allowed in the special
case of:
... SELECT ... INTO VIEW view-name ...
Thus, only a whole Natural view can be updated; individual columns (fields) cannot.
Example:
DEFINE DATA LOCAL 01 PERS VIEW OF SQL-PERSONNEL 02 NAME 02 AGE END-DEFINE SELECT * INTO VIEW PERS FROM SQL-PERSONNEL WHERE NAME LIKE 'S%' IF NAME = 'SMITH' ADD 1 TO AGE UPDATE END-IF END-SELECT ...
In combination with the Natural native DML UPDATE
statement, any other
form of the SELECT
statement is rejected and an error message is
returned.
In all other respects, the Natural native DML UPDATE
statement can be
used with the SELECT
statement in the same way as with the Natural
FIND
statement.
This section covers points you have to consider when using Natural SQL statements with Db2. These Db2-specific points partly consist in syntax enhancements which belong to the Extended Set of Natural SQL syntax. The Extended Set is provided in addition to the Common Set to support database-specific features; see Common Set and Extended Set in the Statements documentation.
For information on logging SQL statements contained in a Natural program, refer to DBLOG Trace Screen for SQL Statements in the DBLOG Utility documentation.
Below is information on the following Natural SQL statements and on common syntactical items:
The following common syntactical items are either Db2-specific and do not conform to the standard SQL syntax definitions (that is, to the Common Set of Natural SQL syntax) or impose restrictions when used with Db2 (see also Using Natural SQL Statements in the Statements documentation).
Below is information on the following common syntactical items:
An atom can be either a parameter (that is, a Natural program variable or host variable) or a constant. When running dynamically, however, the use of host variables is restricted by Db2. For further details, refer to the relevant Db2 literature by IBM.
The comparison operators specific to Db2 belong to the Natural Extended Set. For a description, refer to Comparison Predicate in Search Conditions in the Statements documentation.
The following factors are specific to Db2 and belong to the Natural SQL Extended Set:
special-register |
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' ...
A column function returns a single-value result for the argument it receives. The argument is a set of like values, such as the values of a column. Column functions are also called aggregating functions.
The following column functions conform to standard SQL. They are not specific to Db2:
AVG
COUNT
MAX
MIN
SUM
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
COVAR_POP
COVARIANCE_SAMP
LISTAGG
MEDIAN
PERCENTILE_CONT
PERCENTILE_DISC
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_ICPT
REGR_IMTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
STDDEV
STDDEV_POP
STDDEV_SAMP
VAR
VAR_POP
VAR_SAMP
VARIANCE
VARIANCE_SAMP
XMLAGG
The concatenation operator (CONCAT
or ||
) does not conform
to standard SQL. It is specific to Db2 and belongs to the Natural Extended Set.
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
CLIENT ACCNTG
CURRENT CLIENT_APPLNAME
CLIENT APPLNAME
CURRENT CLIENT_USERID
CLIENT USERID
CURRENT CLIENT_WRKSTNNAME
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 LOCK TIMEOUT
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
CURRENT_MEMBER
CURRENT OPTIMIZATION HINT
CURRENT PACKAGE PATH
CURRENT PACKAGESET
CURRENT_PATH
CURRENT PRECISION
CURRENT QUERY ACCELERATION
CURRENT QUERY ACCELERATION WAITFORDATA
CURRENT REFRESH AGE
CURRENT ROUTINE VERSION
CURRENT RULES
CURRENT SCHEMA
CURRENT SERVER
CURRENT_SERVER
CURRENT SQLID
CURRENT TEMPORAL BUSINESS_TIME
CURRENT TEMPORAL_SYSTEM_TIME
CURRENT TIME
CURRENT_TIME
CURRENT TIMESTAMP
CURRENT TIMEZONE
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.
Units, also called "durations", are specific to Db2 and belong to the Natural SQL Extended Set.
The following units are supported:
DAY
DAYS
HOUR
HOURS
MICROSECOND
MICROSECONDS
MINUTE
MINUTES
MONTH
MONTHS
SECOND
SECONDS
YEAR
YEARS
CASE
|
searched-when-clause ... |
ELSE
|
|
END
|
Case-expressions
do not conform to standard
SQL and are therefore supported by the Natural SQL Extended Set only.
DEFINE DATA LOCAL 01 #EMP 02 #EMPNO (A10) 02 #FIRSTNME (A15) 02 #MIDINIT (A5) 02 #LASTNAME (A15) 02 #EDLEVEL (A13) 02 #INCOME (P7) END-DEFINE SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, (CASE WHEN EDLEVEL < 15 THEN 'SECONDARY' WHEN EDLEVEL < 19 THEN 'COLLEGE' ELSE 'POST GRADUATE' END ) AS EDUCATION, SALARY + COMM AS INCOME INTO #EMPNO, #FIRSTNME, #MIDINIT, #LASTNAME, #EDLEVEL, #INCOME FROM DSN8510-EMP WHERE (CASE WHEN SALARY = 0 THEN NULL ELSE SALARY / COMM END ) > 0.25 DISPLAY #EMP END-SELECT END
The Natural SQL statement CALLDBPROC
is used to call Db2 stored
procedures. It supports the result set mechanism of Db2 and it enables you to call Db2
stored procedures. For further details and statement syntax, see CALLDBPROC (SQL) in the
Statements documentation.
The following topics are covered below:
If the CALLDBPROC
statement is executed dynamically, all parameters and constants are mapped to the
variables of the following Db2 SQL statement:
CALL :hv USING DESCRIPTOR :sqlda statement
:hv
denotes a host variable containing the
name of the procedure to be called and :sqlda
is a dynamically generated sqlda describing the parameters to be passed to the stored
procedure.
If the CALLDBPROC
statement is executed statically, the constants of the
CALLDBPROC
statement are also generated as constants in the generated
assembler SQL source for the Db2 precompiler.
If the SQLCODE created by the CALL
statement indicates that there are result sets
(SQLCODE +466
and +464
), Natural for Db2 runtime executes a
DESCRIBE PROCEDURE :hv INTO
:sqlda
statement in order to retrieve the result
set locator values of the result sets created by the invoked stored procedure. These
values are put into the RESULT
SETS
variables specified in the CALLDBPROC
statement.
Each RESULT SETS
variable specified in a CALLDBPROC
for
which no result set locator value is present is reset to zero. The result set locator
values can be used to read the result sets by means of the READ RESULT SET
statement as long as the database transaction which created the result set has not yet
issued a COMMIT
or ROLLBACK
.
If the result set was created by a cursor WITH HOLD
,
the result set locator value remains valid after a COMMIT
operation.
Unlike other Natural SQL statements, CALLDBPROC
enables you
(optionally!) to specify an SQLCODE variable following the GIVING
keyword which will contain the SQLCODE of the underlying CALL
statement. If GIVING
is specified, it is up to the Natural program to react on the SQLCODE (error message
NAT3700 is not issued by the runtime).
Below are the parameter data types supported by the CALLDBPROC
statement:
Natural Format/Length | Db2 Data Type |
---|---|
An | CHAR(n) |
B2 | SMALLINT |
B4 | INT |
Bn
|
CHAR(n) |
F4 | REAL |
F8 | DOUBLE PRECISION |
I2 | SMALLINT |
I4 | INT |
Nnn.m | NUMERIC(nn+m,m) |
Pnn.m | NUMERIC(nn+m,n) |
Gn | GRAPHIC(n) |
An/1:m | VARCHAR(n*m) |
D | DATE |
T |
TIME
Note: |
This parameter is used to invoke Natural stored procedures defined with
PARAMETER STYLE GENERAL/WITH NULL
.
If the CALLMODE=NATURAL
parameter is specified, an additional
parameter describing the parameters passed to the Natural stored procedure is passed
from the client, that is, caller, to the server, that is, the Natural for Db2 server
stub. The parameter is the Stored Procedure Control Block (STCB; see also STCB
Layout in PARAMETER STYLE in the section Processing Natural Stored Procedures and
UDFs) and has the format VARCHAR
from the
viewpoint of Db2. Therefore, every Natural stored procedure defined with
PARAMETER STYLE GENERAL/WITH NULL
has to be defined with the
CREATE PROCEDURE
statement by using this VARCHAR
parameter
as the first in its PARMLIST
row.
From the viewpoint of the caller, that is, the Natural program, and from the
viewpoint of the stored procedure, that is, Natural subprogram, the STCB is invisible.
It is passed as first parameter by the Natural for Db2 runtime and it is used as on
the server side to build the copy of the passed data in the Natural thread and the
corresponding CALLNAT
statement. Additionally, this parameter serves as a container for error information
created during execution of the Natural stored procedure by the Natural runtime. It
also contains information on the library where you are logged on and the Natural
subprogram to be invoked.
Below is a sample program for issuing CALLDBPROC
and READ RESULT SET
statements:
DEFINE DATA LOCAL 1 ALPHA (A8) 1 NUMERIC (N7.3) 1 PACKED (P9.4) 1 VCHAR (A20/1:5) INIT <'DB25SGCP'> 1 INTEGER2 (I2) 1 INTEGER4 (I4) 1 BINARY2 (B2) 1 BINARY4 (B4) 1 BINARY12 (B12) 1 FLOAT4 (F4) 1 FLOAT8 (F8) 1 INDEX-ARRAY (I2/1:11) 1 INDEX-ARRAY1(I2) 1 INDEX-ARRAY2(I2) 1 INDEX-ARRAY3(I2) 1 INDEX-ARRAY4(I2) 1 INDEX-ARRAY5(I2) 1 INDEX-ARRAY6(I2) 1 INDEX-ARRAY7(I2) 1 INDEX-ARRAY8(I2) 1 INDEX-ARRAY9(I2) 1 INDEX-ARRAY10(I2) 1 INDEX-ARRAY11(I2) 1 #RESP (I4) 1 #RS1 (I4) INIT <99> 1 #RS2 (I4) INIT <99> LOCAL 1 V1 VIEW OF SYSIBM-SYSTABLES 2 NAME 1 V2 VIEW OF SYSIBM-SYSPROCEDURES 2 PROCEDURE 2 RESULT_SETS 1 V (I2) INIT <99> END-DEFINE CALLDBPROC 'DAEFDB25.SYSPROC.SNGSTPC' DSN8510-EMP ALPHA INDICATOR :INDEX-ARRAY1 NUMERIC INDICATOR :INDEX-ARRAY2 PACKED INDICATOR :INDEX-ARRAY3 VCHAR(*) INDICATOR :INDEX-ARRAY4 INTEGER2 INDICATOR :INDEX-ARRAY5 INTEGER4 INDICATOR :INDEX-ARRAY6 BINARY2 INDICATOR :INDEX-ARRAY7 BINARY4 INDICATOR :INDEX-ARRAY8 BINARY12 INDICATOR :INDEX-ARRAY9 FLOAT4 INDICATOR :INDEX-ARRAY10 FLOAT8 INDICATOR :INDEX-ARRAY11 RESULT SETS #RS1 #RS2 CALLMODE=NATURAL READ (10) RESULT SET #RS2 INTO VIEW V2 FROM SYSIBM-SYSTABLES WRITE 'PROC F RS :' PROCEDURE 50T RESULT_SETS END-RESULT END
The Natural SQL COMMIT
statement indicates the end of a logical
transaction and releases all Db2 data locked during the transaction. All data
modifications are made permanent. For further details and statement syntax, see
COMMIT (SQL) in the
Statements documentation.
COMMIT
is a synonym for the Natural native DML statement END TRANSACTION
as described
in the section Using Natural Native DML
Statements.
No transaction data can be provided with the COMMIT
statement.
If this command is executed from a Natural stored procedure or user-defined function (UDF), Natural for Db2 does not execute the underlying commit operation. This allows the Natural stored procedure or UDF to commit updates against non Db2 databases.
Under CICS, the COMMIT
statement is translated into an EXEC
CICS SYNCPOINT
command. If the file server is used, an implicit
end-of-transaction is issued after each terminal I/O. This is due to CICS-specific
transaction processing in pseudo-conversational mode, see Natural for Db2 under
CICS.
Under IMS TM, the COMMIT
statement is not translated into an IMS
CHECKPOINT
command, but is ignored. An implicit
end-of-transaction is issued after each terminal I/O. This is due to IMS TM-specific
transaction processing, see Natural
for Db2 under IMS TM.
Unless when used in combination with the WITH HOLD
clause (see Syntax 1 - Cursor-Oriented Selection in
SELECT (SQL) in the Statements documentation),
a COMMIT
statement must not be placed within a database loop, since all
cursors are closed when a logical unit of work ends. Instead, it has to be placed
outside such a loop or after the outermost loop of nested loops.
If an external program written in another standard programming language is called from
a Natural program, this external program must not contain its own COMMIT
command if the Natural program issues database calls, too. The calling Natural program
must issue the COMMIT
statement on behalf of the external program.
Both the cursor-oriented or Positioned
DELETE
, and the non-cursor or Searched
DELETE
statements are supported as part of Natural SQL; the
functionality of the Positioned DELETE
statement corresponds to that of the
Natural DML DELETE
statement. For further details and statement syntax, see DELETE (SQL) in the
Statements documentation.
With Db2, a table name in the FROM
Clause of a Searched
DELETE
statement can be assigned a correlation-name
. This does
not correspond to the standard SQL syntax definition and therefore belongs to the
Natural SQL Extended
Set.
The Searched DELETE
statement must be used, for example, to delete a row
from a self-referencing table, since with self-referencing tables a Positioned
DELETE
is not allowed by Db2.
The Natural SQL INSERT
statement is used to add one or more new rows to a
table.
Since the INSERT
statement can contain a select expression, all the
Db2-specific common syntactical
items described above apply.
For further details and statement syntax, see INSERT (SQL) in the Statements documentation.
The MERGE
statement is a hybrid SQL statement consisting of an
UPDATE
component and an INSERT
component. It allows you
either to insert a row into a Db2 table or to update a row of a Db2 table if the input
data matches an already existing row of a table.
The MERGE
statement belongs to the SQL Extended Set.
For further details and statement syntax, see MERGE (SQL) in the Statements documentation.
The Natural PROCESS SQL
statement is used to issue SQL statements to the
underlying database. The statements are specified in a
statement-string
, which can also include
constants and parameters. The set of statements which can be issued is also referred to
as Flexible SQL and comprises those statements which can be issued with the SQL
statement EXECUTE
.
In addition, Flexible SQL includes the following Db2-specific statements:
CALL
CONNECT
GET DIAGNOSTICS
SET APPLICATION ENCODING SCHEME
SET CONNECTION
SET CURRENT DEGREE
SET CURRENT LC_CTYPE
SET CURRENT OPTIMIZATION HINT
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
SET CURRENT PACKAGE PATH
SET CURRENT PACKAGESET
SET CURRENT PATH
SET CURRENT PRECISION
SET CURRENT REFRESH AGE
SET CURRENT RULES
SET CURRENT SCHEMA
SET CURRENT SQLID
SET ENCRYPTION PASSWORD
SET
host-variable=special-register
RELEASE
Notes:
PROCESS SQL
are skipped during static
generation. Thus they are always executed dynamically via NDBIOMO
.
COMMIT
and
ROLLBACK
statements must not be used within PROCESS SQL
.
For further details and statement syntax, see PROCESS SQL in the Statements documentation.
The Natural SQL READ RESULT SET
statement reads a result set created by a
Natural stored procedure that was invoked by a CALLDBPROC
statement. For
details on how to specify the scroll direction by using the variable scroll-hv, see the
SELECT
statement.
For further details and statement syntax, see READ RESULT SET (SQL) in the Statements documentation.
The Natural SQL ROLLBACK
statement undoes all database modifications made since the beginning of the last logical
transaction. Logical transactions can start either after the beginning of a session or
after the last COMMIT
/END TRANSACTION
or
ROLLBACK
/BACKOUT TRANSACTION
statement. All records held
during the transaction are released.
For further details and statement syntax, see ROLLBACK
(SQL)
in the Statements documentation.
ROLLBACK
is a synonym for the Natural statement BACKOUT TRANSACTION
as
described in the section Using Natural
Native DML Statements.
If this command is executed from a Natural stored procedure or user-defined function
(UDF), Natural for Db2 executes the underlying rollback operation. This sets the caller
into a must-rollback state. If this command is executed by Natural error processing
(implicit ROLLBACK
), Natural for Db2 does not execute the underlying
rollback operation, thus allowing the caller to receive the original Natural error.
Under CICS, the ROLLBACK
statement is translated into an EXEC
CICS ROLLBACK
command. However, if the file server is used, only changes
made to the database since the last terminal I/O are undone. This is due to
CICS-specific transaction processing in pseudo-conversational mode, see Natural for Db2 under
CICS.
Under IMS TM, the ROLLBACK
statement is translated into an IMS Rollback
(ROLB
) command. However, only changes made to the database
since the last terminal I/O are undone. This is due to IMS TM-specific transaction
processing, see Natural for Db2 under
IMS TM.
As all cursors are closed when a logical unit of work ends, a ROLLBACK
statement must not be placed within a database loop; instead, it has to be placed
outside such a loop or after the outermost loop of nested loops.
If an external program written in another standard programming language is called from
a Natural program, this external program must not contain its own ROLLBACK
command if the Natural program issues database calls, too. The calling Natural program
must issue the ROLLBACK
statement on behalf of the external program.
The Natural SQL SELECT
statement supports both the cursor-oriented
selection, which is used to retrieve an arbitrary number of rows and the non-cursor
selection (Singleton SELECT
), which retrieves at most one single row.
For full details and statement syntax, see SELECT (SQL) in the Statements documentation.
Like the Natural native DML FIND
statement, the cursor-oriented
SELECT
statement is used to select a set of rows (records) from one or
more Db2 tables, based on a search criterion. Since a database loop is initiated, the
loop must be closed by a LOOP
statement (in reporting mode) or by an
END-SELECT
statement (in structured mode). With this construction,
Natural uses the same loop processing as with the FIND
statement. In
addition, no cursor management is required from the application program; it is
automatically handled by Natural.
For further details and syntax, see Syntax 1 - Cursor-Oriented Selection in SELECT (SQL) in the Statements documentation.
The Natural SQL statement SELECT SINGLE
provides the functionality of a
non-cursor selection (Singleton SELECT
); that is, a select expression
that retrieves at most one row without using a cursor.
Since Db2 supports the Singleton SELECT
command in static SQL only, in
dynamic mode, the Natural SELECT SINGLE
statement is executed in the same
way as a set-level SELECT
statement, which results in a cursor operation.
However, Natural checks the number of rows returned by Db2. If more than one row is
selected, a corresponding error message is returned.
For further details and syntax, see Syntax 2 - Non-Cursor Selection in SELECT (SQL) in the Statements documentation.
Both the cursor-oriented or Positioned
UPDATE
and the non-cursor or Searched
UPDATE
statements are supported as part of Natural SQL. Both
of them reference either a table or a Natural view.
With Db2, the name of a table or Natural view to be referenced by a Searched
UPDATE
can be assigned a correlation-name. This does not correspond to
the standard SQL syntax definition and therefore belongs to the Natural Extended Set.
The Searched UPDATE
statement must be used, for example, to update a
primary key field, since Db2 does not allow updating of columns of a primary key by
using a Positioned UPDATE
statement.
Note:
If you use the SET *
notation, all fields of the referenced Natural
view are added to the FOR UPDATE OF
and SET
lists. Therefore,
ensure that your view contains only fields which can be updated; otherwise, a negative
SQLCODE is returned by Db2.
For further details and syntax, see UPDATE (SQL) in the Statements documentation.
When used with Db2, there are restrictions and/or special considerations concerning the following Natural system variables:
For information on restrictions and/or special considerations, refer to the section Database-Specific Information in the corresponding system variable documentation.
This section describes the multiple row functionality for Db2 databases.
You have to operate against Db2 for z/OS Version 8 or higher to use these features.
Natural for Db2 provides two kinds of multiple row processing features:
This feature does not influence the program logic. Although the Natural native DML and Natural SQL DML provide clauses for specification of the multi-fetch-factor, the Natural program operates with one database row and from the program point of view only one row is received from or is send to the database.
This feature is only available with Natural SQL DML and has a lot of impact on the
program logic, as it allows the retrieval of multiple rows from the database into the
program storage by a single Natural SQL SELECT
statement into a set of
arrays. Additionally it is possible to insert multiple rows into the database from a
set of arrays by the Natural SQL INSERT
statement.
Below is information on the following topics:
In standard mode, Natural does not read multiple records with a single database call; it always operates in a one-record-per-fetch mode. This kind of operation is solid and stable, but can take some time if a large number of database records are being processed.
To improve the performance of those programs, you can use the Multi-Fetch Clause in the
Natural DML FIND
, READ
or HISTOGRAM
statements. This allows you
to specify the number of records read per database access.
FIND
|
MULTI-FETCH
|
ON
|
||||||
READ
|
OFF
|
|||||||
HISTOGRAM
|
OF
multi-fetch-factor
|
Where the multi-fetch-factor
is either a
constant or a variable with a format integer (I4).
To improve the performance of the Natural SQL SELECT
statements, you can
use the WITH ROWSET POSITIONING FOR
Clause to specify a
multi-fetch-factor.
WITH ROWSET POSITIONING FOR
|
[:] row_hv |
ROWS
|
||||
integer |
At statement execution time, the runtime checks if a
multi-fetch-factor
greater than 1 is
supplied for the database statement.
If the multi-fetch-factor
is
less than or equal to 1 | the database call is continued in the usual one-record-per-access mode. |
greater than 1 | the database call is prepared dynamically to read multiple records (e.g.
10) with a single database access into an auxiliary buffer (multi-fetch buffer).
If successful, the first record is transferred into the underlying data view.
Upon the execution of the next loop, the data view is filled directly from the
multi-fetch buffer, without database access. After all records are fetched from
the multi-fetch buffer, the next loop results in the next record set being read
from the database. If the database loop is terminated (either by end-of-records,
ESCAPE , STOP , etc.), the content of the
multi-fetch buffer is released.
|
The program does not receive "fresh" records from the database for every loop, but operates with images retrieved at the most recent multi-fetch access.
If a dynamic direction change (IN DYNAMIC...SEQUENCE
) is coded for a
Natural DML READ
or HISTOGRAM
statement, the
multi-fetch feature is not possible and leads to a corresponding syntax error at
compilation.
The size occupied by a database loop in the multi-fetch buffer is determined according to the rule:
header + sqldaheader + columns*(sqlvar+lise) + mf*(udind + sum(collen) + sum(LF(columns) + sum(nullind)) |
= |
32 + 16 + columns*(44+12) + mf*(1 + sum(collen) + sum(LF(column)) + sum(2)) |
where
header denotes the length of the header of a entry in the Db2 multifetch buffer, that is, 32
sqldaheader denotes the length of the header of a sqlda, that is, 16
columns denotes the number of receiving fields of a SQL request
sqlvar denotes the length of a sqlvar, that is, 44
lise denotes the length of a Natrual for Db2 specific sqlvar extension
mf denotes the multifetch factor, that is, the number of rows fetched by one database call
collen denotes the length of the receiving field
LF(column) denotes the size of the length field of the receiving field, that is, 0 for fixed length fields, 2 for variable length fields, and 4 for large object columns (LOBs)
nullind denotes the length of a null indicator, that is, 2
The multifetch buffer is released at terminal i/o in pseudo conversional mode.
Therefore there is no size limitation for the Db2 multifetch buffer
(DB2SIZE6
). The buffer will be automatical enlarged if necessary.
When multi-fetch is used, real database calls are only submitted to get a new set of records.
The TEST DBLOG Q
facility is also called from the Natural for Db2 multi
fetch handler for every rowset fetch from Db2 and for every record moved from the multi
fetch buffer to the program storage. The events are distinguished by the literal
MULTI FETCH ...
and <BUFF FETCH ...
10:51:57 ***** NATURAL Test Utilities ***** 2006-01-27 User HGK - DBLOG Trace - Library NDB42 M No R SQL Statement (truncated) CU SN SREF M Typ SQLC/W Program Line LV _ 1 SELECT EMPNO,FIRSTNME,LASTNAM 01 01 0260 D DB2 MF000001 0260 01 _ 2 MULTI FETCH NEX 01 01 0260 D DB2 MF000001 0260 01 _ 3 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 4 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 5 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 6 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 7 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 8 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 9 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 10 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 11 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 12 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 13 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 14 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 15 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 16 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 _ 17 <BUFF FETCH NEX 00 00 0260 D DB2 MF000001 0260 01 Command ===> |
where column No represents the following:
1 | is a open cursor Db2 call. |
2 | is a "real" database call that reads a set of records via
multi-fetch (see MULTI FETCH NEX in column SQL
Statement).
|
3-17 |
are "no real" database calls, but only entries that
document that the program has received these records from the |
The feature allows programs to retrieve multiple rows from Db2 into arrays.
This feature is only available with the SELECT
statement.
To use this feature
Set the compiler option DB2ARRY=ON
(by using an OPTIONS
statement or the
COMPOPT
command or the CMPO
profile
parameter).
Specify a list of receiving arrays in the INTO
clause (see
into-clause) of the SELECT
statement.
Specify the number of rows to be retrieved from the database by a single
FETCH
operation via the WITH ROWSET POSITIONING
Clause.
Specify a variable receiving the number of rows retrieved from the database via
the ROWS_RETURNED
Clause.
DB2ARRY=ON
is
necessary to allow the specification of arrays in the INTO
clause (see
into-clause). DB2ARRY=ON
also
prevents the usage of arrays as sending or receiving fields for Db2
CHAR
/VARCHAR
/GRAPHIC
/VARGRAPHIC
columns. Instead Natural scalar fields with the appropriate length have to be
used.
Each array specified in the INTO
clause (see into-clause) has to be contiguous (one
occurrence following immediately by another, this is expected by Db2) and has to be
one-dimensional. The arrays are filled from the first occurrence (low) to last
occurrence (high). The first array occurrences compose the first row of the received
rowset, the second array occurrences compose the second row of the received rowset.
The array occurrences of the nth index compose the nth row returned from Db2. If an
LINDICATOR
Clause or INDICATOR
Clause is used in the INTO
clause for arrays,
the specified length indicators or null indicators have also to be arrays. The number
of occurrences of LINDICATOR
and INDICATOR
arrays have to
equal or greater than the number of occurrences of the master array.
The WITH_ROWSET_POSITIONING
Clause is used to specify the number of rows
to be retrieved from the database by one processing cycle. The specified number has to
be equal or smaller than the minimum of occurrences of all specified arrays. If a
variable, not a constant, is specified the actual content of the variable will be used
during each processing cycle. The specified number has to be greater 0
and smaller than 32768
.
The ROWS_RETURNED
Clause is used to specify a variable, which will
contain the number of rows read from the database during the actual fetch operation.
The format of the variable has to be I4.
Natural Views: It is not possible to use Natural arrays of views in the
INTO
clause (see into-clause), that is, the use of keyword
VIEW
is not possible.
The purpose of this feature is to reduce the number of database and database
interface calls for bulk batch processing. Therefore it is not recommended to use this
kind of programming in online CICS or IMS environments, when terminal I/Os occur
within open cursor loops; that is, the file server is used. A fortiori it is not
possible to perform a Positioned
UPDATE
or Positioned
DELETE
statement after terminal I/O.
Example:
DEFINE DATA LOCAL 01 NAME (A20/1:10) 01 ADDRESS (A100/1:10) 01 DATEOFBIRTH (A10/1:10) 01 SALARY (P4.2/1:10) 01 L$ADDRESS (I2/1:10) 01 ROWS (I4) 01 NUMBER (I4) 01 INDEX (I4) END-DEFINE OPTIONS DB2ARRY=ON ASSIGN NUMBER := 10 SEL. SELECT NAME, ADDRESS , DATEOFBIRTH, SALARY INTO :NAME(*), /* <-- ARRAY :ADDRESS(*) LINDICATOR :L$ADDRESS(*), /* <-- ARRAY :DATEOFBIRTH(1:10), /* <-- ARRAY :SALARY(01:10) /* <-- ARRAY FROM NAT-DEMO WHERE NAME > ' ' WITH ROWSET POSITIONING FOR :NUMBER ROWS /* <-- ROWS REQ ROWS_RETURNED :ROWS /* <-- ROWS RET IF ROWS > 0 FOR INDEX = 1 TO ROWS STEP 1 DISPLAY INDEX (EM=99) *COUNTER (SEL.) (EM=99) ROWS (EM=99) NAME(INDEX) ADDRESS(INDEX) (AL=20) DATEOFBIRTH(INDEX) SALARY(INDEX) END-FOR END-IF END-SELECT END
The feature allows programs to insert multiple rows into a Db2 table from arrays.
This feature is only available with the Natural SQL INSERT
statement.
To use this feature
Set the compiler option DB2ARRY=ON
(by using an OPTIONS
statement or the
COMPOPT
command or the CMPO
profile
parameter).
Specify a list of sending arrays in the VALUES
Clause of the Natural SQL INSERT
statement.
Specify the number of rows to be inserted into the database by a single Natural
SQL INSERT
statement via the FOR
n ROWS Clause.
DB2ARRY=ON
is
necessary to allow the specification of arrays in the VALUES
Clause. DB2ARRY=ON
also prevents the usage of
arrays as sending or receiving fields for Db2 CHAR
/VARCHAR
/GRAPHIC
/VARGRAPHIC
columns. Instead Natural scalar fields
with the appropriate length have to be used.
Each array specified in the VALUES
Clause has to be contiguous (one occurrence following
immediately by another, this is expected by Db2) and has to be one-dimensional. The
arrays are read from the first occurrence (low) to last occurrence (high). The first
array occurrences compose the first row inserted into the database, the second array
occurrences compose the second row inserted into the database. The array occurrences
of the nth index compose the nth row inserted into the database. If an LINDICATOR
Clause or INDICATOR
Clause is used in the VALUES Clause for
arrays, the specified length indicators or null indicators have also to be arrays. The
number of LINDICATOR
and INDICATOR
array occurrences has to
be equal or greater than the number of occurrences of the master array.
The FOR
n ROWS Clause is used to specify
how many rows are to be inserted into the database table by one INSERT
statement. The specified
number has to be equal or smaller than the minimum of occurrences of all specified
arrays in the VALUES Clause. The specified number has to be
greater than 0
and smaller than 32768.
It is not possible to use Natural arrays of views in the VALUES
Clause, that is, the use of keyword VIEW
is not
possible.
Due to Db2 restrictions it is not possible to execute multiple row inserts in
static mode. Therefore, multiple row inserts are not generated static and are
always dynamically prepared and executed by Natural for Db2. The Natural for Db2
static generation creates an assembler language SQL program, which is precompiled
by the Db2 precompiler, which in turn creates a DBRM necessary for static
execution. However, the Db2 assembler precompiler has no support for host variable
arrays. They can only be used when specified in a SQLDA structure, which has to be
build at execution time. But a static INSERT
with a
multiple-row-insert VALUES
clause does not allow the specification of
an SQLDA, but only host-variable arrays, which are not supported by the Db2
assembler precompiler.
It is not possible to use Natural arrays of views in the INTO
clause
(see into-clause), that is, the use of
keyword VIEW
is not possible.
Example:
DEFINE DATA LOCAL 01 NAME (A20/1:10) INIT <'ZILLER1','ZILLER2','ZILLER3','ZILLER4' ,'ZILLER5','ZILLER6','ZILLER7','ZILLER8' ,'ZILLER9','ZILLERA'> 01 ADDRESS (A100/1:10) INIT <'ANGEL STREET 1','ANGEL STREET 2' ,'ANGEL STREET 3','ANGEL STREET 4' ,'ANGEL STREET 5','ANGEL STREET 6' ,'ANGEL STREET 7','ANGEL STREET 8' ,'ANGEL STREET 9','ANGEL STREET 10'> 01 DATENATD (D/1:10) INIT <D'1954-03-27',D'1954-03-27',D'1954-03-27' ,D'1954-03-27',D'1954-03-27',D'1954-03-27' ,D'1954-03-27',D'1954-03-27',D'1954-03-27' ,D'1954-03-27'> 01 SALARY (P4.2/1:10) INIT <1000,2000,3000,4000,5000 ,6000,7000,8000,9000,9999> 01 SALARY_N (N4.2/1:10) INIT <1000,2000,3000,4000,5000 ,6000,7000,8000,9000,9999> 01 L§ADDRESS (I2/1:10) INIT <14,14,14,14,14,14,14,14,14,15> 01 N§ADDRESS (I2/1:10) INIT <00,00,00,00,00,00,00,00,00,00> 01 ROWS (I4) 01 INDEX (I4) 01 V1 VIEW OF NAT-DEMO_ID 02 NAME 02 ADDRESS (EM=X(20)) 02 DATEOFBIRTH 02 SALARY 01 ROWCOUNT (I4) END-DEFINE OPTIONS DB2ARRY=ON /* <-- ENABLE DB2 ARRAY ROWCOUNT := 10 INSERT INTO NAT-DEMO_ID (NAME,ADDRESS,DATEOFBIRTH,SALARY) VALUES (:NAME(*), /* <-- ARRAY :ADDRESS(*) /* <-- ARRAY INDICATOR :N§ADDRESS(*) /* <-- ARRAY LINDICATOR :L§ADDRESS(*), /* <-- ARRAY DB2 VCHAR :DATENATD(1:10), /* <-- ARRAY NATURAL DATES :SALARY_N(01:10) /* <-- ARRAY NATURAL NUMERIC ) FOR :ROWCOUNT ROWS SELECT * INTO VIEW V1 FROM NAT-DEMO_ID WHERE NAME > 'Z' DISPLAY V1 /* <-- VERIFY INSERT END-SELECT BACKOUT END
In contrast to the normal Natural error handling, where either an ON ERROR
statement is used to intercept
execution time errors or standard error message processing is performed and program
execution is terminated, the enhanced error handling of Natural for Db2 provides an
application controlled reaction to the encountered SQL error.
Two Natural subprograms, NDBERR
and NDBNOERR
, are provided to disable the usual Natural error
handling and to check the encountered SQL error for the returned SQLCODE. This
functionality replaces the E
function of the DB2SERV
interface,
which is still provided but no longer documented.
For further information on Natural subprograms provided for Db2, see the section Interface Subprograms.
Example:
DEFINE DATA LOCAL 01 #SQLCODE (I4) 01 #SQLSTATE (A5) 01 #SQLCA (A136) 01 #DBMS (B1) END-DEFINE * * Ignore error from next statement * CALLNAT 'NDBNOERR' * * This SQL statement produces an SQL error * INSERT INTO SYSIBH-SYSTABLES (CREATOR, NAME, COLCOUNT) VALUES ('SAG', 'MYTABLE', '3') * * Investigate error * CALLNAT 'NDBERR' #SQLCODE #SQLSTATE #SQLCA #DBMS * IF #DBMS NE 2 /* not DB2 MOVE 3700 TO *ERROR-NR END-IF * DECIDE ON FIRST VALUE OF #SQLCODE VALUE 0, 100 /* successful execution IGNORE VALUE -803 /* duplicate row /* UPDATE existing record /* IGNORE NONE VALUE MOVE 3700 TO *ERROR-NR END-DECIDE * END