This document describes the steps for installing Natural for DB2 Version 8.2.7 (product code NDB) on z/OS.
For information on how to operate Natural in a DB2 environment, see Natural for DB2 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 DB2:
Natural (you cannot install Natural and Natural for DB2 at the same time)
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 |
---|---|
NDBvrs.LOAD |
Load modules |
NDBvrs.SRCE |
Source modules and macros |
NDBvrs.JOBS |
Sample installation jobs |
NDBvrs.INPL |
Natural objects |
NDBvrs.ERRN |
Natural 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.
Be sure to read Installation Process and Major Natural Features before you start the installation procedure.
The installation procedure comprises the following:
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.
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.
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.
(Job I055, Step 1600)
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.
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:
DB2V9
for DB2 Version
9 in new-function mode, or any higher DB2 version,
DB2V10
for DB2 Version
10 in new function mode, or any higher DB2 version, or
DB2V11
for DB2 Version
11 in new function mode, or any higher DB2 version.
The second parameter specifies the maximum number of parallel dynamic prepared DB2 statements.
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, 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.
(Job I055, Step 1620)
Bind the DBRM NDBIOMO
into a package.
(Job I055, Step 1630)
Create the DB2 plans to be used by Natural for DB2 in batch mode, TSO and under CICS.
(Job I055, Step 1680)
Link-edit the Natural for DB2 load module NATGWDB2
.
(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.
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.
Assemble and link the Natural parameter module.
(Jobs I060, I080)
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 NDBLIB(NDBPARM) |
Natural for DB2 load module contained on the
NDBvrs.LOAD data set.
|
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 |
DFSLI000
|
IMSLIB
|
Under IMS TM (MPP and BMP) and in batch mode
using the DB2 DL/I batch support (DSNMTV01 )
|
NDBCOM
|
NDBLIB
|
Under Com-plete |
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)
Instead of linking the nucleus in the way described above, you can use one of the following methods:
Create an environment-dependent nucleus without environment-independent nucleus:
Link all environment-dependent parts to the environment-independent parts, thus creating one environment-dependent nucleus.
Separate the NATGWDB2
module (not linked to the
nucleus):
Remove the Natural for DB2 load module NATGWDB2
from the
environment-independent nucleus and run it 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.
Separate the Natural parameter module:
Move all environment-dependent Natural parts (Natural parameter
module) and Natural for DB2 parts (DB2 interface, DB2 error message module,
NDBIOMO
and NDBPARM
) into a separate Natural
parameter module with the mandatory name CMPRMTB
.
This Natural parameter module has a separate link and must be loaded
by specifying its name in the profile parameter
PARM
(described in the Parameter Reference documentation).
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.
|
(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).
(Job I061, Step 1620)
Before executing this step, change the CMWKF02
DD
statement to point to the NDBvrs.ERRN
data set.
Load the Natural error messages specific to Natural for DB2 from the
NDBvrs.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 FNAT
system file.
(Job I070, Steps 1604, 1606, 1608, 1610)
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 modulesNDBSTRP
(delivered with Natural for DB2) andNATCONFG
(delivered with Natural) fromNDBvrs.LOAD
andNATvrs.LOAD
.
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.
Place the resulting load modules into a steplib library of the JCL used to execute the DB2 stored procedure address space.
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
.
(Job I070, Step 1615)
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.
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:
(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.
If you want to perform plan selection by using the dynamic plan exit, perform the following steps:
(Job I070, Step 1630)
If you want to specify a default plan name, modify the source module
NDBUEXT
.
The sample exit 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 module NDBUEXT
for details about
specifying a default plan name.
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.
(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.
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.
If you want to use the Natural file server with VSAM, perform the following additional installation steps:
(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).
(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).
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
Assemble and link the CICS table.
Restarting CICS is required, because of the additional FCT entry specified in the previous step.
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).
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.
(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.
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.
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:
(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).
(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).
Include the DD statement CMFSERV
to define the Natural
file server data set.
Increase the REGION
parameter if necessary.
Restart your MPP region, because of the additional DD statement.
This section describes the additional installation steps required in a TSO environment if you want to use the Natural file server with VSAM:
Set the keyword subparameter
NDBFSRV
(see the Parameter Reference documentation) in the
NTTSOP
macro to ON
and reassemble and relink your Natural TSO
Interface.
(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).
(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).
This section provides example batch jobs and online methods for verifying the successful installation of Natural for DB2.
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.
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.
NDBMTV01
contains a sample JCL to execute Natural by using
the DB2 DL/I 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.
You can perform the following steps to test the TSO installation of Natural for DB2 under CAF (Call Attachment Facility):
(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
.
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.
You can perform the following steps to test the TSO installation of Natural for DB2 under DSN:
(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
.
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.
You can verify the successful installation of Natural for DB2 online by
using either DEM2
example programs:
You can verify the successful installation of Natural for DB2 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 function code B
to
invoke .
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.
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.
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.
This section covers the following topics:
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 KBIf 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 KBThe variables
n1
,n2
andn3
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 theNTDB2
macron3
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 theNTDB2
macro, if you decided to use the Software AG Editor buffer pool as the Natural file serverSince
DB2SIZE
applies to Natural for DB2 and Natural SQL Gateway, it must be set to the maximum value if you run both products in the same environment.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 theNTDB
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 sampleNTDB
macros follow:NTDB DB2,250NTDB 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 parameterDB2
.- NTLFILE Macro
Set the profile parameter
LFILE
in the macroNTLFILE
to specify a logical DBID (database ID) that relates to database type DB2:NTLFILE 100,250,1This 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.
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 DBSIZE
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:
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.
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.
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.
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.
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.
Consider the following requirements and recommendations for using the Natural Tools for DB2 (described in the Database Management System Interfaces documentation).
In order to be independent of DB2 versions, the
and
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 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
.
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
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.
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 */
.
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.
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.
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.
//************************************************************* //* 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.
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
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
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
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
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
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
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
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 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
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. |
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
(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 |