This document describes the steps for installing Natural for SQL/DS (product code NSQ) on z/VSE.
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.
When used in this document, the notation
vrs or
vr represents the relevant product
version (see also Version in the
Glossary).
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.
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.
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.
(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.
(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.
Build the Natural parameter module for batch mode. The macros and parameters mentioned in this section are described in the Parameter Reference documentation.
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.
Assemble and link the Natural parameter module.
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:
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.
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.
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.
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:
The resulting module must be defined via a PPT entry or RDO:
DFHPPT TYPE=ENTRY,PROGRAM=module-name,PGMLANG=ASSEMBLER
(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). |
(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.
(Job 1065, Step 1600)
Define six Natural work files for output.
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.
Define the necessary Natural commands and the Natural input for the static generation procedure.
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:
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.
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.
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)
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.
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.
You can verify the successful installation of Natural for SQL/DS by following the instructions in this section.
As all dynamic access to SQL/DS is performed by NDBIOMO,
all Natural for SQL/DS users must have RUN privilege
on NDBIOMO.
You can verify the successful installation of Natural for SQL/DS online
by using either or DEM2
example programs:
You can verify the successful installation of Natural for SQL/DS by using the function (described in the Database Management System Interfaces documentation) of the Natural SYSDDM utility:
Invoke Natural.
Invoke the SYSDDM utility.
In the SYSDDM main menu, enter the function code B to
invoke the 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.
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 |
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.
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.
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.
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.