Lesson 2.2: Running the LOAD utility from DB2I
You can use the LOAD utility to load data into one or more tables. You can also insert rows by using SQL INSERT statements, but LOAD is a more efficient way to add a large number of rows.
Recall that in the previous lesson (Lesson 2.1: Running the UNLOAD utility from DB2I), you copied data from the MY_EMP table by using the UNLOAD utility. In this lesson, you will load that data into another table that you can use for testing. Although you can run the LOAD utility by using other methods, you will continue to use Db2 Interactive (DB2I) in this lesson.
Before you begin
Complete Lesson 2.1: Running the UNLOAD utility from DB2I, and open the TUTOR01.SYSPUNCH.MYEMP data set.
Procedure
Procedure
-
Edit the TUTOR01.SYSPUNCH.MYEMP data set and change the table name in the LOAD utility control
statement.
In the following example, the name has been changed to MY_EMP2.
File Edit Edit_Settings Menu Utilities Compilers Test Help EDIT TUTOR01.SYSPUNCH.MYEMP Columns 00001 00072 Command ===> Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000001 LOAD DATA INDDN SYSREC LOG NO RESUME YES 000002 EBCDIC CCSID(00037,00000,00000) 000003 INTO TABLE 000004 "TUTOR01". 000005 "MY_EMP2" 000006 WHEN(00001:00002) = X'0003' 000007 NUMRECS 43 000008 ( "EMPNO" 000009 POSITION( 00003:00008) CHAR(00006) 000010 , "FIRSTNME" 000011 POSITION( 00009:00022) VARCHAR 000012 , "MIDINIT" 000013 POSITION( 00023:00023) CHAR(00001) 000014 , "LASTNAME" 000015 POSITION( 00024:00040) VARCHAR 000016 , "WORKDEPT" 000017 POSITION( 00042:00044) CHAR(00003) F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up F8=Down F9=Swap F10=Left F11=Right F12=Cancel -
Press PF3 to exit. Then, navigate to SPUFI, and issue the
following CREATE TABLE statement to create the table that you specified in the LOAD
utility control statement:
CREATE TABLE MY_EMP2 LIKE MY_EMP;If you are not familiar with SPUFI or need a refresher on using SPUFI, see Lesson 1.2: Creating a table.A new empty table is created.
- From the DB2I Primary Option Menu, select option 8 to open the DB2 Utilities panel.
-
Specify some basic information about the utility that you want to run, and press
Enter:
- 3 UTIILITY
- The name of the utility that you want to run. This time, you want to run LOAD.
- 4 STATEMENT DATA SET
- The name of the data set or member that contains the utility control statement. In this case, you want to use the LOAD statement that the UNLOAD utility generated in the previous lesson. If you are following the conventions of this tutorial, that data set is named userID.SYSPUNCH.MYEMP, where userID is your Db2 authorization ID.
- 6 LISTDEF and TEMPLATE
- As in the previous lesson, you do not want to use the LISTDEF or TEMPLATE utilities. Type NO in these required fields.
DB2 UTILITIES SSID: DB2A ===> Select from the following: 1 FUNCTION ===> EDITJCL (SUBMIT job, EDITJCL, DISPLAY, TERMINATE) 2 JOB ID ===> TEMP (A unique job identifier string) 3 UTILITY ===> LOAD (CHECK DATA, CHECK INDEX, CHECK LOB, COPY, DIAGNOSE, LOAD, MERGE, MODIFY, QUIESCE, REBUILD, RECOVER, REORG INDEX, REORG LOB, REORG TABLESPACE, REPORT, REPAIR, RUNSTATS, STOSPACE, UNLOAD) 4 STATEMENT DATA SET ===> 'TUTOR01.SYSPUNCH.MYEMP' 5 RESTART ===> NO (NO, CURRENT, PHASE or PREVIEW) 6 LISTDEF? (YES|NO) ===> NO TEMPLATE? (YES|NO) ===> NO 7 LIB ==> (BLANK or DB2 Library name). * The data set names panel will be displayed when required by a utility. F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE -
In the DATA SET NAMES panel, specify the data sets to be used by
the utility, and generate the job:
- In field 1 RECDSN, specify the name of the data set that contains the data that you want to load. In this case, you want to load the data that you unloaded from the MY_EMP table in the previous lesson. The name of that unload data set is UNLOAD.MYEMP.
- Ensure that field 7 PUNCHDSN is blank.
- Press Enter twice to generate the utility job.
DATA SET NAMES SSID: ===> Enter data set name for LOAD, REORG TABLESPACE, or UNLOAD: 1 RECDSN ===> UNLOAD.MYEMP Enter data set name for LOAD or REORG TABLESPACE: 2 DISCDSN ===> Enter output data sets for local/current site for COPY, MERGECOPY, LOAD, or REORG TABLESPACE: 3 COPYDSN ===> 4 COPYDSN2 ===> Enter output data sets for recovery site for COPY, LOAD, or REORG TABLESPACE: 5 RCPYDSN1 ===> 6 RCPYDSN2 ===> Enter output data sets for REORG or UNLOAD 7 PUNCHDSN ===> F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVEThe generated job is displayed. Scroll down to see that it includes the LOAD statement from the TUTOR01.SYSPUNCH.MYEMP data set. -
To run the JCL utility job, type SUB in the
Command field and press Enter.
EDIT TUTOR01.DSNUUNL.CNTL Columns 00001 00072 Command ===> SUB Scroll ===> PAGE ****** ***************************** Top of Data ****************************** ==MSG> -Warning- The UNDO command is not available until you change ==MSG> your edit profile using the command RECOVERY ON. 000001 //ADMF002A JOB (ACCOUNT),'NAME' 000002 //* 000003 //* 000004 //* 000005 //UTIL EXEC DSNUPROC,SYSTEM=DB2A,UID='TEMP',UTPROC='' 000006 //* 000007 //********************************************** 000008 //* 000009 //* GENERATING JCL FOR THE UNLOAD UTILITY 000010 //* DATE: 04/25/14 TIME: 10:47:06 000011 //* 000012 //********************************************** 000013 //* 000014 //DSNUPROC.SYSREC DD DSN=ADMF002.UNLOAD.MYEMP, 000015 // DISP=(MOD,CATLG) F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up F8=Down F9=Swap F10=Left F11=Right F12=CancelIf you receive the message
ENTER JOBNAME CHARACTER(S) -, type any character and press Enter.A message indicates that the job was submitted and provides the job number. Note the job number for future use, and press Enter.JOB TUTOR01(JOB00161) SUBMITTED *** -
Review the output. Type TSO SDSF H in the
Command field and press Enter.
The SDSF HELD OUTPUT DISPLAY opens, which contains a list of held jobs.
-
Display the results of the job. In the NP column, type
S in the row for your job number, and press Enter.
The utility output is shown, and the utility return code DSNUTILB 0004 indicates that the utility completed with warning messages.
-
Determine the reason for the warning return code. Type BOTTOM in
the Command field, and press Enter. Press PF11 to scroll
right.
In the following example, you can see part of message DNSU380I, which indicates the table space was left in COPY PENDING status.
SDSF OUTPUT DISPLAY TUTOR01A JOB00161 DSID 102 LINE 6 COLUMNS 55- 134 COMMAND INPUT ===> SCROLL ===> PAGE E" POSITION(9:22) VARCHAR, " POSITION(23:23) CHAR(1), E" POSITION(24:40) VARCHAR, T" POSITION(42:44) CHAR(3) NULLIF(41)=X'FF', " POSITION(46:49) CHAR(4) NULLIF(45)=X'FF', E" POSITION(51:60) DATE EXTERNAL NULLIF(50)=X'FF', SITION(62:69) CHAR(8) NULLIF(61)=X'FF', " POSITION(71:72) SMALLINT NULLIF(70)=X'FF', SITION(74:74) CHAR(1) NULLIF(73)=X'FF', TE" POSITION(76:85) DATE EXTERNAL NULLIF(75)=X'FF', POSITION(87:91) DECIMAL NULLIF(86)=X'FF', POSITION(93:97) DECIMAL NULLIF(92)=X'FF', OSITION(99:103) DECIMAL NULLIF(98)=X'FF') WAS SPECIFIED FOR EMPTY TABLESPACE ASE STATISTICS - NUMBER OF RECORDS=43 FOR TABLE TUTOR01.MY_EMP2 ASE STATISTICS - TOTAL NUMBER OF RECORDS LOADED=43 FOR TABLESPACE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=43 COMPLETE, ELAPSED TIME=00:00:00 DSN00016.MYREMP2 PARTITION 1 IS IN COPY PENDING ION COMPLETE, HIGHEST RETURN CODE=4 ******************************** BOTTOM OF DATA ********************************In this case, the table space is in COPY-pending status because the LOAD statement had the LOG NO option. This option indicates that some logging activities are skipped during the LOAD process. Therefore, to ensure that the object is recoverable, Db2 requires an image copy to be taken or some equivalent action. You will resolve this COPY-pending status in the next lesson. -
Confirm that your new table was populated. Press PF3 to exit. Then, navigate to
SPUFI and issue a SELECT statement to query the table that you
loaded.
For example, the following query returns the number of rows in the table and confirms that the LOAD utility added rows to the table:
BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080 Command ===> Scroll ===> PAGE ********************************* Top of Data ********************************** ---------+---------+---------+---------+---------+---------+---------+---------+ 00010035 --CREATE TABLE MY_EMP2 LIKE MY_EMP; 00060036 SELECT COUNT (*) FROM MY_EMP2; 00061037 ---------+---------+---------+---------+---------+---------+---------+---------+ ---------+---------+---------+---------+---------+---------+---------+---------+ 42 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+---------+ 00062035 00063035 00064035 00070035 00070135 00071035 F1=Help F2=Split F3=Exit F5=Rfind F7=Up F8=Down F9=Swap F10=Left F11=Right F12=Cancel -
Determine the database name and table space name of the MY_EMP2 table. Note these
values for the next lesson.
For example, if you have SELECT privilege for the Db2 catalog tables, you might issue the following query to return the database name and table space name of your table:
SELECT NAME, CREATOR, DBNAME, TSNAME FROM SYSIBM.SYSTABLES WHERE NAME='MY_EMP2';The result contains the database name in the DBNAME column and the table space name in the TSNAME column. If more than one MY_EMP2 table exists, use the CREATOR column to identify the row for the table that you created.
If you do not have SELECT privilege for the Db2 catalog, ask a colleague who does have SELECT privilege for the Db2 catalog tables to get this information for you by querying the catalog.
What to do next
Lesson checkpoint
In this lesson, you learned how to complete the following tasks:
- Load data into an empty table by running the LOAD utility
- Check the utility output for any warnings