Architecturally, the Oracle database server views all client connections as network connections regardless of whether the connecting client is on the same or a different computer than the server. When you run Oracle tools, utilities, or your own Oracle-accessing 3GL programs on z/OS, Oracle Net software that is part of the Oracle program interface code is executed-even if you are connecting to an Oracle instance on the same z/OS system using cross-memory services. This means Oracle Net features, parameter files, and so on, come into play in any Oracle programs you run on z/OS. This chapter describes z/OS-specific considerations in using Oracle Net and supplements the product information in the Oracle Net Services Reference Guide.
When we use the term "application" in this chapter we mean any Oracle-accessing program that you run on z/OS, including Oracle-supplied tools and utilities and your own 3GL applications using OCI or an Oracle Precompiler language.
Different Oracle Net considerations apply to Oracle-accessing programs running under Oracle Access Manager for CICS TS or Oracle Access Manager for IMS TM. Refer to Chapter 6, "Developing Oracle Applications to Run on z/OS" for more information.
This chapter contains the following sections:
Oracle Net on z/OS supports two different protocols: cross-memory (XM) and TCP/IP (TCP). Normally you use XM protocol to connect to a local Oracle server (on the same z/OS system as the application) and TCP protocol to connect to a remote Oracle server (on z/OS or any other operating system on which Oracle runs). You can also use TCP protocol to connect to a local server, but it is less efficient than using XM. (There are, however, circumstances in which you might do this in order to exploit certain server features, discussed later.) You cannot use XM protocol to connect to an Oracle server on a different system than the application.
Most applications connect to just one Oracle server and so use only a single protocol at a time. Applications can connect to multiple Oracle servers, however, and it is possible for an application to mix protocols, such as using XM for one connection and TCP for another, simultaneously.
Regardless of which protocol you use, Oracle Net address data for a server must be available in order to locate and connect to the server. Address data depends on the protocol used and includes things like a TCP/IP hostname and port, server name or identifier. The address data for a server can be imbedded in the application program, supplied through an external (but local) mechanism such as a parameter file or environment variable, or obtained from a remote name resolution service such as Oracle Names or an LDAP service. Using a remote name resolution service is applicable to TCP connections only; server address data for XM connections is always supplied locally.
When an Oracle tool, utility, or 3GL application runs on z/OS, it makes a specific call to connect to an Oracle server. The details of this call vary depending on application design and on which program interface (API) is used, but in all cases the application is allowed to supply a character string representing either a name or address data for the target server. With Oracle tools and utilities this data usually is combined with the Oracle user ID and password into a single string supplied on the command line or on an input parameter or command. By convention, the user ID and password are separated by a forward slash and the password and name or address data are separated by an "at" sign (@). So, for example, a SQL*Plus session might be started in TSO with a command similar to the following:
READY sqlplus scott/tiger@testdb1
In this example the user ID and password are scott
and tiger
respectively, and a target server name testdb1
is included.
The behavior of Oracle Net in determining the protocol and address data depends on whether the connect call supplies target server data and whether that data specifies a name or an address:
If server data is supplied and is a simple or dotted name, the name is looked up in a local tnsnames.ora
parameter file or using a remote names server. The choice between a file and a names server is controlled by other Oracle Net parameters. (Refer to the section "Oracle Net Output Files".)
If server data is supplied and is an Oracle Net address (normally beginning with a left parenthesis), the address is used verbatim and no external file or remote names server is accessed.
If server data is not supplied, a default hierarchy of external address data sources is examined to determine the protocol choice and target server address.
The external sources that are checked when server name or address data are omitted are, in descending order of precedence:
An included DD statement (or comparable TSO allocation) in the application job or session of the form //ORA@
sid
DD DUMMY, where ORA@ is exactly as shown and sid
is the 1-character to 4-character SID for an XM protocol address. Refer to the section "XM Protocol Address" . Only XM protocol is supported with this mechanism. The DD is not opened by Oracle software; it is supplied only to convey the SID. An Oracle SID on z/OS can be up to 8 characters long; SIDs longer than 4 characters cannot be accessed with this technique. If multiple DD names beginning with ORA@ are present, the behavior is unpredictable.
A character value assigned to the environment variable TWO_TASK. This value is interpreted exactly the same as the target server data: it can be either a target server name (looked up in a tnsnames.ora
file or remote name resolution service) or an Oracle Net address string. Either XM or TCP protocol can be specified with this mechanism.
A character value assigned to the environment variable ORACLE_SID. This value is interpreted as a 1-character to 8-character SID for an XM protocol address. Refer to "XM Protocol Address" . Lowercase letters in this SID are converted to upper case. Only XM protocol is supported with this mechanism.
If an application supplies no server name or address data on its connection attempt and none of the three mechanisms in the prior list is used, the connection will fail, typically with an ORA-06413 error.
The XM protocol exploits the unique cross-memory hardware and software facilities of z/Series and z/OS. It can be used only when the application and the target Oracle server are on the same z/OS system. It cannot be used between separate nodes of a Sysplex cluster, even if the nodes are logical partitions (LPARs) in the same physical processor complex.
Unlike other protocols, XM does not use shadow or surrogate processes to execute application requests in the database server. Instead, the Oracle program interface issues a System/390 Program Call (PC) hardware instruction to transfer execution directly into the server address space to execute requests. Request input and output data are copied between the application and the server address spaces using cross-memory instructions. On conclusion of a server request, control returns to the application through a Program Return (PR) instruction. Thus, client/server interactions with XM protocol take place at processor instruction speeds, without expensive context-switching or task-switching operations.
There are several characteristics of the XM protocol of which you should be aware:
All processor (CPU) time consumed by your application while executing in the Oracle server address space is accounted by z/OS under your application's task and address space, not those of the Oracle server. This CPU time appears, for example, in the z/OS SMF accounting data written for your application job or session, added to the CPU time that your application consumed on its own, locally.
Database and other file I/O operations that your application issues while executing in the Oracle server address space are accounted to the Oracle server address space, not to your application job or session.
When you use XM protocol, your database requests run at the z/OS dispatching priority or WLM goal of your job or session, not at the priority or WLM goal of the Oracle server address space.
If your application uses Oracle server parallel execution features, such as parallel query processing, some of the application's work is done by local subtasks in the server address space. This work does run at the z/OS dispatching priority or WLM goal of the server address space and is not accounted by z/OS to your application job or session.
Oracle for z/OS provides the ability to impose native z/OS security checks (through IBM's RACF or a comparable product) on XM protocol connections. If your installation has activated this feature, the z/OS user ID under which your application runs must be granted access authorization for the target Oracle server. This check is completely separate from Oracle logon processing, the Oracle user ID and password, and so on.
Unlike TCP, the XM protocol is always, by its nature, synchronous and "half duplex." A few rather specialized server features require a protocol capable of asynchronous or nonblocking operation. To exploit those specific features on z/OS you must use TCP protocol, even if the application and server are on the same z/OS system.
If you are not using an ORA@sid
DD statement or an ORACLE_SID environment variable to indicate use of XM protocol, you will need to code an XM protocol address, either in a tnsnames.ora
file, discussed in "Oracle Net Files", or directly in a connect parameter or argument. The generic considerations for coding protocol addresses are described in the Oracle Net Services Reference Guide. Refer to the guide for general syntax conventions and for details and examples illustrating TCP protocol. Here we describe the name-value pairs that make up an XM protocol address, unique to z/OS. All pairs described are used in the ADDRESS portion of the protocol address.
(PROTOCOL=XM)
This pair is specified exactly as shown and is required to indicate that XM protocol is being used.
(SID=sid)
This specifies the 1-character to 8-character SID (Service Identifier) of the target z/OS Oracle server. The SID is a unique identifier assigned to each Oracle server configured on a given z/OS system. All SIDs use upper case letters; any lowercase letters are converted to upper case. If you specify SID, which is recommended, neither of the following two parameters is used. If you do not specify SID, both of the following parameters are required.
(SUBSYS=ssn)
This specifies the 1-character to 4-character z/OS subsystem name of the OSDI subsystem in which the target Oracle service is defined. Any lowercase letters in ssn
are converted to upper case.
(SERVICE=srvname)
This specifies the 1-character to 8-character OSDI service name of the target Oracle server. Any lowercase letters in srvname
are converted to upper case.
Here we show several examples that use XM protocol. All use SQL*Plus, but the techniques shown extend readily to other tools and utilities and to your own 3GL applications.
In the first example, SQL*Plus is invoked at the TSO READY prompt and the XM address is passed directly in the first command line parameter with the user ID and password. The target server has the SID "ODB1". This example assumes that TSOLIB/STEPLIB and ORA$LIB are already allocated as needed.
READY sqlplus scott/tiger@(address=(protocol=xm)(sid=odb1))
In the next example, a batch job execution of SQL*Plus omits the server data from the user ID/password parameter and uses an ORA@sid DD to specify the same target as the prior example.
//PLUS EXEC PGM=SQLPLUS,PARM='SCOTT/TIGER' //STEPLIB DD DISP=SHR,DSN=ORACLE.V10.CMDLOAD //ORA$LIB DD DISP=SHR,DSN=ORACLE.V10.MESG //ORA@ODB1 DD DUMMY //SYSIN DD DISP=SHR,DSN=FDNR30.PROD.SQL(WNP14C) //
In the third example, a similar batch job relies on an entry in a tnsnames.ora
file to obtain the XM address data. The tnsnames.ora
file is discussed in "Oracle Net Files" . Typically this file would be a data set or PDS member rather than instream as shown in the following example:
//PLUS EXEC PGM=SQLPLUS,PARM='SCOTT/TIGER@PRODDB1' //STEPLIB DD DISP=SHR,DSN=ORACLE.V10.CMDLOAD //ORA$LIB DD DISP=SHR,DSN=ORACLE.V10.MESG //TNSNAMES DD * proddb1 = (description= (address=(protocol=xm)(sid=odb1))) /* //SYSIN DD DISP=SHR,DSN=FDNR30.PROD.SQL(WNP14C) //
The last example shows the use of an ORACLE_SID environment variable in a z/OS UNIX shell to specify the target server. It assumes that the required ORACLE_HOME, PATH, and LIBPATH are already set.
$ export ORACLE_SID=odb1 $ sqlplus scott/tiger
Oracle Net TCP protocol provides connectivity between client and server over a LAN or WAN as well as local connections. All Oracle platforms implement TCP/IP protocol, so it can be used by outbound z/OS client applications to connect to Oracle servers on any supported platform. If the installation configures and runs an Oracle Net (Listener) service on z/OS, inbound remote clients on any platform can connect to Oracle servers running on z/OS. This material deals mainly with outbound z/OS client considerations; inbound client considerations are covered in the Oracle Database System Administration Guide for IBM z/OS on System z.
The following considerations pertain to outbound client use of TCP protocol on z/OS:
In Oracle9i Version 9.0 and earlier, outbound z/OS clients' network I/O operations were performed by the Oracle Net address space. Thus, clients were dependent on the Net service being up and operational, and an interruption in the Net service impacted client operations. Starting with Oracle 9i Version 9.2 and in all subsequent releases, outbound clients interact directly with the z/OS TCP/IP service and are not affected by Oracle Net service availability.
IBM's z/OS TCP/IP protocol service can be stopped and started. If it is stopped, in-progress Oracle Net TCP connections receive permanent errors and all new connection attempts fail until the service is restarted.
Using z/OS TCP/IP requires the application to be a z/OS UNIX System Services process. If the application is running in a z/OS UNIX shell, it already is a POSIX process and the requirement is met. Batch jobs and TSO sessions (not in a shell) generally are not z/OS UNIX processes and the first use of TCP/IP (or any other z/OS UNIX interaction) will cause the job or session to be "dubbed" or made a z/OS UNIX process.
For dubbing to succeed, the z/OS user ID associated with the job or session must have a default OE segment defined to the z/OS security service. If dubbing fails, TCP/IP interactions fail and the client will be unable to connect to a server using TCP. Successful dubbing does not affect application program operations nor does it change the POSIX indicator. If you are uncertain about whether your application's z/OS user ID is capable of being dubbed, consult with your security administrator.
Using TCP protocol means it is possible to use a remote name resolution service such as Oracle Names or LDAP instead of locally-configured address data (for example, a tnsnames.ora
file) to contact Oracle database servers. Consult with your systems administrator or database administrator to determine if name resolution services are available in your installation. Using a remote name service will also require dubbing independent of the protocol selected as a result of the lookup.
Oracle Net TCP protocol address particulars are covered in the Oracle Net Services Reference Guide.
When you use Oracle Net (including local server connections) any of several Oracle Net input (parameter) files may be involved. In some situations output files (log or trace files) may also be produced. General considerations for these files and what goes in them are covered in the Oracle Net Services Reference Guide. Here we address issues specific to z/OS.
A primary consideration is how each file is identified. On z/OS, Net default filespecs differ depending on the POSIX indicator. Remember that with Oracle tools and utilities, POSIX is determined by whether the program is initiated from a z/OS UNIX shell or not. (POSIX is ON in a z/OS UNIX shell, OFF otherwise.) In your own 3GL applications, the POSIX setting can be built into the application module, specified at runtime, or set automatically based on where the application runs (as with Oracle tools and utilities).
Oracle Net input (parameter) files are accessed when an application makes its first attempt to connect to an Oracle server. In some cases, they may be opened and read multiple times in the life of an application. The following sections correspond to similarly-titled sections in the Oracle Net Services Reference Guide.
This is the primary parameter file of Oracle Net, usually referred to by its UNIX HFS file name sqlnet.ora
. It is optional, but must be supplied if anything other than default parameters is desired. Activating Oracle Net logging or tracing, using a remote name resolution service, and other Oracle Net features require this file to be supplied.
In a POSIX ON environment, this file has the name sqlnet.ora
and is located in one of several HFS directories as described for UNIX systems in the Oracle Net Services Reference Guide.
When POSIX is OFF, this file is opened as //DD:SQLNET which means it is specified by the SQLNET DD statement. In this case the file can be a sequential data set, a PDS member, an instream (DD *) data set, or an HFS file (DD PATH=). A sequential or partitioned data set can have record format F, FB, V, or VB. Data that is not a part of the Oracle Net input, such as sequence numbers, is not permitted.
Some of the parameters you might specify in sqlnet.ora
have z/OS-specific considerations, as follows:
This parameter indicates the "directory" in which Oracle Net log files are written when client logging is activated. On z/OS, it can supply an HFS directory path or the left-hand portion of a data set name, as discussed in Chapter 2, "Oracle Software Interaction with z/OS and Language Environment." If this parameter is specified, it must be syntactically compatible with whatever is specified or defaulted for LOG_FILE_CLIENT
. If LOG_FILE_CLIENT
is a filespec to which no directory can be added, this parameter is ignored.
The default for this parameter is an empty string, signifying the current working directory (POSIX ON) or default LE data set name prefixing (POSIX OFF). Note that when OraRTL compatibility is enabled, no data set name prefix is added in non-TSO environments.
This parameter specifies either a complete or trailing (right hand) portion of the filespec for Oracle Net logging. This may be combined with the LOG_DIRECTORY_CLIENT value to produce the complete filespec. If this parameter specifies an absolute HFS path or a non-HFS filespec that cannot be modified (such as a //DD: or //SYSOUT: filespec), the LOG_DIRECTORY_CLIENT part is not incorporated. If the two strings are combined, the result must be a valid, usable LE filespec.
The default for this parameter is sqlnet.log
when POSIX is ON and //DD:SQLNETLG when POSIX is OFF. Note that the POSIX OFF default means any setting of LOG_DIRECTORY_CLIENT is ignored.
This parameter indicates the "directory" in which Oracle Net trace files are written when client tracing is activated. On z/OS, it can supply an HFS directory path or the left-hand portion of a data set name as discussed in Chapter 2. If this parameter is specified, it must be unambiguous and compatible with whatever is specified or defaulted for TRACE_FILE_CLIENT. If TRACE_FILE_CLIENT is a filespec to which no directory can be added, this parameter is ignored.
The default for this parameter is an empty string, signifying the current working directory (POSIX ON) or default LE data set name prefixing (POSIX OFF). Note that when OraRTL compatibility is enabled, no data set name prefix is added in non-TSO environments.
This parameter specifies either a complete or trailing (right hand) portion of the filespec for Oracle Net tracing. This may be combined with the TRACE_DIRECTORY_CLIENT value to produce the complete filespec. If this parameter specifies an absolute HFS path or a non-HFS filespec that cannot be modified (such as a //DD:
or //SYSOUT: filespec
), the TRACE_DIRECTORY_CLIENT part is not incorporated. If the two strings are combined, the result must be a valid, usable LE filespec.
The default for this parameter is sqlnet.trc
when POSIX is ON and //DD:CLITC
when POSIX is OFF. Note that the POSIX OFF default means any setting of TRACE_DIRECTORY_CLIENT is ignored.
This parameter specifies the directory in which the TNSPING trace file is written. The default is $
ORACLE_HOME
/network/trace
when POSIX is ON. This parameter is ignored when POSIX is OFF.
Trace information is written to the tnsping.trc
file when POSIX is ON and to //DD:TNSPING
when POSIX is OFF.
This file supplies Oracle server name resolution mappings. When the server data supplied on a connection attempt is a name (rather than an explicit Oracle Net address string) and you have specified (or defaulted) "local naming" in sqlnet.ora
, this file is searched for an entry matching the supplied name. The file is optional, but if you omit it and attempt to connect to a server identified by name, and have not enabled an alternative name resolution method (such as a remote resolution service), the connection attempt will fail.
In a POSIX ON environment, this file has the name tnsnames.ora
and is located in one of several HFS directories as described for UNIX systems in the Oracle Net Services Reference Guide.
When POSIX is OFF, this file is opened as //DD:TNSNAMES which means it is specified by the TNSNAMES DD statement. In this case the file can be a sequential data set, a PDS member, an instream (DD *) data set, or an HFS file (DD PATH=). A sequential or partitioned data set can have record format F, FB, V, or VB. Data that is not a part of the Oracle Net input, such as sequence numbers, is not permitted
Except for the XM protocol address particulars, already discussed, there are no z/OS-specific considerations for the data in this file.
This file supplies configuration parameters related to using directory-based server name resolution. You may need to supply it when your sqlnet.ora
NAMES.DIRECTORY_PATH parameter includes LDAP as a naming method.
In a POSIX ON environment, this file has the name ldap.ora
and is located in one of several HFS directories as described for UNIX systems in the Oracle Net Services Reference Guide.
When POSIX is OFF, this file is opened as //DD:LDAP which means it is specified by the LDAP DD statement. In this case the file can be a sequential data set, a PDS member, an instream (DD *) data set, or an HFS file (DD PATH=). A sequential or partitioned data set can have record format F, FB, V, or VB. Data that is not a part of the Oracle Net input, such as sequence numbers, is not permitted
There are no z/OS-specific considerations for the data in this file.
When requested through sqlnet.ora
parameters, Oracle Net writes log and trace files. Usually this occurs during problem diagnosis, at the request of Oracle Support Services. The type and name (filespec) for these files is controlled by the four parameters discussed under "Profile Parameters (sqlnet.ora)". If these parameters are not specified, the log and trace files default to //DD:SQLNETLG and //DD:CLITC in POSIX OFF applications and to sqlnet.log
and sqlnet.trc
(in the current working directory) in POSIX ON applications.