Oracle SQL*Loader is a batch utility for loading data into a target Oracle table.

DRMT supports SQL*Loader on AIX and on MVS.

---

See the Oracle7 Utilities Guide for information on how to format your data file and your control file. There are numerous options. For the most part, this information is applicable to both AIX or MVS. For MVS loading, ignore any references to "Direct Path". Direct path is not applicable with MVS because SQL*Net is used to connect the MVS client to the Oracle server (see diagram).

To execute SQL*Loader from MVS use JCL such as this:

 PVEDIT --- UADP.PAN.SAVLIB(JJLSQLLOAD)----------------------- COLUMNS 001 072
 COMMAND ===>                                                  SCROLL ===> CSR
 ****** ***************************** Top of Data ******************************
 000001 //TJJLSQLL JOB (3281,JJL,5,5),93952002,CLASS=0,MSGLEVEL=1,
 000002 //         MSGCLASS=T
 000003 //*
 000004 //STEP01   EXEC ORALDR,INPUT='T.JJL.XLATDATA',CTL='T.JJL.XLATCTL',
 000005 //           BADRECS='T.JJL.BADRECS',DSCRECS='T.JJL.DISCARDS'
 000006 //SYSPARM  DD
 000007 //         DD *
 000008   USERID=SCOTT/TIGER@UWT1
 ****** **************************** Bottom of Data ****************************

The INPUT and CTL datasets are required. The BADRECS and DSCRECS datasets are optional.

The userid/password do not appear in the held output. It is recommended that you not save the userid/password in your SAVLIB members.

SQL*Loader passes blocks of input records to the server for loading. For an average 100 byte row length, you might expect to load about 100 rows per second using SQL*Loader on MVS.
Below is an example of a control file (i.e. T.JJL.XLATCTL).

     
     LOAD DATA
     INFILE 't.jjl.xlatdata'
     REPLACE INTO TABLE pyuwba.emps_paid   
     (calc_id                   POSITION(01:09)   CHAR,
      person_id                 POSITION(10:17)   CHAR,
      multi                     POSITION(18:18)   CHAR,
      pay_date                  POSITION(19:26)   CHAR 
                        "TO_DATE(:pay_date, 'YYYYMMDD')",
      payroll_type              POSITION(27:28)   CHAR,
      ded_period                POSITION(29:34)   CHAR,
      calc_type                 POSITION(35:35)   CHAR,
      withhold_method           POSITION(36:36)   CHAR,
      dedn_method               POSITION(37:37)   CHAR)

NOTES:

calc_id, person_id, etc. are names of columns on the Oracle table emps_paid.

"POSITION" refers to the position on the flat file.

Note the use of TO_DATE to convert chars to Oracle dates.

Be careful when creating the control file. Sometimes it puts 6 digit line nums (Ex. 000002) on the right side of the file.

Never use "SORTED INDEXES (pk_emps_paid)" because it only works fordirect path loads which we cannot do.

last revised 11-18-99