DB2 - Parameters for SQL Database Management Interfaces

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,                                          *
               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

Special Keyword Subparameters

DB2COLL | DB2GROV | DB2PLAN | DB2SSID | DB2XID

Notes:

These special keyword subparameters belong together. They apply only to DB2 for z/OS in environments using CAF or RRSAF.
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.
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.

Note:
This parameter is relevant in CICS and IMS TM environments only.

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:

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.
With Natural for DB/2, DB2 SQLCODE -501, 504, 507, 514 or 518 applies.
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:

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

This subparameter is only honored by Natural for z/OS.
This parameter is only honored by the RRSAF interface if the DB2PLAN character contains as first character a question mark.
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:

This subparameter is only honored by Natural for z/OS.
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:

This subparameter is only honored by Natural for z/OS.
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:

This subparameter is only honored by Natural for z/OS.
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:

This subparameter is only honored by Natural for z/OS.
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:

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.
To enable generation of delimited identifiers, DELIMID must be set to double quotation mark ('') or single quotation mark (').
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.
If you enable delimited identifiers, ensure that the value specified for DELIMID also complies with the SQL string delimiter value of your DB2 installation.
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:

This subparameter defines the upper limit for the allocation of buffer pool blocks to a single user.
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.

Note:
If the EBPFSRV subparameter is set to OFF, EBPPRAL is not used at runtime.

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:

The secondary allocation is used to allocate buffer pool blocks to the user if the primary allocation amount is already exhausted.
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:

This subparameter is relevant in IMS MPP and message-oriented BMP environments only.
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.

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

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.

Note:
This subparameter is used by Natural for DB2 only.

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:

If the subparameter PSCIGN is set to OFF, a NAT3700 error message is issued.
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.

Note:
Server and package set are refreshed by using the CONNECT TO server-name and SET CURRENT PACKAGESET = 'package-name' SQL statements of DB2.

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:

This subparameter applies only to dynamic scrollable cursors.
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.
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.
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.

Note:
RWRDONL only takes effect if the setting of the DELIMID subparameter allows 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.

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.

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

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

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