Several Natural and non-Natural subprograms are available to provide you with internal information from Natural for Db2 or specific functions for which no equivalent Natural statements exist.
This section covers the following topics:
The following Natural subprograms are provided:
Subprogram | Function |
---|---|
NDBCONV |
Sets or resets conversational mode 2. |
NDBDBRM |
Checks whether a Natural program contains SQL access and whether it has been modified for static execution. |
NDBDBR2 |
Checks whether a Natural program contains SQL access and whether it has been modified for static execution. |
NDBDBR3 |
Checks whether a Natural program contains SQL access, whether it has been modified for static execution, and whether it can be generated as static. |
NDBDBRZ |
Checks whether a Natural program contains SQL access, whether it has been modified for NDZ static execution, and whether it can be generated as static. |
NDBERR |
Provides diagnostic information on the most recently executed SQL call. |
NDBISQL |
Executes SQL statements in dynamic mode. |
NDBISQLD |
Executes SQL statements in dynamic mode, using dynamic variables. |
NDBNOERR |
Suppresses normal Natural error handling. |
NDBNROW |
Obtains the number of rows affected by a Natural SQL statement. |
NDBSTMP |
Provides a Db2 TIMESTAMP column as an alphanumeric field and
vice versa.
|
All these subprograms are provided in the Natural system library SYSDB2
and
the Natural library SYSTEM
on the system file FNAT
.
In addition, the Natural library SYSTEM
in the FNAT
system file
contains the subprogram DBTLIB2N
and the subroutine DBDL219S
.
They are used by NDBDBRM
and NDBDBR2
. The corresponding
parameters must be defined in a DEFINE DATA
statement.
The Natural subprograms NDBDBRM
, NDBDBR2
and
NDBDBR3
allow the optional specification of the database ID, file number,
password and cipher code of the library file containing the program to be examined.
If these parameters are not specified, either the actual FNAT
file or the
FUSER
file is used to locate the program to be examined depending on
whether the library name begins with "SYS" or not.
Programs invoking NDBDBRM
, NDBDBR2
or NDBDBR3
without these parameters will also work like before this change as the added parameters
are declared as optional.
For detailed information on these subprograms, follow the links shown in the table above
and read the description of the call format and of the parameters in the text object
provided with the subprogram (subprogram-nameT
).
Natural subprograms are invoked with the Natural CALLNAT
statement.
Non-Natural subprograms are invoked with the Natural CALL
statement.
The Natural subprogram NDBCONV
is used to either set or reset the
conversational mode 2 in CICS environments. Conversational mode 2 means that update
transactions are spawned across terminal I/Os until either a COMMIT
or
ROLLBACK
has been issued (Caution Db2 and CICS resources are kept across
terminal I/Os!). This means conversational mode 2 has the same effect as the Natural
profile parameter PSEUDO=OFF
,
except that the conversational mode is entered after an Db2 update statement
(UPDATE
, DELETE
, INSERT
) and left again after a
COMMIT
or ROLLBACK
, while PSEUDO=OFF
causes
conversational mode for the total Natural session.
A sample program called CALLCONV
is provided in library
SYSDB2
; it demonstrates how to invoke NDBCONV
. A description of
the call format and of the parameters is provided in the text object
NDBCONVT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBCONV' #CONVERS #RESPONSE
The various parameters are described in the following table:
Parameter | Format/Length | Explanation |
---|---|---|
#CONVERS |
I1 | Contains the desired conversational mode (input) |
#RESPONSE |
I4 | Contains the response of NDBCONV (output)
|
The #CONVERS
parameter can contain the following values:
Code | Explanation |
---|---|
0 |
The conversational mode 2 has to be reset. |
1 |
The conversational mode 2 has to be set. |
The #RESPONSE
parameter can contain the following response
codes:
Code | Explanation |
---|---|
0 |
The conversational mode 2 has been successfully set or reset. |
-1 |
The specified value of #CONVERS is invalid, the
conversational mode has not been changed.
|
-2 |
NDBCONV is called in a environment, which is not a CICS
environment, where the conversational mode 2 is not supported.
|
The Natural subprogram NDBDBRM
is used to check whether a Natural program
contains SQL access and whether it has been modified for static execution. It is also used
to obtain the corresponding DBRM (database request module) name from the header of a
Natural program generated as static (see also Preparing Programs for Static Execution).
A sample program called CALLDBRM
is provided on the installation medium; it
demonstrates how to invoke NDBDBRM
. A description of the call format and of
the parameters is provided in the text object NDBDBRMT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBDBRM' #LIB #MEM #DBRM #RESP #DBID #FILENR #PASSWORD #CIPHER
The various parameters are described in the following table:
Parameter | Format/Length | Explanation |
---|---|---|
#LIB |
A8 | Contains the name of the library of the program to be checked. |
#MEM |
A8 | Contains the name of the program (member) to be checked. |
#DBRM |
A8 | Returns the DBRM name. |
#RESP |
I2 | Returns a response code. The possible codes are listed below. |
#DBID |
N5 | Optional. Database ID of library file. |
#FILENR |
N5 | Optional. File number of library file. |
#PASSWORD |
A8 | Optional. Password of library file. |
#CIPHER |
N8 | Optional. Cipher code of library file. |
The #RESP
parameter can contain the following values:
Code | Explanation |
---|---|
0 |
The member #MEM in library #LIB has SQL access; it
is static if #DBRM contains a value.
|
-1 |
The member #MEM in library #LIB has no SQL access.
|
-2 |
The member #MEM in library #LIB does not exist.
|
-3 |
No library name has been specified. |
-4 |
No member name has been specified. |
-5 |
The library name must start with a letter. |
>-5 |
Further negative response codes correspond to error numbers of Natural error messages. |
>0 |
Positive response codes correspond to error numbers of Natural Security messages. |
The Natural subprogram NDBDBR2
is used to check whether a Natural program
contains SQL access and whether it has been modified for static execution. It is also used
to obtain the corresponding DBRM (database request module) name from the header of a
Natural program generated as static (see also Preparing Programs for Static Execution) and the time stamp generated by the
precompiler.
A sample program called CALLDBR2
is provided on the installation medium; it
demonstrates how to invoke NDBDBR2
. A description of the call format and of
the parameters is provided in the text object NDBDBR2T
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBDBR2' #LIB #MEM #DBRM #TIMESTAMP #PCUSER #PCRELLEV #ISOLLEVL #DATEFORM #TIMEFORM #RESP #DBID #FILENR #PASSWORD #CIPHER
The various parameters are described in the following table:
Parameter | Format/Length | Explanation |
---|---|---|
#LIB |
A8 | Contains the name of the library of the program to be checked. |
#MEM |
A8 | Contains the name of the program (member) to be checked. |
#DBRM |
A8 | Returns the DBRM name. |
#TIMESTAMP |
B8 | Consistency token generated by precompiler. |
#PCUSER |
A8 | Unsupported parameter; retained for compatibility reasons. only. |
#PCRELLEV |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#ISOLLEVL |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#DATEFORM |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#TIMEFORM |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#RESP |
I2 | Returns a response code. The possible codes are listed below. |
#DBID |
N5 | Optional. Database ID of library file. |
#FILENR |
N5 | Optional. File number of library file. |
#PASSWORD |
A8 | Optional. Password of library file. |
#CIPHER |
N8 | Optional. Cipher code of library file. |
The #RESP
parameter can contain the following values:
Code | Explanation |
---|---|
0 |
The member #MEM in library #LIB has SQL access; it
is static if #DBRM contains a value.
|
-1 |
The member #MEM in library #LIB has no SQL access.
|
-2 |
The member #MEM in library #LIB does not exist.
|
-3 |
No library name has been specified. |
-4 |
No member name has been specified. |
-5 |
The library name must start with a letter. |
>-5 |
Further negative response codes correspond to error numbers of Natural error messages. |
>0 |
Positive response codes correspond to error numbers of Natural Security messages. |
The Natural subprogram NDBDBR3
is used to check whether a Natural program
contains SQL access (#RESP 0
), whether the Natural program contains solely
SQL statements, which are dynamically executable (#RESP 0
, #DBRM
‘*DYNAMIC’
) and whether it has been modified for static execution (#RESP
0
, #DBRM dbrmname
). It is also used to
obtain the corresponding DBRM (database request module) name from the header of a Natural
program generated as static (see also Preparing
Programs for Static Execution) and the time stamp generated by the
precompiler.
A sample program called CALLDBR3
is provided on the installation medium; it
demonstrates how to invoke NDBDBR3
. A description of the call format and of
the parameters is provided in the text object NDBDBR3T
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBDBR3' #LIB #MEM #DBRM #TIMESTAMP #PCUSER #PCRELLEV #ISOLLEVL #DATEFORM #TIMEFORM #RESP #DBID #FILENR #PASSWORD #CIPHER
The various parameters are described in the following table:
Parameter | Format/Length | Explanation |
---|---|---|
#LIB |
A8 | Contains the name of the library of the program to be checked. |
#MEM |
A8 | Contains the name of the program (member) to be checked. |
#DBRM |
A8 | Returns the DBRM name.
|
#TIMESTAMP |
B8 | Consistency token generated by precompiler. |
#PCUSER |
A8 | Unsupported parameter; retained for compatibility reasons only. |
#PCRELLEV |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#ISOLLEVL |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#DATEFORM |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#TIMEFORM |
A1 | Unsupported parameter; retained for compatibility reasons only. |
#RESP |
I2 | Returns a response code. The possible codes are listed below. |
#DBID |
N5 | Optional. Database ID of library file. |
#FILENR |
N5 | Optional. File number of library file. |
#PASSWORD |
A8 | Optional. Password of library file. |
#CIPHER |
N8 | Optional. Cipher code of library file. |
The #RESP
parameter can contain the following values:
Code | Explanation |
---|---|
0 |
The member #MEM in library #LIB has SQL access; it
is static if #DBRM contains a value other than space and
*DYNAMIC .
|
-1 |
The member #MEM in library #LIB has no SQL access.
|
-2 |
The member #MEM in library #LIB does not exist.
|
-3 |
No library name has been specified. |
-4 |
No member name has been specified. |
-5 |
The library name must start with a letter. |
>-5 |
Further negative response codes correspond to error numbers of Natural error messages. |
>0 |
Positive response codes correspond to error numbers of Natural Security messages. |
The Natural subprogram NDBDBRZ
is used to check whether a Natural program
contains SQL access (#RESP 0
), whether the Natural program contains solely
SQL statements, which are dynamically executable (#RESP 0
, #SQLJPROF
‘*DYNAMIC’
) and whether it has been modified for NDZ static execution
(#RESP 0
, #SQLJPROF SQLJ profile
name
). It is also used to obtain the corresponding SQLJ profile
name from the header of a Natural program generated as static (see also Preparing Programs for Static Execution).
A sample program called CALLDBRZ
is provided on the installation medium and
it demonstrates how to invoke NDBDBRZ
. A description of the call format and
of the parameters is provided in the text object NDBDBRZT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBDBRZ' #LIB #MEM #SQLJPROF #RESP #DBID #FILENR #PASSWORD #CIPHER
The various parameters are described in the following table:
Parameter | Format/Length | Explanation |
---|---|---|
#LIB |
A8 | Contains the name of the library of the program to be checked. |
#MEM |
A8 | Contains the name of the program (member) to be checked. |
#SQLJPROF |
A8 | Returns the SQLJ profile name.
|
#RESP |
I2 | Returns a response code. The possible codes are listed below. |
#DBID |
N5 | Optional. Database ID of library file. |
#FILENR |
N5 | Optional. File number of library file. |
#PASSWORD |
A8 | Optional. Password of library file. |
#CIPHER |
N8 | Optional. Cipher code of library file. |
The #RESP
parameter can contain the following values:
Code | Explanation |
---|---|
0 |
The member #MEM in library #LIB has SQL access; it
is static if #DBRM contains a value other than space and
*DYNAMIC .
|
-1 |
The member #MEM in library #LIB has no SQL access.
|
-2 |
The member #MEM in library #LIB does not exist.
|
-3 |
No library name has been specified. |
-4 |
No member name has been specified. |
-5 |
The library name must start with a letter. |
>-5 |
Further negative response codes correspond to error numbers of Natural error messages. |
>0 |
Positive response codes correspond to error numbers of Natural Security messages. |
The Natural subprogram NDBERR
replaces Function E
of the
DB2SERV
interface, which is still provided but no longer documented. It
provides diagnostic information on the most recent SQL call. It also returns the database
type which returned the error. NDBERR
is typically called if a database call
returns a non-zero SQLCODE (which means a NAT3700 error).
A sample program called CALLERR
is provided on the installation medium; it
demonstrates how to invoke NDBERR
. A description of the call format and of
the parameters is provided in the text object NDBERRT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBERR' #SQLCODE #SQLSTATE #SQLCA #DBTYPE
The various parameters are described in the following table:
Parameter | Format/Length | Explanation |
---|---|---|
#SQLCODE |
I4 | Returns the SQL return code. |
#SQLSTATE |
A5 | Returns a return code for the output of the most recently executed SQL statement. |
#SQLCA |
A136 | Returns the SQL communication area of the most recent Db2 access. |
#DBTYPE |
B1 | Returns the identifier (in hexadecimal format) for the currently used
database (where X'02' identifies Db2).
|
The Natural subprogram NDBISQL
is used to execute SQL statements in dynamic
mode. The SELECT
statement and all SQL statements which can be prepared
dynamically (according to the Db2 literature by IBM) can be passed to
NDBISQL
.
A sample program called CALLISQL
is provided on the installation medium; it
demonstrates how to invoke NDBISQL
. A description of the call format and of
the parameters is provided in the text object NDBISQLT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBISQL'#FUNCTION #TEXT-LEN #TEXT (*) #SQLCA #RESPONSE #WORK-LEN #WORK (*)
The various parameters are described in the following table:
Parameter | Format/Length | Explanation | |
---|---|---|---|
#FUNCTION |
A8 | For valid functions, see below. | |
#TEXT-LEN |
I2 | Length of the SQL statement or of the buffer for the return area. | |
#TEXT |
A1(1:V) | Contains the SQL statement
(EXECUTE ) or receives a data row (FETCH ).
|
|
#SQLCA |
A136 | Contains the SQLCA. | |
#RESPONSE |
I4 | Returns a response code. | |
#WORK-LEN |
I2 | Length of the workarea specified by
#WORK (optional).
|
|
#WORK |
A1(1:V) | Workarea used to hold SQLDA/SQLVAR and auxiliary fields across calls (optional). | |
#DBTYPE |
I2 | Database type (optional). | |
0 |
Default | ||
2 |
DB2 | ||
4 |
CNX |
Valid functions for the #FUNCTION
parameter are:
Function | Parameter | Explanation |
---|---|---|
CLOSE |
Closes the cursor for the SELECT statement.
|
|
EXECUTE |
#TEXT-LEN |
Executes the SQL statement. |
FETCH |
#TEXT-LEN |
Returns a record from the |
TITLE |
|
Returns the header for the |
The #RESPONSE
parameter can contain the following response
codes:
Code | Function | Explanation |
---|---|---|
5 |
EXECUTE |
The statement is a SELECT statement.
|
6 |
TITLE , FETCH |
Data are truncated; only set on first TITLE or
FETCH call.
|
100 |
FETCH |
No record / end of data. |
-2 |
Unsupported data type (for example, GRAPHIC ).
|
|
-3 |
TITLE , FETCH |
No cursor open; |
-4 |
Too many columns in result table. | |
-5 |
SQLCODE from call. | |
-6 |
Version mismatch. | |
-7 |
Invalid function. | |
-8 |
Error from SQL call. | |
-9 |
Workarea invalid (possibly relocation). | |
-10 |
Interface not available. | |
-11 |
EXECUTE |
First two bytes of statement not blank. |
The first call must be an EXECUTE
call. NDBISQL
has a fixed
SQLDA AREA holding space for 50 columns. If this area is too small for a particular
SELECT
it is possible to supply an optional work area on the calls to
NDBISQL
by specifying #WORK-LEN (I2)
and
#WORK(A1/1:V)
.
This workarea is used to hold the SQLDA and temporary work fields like null indicators
and auxiliary fields for numeric columns. Calculate 16 bytes for SQLDA header and 44 bytes
for each result column and 2 bytes null indicator for each column and place for each
numeric column, when supplying #WORK-LEN
and
#WORK(*)
during NDBISQL
calls. If these optional
parameters are specified on an EXECUTE
call they have also to be specified on
any following call.
If the statement is a SELECT
statement (that is, response code 5
is
returned), any sequence of TITLE
and FETCH
calls can be used to
retrieve the data. A response code of 100
indicates the end of the data.
The cursor must be closed with a CLOSE
call.
Function code EXECUTE
implicitly closes a cursor which has been opened by a
previous EXECUTE
call for a SELECT
statement.
In TP environments, no terminal I/O can be performed between an EXECUTE
call
and any TITLE
, FETCH
or CLOSE
call that refers to
the same statement.
The Natural subprogram NDBISQLD
is used to execute SQL statements in dynamic
mode. The SELECT
statement and all SQL statements which can be prepared
dynamically (according to the Db2 literature by IBM) can be passed to
NDBISQLD
.
A sample program called CALISQLD
is provided on the installation medium. It
demonstrates how to invoke NDBISQLD
. A description of the call format and of
the parameters is provided in the text object ISQLDT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBISQLD'#FUNCTION #TEXT #SQLCA #RESPONSE #WORK #DBTYPE
The various parameters are described in the following table:
Parameter | Format/Length | Explanation | |
---|---|---|---|
#FUNCTION |
A8 | For valid functions, see below. | |
#TEXT |
A DYNAMIC | Contains the SQL statement
(EXECUTE ) or receives the data row (FETCH ).
|
|
#SQLCA |
A136 | Contains the SQLCA. | |
#RESPONSE |
I4 | Returns a response code. | |
#WORK |
A DYNAMIC | Workarea used to hold SQLDA/SQLVAR and auxiliary
fields across calls (optional).
If specified, |
|
#DBTYPE |
I2 | Database type (optional). | |
0 |
Default | ||
2 |
DB2 | ||
4 |
CNX |
Valid functions for the #FUNCTION
parameter are:
Function | Parameter | Explanation |
---|---|---|
CLOSE |
- | Closes the cursor for the SELECT statement.
|
EXECUTE |
#TEXT |
Executes the SQL statement. |
FETCH |
#TEXT |
Returns a row from the SELECT statement.
After |
TITLE |
#TEXT |
Returns the header literals for the SELECT statement.
|
The #RESPONSE
parameter can contain the following response
codes:
Code | Function | Explanation |
---|---|---|
5 |
EXECUTE |
The statement is a SELECT statement.
|
6 |
TITLE , FETCH |
Data are truncated; only set on first TITLE or
FETCH call.
|
100 |
FETCH |
No record/end of data. |
-2 |
- | Unsupported data type (for example, GRAPHIC ).
|
-3 |
TITLE , FETCH |
No cursor open. |
-4 |
- | Too many columns in result table. |
-5 |
- | SQLCODE from call. |
-6 |
- | Version mismatch. |
-7 |
- | Invalid function. |
-8 |
- | Error from SQL call. |
-9 |
- | Workarea invalid (possibly relocation). |
-10 |
- | Interface not available. |
-11 |
EXECUTE |
First two bytes of statement not blank. |
The first call must be an EXECUTE
call. NDBISQLD
has a fixed
SQLDA AREA, holding space for 50 columns. If this area is too small for a particular
SELECT
, it is possible to supply an optional work area on the calls to
NDBISQLD
by #WORK(A)DYNAMIC
.
This workarea is used to hold the SQLDA and temporary work fields like null indicators
and auxiliary fields for numeric columns. Calculate 16 bytes for SQLDA header and 44 bytes
for each result column and 2 bytes null indicator for each column and place for each
numeric column, when supplying #WORK(A)DYNAMIC
during NDBISQLD
calls. If these optional parameters are specified on an EXECUTE
call, they
have also to be specified on any following call.
If the statement is a SELECT
statement (that is, response code 5 is
returned), any sequence of TITLE
and FETCH
calls can be used to
retrieve the data. A response code of 100
indicates the end of the data.
The cursor must be closed with a CLOSE
call.
Function code EXECUTE
implicitly closes a cursor which has been opened by a
previous EXECUTE
call for a SELECT
statement.
In TP environments, no terminal I/O can be performed between an EXECUTE
call
and any TITLE
, FETCH
or CLOSE
call that refers to
the same statement.
The Natural subprogram NDBNOERR
is used to suppress Natural NAT3700 errors
caused by the next SQL call. This allows a program controlled continuation if an SQL
statement produces a non-zero SQLCODE. After the SQL call has been performed, NDBERR
is used to investigate the
SQLCODE.
A sample program called CALLNOER
is provided on the installation medium; it
demonstrates how to invoke NDBNOERR
. A description of the call format and of
the parameters is provided in the text object NDBNOERT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBNOERR'
There are no parameters provided with this subprogram.
Note:
Only NAT3700 errors (that is, non-zero SQL response codes) are suppressed, and also
only errors caused by the next following SQL call.
If NDBNOERR
is called before a statement that initiates a database loop
and an initialization error occurs, no processing loop will be initiated, unless a
IF NO RECORDS FOUND
clause has been specified.
If NDBNOERR
is called within a database loop, it does not apply to the
processing loop itself, but only to the SQL statement subsequently executed inside
this loop.
The Natural subprogram NDBNROW
is used to obtain the number of rows affected
by the Natural SQL statements Searched UPDATE
, Searched DELETE
,
and INSERT
. The number of rows affected is read from the SQL communication
area (SQLCA). A positive value represents the number of affected rows, whereas a value of
minus one (-1
) indicates that all rows of a table in a segmented tablespace
have been deleted; see also the Natural system variable *NUMBER
as
described in the Natural System Variables documentation.
A sample program called CALLNROW
is provided on the installation medium; it
demonstrates how to invoke NDBNROW
. A description of the call format and of
the parameters is provided in the text object NDBNROWT
.
The calling Natural program must use the following syntax:
CALLNAT 'NDBNROW' #NUMBER
The parameter #NUMBER
(I4) contains the number of affected
rows.
For Db2, Natural provides a TIMESTAMP
column as an alphanumeric field (A26)
of the format YYYY-MM-DD-HH.MM.SS.MMMMMM
.
Since Natural does not yet support computation with such fields, the Natural subprogram
NDBSTMP
is provided to enable this kind of functionality. It converts
Natural time variables to Db2 time stamps and vice versa and performs Db2 time stamp
arithmetics.
A sample program called CALLSTMP
is provided on the installation medium; it
demonstrates how to invoke NDBSTMP
. A description of the call format and of
the parameters is provided in the text object NDBSTMPT
.
The functions available are:
Code | Explanation |
---|---|
ADD |
Adds time units (labeled durations) to a given Db2 time stamp and returns a Natural time variable and a new Db2 time stamp. |
CNT2 |
Converts a Natural time variable (format T) into a Db2 time stamp (column
type TIMESTAMP ) and labeled durations.
|
C2TN |
Converts a Db2 time stamp (column type TIMESTAMP ) into a Natural
time variable (format T) and labeled durations.
|
DIFF |
Builds the difference between two given Db2 time stamps and returns labeled durations. |
GEN |
Generates a Db2 time stamp from the current date and time values of the
Natural system variable *TIMX and returns a new Db2 time
stamp.
|
SUB |
Subtracts labeled durations from a given Db2 time stamp and returns a Natural time variable and a new Db2 time stamp. |
TEST |
Tests a given Db2 time stamp for valid format and returns TRUE
or FALSE .
|
Note:
Labeled durations are units of year, month, day, hour, minute, second and
microsecond.
DB2SERV
is an Assembler program entry point which can be called from within
a Natural program.
DB2SERV
performs either of the following functions:
Function D, which
performs the SQL statement EXECUTE IMMEDIATE
.
Function P, invokes an
Assembler module named NDBPLAN
.
The parameter or variable values returned by each of these functions are checked for their format, length, and number.
Function D
performs the SQL statement EXECUTE IMMEDIATE
. This
allows SQL statements to be issued from within a Natural program.
The SQL statement string that follows the EXECUTE IMMEDIATE
statement must
be assigned to the Natural program variable STMT
. It must
contain valid SQL statements allowed with the EXECUTE IMMEDIATE
statement
as described in the relevant IBM literature. Examples can be found below and in the
demonstration programs DEM2*
in the Natural system library
SYSDB2
.
Note:
The conditions that apply to issuing the Natural END TRANSACTION
or
BACKOUT TRANSACTION
statements also apply when issuing the SQL
COMMIT
or ROLLBACK
statements.
CALL 'DB2SERV' 'D' STMT STMTL SQLCA RETCODE
The variables used in this command are described in the following table:
Variable | Format/Length | Explanation | ||||||||
---|---|---|---|---|---|---|---|---|---|---|
STMT |
Annn |
Contains a command string which consists of SQL syntax as described above. | ||||||||
STMTL |
I2 | Contains the length of the string defined in
STMT .
|
||||||||
SQLCA |
A136 | Returns the current contents of the SQL communication area. | ||||||||
RETCODE |
I2 | Returns an interface return code. The following codes are possible:
|
The current contents of the SQLCA and an interface return code (RETCODE) are returned. The SQLCA is a collection of variables that are used by Db2 to provide an application program with information on the execution of its SQL statements.
The following two examples show you how to use DB2SERV
with Function
D
.
************************************************************************** * DEM2CREA - CREATE TABLE NAT.DEMO * ************************************************************************** * DEFINE DATA LOCAL USING DEMSQLCA LOCAL * Parameters for DB2SERV 1 STMT (A250) 1 STMTL (I2) CONST <250> 1 RETCODE (I2) * END-DEFINE * COMPRESS 'CREATE TABLE NAT.DEMO' '(NAME CHAR(20) NOT NULL,' ' ADDRESS VARCHAR(100) NOT NULL,' ' DATEOFBIRTH DATE NOT NULL,' ' SALARY DECIMAL(6,2),' ' REMARKS VARCHAR(500))' INTO STMT CALL 'DB2SERV' 'D' STMT STMTL SQLCA RETCODE * END TRANSACTION * IF RETCODE = 0 WRITE 'Table NAT.DEMO created' ELSE FETCH 'SQLERR' END-IF END **************************************************************************
Note:
The functionality of the DB2SERV
Function D
is also
provided with the PROCESS SQL
statement.
************************************************************************** * DEM2SET - Set Current SQLID * ************************************************************************** * DEFINE DATA LOCAL USING DEMSQLCA LOCAL * Parameter for DB2SERV 1 STMT (A250) 1 STMTL (I2) CONST <250> 1 RETCODE (I2) 1 OLDSQLID (A8) 1 NEWSQLID (A8) * END-DEFINE * SELECT DISTINCT CURRENT SQLID INTO OLDSQLID FROM SYSIBM.SYSTABLES ESCAPE BOTTOM END-SELECT * MOVE 'SET CURRENT SQLID="PROD"'; TO STMT CALL 'DB2SERV' 'D' STMT STMTL SQLCA RETCODE * IF RETCODE > 0 FETCH 'SQLERR' ELSE SELECT DISTINCT CURRENT SQLID INTO NEWSQLID FROM SYSIBM.SYSTABLES ESCAPE BOTTOM END-SELECT * WRITE ' Old SQLID was :' OLDSQLID WRITE ' New SQLID is :' NEWSQLID END-IF * END **************************************************************************
When using 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. Thus, table names
must not be qualified by a creator name if this is to be substituted by the
SQLID
.
In all supported TP-monitor environments, the SQLID
can then be kept
across terminal I/Os until either the end of the session or its resetting via
DB2SERV
.
Function P
invokes an Assembler module named NDBPLAN
, which
is used to establish and/or terminate the Db2 connection under TSO and in batch mode.
This allows a Natural application to perform plan switching under TSO and in batch
mode.
The program DEM2PLAN
is an example of the use of DB2SERV
with
Function P
.
The name of the current Db2 subsystem (#SSM
) and the name of the new
application plan (#PLAN
) must be specified. In addition, an interface
return code (#RETCODE
) and the Db2 reason code (#REASON
) are
returned.
CALL 'DB2SERV' 'P' #SSM #PLAN #RETCODE #REASON
Variable | Format/Length | Explanation | ||||||||
---|---|---|---|---|---|---|---|---|---|---|
#SSM |
A4 | Contains the name of the current Db2 subsystem. | ||||||||
#PLAN |
A8 | Contains the new plan name. | ||||||||
#RETCODE |
Returns an interface return code. The following codes are possible:
|
|||||||||
#REASON |
I4 | Returns the reason code of the CAF interface (see also the relevant Db2 literature by IBM). |
************************************************************************** * DEM2PLAN - Switch application plan under TSO/Batch with CAF interface * ************************************************************************** * DEFINE DATA LOCAL * Parameter for DB2SERV 01 #SSM (A4)) CONST <'DB2'> 01 #PLAN (A8 01 #RETCODE (I2) 01 #REASON (I4) * END-DEFINE * INPUT 'PLEASE ENTER NEW PLAN NAME' #PLAN (AD='_'I) * END TRANSACTION * CALL 'DB2SERV' 'P' #SSM #PLAN #RETCODE #REASON * DECIDE FOR FIRST VALUE OF #RETCODE * VALUE 0 IGNORE VALUE 99 INPUT 12/23 'This is not a CAF environment !!' VALUE 8,12 INPUT 12/18 'New plan not scheduled, reason code' #REASON (AD=OI EM=H(4)) NONE INPUT 12/15 'CAF interface error' #RETCODE (AD=OI EM=Z(3)) 'with reason code' #REASON (AD=OI EM=H(4)) * END-DECIDE * END **************************************************************************
Important:
Plan switching under TSO and in batch mode is possible with the CAF
interface only; see also the section Plan Switching under TSO and in Batch
Mode.