| Oracle® Transparent Gateway for DB2 Installation and User's Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B16220-02 |
|
|
View PDF |
| Oracle® Transparent Gateway for DB2 Installation and User's Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B16220-02 |
|
|
View PDF |
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 a
JOBNAME 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, nK (denoting a multiplier of 1024), or nM (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, nK (denoting a multiplier of 1024), or nM (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 nnnS for seconds or nnnM 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-31, 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 PROCs that define the gateway address space and the Net address space, and a procedure to copy them into a system installation library.
The PROCs 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 PROCs 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 PROCs 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 PROCs 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".
