4 Oracle Tools and Utilities on z/OS

4.1.1 Invocation

SQL*Plus on z/OS supports batch job, TSO, and z/OS UNIX shell execution environments. A JCL procedure for batch execution (default name ORASQL) is supplied by Oracle and may be installed on your system with the same or a different name. In TSO, both CALL and command processor (CP) invocation are supported. For the non-POSIX environments, the load module or program object name is SQLPLUS. In a z/OS UNIX shell (including OMVS under TSO), use the sqlplus (lower case) command to invoke this tool.

SQL*Plus reads C standard input (described in Chapter 2) as the primary source of input commands and SQL statements. If you do not supply an Oracle userid/password (or the special value /nolog) as an invocation parameter, SQL*Plus prompts for a userid and password and reads those from standard input before reading for normal commands. If you do this in a batch job, the prompt responses must appear in the first records or lines of standard input, ahead of any input commands or SQL statements. In interactive usage (TSO or a shell), these prompts and responses normally occur at the terminal. In this case, if the password is entered separately from the userid, it is not displayed at the terminal.

For certain database control operations (such as Oracle startup and shutdown), SQL*Plus requires a special userid/password parameter that contains spaces, such as "/ AS SYSDBA." Refer to the section "Parameters Containing Spaces" for information on how to specify such values.

SQL*Plus output (including the aforementioned prompts) is written to C standard output. There are various options to control exactly what gets written as well as a SPOOL command to write output to a separate data set or file.

4.1.2 Output Interruption

Interruption refers to using the terminal ATTN or PA1 key (in TSO) or a POSIX signal mechanism such as Ctl-C to interrupt in progress activity. Besides the normal interrupt handler that is established when connecting to Oracle from TSO or a shell, SQL*Plus establishes its own handler to allow termination of display output. If you generate an interrupt while using SQL*Plus, the behavior depends on whether SQL*Plus or the target Oracle server was in control when the interrupt was processed. If the Oracle server was in control (it was processing a SQL statement or PL/SQL procedure call from SQL*Plus) the interrupt halts the in progress statement with an ORA-01013 error, as described in Chapter 2. If the interrupt occurs while SQL*Plus is in control, driving the SQL*Plus-specific handler, it causes current fetches for any SELECT statement to stop. The typical occurrence of the latter situation is where you have issued a SQL SELECT statement that produces more output than expected. This provides an alternative to canceling or killing the entire SQL*Plus session to halt unwanted output.

4.1.3 Profile Files

SQL*Plus reads optional profile files during initialization (after logging on to Oracle). The files can contain SQL*Plus commands (such as SET commands) as well as SQL statements and PL/SQL blocks.

When SQL*Plus runs in a shell on z/OS, there are two profile files with the same names and locations as on other UNIX platforms. The site profile applies to all users and is read from $ORACLE_HOME/sqlplus/admin/glogin.sql. The user profile is read from login.sql in the first HFS directory (of multiple possible directories) in which a file of that name is found. The directories that are searched for the user profile are the current working directory followed by any directories listed in the SQLPATH environment variable. (Refer to the SQL*Plus User's Guide and Reference for details on using the SQLPATH environment variable.)

In z/OS batch and TSO (POSIX OFF), there is no site profile file. The user profile filespec is //DD:SQLLOGIN which means the SQLLOGIN DD is read for profile statements. The DD can specify any of the z/OS data set or file types permitted for SQL files, discussed in the section "SQL Files".

Profile files are optional. If a file can't be opened, profile processing is skipped and no error message is issued.

4.1.4 SQL Files

Various SQL*Plus commands operate on files containing SQL, PL/SQL, and/or SQL*Plus commands. On z/OS, these files can be a sequential data set, a PDS member, an instream (DD *) data set, or an HFS file. A sequential or partitioned data set can have a record format of F, FB, V, or VB. Records in a data set (including instream) must not contain sequence numbers or other data not intended for SQL*Plus to read.

If the file you want to use is counter to the POSIX expectation (for example, an HFS file when POSIX is OFF), you must use an unambiguous filespec when specifying the file. When a file is specified as a data set name or HFS file name, the name is subject to extension processing (as discussed in Chapter 2) with the type suffix sql. The SQL*Plus commands @, @@, EDIT, GET, SAVE, and START all apply this extension to supplied filespecs.

In POSIX OFF environments, you can use FNA to manipulate data set filespecs produced by name extension or to associate disk space and DCB attributes with files that are written. Refer to Chapter 2 for a complete description of FNA. Use of FNA is required when you run Oracle catalog creation or upgrade scripts using SQL scripts that are members of a partitioned data set. In this case, FNA is used to transform simple 1-segment script names into PDS member names inside parentheses following a DD or data set name.

The SQL*Plus @@ operator requires additional explanation on z/OS. This operator is shorthand for the START command, similar to @ but with a slight difference: when an @@ command is coded in a file and the filespec supplied on the command is not absolute; the file is read from the same directory that contains the file in which the @@ command was coded. With data sets, "directory" refers to the high-level (leftmost) data set name qualifier, so the behavior of @@ with data sets might not be what you expected. In particular, you cannot code @@foo within a PDS member and assume that foo will be treated as another member name within that PDS. Without FNA, @@foo will end up trying to access a data set named hlq.FOO.SQL where hlq is the high-level dsname qualifier of the data set in which the @@foo command is supplied. To get different behavior, such as treating foo as a member name, you must use FNA.

4.1.5 EDIT Command Processing

The SQL*Plus EDIT command is designed to invoke an external editor of your choosing. In z/OS TSO (POSIX OFF), the only external editor supported is IBM's ISPF editor. In this environment, the SQL*Plus variable _EDITOR is automatically set to ISPF.

To be able to invoke the ISPF editor in TSO, SQL*Plus requires that the ISPF environment already be established. To accomplish this, invoke SQL*Plus from within an ISPF session (from the ISPF "Command Shell" panel). Keep in mind that a data set name filespec supplied on an EDIT command is subject to extension (and FNA) processing. HFS files cannot be edited with the ISPF editor using SQL*Plus EDIT.

In a z/OS UNIX shell, _EDITOR defaults to vi. To use a different editor, define the _EDITOR variable to the exact name of the editor executable or shell script. Whether the editor you use in a shell is capable of processing data sets (in addition to HFS files) depends on the editor. Editing data sets with the default z/OS UNIX editor (vi) is not supported.

Neither vi nor ISPF work in the TSO OMVS shell. To use EDIT when running SQL*Plus in OMVS, you must define _EDITOR to oedit before issuing the EDIT command, as follows:

SQL> def _editor=oedit
SQL>
 ===> edit /tmp/load_it.sql

The editor, oedit, is a form of the ISPF editor that works in POSIX environments. It is the only editor supported when SQL*Plus runs in the OMVS shell. Only HFS files can be accessed with oedit.

The EDIT command is not available when SQL*Plus runs as a z/OS batch job.

4.1.6 SPOOL Command Processing

The SPOOL command causes SQL*Plus to begin writing output (including command responses as well as SQL statement results) to a specified file. On z/OS, the filespec you supply on the spool command can specify a sequential data set, PDS member, SYSOUT, or an HFS file. If the file you want to use is counter to the POSIX expectation, you must use an unambiguous filespec. If the filespec you supply is a data set or HFS file name, it is subject to extension with the suffix first. When running SQL*Plus in batch or TSO, you can use FNA to manipulate the resulting filespec or to associate space or DCB attributes with the data set.

If you spool to a SYSOUT filespec, as in the following:

SQL>
spool //s:*,,MCMDEV3
 SQL>
…

the output is freed (becomes eligible for routing or printing) as soon as the file is closed. The spool file is closed when you issue another SPOOL command (including the special command SPOOL OFF) or when you exit SQL*Plus.

The special command SPOOL OUT is not supported on z/OS. Attempting to use it will produce an error message.

4.1.7 HOST Command Processing

The HOST command allows you to invoke an arbitrary external program or script from within a SQL*Plus session. This feature is implemented on z/OS using the C function system, described in the IBM manual C/C++ Run-Time Library Reference. Refer to this manual for details on the capabilities and limitations of the system function.

The behavior of system() (and thus of the HOST command) depends on the POSIX indicator and, in the POSIX OFF case, on command syntax and whether the environment is batch or TSO. In TSO, HOST can be used to invoke TSO commands, CLISTs, and REXX EXECs. To do this, code the command text after HOST exactly as it would be entered at the TSO READY prompt, as in the following example:

SQL>
host listd misc.sql
 JSMITH.MISC.SQL
 --RECFM-LRECL-BLKSIZE-DSORG
   VB    255   4096    PO
 --VOLUMES--
   PNW042
 SQL>

In addition, in both TSO and batch, HOST can be used to invoke jobstep z/OS programs such as IBM utilities. To do this, special syntax (similar to the JCL EXEC statement) is required to indicate the program name and, optionally, PARM data in the command text as illustrated here:

SQL>
host pgm=iebcopy,parm=COPY
 IEBCOPY MESSAGES AND CONTROL STATEMENTS                     PAGE   1
 IEB1035I JSMITH  $TS1  $TS1 13:59:38 THU 02 OCT 2003 PARM='COPY'
 IEB1099I *** IEBCOPY IS NOT APF AUTHORIZED ***
 $TS1  COPY      INDD=SYSUT1,OUTDD=SYSUT2         GENERATED STATEMENT
 …

With this technique, letter case is preserved in any PARM data that you supply, which is why COPY is in upper case in the example. Refer to the IBM manual C/C++ Run-Time Library Reference for additional details on this command syntax.

In non-POSIX TSO and batch environments, you cannot use SQL*Plus HOST to invoke a an HFS executable or shell script. Also, you cannot issue HOST alone (with no command text) in order to be placed at a general command prompt.

The system() function and HOST behave differently when you run SQL*Plus in a z/OS UNIX shell. The command text that you supply with HOST must resolve to a valid executable or shell script file in the HFS, or be a built-in shell command. Commands are processed with the shell indicated by the SHELL environment variable; if SHELL is not set, the default shell /bin/sh is used. Be aware that commands issued through HOST are executing in a subshell relative to the shell in which SQL*Plus was invoked, so issuing commands that change the state of the current shell has no effect on the SQL*Plus session. For example, using HOST to issue a cd (change directory) command or to set an environment variable does not change the current working directory or the value of that environment variable as seen by SQL*Plus.

When SQL*Plus runs in a z/OS UNIX shell, HOST can be issued alone, without command text, causing the user to be placed at a general shell prompt. In this situation multiple commands can be issued in succession. Control returns to SQL*Plus when you end execution of the nested shell. For example:

SQL> host
$ cd /oradev/js1/src
$ ls -l *.c
-rw-rw-r--   1 JSMITH   PDRP88     7438 Nov  5 15:55 ocidemo3.c
$ rm ocidemo3.c
$ exit
SQL>

In this case, the cd command is effective in terms of setting the directory used by the subsequent commands within the subshell.

There is no direct way to invoke non-HFS z/OS programs or TSO commands using SQL*Plus HOST in a POSIX ON environment.

4.1.8 TIMING Processing

The SQL*Plus timing feature, activated with the command SET TIMING ON, reports elapsed time for server SQL and PL/SQL operations. On z/OS, the timing feature also reports the processor (CPU) time consumed by SQL*Plus during these operations. As described in Chapter 3, when you use XM protocol to connect to a local Oracle server, this time figure includes the Oracle server processing, which is executed by the SQL*Plus task in cross-memory mode. This allows the timing feature to be used to collect approximate server processing times for SQL and PL/SQL operations.

Be aware that server caching of data, SQL, and PL/SQL, and other workload-related factors, mean that processor time for a particular operation can vary significantly over multiple trials. Also, the processor time reported by the timing feature on z/OS does not include time consumed by server "slave" tasks used in parallel query and other parallel operations. Finally, when using TCP protocol rather than XM, the processor time reported by the timing feature does not include any Oracle server processor time.

4.1.9 Return Codes

Except for initialization failures, SQL*Plus normally sets a SUCCESS (zero) return code regardless of the success or failure of any SQL, PL/SQL, or SQL*Plus commands processed. To change this behavior, for example to set a nonzero return code if any SQL operation fails, you must use the EXIT command, possibly in conjunction with the WHENEVER command. EXIT allows you to set any valid numerical return code you want using either a specific number or the current value of a SQL*Plus variable. Remember, however, that in non-POSIX environments return codes are limited to the range 0-4095. If you issue EXIT with a number higher than 4095, the resulting return code is unpredictable.

You can also issue EXIT with one of three keywords whose numerical value depends on the platform. On z/OS, the values associated with these keywords are shown in the following table:

On an initialization failure, SQL*Plus ends as though EXIT FAILURE occurred, producing a return code 8 on z/OS.

Refer to the Oracle Database SQL*Plus User's Guide and Reference for complete details on the EXIT and WHENEVER commands.

4.1.10 Unsupported Features

The following SQL*Plus features or commands are not available on z/OS:

• Site profile file, when running in POSIX OFF environments.

• SPOOL OUT command. This command results in an error message on z/OS.

• SET NEWPAGE 0 command. This command is accepted but does not clear the terminal screen (as it does on some ports) when issued in TSO or a shell.

• RUNFORM command. This command results in an error message on z/OS.

4.1.11 SQL*Plus Examples

The following example is SQL*Plus executed as a batch job step with both the user profile file and standard input provided as instream data sets. An Oracle database instance with SID ORA1 is accessed using cross-memory (XM) protocol, with the connection specified using an ORA@ DD statement.

//PLUS     EXEC PGM=SQLPLUS,PARM='/nolog',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORACLE.V10G.MESG
//ORA@ORA1 DD DUMMY
//SQLLOGIN DD *
whenever oserror exit 16
whenever sqlerror exit 12
set pagesize 0
/*
//SYSIN    DD  *
connect conklin/grommet6
create table big_acct (
  acct number,
  balance number,
  desc varchar2(200))
 as
  select account_primary, balance_fwd, account_pdesc
   from pmds011.master
   where (balance_fwd > 100000.00) and
         (account_pdesc not null);
commit;
select * from sub_acct1 order by balance descending;
exit
/*

The following example shows SQL*Plus used in line-mode TSO and assumes the following:

• The CMDLOAD data set is provided as STEPLIB, TSOLIB, or a linklist library.

• The MESG data set is already allocated to ORA$LIB.

• A TNSNAMES DD is allocated and contains a valid entry with the identifier ORALNX1. This is a TCP/IP connection to an Oracle 10g instance running on a z/Linux system.

READY
sqlplus pdbr/asdfghj@oralnx1

 SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 16 12:48:12 2004
 Copyright (c) 1982, 2004, Oracle.  All rights reserved.

 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.1.0 - Production
 With the Partitioning, Oracle Label Security and Data Mining options

 SQL>
host listd test.sql mem   [Text entered by user]
 host listd test.sql mem
 JCONKLI.TEST.SQL
 --RECFM-LRECL-BLKSIZE-DSORG
   VB    255   27998   PO
 --VOLUMES--
   DSM119
 --MEMBERS--
   RVNT030
   RVNT040
 SQL>
get test(rvnt030)         [Text entered by user]
 get test(rvnt030)
   1  SELECT DNAME, DACCT, DDESC FROM ANWR.MASTER WHERE
   2   (DLCODE IN (12, 43, 663, 900)) AND
   3*  (DDESC NOT NULL);
 SQL>
l 1                       [Text entered by user]
 l 1
   1* SELECT DNAME, DACCT, DDESC FROM ANWR.MASTER WHERE
 SQL>
c /ANWR./TEMP./        [Text entered by user]
 c /ANWR./TEMP./
   1* SELECT DNAME, DACCT, DDESC FROM TEMP.MASTER WHERE
 SQL>
/                      [Text entered by user]
 /

                        [SELECT output omitted]

 SQL>
exit                   [Text entered by user]
 exit
 Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0 -
  Production
 With the Partitioning, Oracle Label Security and Data Mining options
 READY

4.2.1 Invocation

SQL*Loader on z/OS supports batch job, TSO, and shell execution environments. A JCL procedure for batch execution (default name ORALDR) is supplied by Oracle and may be installed on your system with the same or a different name. In TSO, both CALL and command processor (CP) invocation are supported. For the non-POSIX environments, the load module or program object name is SQLLDR. In a shell (including OMVS under TSO), use the sqlldr (lower case) command to invoke this utility.

Unlike most tools and utilities, SQL*Loader does not read C standard input as a primary source of input. Loader processing details are specified in a text control file whose filespec usually is supplied on the command line or PARM field. If you don't supply this parameter but you do supply a userid/password parameter, Loader prompts for the control filespec and reads it from C standard input. This is intended mainly for interactive (TSO or shell) execution; if you do this in a batch job, the prompt response must be the first record or line of standard input. If you supply neither a control file nor a userid/password as invocation parameters, SQL*Loader displays a help message and ends normally.

The parser used by SQL*Loader for command line parameters has its own syntax conventions (described in the generic documentation) and is sensitive to apostrophes (single quotes). If you use apostrophes in a command line filespec parameter, you must "escape" them with a preceding backslash in order to keep the parser from trying to interpret them as syntax elements. For example, to specify the CONTROL= parameter with the data set name filespec //'DMWONG.ORA.CTL(LOAD2)' in a batch job, code

//LOAD EXEC PGM=SQLLDR,PARM='control=//\''DMWONG.ORA.CTL(LOAD2)\'''

Notice that the filespec apostrophes in the example are doubled, which is required to get an apostrophe through the JCL PARM mechanism, as well as being escaped with a preceding backslash for Loader's parser.

Command line parameters for SQL*Loader can easily exceed the 100-character maximum of the JCL PARM field or TSO CALL command. You can use the "++" parameter file mechanism described in Chapter 2 to supply Loader parameters in a data set. SQL*Loader also has a generic parameter file feature that is similar to the "++" mechanism but is available on all platforms (and also works in a POSIX shell, which is not true of "++"). If you use the generic parameter file, the filespec supplied for the PARFILE parameter is not subject to name extension before opening. This file can be a sequential or instream (DD *) data set, a PDS member, or an HFS file. The file must not contain record sequence numbers or other data that is not a SQL*Loader command line parameter.

SQL*Loader message output (including the aforementioned prompt and help message) is written to C standard output. Detailed information about load processing is discussed in the sections that follow.

4.2.2 SQL*Loader Files and Filespecs

SQL*Loader uses several types of files. In a single execution of Loader, multiple files of some types may be used. In some cases, if you don't supply a filespec for a file that is required, Loader derives a filespec for the file based on some other filespec that you did supply. What gets derived depends on the supplied filespec and on the environment in which Loader is running with POSIX OFF or ON.

The filespec derivation schemes mean care must be taken when running SQL*Loader with filespecs that are counter to the POSIX expectation: HFS files with POSIX OFF or data sets with POSIX ON. Deriving a data set filespec from an HFS filespec, or an HFS filespec from a data set filespec, is not supported. In these situations, filespecs must be supplied explicitly rather than being derived.

4.2.3 VSAM Data File Processing Considerations

The following considerations pertain to loads where a data file is one of the supported VSAM cluster types (KSDS, ESDS or RRDS).

• Input records are read in normal forward sequence for the cluster type: key sequence for KSDS, RBA sequence for ESDS, and record number sequence for RRDS. Empty slots in an RRDS cluster are not read and do not contribute to Loader SKIP or LOAD counts.

• While you can specify an AIX PATH for a load, causing records to be read in alternate key sequence, this impacts load performance and is not recommended.

• Loading from a VSAM object that is also open for update in another z/OS job or address space (including CICS TS and IMS TM address spaces) is not supported. Record Level Sharing (RLS) is not supported.

• Loading from VSAM objects containing invalid Control Intervals (lacking valid CIDF/RDF fields) is not supported.

• SQL*Loader provides no mechanism for specifying VSAM cluster passwords.

4.2.4 Bad and Discard File Attributes

The bad and discard files, when written, contain subsets of the data from the associated data file. On z/OS, these files must be the same major type (data set or HFS file) as the associated data file. When using HFS files, the bad and discard files are written with the same line terminator and related attributes that you specified (or defaulted) in the file-processing options string for the data file.

When you load data from a data set, you can allow the DCB attributes of the bad and discard files to default or you can override them by coding them explicitly on a DD statement or by using an existing data set with established DCB attributes (which Loader will preserve). When allowed to default, the RECFM and LRECL will match those of the associated data file. If you override these attributes you must ensure that the output record length is sufficient. For example, if the input data file has fixed record format (F or FB) and LRECL=100 and you want to use RECFM=VB for the bad file, you must use an LRECL of at least 104 for the bad file (100 bytes for data and 4 bytes for the record descriptor word).

When an input data file is a VSAM data set, the bad and discard files will be nonVSAM sequential data sets. (Loader cannot create a VSAM bad or discard file.) As with nonVSAM data set input, the bad and discard file DCB attributes will default to values that are appropriate for the VSAM cluster and its maximum RECORDSIZE attribute. If you override these attributes you must ensure that the LRECL is adequate.

If you override bad or discard DCB attributes with a fixed (F or FB) format and a data file record to be written is shorter than the fixed record length, the record is padded with binary zeroes to the required length.

4.2.5 SQL*Loader Return Codes

Refer to the Oracle Database Utilities manual for information on conditions in which return codes are set. The keyword return codes listed there have the following values on z/OS:

4.2.6 SQL*Loader Examples

In the following examples, Example 1 is a z/OS UNIX shell execution of SQL*Loader, loading data from an HFS file into an Oracle instance on the same z/OS system. It assumes the environment variables ORACLE_HOME, PATH, and LIBPATH are already set appropriately. ORACLE_SID is set to enable cross-memory (XM) connection to the instance whose SID is TDB3.

Example 1

$ export ORACLE_SID=TDB3                   [Text entered by user]
$ ls -l mydata.*                           [Text entered by user]
-rw-r--r--   1 RJONES   PGACCT   181203 Nov  5 15:52 mydata.dat
$ cat myload.ctl                      [Text entered by user]
LOAD DATA  
 INFILE 'mydata'
 DISCARDMAX 10
  INTO TABLE SITE_REVIEW
  WHEN DATA3 != '500'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  (DATA1,
   DATA2 CHAR "NVL(:C,'***')",
   DATA3)
$ sqlldr control=myload userid=bsmyth/pinot    [Text entered by user]
SQL*Loader: Release 10.1.0 - Production on Wed Mar 17 10:26:06 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 6
Commit point reached - logical record count 7
$ ls -l mydata.* *.log                       [Text entered by user]
-rw-r--r--   1 RJONES   PGACCT   181203 Nov  5 15:52 mydata.dat
-rw-r--r--   1 RJONES   PGACCT      594 Mar 17 10:26 mydata.dsc
-rw-r--r--   1 RJONES   PGACCT     6288 Mar 17 10:26 myload.log
$

Example 2 is a batch jobstep execution of SQL*Loader. It is similar to the previous example except for the environment. All filespecs except the control file have been allowed to default.

Example 2

//LOAD     EXEC PGM=SQLLDR,REGION=0M,
//   PARM='userid=bsmyth/pinot control=DD:ctl'
//STEPLIB  DD DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORACLE.V10G.MESG
//ORA@TDB3 DD DUMMY
//CTL      DD *
LOAD DATA
 DISCARDMAX 10
  INTO TABLE SITE_REVIEW
  WHEN DATA3 != '500'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  (DATA1,
   DATA2 CHAR "NVL(:C,'***')",
   DATA3)
/*
//DATA     DD DISP=SHR,DSN=GKSC.MASTER.SPR
//LOG      DD SYSOUT=*
//DISCARD  DD DISP=(,CATLG),DSN=BSMYTH.LOAD.DSC,UNIT=SYSDA,
//   SPACE=(TRK,(10,10),RLSE)

4.3.1 Invocation

On z/OS, Export and Import support batch job, TSO, and shell execution environments. JCL procedures for batch execution (default names ORAEXP and ORAIMP) are supplied by Oracle and may be installed on your system with the same or different names. In TSO, both CALL and command processor (CP) invocation are supported. For the non-POSIX environments, the load module or program object names are EXP and IMP. In a z/OS UNIX shell, use the exp and imp (lower case) commands to invoke these utilities.

Parameters for Export and Import can be supplied on the command line or PARM field or in a file. If parameters are supplied in neither place, these utilities prompt for required inputs; prompts are written to C standard output and the responses are read from standard input.

The parser used by Export and Import for parameters has its own syntax conventions (described in the generic documentation) and is sensitive to apostrophes (single quotes). If you use apostrophes in a filespec parameter, you must "escape" them with a preceding backslash in order to keep the parser from trying to interpret them as syntax elements. For example, to specify the FILE= parameter with the data set name filespec //'JSMITH.ORADB1.DMP' in a batch Export job, use the following code:

//EXPORT EXEC PGM=EXP,
//   PARM='scott/tiger file=//\''JSMITH.ORADB1.DMP\'''

Notice that the filespec apostrophes in the example are doubled, which is required to get an apostrophe through the JCL PARM mechanism, as well as being escaped with a preceding backslash for Export's parser.

If command line parameters for Export and Import exceed the 100-character maximum of the JCL PARM field or TSO CALL command, you can use the "++" parameter file mechanism described in the section "Parameters in Batch (JCL)" to supply parameters in a data set. Export and Import also have a generic parameter file feature that is similar to the "++" mechanism but is available on all platforms (and also works in z/OS UNIX shell, which is not true of "++"). If you use the generic parameter file, the filespec supplied for the PARFILE parameter is not subject to name extension before opening. This file can be a sequential or instream (DD *) data set, a PDS member, or an HFS file. The file must not contain record sequence numbers or other data not part of Export or Import's command line parameters.

For certain operations, Export and Import require a special userid/password parameter that contains spaces, such as "/ AS SYSDBA". Refer to the section "Parameters Containing Spaces" for information on how to specify such values.

4.3.2 Export File

The file written by Export and read by Import is called an export file or dump file. Filespecs that you supply for this purpose are subject to extension processing with the suffix dmp. If you don't supply a filespec (you omit the FILE parameter and/or supply an empty line at the file prompt) the default expdat.dmp is used in both POSIX OFF and POSIX ON environments. In a POSIX OFF environment this is processed as a data set named EXPDAT.DMP (with prefixing implied). In a shell, it is treated as an HFS file named expdat.dmp in the current working directory.

When the export file is a data set, DCB attributes must be established when the Export utility opens the file. If you don't specify DCB attributes on a DD statement or TSO ALLOC command, and you do not use an existing data set with established attributes (which Export will preserve), LE default attributes are used. Unlike most Oracle tool and utility files, the export file is opened in "binary" mode and is subject to different default attributes than those described in the section "Data Set DCB Attributes" . For both disk and tape devices, the default attributes for output binary files are RECFM=FB and LRECL=80.

If you override the DCB attribute defaults by supplying them on a DD or ALLOC command or by using an existing data set, you can use either fixed- or variable-length record formats and any LRECL allowed by LE. However, if you are creating an export file that will be processed by Import running on a non-z/OS system, you may want to avoid variable record formats (V or VB) depending on how the data will be moved to the target system. Either the transporting software (for example, FTP) or the Import utility on the target system may have difficulty with the imbedded record and block descriptors used by V and VB formats. Refer to the section "Cross-System Export/Import" for additional details.

The RECORDLENGTH parameter of Export and Import does not affect or relate to the DCB attributes of the export file.

4.3.3 Cross-System Export/Import

One of the strengths of Export and Import is that they can be used to move Oracle data between dissimilar platforms without an Oracle Net connection between the two. This may be faster, and in some cases more secure, than running one utility or the other over an Oracle Net connection.

When Import reads data that was created by Export running on a different platform, the data must be unmodified from what was written. (Translation of data between formats and character sets is handled automatically by Import.) If you use something like File Transfer Protocol (FTP) software to move the data to the target system, specify a "binary mode" or similar processing option to prevent attempts to translate character data. This requirement exists when moving non-z/OS Export data to z/OS and when moving z/OS Export data to a non-z/OS system. If you fail to do this and the data is translated, Import typically issues the following message:

IMP-00010: not a valid export file, header failed verification

4.3.4 Export and Import Return Codes

Refer to Oracle Database Utilities for information on conditions in which return codes are set. The keyword return codes listed there have the following values on z/OS:

4.3.5 Unsupported Features

Cross-platform Export/Import of a partitioned table between EBCDIC (z/OS) and ASCII systems will fail if the partitioning is based on character ranges that are incompatible between EBCDIC and ASCII. If your partition ranges are not compatible and retain the same sequence between ASCII and EBCDIC, you must manually create the table with different partition criteria (or with no partitioning) in the target database and then run Import with the IGNORE=Y option so the error on Import's table create is ignored.

4.3.6 Export and Import Examples

The following example shows a batch jobstep execution of Export. This is a simple export of all objects owned by a userid:

//EXP EXEC PGM=EXP,
//   PARM='hrmdba/satchel file=dd:expout'
//STEPLIB  DD DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORACLE.V10G.MESG
//ORA@RM5  DD DUMMY
//EXPOUT   DD DISP=(,CATLG),DSN=BMANRY.EXPTEST2.DMP,
//   UNIT=SYSDA,SPACE=(TRK,(10,10),RLSE),
//   DCB=(RECFM=FB,LRECL=120)

4.4.1 Invocation

On z/OS, Datapump Export and Import support batch job, TSO, and shell execution environments. JCL procedures for batch execution (default names ORAEXD and ORAIMD) are supplied by Oracle and may be installed on your system with the same or different names. In TSO, both CALL and command processor (CP) invocation are supported. For the non-POSIX environments, the load module or program object names are EXPDP and IMPDP. In a z/OS UNIX shell, use the expdp and impdp (lower case) commands to invoke these utilities.

Parameters for Datapump Export and Import can be supplied on the command line or PARM field or in a file. If parameters are supplied in neither place, these utilities may prompt for certain inputs. Prompts are written to C standard output and the responses are read from standard input.

If command line parameters for Datapump Export and Import exceed the 100-character maximum of the JCL PARM field or TSO CALL command, you can use the "++" parameter file mechanism described in the section "Parameters in Batch (JCL)" to supply parameters in a data set. Datapump Export and Import also have a generic parameter file feature that is similar to the "++" mechanism but is available on all platforms (and also works in a shell, which is not true of "++"). If you use the generic parameter file, the filespec supplied for the PARFILE parameter is not subject to name extension before opening. This file can be a sequential or instream (DD *) data set, a PDS member, or an HFS file. The file must not contain record sequence numbers or other data not part of Datapump Export or Import's command line parameters.

For certain operations, Datapump Export and Import require a special userid/password parameter that contains spaces, such as "/ AS SYSDBA". Refer to the section "Parameters Containing Spaces" for information on how to specify such values.

4.4.2 Datapump Export and Log Files

The file that contains the database data written by Datapump Export and read by Datapump Import is called an export file or dump file. While conceptually similar to the export files of the Export and Import utilities described previously, the two types are not interchangeable. For example, you cannot read a plain Export file with Datapump Import and you cannot read a Datapump Export file with plain Import.

Besides writing or reading an export file, the execution of Datapump Export or Import normally writes a log file. This is just a text log reporting processing details.

On z/OS, the export and log files used by Datapump Export and Import must be HFS files; they cannot be z/OS data sets.

Because Datapump export and log file processing is done in the Oracle database server rather than the client job or session, access to both types of file is controlled with database directory objects, which are created by a database administrator. Although there are forms of directory object for both HFS files and data sets, as discussed in Chapter 5, "Oracle Server Considerations on z/OS", only the HFS form of directory object can be used in a Datapump operation.

The user-supplied names for the export and log files are required to begin with "./" (dot-slash) so that they are seen an unambiguous HFS filespecs. This requirement is unique to z/OS Oracle database servers and is not accepted by Oracle database servers on other platforms. (Oracle normally prohibits any kind of path prefix on user-supplied filenames that are processed in the database server.)

When supplied by the user, the export and log file names are subject to extension processing with the suffixes dmp and log, respectively. Because these are always treated as HFS files, extension processing is under HFS rules, as described in Chapter 2, "Oracle Software Interaction with z/OS and Language Environment", and FNA processing is not available. If omitted, the default name for the export file is ./expdat.dmp and for the log file it is ./export.log (Datapump Export) or ./import.log (Datapump Import). These files are always accessed in an HFS directory from a database directory object. The directory object name is supplied with the file name (separated by a colon) or separately, in the DIRECTORY command line parameter.

The following example shows Datapump Export being invoked in TSO to dump tables ACCT1_MST and ACCT1_DET for user RACHELQ:

READY
expdp rachelq/qow00a tables=(acct1_mst,acct1_det) directory=acctdir -
dumpfile=./acctexp

Export: Release 10.1.0.2.21 - Production on Monday,…

Both the export file and the log are written in the HFS directory associated with the ACCTDIR directory object, to which RACHELQ was previously granted access by a database administrator. The export file has root name acctexp. After extension processing the name is acctexp.dmp. The log file defaults to ./export.log.

4.4.3 Datapump Processing in the Server

When you use Datapump, much of the server processing is performed by auxiliary processes (z/OS subtasks) in the server address space. The number of processes involved depends on the degree of parallelism requested or permitted by the database administrator. Processing by auxiliary subtasks runs in the server's WLM goal and is accounted to the server address space, not to the client.

4.4.4 Datapump Export and Import Interactive Mode

Datapump Export and Import utilities have an interactive mode that is initiated by interrupting the utility after server processing begins. In TSO, this is accomplished with an ATTN or PA1 signal. In a shell, use Ctl-C or a similar mechanism to interrupt the utility. Interactive mode is not supported in z/OS batch environments.

4.4.5 Datapump Export and Import Return Codes

Datapump Export and Import can end with normal success, success with one or more warning conditions, or be terminated by an error. The return codes associated with these conditions are as follows:

In the event of a warning or error return code, check the Datapump log file for messages describing the warning or error condition.

4.5.1 Invocation

TKPROF on z/OS supports batch job, TSO, and shell execution environments. No batch JCL procedure is provided, but one can be created easily, if necessary. In TSO, both CALL and command processor (CP) invocation are supported. For non-POSIX environments, the program object name is TKPROF. In a z/OS UNIX shell, use the tkprof command (lower case) to invoke this tool.

TKPROF requires two command line parameters, the first identifying the input Oracle trace file to be read and the second supplying a filespec for the output report. If no parameters are supplied, TKPROF displays a brief help message and ends normally. If the input trace file is specified but not the output file, TKPROF prompts (to standard output) for the output filespec and reads it from standard input. Additional command line parameters are permitted for various options.

4.5.2 Input Trace File

Input to TKPROF is a flat file written by the Oracle database server when SQL tracing is active in a client session. For information on methods for activating and deactivating the trace with an ALTER SESSION statement or the DBMS_SESSION.SET_SQL_TRACE procedure, refer to the Oracle Database Performance Tuning Guide.

On z/OS, Oracle database server trace files are written in one of two forms: as SYSOUT (JES) spool files or as sequential (DSORG=PS) disk data sets. TKPROF cannot read JES spool files directly, so if your installation is using SYSOUT for trace files, you will need to identify the trace file containing the SQL trace and copy it to a sequential disk data set in order to use TKPROF. Copying can be done with a tool such as the PRINT command of the IBM Spool Display and Search Facility (SDSF) or a comparable mechanism. Whether the trace files for your database instance are written to SYSOUT or directly to a data set, you may need to work with your database administrator to obtain access to the trace data.

• It is a limitation of IBM access methods that you cannot run TKPROF (or any other program) to directly access an Oracle database server trace that is being written as a disk data set while the database server still has the data set open for output. To ensure that a trace file is closed, the database server session associated with the trace must end (disconnect from the database server). This is one of the advantages in using spool files for database server tracing; tools such as IBM SDSF can read and copy a spool file while it is still open to the database server.

  Note:

  The Oracle Database Performance Tuning Guide discusses several Oracle database server initialization parameters that are relevant to SQL tracing and TKPROF. Two of these, MAX_DUMP_FILE_SIZE and USER_DUMP_DEST, are not meaningful on z/OS and can be ignored. The third, TIMED_STATISTICS, is meaningful and may need to be set to get useful performance data. TIMED_STATISTICS is a dynamic parameter and can be set at the session level.

Although the input trace file is normally going to be a sequential data set on z/OS, TKPROF will also read a PDS member or an HFS file as its input. File name extension processing with the suffix trc is done on the trace filespec before it is opened. If your trace data set name does not end in .TRC, include two slashes in front of the name or use a DD: filespec to avoid extension processing.

4.5.3 Output Files

The report file written by TKPROF can be a sequential disk data set, a PDS member, a SYSOUT (spool) data set, or a file in the HFS. The filespec you supply for the output file is subject to extension processing with the suffix prf. By default, records in this report are at most 80 data bytes long; if you use the WIDTH command line option to request report lines that will exceed the LE default LRECL (1028, or 1024 data bytes), you must override the file's LRECL using DD statement parameters or FNA.

Optionally, using the RECORD= parameter, TKPROF will write a file containing all user SQL statements from the session. Like the report, this file can be a sequential disk data set or PDS member, a SYSOUT data set, or a file in the HFS. Filespecs supplied using the RECORD parameter are subject to extension processing with the suffix sql.

4.5.4 Return Codes

TKPROF either processes successfully or encounters errors and fails, there are no warning conditions. Return codes from TKPROF are as follows:

4.5.5 TKPROF Example

The following example shows a batch job step to run TKPROF to analyze a trace file in a z/OS data set named JSMITH.ORAPDB1.SESS3.TRACE:

//TKPROF  EXEC PGM=TKPROF,REGION=8192K,
//   PARM='DD:TRACE S:C'
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10.MESG
//TRACE    DD  DISP=SHR,DSN=JSMITH.ORAPDB1.SESS3.TRACE

The report is written directly to SYSOUT class C using a SYSOUT filespec.