July 2003 Technical Tip – DB2's LOAD Utility

July 2003 Technical Tip – DB2's LOAD Utility

The tables used in our DB2 courses tend to be very small, with ten to twenty rows per table. Under these conditions, it is appropriate to use the INSERT command to add rows to a table. For example,

INSERT INTO BQ01.EMPL VALUES
  ('211', 'DAYLEY', 'BRIAN', 'A', 6.00, 'H');

The problem with the INSERT command is that it can only insert one row at a time. Clearly, this is not realistic for most production databases. But there is an alternative: the LOAD utility. This utility allows you to specify a sequential data set and its layout, then the records of that data set are inserted as rows in the table.

For example, given the following data set named BQ01.TRAINING.DATA(EMPL):

----+----1----+----2----+----3
211DAYLEY    BRIAN     A00600H
270DAYLEY    CINDY     B30000S
601STEELE    DOUG      A00500H
719ALFORD    BRIAN     B00450H
828GOYAK     DEBBIE    A32000S
857ALFORD    MARK      B00500H

the following JCL could be used to load the data into DB2 table BQ01.EMPL:

//jobcard
//UTIL EXEC DSNUPROC,SYSTEM=DB2A,UID='TEMP',UTPROC=''
//DSNUPROC.SYSREC DD DSN=BQ01.TRAINING.DATA(EMPL),DISP=SHR
//DSNUPROC.SYSIN  DD *
LOAD DATA
     RESUME YES
     INTO TABLE BQ01.EMPL
     ( ENUM    POSITION(1)   CHAR(3)     ,
       LNAME   POSITION(4)   CHAR(10)    ,
       FNAME   POSITION(14)  CHAR(10)    ,
       DEPT    POSITION(24)  CHAR(1)     ,
       RATE    POSITION(25)  DECIMAL EXTERNAL (5,2),
       TYPE    POSITION(30)  CHAR(1) )

Notes

The DB2 Utilities panel will generate the JCL for you.
SYSTEM=DB2A identifies the DB2A as the DB2 subsystem id (SSID).
RESUME YES means records should be appended to existing rows.

For more information, check the web. A Google® search on "DB2 LOAD UTILITY" will provide you with plenty of information.

This article was written in response to a question from a client. If you have a question, please feel free to call us.