Installing Natural for Db2 on z/OS

#### Search Results

Search

# Installing Natural for Db2 on z/OS

This document describes the steps for installing Natural for Db2 (product code NDB) on z/OS.

- Prerequisites

- Installation Medium

- Installation Procedure

- Common Installation Steps

- Installation Steps Specific to CICS

- Installation Steps Specific to Com-plete

- Installation Steps Specific to IMS TM

- Installation Steps Specific to TSO

- Installation Verification

- Natural Parameter Modifications for Natural for Db2

- Special Requirements for Natural Tools for Db2

- Natural for Db2 Server Stub

#### Related Topics:

For information on how to operate Natural in a Db2 environment, see Natural for Db2 in the Database Management System Interfaces documentation.

#### Notation vrs or vr:

When used in this document, the notation `vrs` or `vr` represents the relevant product version (see also Version in the Glossary).

## Prerequisites

The following software must be installed before you can install Natural for Db2:

- Natural Version 9.2.3 (or higher); you cannot install Natural and Natural for Db2 at the same time.

- Software AG Editor

See also General Prerequisites and System Support in the section Overview of the Installation Process.

## Installation Medium

The installation medium contains the following data sets required for product installation:

Data Set Name | Contents
--- | ---
`NDBvrs.LOAD` | Load modules
`NDBvrs.SRCE` | Source modules and macros
`NDBvrs.JOBS` | Sample installation jobs
`NDBvrs.INPL` | Natural objects including error messages

Copy the data sets into your environment as described in Copying Data Sets to a z/OS Disk in the section Installing Natural.

## Installation Procedure

Be sure to read Installation Process and Major Natural Features before you start the installation procedure.

The installation procedure comprises the following:

- Common Installation Steps

- Installation Steps Specific to CICS

- Installation Steps Specific to Com-plete

- Installation Steps Specific to IMS TM

- Installation Steps Specific to TSO

## Common Installation Steps

This section described the installation steps that apply to all Natural environments where Natural for Db2 can be installed. The steps additionally required for a particular TP monitor are described in the following sections.

- Db2 Upgrade Considerations

- Step 1: Install the Natural for Db2 License File

- Step 2: Allocate the DBRM Library for Use with Natural for Db2

- Step 3: Generate the Natural for Db2 I/O Module NDBIOMO

- Step 4: Build NDBIOMO

- Step 5: Bind the DBRM NDBIOMO into a Package

- Step 6: Create the Db2 Plan for Use with Natural for Db2

- Step 7: Link-Edit NATGWDB2

- Step 8: Build the Natural Parameter Module

- Step 9: Link the Nucleus

- Step 10: Load New Objects

- Step 11: Create the Natural for Db2 Server Stub

- Step 12: Bind the DBRM ROUTINEN into a Package

### Db2 Upgrade Considerations

If you upgrade to a newer Db2 version, you need not upgrade your current Natural for Db2 installation.

If you upgrade to a newer Db2 version and also want to upgrade to a newer Natural for Db2 version, consider the following:

- Do not recreate the `NDBIOMO` module with the Db2 version parameter of the new Db2 version, unless the new-function mode is enabled in the new Db2 version. In this case, skip the Steps 2 to 5.

- If the new-function mode is enabled in the new Db2 version, you only need to create a new `NDBIOMO` module if you want to use the new Db2 statements added to the `NDBIOMO` module for the new Db2 version. Otherwise, you can also skip the Steps 2 to 5.

### Step 1: Install the Natural for Db2 License File

In addition to the Natural product license file, a license file is required and must be installed for each environment in which Natural for Db2 (NDB) runs. For license file `NDBvrs.LICS`, see Step 2: Prepare, Convert, Assemble and Link the License File in section Installing Natural on z/OS.

Natural for Db2 is not enabled unless you install a valid Natural for Db2 license file. Trying to activate the NDB without a valid license file results in warnings or errors at session start.

### Step 2: Allocate the DBRM Library for Use with Natural for Db2

- Allocate a PDS as DBRM (database request module) library. The size of this data set and the number of directory entries depend on the particular site (5 tracks and 20 directory blocks must be adequate for most environments). The PDS must have a fixed-block record format and a record length of 80.

  Any standard data set name can be used for this DBRM library; however, this installation procedure assumes that the name `SAGLIB.DB2DBRM` is used.

### Step 3: Generate the Natural for Db2 I/O Module NDBIOMO

(Job I055, Step 1600)

1. Execute the standard Natural batch job provided with this step to generate the assembly source for the `NDBIOMO` module from the `NDBIOTM` member. This batch job invokes the Natural program `NDBGENI`, which is loaded with the Natural INPL utility during the installation of base Natural.

   `NDBIOMO` provides dynamic access to Db2 and contains all necessary `EXEC SQL` statements. In addition, it contains some special SQL statements which cannot be executed in dynamic mode. See also I/O Module NDBIOMO for Dynamic SQL Statement Execution in the Database Management System Interfaces documentation.

2. Modify the following two positional parameters contained in `NDBGENI` to meet your requirements.

   - The first parameter restricts the use of SQL statements to those supported by a particular Db2 version. Set this parameter to one of the following values:

     `DB2V11` for Db2 Version 11 in new function mode, or any higher Db2 version.
     `DB2V13` for Db2 Version 13 in new function mode, or any higher Db2 version.

   - The second parameter specifies the maximum number of parallel dynamic prepared Db2 statements.

3. Check the output report created by this job for successful job completion. In addition, a condition code of 0 indicates normal completion.

### Step 4: Build NDBIOMO

(Job I055, Step 1610)

- Precompile, assemble and link the Natural for Db2 I/O module `NDBIOMO`.

  The link-edit step receives a condition code of 4 because of unresolved references for `DSNHLI`. This is normal and can be ignored.

### Step 5: Bind the DBRM NDBIOMO into a Package

(Job I057, Step 1620)

- Bind the DBRM `NDBIOMO` into a package.

### Step 6: Create the Db2 Plan for Use with Natural for Db2

(Job I057, Step 1631 - 1634)

- Create the DB2 plans to be used by Natural for DB2 in batch mode, TSO and under CICS.

### Step 7: Link-Edit NATGWDB2

(Job I055, Step 1680)

- Link-edit the Natural for Db2 load module `NATGWDB2`.

### Step 8: Build the Natural Parameter Module

(Job I060, Steps 0010, 0015)

Build the Natural parameter module for batch mode. The macros and parameters mentioned in this section are described in the Parameter Reference documentation.

1. Modify the settings of the parameters supplied with the Natural parameter module as required:

   - Set the parameters supplied with the `NTOSP` macro to configure the z/OS batch interface. For descriptions of these parameters, see the corresponding dynamic profile parameter `OSP`.

   - Set the parameters specific to Natural for Db2 supplied with the `NTDB2` macro. For descriptions of these parameters, see the corresponding dynamic profile parameter `DB2`.

   See also Natural Parameter Modifications for Natural for Db2.

2. Assemble and link the Natural parameter module.

### Step 9: Link the Nucleus

(Jobs I060, I080)

1. Link the environment-dependent nucleus:

   Add the following `INCLUDE` statements and corresponding DD statements to the link instructions for the linkage editor:

        INCLUDE SMALIB(nat-parm-module)
   | Natural parameter module, where `nat-parm-module` is the module name used in Step 7: Build the Natural Parameter Module
        INCLUDE DSNLIB(DSNTIAR)
   | SQL error message module
        INCLUDE SMALIB(NDBIOMO)
   | Natural for Db2 I/O module created in Step 3: Build NDBIOMO
        INCLUDE xxxxxxxx(yyyyyyyy)
   | Environment-dependent Db2 interface (see below)

   Depending on your environment, specify the appropriate Db2 interface `yyyyyyyy` from library `xxxxxxxx` in your `INCLUDE` statement as shown in the following table:

   Interface | Library | Environment
   --- | --- | ---
   `DSNALI` | `DSNLIB` | Under TSO and in batch mode without running under the control of the DSN command processor, that is, with CAF (Call Attachment Facility)
   `DSNRLI` | `DSNLIB` | WLM (Workload Manager) stored procedure address space and Natural Development Server (recommended) This can also be used in TSO and batch environments.
   `DSNELI` | `DSNLIB` | Under TSO and in batch mode when running under the control of the DSN command processor
   `DSNCLI` | `DFHLIB` | Under CICS
   `DSNULI` | `DSNLIB` | Under all environments except Com-plete
   `DFSLI000` | `IMSLIB` | Under IMS TM (MPP and BMP) and in batch mode using the Db2 batch support (`DSNMTV01`)
   `NDBCOM` | `NDBLIB` | Under Com-plete

2. Link the environment-independent nucleus:

   Add the following `INCLUDE` statement and corresponding DD statement to the link instructions for the Natural for Db2 load module:

        INCLUDE SMALIB(NATGWDB2)

#### Alternatives

Instead of linking both the environment-dependent nucleus and environment-independent nucleus as described above, you can use one of the following methods:

1. Create a single environment-dependent nucleus:

   Link all the environment-dependent modules together with all environment-independent modules, thus creating one single, environment-dependent nucleus.

2. Separate the `NATGWDB2` module (not linked to the environment-independent nucleus):

   Run the `NATGWDB2` module as a separate module by using the Natural Resolve STATIC Addresses feature (RCA).

   You can modify the name of the `NATGWDB2` module linked in Step 6. However, if you use a name different from `NATGWDB2`, this name must be specified as an alias name in the `NTALIAS` macro (see the Parameter Reference documentation) in the Natural parameter module.

3. Create an alternative Natural parameter module containing all the environment-dependent and environment-independent Natural for Db2 and Db2 for z/OS modules:

   Link the alternative Natural parameter module together with all Natural for Db2 modules (`NATGWDB2`, `NDBIOMO`) and all Db2 for z/OS modules (`DSNTIAR` and a Db2 interface module) as Natural for Db2 nucleus `NDBNUCxx` with `ENTRY NATPARM`.

   You can deploy the Natural for Db2 nucleus `NDBNUCxx` by specifying `PARM=NDBNUCxx` as a dynamic parameter.

   This method provides the option to execute a new Natural for Db2 Version `xx` in an existing Natural environment with an older Natural for Db2 version.

   A Natural for Db2 nucleus with a linked Db2 interface module `DSNULI` supported by Db2 for z/OS Version 12 can operate in all environments except Com-plete.

The following applies when linking a separate `NATGWDB2` module (Alternative 2 above) or a separate Natural parameter module (Alternative 3 above):

TP Monitor | Requirement
--- | ---
CICS | The resulting module must be defined as PPT entry or RDO. PPT entry:
    DFHPPT TYPE=ENTRY,PROGRAM=module-name,PGMLANG=ASSEMBLER
Com-plete | The resulting module must be defined as `RESIDENTPAGE` or reside in the LPA/(E)LPA.

### Step 10: Load New Objects

(Job I061, Step 1610)

Before executing this step, change the `CMWKF01` DD statement to point to the `NDBvrs.INPL` data set.

- Load the Natural objects specific to Natural for Db2 from the `NDBvrs.INPL` data set into the Natural system file by using the Natural INPL utility. The Natural objects are loaded into the Natural system libraries `SYSDDM`, `SYSTEM` and `SYSDB2` in the `FNAT` system file.

  Important
  Ensure that your newly created `SYSDB2` library contains all necessary Predict interface programs which are loaded into `SYSDB2` when installing Predict (see the relevant Predict documentation).

### Step 11: Create the Natural for Db2 Server Stub

(Job I070, Steps 1604, 1606, 1608, 1610)

1. Create server stubs to execute Natural stored procedures and Natural user-defined functions. Natural for Db2 server stubs are interface modules between the Db2 database system and the Natural server. The server stub must be installed in order to execute Natural stored procedures and Natural user-defined functions.

   There are two types of server stub:

   > Natural for Db2 server stub (module NDBvrSRV, Steps 1604 and 1606)

   >   The server stub is used to execute Natural stored procedures and Natural user-defined functions.

   >   The IBM Language Environment (LE) runtime modules required must be linked to the Natural for Db2 server stub module. Use the CALL option of the linkage editor and assign the LE runtime library as `SYSLIB`.

   > Natural for Db2 start server stub (module NDBvrSTR, Steps 1608 and 1610)

   >   The start server stub is used to start the Natural server environment(s) explicitly.

   >   The IBM LE runtime modules required must be linked to the Natural for Db2 start server stub module. Use the CALL option of the linkage editor and assign the LE runtime library as `SYSLIB`. Additionally, include the modules `NDBSTRP` (delivered with Natural for Db2) and `NATCONFG` (delivered with Natural) from `NDBvrs.LOAD` and `NATvrs.LOAD`.

2. Natural for Db2 server stubs are generated from the `NDBSTUB` macro. You can generally use the default values for all parameters. Modify only the values of the parameters whose default values do not suit your requirements. The individual parameters are described in the section Natural for Db2 Server Stub.

3. Place the resulting load modules into a steplib library of the JCL used to execute the Db2 stored procedure address space.

4. For Db2 UDB, each Natural stored procedure or Natural user-defined function must be defined by a Db2 `CREATE PROCEDURE` or Db2 `CREATE FUNCTION` statement, where the name of the generated Natural for Db2 server stub module `NDBvrSRV` is specified as `EXTERNAL NAME`.

### Step 12: Bind the DBRM ROUTINEN into a Package

(Job I057, Step 1640)

- Bind the DBRM `ROUTINEN` into a package.

  The DBRM `ROUTINEN` is contained in the collection `SAGNDBROUTINENPACK` and delivered with Natural for DB2. Natural for DB2 needs this collection to access the DB2 catalog and retrieve the parameter descriptions of Natural stored procedures and Natural user-defined functions.

## Installation Steps Specific to CICS

This section describes the additional steps required to install Natural for Db2 in a CICS environment.

Ensure that your Natural/CICS thread size is large enough to contain the `DB2SIZE`; if you use the Natural Tools for Db2 (described in the Database Management System Interfaces documentation), additional storage of 8 KB is required.

This section covers the following topics:

- Using Plan Selection by CICS RCT Entry Threads

- Using Plan Selection by Dynamic Plan Exit

- Installing the Natural File Server with VSAM on CICS

### Using Plan Selection by CICS RCT Entry Threads

(Job I005)

If you want fixed assignment of your transaction code to the Db2 plan, add an additional entry to your CICS RCT, or define a `DB2Entry` with RDO by performing one of the following alternative steps:

- Modify your RCT:

      DSNRCT TYPE=ENTRY,PLAN=plan-name,TXID=(transaction-id)

  where `plan-name` must be the same as the name used to create the Db2 plan for Natural for Db2: see Common Installation Steps.

  Assemble and link the RCT.

- Define a DB2Entry with RDO:

      DEFINE DB2ENTRY
        OVERTYPE TO MODIFY                                        CICS RELEASE = nnnn
        CEDA  DEFine DB2Entry(          )
         DB2Entry       : DB2ENTR
         Group          : NCI
         DEscription    :
        THREAD SELECTION ATTRIBUTES
         TRansid        : transaction-id
        THREAD OPERATION ATTRIBUTES
         ACcountrec     : None               None ! TXid ! TAsk ! Uow
         AUTHId         :
         AUTHType       : Userid             Userid ! Opid ! Group ! Sign ! TErm
                                             ! TX
         DRollback      : Yes                Yes ! No
         PLAN           : plan-name
         PLANExitname   :
         PRIority       : High              High ! Equal ! Low
         PROtectnum     : 0005               0-2000
         THREADLimit    : 0005               0-2000
         THREADWait     : Pool               Pool ! Yes ! No

`plan-name` must be the same as the name used to create the Db2 plan for Natural for Db2: see Common Installation Steps.

For explanations of the parameters, refer to the relevant CICS literature from IBM.

### Using Plan Selection by Dynamic Plan Exit

If you want to perform plan selection by using the dynamic plan exit, perform the following steps:

- Step 1: Build the CICS Dynamic Plan Selection Exit Module NDBUEXT
- Step 2: Link the CICS Dynamic Plan Selection Exit Module NDBUEXT
- Step 3: Define a DB2Entry

#### Step 1: Build the CICS Dynamic Plan Selection Exit Module NDBUEXT

(Job I070, Step 1630)

1. If you want to specify a default plan name, modify the source module `NDBUEXT`.

   The sample exit routine `NDBUEXT` can be modified to use a default plan name if none has been specified prior to the first SQL call. Review the source code in the `NDBUEXT` module for details about specifying a default plan name.

   Ensure that all `NDBUEXT` modules used in Natural for Db2 versions prior to Version 8.3 are replaced by the new `NDBUEXT` module built in this step. The new `NDBUEXT` module still supports CICS TS queue names used in previous versions of Natural for Db2.

2. Precompile, assemble and link `NDBUEXT` for CICS.

   This step receives a condition code of 4 because of an unresolved external reference for `DFHEAI0` and `DFHEI1`. This is normal and can be ignored.

#### Step 2: Link the CICS Dynamic Plan Selection Exit Module NDBUEXT

(Job I075, Step 1640)

- Link the module `NDBUEXT` resulting from the previous step to the CICS load library and define it via a corresponding PPT entry or RDO.

  PPT entry:

      DFHPPT TYPE=ENTRY,PROGRAM=NDBUEXT,PGMLANG=ASSEMBLER

  For explanations of the parameters, refer to the relevant CICS literature from IBM.

#### Step 3: Define a DB2Entry

Perform the following step:

- Define a DB2Entry with RDO:

      DEFINE DB2ENTRY
        OVERTYPE TO MODIFY                                        CICS RELEASE = nnnn
        CEDA  DEFine DB2Entry(          )
         DB2Entry       : DB2ENTR
         Group          : NCI
         DEscription    :
        THREAD SELECTION ATTRIBUTES
         TRansid        : transaction-id
        THREAD OPERATION ATTRIBUTES
         ACcountrec     : None               None ! TXid ! TAsk ! Uow
         AUTHId         :
         AUTHType       : Userid             Userid ! Opid ! Group ! Sign ! TErm
                                             ! TX
         DRollback      : Yes                Yes ! No
         PLAN           :
         PLANExitname   : NDBUEXT
         PRIority       : High              High ! Equal ! Low
         PROtectnum     : 0005               0-2000
         THREADLimit    : 0005               0-2000
         THREADWait     : Pool               Pool ! Yes ! No

  The parameter `PLANExitname` must specify the same program as the `NAME` statement of Step 2: Link the CICS Dynamic Plan Selection Exit Module NDBUEXT.

  Alternatively or additionally, you can specify the plan exit program `NDBUEXT` with the `PLANExitname` parameter of `POOL THREAD ATTRIBUTES` of the `DB2Conn` resource definition of CICS TS.

### Installing the Natural File Server with VSAM on CICS

If you want to use the Natural file server with VSAM, perform the following additional installation steps:

- Step 1: Define a VSAM Data Set for the Natural File Server
- Step 2: Format the Natural File Server Data Set
- Step 3: Build the CICS Table
- Step 4: Restart CICS

#### Step 1: Define a VSAM Data Set for the Natural File Server

(Job I008, Step 1610)

- Specify the size and the name of the VSAM RRDS that is to be used as the Natural file server (see also Preparations for Using the File Server in the Database Management System Interfaces documentation).

#### Step 2: Format the Natural File Server Data Set

####

(Job I075, Step 1610)

- Specify the five input parameters required to format the Natural file server data set (see also Preparations for Using the File Server in the Database Management System Interfaces documentation).

#### Step 3: Build the CICS Table

1. Add an additional FCT entry required for the Natural file server and the Db2 components of Natural according to the following example:

       CMFSERV  DFHFCT TYPE=DATASET,                                          *
                        ACCMETH=VSAM ,                                        *
                        BUFND=5,                                              *
                        BUFNI=4,                                              *
                        DATASET=CMFSERV,                                      *
                        DISP=SHR,                                             *
                        DSNAME=SAGLIB.NCIDB2.SERVER,                          *
                        FILSTAT=(ENABLED,CLOSED),                             *
                        JID=NO,                                               *
                        LOG=NO,                                               *
                        LSRPOOL=NONE,  1-8 ONLY FOR XA; NONE                  *
                        RECFORM=(FIXED,BLOCKED),                              *
                        RSL=PUBLIC,                                           *
                        SERVREQ=(ADD,UPDATE,DELETE,BROWSE),                   *
                        STRNO=4

2. Assemble and link the CICS table.

#### Step 4: Restart CICS

- Restarting CICS is required, because of the additional FCT entry specified in the previous step.

## Installation Steps Specific to Com-plete

Under Com-plete, the installation procedure of Natural for Db2 continues with the adaptation of your Com-plete environment.

Ensure that the changes required for Db2 have been applied to your Com-plete environment (see the relevant section in the Com-plete documentation).

## Installation Steps Specific to IMS TM

This section describes the additional steps required to install Natural for Db2 in an IMS TM environment.

Ensure that the thread of your Natural IMS TM Interface is large enough to contain the `DB2SIZE`; if you use the Natural Tools for Db2 (described in the Database Management System Interfaces documentation), additional storage of 8 KB is required.

- Binding Db2 Plans

- Using Plan Selection with IMS TM Resource Translation Table

- Installing the Natural File Server with VSAM in an IMS TM Environment

### Binding Db2 Plans

(Job I055, Steps 1631, 1632, 1633, 1634 for IMS MPP conversational, IMS BMP, IMS MPP non-conversational, OBMP)

- Build the Db2 plan to be used by Natural for Db2 in all IMS TM environments supported by Natural.

### Using Plan Selection with IMS TM Resource Translation Table

If the name (or any `ALIAS`) of your environment-dependent nucleus does not match the name of your Db2 plan or if you want to use the same Db2 plan for all IMS TM environments, you must use a Resource Translation Table (RTT).

- Modify, assemble and link the IMS TM RTT:

  Add an additional `DSNMAPN` macro to your RTT as follows (for any other parameters, refer to the relevant Db2 literature from IBM):

  `DSNMAPN` macro:

      DSNMAPN APN=load-module,PLAN=plan-name

  where `load-module` is the environment-dependent nucleus (that is, the IMS TM application program) and `plan-name` is the same as the one used in Binding Db2 Plans.

### Installing the Natural File Server with VSAM in an IMS TM Environment

Be aware that database loops cannot be continued across terminal I/Os without using the Natural file server.

If you want to use the Natural file server with VSAM, perform the following additional installation steps:

- Step 1: Define the VSAM Data Set for the Natural File Server
- Step 2: Format the Natural File Server Data Set
- Step 3: Update the JCL for the MPP Region
- Step 4: Restart the MPP Region Used by Your Natural IMS TM Interface

#### Step 1: Define the VSAM Data Set for the Natural File Server

(Job I008, Step 1600)

- Specify the size and the name of the VSAM RRDS that is to be used as the Natural file server (see also Preparations for Using the File Server in the Database Management System Interfaces documentation).

#### Step 2: Format the Natural File Server Data Set

(Job I075, Step 1600)

- Specify the five input parameters required to format the Natural file server data set (see also Preparations for Using the File Server in the Database Management System Interfaces documentation).

#### Step 3: Update the JCL for the MPP Region

- Include the DD statement `CMFSERV` to define the Natural file server data set.

- Increase the `REGION` parameter if necessary.

#### Step 4: Restart the MPP Region Used by Your Natural IMS TM Interface

- Restart your MPP region, because of the additional DD statement.

## Installation Steps Specific to TSO

This section describes the additional installation steps required in a TSO environment if you want to use the Natural file server with VSAM:

- Step 1: Modify NDBFSRV in NTTSOP

- Step 2: Define the VSAM Data Set for the Natural File Server

- Step 3: Format the Natural File Server Data Set

### Step 1: Modify NDBFSRV in NTTSOP

- Set the keyword subparameter `NDBFSRV` (see the Parameter Reference documentation) in the `NTTSOP` macro to `ON` and reassemble and relink your Natural TSO Interface.

### Step 2: Define the VSAM Data Set for the Natural File Server

####

(Job I008, Step 1620)

- Specify the size and the name of the VSAM RRDS that is to be used as the Natural file server (see also Preparations for Using the File Server in the Database Management System Interfaces documentation).

### Step 3: Format the Natural File Server Data Set

(Job I075, Step 1620)

- Specify the five input parameters required to format the Natural file server data set (see also Preparations for Using the File Server in the Database Management System Interfaces documentation).

## Installation Verification

This section provides example batch jobs and online methods for verifying the successful installation of Natural for Db2.

- Test Batch Natural for Db2 under CAF - Job NDBBATCA

- Test Batch Natural for Db2 under DSN - Job NDBBATTB

- Test DSNMTV01 - Job NDBMTV01

- Test TSO Natural for Db2 under CAF - CLIST NDBCAF

- Test TSO Natural for Db2 under DSN - CLIST NDBTSO

- Online Verification Methods

### Test Batch Natural for Db2 under CAF - Job NDBBATCA

`NDBBATCA` contains sample JCL to test Natural for Db2 in batch mode by using the CAF (Call Attachment Facility) interface.

Modify the sample JCL to meet your requirements.

Before the first SQL call, you must call `NATPLAN` to explicitly allocate the plan. The plan name must be the same as the name used in Step 5: Create the Db2 Plan for Use with Natural for Db2. `NATPLAN` can be edited to specify the appropriate Db2 subsystem ID.

### Test Batch Natural for Db2 under DSN - Job NDBBATTB

`NDBBATTB` contains sample JCL to test Natural for Db2 in batch mode by using the DSN command processor. Modify the sample JCL to meet your requirements.

The plan name must be the same as the name used in Step 5: Create the Db2 Plan for Use with Natural for Db2. For explanations of the `DSN` and `RUN` commands, refer to the relevant IBM literature for Db2/TSO and batch users.

### Test DSNMTV01 - Job NDBMTV01

`NDBMTV01` contains a sample JCL to execute Natural by using the Db2 batch support.

Modify the sample JCL to meet your requirements.

The plan name must be the same as the name used in Step 5: Create the Db2 Plan for Use with Natural for Db2.

### Test TSO Natural for Db2 under CAF - CLIST NDBCAF

You can perform the following steps to test the TSO installation of Natural for Db2 under CAF (Call Attachment Facility):

1. #### Adapt CLIST NDBCAF

   (Job I070, Step 240C)

   Change the library and program names in the CLIST `NDBCAF` to meet your requirements. If you do not use the Natural file server, remove the `ALLOC` and `FREE` statements for `CMFSERV`.

2. #### Invoke Natural

   Invoke Natural by executing the CLIST adapted in the previous step. Ensure that Db2 tables can be accessed and that plan switching can be performed.

   Before the first SQL call, you must call `NATPLAN` to explicitly allocate the plan. The plan name must be the same as the name used in Step 5: Create the Db2 Plan for Use with Natural for Db2. `NATPLAN` can be edited to specify the appropriate Db2 subsystem ID.

### Test TSO Natural for Db2 under DSN - CLIST NDBTSO

You can perform the following steps to test the TSO installation of Natural for Db2 under DSN:

1. #### Adapt CLIST NDBTSO

   (Job I070, Step 240B)

   Change the subsystem ID and the library, plan and program names in the CLIST `NDBTSO` to meet your requirements. If you do not use the Natural file server, remove the `ALLOC` and `FREE` statements for `CMFSERV`.

2. #### Invoke Natural

   Invoke Natural by executing the CLIST created in the previous step. Ensure that Db2 tables can be accessed. The plan name must be the same as the name used in Binding Db2 Plans. For explanations of the `DSN` and `RUN` commands, refer to the relevant IBM literature for Db2/TSO and batch users.

### Online Verification Methods

You can verify the successful installation of Natural for Db2 online by using either SQL Services or `DEM2` example programs:

- Using SQL Services
- Using DEM2* Example Programs

#### Using SQL Services

You can verify the successful installation of Natural for Db2 by using the SQL Services (NDB/NSQ) function (described in the Database Management System Interfaces documentation) of the Natural SYSDDM utility:

1. Invoke Natural.

2. Invoke the SYSDDM utility.

3. In the SYSDDM main menu, enter function code `B` to invoke SQL Services (NDB/NSQ).

4. Enter function code `S` to select all Db2 tables.

   The communication between Natural and Db2 works if all existing Db2 tables are displayed.

   For one of the tables, generate a Natural DDM as described in the section Generate DDM from an SQL Table in the Database Management System Interfaces documentation.

5. After you have generated a DDM, access the corresponding Db2 table with a simple Natural program as indicated in the following example:

       DEFINE DATA
       01 view-name OF ddm-name
       02 field
       ...
       END-DEFINE
       FIND view-name WITH field = value
         DISPLAY field
       END-FIND
       END

   where:
   `view-name` is a view of the DDM ddm-name,
   `field` is a DDM field,
   `value` is the search value to be used for the field.

If you receive the message NAT3700, enter the Natural system command `SQLERR` to display the corresponding SQL return code. `SQLERR` is described in the System Commands documentation.

#### Using DEM2* Example Programs

You can also use the `DEM2*` example programs in the Natural system library `SYSDB2` provided on the installation medium to verify and test your installation.

You can create a Db2 table with `DEM2CREA`, and then create the corresponding DDM by using the Natural SYSDDM utility. You can store data in the created table with `DEM2STOR`, and retrieve data from the table with `DEM2FIND` or `DEM2SEL`. You can also drop the table with the `DEM2DROP` program.

## Natural Parameter Modifications for Natural for Db2

This section covers the following topics:

- Natural Profile Parameter Settings

- Performance Considerations for the DB2SIZE Parameter

### Natural Profile Parameter Settings

Adapt the Natural parameter module to meet your requirements. The Natural parameters mentioned in this section are described in the Parameter Reference documentation.

> DB2SIZE Parameter

>   Specify the profile parameter `DB2SIZE`:

>       DB2SIZE=nn

>   `DB2SIZE` indicates the size of the work area used for processing SQL requests. It must be set to at least 6 KB.

>   The setting of `DB2SIZE` also depends on whether you use the Natural file server or not. If the Natural file server is not used, the setting can be calculated according to the following formula:

>       ((1064 + n1 * 48 + n2 * 120) + 1023) / 1024 KB

>   If the Natural file server is used, the setting can be calculated according to the following formula:

>       ((1160 + n1 * 48 + n2 * 160 + n3 * 8) + 1023) / 1024 KB

>   The variables `n1`, `n2` and `n3` correspond to the following:

>   `n1` | Number of statements for dynamic access as specified as the second parameter in Step 2: Generate the Natural for Db2 I/O Module NDBIOMO
>   `n2` | Maximum number of nested database loops as specified with the `MAXLOOP` parameter in the `NTDB2` macro
>   `n3` | Maximum number of Natural file server blocks to be allocated per user specified as the fifth parameter in Job I075, Step 1620, or the `EBPMAX` parameter in the `NTDB2` macro, if you decided to use the Software AG Editor buffer pool as the Natural file server

>   The `DB2SIZE` parameter can also be specified dynamically at the start of a Natural session.

>   Important
>   Ensure that you have also added the Natural parameters required for the Software AG Editor; see the relevant installation description in the section Installing Software AG Editor.

> NTDB Macro

>   Specify database type Db2 and a list of DBIDs (database IDs) in the `NTDB` macro. All Natural DDMs that refer to a Db2 table must be cataloged with a DBID from this list. DBID can be any number from 1 to 65535. For most environments, one DBID (usually 250) is sufficient for database type Db2.

>   The Db2 DBIDs can also be specified dynamically at the start of a Natural session by using the `DB` profile parameter.

>   Important
>   Ensure that all Db2 DDMs used when cataloging a given program have a valid Db2 DBID. Also ensure that the DBIDs selected in the `NTDB` macro for Db2 do not conflict with DBIDs selected for other database systems.

>   The DBID for SQL/DS used when cataloging a Natural program does not have to be in the `NTDB` list of DBIDs used when executing this program. Therefore, when executing existing Natural programs, DBID 250 is not mandatory. Two sample `NTDB` macros follow:

>       NTDB DB2,250

>       NTDB DB2,(200,250,251)

> NTDB2 Macro

>   Set the keyword subparameters in the `NTDB2` macro according to your requirements.

>   The `NTDB2` keyword subparameters can also be specified dynamically at the start of a Natural session by using the profile parameter `DB2`.

> NTLFILE Macro

>   Set the profile parameter `LFILE` in the macro `NTLFILE` to specify a logical DBID (database ID) that relates to database type Db2:

>       NTLFILE 100,250,1

>   This is necessary for using ISQL or calling `NDBISQL` with Natural for Db2.

>   The `LFILE` parameter can also be specified dynamically at the start of a Natural session.

### Performance Considerations for the DB2SIZE Parameter

During execution of an SQL statement, storage is allocated dynamically to build the SQLDA for passing the host variables to Db2.

In previous Natural for Db2 versions, this storage was always obtained from the TP monitor or operating system. For performance reasons, it is now first attempted to meet the storage requirements by free space in the Natural for Db2 buffer (`DB2SIZE`). If there is not enough space available in this buffer, the TP monitor or operating system is invoked to provide additional storage.

You can avoid `GETMAIN` requests by setting `DB2SIZE` to a size larger than calculated with the formulas in the section DB2SIZE Parameter.

Depending on the SQL execution mode and on the usage of the Natural file server, the additional storage requirements (in bytes) can be calculated as follows:

- Dynamic Mode
- Static Mode
- Storage Requirements for the Natural File Server
- Example Calculation for Dynamic Mode without Using the Natural File Server
- Considerations for VARCHAR Fields

#### Dynamic Mode

- With sending fields:

      80 + n * 56

  With sending fields including LOB columns:

      80 + 2 * n * 56

  where `n` is the number of sending fields in an SQL statement.

  The storage is freed immediately after the execution of the SQL statement.

- With receiving fields (that is, with variables of the `INTO` clause (see into-clause) of a `SELECT` statement):

      80 + n * 56 + 24 + n * 2

  With receiving fields including LOB columns:

      80 + 2 * n * 56 + 24 + n * 2

  where `n` is the number of receiving fields in an SQL statement.

  The storage remains allocated until the loop is terminated.

#### Static Mode

- With sending fields:

      80 + n * 24

  With sending fields including LOB columns:

      80 + 2 * n * 56

  where `n` is the number of sending fields in an SQL statement.

  The storage is freed immediately after the execution of the SQL statement.

- With receiving fields (that is, with variables of the `INTO` clause (see into-clause) of a `SELECT` statement):

      80 + n * 24 + 24 + n * 2

  With receiving fields including LOB columns:

      80 + 2 * n * 56 + 24 + n * 2

  where `n` is the number of receiving fields in an SQL statement.

  The storage remains allocated until the loop is terminated.

#### Storage Requirements for the Natural File Server

When using the Natural file server, additional storage is required for each database loop that contains positioned `UPDATE` and/or `DELETE` statements.

For each of such loops, a buffer is allocated to save the contents of all receiving fields contained in the `INTO` clause (see into-clause). Therefore, the size of this buffer corresponds to the total length of all receiving fields:

    20 + 4 + sum (length (v1), ..., length (vn))

where `v1 ... vn` refers to the variables contained in the `INTO` clause.

The buffer remains allocated until the loop is terminated.

#### Example Calculation for Dynamic Mode without Using the Natural File Server

If you use the default value 10 for both variables (`n1` and `n2`), the calculated `DB2SIZE` will be 2208 bytes. However, if you specify a `DB2SIZE` of 20 KB instead, the available space for dynamically allocated storage will be 18272 bytes, which means enough space for up to either 325 sending fields or 313 receiving fields.

Since space for receiving fields remains allocated until a database loop is terminated, the number of fields that can be used inside such a loop is reduced accordingly. For example, if you retrieve 200 fields, you can update about 110 fields inside the loop.

#### Considerations for VARCHAR Fields

When using `VARCHAR` fields (that is, fields with either an accompanying `L@` field in the Natural view or an explicit `LINDICATOR` clause), additional storage is allocated dynamically if the `L@` or `LINDICATOR` field is not specified directly in front of the corresponding base field. Therefore, always specify these fields in front of their base fields.

## Special Requirements for Natural Tools for Db2

Consider the following requirements and recommendations for using the Natural Tools for Db2 (described in the Database Management System Interfaces documentation).

- Retrieval and Explain Functions

- LISTSQL and Explain Functions

### Retrieval and Explain Functions

In order to be independent of Db2 versions, the Retrieval and Explain functions of the Natural Tools for Db2 have been designed not to access the Db2 catalog tables directly, but to access identical tables qualified by the creator name `SYSSAG`.

Thus, before you can use the Retrieval or Explain function, you must create these tables. The `SYSSAG` tables must have the same columns as the Db2 catalog tables and they must be created as `ALIAS`, `VIEW` or `TABLE`.

You can use the sample SQLCODE provided in the member `DEMSQL4` in the Natural system library `SYSDB2` to create these tables. By default, it creates an `ALIAS SYSSAG.xxx` for the corresponding `SYSIBM` table.

For some catalog tables, no indexes are defined. For performance reasons, consider creating copies of these tables with appropriate indexes.

We recommend that you work with copies of the catalog tables for the following tables:

`SYSCOLAUTH`
`SYSDBRM`
`SYSFOREIGNKEYS`
`SYSINDEXPART`
`SYSKEYS`
`SYSSTMT`
`SYSSYNONYMS`
`SYSTABLEPART`
`SYSVIEWS`

The required `CREATE TABLE` and `CREATE INDEX` statements are contained as comments in the sample SQL member `DEMSQL4`. In addition, `DEMSQLUP` contains sample SQLCODE to update the data in the copies of the catalog tables.

For any other table, we recommend that you create an `ALIAS` or a `VIEW` that points to the corresponding `SYSIBM` table.

Note
The sample SQL members can be executed with the `ISQL` part of `SYSDB2`. `ISQL` enables you to read SQL members from the Natural system library `SYSDB2`. You can save an SQL member in any other library by issuing the command `LIBRARY MYLIB` from the ISQL input screen to switch to another library and then save the SQL member there. You cannot save SQL members in the library `SYSDB2`.

### LISTSQL and Explain Functions

These functions access Db2 `PLAN_TABLE`s. You can only use these functions if a `PLAN_TABLE` exists for your SQLID. For the layout of the `PLAN_TABLE`, refer to IBM's Db2 literature on the `EXPLAIN` command.

We recommend that you create an index on the following columns of the `PLAN_TABLE`:

`APPLNAME`
`PROGNAME`
`COLLID`
`QUERYNO`
`TIMESTAMP`
`DESC`
`QBLOCKNO`
`PLANNO`
`MIXOPSEQ`

## Natural for Db2 Server Stub

A Natural for Db2 server stub is an interface module needed to communicate between the Db2 database system and the Natural server. The server stub module determines, sets up and invokes a Natural server environment for executing Natural stored procedures and Natural user-defined functions.

As mentioned in the Installation Procedure, there are two types of server stub: the Natural for Db2 start server stub (STR) and the Natural for Db2 server stub (SRV). Both stubs are generated from the `NDBSTUB` macro.

- Natural for Db2 Start Server Stub

- Specify Natural for Db2 Server Stub

- JCL Procedure

- NDBSTUB Macro

### Natural for Db2 Start Server Stub

The Natural for Db2 start server stub is used for setting up the Natural server environments desired. The start server stub must be the main execution program in the Stored Procedure Address Space (SPAS). After the start server stub has established the Natural server environments, it passes control to the appropriate Db2 program (`DSNX9WLM` for WLM SPAS and `DSNX9STP` for Db2 SPAS). When SPAS terminates, the Db2 program returns control to the start server stub. The start server stub stops the Natural server environments and returns control to the operating system.

The Natural for Db2 start server stub reads the names and parameters of the Natural server to be started from the `CMSRVIN` data set. `CMSRVIN` must be specified with the DD name `CMSRVIN`.

The `CMSRVIN` data set is a sequential file that contains all information required to start the desired Natural servers. For each server to be started, one `START` entry must be provided. The parameters used for the `START` entries are identical to the parameters that apply to the `NDBSTUB` macro. Enclose the contents of each `START` entry in brackets and delimit comments by the following signs: `/*` and `*/`.

#### Example of START Entries:

    START=(SERVER=NDBvrSRV,NATURAL=NATBATvr,CMPRMIN=CMPRMIN,
            CMPRINT=CMPRINT,CMTRACE=CMTRACE,THREADSIZE=768,
            THREADNUMBER=2,TRACE=ON)
     START=(SERVER=WDBvrSRV,NATURAL=NATBATvr,CMPRMIN=CMPRMIN,
            CMPRINT=CMPRINT,CMTRACE=CMTRACE,THREADSIZE=768,
            THREADNUMBER=2,TRACE=ON)
    /*  START=(SERVER=QEvrsSRV,NATURAL=NATBATvr,CMPRMIN=QAPARM, */
    /*        CMPRINT=CMPRINT,CMTRACE=CMTRACE,THREADSIZE=700, */
    /*        THREADNUMBER=2,TRACE=OFF) */

If the start server data set is missing or has not been assigned, the start server stub will start a Natural server environment with the parameters that derive from the parameters defined for the start server stub itself.

### Specify Natural for Db2 Server Stub

The Natural for Db2 server stub is the link between Db2 and Natural stored procedures or Natural user-defined functions (Natural UDFs). Specify the Natural for Db2 server stub as `EXTERNAL NAME` in the `SYSIBM.SYSROUTINES` table row that refers to the Natural stored procedure or Natural UDF. The server stub is started by Db2/WLM when the Natural stored procedures or Natural UDFs are invoked. The Natural for Db2 server stub creates a Natural session in the Natural server environment and invokes the Natural subprogram comprising the Natural stored procedure or the Natural UDF.

A Natural session created for executing a Natural stored procedure terminates when the corresponding Natural subprogram ends and control returns to Db2 and to the calling client.

A Natural session created for executing a Natural UDF stays active for multiple function invocations if the `PARALLEL` attribute is set to `D` and the `FINAL CALL` attribute is set to `Y`. The session invoked for a Natural UDF function is terminated by the server stub if it detects a termination call.

### JCL Procedure

The JCL procedure of the Stored Procedure Address Space (SPAS) must specify the Natural for Db2 start server stub as program in the `EXEC` statement.

The Natural for Db2 start server stub and the Natural for Db2 server stub must reside in a library contained in the steplib concatenation of the JCL procedure of the SPAS.

#### Example of JCL:

    //*************************************************************
    //*    JCL FOR RUNNING THE WLM-ESTABLISHED STORED PROCEDURES
    //*    ADDRESS SPACE
    //*       RGN     -- MVS REGION SIZE FOR THE ADDRESS SPACE.
    //*       DB2SSN  -- DB2 SUBSYSTEM NAME.
    //*       NUMTCB  -- NUMBER OF TCBS USED TO
    //*                  PROCESS END USER REQUESTS.
    //*       APPLENV -- MVS WLM APPLICATION ENVIRONMENT
    //*                  SUPPORTED BY THIS JCL PROCEDURE.
    //*
    //*************************************************************
    //DBvrsENV PROC RGN=0K,APPLENV=DBvrsENV,DB2SSN=DBvrs,NUMTCB=8
    //IEFPROC EXEC PGM=NDBvrSTR,REGION=&RGN,TIME=NOLIMIT, /* Start server stub
    //*IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
    //        PARM='&DB2SSN,&NUMTCB,&APPLENV'
    //STEPLIB  DD  DISP=SHR,DSN=DSNvrs.RUNLIB.LOAD
    //         DD  DISP=SHR,DSN=CEE.SCEERUN
    //         DD  DISP=SHR,DSN=DSNvrs.SDSNLOAD
    //         DD  DISP=SHR,DSN=NATURAL.LOAD /* Library containing stubs and nucleus
    //CMPRMIN  DD  DISP=SHR,DSN=hilev.SOURCE(DYNPARM) /* Dynamic Natural parameters
    //CMSRVIN  DD  DISP=SHR,DSN=hilev.SOURCE(CMSRVIN) /* Servers to be started
    //CEEDUMP  DD  SYSOUT=X
    //SYSOUT   DD  SYSOUT=X /* Traces records of server stub
    //RMTRACE  DD  SYSOUT=X
    //CMPRINT  DD  SYSOUT=X
    //SYSPRINT DD  SYSOUT=X
    //SYSERROR DD  SYSOUT=X
    //SYSUDUMP DD  SYSOUT=X

where `hilev` represents a high-level qualifier.

### NDBSTUB Macro

The `NDBSTUB` macro is used to generate the Natural for Db2 server stub and Natural for Db2 start server stub. You can parameterize `NDBSTUB` to create different stubs.

Below are the parameters available with `NDBSTUB`:

`CMPRINT` | `CMPRMIN` | `CMTRACE` | `GTRACE` | `GTRCID` | `MODE` | `NATURAL` | `SERVER` | `THREADSIZE` | `TRACE` | `WLM`

#### CMPRINT - DD Name of CMPRINT Data Set

`CMPRINT` specifies the DD name of the `CMPRINT` data set to which the primary report output is written. If an asterisk (*) is specified, a unique `ddname Pnnnnnnn` is built whenever a Natural stored procedure is invoked.

Possible Values:

Value | Explanation
--- | ---
`ddname` | Any valid 8-character DD name
`CMPRINT` | This is the default name.

#### CMPRMIN - DD Name of CMPRMIN Data Set

`CMPRMIN` specifies the DD name of the `CMPRMIN` data set during startup to read the input `PROFILE` parameter for this server.

Possible Values:

Value | Explanation
--- | ---
`ddname` | Any valid 8-character DD name
`CMPRMIN` | This is the default name.

#### CMTRACE - DD Name of CMTRACE Data Set

`CMTRACE` specifies the DD name of the `CMTRACE` data set to which the primary report output is written. If an asterisk (*) is specified, a unique `ddname Pnnnnnnn` is built whenever a Natural stored procedure is invoked, which makes it possible to store each output separately.

Possible Values:

Value | Explanation
--- | ---
`ddname` | Any valid 8-character DD name
`CMTRACE` | This is the default name.

#### GTRACE - Natural for Db2 Server Stub to Execute GTRACE Calls

`GTRACE` specifies whether or not the server stub executes `GTRACE` macro calls for tracing purposes.

Possible Values:

Value | Explanation
--- | ---
`ON` | The generated server stub executes `GTRACE` macros in order to document its processing.
`OFF` | The generated server stub does not execute `GTRACE` macros during its processing cycle. This is the default value.

#### GTRCID - GTRACE ID to be Used

`GTRCID` specifies the event ID recorded with the trace data created by the Natural for Db2 server stub.

Possible Values:

Value | Explanation
--- | ---
`event-id` | Decimal number from `0` to `1023`
`203` | This is the default value.

#### MODE - Operating Mode of Natural for Db2 Server Stub

`MODE` determines the operating mode of the Natural for Db2 server stub generated.

Value | Explanation
--- | ---
`STR` | The generated Natural for Db2 server stub operates as Natural for Db2 start server stub that sets up the Natural server environment.
`SRV` | The generated Natural for Db2 server stub operates as Natural for Db2 server stub that invokes the associated Natural stored procedure or Natural UDF. This is the default value.

#### NATURAL - Name of Server Front-End or Natural Server

`NATURAL` denotes the name of the server front-end or Natural server load module loaded by the Natural for Db2 server stub if the external `CMSTART` has not yet been resolved by the linkage editor during the creation of the server stub. The named load module has to be present in any steplib of the stored procedure address space.

Value | Explanation
--- | ---
`name` | Any valid load module name
`NATBATvr` | This is the default value.

#### SERVER - Server Name for Natural Server Environment

Server names suffixed with the three characters `SRV` denote the names of the servers used by the server front-end in order to identify the Natural server. These names must be unique within one address space.

Value | Explanation
--- | ---
`server-name` | Server name of up to 5 characters
`NDBvr` | This is the default value.

#### THREADSIZE - Size of Natural Threads for Natural Server

`THREADSIZE` determines the size of the Natural threads to be used by the Natural server. The size is specified in units of kilobytes.

Value | Explanation
--- | ---
`threadsize` | Decimal number
`768` | This is the default value.

#### TRACE - Natural for Db2 Server Stub to Write Trace Records

Determines whether the generated Natural for Db2 server stub writes trace records. The trace records are written to the data set specified with `ddname SYSOUT`.

Value | Explanation
--- | ---
`YES` | Trace records are written.
`NO` | No trace records are written. This is the default value.

#### WLM - Natural for Db2 Start Server Stub Mode WLM/DB2 SPAS

`WLM` (Workload Manager) specifies where control is passed to after the Natural for Db2 start server stub has established the Natural server environments requested.

This parameter is only evaluated if the `MODE=STR` parameter is set. Specify `WLM=YES` if the Natural for Db2 start server stub runs in an address space that has been established by `WLM`.

Value | Explanation
--- | ---
`YES` | The start server stub generates links to `DSNX9WLM`, after setting up the Natural server environments.
`NO` | The start server stub generates links to `DSNX9STP`, after setting up the Natural server environments. This is default value.

### On this page

- Prerequisites
- Installation Medium
- Installation Procedure
- Common Installation Steps
- Installation Steps Specific to CICS
- Installation Steps Specific to Com-plete
- Installation Steps Specific to IMS TM
- Installation Steps Specific to TSO
- Installation Verification
- Natural Parameter Modifications for Natural for Db2
- Special Requirements for Natural Tools for Db2
- Natural for Db2 Server Stub