Installing Natural for SQL/DS on z/VSE

This document describes the steps for installing Natural for SQL/DS (product code NSQ) on z/VSE.

Related Topic:

For information on how to operate Natural in an SQL/DS environment, see Natural for SQL/DS in the Database Management System Interfaces documentation.

Note:
When used in this document, DB2 for VSE & VM is referred to as SQL/DS.

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 SQL/DS:

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
NSQvrs.LIBJ Sample installation jobs
NSQvrs.LIBR Source modules, macros, phases and object modules
NSQvrs.INPL Natural objects
NSQvrs.ERRN Natural error messages

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

Installation Procedure

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

This section provides step-by-step instructions on how to install Natural for SQL/DS.

Step 1: Generate the Natural for SQL/DS I/O Module NDBIOMO

(Job I055, Step 1600)

By executing a standard Natural batch job, this step generates the assembly source for NDBIOMO from the member NDBIOTM.

This batch job invokes the Natural program NDBGENI which is loaded with the Natural INPL utility during the installation of base Natural. NDBGENI contains the following two positional parameters which can be modified to meet your specific requirements:

  • the first parameter specifies the DB environment and must be set to SQL/DS,

  • the second parameter specifies the number of parallel dynamic prepared SQL/DS statements.

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

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

Step 2: Build NDBIOMO

(Job I055, Step 1610)

  • Precompile (using the SQL precompiler), assemble and link-edit the Natural for SQL/DS I/O module NDBIOMO. Ensure that an appropriate SQL/DS user ID and password is specified for precompiling.

Note:
Since no precompiler options are specified, the default SQL/DS isolation level Repeatable Read may lead to locking problems, because all SQL/DS locks are held until the end of the transaction. Thus, depending on your application, it may be necessary to specify a different isolation level.

Step 3: Build the Natural Parameter Module

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 profile parameters supplied with the Natural parameter module as required:

    • Set the parameters supplied with the NTVSEP macro to configure the z/VSE batch interface. For descriptions of these parameters, see the corresponding dynamic profile parameter VSEP.

    • Set the parameters specific to Natural for SQL/DS 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 SQL/DS.

  2. Assemble and link the Natural parameter module.

Step 4: Relink your Nucleus

Modify the JCL used to link your nucleus by adding the following INCLUDE statements and the corresponding DLBL statements:

INCLUDE nat-parm-module Natural parameter module, where nat-parm-module is the module name used in Step 3: Build the Natural Parameter Module
INCLUDE NDBNUC Environment-independent Natural for SQL/DS nucleus
INCLUDE NDBNSQ Environment-independent Natural for SQL/DS interface
INCLUDE NDBPARM Natural for SQL/DS parameter module delivered on the installation medium
INCLUDE NDBIOMO Natural for SQL/DS I/O module created in Step 1: Generate the I/O Module NDBIOMO
INCLUDE xxxxxxx Environment-dependent SQL/DS interface (see below)

Depending on your environment(s), INCLUDE the appropriate environment-specific language interface xxxxxxx as shown in the following table:

Interface Environment
ARIPRDID In batch mode
ARIRRTED Under CICS

Note:
If you want to use Natural for SQL/DS in both environments, repeat this step for each of these environments.

Instead of link-editing your nucleus in the way described above, you have the following alternatives:

  1. If you use a shared nucleus, only include NDBNUC and NDBNSQ in the link-edit of this nucleus. All other modules must be included in the link-edit of your Natural environment-dependent nucleus.

  2. Remove NDBNUC and NDBNSQ from the link-edit of the nucleus and link-edit them as a separate module with the mandatory entry name NATGWDB2. The name of the resulting phase is arbitrary. However, if you use a name different from NATGWDB2, this name must be specified as an alias name in an NTALIAS macro entry of the Natural parameter module. This way of link-editing only applies if the Natural Resolve CSTATIC Addresses feature (RCA) is used.

  3. Include all modules in the link-edit job of a separate Natural parameter module with the mandatory entry name CMPRMTB. The name of the resulting phase is arbitrary. This way of link-editing only applies if an alternative parameter module (see the Natural profile parameter PARM described in the Parameter Reference documentation) is used.

  4. If link-editing is done in this way, you can install Natural for SQL/DS without having to modify your nucleus or driver.

If link-editing is done according to Step 2 or 3 above, the following applies:

Under CICS:

The resulting module must be defined via a PPT entry or RDO:

DFHPPT TYPE=ENTRY,PROGRAM=module-name,PGMLANG=ASSEMBLER

Step 5: Load the Natural Objects

(Job I061, Step 1600)

Load the Natural objects specific to Natural for SQL/DS from the NSQvrs.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 SYSSQL in the system file FNAT.

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

Step 6: Load the Natural Error Messages

(Job I061, Step 1620)

Load the Natural error messages specific to Natural for SQL/DS from the NSQvrs.ERRN data set by executing the Natural batch job that runs the ERRLODUS load program of the Natural SYSERR utility (see the Utilities documentation).

ERRLODUS loads the error messages into the Natural system library SYSERR in the system file FNAT.

Step 7: Generate a Static Assembler Program (Sample)

(Job 1065, Step 1600)

  1. Define six Natural work files for output.

  2. For the static generation process, define the following libraries as PHASE search libraries:

    • the library that contains the batch modules NDBSTAT and NDBCHNK (in the NDBvrs.LOAD data set), and

    • the library where you installed this Natural for SQL/DS version.

  3. Define the necessary Natural commands and the Natural input for the static generation procedure.

Natural Parameter Modifications for Natural for SQL/DS

Adapt the Natural parameters described in this section to meet your requirements. For detailed information on the parameters, refer to the Parameter Reference documentation.

This section covers the following topics:

DB2SIZE Parameter

Add the following Natural profile parameter to your Natural parameter module:

DB2SIZE=nn

The DB2SIZE parameter can also be specified dynamically. It indicates the size of the SQL/DS buffer area, which should be set to at least 6 KB.

The setting of DB2SIZE can be calculated according to the following formula:

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

The variables n1 and n2 correspond to the following:

n1 The number of statements for dynamic access as specified as the second parameter in Step 1: Generate the I/O Module NDBIOMO.
n2 The maximum number of nested database loops as specified with the MAXLOOP keyword subparameter in the NTDB2 macro.

Note:
Ensure that you have also added the Natural parameters required for the Software AG Editor (see Installing Software AG Editor).

Since DB2SIZE applies to Natural for SQL/DS and Natural for DB2, it should be set to the maximum value if you run more than one of these environments.

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 SQL/DS.

In previous Natural for SQL/DS 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 SQL/DS buffer (DB2SIZE). Only if there is not enough space available in this buffer, the TP monitor or operating system is invoked.

To take advantage of this performance enhancement, you must specify your DB2SIZE larger than calculated according to the formula. The additional storage requirements (in bytes) can be calculated as follows:

  • With sending fields:

    64 + 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):

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

Example:

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

As 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 102 fields inside the loop.

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

NTDB Macro

Add an NTDB macro for the database type SQL specifying the list of logical database IDs that relate to SQL/DS tables. All Natural DDMs that refer to an SQL/DS table must be cataloged with a database ID (DBID) from this list.

DBIDs can be any number from 1 to 65535. For most user environments, one DBID (usually 250) is sufficient.

Note:
Ensure that all Natural for SQL/DS DDMs used when cataloging a given program have a valid SQL/DS DBID. Also ensure that the DBIDs selected in the NTDB macro for SQL/DS 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 SQL,250
NTDB SQL,(200,250,251)

NTLFILE Macro

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

NTLFILE 101,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.

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 DB2 profile parameter.

Installation Verification

You can verify the successful installation of Natural for SQL/DS by following the instructions in this section.

Prepare your SQL/DS Environment

As all dynamic access to SQL/DS is performed by NDBIOMO, all Natural for SQL/DS users must have RUN privilege on NDBIOMO.

Online Verification Methods

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

Using SQL Services

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

  1. Invoke Natural.

  2. Invoke the SYSDDM utility.

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

    Enter the function code S to select all SQL/DS tables.

    The communication between Natural and SQL/DS works if all existing SQL/DS tables are displayed.

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

    To enable SYSDDM to generate a DDM, the Natural administrator requires access to the following SQL/DS tables:

    SYSTEM.SYSCATALOG
    SYSTEM.SYSCOLUMNS
    SYSTEM.SYSINDEXES
    SYSTEM.SYSVIEWS
    SYSTEM.SYSSYNONYMS
    SYSTEM.SYSUSAGE
  4. After you have generated a DDM, access the corresponding SQL/DS 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.

  5. If you receive the message SYSFUL 3700, enter the system command SQLERR to display the corresponding SQL return code. SQLERR is described in the System Commands documentation.

Using DEM2* Example Programs

To verify and test your installation you can also use the DEM2* example programs in the Natural system library SYSSQL provided on the installation medium.

You can create an SQL/DS 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 DEM2DROP.

Sample Batch Verification Job

To verify the successful installation of the Natural interface to SQL/DS, a sample batch verification job (Job I065) is provided. This step contains sample JCL and example programs to test Natural with Natural for SQL/DS in batch mode.

The example program DEM2CONN performs the connection to the database, which is required before you can run a Natural program that accesses SQL/DS. DEM2CONN calls the DB2SERV module with the function U which in turn calls the database connect services. For details, see Function U described in the Database Management System Interfaces documentation.

The example program DEM2JOIN performs a JOIN combining information from SQL/DS SYSTEM.SYSDBSPACE and SYSTEM.SYSCATALOG.