Release 1 (9.0.1) for OS/390
Part Number A90087-01
 Library |
 Product |
 Contents |
 Index |
| Oracle9i Enterprise Edition User's Guide Release 1 (9.0.1) for OS/390 Part Number A90087-01 |
|
The primary interface to the Oracle9i database server, SQL*Plus, provides a powerful environment for querying, defining, and controlling data.
This chapter describes accessing and running SQL*Plus commands and procedures in an OS/390 environment. The following topics are discussed:
The information in this chapter supplements the primary documentation for SQL*Plus found in the SQL*Plus User's Guide and Reference.
Oracle9i provides a native OS/390 UNIX System Services version of SQL*Plus. To run it, first ensure the environment is established as described in Chapter 4, Running an Oracle Utility Under USS, then enter the following at the command prompt:
sqlplus
For additional information, refer to Chapter 4, "Accessing Oracle9i Under USS" and the SQL*Plus User's Guide and Reference.
To access SQL*Plus as a command processor, use the following syntax:
SQLPLUS [-S] [userid[/password[@connect-string]]] [@start_file]
where:
|
-S |
calls SQL*Plus silently. The initial banner and the command prompts are not displayed. If used, then -S must be specified immediately following the SQLPLUS command. |
|
|
is the Oracle user id. |
|
|
is the Oracle password associated with |
|
|
is a tnsnames alias name or a complete Oracle Net address string. This parameter is optional if the Oracle database server is specified using methods described in Chapter 2. Otherwise, refer to Chapter 10 for further details. |
|
|
specifies a SQL command file to start after SQL*Plus initialization. This string must be separated from |
All parameters are optional to SQL*Plus.
According to the following syntax, the command connects Oracle user SCOTT to the Oracle instance specified in the ORA1 tnsnames alias. This automatically runs the SQL statements contained in member INITIAL in data set TEST.ORACLE.SQL:
SQLPLUS SCOTT/TIGER@ORA1 @/DSN/TEST.ORACLE.SQL(INITIAL)
Use of the [PA1] or [Attn] key interrupts the operation of SQL*Plus. When you enter a command producing results you did not expect or are not interested in viewing, using the attention interrupt is useful.
SQL*Plus does not support the NOSTAX attribute in the attention interrupt process.
Because the computer operator can issue an attention interrupt at any time, the attention exit responds differently depending upon the processing state of SQL*Plus. Regardless of the processing state, the following message is displayed:
! Oracle ATTN.
If the [PA1] key is pressed during a running query or update, then the operation is canceled. The Oracle9i database server returns the following error message, acknowledging the cancel:
Error: ORA-1013: user requested cancel of current operation
If the [Attn] key is pressed during a running query, then the query might continue to retrieve and display a number of rows before the cancel is acknowledged. The query sometimes continues because the [Attn] key operates asynchronously. Any messages queued to the user before the attention interrupt are displayed.
If there is no update or query running, then SQL*Plus waits for other user input.
Because attention processing cannot occur in the OS/390 batch environment, attention processing considerations do not apply to SQL*Plus operations in batch.
You can call SQL*Plus in the batch environment by running the ORASQL JCL procedure. This procedure is created during the Oracle9i database server installation process. Consult with your DBA or systems programming staff for the location of this procedure. A copy of the procedure is reproduced in the following example:
//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=*
When running this JCL procedure, include additional DD statements referring to other data sets required for proper operation of the utility.
The following JCL illustrates the use of the procedure described in the previous example:
//USER1JOB JOB ........ //STEP1 EXEC ORASQL,PARM='SCOTT/TIGER' //ORA@sid DD DUMMY //SYSIN DD DSN=SCOTT.ORACLE.SQL(SQLSTUFF),DISP=SHR
Note the additional DD statements:
ORA@sid, where sid is the SID associated with the Oracle9i database server to which you want to connect.
Use this procedure as a model for creating your own JCL procedures conforming to your installation's policies.
To pass parameters to SQL*Plus in the batch environment, use the JCL PARM field.
You can also access the Oracle9i database server through the terminal monitor program (TMP) in batch mode. Refer to "Running Under TSO" in this case. File allocations required in TSO access are also required in batch mode.
During initialization, SQL*Plus searches for an allocation to the SQLLOGIN DD statement. If a file is allocated to this DD statement, then it is opened for input and the contents are run automatically, one command at a time, until an EOF is encountered. This DD statement points to a file that can contain any valid SQL or SQL*Plus statements you want run with each logon to SQL*Plus.
If the file allocated by the SQLLOGIN DD statement is not found during initialization, then the following messages are displayed:
ERROR OPENING /DD/SQLLOGIN: DDNAME STATEMENT MISSING OR MISSPELLED UNABLE TO OPEN FILE: "/DD/SQLLOGIN"
These messages signal an unsuccessful SQLLOGIN allocation and you are provided with the normal SQL prompt. This message display is not an error condition. It indicates automatic running of SQL or SQL*Plus statements at startup did not occur.
If you are not using automatic running of SQL or SQL*Plus statements at startup and do not want to receive these messages, then use the DUMMY parameter in the data set allocation.
A short SQLLOGIN profile example is shown in the following example:
_________________________________________________ Set echo off set feedback 4 set message on set echo on select * from daily_reminder; __________________________________________________
The SQL*Plus HOST command allows you to use a variety of commands and utilities from within SQL*Plus.
The SQL*Plus HOST command lets you run TSO/E commands from within SQL*Plus:
SQL> HOST SEND 'GOOD MORNING' USER(SMITH)
The HOST command is only valid in native TSO/E and cannot be used in batch mode.
The SQL*Plus HOST command lets you use CLISTs from within SQL*Plus. To run a CLIST use the following, where clist is the name of the CLIST to run:
SQL> HOST clist
Call an OS/390 editor using one of the following SQL*Plus HOST commands:
SQL> HOST EDIT filename OLD DATA SQL> HOST ISPF 2
Use the HOST command from within SQL*Plus with a different Oracle user id to start a new, separate SQL*Plus process. Other Oracle utilities can also be called, subject to available memory.
The HOST command under SQL*Plus returns codes as shown in the following table. (IKJEFTSR is an IBM TSO command processor.)
SQL*Plus provides two means of gathering time usage information: the TIMING and SET TIMING commands. Refer to the SQL*Plus User's Guide and Reference for more information about TIMING and SET TIMING.
The TIMING command is used to record and display time usage statistics. The timing data returned has the following format:
Elapsed x:xx:xx.xx, user-CPU y.yy sec
where:
|
|
is the amount of time since the current timing area started. |
|
|
is the amount of CPU-time used since the current timing area started. |
The SET TIMING command is used to display time usage information. The information returned by the SET TIMING command is displayed after each SQL*Plus command is run. The timing data returned has the following format:
Elapsed x:xx:xx.xx, user-CPU y.yy sec
where:
|
|
is the amount of time since the SQL*Plus command was issued. |
|
|
is the amount of CPU-time used to run the SQL*Plus command. |
You can use the following OS/390 editors with SQL*Plus:
SQL*Plus lets you edit external files or the current SQL buffer. Edit files by using the editing commands (such as CH/old/new/) or by calling an external editor of your choice.
The EDIT command, without a parameter, copies the current SQL buffer to an OS/390 data set. The name of this data set is determined by the EDITFILE variable of the SET command. Default values for EDITFILE and SUFFIX are AFIEDT and SQL, respectively. The following is the default data set name:
tso_prefix.AFIEDT.SQL
The call fails if there is nothing in the SQL buffer. EDIT calls the specified editor, passing the file name as the first parameter. If the editor can take the file name as an input argument, then the invocation of EDIT places you in an editor session with the data set already loaded.
Exiting the editor reloads the updated file into the SQL buffer and returns control to SQL*Plus.
To support the ISPF editor, the editor must be defined as ISPF using a DEFINE_EDITOR command from the command line or from a profile. Once the editor is defined as ISPF, when a SQL*Plus EDIT command is issued, the ISPLINK interface is dynamically loaded.
You cannot use /DD/ with the EDIT command to access a DD concatenation. Instead, you must use /DSN/, as in the following example:
EDIT /DSN/GJONES.ORACLE.SQL(SQLSTUFF)
When you begin editing a member with ISPF, ISPF obtains an exclusive ENQ for QNAME SPFEDIT and a 52-byte RNAME consisting of the data set name and member. ENQ is held during the entire edit session. When you save the member, ISPF obtains a second exclusive ENQ for QNAME SPFEDIT and a 44-byte RNAME containing only the data set name. ISPF opens the PDS for output, writes the edited member, and closes the PDS. The two enqueues are released in the order they were obtained.
When you open an output PDS with the SQL*Plus SPOOL command, the Oracle runtime library obtains the same two enqueues, but must obtain them both immediately when the data set is opened for output. Both enqueues are held until the data set is closed by the SPOOL OFF command or by exiting SQL*Plus.
Refer to Chapter 2, "Using the OS/390 Database Instance" for more information about the enqueue mechanism used by Oracle tools.
The DBA can use the PRODUCT_USER_PROFILE table, located in the SYSTEM account, to disable certain SQL*Plus commands for a user. You might be able to use the PRODUCT_USER_PROFILE table with your applications. For more information about the PRODUCT_USER_PROFILE table, consult with the DBA for your system and refer to the SQL*Plus User's Guide and Reference.
To exit SQL*Plus, you can enter one of the following:
SQL>EXIT [return code keyword | variable] SQL>QUIT [return code keyword | variable]
QUIT is a synonym for the EXIT command.
The EXIT command lets you specify an operating system return code. This enables you to run SQL*Plus command files in batch mode and detect any unexpected events. Under OS/390, the return code values are:
| Command | Condition | Return Code |
|---|---|---|
|
SQL>EXIT |
SUCCESS |
0 |
|
|
FAILURE |
1 |
|
WARNING |
1 |
|
|
|
Value of |
|
|
SQL.SQLCODE |
Return code from the last SQL operation to the database |
The syntax for the EXIT command is described in the SQL*Plus User's Guide and Reference.
When using the SQL*Plus SPOOL command, SQL*Plus uses a default output file suffix of LST.
There are special considerations for the following:
The SQL symbol for negation is the exclamation point. For not equal, <> is recommended.
The SQL symbol for concatenation under OS/390 is the solid vertical bar.
A string concatenation function, CONCAT, provides an alternative to using the vertical bar character for concatenation. Installations that do not have computer keyboards with the vertical bar can use this function. For installations where the vertical bar is available on the computer keyboards and where portability to environments with other character sets is not important, the vertical bar character can be used.
The CONCAT function has the following syntax, where x and y are the strings to concatenate:
CONCAT('x','y')
The quotes in the syntax are required.
For example, the following statement selects rows where the employee name is CLARK:
SELECT * FROM EMP WHERE ENAME = CONCAT('CL','ARK');
SQL*Plus currently truncates, without warning, input lines exceeding 255 characters.
Under OS/390, the SQL ASCII function can cause confusion. Contrary to what the name implies, the ASCII function does not convert an EBCDIC value to its ASCII equivalent.
The ASCII function takes a character, in this case EBCDIC, and returns the number representation for that character in the given character set. For example, the ASCII value of A is 193 (ASCII('A')=193). The inverse function is CHR (meaning, CHR(193)='A').
The following functions are not supported:
The SPOOL OUT function documented in the SQL*Plus User's Guide and Reference is not supported in OS/390 in the TSO or batch environments. To spool to a data set that is automatically printed, you can use the following steps:
//SPOOLED DD SYSOUT=*
SQL> SPOOL /DD/SPOOLED
Using SET NEWPAGE 0 in SQL*Plus in OS/390 does not clear the screen between logical pages on a 3270 computer. NEWPAGE 0 only applies to printed output.
The RUNFORM command is unavailable. When you enter the RUNFORM command, you receive an error message.
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|