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:

• DB2 Parameter Syntax

• NTDB2 Macro Syntax

• Keyword Subparameters

• Example of DB2 Parameter

• Example of NTDB2 Macro

---

DB2 Parameter Syntax

The DB2 parameter is specified as follows:

DB2=(keyword-subparameter=value,keyword-subparameter=value,...)

See Keyword Subparameters.

NTDB2 Macro Syntax

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

See Keyword Subparameters.

Keyword Subparameters

There are two groups of keyword subparameters:

General 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

Special Keyword Subparameters

DB2COLL | DB2GROV | DB2PLAN | DB2SSID | DB2XID

Notes:

1. These special keyword subparameters belong together. They apply only to DB2 for z/OS in environments using CAF or RRSAF.
2. They provide DB2 connecting and resource functionality, which in earlier versions was only supplied by the 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.
3. If the application already connected to DB2 by NATPLAN before the first SQL request, the NTDB2 or DB2 parameters are ignored and the connection already established is used.

BTIGN - Ignore BACKOUT TRANSACTION Error

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:

1. This parameter is relevant in CICS and IMS TM environments only.
2. This subparameter is ignored by Natural SQL Gateway.

CONVERS - Conversational Mode under CICS

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:

1. If this subparameter is set to OFF and no Natural file server is used, you cannot continue database loops across terminal I/O; if so, the following codes may occur.
2. With Natural for DB/2 and Natural for SQL/DS, DB2 SQLCODE -501, 504, 507, 514 or 518 applies.
3. With Natural SQL Gateway, Connex SQLCODE -4011 applies.
4. If, in a CICS environment, you are using the function SQL Services (NDB/NSQ) or SQL Services (NSB) (described in the Database Management System Interfaces documentation) without Natural for DB2 file server, you must specify CONVERS=ON, otherwise the error mentioned above can occur.

CONVRS2 - Conversational Mode 2 under CICS

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:

1. This subparameter is used to control conversational mode 2 in CICS environments. Conversational mode 2 means that update transactions are spawned across terminal I/O until either an explicit 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.
2. See also CALLNAT subprogram NDBCONV (described in the Database Management System Interfaces documentation), which allows setting or resetting conversational mode 2 dynamically.

DB2COLL – DB2 Collection Name

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:

1. This subparameter is only honored by Natural for z/OS.
2. This parameter is only honored by the RRSAF interface if the DB2PLAN character contains as first character a question mark.
3. See also Special Keyword Subparameters.

DB2GROV – DB2 Group Override

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:

1. This subparameter is only honored by Natural for z/OS.
2. See also Special Keyword Subparameters.

DB2PLAN – DB2 Plan Name

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:

1. This subparameter is only honored by Natural for z/OS.
2. See also Special Keyword Subparameters.

DB2SSID – DB2 Subsystem Identifier

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:

1. This subparameter is only honored by Natural for z/OS.
2. See also Special Keyword Subparameters.

DB2XID – DB2 Global Transaction ID

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:

1. This subparameter is only honored by Natural for z/OS.
2. See also Special Keyword Subparameters.

DDFSERV - Alternate DD Name for Natural File Server

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 - Escape Character for Delimited Identifiers

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:

1. A delimited identifier is a sequence of one or more characters enclosed in escape characters. You must specify a delimited identifier if you use SQL-reserved words for column names and table names, as demonstrated in Example of DELIMID.
2. To enable generation of delimited identifiers, DELIMID must be set to double quotation mark ('') or single quotation mark (').
3. The escape character specified for 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.
4. If you enable delimited identifiers, ensure that the value specified for DELIMID also complies with the SQL string delimiter value of your DB2 installation.
5. See also the RWRDONL subparameter to determine which delimited identifiers are generated in the SQL string.

Example of DELIMID

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 - Editor Buffer Pool for Natural File Server

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. ON must be set if the file server is to be used in a Parallel Sysplex environment. In this case, your Natural session must use the auxiliary editor buffer pool (see also Support of a z/OS Parallel Sysplex Environment in Installing Software AG Editor).
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 - Editor Buffer Pool Maximum Allocation

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:

1. This subparameter defines the upper limit for the allocation of buffer pool blocks to a single user.
2. If the EBPFSRV subparameter is set to OFF, EBPMAX is not used at runtime.
3. This subparameter is ignored by Natural for SQL/DS.

EBPPRAL - Editor Buffer Pool Primary Allocation

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:

1. If the EBPFSRV subparameter is set to OFF, EBPPRAL is not used at runtime.
2. This subparameter is ignored by Natural for SQL/DS.

EBPSEC - Editor Buffer Pool Secondary Allocation

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:

1. The secondary allocation is used to allocate buffer pool blocks to the user if the primary allocation amount is already exhausted.
2. If the EBPFSRV subparameter is set to OFF, EBPSEC is not used at runtime.
3. This subparameter is ignored by Natural for SQL/DS.

ETIGN - Ignore END TRANSACTION Error

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:

1. This subparameter is relevant in IMS MPP and message-oriented BMP environments only.
2. In such a region, an 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.
3. This subparameter is ignored by Natural for SQL/DS and Natural SQL Gateway.

FSERV - Activate Natural File Server

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:

1. If 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.
2. This subparameter is ignored by Natural for SQL/DS.

MAXLOOP - Maximum Number of Nested Program Loops

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 - Maximum Number of Allocated Dynamic SQL Statements

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.

MF - Multi-Fetch Row Count

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.

NNPSF - Set Natural Numerics' Positive Sign to F

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 - Set Natural SQL Gateway Date String Format

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).

NSBHOST - Set Natural SQL Gateway Server Host Name

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:

1. If the host name specification contains dots (.), the host name should be surrounded with single quotes.
2. This subparameter is ignored by Natural for DB2 and Natural for SQL/DS.

Example:

NSBHOST='IBM2.HQ.SAG'

NSBPORT – Set Natural SQL Gateway Server TCP/IP Port Number

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.

Example:

NSBPORT=4713

PSCIGN - Treat Positive SQLCODEs as SQLCODE 0

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:

1. If the subparameter PSCIGN is set to OFF, a NAT3700 error message is issued.
2. If the subparameter PSCIGN is set to ON, positive SQLCODEs are treated as if they were zero; that is, no NAT3700 error message is issued.

REFRESH - Refresh Setting of DB2 Server and Package Set

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:

1. Server and package set are refreshed by using the CONNECT TO server-name and SET CURRENT PACKAGESET = 'package-name' SQL statements of DB2.
2. This subparameter is ignored by Natural SQL Gateway.

RETRYPO - Number of Positioning Retries

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:

1. This subparameter is ignored by Natural SQL Gateway and Natural for SQL/DS.
2. This subparameter applies only to dynamic scrollable cursors.
3. In pseudo-conversational environments, cursors are closed at terminal I/O. For dynamic scrollable cursors the current absolute position number and the current key column values are saved. After terminal I/O, the dynamic scrollable cursor is opened again and positioned absolutely to the position of the saved absolute position. The contents of the key columns are compared with the saved values. If they match, processing continues with the next database operation requested.
4. If the contents of the key columns do not match the saved values, the next rows are fetched and compared with the saved values until either the values match or no row is found or the 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.
5. RETRYPO delimits the retries in each direction (next or prior).

RWRDONL - Generate Delimited Identifiers for Reserved Words Only

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.

SMFSRV - Use Shared Memory Object File Server (FSSM)

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 - Allow Static to Dynamic Switch

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: NAT3706: Load module not found SQL -805: DBRM (database request module) does not exist in plan SQL -818: Mismatch of time stamps

Example of DB2 Parameter

DB2=(FSERV=DIS,DELIMID=DQ,RWRDONL=ON,STATDYN=ALWAYS)

Example of NTDB2 Macro

         NTDB2 FSERV=ON,                                               *
             DELIMID=DQ,                                               *
             NSBHOST=LOCALHOST,                                        *
             NSBPORT=4851,                                             *
             RWRDONL=ON