Lesson checkpoint
In this lesson, you learned how to complete the following tasks:
- Run a utility from DB2I
- View the utility job results
- View the output data sets
For this lesson, suppose that you want to copy your data from the MY_EMP table into another table so that you can use that copy for testing purposes. In this lesson, you will use the UNLOAD utility to copy the data in the MY_EMP table into an output data set. Then, in the next lesson, you will load that data into another table.
The UNLOAD utility copies the data in a format that can be used by the LOAD utility. UNLOAD does not delete the data from the source object, so the original table remains unchanged. You can also request that UNLOAD generate a LOAD statement based on the data, which can save you the time of having to create the statement yourself.
Complete the prerequisites that are described in Module 2: Running DB2 utilities.
Procedure
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_EMP';The result contains the database name in the DBNAME column and the table space name in the TSNAME column. If more than one MY_EMP 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.
Menu RefList RefMode Utilities Workstation Help
Edit Entry Panel
Command ===>
More: +
ISPF Library:
Project . . .
Group . . . . . . . . . . . . .
Type . . . .
Member . . . (Blank or pattern for member selection list)
Other Partitioned, Sequential or VSAM Data Set, or z/OS UNIX file:
Name . . . . . 'USER.SRCLIB.TUTORIAL(MYUTIL)' +
Volume Serial . . (If not cataloged)
Workstation File:
File Name . .
Options
Initial Macro . . . . Confirm Cancel/Move/Replace
Profile Name . . . . . Mixed Mode
Format Name . . . . . Edit on Workstation
F1=Help F2=Split F3=Exit F7=Backward F8=Forward F9=Swap
F10=Actions F12=Cancel You can use the following example UNLOAD utility statement. Change the qualified table space name (DSN00006.MYREMP) to the values that you noted in step 2 and the table qualifier (ADMF002) to your user ID.
File Edit Edit_Settings Menu Utilities Compilers Test Help
EDIT USER.SRCLIB.TUTORIAL(MYUTIL) - 01.13 Columns 00001 00072
Command ===> Scroll ===> PAGE
****** ***************************** Top of Data ******************************
000100 UNLOAD TABLESPACE DSN00006.MYREMP
000200 FROM TABLE ADMF002.MY_EMP
****** **************************** Bottom of Data **************************** 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 ===> UNLOAD (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 ===> 'USER.SRCLIB.TUTORIAL(MYUTIL)'
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 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 ===> SYSPUNCH.MYEMP
F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE
F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE >>DSNU EXEC:
>> UNLOAD UTILITY REQUESTED WITH
>> CONTROL=NONE, EDIT=SPF, RESTART=NO,
>> INDSN=USER.SRCLIB.TUTORIAL(MYUTIL), RECDSN=TUTOR01.UNLOAD.MYEMP, PUNCHDSN
=TUTOR01.SYSPUNCH.MYEMP,
>> DISCDSN="OMITTED",LISTDSN="OMITTED", TEMPDSN="OMITTED",
>> COPYDSN=**NOT REQUIRED**, COPYDSN2=**NOT REQUIRED**,
>> RCPYDSN1=**NOT REQUIRED**, RCPYDSN2=**NOT REQUIRED**,
>> SYSTEM=DB2A, SUBMIT=NO, UID=TEMP,
>> UNIT=SYSDA, VOLUME="OMITTED", DB2I=YES,
>> LIB="NULL". /*@041*/
>> THE RESULTING JCL WILL BE WRITTEN TO DSNUUNL.CNTL
>>SPF EDITING FACILITY INVOKED TO EDIT DSNUUNL.CNTL
>> WHEN *** APPEAR, PLEASE PRESS ENTER
>> TO TERMINATE SPF:
>> PRESS PF3 - RETURN TO CLIST WITH CHANGES
>> PRESS PF4 - RETURN TO CLIST WITH CHANGES THEN
>> RETURN TO MAIN MENU
>> ENTER CANCEL - RETURN TO CLIST WITH NO CHANGES
***
If any error messages are issued, press Enter to return to the DB2 Utilities panel and correct the mistakes.
Otherwise, press Enter again to generate the utility job. The job is displayed in an EDIT panel.
File Edit Edit_Settings Menu Utilities Compilers Test Help
EDIT TUTOR01.DSNUUNL.CNTL Columns 00001 00072
Command ===> Scroll ===> PAGE
000016 // SPACE=(16384,(20,20),,,ROUND),
000017 // UNIT=SYSDA
000018 //DSNUPROC.SYSPUNCH DD DSN=TUTOR01.SYSPUNCH.MYEMP,
000019 // DISP=(MOD,CATLG),
000020 // SPACE=(16384,(20,20),,,ROUND),
000021 // UNIT=SYSDA
000022 //DSNUPROC.SYSIN DD *
000023 UNLOAD TABLESPACE DSN00006.MYREMP
000024 FROM TABLE TUTOR01.MY_EMP
000025 //
****** **************************** Bottom of Data **************************** File Edit Edit_Settings Menu Utilities Compilers Test Help
EDIT TUTOR01.DSNUUNL.CNTL Columns 00001 00072
Command ===> SUB Scroll ===> PAGE
****** ***************************** Top of Data ******************************
000001 //TUTOR01A 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/28/14 TIME: 14:43:14
000011 //*
000012 //**********************************************
000013 //*
000014 //DSNUPROC.SYSREC DD DSN=TUTOR01.UNLOAD.MYEMP,
000015 // DISP=(MOD,CATLG),
000016 // SPACE=(16384,(20,20),,,ROUND),
000017 // UNIT=SYSDA
F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up
F8=Down F9=Swap F10=Left F11=Right F12=Cancel If 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 TUTOR01A(JOB00064) SUBMITTED
*** EDIT TUTOR01.DSNUUNL.CNTL Columns 00001 00072
Command ===> TSO SDSF H Scroll ===> PAGE
****** ***************************** Top of Data ******************************
000001 //TUTOR01A JOB (ACCOUNT),'NAME'
000002 //* SDSF HELD OUTPUT DISPLAY ALL CLASSES LINES 2,049 LINE 1-5 (5)
COMMAND INPUT ===> SCROLL ===> PAGE
NP JOBNAME JobID Owner Prty C ODisp Dest Tot-Rec Tot-
TUTOR01 TSU00057 ADMF002 144 Z HOLD LOCAL 498
TUTOR01 TSU00059 ADMF002 144 Z HOLD LOCAL 419
TUTOR01 TSU00060 ADMF002 144 Z HOLD LOCAL 431
TUTOR01 TSU00061 ADMF002 144 Z HOLD LOCAL 425
S TUTOR01A JOB00064 ADMF002 144 Z HOLD LOCAL 276 SDSF OUTPUT DISPLAY ADMF002A JOB00064 DSID 2 LINE 0 COLUMNS 02- 81
COMMAND INPUT ===> SCROLL ===> PAGE
********************************* TOP OF DATA **********************************
J E S 2 J O B L O G -- S Y S T E M S T L 0 -- N O D
14.55.06 JOB00064 ---- MONDAY, 28 APR 2014 ----
14.55.06 JOB00064 IRR010I USERID ADMF002 IS ASSIGNED TO THIS JOB.
14.55.06 JOB00064 ICH70001I TUTOR01 LAST ACCESS AT 14:13:49 ON MONDAY, APRIL 2
14.55.06 JOB00064 $HASP373 TUTOR01A STARTED - INIT 8 - CLASS A - SYS STL0
14.55.08 JOB00064 SMF000I TUTOR01A DSNUPROC DSNUTILB 0000
14.55.08 JOB00064 $HASP395 TUTOR01A ENDED
------ JES2 JOB STATISTICS ------
28 APR 2014 JOB EXECUTION DATE
24 CARDS READ
276 SYSOUT PRINT RECORDS
0 SYSOUT PUNCH RECORDS
18 SYSOUT SPOOL KBYTES
0.03 MINUTES EXECUTION TIME
1 //TUTOR01A JOB (ACCOUNT),'NAME'
//*
//*
//*
2 //UTIL EXEC DSNUPROC,SYSTEM=DB2A,UID='TEMP',UTPROC=''
3 XXDSNUPROC PROC LIB='DB2A.DSNLOAD', You can type BOTTOM in the COMMAND INPUT line to jump to the bottom of the data and see the utility messages. You can also use PF11 to scroll right and PF10 to scroll left.
SDSF OUTPUT DISPLAY ADMF002A JOB00064 DSID 102 LINE 1 COLUMNS 02- 81
COMMAND INPUT ===> SCROLL ===> PAGE
DSNU000I 118 14:55:07.75 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = TEMP
DSNU1044I 118 14:55:07.90 DSNUGTIS - PROCESSING SYSIN AS EBCDIC
DSNU050I 118 14:55:08.03 DSNUGUTC - UNLOAD TABLESPACE DSN00006.MYREMP
DSNU650I -DB2A 118 14:55:08.20 DSNUUGMS - FROM TABLE TUTOR01.MY_EMP
DSNU253I 118 14:55:08.55 DSNUUNLD - UNLOAD PHASE STATISTICS - NUMBER OF RECOR
DSNU252I 118 14:55:08.55 DSNUUNLD - UNLOAD PHASE STATISTICS - NUMBER OF RECOR
DSN00006.MYREMP
DSNU250I 118 14:55:08.55 DSNUUNLD - UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00
DSNU010I 118 14:55:08.62 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETUR
******************************** BOTTOM OF DATA ******************************** Menu RefList RefMode Utilities Help
Data Set List Utility
Option ===>
More: +
blank Display data set list P Print data set list
V Display VTOC information PV Print VTOC information
Enter one or both of the parameters below:
Dsname Level . . . TUTOR01
Volume serial . .
Data set list options
Initial View Enter "/" to select option
1 1. Volume / Confirm Data Set Delete
2. Space / Confirm Member Delete
3. Attrib / Include Additional Qualifiers
4. Total / Display Catalog Name
Display Total Tracks
Prefix Dsname Level
When the data set list is displayed, enter either:
F1=Help F2=Split F3=Exit F7=Backward F8=Forward F9=Swap
F10=Actions F12=Cancel
DSLIST - Data Sets Matching ADMF002 Row 1 of 5
Command ===> Scroll ===> PAGE
Command - Enter "/" to select action Message Volume
-------------------------------------------------------------------------------
TUTOR01.DSNUUNL.CNTL SCR03
TUTOR01.MYOUT SCR03
TUTOR01.SPFLOG1.LIST SCR03
TUTOR01.SYSPUNCH.MYEMP SCR03
TUTOR01.UNLOAD.MYEMP SCR03
***************************** End of Data Set list ****************************You can look at this data set to confirm that it contains the expected data. If you do so, you might notice some unreadable characters. Such characters are expected. They represent items such as null-indicator bytes and numeric fields in internal formats.
DSLIST - Data Sets Matching ADMF002 Row 1 of 5
Command ===> Scroll ===> PAGE
Command - Enter "/" to select action Message Volume
-------------------------------------------------------------------------------
TUTOR01.DSNUUNL.CNTL SCR03
TUTOR01.MYOUT SCR03
TUTOR01.SPFLOG1.LIST SCR03
E TUTOR01.SYSPUNCH.MYEMP SCR03
TUTOR01.UNLOAD.MYEMP SCR03
***************************** End of Data Set list **************************** 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_EMP"
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 In this lesson, you learned how to complete the following tasks: