Lesson 2.1: Running the UNLOAD utility from DB2I
Db2 Interactive (DB2I) is a quick way to run a utility without knowing job control language (JCL). You can run most online utilities from the DB2 Utilities panel in DB2I.
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.
Before you begin
Complete the prerequisites that are described in Module 2: Running Db2 utilities.
Procedure
Procedure
- Open DB2I Primary Option Menu, and set the subsystem ID as described in steps 1–3 of Lesson 1.1: Querying data interactively.
-
Determine the database name and table space name of the MY_EMP table.
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.
-
Create another member in the USER.SRCLIB.TUTORIAL data set.
- From the ISPF/PDF Primary Option Menu, select option 2 EDIT.
-
In the Edit Entry panel, type the data set name and the new member name in
the Name field, and press Enter.
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=CancelThe Edit panel is displayed for the new member.
-
Type an UNLOAD utility control statement to unload the MY_EMP table. Then, press PF3 to
save and exit.
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 ****************************Tip: If you need help with the UNLOAD utility syntax, see Syntax and options of the UNLOAD control statement -
Navigate to the DB2I Primary Option Menu.
If you need help navigating to this panel, see Lesson 1.1: Querying data interactively.
- From the DB2I Primary Option Menu, select option 8 to display the DB2 Utilities panel.
-
Specify some basic information about the utility that you want to run, and press
Enter:
- 3 UTILITY
- The name of the utility that you want to run. In this case, you want to run UNLOAD.
- 4 STATEMENT DATA SET
- The name of the data set or member in which your utility control statement is stored. If you are using the same data set names as the tutorial, this value is 'USER.SRCLIB.TUTORIAL(MYUTIL)'. In this case, the value is enclosed in single quotation marks so that your user name is not appended to the front.
- 6 LISTDEF and TEMPLATE
- Use these fields to indicate whether you want to use lists (defined by the LISTDEF utility) and templates (defined by the TEMPLATE utility). These fields are blank by default, and you must specify a value for them. In this case, you do not want to use lists or templates, so type NO in these fields.
Tip: You can use the Tab key to navigate between 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 ===> 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 -
In the Data Set Names panel, specify the data sets to be used by
the utility, and press Enter.
- 1 RECDSN
- The name of the data set where you want to store the data that is unloaded from the table. Db2 allocates this data set for you.
- 7 PUNCHDSN
- The name of the data set where you want to store the generated LOAD statement. Db2 allocates this data set for you.
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=RETRIEVEThe following output is displayed to confirm your request to run the utility:>>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.
-
In the EDIT panel, press PF8 to scroll
down.
The generated JCL job contains the UNLOAD utility control statement that you created.
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 **************************** -
To run the JCL utility job, type SUB in the
Command field and press Enter.
SUB is the TSO SUBMIT command, which submits the data set or member to be processed as a batch job.
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=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 TUTOR01A(JOB00064) SUBMITTED *** -
To review the output, type TSO SDSF H in the
Command field.
EDIT TUTOR01.DSNUUNL.CNTL Columns 00001 00072 Command ===> TSO SDSF H Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000001 //TUTOR01A JOB (ACCOUNT),'NAME' 000002 //*The SDSF HELD OUTPUT DISPLAY opens, which contains a list of held jobs in z/OS System Display and Search Facility (SDSF).Tip: If you need help navigating between SDSF panels, see What is SDSF? (z/OS basic skills). -
Display the results of the job. In the NP column, type
S in the row for your job number, and press Enter.
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 276The utility output is displayed. In the output, the utility return code DSNUTILB 0000 indicates that the utility completed with no warnings or errors.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 ******************************** - Press PF3 until you return to the z/OS Primary Option Menu.
-
Confirm that unload data set was created.
- From ISPF/PDF Primary Option Menu, type 3.4 and press Enter to display the Data Set List Utility panel.
-
In the Dsname Level field, type your authorization ID,
ensure that the Include Additional Qualifiers field is
selected, and press Enter.
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=CancelA list of your data sets is displayed. The list includes the data set that your UNLOAD utility job created to contain the unloaded data. In this example, the unload data set is TUTOR01.UNLOAD.MYEMP.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.
-
Open the SYSPUNCH data set for editing. This data set contains the generated LOAD statement. In
the Command column, type E in the row for the TUTOR01.SYSPUNCH.MYEMP data set,
and press Enter twice.
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 ****************************An EDIT panel displays the contents of the SYSPUNCH data set.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
What to do next
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