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:

• 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,                                          *
               DSTNAME=value,                                          *
               DYSQLTR=value,                                          *
               EBPFSRV=value,                                          *
               EBPMAX=value,                                           *
               EBPPRAL=value,                                          *
               EBPSEC=value,                                           *
               ETIGN=value,                                            *
               FSERV=value,                                            *
               MAXLOOP=value,                                          *
               MF=value,                                               *
               NDZSRV=value,                                           *
               NNPSF=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 | DSTNAME | DYSQLTR | EBPFSRV | EBPPRAL | EBPSEC | EBPMAX | ETIGN | FSERV | MAXLOOP | MF | NDZSRV | NNPSF | 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.

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 Db2, Db2 SQLCODE -501, 504, 507, 514 or 518 applies.
3. If, in a CICS environment, you are using the function SQL Services (NDB) (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

DSTNAME

DSTNAME=value specifies the name of the shared memory object used for tracing dynamic SQL statements (DYSQLTR=ON).

Value	Description
dstname	Any valid 8-character shared memory object name.
NDBRDC01	This is the default name.

DYSQLTR

DYSQLTR=value specifies whether dynamically executed SQL statements are traced into shared memory object above the bar or not.

Value	Description
ON	Natural for Db2 writes trace records into a shared memory object specified in the DSTNAME parameter above the bar for dynamically executed SQL statements.
OFF	Natural for Db2 does not write trace records for dynamically executed SQL statements. This is the default value.

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

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.

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.

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.

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.

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.

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.

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.

NDZSRV - NDZ Server Name

NDZSRV=value specifies the name of the NDZ Server to be used, when available, to process SQL statements.

Value	Explanation
value	Any valid 8 character NDZ server name.
" " (blank)	No name is specified. Natural will not attempt to use NDZ to process SQL statements. 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.

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.

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 applies only to dynamic scrollable cursors.
2. 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.
3. 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.
4. 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 was merged from the lists of reserved words for Db2 for z/OS, 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,                                               *
             RWRDONL=ON