This Natural profile parameter is used to specify the parameters for the
database management interfaces
Natural for DB2,
Natural for
SQL/DS 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, *
MAXSTMT=value, *
MF=value, *
NNPSF=value, *
NSBDATE=value, *
NSBHOST=value, *
NSBPORT=value, *
PSCIGN=value, *
REFRESH=value, *
RETRYPO=value, *
RWRDONL=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 |
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/Os;
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/Os!). 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 a DD
name for the Natural file server module.
| Value | Explanation |
|---|---|
ddname |
Any valid 8-character DD name. |
CMFSERV |
This is the default name. |
Note:
This subparameter is ignored by Natural for SQL/DS.
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 is to be used as the storage medium
for the Natural file server.
This is the default value. |
Note:
This subparameter is ignored by Natural for SQL/DS.
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 and Natural for
SQL/DS.
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.
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. |
Note:
This subparameter is ignored by Natural for SQL/DS.
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 and Natural for
SQL/DS.
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 and Natural for
SQL/DS.
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.
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