2
Using the OS/390 Database Instance

Enabling OS/390 Local Client Access

Oracle database server clients use a cross-memory protocol for connecting to database instances.  The protocol is based on Oracle Net architecture.

Since Oracle's cross-memory protocol is based on Oracle Net, the formal mechanism for specifying a local target Oracle instance is to supply a name that is looked up in the client's tnsnames file (which is identified by the TNSNAMES DD statement).  Applications supply the name by appending an "@" character followed by the name to the userid and password that are passed during an Oracle connect request.  The entry in the tnsnames file contains an Oracle Net address string with PROTOCOL=XM and additional parameters identifying the target database service.  The complete format of the PROTOCOL=XM Oracle Net address is described under "Cross-Memory Protocol Address".  Usually the target service is identified by its SID.  Every service must have a SID that is unique throughout the OS/390 image.  Even services that are defined in different Oracle subsystems cannot have the same SID if they are on the same OS/390 image.

It also is possible to append the complete Oracle Net address string directly to the userid and password to avoid using the tnsnames file.  Oracle does not recommend this technique, however.

Cross-Memory Protocol Address

The formal Oracle Net address string for Oracle's cross-memory protocol can be specified using either of two methods:

• One method identifies the database instance by its SID:

  (ADDRESS=(PROTOCOL=XM)(SID=sid))
  

  where sid is the SID associated with the database instance.

• The other method uses the Oracle subystem and service names:

  (ADDRESS=(PROTOCOL=XM)(SUBSYS=ssn)(SERVICE=srvn))
  

  where ssn is the Oracle subsystem name, and srvn is the database service name.

  Oracle recommends using the SID form of address because it is simpler and because it avoids application dependence on the subsystem name.

Specifying Connections

There are four ways, including hardcoding an Oracle Net address on the connect string as described in the previous section, to specify a target instance.  In descending order of precedence, the four ways are:

1. Specifying a tnsname entry which refers to the Oracle Net cross-memory protocol on the connect string.

2. Providing a DD statement (or equivalent TSO allocation) of the form:

   //ORA@sid  DD  DUMMY

   where sid matches the SID of the target service.  Normally this is used only in batch or TSO applications.

3. A TWO_TASK environment variable that is set to an Oracle Net tnsnames-style name or to an explicit Oracle Net address string.  If a name is specified, then the TNSNAMES DD in the client address space is opened and read to resolve the name to an Oracle Net address.  If the Oracle Net address specifies PROTOCOL=XM, then the client is connected to the indicated OSDI service.  (The Oracle Net address could also specify PROTOCOL=TCP, in which case the client would connect to a remote Oracle instance via Oracle Net as discussed in "Oracle Net Connect Descriptors for OS/390" in Chapter 10.)  In terms of precedence, clients look for TWO_TASK after determining that no ORA@sid DD is allocated in the address space.

4. An ORACLE_SID environment variable is set to the SID of a database instance; the client is connected to that instance.  Clients look for ORACLE_SID last, after determining that no TWO_TASK environment variable is set.

Oracle9i Server and Library Names

The installation process creates several load and source module libraries you might need to access.  For example, you might need information about the following:

• CMDLOAD library name

  All utilities, programs, and dynamically loaded modules for Oracle utilities are located in the CMDLOAD library.  The person who installs the Oracle9i database server at your site determines this data set name.

  The examples presented in this guide assume the CMDLOAD library has the data set name oran.orav.CMDLOAD.

• MESG library name

  This library contains NLS data objects and all Oracle message modules.

• SQLLIB library name

  The SQLLIB library contains modules used in linking programs developed in high-level languages and IBM Assembler.  It contains modules used by both Oracle Precompiler applications and OCI applications.

• SRCLIB library name 

  The SRCLIB library contains sample JCL, program code, and scripts for Oracle tools, Programmatic Interfaces and Access Manager.

• OBJLIB library name

  The OBJLIB library contains the object module ()RASTBS) for liking Oracle call Interface programs that do not use the LONGNAME compiler option.

Refer to the Oracle9i Enterprise Edition Installation Guide for OS/390 for more information on load and source module libraries.

Tool Syntax and Batch Procedures

Contact your Oracle DBA for the following information you need to use the Oracle utilities:

• Name and location of the Oracle cataloged procedures

The following factors influence the syntax used to run the Oracle utilities:

• Allocation of the Oracle CMDLOAD library to the STEPLIB DD statement in the TSO LOGON procedure

• Installation of TSO/E

• Allocation of the ORA$ENV DD statement

• Allocation of the ORA$LIB DD statement

• Allocation of the TNSNAMES DD statement

• Inclusion of Oracle CMDLOAD library in the system LINKLIST

• Installation of reentrant Oracle modules in the system link pack area (EPLPA and PLPA)

OS/390 Features

Use of Oracle utilities is subject to the normal conventions and features of the OS/390 operating system.  The syntax of TSO commands, CLISTs, and batch JCL must be followed.

Invoking Oracle Utilities from TSO

How you invoke Oracle utilities from TSO at your site might differ slightly from what is described in this guide because access standards and requirements differ among installations.  Your DBA can provide you with the command syntax standards specific to your installation.

Call the Oracle utilities from TSO using one of the following:

• Running as a command processor

• Using the TSO CALL command

You control tool operation with CLISTs and the Interactive System Productivity Facility (ISPF) regardless of which technique you use.  The technique used is largely determined by decisions made at your site during installation.  The technique can also be affected by the number of active Oracle instances and the release levels of the instances.  Contact your DBA if you have any questions about which technique to use.

command [parm1][parm2]...[parmn][userid[/password[@tnsname]]] [options]

CALL 'oran.orav.CMDLOAD(command)' '[parm1] [parm2]...[parmn] —

   [userid[/password[@tnsname]]] [options]' 

Oracle Utilities Accessed Through CLISTs

You may also access Oracle utilities using CLISTs.  The CLIST can access the utilities as command processors or as called programs.  The developer of a CLIST can perform preliminary allocations of files, check for error conditions, and generally ensure that access to the Oracle server proceeds according to installation standards.

If you have any questions about the approved method of calling any Oracle tool at your site, then contact your DBA.

Submitting a Batch Job

The Oracle installation process optionally creates batch JCL procedures you can use to call the Oracle utilities.  The procedures at your installation might have different names than those in the following list if a procedure name suffix is selected during the installation.  Check with your system administrator before attempting to use the following procedures.

Table 2-1 Batch JCL Procedures for OS/390

//ORASQL   PROC  INDEX=oran,

//               LIBV=orav, 

//               SYSOUT='SYSOUT=*' 

//ORASQL   EXEC  PGM=SQLPLUS,REGION=4M 

//STEPLIB  DD    DSN=&INDEX..&LIBV..CMDLOAD,DISP=SHR

//ORA$LIB  DD    DSN=&INDEX..&LIBV..MESG,DISP=SHR

//SYSOUT   DD    &SYSOUT,DCB=(LRECL=132,BLKSIZE=1320,RECFM=VB) 

//SYSERR   DD    SYSOUT=*,DCB=(LRECL=132,BLKSIZE=1320,RECFM=VB) 

//ORAPRINT DD SYSOUT=* 

//* REQUIRES //SYSIN DD* STATEMENT 

//* REQUIRES //ORA@sid DD DUMMY STATEMENT (ORACLE INSTANCE NAME)

oran.orav.CMDLOAD

//ORA@ORA1 DD DUMMY

//ORA$ENV     DD *

TWO_TASK=ORANAME

//TNSNAMES  DD *

ORANAME=(DESCRIPTION=

    (ADDRESS=(PROTOCOL=XM)(SID=ORA1))

)

 //ORA$ENV     DD *

 ORACLE_SID=ORA1

// ...     JOB   ... 

//SQL EXEC ORASQL,

//    PARM='SCOTT/TIGER' 

//ORA@ORA1 DD DUMMY  

//SYSIN    DD DSN=SCOTT.ORACLE.SQL(SQLSTUFF),DISP=SHR 

Exiting Utilities and Utilities in TSO

In response to an input prompt, a forward slash followed by an asterisk (/*) is used to exit any line mode Oracle9i for OS/390 tool or utility.

Attention Processing

For TSO applications, the Oracle subsystem establishes an attention processing subtask through the OS/390 STAX macro instruction.  When the [Attn] or [PA1] key is pressed, the interrupted status is passed to the connected Oracle address space.  The corresponding main task then performs the interrupt processing and returns to the application address space.  The attention processing is removed when the disconnect function is called and the attention processing subtask is detached.

For both Oracle tools and customer-written applications running in TSO/E, the Oracle program interface code will attempt to establish an attention-processing subtask that uses the TSO/E STAX service.

This mechanism allows the user to interrupt an operation in the Oracle server by pressing the terminal [Attn] or [PA1] key.  For this to work, the ORASTAX module must be loadable from JOBLIB/STEPLIB, a linklist library, or LPA.  If ORASTAX cannot be loaded, attention processing is suppressed; no messages are issued to indicate this condition.

In addition to the above, certain Oracle tools or utilities recognize and process TSO/E attention if it occurs while the utility is active (that is; not during a server request).  These are discussed in the relevant tool or utility sections.

Attention interrupt processing is specific to each tool.  Chapter 9, " SQL*Plus" discusses the effect of attention processing.