This Natural profile parameter is used to specify the parameters for
the database management interfaces
Natural for
DB2 and
Natural SQL
Gateway. It corresponds to the
NTDB2 macro
in the Natural parameter module.
| Possible settings | See DB2 Parameter Syntax. | |
|---|---|---|
| Default setting | See Keyword Subparameters. | |
| Dynamic specification | yes | The parameter DB2 can only be
specified dynamically. In the
Natural
parameter module, use the macro
NTDB2.
|
| Specification within session | no | |
The following topics are covered:
The DB2 parameter is specified as
follows:
| DB2=(keyword-subparameter=value,keyword-subparameter=value,...) |
The NTDB2 macro is specified as follows:
NTDB2 BTIGN=value, *
CONVERS=value, *
CONVRS2=value, *
DB2COLL=value, *
DB2GROV=value, *
DB2PLAN=value, *
DB2SSID=value, *
DB2XID=value, *
DDFSERV=value, *
DELIMID=value, *
EBPFSRV=value, *
EBPMAX=value, *
EBPPRAL=value, *
EBPSEC=value, *
ETIGN=value, *
FSERV=value, *
MAXLOOP=value, *
MF=value, *
MAXSTMT=value, *
NNPSF=value, *
NSBDATE=value, *
NSBHOST=value, *
NSBPORT=value, *
PSCIGN=value, *
REFRESH=value, *
RETRYPO=value, *
RWRDONL=value, *
SMFSRV=value, *
STATDYN=value
There are two groups of keyword subparameters:
BTIGN
| CONVERS
| CONVRS2 |
DDFSERV |
DELIMID |
EBPFSRV |
EBPPRAL |
EBPSEC |
EBPMAX |
ETIGN |
FSERV |
MAXLOOP |
MAXSTMT |
MF |
NNPSF |
NSBDATE |
NSBHOST |
NSBPORT |
PSCIGN |
REFRESH |
RETRYPO |
RWRDONL |
SMFSRV |
STATDYN
DB2COLL |
DB2GROV |
DB2PLAN |
DB2SSID |
DB2XID
Notes:
NATPLAN program.
These parameters apply only to DB2 for z/OS and to environments where either
the DB2 Call Attachment Facility (CAF) or the DB2 Resource Recovery Services
Attachment Facility (RRSAF) is used. An exception to this is the Natural for
DB2 stored procedure environment, where DB2 already provides the DB2 resources
based on the stored procedure creation parameter COLLID.
Therefore, the keyword subparameters mentioned here are not used in a Natural
for DB2 stored procedure environment. Before the very first DB2 SQL access is
performed by Natural for DB2 in a CAF or RRSAF environment, Natural connects to
the desired DB2 subsystem (DB2SSID), and the desired plan
(DB2PLAN) is allocated.
NATPLAN before the first SQL request, the
NTDB2 or DB2 parameters are
ignored and the connection already established is used.
BTIGN=value enables you to
ignore the error which results from a BACKOUT TRANSACTION statement
that was issued too late for backing out the current transaction, because an
implicit Syncpoint has previously been issued by the TP monitor.
| Value | Explanation |
|---|---|
ON |
The error after a late BACKOUT
TRANSACTION is ignored.
This is the default value. |
OFF |
The error after a late BACKOUT
TRANSACTION is not ignored.
|
Notes:
CONVERS=value is used to
allow conversational mode in CICS environments where no Natural file server is
used.
| Value | Explanation |
|---|---|
ON |
Conversational mode is allowed.
This is the default value. |
OFF |
Conversational mode is not allowed. |
Notes:
OFF and no
Natural file server is used, you cannot continue database loops across terminal
I/O; if so, the following codes may occur.
CONVERS=ON, otherwise the error
mentioned above can occur.
CONVRS2=value
allows/disallows the conversational mode 2 in CICS environments.
| Value | Explanation |
|---|---|
ON |
Conversational mode 2 is allowed. |
OFF |
Conversational mode 2 is not
allowed.
This is the default value. |
Notes:
COMMIT or
explicit ROLLBACK has been issued (Caution: DB2 and CICS resources
are kept across terminal I/O.). This means CONVRS2=ON has the same
effect as the Natural profile parameter PSEUDO=OFF, except that the
conversational mode is entered after a 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.
CALLNAT subprogram
NDBCONV (described in the
Database Management System Interfaces documentation),
which allows setting or resetting conversational mode 2 dynamically.
DB2COLL=value specifies
the collection name of DB2 packages used by the application in an environment
where the RRSAF interface is used.
| Value | Explanation |
|---|---|
value |
Any valid 18 character DB2 collection name. |
| ' ' (blank) | No name is specified.
This is the default value. |
Notes:
DB2PLAN character contains as first character a question
mark.
DB2GROV=value specifies
whether the connection to the DB2 system identified by
DB2SSID is
to be made to the single DB2 subsystem or to the DB2 sharing group, in case
there exists a DB2 sharing group and a single DB2 with the identical
DB2SSID.
| Value | Explanation |
|---|---|
| ' ' (blank) | Connection will be made to the DB2
sharing group identified by DB2SSID.
This is the default value. |
NOGROUP |
Connection will be made to the DB2
subsystem identified by DB2SSID.
|
Notes:
DB2PLAN=value specifies
the plan name used by the application.
| Value | Explanation |
|---|---|
value |
Any valid 8 character DB2 plan name.
If the first character is a question mark (?) and the RRSAF interface is used
by the application, the packages identified by the collection name specified
with the subparameter DB2COLL will be used
by the application.
|
| ' ' (blank) | No name is specified.
This is the default value. |
Notes:
DB2SSID=value specifies
the name of the DB2 sharing group or the name of the DB2 subsystem to be
connected to.
| Value | Explanation |
|---|---|
value |
Any valid 4 character DB2 sharing group or DB2 subsystem name. |
| ' ' (blank) | No name is specified. This is the default value. |
Notes:
DB2XID=value specifies
whether the RRSAF interface should use a global transaction ID or not.
| Value | Explanation |
|---|---|
ON |
RRSAF will create a global transaction
ID.
This is the default value. |
OFF |
RRSAF will not create a global transaction ID. |
Notes:
DDFSERV=value specifies
either a DD name for the Natural file server (VSAM) or the name of the shared
memory object used for a Shared Memory Objects File Server (SMFSRV=ON).
| Value | Explanation |
|---|---|
ddname |
Any valid 8-character DD name or a shared memory object name. |
CMFSERV |
This is the default name. |
DELIMID=value specifies
the escape character to be used for generating delimited SQL identifiers for
the column names and table names in SQL statements.
| Value | Explanation |
|---|---|
DQ |
Double quotation mark ('') |
SQ |
Single quotation mark (') |
OFF |
Delimited identifiers are not enabled.
This is the default value. |
Notes:
DELIMID must be set to double quotation mark
('') or single quotation mark (').
DELIMID and the SQL STRING
DELIMITER are mutually exclusive. This implies that the mark
(double or single quotation) used to enclose alphanumeric strings in SQL
statements must be different from the value specified for
DELIMID.
DELIMID also complies with the SQL string
delimiter value of your DB2 installation.
RWRDONL subparameter
to determine which delimited identifiers are generated in the SQL string.
In the following example, a double quotation mark
('') has been specified as the escape character for the delimited
identifier:
Natural statement:
SELECT FUNCTION INTO #FUNCTION FROM XYZ-T1000
Generated SQL string:
SELECT "FUNCTION" FROM XYZ.T1000
EBPFSRV=value specifies
whether the Natural file server uses the
Software AG
Editor buffer pool as the storage medium.
| Value | Explanation |
|---|---|
ON |
The Software AG buffer pool is to be
used as the storage medium for the Natural file server.
|
OFF |
A VSAM file or a shared memory object
(SMFSRV=ON) is to be
used as the storage medium for the Natural file server (SMFSRV=ON).
This is the default value. |
EBPMAX=value specifies the
maximum number of blocks to be allocated to each user of the Natural file
server if the
Software AG
Editor buffer pool is used as the storage medium.
| Value | Explanation |
|---|---|
0 - 32676 |
Maximum number of blocks to be allocated. |
100 |
This is the default value. |
Notes:
EBPFSRV subparameter
is set to OFF, EBPMAX is not used at
runtime.
EBPPRAL=value specifies
the number of blocks to be allocated primarily to each user of the Natural file
server if the
Software AG
Editor buffer pool is used as the storage medium.
| Value | Explanation |
|---|---|
0 - 32676 |
Number of blocks to be allocated primarily. |
20 |
This is the default value. |
Notes:
EBPFSRV subparameter
is set to OFF, EBPPRAL is not used at
runtime.
EBPSEC=value specifies the
number of blocks to be allocated secondarily to each user of the Natural file
server if the
Software AG
Editor buffer pool is used as the storage medium.
| Value | Explanation |
|---|---|
0 - 32676 |
Number of blocks to be allocated secondarily. |
10 |
This is the default value. |
Notes:
EBPFSRV subparameter
is set to OFF, EBPSEC is not used at
runtime.
ETIGN=value is used to
handle END TRANSACTION statements in a message-driven IMS region
(MPP or message-oriented BMP).
| Value | Explanation |
|---|---|
ON |
The END TRANSACTION error
is ignored and processing is continued.
This is the default value. |
OFF |
The END TRANSACTION error
is not ignored.
|
Notes:
END TRANSACTION cannot be
executed by the Natural IMS TM Interface and is therefore ignored without any
notification. In such situations, the ETIGN subparameter
can be used to issue an error message instead.
FSERV=value specifies
whether the Natural file server is to be used and whether it can be disabled in
the case of an initialization error.
| Value | Explanation |
|---|---|
ON |
Natural file server is to be used. |
OFF |
Natural file server is not to be used.
This is the default value. |
DIS |
Natural file server is to be used but is to be disabled if it cannot be initialized. |
Notes:
FSERV is set to ON
and the Natural file server is not operational, the initialization of Natural
for DB2 is terminated with a corresponding Natural error message. The Natural
interface to DB2 is disabled, and any SQL call is rejected with a corresponding
error message.
MAXLOOP=value specifies
the maximum possible number of nested SQL database access statements.
| Value | Explanation |
|---|---|
1 - 99 |
Maximum possible number of nested database access loops. |
10 |
This is the default value. |
MAXSTMT=value specifies
the maximum possible number of allocated dynamic SQL statements for the Natural
SQL Gateway.
| Value | Explanation |
|---|---|
1 - 99 |
Maximum possible number of allocated dynamic SQL statements. |
10 |
This is the default value. |
Note:
This subparameter is ignored by Natural for DB2.
MF=value specifies the
number of rows to be fetched by DB2 in one FETCH operation. This
subparameter can be used to enable multi-fetch operations by DB2 on a global
basis. Changes to the application program are not required.
During static generation, MF also
determines whether a generated DECLARE CURSOR statement contains
the WITH ROWSET POSITIONING clause:
If MF is set to zero (0),
DECLARE CURSOR will not contain WITH ROWSET
POSITIONING.
If MF is set to a value greater than zero
(0), DECLARE CURSOR will contain WITH ROWSET
POSITIONING.
If a Natural for DB2 program already uses multi-fetch syntax in a
FIND, READ or SELECT statement, this
statement is executed as specified in the program and not affected by the
MF subparameter. Irrespective of whether one of these
statements already has a multi-fetch specification, the statement will use a
multi-fetch buffer holding space for the number of rows specified in the
MF subparameter.
FIND, READ and SELECT
statements associated with a positioned UPDATE or
DELETE do not use multi-fetch operations even if the
MF subparameter is set to a value greater than zero
(0).
If the MF subparameter is set to a value greater than
zero (0) and one or more rows encounter a warning condition (for
example, due to row-value truncation), DB2 can return SQLCODE
+354. In this case, either set PSCIGN=ON to ignore the return
code as positive SQLCODE, or increase the size of the
receiving fields in the program so that the warning condition no longer occurs.
In non-multi-fetch mode, the warning condition does not result in
SQLCODE greater than zero (0).
| Value | Explanation |
|---|---|
1 -32767 |
The number of rows to be fetched by DB2
in one FETCH operation.
|
0 |
This is the default value. |
Note:
This subparameter is used by Natural for DB2 only.
NNPSF=value is used to
change the sign character of positive Natural variables which have format
N, if they are filled from the SQL database system. Usually, these
variables have the C as the positive sign character. If the
subparameter NNPSF is set to ON,
F is used as the positive sign character.
| Value | Explanation |
|---|---|
ON |
Positive numbers put into Natural
numeric variables by the SQL database system get the sign F.
|
OFF |
Positive numbers put into Natural
numeric variables by the SQL database system remain unchanged.
This is the default value. |
NSBDATE=value specifies
the format in which the Natural SQL Gateway server returns SQL date strings to
the application. The application returns an SQL date as a string in the ISO
format (YYYY-MM-DD) by default. NSBDATE=E enables the
application to return SQL date strings in the EUR format
(DD.MM.YYYY).
| Value | Explanation |
|---|---|
' ' |
Natural SQL Gateway returns SQL date
strings in the ISO format (YYYY-MM-DD).
This is the default value. |
E |
Natural SQL Gateway returns SQL date
strings in the EUR format (DD.MM.YYYY).
|
Note:
This subparameter is ignored by Natural for DB2.
NSBHOST=value specifies
the Natural SQL Gateway server TCP/IP host name used to communicate from
TP-monitor environments such as CICS or Com-plete to CONNX JDBC in order to
access SQL databases.
| Value | Explanation |
|---|---|
hostname |
This host name designates the TCP/IP address of the Natural SQL Gateway server that communicates with the CONNX JDBC server. |
LOCALHOST |
This is the default value, meaning the Natural SQL Gateway server resides on the local host. |
Notes:
NSBHOST='IBM2.HQ.SAG'
NSBPORT=value specifies
the TCP/IP port number to which the Natural SQL Gateway server listens.
| Value | Explanation |
|---|---|
integer |
Specifies the port number to which the Natural SQL Gateway server listens. |
0 |
This is the default value, meaning no Natural SQL Gateway server port number is specified. |
Note:
This subparameter is ignored by Natural for DB2.
NSBPORT=4713
PSCIGN=value influences
the treatment of positive SQLCODEs returned from the SQL database system.
| Value | Explanation |
|---|---|
ON |
Positive SQLCODEs are treated as zero. |
OFF |
Positive SQLCODEs cause a NAT3700 error
message.
This is the default value. |
Notes:
PSCIGN is set to
OFF, a NAT3700 error message is issued.
PSCIGN is set to
ON, positive SQLCODEs are treated as if they were zero; that is,
no NAT3700 error message is issued.
REFRESH=value is used to
automatically set the DB2 server and package set to the values that applied
when the last transaction was executed.
| Value | Explanation |
|---|---|
ON |
An automatic refresh is performed every time before a database transaction starts and if a server or package set has been specified. |
OFF |
No automatic refresh is performed.
This is the default value. |
Notes:
CONNECT TO server-name and SET
CURRENT PACKAGESET = 'package-name' SQL
statements of DB2.
RETRYPO=value delimits the
number of retries done by Natural for DB2 in order to reposition a dynamic
scrollable cursor in a pseudo-conversational environment (IMS MPP or CICS).
| Value | Explanation |
|---|---|
1 - 2147483648 |
Number of retries done by Natural for DB2. |
0 |
No retries are done if
RETRYPO is set to 0.
|
10 |
This is the default value. |
Notes:
RETRYPO count is exhausted. In the latter cases, the
cursor is repositioned to the saved position and the prior rows are fetched and
compared until either the values match or no row is found or the
RETRYPO count is exhausted. In the latter cases, a
NAT3703 error message is issued. If a row is fetched whose key columns match
the saved values, processing continues with the next database
instruction.
RETRYPO delimits the retries in each
direction (next or
prior).
RWRDONL=value determines
which identifiers are generated as delimited identifier in an SQL string.
| Value | Explanation |
|---|---|
ON |
Only identifiers that are reserved
words are generated as delimited identifiers. The list of reserved words is
contained in the NDBPARM module. This list was merged from the
lists of reserved words for DB2 for z/OS, DB2 for VSE & VM, DB2 for LINUX,
OS/2, Windows and UNIX, and ISO/ANSI SQL99.
This is the default value. |
OFF |
All identifiers are generated as delimited identifiers. |
Note:RWRDONL only takes effect if the setting
of the DELIMID subparameter
allows delimited identifiers.
SMFSRV=value specifies
whether a Shared Memory Objects File Server (FSSM) is used. For more
information, see File Server – Shared Memory Object
in the section Natural for DB2 in the Database
Management System Interfaces documentation
| Value | Explanation |
|---|---|
ON |
The shared memory object above the bar
specified in the DDFSERV parameter is
used for the FSSM.
|
OFF |
The shared memory object is not used.
Either a VSAM file or the Software AG Editor buffer pool is used as the storage medium for the file server. This is the default value. |
STATDYN=value is used to
allow dynamic execution of statically generated SQL statements if the static
execution returns an error.
| Value | Explanation |
|---|---|
NEVER |
Dynamic execution is never allowed.
This is the default value. |
ALWAYS |
Dynamic execution is always allowed after an error. |
SPECIAL |
Dynamic execution is allowed after
special errors only.
These special errors are:
|
Note:
This subparameter is ignored by Natural SQL Gateway.
DB2=(FSERV=DIS,DELIMID=DQ,RWRDONL=ON,STATDYN=ALWAYS)
NTDB2 FSERV=ON, *
DELIMID=DQ, *
NSBHOST=LOCALHOST, *
NSBPORT=4851, *
RWRDONL=ON