6
SQL*Loader

SQL*Loader is a utility that loads from sequential files into tables in an Oracle9i database.

Read this chapter to understand how this powerful utility's advanced function data loader capabilities are used under OS/390.

The following sections are discussed in this chapter:

This information supplements the documentation for SQL*Loader in Oracle9i Database Utilities.

Running Under UNIX System Services

When running SQL*Loader under USS, considerations are the same as described in the Oracle9i Database Utilities manual. Refer to the section "OS/390 UNIX System Services Overview" for general information about running utilities in the USS environment.

Running Under TSO

Use SQL*Loader to load data from sequential files into the Oracle9i database. Use a control file to describe the data location, data format, and target tables. You can direct data that does not satisfy specified conditions to special files.

The following is the syntax of the SQL*Loader command under TSO:

SQLLDR keyword=value [keyword=value,...]

The parameters passed to SQL*Loader are keyword parameters. The parameters are fully described in Oracle9i Database Utilities.

SQL*Loader Files

SQL*Loader uses the following files:

A BAD file to store records from the input data file that fail basic validation checks, such as nonnumeric data in a numeric field
A CTL file containing all the control information
A DAT file containing the data to be loaded
A DSC file to store the discarded records from the input data file, which are records that do not qualify to be loaded into the database
A LOG file containing entries describing what SQL*Loader did

SQL*Loader File Names

The section "File Name/Attribute Augmentation (FNA) Facility " describes extension and FNA processing that each of the file names supplied to SQL*Loader is subject to . The filetype extensions are BAD, CTL, DAT, DSC, and LOG.

For example, without any special FNA controls, the following specification results in SQL*Loader using tsoprefix.TESTDATA.CTL as the control data set:

SQLLDR ... CONTROL=TESTDATA

In batch or when using TSO without a PROFILE PREFIX, TESTDATA.CTL is used as the control file. This data set is not a good choice in most OS/390 systems because TESTDATA is not a proper high-level qualifier for a data set name.

If any file other than the control file is not specified, then SQL*Loader uses the control file name to derive the name of the file. For example, if the LOG parameter is omitted but CONTROL=TEST3 is specified, then a log file name of tsoprefix.TEST3.LOG is used . The same processing applies to the BAD, DAT, and DSC files if any of these are not specified.

To have a BAD file generated, you must specify a BAD filetype.

If the supplied control file name is one that cannot be modified with an extension (that is, it is prefixed with a pathname surrounded by slashes), then the name cannot be used to derive the names of any omitted files. In this case, SQL*Loader uses the default name LOADER for the derivation.

For example, to call SQL*Loader use the following command:

SQLLDR USERID=SCOTT/TIGER CONTROL=/DD/CTLFILE ...

This command results in the derived LOG file name tsoprefix.LOADER.LOG because /DD/CTLFILE cannot be used to derive the other distinct file names.

This approach to deriving file names can be convenient in the TSO environment. But when running in batch where there is no TSO PROFILE PREFIX it might be difficult to use, depending on the data set naming standards and allocation controls at your installation . The best approach to running SQL*Loader in batch is to specify all required files with /DSN/ or /DD/ notation:

//LOAD EXEC PGM=SQLLDR, 

// PARM='CONTROL=/DD/CTL DATA=/DD/DATA LOG=/DD/LOG BAD=/DD/BAD DISCARD=/DD/DIS'

//CTL  DD  ... 

//DATA DD  ... 

//LOG  DD  ... 

//BAD  DD  ... 

//DIS  DD  ...

SQL*Loader File Attributes

SQL*Loader for OS/390 differs from SQL*Loader for other systems in the way file attributes are decided for the BAD and DSC files. In other systems, the file attributes for these files are made identical to the data file. This is done so you can make corrections to these files and recycle them through SQL*Loader again. In OS/390, these data sets are created with default DCB attributes that are generally different from the attributes of the input file. There are two ways to overcome this:

You can, in your JCL or TSO ALLOC command for the BAD/DSC file, specify the DCB attributes of the BAD/DSC files be the same as that of the data file by a refer back, as shown in the following example:
```
//BAD DD DISP=SHR,DSN=PROD.CASE1.BAD,DCB=PROD.CASE1.DAT 

//DIS DD DISP=SHR,DSN=PROD.CASE1.DSC,DCB=PROD.CASE1.DAT 
```
For each invocation of SQL*Loader you can have a different FNA control file allocated to the ORA$FNA DD statement at runtime . This FNA control file can contain FSA entries for suffixes BAD and DSC specifying the correct FATTR parameters.

Running in Batch

SQL*Loader is supported in the batch environment through the ORALDR JCL procedure . A copy of this procedure is reproduced in the following example. The example assumes the Oracle9i database server has been installed with the system identification (SID) of ORA1.

//ORALDR PROC INDEX='oran',

//*                                         NONVSAM LIBRARY HLINDEX.

//             LIBV='orav',

//*                                ORACLE/VERSION AND INSTALL LEVEL.

//             INDD=LDRCTL,          INPUT CONTROL FILE DDNAME.

//             SYSOUT='SYSOUT=*',    SYSOUT CLASS.

//             LOGDD=LOGDD,          OUTPUT REPORT.

//             USERID='NAME/PASSWORD', USERID

//             D=FALSE               DIRECT=TRUE FOR FAST LOADER

//*

//*                                                                *

//*                 SQL*LOADER BATCH PROCESSOR                     *

//*

//ORALDR   EXEC PGM=SQLLDR,REGION=4M,

// PARM=('CONTROL=/DD/&INDD USERID=&USERID',

//       'LOG=/DD/&LOGDD DIRECT=&D')

//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=*

//SYSIN    DD DUMMY

//*

//*  REQUIRES //ORA@SID  DD DUMMY STATEMENT (ORACLE SUBSYSTEM NAME).

//*  ALSO REQUIRES BADDD AND DSCDD TO SPECIFY BAD AND DSC FILES

//*

When running this procedure, you must include additional DD statements. These DD statements refer to other required data sets.

The following example illustrates the use of the previous procedure:

//USER1JOB JOB  .............. 

//S1  EXEC ORALDR, 

// PARM='CONTROL=/DD/CTL USERID=A/B BAD=/DD/BAD DATA=/DD/DAT 

//        DISCARD=/DD/DIS' 

//CTL  DD DISP=SHR,DSN=PROD.CASE1.CTL 

//DAT  DD DISP=SHR,DSN=PROD.CASE1.DAT 

//BAD  DD DISP=SHR,DSN=PROD.CASE1.BAD,DCB=PROD.CASE1.DAT 

//DIS  DD DISP=SHR,DSN=PROD.CASE1.DSC,DCB=PROD.CASE1.DAT 

//SYSIN  DD DUMMY 

//ORA@ORA1 DD DUMMY

When called in batch, the command line arguments are often longer than is allowed in the PARM field of the EXEC statement . To work around this, use the standard Oracle parameter redirection operation as in any other Oracle utility. Refer to "Redirecting Standard Files and Parameters" for more information.

Return Codes

SQL*Loader issues the following return codes:

0	is a normal (successful) completion.
4	indicates a discontinued load. This is due to a runtime error, such as running out of file extents.
8	is an end due to an irrecoverable error.

SQL*Loader VSAM File Support

SQL*Loader for OS/390 is enhanced to provide native VSAM support. This feature lets you load Oracle tables directly from VSAM key sequenced data set (KSDS), entry sequenced data set (ESDS), and relative record data set (RRDS) clusters . All the normal SQL*Loader operating capabilities, such as loading multiple tables and discard logic, work with VSAM clusters in the same way as with non-VSAM data sets.

Specifying VSAM Input to SQL*Loader

The file name supplied with the INFILE or INDDN keyword can take one of three forms:

/DD/`name`	is the one-character to eight-character name of a DD statement or TSO allocation already present in the jobstep or TSO session. The DD statement or allocation specifies the VSAM cluster to be loaded.
/DSN/`name`	is the 1-character to 44-character, fully-qualified data set name of the VSAM cluster. SQL*Loader dynamically allocates the cluster with DISP set to SHR before opening it.
`name`	a file name that does not begin with /DD/ or /DSN/ is assumed to be an unqualified data set name. In this case, SQL*Loader appends a TSO user's PROFILE PREFIX on the left side of `name` and proceeds with dynamic allocation and open as if /DSN/ is used. When no PROFILE PREFIX is defined for a TSO user and when running in batch, `name` is processed as a fully-qualified name just as though a /DSN/ pathname prefix is included.

In all these cases, name can optionally include a slash followed by a VSAM cluster password on the right side. Because SQL*Loader only opens VSAM clusters for input, a cluster's READPW (read password) or any higher-level password can be specified . If a cluster does not have a read password, then no password must be specified. If a cluster has a read password and the correct password is not supplied in the file name, then VSAM performs normal prompting of the TSO user or system operator when SQL*Loader opens the cluster. VSAM password prompting is affected by the ATTEMPTS parameter of the IDCAMS DEFINE or ALTER command.

Examples

The following partial examples of SQL*Loader LOAD DATA statements show how to specify VSAM files in various ways.

The VSAM cluster GJONES.PAYSEQ2.MASTER is dynamically allocated for input; the password ZAP is included:

LOAD DATA INFILE /DSN/GJONES.PAYSEQ2.MASTER/ZAP ...

Assuming a TSO user with a PROFILE PREFIX of GJONES, the VSAM cluster GJONES.MY.RRDS is dynamically allocated for input:

LOAD DATA INFILE MY.RRDS ...

A password is also supplied:

LOAD INFILE /DD/VSAMDD/password ...

For the following specification, the job includes a DD statement:

//VSAMDD DD DISP=SHR,DSN=GJONES.RECVBL.LOG

Or, if it is being used in a TSO session, a prior TSO ALLOC command can be issued:

ALLOC FILE(VSAMDD) DA('GJONES.RECVBL.LOG') SHR

SQL*Loader VSAM Processing Considerations

SQL*Loader VSAM processing is generally the same as for non-VSAM input files. The following considerations are unique to VSAM:

SQL*Loader reads KSDS clusters in key sequence, RRDS clusters in record number sequence, and ESDS clusters in RBA sequence . Empty record slots in an RRDS are not read and do not contribute to SQL*Loader SKIP or LOAD counts.
SQL*Loader also processes an Alternate Index Path (AIX) as input . Oracle Corporation recommends loading directly from the base cluster since AIX only changes the order in which records are read and can significantly reduce performance.
All VSAM-specific error or warning messages issued by SQL*Loader are written to the C programming language standard error file (normally the SYSERR DD allocation in batch or the user's screen in TSO).
If SQL*Loader is unable to dynamically allocate a VSAM cluster, then the return, error, and information codes from OS/390 dynamic allocation are reported and the load ends.
If the OPEN of a VSAM cluster fails, then the return code and ACBERFL (ACB error code) value are reported and the load ends.
Warning conditions indicated by VSAM OPEN are reported by SQL*Loader but do not end processing.
VSAM clusters containing invalid control intervals (that is, lacking valid CIDF/RDF fields) cannot be processed by SQL*Loader.
If a VSAM GET request fails, then the return, component, and RPL feedback codes are reported and the load ends.
If the CLOSE of a VSAM cluster fails, then the return code and ACBERFL value are reported, but the already completed load is unaffected.

Return and related codes from OS/390 dynamic allocation and VSAM request macroinstructions are documented in the appropriate IBM documentation.

BAD and DISCARD File Considerations with VSAM Input

When an input record to SQL*Loader encounters an Oracle error during insertion or is not selected for insertion due to WHEN criteria, it is written to the BAD or DSC file, respectively . These files are normally identical to the input data file in structure and format.

When a VSAM file is used as input to SQL*Loader, the BAD and DSC files remain non-VSAM in structure (as they are with non-VSAM input). This eliminates the need to DEFINE additional VSAM files each time SQL*Loader is used with VSAM input.

With VSAM input, SQL*Loader normally requires the BAD and DSC files to use RECFM=V or VB and LRECL=n, where n is greater than or equal to the maximum record length of the input VSAM file . When the input VSAM file is known to have all records the same length (such as in an RRDS), you can use RECFM=F or FB and LRECL=x, where x is the exact length of the input VSAM records. Use caution when using this format for non-RRDS clusters, however, because VSAM has no mechanism to ensure all records in a KSDS cluster or ESDS cluster are actually the same size.

SQL*Loader Direct Path

The SQL*Loader direct path (DIRECT option) has significant performance improvements for many data load applications . Some of the performance improvements include reduced CPU usage and reduced time to perform a data load. Refer to Oracle9i Database Utilities for a complete description of the SQL*Loader direct path.

Performance

When you run SQL*Loader with the DIRECT option set to TRUE, performance is significantly influenced by the number of QSAM buffers available for processing the input file . The number of buffers is controlled by the DCB BUFNO parameter value you specify when you allocate the data file. The default value is five if you do not specify a BUFNO value. You can dramatically increase the data load rate in DIRECT mode by increasing the number of buffers to 100 or more.

Tests show that 200 buffers can increase the data load rate by a factor of three or more over the data load rate with the default number of buffers. SQL*Loader performance does not appear to improve with more than 200 buffers allocated for the input file.

Increase the number of buffers by providing a BUFNO value when the input file is allocated. For example, if you are running SQL*Loader as a batch job, then provide 100 buffers for the input file by allocating the file with the DCB=BUFNO=100 parameter as shown in the following example:

//DATA  DD  DSN=input.file.name,DISP=SHR,DCB=BUFNO=100

If you are running SQL*Loader from TSO, then provide 100 buffers for the input file by adding the BUFNO parameter to the ALLOC command that allocates the file. For example:

ALLOC FILE(DATA) DA('input.file.name') SHR BUFNO(100)

Ensure the region available to SQL*Loader includes enough memory to hold the extra buffers . For example, a file with a blocksize of 23,476 allocated with 100 input buffers requires 2,347,600 bytes of memory for the buffers. You might need to increase the region parameter value to 4M or more to run SQL*Loader with many buffers.

With 200 input file buffers available, SQL*Loader running with the DIRECT set to the TRUE option can use more than 90% of one central processor, and attain data load rates of over 250K per second on some types of processors . If you do not want SQL*Loader to use this much CPU, then reduce the data load rate by reducing the BUFNO value for the input file to 20 or less.

Each SQL*Loader session runs as a single OS/390 task and does not use more than one central processor in a multiprocessor complex. For example, the maximum amount of CPU a single SQL*Loader session can use in a four-way processor complex is 25 percent.

To exploit more than one processor in a multiprocessor complex, use multiple SQL*Loader sessions as documented in Oracle9i Database Utilities.

6 SQL*Loader