Release 2 (9.2.0.1.0) for OS/390
Part No. A97312-01
 Contents |
 Index |
|
Oracle9i Enterprise Edition User's Guide
Release 2 (9.2.0.1.0) for OS/390 Part No. A97312-01 |
|
Oracle utilities provide flexible file support within the OS/390 environment. Using file specification syntax and a special feature, Oracle utilities allow great flexibility in specifying and accessing OS/390 data sets and other types of files.
This discussion does not apply to Oracle utilities running in the UNIX System Services environment. For information on Oracle utilities running in the USS environment, see Chapter 4, " Accessing Oracle9i Under USS".
This chapter describes the interaction between OS/390 files and Oracle utilities. The following topics are discussed:
All Oracle utilities use OS/390 files. Every tool or utility accesses one or more input or output files. The term file refers to the logical unit that is the actual repository of the data the tool or utility reads from or writes to. A file can be an OS/390 data set, a TSO screen, the OS/390 operator console, an input or output spool file, or even a load module containing static input data.
Most OS/390 users are accustomed to utilities or applications that operate on files specified in DD statements or TSO ALLOC commands. Many TSO commands and some IBM utilities (such as IDCAMS) let you specify a file as a data set name, performing dynamic allocation automatically instead of requiring a pre-allocated DD statement. With Oracle file support, you can also create new data sets without a DD statement and you can access devices (such as the operator console) OS/390 does not usually consider files.
The Oracle9i database server supports all non-VSAM file organizations except spanned records (RECFM=VS or VBS) and ISAM (DSORG=IS). For information about the VSAM files that comprise the Oracle control, database server, and redo log files, refer to the Oracle9i Enterprise Edition System Administration Guide for OS/390.
All Oracle9i for OS/390 utilities recognize a uniform notation for specifying files. The general form of this notation (called a file specification or filespec) is shown in the following example:
[/pathname/]data
where:
pathname
|
is one of several reserved names specifying the type of filespec. |
data
|
is additional information or specific file related parameters. |
The following string indicates a file identified by DDname INFILE in the present job step or TSO session:
/DD/INFILE
You can specify files in the following ways:
By DDname
By full data set name
By unqualified data set name
By PDS member (as a control or parameter file)
The following table summarizes the types of file specifications you can use with Oracle utilities.
Table 3-1 Summary of Filespecs
The file specifications and the notation for using them are described in the following sections:
To specify a file by DDname to an Oracle tool use the following string, where ddname is the one-character to eight-character DDname:
/DD/ddname
The DDname must be allocated to the job step or TSO session before the file is opened. In a TSO session, an ALLOC command must be issued, usually before the tool is called. (During a SQL*Plus session, you can use the HOST command to issue a TSO ALLOC command for a file used by a subsequent SQL*Plus command such as START or SAVE.) In a batch job, a matching DD statement must be included in the JCL.
You can specify a member of an OS/390 PDS as part of the /DD/ notation. The member name must be provided in parentheses immediately after the DDname, as shown in the following example:
/DD/MYPDS(SQL2)
The DD statement for batch (or TSO ALLOC) specifies the PDS name but no member name, as shown in the following example:
//MYPDS DD DISP=SHR,DSN=PAYROLL.SQLLIB.CNTL
The following usage notes are for specifying files by DDname:
When an existing PDS member is specified for output, the current contents of the member are completely replaced by what is written. If a nonexistent member name is specified for output, then a new member with that name is created.
When a JES spool file is used by an Oracle tool, the file must be specified by DDname. The corresponding DD statement or ALLOC command indicates a SYSOUT class (output spool files) or DD * (input spool files, only used in batch jobs).
See the SPOOL OUT command in "SPOOL OUT " for an example specifying a filename with /DD/.
You can specify a file by OS/390 data set name to an Oracle tool using the following syntax, where hlq.dsname is the fully-qualified data set name:
/DSN/hlq.dsname
This notation gives access to a sequential OS/390 data set with the fully-qualified data set name. The Oracle9i database server dynamically allocates the data set. You do not need to provide a DD statement or a TSO ALLOC command.
Access a member of a PDS by including a member name in parentheses immediately after the data set name, as in the following example:
/DSN/ORACLE.OSDI.CNTL(ORASQL1)
In this case, the member ORASQL1 of PDS ORACLE.OSDI.CNTL is read or written.
The following usage notes are for specifying files by full data set name:
If the file is being used for input, then the data set must exist and be cataloged; otherwise, an error is reported.
If the file is being used for output, then the existing data set of the specified name is overwritten. If the data set does not exist, then it is created through dynamic allocation with your system's default space allocation.
When an existing PDS member is specified for output, the current contents of the member are completely replaced by what is written. If a nonexistent member name is specified for output, then a new member with that name is created.
If an output file using PDS member notation specifies a data set name that does not exist, then the data set is created dynamically in the same manner as a sequential data set. However, because OS/390 has no default space allocation for PDSs, the Oracle9i server inserts its own default of one track primary, one track secondary, and one PDS directory block. This default is not appropriate for most PDS applications.
See the start file parameter in "Running Under TSO " for an example of the /DSN/ notation.
You can specify a file without a pathname to an Oracle tool. If a file specification does not have a pathname (it does not begin with a slash), then it is treated as a partial data set name that is subject to extension.
Most Oracle utilities also extend unqualified data set names by appending a suffix to the right of the name. The suffix, often called a filetype extension, is generally an acronym or mnemonic describing the type of data or records in the file. For example, files read by the SQL*Plus START command, which normally contain SQL*Plus commands or SQL statements, have a filetype extension of SQL. "Filetype Suffixes" provides a list of the filetype extensions used by Oracle utilities.
Unqualified data set file names, like fully-qualified names, can refer to PDS members by including the member name in parentheses immediately after the name. The addition of the filetype extension does not affect the member name because the suffix is added before the member name.
The following usage notes are for specifying files by unqualified data set name:
Only file names specified without a pathname are subject to extension processing.
Extension processing is further controlled by the File Name/Attribute Augmentation Facility (FNA).
To illustrate prefix and suffix extension of unqualified data set names, assume a TSO user, whose PROFILE PREFIX is GJONES, calls SQL*Plus and then enters the following command:
START MYSQL2
Because there is no pathname prefix, MYSQL2 is considered an unqualified data set name and is extended (at both ends) to become GJONES.MYSQL2.SQL. The following command produces the same result:
START /DSN/GJONES.MYSQL2.SQL
The next example illustrates unqualified file specification with a member name:
START MYSQLLIB(TEST2)
This name is extended to GJONES.MYSQLLIB.SQL (TEST2).
You can redirect standard files and parameters to specify a nondefault file for the duration of the associated TSO command or job step. To specify redirection, use one of the following symbols followed by a file specification from the command parameter line in TSO or the PARM field of the EXEC statement in batch:
<
|
for input |
>
|
for output |
>>
|
to append output |
?
|
for error output |
? ?
|
to append error output |
++
|
for parameters |
Any of the standard input, output, and error files can be redirected with these symbols. While the redirection parameters can be mixed with other command or utility parameters, Oracle Corporation recommends grouping these parameters at the beginning or end of the command parameters or PARM field for clarity. The following example redirects the input file to the INFIL DD statement.
//STEP1 EXEC PGM=PROC,PARM='</DD/INFIL' //INFIL DD *
If an output file specified by a data set name does not exist, then the Oracle application dynamically allocates it using the installation or IBM default space and unit specifications. If the output file does exist, then the Oracle application normally overwrites any existing contents.
You can append output to a data set by doubling the redirection prefix symbol. If the data set does not already exist, then it is created. For example, the following command adds error messages to the end of the data set named tsoprefix.MY.ERRORS:
??MY.ERRORS
To use parameter redirection, imbed the following string in the OS/390 parameter field, where filespec is a valid Oracle for OS/390 file specification:
++filespec
When the Oracle tool or utility encounters this in the parameter field, it scans the data in the file specified by filespec and processes it as if it is included in the OS/390 parameter field. At end-of-file (EOF), the Oracle tool or utility continues scanning the OS/390 PARM field at the end of filespec.
The following example uses a parameter redirection operator:
//STEP1 EXEC PGM=PROCOB18,PARM='++/DD/SYSPARM HOST=COBOL' //SYSPARM DD * INAME=/DD/SYSIN ONAME=/DD/SYSPUNCH LNAME=/DD/SYSPRINT USERID=SCOTT/TIGER /*
In this example, the Oracle Pro*COBOL Precompiler processes all the data (including INAME=......USERID=SCOTT/TIGER) as if it is passed in the OS/390 parameter field. You can specify parameter redirection anywhere in the OS/390 parameter field and it is implemented at that point.
|
Note: The ++ filespec does not support the use of redirection parameters. |
The contents of Oracle files can be identified by the filetype suffix. A filetype suffix is supplied by each Oracle tool for each file used. The suffix is a short mnemonic or acronym identifying the file contents. The suffixes in Table 3-2 identify the different types of Oracle files:
Table 3-2 Filetype Suffixes for Oracle Utilities
Many utilities support the full range of naming syntax described in "Using OS/390 Files ". Some utilities require file names in a simpler format or shorter length than is preferable in OS/390. If the full range of naming syntax is not available, then you can take advantage of FNA name manipulation to convert a simple name string to a larger, more complex file name string.
The FNA facility provides automated user-defined manipulation of file names beyond the scope of normal OS/390 file name and extension processing. Before a tool opens a file, it calls FNA processing. The tool supplies FNA with the base file name and filetype suffix. FNA builds the final file name from this input, using the file syntax array (FSA) data and a set of rules. The final file name string, which is essentially unrestricted in size and syntax, is returned to the tool. The tool then proceeds with opening and reading or writing the file.
Only file names specified without a pathname (without a /.../ prefix) are subject to extension processing.
With FNA, you can do the following:
Access OS/390 data set names that have relatively complex syntax (such as PDS).
Overcome logical ambiguities imposed by nonextendable file names (those prefixed with a pathname).
Select JCL-like parameters (such as DCB attributes and DASD space allocations) for specific types of files written by Oracle utilities.
A small set of FNA controls is distributed with Oracle9i for OS/390. These controls ensure the use of specific DCB attributes with several types of output files. You can modify, eliminate, or expand these controls.
FNA is controlled by the file syntax array (FSA), a logical table containing specifications for modifying file names and adding JCL-like keywords before a file is actually opened. The FSA contents are determined by the following:
A set of default FSA entries distributed with the product.
An optional user-defined control file that adds more FSA entries or alters the distributed default entries.
FNA file name manipulation is controlled by the FSA contents and the following specifications:
You can supply the base file name in the following ways:
In the parameters specified when a tool is called
In response to a prompt from the tool
In a subcommand of the tool (like the SQL*Plus SPOOL command)
The logical table of FSA data used by FNA is comprised of three columns. The first column contains filetype suffixes. Each filetype for which FSA data is present appears in this column once. The second column contains character strings that designate construction of the final file name for the corresponding filetype. The third column contains character strings of JCL-like keywords. These are added to final file names for the corresponding filetype and are only effective when the file is opened for output.
To construct a file name, FNA substitutes characters as appropriate, then extends the name accordingly.
The FNA file name construction process copies all characters from the modification string except plus signs exactly as given. FNA uses the following substitution for these special characters:
FNA takes the base file name and filetype suffix as input and uses the following procedure to construct the final file name:
The FSA is searched for an entry with a filetype matching the filetype suffix provided. If a matching entry is found, then FNA proceeds with Step 2 through Step 4. If no matching entry is found, then FNA uses the unmodified base file name and proceeds with Step 4.
If the FSA contains a file name string, then that name is modified. If the FSA does not contain a name string, then the base (user-supplied) file name is modified.
To modify the name, first the base file name is substituted wherever the plus sign appears in the FSA file name string, and then the TSO PROFILE PREFIX is substituted wherever an asterisk appears. If no PREFIX is defined, then each asterisk is replaced by a null string.
If the FSA entry for the filetype contains a non-null attribute string, then a colon (:) and the attribute string are appended to the file name (which might have been modified by Step 2).
The colon is a delimiter to separate the attribute keywords from the file name. The attribute string is the third column of the FSA entry, where JCL-like attributes are specified. The addition of attributes is only meaningful for files that undergo dynamic allocation. FNA attributes for /DD/ files have no effect.
If the file name does not begin with a path prefix (/) or apostrophe ('), then the following steps are taken:
A period and the filetype suffix are added on the right. The filetype suffix is added so any PDS member name or attributes in parentheses are kept on the right end of the file name.
If attributes are specified by the user in addition to those specified in the FSA, then the user's attributes are attached to the name following those from the FSA. If the user specifies attribute keywords that duplicate those in the FSA, then the user's attributes are used.
The following string is attached to the beginning of the name, where prefix is your TSO PROFILE PREFIX:
/DSN/prefix
If the PROFILE PREFIX is not set or you are not running under TSO, then only /DSN/ is attached to the name.
FNA processing is complete and the file name is returned to the tool, which opens and then reads or writes the file.
Suppose TSO user GJONES (with PROFILE PREFIX set as GJONES) has issued the following SQL*Plus command, which specifies a base file name of MYSQL2:
START MYSQL2
SQL*Plus associates a filetype of SQL with the file used in the START command. Assume also that the following entry is in the FSA:
FSA (
FTYPE(SQL)
FNAME('SQLLIB(+)')
FATTR('VB,LRECL=240')
)
The following sample shows an FNA process:
Locate the FSA entry
This locates the FSA entry for the filetype SQL (case is ignored).
Perform the substitution
This processes the file name string SQLLIB(+), substituting the base file name MYSQL2 for the +, resulting in the following:
SQLLIB(MYSQL2)
Add the attribute string
This step appends a colon (:) and the attribute string. The section "Attribute Keywords" discusses the attribute string. The result is the following name:
SQLLIB(MYSQL2):VB,LRECL=240
Since the name does not begin with a path prefix or apostrophe, the following steps are completed:
Add the suffix
Attaches a period and a filetype suffix to the end of the name, before the member name in parentheses. The result is the following name:
SQLLIB.SQL(MYSQL2):VB,LRECL=240
Add the user's attributes
No additional attributes are specified with the SQL*Plus START command, so the name remains unchanged.
Add /DSN/ and the TSO PREFIX
This step attaches the /DSN/ pathname and the TSO PROFILE PREFIX to the beginning of the name, resulting in:
/DSN/GJONES.SQLLIB.SQL(MYSQL2):VB,LRECL=240
This file name string is passed back to SQL*Plus and opened. Because the START command opens the subject file for input, the attribute data is ignored and the data set's existing DCB attributes are observed. A member called MYSQL2 in the PDS named GJONES.SQLLIB.SQL is opened for the user's MYSQL2 specification.
You can use FNA to provide additional flexibility in managing the interaction between Oracle utilities and OS/390 files by controlling the FSA entries. The section "User-Defined FNA Control Files" describes additional examples of FSA entries and FNA processing.
The distributed Oracle product contains the default FSA entry listed in Table 3-3. For all other filetypes, the defaults are null strings, which means FNA supplies no special name or attributes.
Table 3-3 Oracle Product Default FSA Entry
| Filetype | FNAME | FATTR |
|---|---|---|
| DMP | (null) | FB,LR=4096 |
The DMP entry sets DCB attributes required for Export data.
The FSA default entry can be overridden, unless otherwise noted in Table 3-3, by supplying an FNA control file with an entry for the corresponding filetype with a string specified for the item (FNAME or FATTR) to be overridden.
All Oracle utilities use the default FSA entries every time the Oracle9i database server is called. You cannot permanently modify the default entries. However, you can define a control file that includes FSA entries for a specific invocation of an Oracle tool.
An FNA control file has the following uses:
Add FSA entries for suffixes not included in the default FSA, including specifications for modifying file names and appending output attributes.
Change the default FSA specifications for modifying file names and appending attribute data. These changes can include eliminating the default specifications entirely.
Add specifications for modifying file names to a default entry that only appends attribute data, or add specifications for appending attribute data to an entry that only modifies file names.
A user-defined FNA control file always has the following name:
/DD/ORA$FNA
You must allocate this file to an ORA$FNA DD statement in your JCL or TSO session. The ORA$FNA DD statement must refer to one of the following types of data sets:
A sequential data set with fixed or variable length records
An instream (DD *) data set
A PDS member with the member name specified in the DD statement
If an ORA$FNA DD statement is present when a utility is called, then the utility refers to the FNA control file specified in the DD statement. If an ORA$FNA DD statement is not present, then FNA uses the default FSA entries.
The FNA control file contains FSA entries specified with EBCDIC characters. Any number of FSA entries can be included. An FSA entry can be continued over multiple lines; no special continuation indicator is required. Comments, surrounded by /* and */, can be included at any point that permits a blank or end of record. The control file records must not contain sequence numbers.
FSA entries have the following format:
FSA (FTYPE(suffix-type)
FNAME('name-string') /*<optional>*/
FATTR('attribute-string') /*<optional>*/
) /*<required>*/
where:
is a one-character to eight-character string indicating a file name suffix. Any file name with this suffix is substituted by the value provided by the FNAME keyword that follows.
Do not enclose this string in single or double quotes. The string is converted to uppercase characters. The first blank space or right parenthesis, , is considered the end of the string. For example:
FTYPE(SQL)
is an alphanumeric string indicating the file name to substitute for files that have the suffix specified in FTYPE. The FNAME keyword is optional.
Because FNAME is optional, the value for the omitted keyword is the value in the default FSA distributed with the Oracle9i database server, if any. If there is no default entry in the default FSA, then an omitted FNAME is assumed to be a null (empty) string.
Enclose the substituted file name in single or double quotes; blanks within the quotes are recognized. For example:
FNAME('/DSN/*.ORACLE.+.SQL')
If the substituted file name needs more than one line in the control file, then extend the value to the end of the line and continue in the first position of the next line.
If the TSO PREFIX is GJONES and the user supplies a base file named MYSQL, then FNA processing resolves this name string to:
/DSN/GJONES.ORACLE.MYSQL.SQL
If there is no TSO PREFIX, then FNA processing resolves this string to the invalid file name string:
/DSN/.ORACLE.MYSQL.SQL
This particular FNAME is only usable with a TSO PROFILE PREFIX.
is the attribute data added to the substituted file name. The FATTR keyword is optional.
The attribute data can be in the form of a keyword list separated by commas and enclosed in single or double quotes. Do not include blanks. For example:
FATTR('VB,LRECL=255')
Because this keyword is optional, the value for the omitted keyword is the value in the default FSA distributed with the Oracle9i database server, if any. If there is no default entry in the default FSA, then an omitted FATTR is assumed to be a null (empty) string.
Refer to "Attribute Keywords" for information about the correct syntax for this keyword value.
The following usage notes are for FSA control file entries:
Include any character you want added to the base file. These characters are added exactly as specified in the string.
Use a plus sign, +, to indicate where to substitute the user-supplied base file name.
When the utility is run with PROFILE PREFIX, use an asterisk (*)to indicate where to substitute the TSO PROFILE PREFIX in the file name. If the utility is run with PROFILE NOPREFIX, then the asterisk is ignored.
To use the TSO prefix substitution function in a batch job, run the job under the control of the batch monitor program IKJEFT01. Use the TSO PROFILE PREFIX command in your SYSIN data stream to set the appropriate TSO prefix before calling the utility.
To eliminate a default FSA entry, specify an empty string with a pair of adjacent single or double quotes.
Attribute data that can be added to a file name includes the following examples with the equivalent JCL parameter. Keyword characters in lowercase can be omitted.
Table 3-4 Attribute Keywords and JCL Parameter Equivalents
The following keywords indicate DCB record format:
Table 3-5 DCB Record Format Keywords and JCL Parameter Equivalents
| Keyword | JCL Parameter Equivalents |
|---|---|
| F | RECFM=F |
| FA | RECFM=FA |
| FB | RECFM=FB |
| FBA | RECFM=FBA |
| FBS | RECFM=FBS |
| FBAS | RECFM=FBAS |
| V | RECFM=V |
| VA | RECFM=VA |
| VB | RECFM=VB |
| VBA | RECFM=VBA |
Most logical or syntax errors in the FNA control file are detected when the Oracle utility initializes. Error messages are displayed in the standard error file, which is normally the screen in TSO (if PROFILE WTPMSG is set) or the OS/390 console (if PROFILE NOWTPMSG is set). In batch, the SYSERR DD statement controls the output destination. In general, an erroneous entry is ignored and utility operation proceeds as though the entry is not specified.
Errors within the quoted FNAME or FATTR strings, such as invalid name syntax or improper attribute keywords or values, are not detected until the utility opens the associated file. These errors normally result in descriptive messages written to the standard error file. Because such errors usually make it impossible to open the associated file, utility operation typically halts in this situation.
When you use an FNA control file to override a default FSA entry, the string you specify for FNAME or FATTR completely replaces the string in the default entry. Be careful to add attributes using FATTR; a default attribute not replicated in the control file FATTR string is lost. For example, the DMP filetype has default FATTR of FB and LR of 4096 to meet the requirements of Export. To force the DMP file to a specific allocation unit, you can use an FNA control file containing:
FSA(FTYPE(DMP) FATTR('UNIT=SYSTSO'))
Because the override FATTR string replaces the default, the RECFM with FB and LRECL with 4096 attributes are lost in this sample specification, resulting in an unusable Export file. To prevent this, you must respecify the needed defaults along with the attribute being added, as in the following example:
FSA(FTYPE(DMP) FATTR('UNIT=SYSTSO,FB,LR=4096'))
DCB attributes specified through FATTR, whether defaults or from an FNA control file, replace corresponding attributes that might already be associated with an OS/390 data set.
When writing to pre-allocated data sets with DCB attributes you want to retain, do not use an FATTR specification, as shown in the following example:
FSA ( FTYPE(SQL) FNAME('/DD/SQLS') )
Attributes supplied for a file specified with a /DD/ pathname generally have no effect. In this case, attributes must be specified on the DD statement or in the TSO ALLOC command, or must already exist (for an existing data set).
Files opened for output default to DISP=OLD. If you want to allow for concurrent use of PDSs, then you must override this default by including an FATTR string of SHR in the FNA control file.
The examples in the following sections illustrate sample FNA control file specifications for:
Partitioned data sets
Shared libraries
Group libraries
Example 1 Partitioned Data Sets
In the following example, assume the installation wants each user to have a PDS that stores the SQL files created and run by each user. The installation sets up an FNA control file containing the following entry:
FSA( FTYPE(SQL) /* SQL*PLUS */
FNAME('SQLS(+)'))
Assume a user with a TSO PREFIX of GJONES has an ORA$FNA DD statement pointing to the FSA in the previous example. When the user issues the SQL*Plus START command with PAYROLL as the argument, GJONES.SQLS.SQL(PAYROLL) becomes the file name and is used for the SQL file.
Without this FSA entry, the system defaults to file name GJONES.PAYROLL.SQL for the SQL file.
This example emphasizes two points:
The general defaults for naming files are implemented in the default FSA, which has the following format:
FSA (
FTYPE(suffix)
FNAME('+')
)
You can use the FNA facility to specify simple names that are treated as members of PDSs.
Example 2 Shared Libraries
In this example, assume the installation wants shared libraries for SQL files. The installation sets up the following FSA control file:
FSA (
FTYPE(SQL)
FNAME('/DSN/SYS5.SQL(+)')
)
Assuming the same user described in "Partitioned Data Sets", the SYS5.SQL(PAYROLL) becomes the shared library name and is used for the SQL file.
The pathname /DSN/ preceding the data set name suppresses the extension processing normally performed under OS/390. Both /DSN/ and /DD/ can be used in the FSA.
Example 3 Group Libraries
In the following example, assume an installation groups users, and each group is identified by a TSO PREFIX. This FSA entry provides the necessary names for a library shared within a group:
FSA( FTYPE(SQL)
FNAME('/DSN/*.SQL(+)')
Assume a user with a TSO PREFIX of GROUP2 uses the SQL*Plus command START PAYROLL and has an ORA$FNA DD statement pointing to the previous FSA. Group2.SQL(PAYROLL) becomes the shared group library name and is used for the SQL file.
Although the Oracle9i server dynamically creates these data sets, Oracle Corporation recommends the installation pre-allocate all data sets referenced by FNA. Pre-allocating the data sets that are referenced by FNA ensures your requirements are met. The default values selected by FNA might not be appropriate for your requirements.
|
 Copyright © 2002 Oracle Corporation All rights reserved |
|