Sample UNLOAD control statements

Use the sample control statements as models for developing your own UNLOAD control statements.

Example 1: Unloading all columns of specified rows

The control statement specifies that all columns of rows that meet the following criteria are to be unloaded from table DSN8810.EMP in table space DSN8D10A.DSN8S71E:

The value in the WORKDEPT column is D11.
The value in the SALARY column is greater than 25 000.

Figure 1. Example of unloading all columns of specified rows

//STEP1    EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'
//SYSREC   DD DSN=USERID.SMPLUNLD.SYSREC,
//         DISP=(NEW,CATLG,CATLG),
//         UNIT=SYSDA,SPACE=(TRK,(2,1)) 
//SYSPUNCH DD DSN=USERID.SMPLUNLD.SYSPUNCH,
//         DISP=(NEW,CATLG,CATLG), 
//         UNIT=SYSDA,SPACE=(TRK,(1,1))
//SYSPRINT DD SYSOUT=* 
//SYSIN    DD * 
  UNLOAD TABLESPACE DSN8D10A.DSN8S81E
    FROM TABLE DSN8A10.EMP 
    WHEN (WORKDEPT = 'D11' AND SALARY > 25000)

Example 2: Unloading specific columns by using a field specification list

The following control statement specifies that columns EMPNO, LASTNAME, and SALARY are to be unloaded, in that order, for all rows that meet the specified conditions. These conditions are specified in the WHEN clause and are the same as those conditions in example 1. The SALARY column is to be unloaded as type DECIMAL EXTERNAL. The NOPAD option indicates that variable-length fields are to be unloaded without any padding.

UNLOAD TABLESPACE DSN8D10A.DSN8S81E NOPAD
  FROM TABLE DSN8A10.EMP
    (EMPNO, LASTNAME, SALARY DECIMAL EXTERNAL)
  WHEN (WORKDEPT = 'D11' AND SALARY > 25000)

The output from this example might look similar to the following output:

000060@@STERN#   32250.00
000150@@ADAMSON#   25280.00
000200@@BROWN#   27740.00
000220@@LUTZ#   29840.00
200220@@JOHN#   29840.00

In this output:

'@@' before the last name represents the 2-byte binary field that contains the length of the VARCHAR field LASTNAME (for example, X'0005' for STERN).
'#' represents the NULL indicator byte for the nullable SALARY field.
Because the SALARY column is declared as DECIMAL (9,2) on the table, the default output length of the SALARY field is 11 (9 digits + sign + decimal point), not including the NULL indicator byte.
LASTNAME is unloaded as a variable-length field because the NOPAD option is specified.

Example 3: Unloading data from an image copy

The FROMCOPY option in the following control statement specifies that data is to be unloaded from a single image copy data set, JUKWU111.FCOPY1.STEP1.FCOPY1.

PUNCHDDN SYSPUNCH specifies that the UNLOAD utility is to generate LOAD utility control statements and write them to the data set that is defined by the SYSPUNCH DD statement; SYSPUNCH is the default. UNLDDN SYSREC specifies that the data is to be unloaded to the data set that is defined by the SYSREC DD statement; SYSREC is the default.

UNLOAD TABLESPACE DBKW1101.TPKW1101             
           FROMCOPY JUKWU111.FCOPY1.STEP1.FCOPY1    
           PUNCHDDN SYSPUNCH UNLDDN SYSREC

Example 4: Unloading a sample of rows and specifying a header.

The following control statement specifies that a sample of rows is to be unloaded from table ADMF001.TBKW1605. Unloading a sample of rows is useful for building a test system. The SAMPLE option indicates that 75% of the rows are to be sampled. The HEADER option indicates that the string 'sample' is to be used as the header field in the output file. The PUNCHDDN option indicates that UNLOAD is to generate LOAD utility control statements and write them to the SYSPUNCH data set, which is the default. UNLOAD specifies the header field as a criterion in the WHEN clause of these LOAD statements.

UNLOAD TABLESPACE DBKW1603.TPKW1603 
            PUNCHDDN SYSPUNCH UNLDDN SYSREC          
            FROM TABLE ADMF001.TBKW1605              
              HEADER CONST 'sample'                  
              SAMPLE 75

Example 5: Unloading data from two tables in a segmented table space

The following control statement specifies that data from table ADMF001.TBKW1504 and table ADMF001.TBKW1505 is to be unloaded from the segmented table space DBKW1502.TSKW1502. The PUNCHDDN option indicates that UNLOAD is to generate LOAD utility control statements and write them to the SYSPUNCH data set, which is the default. The UNLDDN option specifies that the data is to be unloaded to the data set that is defined by the SYSREC DD statement, which is also the default.

UNLOAD TABLESPACE DBKW1502.TSKW1502             
           PUNCHDDN SYSPUNCH UNLDDN SYSREC          
            FROM TABLE ADMF001.TBKW1504              
            FROM TABLE ADMF001.TBKW1505

Example 6: Unloading data in parallel from a partitioned table space

The UNLOAD control statement specifies that data from table TCRT.TTBL is to be unloaded to data sets that are defined by the UNLDDS template. These data sets are to be dynamically allocated and named according to the naming convention that is defined by the DSN option of the TEMPLATE utility control statement. This naming convention indicates that a data set is to be allocated for each table space partition.

Assume that table space TDB1.TSP1, which contains table TCRT.TTBL, has three partitions. Because the table space is partitioned and each partition is associated with an output data set that is defined by the UNLDDS template, the UNLOAD job runs in parallel in a multi-processor environment. The number of parallel tasks are determined by the number of available processors.

Figure 2. Example of unloading data in parallel from a partitioned table space

//STEP1    EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'
//SYSPUNCH DD DSN=USERID.SMPLUNLD.SYSPUNCH,
//         DISP=(NEW,CATLG,CATLG),
//         UNIT=SYSDA,SPACE=(TRK,(1,1))
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  TEMPLATE UNLDDS   DSN &USERID..SMPLUNLD.&TS..P&PART.
           UNIT SYSDA  DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
  UNLOAD TABLESPACE TDB1.TSP1
    UNLDDN UNLDDS
    FROM TABLE TCRT.TTBL

Assume that the user ID is USERID. This UNLOAD job creates the following three data sets to store the unloaded data:

USERID.SMPLUNLD.TSP1.P00001 ... contains rows from partition 1.
USERID.SMPLUNLD.TSP1.P00002 ... contains rows from partition 2.
USERID.SMPLUNLD.TSP1.P00003 ... contains rows from partition 3.

Example 7: Using a LISTDEF utility statement to specify partitions to unload

The UNLOAD control statement specifies that data that is included in the UNLDLIST list is to be unloaded. UNLDLIST is defined in the LISTDEF utility control statement and contains partitions one and three of table space TDB1.TSP1. The LIST option of the UNLOAD statement specifies that the UNLOAD utility is to use this list.

The data is to be unloaded to data sets that are defined by the UNLDDS template.

Figure 3. Example of using a LISTDEF utility statement to specify partitions to unload

//SAMPJOB  JOB ...
//STEP1    EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'
//SYSPUNCH DD DSN=USERID.SMPLUNLD.SYSPUNCH,
//         DISP=(NEW,CATLG,CATLG),
//         UNIT=SYSDA,SPACE=(TRK,(1,1))
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  LISTDEF  UNLDLIST
           INCLUDE  TABLESPACE TDB1.TSP1 PARTLEVEL(1)
           INCLUDE  TABLESPACE TDB1.TSP1 PARTLEVEL(3)
  TEMPLATE UNLDDS   DSN &USERID..SMPLUNLD.&TS..P&PART.
           UNIT SYSDA  DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
  UNLOAD LIST UNLDLIST              -- LIST name
    UNLDDN UNLDDS                   -- TEMPLATE name

Assume that the user ID is USERID. This UNLOAD job creates the following two data sets to store the unloaded data:

USERID.SMPLUNLD.TSP1.P00001 ... contains rows from partition 1.
USERID.SMPLUNLD.TSP1.P00003 ... contains rows from partition 3.

Example 8: Unloading multiple table spaces by using LISTDEF

The UNLOAD control statement specifies that data from multiple table spaces is to be unloaded. These table spaces are specified in the LISTDEF utility control statement. Assume that the database TDB1 contains two table spaces that can be expressed by the pattern-matching string 'TSP*', (for example, TSP1 and TSP2). These table spaces are both included in the list named UNLDLIST, which is defined in the LISTDEF statement. The LIST option of the UNLOAD statement specifies that the UNLOAD utility is to use this list.

The UNLDDN option specifies that the data is to be unloaded to data sets that are defined by the UNLDDS template. The PUNCHDDN option specifies that UNLOAD is to generate LOAD utility control statements and write them to the data sets that are defined by the PUNCHDS template.

Figure 4. Example of unloading multiple table spaces

//SAMPJOB  JOB ...
//STEP1    EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  LISTDEF  UNLDLIST
           INCLUDE  TABLESPACE TDB1.TSP*
  TEMPLATE UNLDDS   DSN &USERID..SMPLUNLD.&TS.
           UNIT SYSDA  DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
  TEMPLATE PUNCHDS  DSN &USERID..SMPLPUNC.&TS.
           UNIT SYSDA  DISP (NEW,CATLG,CATLG) SPACE (1,1) CYL
  UNLOAD LIST UNLDLIST
    PUNCHDDN PUNCHDS              -- TEMPLATE name
    UNLDDN   UNLDDS               -- TEMPLATE name

Assume that the user ID is USERID. This UNLOAD job creates the following two data sets to store the unloaded data:

USERID.SMPLUNLD.TSP1 ... contains rows from table space TDB1.TSP1.
USERID.SMPLUNLD.TSP2 ... contains rows from table space TDB1.TSP2.

Example 9: Unloading data into a delimited file.

The control statement specifies that data from the specified columns (RECID, CHAR7SBCS, CHAR7BIT, VCHAR20, VCHAR20SBCS, VCHAR20BIT) in table TBQB0501 is to be unloaded into a delimited file. This output format is indicated by the DELIMITED option. The POSITION(*) option indicates that each field in the output file is to start at the first byte after the last position of the previous field.

The column delimiter is specified by the COLDEL option as a semicolon (;), the character string delimiter is specified by the CHARDEL option as a pound sign (#), and the decimal point character is specified by the DECPT option as an exclamation point (!).

PUNCHDDN SYSPUNCH specifies that UNLOAD is to generate LOAD utility control statements and store them in the SYSPUNCH data set, which is the default. UNLDDN SYSREC indicates that the data is to be unloaded to the SYSREC data set, which is the default.

The EBCDIC option indicates that all output character data is to be in EBCDIC.

Figure 5. Example of unloading data into a delimited file.

//*                              
//STEP3    EXEC DSNUPROC,UID='JUQBU105.UNLD1',
//         UTPROC='',
//         SYSTEM='SSTR'
//UTPRINT  DD SYSOUT=*          
//SYSREC   DD DSN=JUQBU105.UNLD1.STEP3.TBQB0501,DISP=(MOD,DELETE,CATLG),
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) 
//SYSPUNCH DD DSN=JUQBU105.UNLD1.STEP3.SYSPUNCH
//         DISP=(MOD,CATLG,CATLG)
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)      
//SYSIN    DD*          
    UNLOAD TABLESPACE DBQB0501.TSQB0501       
         DELIMITED CHARDEL '#' COLDEL ';' DECPT '!'
         PUNCHDDN SYSPUNCH
         UNLDDN SYSREC EBCDIC                 
         FROM TABLE ADMF001.TBQB0501
          (RECID       POSITION(*) CHAR, 
            CHAR7SBCS   POSITION(*) CHAR,
           CHAR7SBIT   POSITION(*) CHAR(7),
           VCHAR20     POSITION(*) VARCHAR,
           VCHAR20SBCS POSITION(*) VARCHAR,     
           VCHAR20BIT  POSITION(*) VARCHAR) 
 /*

Example 10: Converting character data

For this example, assume that table DSN8810.DEMO_UNICODE contains character data in Unicode. The UNLOAD control statement specifies that the utility is to unload the data in this table as EBCDIC data.

Figure 6. Example of unloading Unicode table data into EBCDIC

UNLOAD 
  EBCDIC
  TABLESPACE DSN8D81E.DSN8S81U
  FROM TABLE DSN8810.DEMO_UNICODE

Example 11: Unloading LOB data to a file

The UNLOAD control statement specifies that the utility is to unload data from table DSN8910.EMP_PHOTO_RESUME into the data set that is identified by the SYSREC DD statement. Data in the EMPNO field is six bytes of character data, as indicated by the CHAR(6) option, and is unloaded directly into the SYSREC data set. Data in the RESUME column is CLOB data as indicated by the CLOBF option. This CLOB data is to be unloaded to the files identified by the LOBFRV template, which is defined in the preceding TEMPLATE statement. If these files do not already exist, DB2® creates them. The names of these files are stored in the SYSREC data set. The length of the file name to be stored in this data set can be up to 255 bytes as specified by the VARCHAR option.

Figure 7. Example of unloading LOB data into a file

TEMPLATE LOBFRV DSN 'UNLDTEST.&DB..&TS..RESUME'
                DSNTYPE(PDS) UNIT(SYSDA)

UNLOAD DATA
  FROM TABLE DSN8910.EMP_PHOTO_RESUME
  (EMPNO  CHAR(6),
   RESUME VARCHAR(255) CLOBF LOBFRV)
 SHRLEVEL CHANGE

Example 12: Unloading data from clone tables

The UNLOAD control statement specifies that the utility is to unload data from only clone tables in the specified table spaces. The PUNCHDDN option specifies that the SYSPUNCH data set is to receive the LOAD utility control statements that the UNLOAD utility generates.

UNLOAD TABLESPACE DBKQRE01.TPKQRE01      
        FROM TABLE ADMF001.TBKQRE01_CLONE 
        PUNCHDDN SYSPUNCH UNLDDN SYSREC   
        CLONE