After you have created an Operating System Dependent Interface (OSDI) subsystem, you can configure one or more gateways to run under that subsystem. This chapter describes how to set up OSDI definitions, JCL procedures, parameter files, and other z/OS-specific items required by a gateway instance. If you are new to OSDI, read this chapter to learn how OSDI differs from the MPM subsystem as far as gateway configuration is concerned.
This chapter includes the following sections:
To create a gateway instance under OSDI, you must first define the instance as a service using the OSDI DEFINE SERVICE
command. In addition to defining the service, some other items must be set up before the service can be started: a JCL procedure, several parameter files, and possibly security resource definitions.
After you have defined the instance as a service and set up the additional items, you can start the service, which creates one or more z/OS address spaces based on controls that you have specified. A description of the configuration process is included in this chapter.
The OSDI DEFINE SERVICE
command is described completely in Appendix A, "OSDI Subsystem Command Reference". Here, we cover DEFINE
parameter considerations that are specific to a gateway service.
The service name for a gateway can be anything that you want within the content limitations described in Appendix A. By default, OSDI will use the service name as the SID for the service. (The SID is an identifier that users or application developers must supply to connect an application to a particular database.) The SID can be specified separately, however, and is not required to be the same as the service name.
Note:
If you specify a service name that is the same as any existing subsystem name in your system (gateway or otherwise), then you must also specify aJOBNAME
parameter that is not the same as any existing subsystem. If you do not use unique names, then OSDI starts the service using the service name as the job identifier. When z/OS processes a start for an address space whose job name or job identifier matches a known subsystem, the job runs under control of the master subsystem instead of under control of JES.Caution:
Running OSDI services under the master subsystem is not supported. This situation must be avoided by making sure that the service runs with a job name or a job identifier that is not the same as any subsystem name.
This parameter specifies the name of a service JCL procedure that you will place in one of your system procedure libraries. The procedure need not exist when DEFINE SERVICE
is issued, but it must be in place before the service is started. The procedure name can be anything that you choose or that the naming standards of your installation require. The requirements for this procedure are discussed in section "Gateway Region JCL".
The PARM
parameter for a gateway service specifies the name of a z/OS data set containing service initialization parameters. These are z/OS-specific parameters and are described in the section "Gateway Region Parameters". Typically, PARM
will specify a member of a Partitioned Data Set (PDS) that is used for various Oracle parameter files. If no member name is included in the PARM
string, then the specified data set must be sequential (DSORG=PS
).
If you want to exploit the multiple-address-space server features of OSDI, then you should specify the MAXAS
parameter on DEFINE SERVICE
with a value greater than the default of 1. This sets the maximum number of address spaces for the service, which may be greater than the number started when the service is first brought up. (The number of address spaces to start initially is a gateway region parameter.) This parameter can be altered with OSDI commands as long as the gateway service is not active.
When you run a gateway service with multiple address spaces, the JOBNAME
parameter of DEFINE SERVICE
can be used to cause each address space to have a distinct jobname. Although this is not required, it may be desirable if you use z/OS facilities (such as RMF) that distinguish address spaces by jobname. To do this, specify JOBNAME
(name*)
, where name
is a one-character to five-character jobname prefix followed by an asterisk, as shown. As each address space is started, OSDI substitutes a three-digit address space counter for the asterisk (001, 002, and so on) to produce the final jobname. You can also use JOBNAME
to cause the service to run with a jobname different from the service name (which is used by default).
As discussed in the "Service Name" section, you must specify a JOBNAME
parameter if the service name matches any existing subsystem name in your z/OS system.
The SID
parameter specifies a unique identifier for the service. It is a critical element in the process that is used by Oracle database applications to specify the instance to which they must connect. (Inbound network clients specify a SID
parameter in the Oracle database network address string that must match the SID
that is specified in DEFINE SERVICE
).
Although you can run the OSDI DEFINE SERVICE
command through a z/OS system console or similar facility, you should put definition commands for services that you use regularly into the OSDI subsystem parameter file, after the DEFINE
SERVICEGROUP
command. This ensures that the service is always defined correctly and automatically when the subsystem is initialized (normally at system IPL). In the following sample gateway DEFINE SERVICE
command, the command prefix has been omitted and continuation hyphens have been included as though the command were in the subsystem parameter file:
DEFINE SERVICE DB2GW1 TYPE(GTW) PROC(DB2GW1) - DESC('Test Gateway') - SID(GTW1) PARM('ORACLE.GTW1.PARMLIB(DB2G1P)')
Defining a gateway service requires you to specify a JCL procedure name in a system procedure library. You must create the procedure before you try to start the service, and the procedure must invoke the OSDI gateway region program with an EXEC
statement such as the following:
// EXEC PGM=ORARASC,REGION=0M
REGION=0M
is specified to ensure that the server can allocate as much private virtual memory as it needs. Some z/OS systems may prohibit or alter a REGION
parameter such as this, so you might want to check with your systems programmer to make sure that the system will accept your REGION
parameter.
A z/OS exit called IEFUSI
might be installed on your system. The IEFUSI
exit prevents started tasks or batch jobs from getting the maximum region size when REGION=0M
is specified. If an IEFUSI
exit is implemented, then it is specified in the SMFPRMxx
member of SYS1.PARMLIB
that is used during z/OS initialization. To effectively run the Oracle database with an IEFUSI
exit installed, ensure that the exit is coded to allow batch jobs or started tasks with the names of your Oracle regions to allocate a large amount of virtual memory above the 16M line.
Because the gateway allocates only the amount of memory it needs, you can safely allow it to allocate any amount of memory up to the two-gigabyte limit per address space which is imposed by 31-bit addressing conventions.
Note that no other EXEC
statement parameters are needed. The PARM
parameter of EXEC
is not used by the database region program.
Changing the JCL procedure after starting one or more address spaces for the service, and then starting another address space (to use the changed JCL), is not supported.
In addition to the EXEC
statement, the procedure will need several DD statements, as follows:
This DD statement is optional. When used, it specifies a sequential file or PDS member containing environment variable assignment statements. Environment variables are used to supply operating parameters to certain gateway product features. Reliance on environment variables and considerations for setting them are discussed in feature-specific chapters of this manual. The data specified by ORA$ENV
is read-only at gateway service startup. Therefore, in order for changes to the data set to take effect, the service must be stopped and started.
Be aware that the global environment variable file is not read by the gateway. All environment variable settings for the server must be supplied through ORA$ENV
. For more information on the global environment variable file, refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390).
This DD
statement specifies a sequential file or PDS member containing z/OS-specific parameters that control data set processing in the gateway. These parameters are organized by type of file, and they primarily pertain to creation processing when the gateway uses dynamic allocation to create a z/OS trace data set. For considerations and syntax rules for the ORA$FPS
parameter file, refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390).
The ORA$FPS DD
is optional. If you omit it, then file creation operations may fail unless your installation has DF/SMS ACS
routines that supply defaults for data set creation parameters. At gateway service startup, data specified by ORA$FPS
is read and checked. Any errors are reported and ignored. Valid entries are loaded as server file management parameters. After gateway service startup, a new set of server file management parameters can be loaded from the updated ORA$FPS
specification by using the REFRESH FPS
command. For more information, refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390).
This DD statement specifies a non-authorized load library from which non-executable (data) modules are fetched. The modules contain Globalization Support data objects and messages that are associated with Oracle Globalization Support internationalization features. Normally these modules are installed in the OSDI MESG
data set, for example ORACLE.V10G.MESG
. The ORA$LIB DD
statement is optional: if you omit it, then the Oracle server attempts to fetch messages and Globalization Support data objects modules from STEPLIB
. Do not concatenate a non-APF-authorized MESG data set to STEPLIB
in lieu of specifying ORA$LIB
.
This DD statement specifies an input file containing Oracle Net parameters. It is required if the Oracle instance uses any of the following:
Network data encryption
Network activity tracing
Altering of default Oracle Net file names
Refer to Chapter 6, "Oracle Net", for additional information.
This DD
statement must specify the APF-authorized Oracle AUTHLOAD
library that was populated during installation. The IBM LE/370
run-time library must be concatenated to it unless your installation has put the LE/370
run-time into the system linklist. A typical name for the LE/370
run-time library is SYS1.SCEERUN
, but it may have a different name in your system. The DB2 load library (SDSNLOAD
) must be specified unless it is in the linklist.
This DD statement is optional. When used, the gateway instance alert log is written to it. Regardless of the number of server address spaces, an Oracle database instance has only one alert log, which is opened by the first server address space (AS1). Alerts that are generated by sessions in other address spaces are routed to AS1.
You can specify a sequential (DSORG=PS
) disk data set or a spool file (SYSOUT
) for this DD
. If you omit the SYSPRINT DD
, then the alert log is dynamically allocated as a disk data set or spool file according to the ALERT_DSNAME
region parameter, discussed in "Gateway Region Parameters".
If you specify a disk data set for SYSPRINT
and an error occurs while it is being written (including an out-of-space condition), an alert log switch occurs. Refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390) for additional information on Oracle alert log switching.
The following is an example of a JCL procedure for a gateway region:
//DB2GW1 PROC //ORACLE EXEC PGM=ORARASC,REGION=0M //STEPLIB DD DISP=SHR,DSN=ORACLE.V10G.AUTHLOAD // DD DISP=SHR, DSN=DSN710.SDSNLOAD // DD DISP=SHR,DSN=SYS1.SCEERUN //ORA$LIB DD DISP=SHR,DSN=ORACLE.V10G.MESG //ORA$FPS DD DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(G4DB2FPS) //ORA$ENV DD DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(G4DB2ENV) //SQLNETLG DD SYSOUT=*
OSDI gateway region parameters are supplied in a data set whose name is specified as the PARM
string in the service definition. This will typically be a member of a PDS. Because the data set name is supplied through the service PARM
mechanism, no DD
statement is coded in the region JCL. The data set is dynamically allocated, opened, and read when the service is started. Changing parameters in the data set has no effect until the service is stopped and restarted.
Region parameters are read independently by each address space of a multi-address space server. Adding, removing, or changing parameters between the starting of one address space and later starting of another is not supported.
The OSDI gateway region parameters consist of a parameter name followed by the parameter value in parentheses. Each parameter has a long descriptive name and a shorter name of eight characters or less. Each record may contain only one parameter. No continuation is allowed. Records beginning with an asterisk (*) are treated as comments and are ignored. Embedded spaces and all characters after the closing parenthesis are ignored.
ALERT_DSNAME
specifies a filespec value for the gateway alert log for the purpose of alert log switching. The format for this parameter is as follows:
ALERT_DSNAME ( filespec )
The filespec
value can be a SYSOUT
type or a data set name type with embedded system symbols that will guarantee a unique data set name on each use. Using date and time system symbols is recommended in a data set name filespec value.
Examples:
ALERT_DSNAME(ORACLE.&ORASRVN..ALERT.D&LDATE..T<IME) ADSN(//S:Z,,DBOPS01)
If you omit this parameter, then alert log switches use a default SYSOUT
specification //SYSOUT:*
.
ALERT_MAX ( size )
The size
value is the number of data bytes (sum of logical record lengths) written to the alert log. This value can be specified as a number, n
, n
K
(denoting a multiplier of 1024), or n
M
(denoting a multiplier of 1,048,576). The writing of an alert log record that would exceed this size causes an automatic alert log switch before the new record is written. This happens without regard for the sequence or interrelationship between alert log messages, for example, the switch can occur between a pair of related messages.
The default value is 0. If you specify 0, then no automatic switching is done. Specifying a value less than 65536 (64 K) is not recommended for this parameter.
ALERT_MIN ( size )
The size
value is the number of data bytes (sum of logical record lengths) written to the alert log. This value can be specified as n
, n
K
(denoting a multiplier of 1024), or n
M
(denoting a multiplier of 1,048,576). If an alert log switch is requested (for example, using a LOGSWITCH
service command without the FORCE
option), then the request is honored only if the size of the current alert log exceeds this value.
The default value is 0. If you specify 0,then no minimum size checking is done, and all alert log switch requests are carried out.
The DSN_PREFIX_DB
parameter supplies a constant string that is associated with the &ORAPREFD
system symbol. The &ORAPREFD
system symbol can be used to form the high-level (leftmost) qualifier of z/OS data set names generated by the gateway. The format is as follows:
DSN_PREFIX_DB ( dsn_prefix )
The dsn_prefix
value is a valid one-character to eight-character data set name qualifier that conforms to the installation requirements. In most cases, this will be the qualifier that is used for all Oracle database files associated with this instance. For example:
DSN_PREFIX_DB(ORADB01)
DSN_PREFIX_DB
has no default value. If you omit this parameter, then certain situations in which the gateway generates default file names will produce errors.
DEDICATED_TCB
specifies whether OSDI would assign a dedicated z/OS TCB for each session. For gateways running on z/OS, it must be set to AUTO. This parameter and its value are automatically filled in upon the installation of the gateway. The format is:
DEDICATED_TCB(AUTO)
The IDLE_TIMEOUT
parameter sets a timeout value for idle sessions. Sessions that are idle for a period longer than the interval set are terminated, and all resources are released. The format is as follows:
IDLE_TIMEOUT ( time_interval )
The time_interval
value is the timeout value specified as nnn
or nnn
S for seconds or nnn
M for minutes. There is no default timeout value. The maximum value is 604,800 seconds or one week. The timeout value set is a minimum approximation, and a session may be idle for some additional seconds or minutes before it is terminated. When a session is using a dedicated TCB, as is the case with Transparent Gateway products, the task is terminated with an S222 completion code. Clients with connections to timed-out sessions may see a variety of errors if they attempt to continue.
INIT_ADR_SPACES
controls how many auxiliary address spaces are started. The format is as follows:
INIT_ADR_SPACES ( number_of_address_spaces )
The number_of_address_spaces
value is the number of address spaces to start. The default is 1, which starts only the control address space (AS1). The maximum is the number that was specified for MAXAS
on the associated DEFINE SERVICE
command for the gateway service.
INIT_STACK_SIZE
controls the size of the C stack that is allocated for each session. The format is as follows:
INIT_STACK_SIZE ( init_size )
The init_size
value determines the initial size of the C stack. This value can be specified as n or nK. The default is 128 K. This is the size recommended for the gateway.
LOGON_AUTH
specifies how the gateway interacts with a SAF-based external security product when processing logons. The format is as follows:
LOGON_AUTH ( auth )
You can specify the auth
value by using one of the parameters in the following table.
Parameter | Usage |
---|---|
G4RRSAF |
call standard logon exit for gateway |
exitname |
call an installation-supplied logon exit; exitname is the one-character to eight-character load module name of the exit |
If exitname
is specified, then it must reside in the system linklist or in an APF-authorized library that is part of the server region STEPLIB
concatenation. The default is NONE
.
Examples:
LOGON_AUTH(G4RRSAF)
For more information about gateway logon authorization, refer to "Gateway Security" on page 7-4.
The MAX_SESSION_MEM parameter specifies a hard limit on the amount of virtual memory that a single gateway session can allocate. The format is as follows:
MAX_SESSION_MEM ( session_memory )
The session_memory
value is the maximum amount of virtual memory that a single gateway session can allocate. This value can be specified as n, nK (denoting a multiplier of 1024), or nM (denoting a multiplier of 1,048,576). The default is zero (0), which means no session limit is imposed.
This parameter is useful for stopping a runaway session that is allocating excessive amounts of memory due, perhaps, to problems with application design. This pertains only to session-private C stack and heap memory allocated during gateway processing. It does not include internal memory allocations done by the implementation (for example, DB2).
The MAX_SESSIONS
parameter limits the number of sessions that can be scheduled in an address space. The format is as follows:
MAX_SESSIONS ( number_of_sessions )
The number_of_sessions
value is the maximum number of sessions per address space. This value can be specified as n or nK. The default is 1024. The number of sessions that can be supported in an address space depends on the complexity of the work. Limiting the number of sessions per address space reduces the chances of session failure due to exhaustion of virtual storage.
The PRIMARY_ASC_MODE
parameter specifies whether the gateway or the Oracle server being executed will be given control in the PRIMARY Address Space Control (ASC) Mode. For the Oracle Transparent Gateway for DB2, PRIMARY_ASC_MODE(YES)
must be specified.
PRIMARY_ASC_MODE(NO)
is the default, if the parameter is not specified. The default, or the use of NO
, specifies that the Gateway or the Oracle server is to be given control in Access Register (AR) mode. The Oracle Transparent Gateway for DB2 can run only in PRIMARY Address Space Control Mode, whereas the Oracle server can run in AR mode without a problem.
The REGION_MEM_RESERVE
parameter specifies the amount of private area memory in the server address space to be reserved for implementation and z/OS use. The format is as follows:
The region_memory
value is the amount of private area memory reserved. This value can be specified as n, nK (denoting a multiplier of 1024), or nM (denoting a multiplier of 1,048,576).
During initialization, each server address space calculates the total available private area memory and subtracts the reserve amount from it. The result is the aggregate limit for all session memory requests in that address space.
The default is zero (0), which means that no aggregate limit applies. In this case, it is possible for session memory requests to exhaust the available private area of the address space, leading to unpredictable failures.
Thus, the reserve amount must be sufficient to accommodate internal implementation memory requirements as well as memory required by z/OS services used by the gateway, particularly Local System Queue Area (LSQA) memory. Because it is difficult to predict this amount for any given workload, the best strategy is to specify a relatively large reserve amount, such as 50 M or more. This has the effect of reducing slightly the number of sessions that can be accommodated in a gateway address space. However, additional address spaces can be started, if necessary.
SERVER_LOADMOD
specifies the name of the service load module. The format is as follows:
SERVER_LOADMOD ( loadmod )
The loadmod
value is the name of the load module to load. For the gateway, this is usually G4DB2SRV
. This parameter is required.
SMF_STAT_RECNO
specifies the SMF record number to use. The format is as follows:
SMF_STAT_RECNO ( record_number )
The record_number
value is the number of the desired record of Oracle SMF statistics. The default is zero (0). Otherwise, the value must be specified between 128 and 255 for this parameter. Example:
SMF_STAT_RECNO(204)
The collection and writing of Oracle SMF statistics records is controlled by this single parameter in the OSDI service parameter file. A zero (0) for this parameter indicates that no SMF statistics record is to be written. The SMF record number that is chosen must not be the same as the number that is used by any other z/OS software.
If this parameter is not specified, or if zero is specified, then no SMF statistics collection or recording is done. This saves some CPU overhead and saves the overhead of the SMF write itself (which is mostly asynchronous work done by the SMF address space, the inline overhead is mainly just moving data into SMF buffers). For more information about SMF, refer to Appendix B, "The Oracle SMF Interface".
TRACE_DSNAME
specifies the destination for gateway trace files. This includes normal traces requested by setting the TRACELVL
environment variable as well as diagnostic traces generated automatically in certain error situations. The format is as follows:
TRACE_DSNAME ( filespec )
The filespec
value is either a SYSOUT
specification (including class, form, and JES destination) or a data set name.
A SYSOUT
specification is of the form:
//SYSOUT:class,form,dest
When this is used, trace files are dynamically allocated SYSOUT
data sets. In a multi-address space service, the trace file for a given database session is allocated in the address space that hosts the session. Thus, SYSOUT
trace files can appear in all server address spaces. For example, traces written to SYSOUT class X, form AA01, would be written as:
TRACE_DSNAME(//SYSOUT:X,AA01)
For more information, refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390).
As an alternative to a SYSOUT
specification, you can specify a data set name. Because each trace file created as a data set must have a unique data set name, the value supplied must include system symbols that guarantee uniqueness. For more information about system symbols, refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390)
To guarantee uniqueness, use some combination of the session identifier (&ORASESST
) system symbol, date (&LYYMMDD
), and time (&LHHMMSS
). Also, use high-level qualifiers that are appropriate for your installation. This will avoid the possibility of duplicating trace data set names generated in other Oracle instances you run. All components of the string must resolve to produce a name that is valid for a z/OS sequential data set. For example:
TRACE_DSNAME(DB2GW.TRACE.D&LYYMMDD..T&LHHMMSS..&ORASESST)
The allocation parameters for trace data sets are obtained from the DBTR file group of the server file management parameters. For more information about these parameters, refer to the Oracle Database System Administration Guide for IBM z/OS (OS/390)
If this parameter is omitted or fails to produce a valid, unique data set name, then all Oracle trace files are written to the default SYSOUT
class associated with the server region.
There are two types of parameters for member G4DB2ENV
of the db_hlq.
PARMLIB
library:
The first type is parameters that set an environment variable. An environment variable is used to direct internal gateway processing.
The second type is heterogeneous services (HS) initialization parameters that are uploaded to the Oracle database server during the first connection of a session.
Following are the valid parameters and their defaults for the environment variables in member G4DB2ENV
of the db_hlq
.PARMLIB
library. Set these parameters to values that are applicable to your site:
Table 5-1 Valid Parameters for Environment Variables of Member G4DB2ENV
Parameters | Default |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
no default |
|
no default |
|
|
|
|
|
|
|
|
|
|
|
|
The CURRDEGREE
parameter allows you to specify whether parallel query operations are to be allowed on your DB2 system. The valid values are:
Value | Description |
---|---|
1 |
turns off parallel query operations. The default value is 1. |
ANY |
specifies that parallel query operations are to be allowed on the DB2 system. Parallel processing must be enabled on the DB2 system before parallel query operations can occur. |
For further information on DB2 parallel I/O and CP processing, refer to the IBM documents for your platform and operating system.
The DB2DESCTAB
parameter specifies whether the gateway uses a DB2 DESCRIBE TABLE
for acquiring DB2 table information. The default of DB2DESCTAB
set to YES
directs the gateway to run a DESCRIBE TABLE
when acquiring information about the DB2 table. This can provide performance enhancements but cannot be used when accessing DB2 tables using a DB2 alias. DB2 does not allow a DESCRIBE TABLE
against a DB2 alias. DB2DESCTAB
set to NO
prevents the DESCRIBE TABLE
from being used by the gateway so applications can access DB2 tables using the DB2 alias.
The DB2LONGMSG
parameter allows longer error messages to be returned by the gateway. The default of DB2LONGMSG
set to NO
returns some of the error messages from DB2 but does not always return the entire error message. By setting DB2LONGMSG
to YES
, you ensure that the entire DB2 error message can be returned by the gateway.
The DB2WARNING
parameter enables you to configure the gateway so DB2 warning messages are not returned to the application as errors.
Warning messages are displayed by DB2 when SELECT
statements are not formatted properly, but some default action by DB2 can still be taken to complete the task. Setting DB2WARNING
to YES
allows warning message to be returned by the gateway.
The warning message is interpreted as an error by the Oracle application. The default setting of DB2WARNING
set to NO
processes the SELECT
statement without returning the warning to the application. The gateway continues DB2 default completion without notification to the application that a warning occurred.
DB2STATS
specifies whether or not the gateway is to pass DB2 statistics to the Oracle Optimizer for improved query performance. If set to the default value of NO
, then the gateway does not pass statistics. If set to YES
, then the statistics are used by the Oracle Optimizer to choose the access plan for SQL statements that involve DB2 objects. This results in DB2 tables appearing to the Oracle Optimizer as if they were Oracle-analyzed objects. If set to YES
, then ensure that the DB2 utility RUNSTATS
is run against the DB2 tables accessed by the gateway. This ensures information about these tables is available in the DB2 catalog. Also ensure that the gateway plan qualifier has SELECT
privileges for the SYSIBM.SYSKEYS
, SYSIBM.SYSINDEXES
, and SYSIBM.SYSTABLES
DB2 catalog tables.
DB2READONLY
controls whether the gateway is enabled in read-only mode. If it is set to the default value of NO
, then the gateway is not in read-only mode. If it is set to YES
, then read-only capabilities are enabled and only queries are allowed through the gateway to DB2. Any SQL statements or calls that attempt to modify a DB2 object are rejected. When the gateway is in read-only mode, INSERT
, UPDATE
, DELETE
, DB2 stored procedures, or passthrough SQL are not allowed.
For additional information, refer to Chapter 8, "Using the Gateway".
This parameter controls AUTOREGISTER
, the uploading of class capabilities from the gateway to the Heterogeneous Services layer of the Oracle Integrating Server. When the value of this parameter changes, it causes HS to upload and use the new G4DB2CAP
capabilities table.
You should rely on the default and specify this parameter only at the request of Oracle Support Services.
FLUSH_CACHE_ON_COMMIT
specifies when the describe table cache is flushed. If the value is set to YES
, then the describe table cache is flushed each time a transaction is committed. If the value is NO
, then the describe cache is flushed when the Oracle session terminates. The default setting of NO
reduces the overhead associated with flushing cached information that is retrieved repeatedly after each committed transaction. Performance might be improved by using the default setting of NO
.
The following parameters are set based on values specified during the installation and configuration process.
With the introduction of the dynamic capability table, the DB2CAP
environment parameter is obsolete.
The TARGET
parameter is maintained for backward compatibility with version 4 of the gateway. If you are not a previous customer of TG4DB2 v402110 using GTW_SQL.GTWPASS
procedures, then you do not need to specify this parameter.
This parameter specifies the DB2 subsystem name to be accessed by Oracle Transparent Gateway for DB2. The default parameter is DSN0
.
The DB2PLAN
parameter specifies the DB2 plan name that Oracle Transparent Gateway for DB2 uses to access the DB2 system. The default is G4DB2PLN
.
This parameter specifies the user name that Oracle Transparent Gateway for DB2 uses when logging on to DB2 to perform recovery. This parameter has no default but must be defined for the gateway to initialize successfully.
This parameter must be specified as YYYY-MM-DD. The Oracle database server allows a maximum date value of 4712-12-31
while DB2 allows dates up to 9999-12-31
. This ORA_MAX_DATE
parameter has no default. If this parameter is specified, then any DB2 date value that is being sent back to the Oracle database server is inspected. If the inspected value exceeds 4712-12-31
, then it is replaced by the value of ORA_MAX_DATE
.
If no date value is specified for ORA_MAX_DATE
, then a returned date value might not be valid to the Oracle database server. The value of ORA_MAX_DATE
can be set to a value less than 4712-12-3
1, but the ORA_MAX_DATE
is returned only if the DB2 date value exceeds 4712-12-31
.
If you have no dates exceeding the year 4712, then you do not need to be concerned about this parameter.
Some examples are listed in the following table:
ORA_MAX_DATE | DB2 Value | Returned to Oracle |
---|---|---|
(not specified) | 9999-12-31 |
9999-12-31 (invalid) |
(not specified) | 4713-10-24 |
4713-11-28 (invalid) |
(not specified) | 4500-11-19 |
4500-11-19 |
4712-12-31 |
9999-12-31 |
4712-12-31 |
4712-12-31 |
4713-10-24 |
4712-12-31 |
4712-12-31 |
4500-11-19 |
4500-11-19 |
3388-12-31 |
9999-12-31 |
3388-12-31 |
3388-12-31 |
4713-10-24 |
3388-12-31 |
3388-12-31 |
4500-11-19 |
3388-12-31 |
If set to YES
, then DB2 timestamps are sent to the Oracle database server as Oracle TIMESTAMP(6)
data types.
If set to NO
, then DB2 timestamps are sent to the Oracle database server as CHAR(26)
. This is the default and the behavior prior to release 10g.
If this parameter is set to 4, then the following information is written to the trace file:
SQL text sent to the gateway by the Oracle database server
SQL text sent to the target database
Set this parameter to 255 for all gateway trace information.
The fix for bug 2095461, "SQLCODE = -418 is returned using TO_NUMBER
against a char bind variable", introduces a new initialization parameter. By default, a SQL statement sent through the gateway to DB2 that includes the TO_NUMBER
function with one argument will be translated to the equivalent DB2 function, DECIMAL()
. This can cause problems when the only argument is a bind variable. You can alleviate this problem by setting the new init parameter, TO_NUMBER_OFF
to YES
in the gateway ENV member.
This will turn off the capability, and the TO_NUMBER
function will be processed by the Oracle instance prior to sending the SQL statement to the gateway.
Oracle database and the gateway would perform implicit conversion when necessary before sending SQL to DB2. A specific case would be if there is a WHERE
clause such as:
WHERE
number_variable = 'literal_string'
where the literal string contains comma (,) as the decimal indicator. In that case, you would need to set CNV_LIT_FMT
to YES
.
A related bug of CNV_LIT_FMT
is 1934416.
When set to YES
, HS will not convert empty string to NULL
before sending to DB2.
A related bug of EMPTYSTR_TO_NULL_OFF
is 2249392.
The gateway will translate an empty string in the WHERE
clause to NULL
by default. For example, the command select * from emp@gtwy where ename = ''
would be translated to DB2 as select * from emp where ename is NULL
. Oracle regards NULL
and empty strings as the same, while DB2 distinguishes between them. If you specify EMPTYSTR_TO_NULL_WHERE_OFF = YES
, then TG4DB2 will not attempt to translate the empty string.
A related bug of EMPTYSTR_TO_NULL_WHERE_OFF
is 25887100
When set to YES
, the gateway forces Oracle Integrating server to postprocess it.
A related bug of LIKE_OFF
is 2528836
The DB2 VALUE
function cannot handle decimal bind variables. The gateway translates Oracle NVL
to DB2 VALUE
function by default. In a situation like bug 2359741, set NVL_TO_VALUE_OFF
to YES
.
A related bug of NVL_TO_VALUE_OFF
is 2359741
By default, the CONCAT
function is post-processed. When this is set to YES
, the CONCAT
function is passed on to DB2 which may cause a SQLCODE -418 due to a DB2 restriction (bug 1269591).
Following are the valid HS initialization parameters that can be specified in member G4DB2ENV
:
Table 5-2 Valid HS Initialization Parameters for Member G4DB2ENV
Parameters | Generic Default |
---|---|
|
There is no default. |
|
|
|
|
|
There is no default. Normally you would specify the gateway service |
|
100 |
|
|
|
|
|
|
If GLOBAL_NAMES
is set to true in the INIT.ORA
file, then the HS_DB_DOMAIN
parameter must match the DB_DOMAIN
parameter in the INIT.ORA
file.
Refer to the Oracle Database Heterogeneous Connectivity Administrator's Guide for additional information about the HS initialization parameters for member G4DB2ENV
.
The following information varies from the information presented in the Oracle Database System Administration Guide for IBM z/OS (OS/390). The information presented in this guide facilitates you to use the Oracle Transparent Gateway for DB2:
HS_DB_INTERNAL_NAME
parameter default can be overridden. The default is DB21020
.
There is no default for HS_DB_NAME
. You could normally specify the gateway instance SID
as HS_DB_NAME
. When GLOBAL_NAMES
is set to true in the Oracle integrating server, this parameter must match the name of the database link.
HS_LANGUAGE
is obsolete and must not be specified. The appropriate setting is established automatically.
HS_NLS_DATE_FORMAT
is obsolete and must not be specified.
HS_NLS_NCHAR
is obsolete and must not be specified.
HS_RPC_FETCH_SIZE
parameter defaults can be overridden. If the HS_RPC_FETCH_REBLOCKING
parameter is set to ON
(the default), then the array size for SELECT statements is determined by the HS_RPC_FETCH_SIZE
parameter value. The recommended value for Oracle Transparent Gateway for DB2 is 40 000
. The value shipped with Oracle Transparent Gateway for DB2 is 40 000
.
The HS_RPC_FETCH_SIZE
parameter defines the number of bytes sent with each fetch between the gateway and the Oracle database server.
Notes:
This feature can provide significant performance enhancements, depending on your application design, installation type, and workload.The HS_RPC_FETCH_SIZE
value impacts the performance for elapsed time.
Generally, a higher HS_RPC_FETCH_SIZE
value can correlate with improved performance for elapsed time.
However, it is important to evaluate the requirements for elapsed time since a higher value associated with this parameter might also impact memory use.
Checklists are provided for configuration and post configuration.
Use the steps that follow to configure the gateway:
After installing the gateway software, you use the Oracle Universal Installer configuration utility to create one or more gateway instances. The configuration utility creates INSTLIB
and PARMLIB
libraries customized to your environment. Each gateway instance will have its own set of INSTLIB
and PARMLIB
libraries. These libraries contain the JCL procedures needed to configure the gateway instance.
Before using the configuration utility, determine the following information for the gateway instance to be created:
Subsystem name of the gateway
Names of the JCL procedures for the subsystem
Gateway SID
High-level qualifier for all PDS data sets
Volumes on which PDS data sets will reside
This information should be determined in advance because it is used multiple times in creating the gateway files and can be more complicated to change later. For all other information, you can accept the defaults and change them manually, later.
The following steps provide guidelines for creating a gateway instance using the configuration utility. It is assumed that you have already installed the gateway software, performed the APF authorization, and put the necessary files in the linklist. If not, then you will need to complete those tasks before continuing. For more information, refer to Chapter 4, "Installation".
Start the Installer and select the Oracle Transparent Gateway for the DB2 configuration option. This starts the configuration utility which prompts for the following information:
High-level qualifiers. Specify the high-level qualifiers for the location of the Oracle executable code and the gateway. Although you can use one high-level qualifier for both, it is recommended that you use a separate high-level qualifier for each.
The high-level qualifier for the location of the Oracle executable code (oracle_hlq
). For this high-level qualifier, you should include the version information, for example ORACLE.V10G
.
The high-level qualifier for the gateway (db_hlq
) is used to identify the PDS data set files for the gateway instance. This high-level qualifier should be labeled with the gateway name or a similar name, for example, ORACLE.GTW1
. It is recommended that you do not include version information in the gateway high-level qualifier, as this is likely to change over time.
INSTLIB
and PARMLIB
libraries. Specify where to create the new INSTLIB
and PARMLIB
libraries. This can be done by using IBM Storage Management Subsystem (SMS) or by manually specifying a volume and unit.
Subsystem Definition Parameters. Specify the subsystem name, gateway SID
, net SID
, port on which the gateway should listen for remote connection attempts, and names of the JCL procedures for the gateway.
Gateway Parameters. Specify the basic OSDI parameter file definitions for the gateway.
ORA$FPS
Control File Definitions. Specify the information to create an ORA$FPS
file. This file is used to create various files of a particular size. You can enter new values for your installation or enter default values and later modify the ORA$FPS
file that is created.
The configuration utility generates two PDS data set files for the INSTLIB
and PARMLIB
libraries, db_hlq
.INSTLIB
and db_hlq
.PARMLIB
.
The INSTLIB
library contains all the sample JCL required to configure the gateway instance and perform simple tasks like starting and stopping the gateway.
The PARMLIB
library contains all the required parameter files needed to start the gateway instance.
The degree to which the information provided in Step 1 is accurate will determine how much the JCL procedures and parameter files need to be modified in order to configure the gateway instance.
All JCL
procedures (or batch jobs) need to be reviewed carefully to ensure that they are valid. The JOBCARD
job provided is only a default used by Oracle and will need to be tailored to your environment.
The batch jobs can be divided into the following categories:
Two sample PROC
s that define the gateway address space and the Net address space, and a procedure to copy them into a system installation library.
The PROC
s are named in Step 1 of the configuration process and are cross-referenced to the OSDI parameter file. Each procedure must be defined to RACF
as a started task and associated with a user.
The batch job used to copy the two PROC
s into a system PROCLIB
library job is called COPYPROC
and must be customized to point to the system PROCLIB
library.
The other batch job associated with this is called STRTSRVC
. The STRTSRVC
job is used to define and activate the subsystem. Another set of PROC
s provide basic functions like starting the subsystem services.
The parameter files are located in db_hlq
.PARMLIB
. They are the core definition files for the gateway. You should review these files for accuracy. The parameter files can be divided into the following categories:
OSDI parameter files. Of this group of files, the subsystem definition file is the core file. This file is called only by the OSDI subsystem name. This file has a major impact on the other JCL batch jobs and parameter files and should be modified with care. The following is an example of the subsystem definition file:
INIT (ORASSI,SSN1) DEF SVG SSID(SSN1) DESC('OSDI Gateway 10G Subsystem - SSN1') DEF SRV DB2GW1 PROC(ODB2GW1) TYPE(GTW) - DESC('V10G GATEWAY Service') - SID(GTW1) PARM('ORACLE.ORA1.PARMLIB(G4DB2PRM)') DEF SRV ORAN10 PROC(ORA1N10) TYPE(NET) - DESC('Oracle V10G Net Service') - SID(ORAN) PARM('HPNS PORT(1501) ENCLAVE(SESS)') SHOW SERVICEGROUP LONG START DB2GW1 START ORAN10
The file related to this is called G4DB2PRM
. This contains all the OSDI specific parameters. These parameters are documented in detail in this guide. Most default parameters are acceptable for most basic installations. The following is an example of this file:
* SSN1 OSDI SUBSYSTEM PARAMETER FILE. * USED BY SUBSYSTEM SSN1 SERVICE DB2GW1 PROC DB2GW1 * LOAD MODULE TO USE. SERVER_LOADMOD(G4DB2SRV) * NUMBER OF ADDRESS SPACES TO START. VALUES ARE 1-256 INIT_ADR_SPACES(1) * MAXIMUM NUMBER OF SESSIONS ALLOWED FOR THIS ADDRESS SPACE MAX_SESSIONS(500) * MAX MEMORY ALLOWED PER SESS. VALUES ARE NNNN {K|M}. MAX_SESSION_MEM(100M) * INITIAL STACK SIZE. VALUES ARE NNNN {K|M}. INIT_STACK_SIZE(128K) * TURN ON SMF RECORDING. VALUES 0 AND 128 THROUGH 255. SMF_STAT_RECNO(0) * EXTERNAL AUTHENTICATION LOGON_AUTH(G4RRSAF) * STORAGE CUSHION. VALUES ARE NNNN {K|M}. REGION_MEM_RESERVE(10M) * DATABASE DATASET NAME PREFIX. DSN_PREFIX_DB(G4DB2) * TRACE DATASET NAME PREFIX. TRACE_DSNAME(ORACLE.GTW1.TRACE.&ORASESST..T&HHMMSS) * ALERT DATASET NAME PREFIX. ALERT_DSNAME(ORACLE.GTW1.ALERT.&ORASESST..T&HHMMSS)
The third file in this section is called SUBSYS. It provides the command which needs to be issued in order to define and activate the subsystem definition file.
The G4DB2FPS parameter file contains the default FPS parameters for creating the trace files. Remove any which are not required. If this is defined correctly, then defining additional tran files becomes easier. The minimum that is recommended is DFLT. Refer to this guide for details about what to code for this parameter file. An example is as follows
* Default parameters FILE_GROUP(DFLT) RECALL(NONE) MOUNT(NO) DEFAULT_SPACE(10000 10000) UNIT(SYSDA)
Instance-specific files
The G4DB2ENV contains the environment variables required for the gateway.
Two other files which can be used are the TNSNAMES file and the SQLNET file. The TNSNAMES file provides the default TNSNAMES entries for users to access this instance both through cross memory and through TCP/IP. This can be used by the Oracle database server than connects to this gateway.
When the COPYPROC JCL
has been customized correctly and the PROC
names defined for the database are valid, run the COPYPROC JCL
. It copies the two subsystem PROC
s into a system PROCLIB
library.
If a local date exit is required, then copy the DSNXVDTX
exit to the DB2 exit library. The gateway exits are in the AUTHLOAD
library.
The DB2 Local Date Exit (DSNXVDTX
) provided with the gateway supports Oracle date formats DD-MON-RR
and DD-MON-YYYY
. This requires a DB2 installation update to set the DB2 local date length. Option 10 on DB2 install panel DSNTIPF
, which specifies the local date length, must be set to 11, if not already set as 11.
You must stop and start the DB2 subsystem to access the DB2 local date exit and activate the DB2 local date length parameter.
There are now 4 versions of the DB2 views. If you use DB2 7.1, then use one of the DB2 7.1 SQL scripts. If you use DB2 8.1, then use one of the DB2 8.1 SQL scripts.
The "external security" views are if you use DB2 external security. This DB2 feature calls an external exit which typically makes RACF calls to see if a user has access to a DB2 object (rather than checking against GRANT information in the DB2 catalog).
If you use GRANTs in DB2 to provide access to DB2 objects (such as tables), then use the "standard security" views for your release.
Note that the external security views show all objects of a particular type. For example ALL_TABLES shows all tables in the DB2 catalog, where the standard security views only show the tables you have been granted access to through the DB2 GRANT command. Since DB2's external security uses an exit to check a user's access to DB2 objects (like tables), the GRANT information in the DB2 catalog is ignored, and so can't be used to verify if a user has access to an object.
The external security views allow a user to see the definition of all of the objects in the DB2 catalog. However, access to the objects is still restricted by DB2, and a user can still only access objects that they have access to.
The SQL scripts available are:
G4DDVWS7 DB2 7.1 Standard Security views G4DDVWR7 DB2 7.1 External Security views G4DDVWS8 DB2 8.1 Standard Security views G4DDVWR8 DB2 8.1 External Security views
For the DB2 database named in the configuration, use the DB2 SPUFI utility or batch job to run the following SQL scripts for all gateway installations:
db_hlq
.INSTLIB(G4DB2DDT)
oracle_hlq.
SRCLIB(G4DDVWxx)
where xx
is one of the SQL Scripts listed above (S7
, R7
, S8
, R8
)
Attention:
If you migrated from a lower release of Oracle Transparent Gateway for DB2, then you must first rundb_hlq.
INSTLIB(G4DB2DLV)
. Refer to Chapter 11, "Migration and Coexistence with Existing Gateways", for more information.Oracle Transparent Gateway for DB2 requires you to bind gateway DBRM
G4DB2PLN
to 10 separate packages. The 10-package mechanism enables a maximum of 5000 DB2 cursors per session while keeping EDMPOOL usage under control.
When you bind a package, you specify the collection to which the package belongs. These 10 packages should be included in the package list when binding the gateway plan. You can bind the packages in either of two ways:
In a batch job, with the JCL
supplied in the G4DB2BPL
member of the db_hlq
.INSTLIB
library. The JCL
supplied in this member performs both the bind package and the bind plan.
Interactively, using DB2I
.
Use the IBM DB2I
Bind Package panel options to bind each package. This step must be performed 10 times, once for each collection ID (G4DB2V102021A through G4DB2V102021J). The panels that are discussed below are for DB2, release 6.1, and release 7.1. In some cases, improved performance can be achieved by selecting different options from those discussed. Consult with your DB2 administrator to determine which options best suit your particular environment.
On the DB2I Bind Package panel, use the suitable settings for your installation. Following is a description of each option:
Option 1 specifies which DB2 system to use to bind the package. If left blank, then this option defaults to the local DB2 system.
Option 2 specifies the DB2 collection in which the package is located. The following naming convention must be used for the collection ID: G4DB2V102021x
, where x is any letter in the range A through J. The bind package must be performed 10 times, once for each collection ID from G4DB2V102021A
through G4DB2V102021J
.
Option 3 specifies whether you are creating a new package or making a copy of an existing package. You must specify DBRM
for this option.
Option 4 specifies which DBRM
member to bind. You must specify G4DB2PLN
for this option.
Option 5 specifies a password for the library name listed in the LIBRARY
field. You can leave this option blank.
Option 6 specifies the name of the library containing the DBRM
specified in the MEMBER
field, G4DB2PLN
. In this example, the DBRM
is located in data set oracle_hlq
.SRCLIB
.
Option 7 specifies whether to change current defaults. You must specify YES
to be able to change current defaults.
Option 8 specifies whether to enable or disable other IBM
intersystem connection types to use with this package. You should specify NO
for this option.
Option 9 specifies the primary authorization ID owning the package.
Option 10 should be left blank so that the qualifier specified on the Bind Plan panel takes precedence.
Option 11 specifies whether to replace an existing package or add a new one. You should specify REPLACE
for this option.
Option 12 specifies whether to replace a specific version of the package or create a new one. You should leave this option blank.
For additional information about the options for the Bind Package panel, refer to the IBM documents for your platform and operating system.
When you complete the changes on the Bind Package panel, press Enter to continue.
Option 1 specifies the isolation level of the package. You should leave this option blank so that the isolation level specified on the Bind Plan panel takes precedence.
Option 2 specifies when to validate DB2 objects or privileges for the package. You should specify BIND
for this option.
Option 3 specifies when to release locks on resources. You should leave this blank so that the resource release time specified on the Bind Plan panel takes precedence.
Option 4 specifies whether to obtain EXPLAIN
information on how SQL statements in the package run. You can leave this option blank.
Option 5 specifies whether data currency is required for ambiguous cursors when the isolation level of cursor stability (CS) is in effect. This option also determines whether block fetching can be used for distributed, ambiguous cursors.
Although cursors used in block fetch operations result in reduced network traffic, you are still vulnerable to reading data that has already changed. In a block fetch, DB2 fetches as many rows as can fit into a buffer before sending the entire buffer over the network. During that time, the underlying data might have been modified before the application actually asks for the data. Situations such as fetching a row of values that no longer exists, or missing a recently inserted row, can occur. If these types of situations are acceptable, then data currency is not required (CURRENTDATA=NO
). If data currency is required (CURRENTDATA=YES
), then be aware that a separate buffer is sent over the network for each row fetched.
Option 6 specifies the use of parallel processing (if possible) to run queries. You can leave this option blank.
Option 7 specifies whether run-time rules or bind-time rules apply to dynamic SQL statements at run time. You should specify RUN
for this option.
Option 8 should be left blank.
Option 9 specifies whether to have DB2 determine an access path at run-time using values for host variables, parameter markers, and special registers. You should set this option to NO
.
Option 10 specifies whether to defer preparation for dynamic SQL statements that refer to remote objects. You should set this option to NO
.
Option 11 determines whether DB2 keeps dynamic SQL statements after commit points. You should set this option to NO
.
For additional information about the options for the Defaults for Bind Package panel, refer to the IBM documents for your platform and operating system.
When you complete the changes on the Defaults for Bind Package panel, press Enter to continue. Repeat this step, varying only the collection ID, until all packages (G4DB2V102021A
through G4DB2V102021J
) are bound.
You can bind the gateway plan in either of two ways:
As a batch job
Use a batch job with the JCL
supplied in the G4DB2BPL
member of the Oracle db_hlq
.INSTLIB
library. The JCL supplied in this member performs both the bind package and the bind plan.
Note:
If the job has run successfully in "Step 6: Bind the DB2 Package", then it does not need to be run again.You can proceed to "Step 8: Grant EXECUTE
on DB2 Plan".
Interactively, using DB2I
Use the IBM DB2I Bind Plan panel options to bind the plan. (The panels discussed are for DB2 release 7.1.) In some cases, improved performance can be achieved by selecting options different than those discussed in this section. Consult with your DB2 administrator to determine which options best suit your particular environment.
On the IBM DB2I Bind Plan panel, use the appropriate settings for your installation. The options are:
Option 1 specifies the first DBRM to include in the gateway plan. Specify G4DB2IX
, G4DB2PRC
, and G4DB2V51
for this option.
Option 2 specifies the password for the libraries listed in the LIBRARY
field. You can leave this option blank.
Option 3 specifies the name of the PDS containing the gateway DBRMs. In this example, the DBRMs are located in data set oracle_hlq
.SRCLIB
.
Option 4 must be set to YES
to specify additional DBRM members.
Option 5 specifies the name of the DB2 application plan to create. Specify the DB2 plan name entered on the gateway configuration panel. The default plan name is G4DB2PLN
.
Option 6 must be set to YES
to change current defaults.
Option 7 specifies whether to enable or disable other IBM intersystem connection types to use with this package. Specify NO
for this option.
Option 8 specifies whether to include a package list for the plan. You must specify YES
for this option.
Option 9 designates the primary authorization ID owning the plan.
Option 10 specifies the user ID from the gateway installation panel that is used as the qualifier. This value must be set to OTGDB2
.
Option 11 specifies the size (in bytes) of the authorization cache. For details about this option, refer to the IBM documents for your platform and operating system.
Option 12 specifies whether this plan is new or is being replaced. Specify REPLACE for this option.
Option 13 determines whether users with the authority to bind or run the existing plan are to keep that authority over the changed plan. Specify YES for this option.
Option 14 specifies the initial server to receive and process SQL statements. You can leave this option blank.
For additional information about the options for the Bind Plan panel, refer to the IBM documents for your platform and operating system.
When you complete the changes on the Bind Plan panel, press Enter to continue.
Confirm the DBRM library. Press the PF3
key to continue.
The IBM DB2I Defaults panel lets you set the ISOLATION LEVEL
and RESOURCE
RELEASE TIME
to those values applicable to your site. ISOLATION LEVEL
specifies the ways in which read-only operations are isolated from the effects of concurrent write operations.
The gateway does not change the locking behavior of the DB2 database. Therefore, it is important to understand how the ISOLATION LEVEL
can impact the behavior of Oracle applications accessing DB2 through the gateway.
Oracle applications written for the Oracle database server and deployed later to access DB2 through the gateway might require special consideration. Oracle read operations do not prevent concurrent write operations and, therefore, do not have to COMMIT
in order to allow subsequent updates to the Oracle database server. Thus, many Oracle applications do not COMMIT
at the end of read operations.
DB2 generally requires a COMMIT
to release read transaction locks to allow subsequent write operations by other DB2 transactions. If a previously developed Oracle application that does not run a COMMIT
statement after reading data is redirected to a DB2 database, then DB2 might prevent subsequent DB2 transactions from updating the data. This is because, unlike the Oracle database server, DB2 acquires locks when data is read.
Refer to the IBM documents for your platform and operating system before setting the ISOLATION LEVEL
to one of the following settings:
Cursor stability (CS) can be chosen to work with the gateway for maximum concurrency with data integrity. However, if a read-only application does not COMMIT
or close the cursor, then the CS isolation level might result in read locks being set that would prevent concurrent and subsequent write operations.
Uncommitted read (UR). This isolation level can be used with the gateway to allow Oracle applications that do not COMMIT
with read transactions to access DB2 without preventing write operations. It is important to note that the UR isolation level allows gateway applications to read uncommitted data.
Repeatable read (RR) results in locks being held on all rows or pages accessed, preventing concurrent and subsequent write operations until the application performs a COMMIT
.
Ensure that the other parameters are specified as listed.
On the IBM DB2I Package List for Bind Plan panel, fill in the collection names and package ids to include in the gateway plan. If you plan to run DB2 stored procedures through the gateway, then insert an asterisk (*) for the collection ID and package ID as the last entry in the COLLECTION
and PACKAGE-ID
columns.
When you complete the changes on the Package List for Bind Plan panel, press the PF3
key to bind the plan.
Using the DB2 SPUFI utility, grant EXECUTE
authority to public on the DB2 plan by running the G4DB2GNT
member in the gateway db_hlq
.INSTLIB
library.
Edit the members of the PARMLIB
library to ensure the gateway parameters are set appropriately for the installation. For the list of PARMLIB
members, refer to"G4DB2ENV (Environment Variables and HS Initialization parameters)" .
The ORA$FPS
parameter file contains file group definitions which are specified using keyword(value)
syntax. Each definition must start with the keyword FILE_GROUP(name)
and continue until the next FILE_GROUP
keyword is encountered. Comments must start with an asterisk (*) and can begin in any column as long as comments (that are on the same line as keywords) are separated from the last keyword by at least one blank.
Keywords can be coded one per line or strung together on the same line separated by at least one blank, but a keyword (value) pair cannot be split across two lines. No defaults are defined for the parameters. The default file group (DFLT) supplies parameters for any file group that is completely omitted from the file management parameters.
With this release, the only File Groups supported by or applicable to the gateway are DBTR, indicating the attribute for gateway trace files, and NTTR (network trace).
Specifies an SMS data class name to be specified on DEFINE CLUSTER
or dynamic allocation requests to create new data sets. DATACLAS
can be abbreviated DATACL.
Specifies default primary and secondary space quantities for a data set that is being created. The secondary quantity is optional and is ignored at this time. Both values must be numbers and are expressed in kilobyte (1024-byte) units. DEFAULT_SPACE
can be abbreviated SPA.
Specifies the file group to which the file management parameters belong, where name is one of the allowed 4-letter file group names. This ends any in-progress file group definition and begins a new one. FILE_GROUP
can be abbreviated FILE.
Specifies an SMS management class name to be specified on DEFINE CLUSTER or dynamic allocation requests to create new data sets. MGMTCLAS
can be abbreviated MGMTCL.
Specifies an SMS storage class name to be specified on DEFINE CLUSTER
or dynamic allocation requests to create new data sets. STORCLAS
can be abbreviated STORCL.
Specifies an allocation unit name to use in dynamic allocation requests that create new non-VSAM data sets.
Specifies a volume serial number to use in IDCAMS DEFINE CLUSTER
commands for VSAM data sets or in dynamic allocation requests that create non-VSAM data sets.
Only a single volume serial can be specified. Because of this limitation, it is recommended that you use storage management class parameters instead of explicit volumes. VOLUMES
can be abbreviated VOL.
Example:
Storage management parameter example:
* Oracle gateway file management parameters * Trace data files FILE_GROUP(DBTR) DEFAULT_SPACE(200 50) * a comment * Default for groups not specified FILE_GROUP(DFLT) DEFAULT_SPACE(10000 5000) UNIT(SYSDA) VOL(TEMP01)
This DD statement is optional and is the default destination for network error messages. SYSOUT
or a sequential disk data set can be specified.
Services that are managed by Oracle Database 10g execute as system address spaces, similar to started tasks or STCs. Some of the OS/390 system functions that are called by Oracle Database 10g services perform authorization checks based on the OS/390 user ID that is associated with the service address space. Depending on the security configuration and standards of your installation, those system functions may fail if no user ID is associated with the address space. You, or security personnel for your installation, may need to take steps to ensure that Oracle Database 10g services have an associated user ID that can be authorized for system functions that are called by the database and network services.
With RACF, this authorization is normally accomplished by defining appropriate profiles in the STARTED resource class. Each profile associates a RACF user ID with a started task based on the JCL procedure name of the started task. You will choose and specify JCL procedure names for Oracle Database 10g services when those services are configured. You may want to decide on procedure names now, however, so that RACF profiles can be defined. There are no special procedure naming requirements as far as Oracle Database 10g is concerned, so you can choose procedure names that meet the standards or requirements of your installation. Of course, the names should not be the same as the names of any members already in your system procedure library. The ISPF panel-driven gateway configuration process will generate a JCL procedure name based on the service SID specified. The generated name is G4sid
, where sid
is the service's SID. After the configuration process is complete, you may change the name of this procedure if it does not match the standard naming conventions at your site.
Defining the JCL procedure name in the USER
resource class is an alternate method in which the procedure name itself is also used as the userid.
If you are already running the OSDI and TNS programs as started tasks (as opposed to submitting them as batch jobs), then your installation probably already has STARTED
or USER
profiles for the associated JCL procedures. You should not rely on those for Oracle Database 10g because the Oracle Database 10g procedures should have different names. Plan to create at least two new STARTED
or USER
profiles, one for the gateway service and one for the network service. These may be all that you need, because different instances of a type of service can generally share the same JCL procedure. You may want to create additional profiles, though, if you want different instances of a service to run with different user IDs. Note that this requires using distinct JCL procedures even though the procedures themselves may be otherwise identical.
Details on the STARTED
and USER
resource classes are in the RACF System Administration Guide. The RDEFINE
command that is used to add profiles is described in the RACF Command Language Reference.
With RACF, it is possible to associate a user ID with a started task using a started procedures table that is built with Assembler macros somewhat like the resource class table discussed in the previous section. Activating such changes requires an IPL, however, and is not the preferred method. Refer to the RACF System Administrator's Guide for more information.
This can be done by running the STRTSRVC member. This uses the SETSSI command to define the OSDI subsystem. If the user is not able to issue this command then the define service needs to be done by a system programmer. A sample of the command to issue is provided by the SUBSYS file in the PARMLIB.
At the end of this step, you should have a gateway and a Net subsystem defined and active.
The following optional steps can be performed any time after the gateway is configured.
Step 1: Move Reentrant Modules to z/OS Link Pack Areas
Step 2: Examine Oracle Dump Data Sets and Modify as Necessary
Step 3: Examine Oracle Trace Data Sets and Modify as Necessary
The Oracle AUTHLOAD
modules that have RMODE
set to ANY
and are reentrant can be placed in the z/OS extended pageable link pack area (EPLPA) to decrease storage requirements. Other modules that are linked with RMODE
set to 24 and are reentrant can be placed in the z/OS pageable link pack area (PLPA) below the 16M line. For modules used by multiple batch or TSO users concurrently, real storage working set requirements are greatly reduced because all users of a given module share the same copy.
Following are some considerations for placing Oracle modules in z/OS link pack areas:
A z/OS IPL is generally required to add, remove, or replace a module in the z/OS link pack areas. This complicates the timely application of maintenance or fixes.
It might be necessary to move a module from the Oracle AUTHLOAD
library to another z/OS data set so it is accessible for z/OS link pack area placement.
Adding modules to the PLPA reduces the maximum private area size of all z/OS address spaces. This impact must be evaluated before moving new modules.
For details about adding modules to z/OS link pack areas, refer to the IBM documents for your platform and operating system. Details regarding which modules are candidates for z/OS link pack area placement are covered in the Oracle Database System Administration Guide for IBM z/OS (OS/390).
When the gateway encounters an abend in one of its tasks, it dumps the abend to a SYS1.DUMP
data set. Because the gateway does not attempt to dynamically allocate dump data sets, you must ensure that a SYS1.DUMP
data set is always available. The SYS1.DUMP
data set must be large enough to hold two address spaces (the Oracle address space if there is one and the gateway address space). Refer to the IBM documents for your platform and operating system for information about managing dump data sets.
If a SYS1.DUMP
data set is not available when needed, then z/OS directs OSDI dumps to a SYSMDUMP DD
statement if coded in the OSDI Gateway startup JCL.
If a SYSMDUMP DD
statement is directed to SYSOUT
, then multiple dumps are preserved (at the cost of potentially large amounts of SPOOL space). The z/OS external writer must be used to extract such dumps from the SPOOL file. This file can be written to a tape or DASD data set using an IBM external writer. For information about using the external writer program, refer to the IBM documents for your platform and operating system.
Do not specify SYSUDUMP
and SYSABEND
in OSDI Gateway startup JCL, because they produce dumps that are not computer-readable.
The gateway attempts to gather as much information as possible when internal errors occur for a user or process and when gateway tracing is turned on through the TRACELEVEL
parameter in the gateway environment PARMLIB
member G4DB2ENV
. This information is placed in an Oracle trace data set. The trace data set name is generated based on the OSDI TRACE_DSNAME
value defined in the OSDI Gateway Region parameters in PARMLIB
member <service_name>PARM.
The TRACE_DSNAME
value can specify either a SYSOUT
specification or a data set name. For complete information and syntax of this parameter, see "TRACE_DSNAME | TDSN".