DB2 - Parameters for SQL Database Management Interfaces

#### Search Results

Search

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

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:

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

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

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.

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:

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.

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:

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.

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

### On this page

- DB2 Parameter Syntax
- NTDB2 Macro Syntax
- Keyword Subparameters
- Example of DB2 Parameter
- Example of NTDB2 Macro