This Natural profile parameter is used to specify the parameters for
the database management interface
Natural for DB2.
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, * NNPSF=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
|
MF
|
NNPSF
|
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.
|
Note:
This parameter is relevant in CICS and IMS TM environments
only.
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. |
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
statements in the static-generated program will not
contain the WITH ROWSET POSITIONING
clause. This prevents
multi-fetching during static program execution regardless of the setting of the
MF
parameter of the executing Natural session.
Programs which are generated static before the introduction of
the MF
parameter, will not be able to perform multi-fetching.
Instead single-fetching will be used.
If MF
is set to a value greater than zero
(0
), the DECLARE CURSOR
statements in the
static-generated program will contain the WITH ROWSET POSITIONING
clause. For these programs, multi-fetching will be used when the
MF
parameter is greater than 0 in the executing Natural
session.
If the executing Natural session runs with MF=0
,
these programs will not use multi-fetching.
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. |
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:
|
DB2=(FSERV=DIS,DELIMID=DQ,RWRDONL=ON,STATDYN=ALWAYS)
NTDB2 FSERV=ON, * DELIMID=DQ, * RWRDONL=ON