The UNLOAD Utility

The UNLOAD Utility

Data in DB2 tables often needs to be moved or copied . For example, you may want to move data to a different DB2 subsystem, from a DB2 table to a sequential file for external processing, or possibly to another relational database system or platform (such as Oracle on Unix or SQL Server on Windows). Certain database schema changes require database objects to be dropped and re-created ”and when the object is dropped so is the data, so you need to unload the data before making database object changes. Or, maybe you just want to extract a subset of rows from a table for use as test data.

Prior to V7, DB2 provided two ways of unloading DB2 table data:

• Using SQL SELECT statements issued through DSNTIAUL , DSNTEP2 , or perhaps QMF or another application program you have written, or;

• Using the DB2 REORG utility and specifying UNLOAD ONLY , but this method allows you only to reload the data back into the same DB2 table.

These methods were too slow for large quantities of data and too inflexible for most production database requirements. So IBM finally offers a true utility with better speed than DSNTIAUL and much more flexibility than REORG UNLOAD ONLY . The UNLOAD utility performs many of the basic data movement tasks required by DB2 DBAs.

UNLOAD reads data from DB2 tables and externalizes the indicated data to a data set. More than one table or partition for each table space can be unloaded with a single invocation of the UNLOAD utility. The FROM TABLE statement is used to specify the table(s) to be unloaded. Refer to Listing 33.3 for sample unload JCL.

Listing 33.3. UNLOAD JCL

 //DB2JOBU  JOB (UTILITY),'DB2 UNLD',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*            DB2 UNLOAD UTILITY //* //**************************************************************** //* //UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='UNLDDATA',UTPROC=" //* //*  UTILITY WORK DATAETS //* //DSNUPROC.SYSPUNCH DD DSN=CAT.UNLOAD.SYSPUNCH,DISP=(NEW,CATLG,CATLG), //        UNIT=SYSDA,SPACE=(TRK,(1,1)) //DSNUPROC.SYSREC DD DSN=CAT.UNLOAD.EMP,DISP=(NEW,CATLG,CATLG), //        UNIT=SYSDA,SPACE=(TRK,(2,1)) //DSNUPROC.SYSPRINT DD SYSOUT=X //* //*  UTILITY CONTROL STATEMENTS //*      This UNLOAD statement unloads data from DSN8810.EMP //* //DSNUPROC.SYSIN    DD  *     UNLOAD TABLESPACE DSN8D81A.DSN8S81E     FROM TABLE DSN8810.EMP /* // 

The UNLOAD utility can unload data from an image copy data set instead of from the actual, live DB2 table space data set. This is accomplished using the FROMCOPY option. Unloading from an image copy can be beneficial because the live data will be unaffected ”meaning no locks are taken on the live data nor is any data read from the actual table space data set on disk. So, the performance and availability of applications running against the live data is unaffected by the concurrent unload operation. Of course, when unloading from an image copy the freshness of the data may be an issue. If subsequent updates, inserts , and deletes were processed against the table after the image copy was taken, those modifications will not be captured in the unloaded data because they were not made to the image copy data set ”only to the live data itself.

Let's look at an example. The following sample code unloads data from the image copy backup data set named CAT.FULLCOPY.SEQ.DATASET1 :

 UNLOAD TABLESPACE DSN8D81A.DSN8S81E     FROMCOPY DSN=CAT.FULLCOPY.SEQ.DATASET1     PUNCHDDN SYSPUNCH UNLDDN SYSREC 

The table owner is always permitted to use UNLOAD against a table. Otherwise, before a process or user can execute the UNLOAD utility, one of the following privileges must already exist or have been granted to the user or process:

• SELECT privilege on the table(s)

• DBADM authority for the database

• SYSADM authority

• SYSCTRL authority (only for DB2 Catalog tables)

UNLOAD Phases

The UNLOAD utility operates in three distinct phases as it reads data from DB2 tables and externalizes the indicated data to a data set:

UTILINIT	Sets up and initializes the UNLOAD utility.
UNLOAD	Reads the data (in a single pass) and unloads formatted records to sequential data sets. If UNLOAD is processing a table space or partition, DB2 takes internal commits to enable the UNLOAD process to be restarted in case operation should halt in this phase.
UTILTERM	Performs the final utility cleanup.

UNLOAD Termination and Restart Issues

If the UNLOAD utility is terminated using the TERM UTILITY command during the unload phase, the unloaded data is not erased. However, the output data will be incomplete and will remain that way unless you restart the UNLOAD or delete the data set.

When the source is one or more table spaces, you can restart the UNLOAD job at the partition level or at the table space level when data is unloaded from multiple table spaces using the LIST option. When you restart a terminated UNLOAD job, processing begins with the table spaces or partitions that had not yet been completed. For a table space or partitions that were being processed at termination, UNLOAD resets the output data sets and processes those table space or partitions again. When the source is one or more image copy data sets ( FROMCOPY or FROMCOPYDDN was specified), UNLOAD always starts processing from the beginning.

UNLOAD Locking Considerations

The level of concurrent activity against a table that is being unloaded depends on the parameters and options being used by the UNLOAD utility.

For an UNLOAD with SHRLEVEL REFERENCE , the write claim class is drained for the table space or partition being unloaded and concurrent SQL readers are allowed. When unloading with SHRLEVEL REFERENCE , the following utilities are incompatible when run on the same target database object:

• CHECK DATA DELETE YES

• LOAD ( SHRLEVEL NONE and SHRLEVEL CHANGE )

• RECOVER (all options)

• REORG TABLESPACE UNLOAD CONTINUE or PAUSE

• REPAIR LOCATE KEY , RID DELETE , or REPLACE

• REPAIR LOCATE TABLESPACE PAGE REPLACE

For an UNLOAD with SHRLEVEL CHANGE , a claim read is taken for the table space or partition being unloaded and concurrent SQL readers and writers are allowed. When unloading with SHRLEVEL CHANGE , the same utilities are incompatible with the exception of LOAD SHRLEVEL CHANGE , which is permitted.

For an UNLOAD of an image copy, a claim read is taken for the table space or partition being unloaded and concurrent SQL readers and writers are allowed. When unloading from an image copy, only the COPY - related utilities ( MERGECOPY and MODIFY RECOVERY ) are incompatible to be run concurrent with the UNLOAD .

UNLOAD Guidelines

When running the UNLOAD utility consider applying the following tips, tricks, and techniques.

Automatically Generate LOAD Control Statements

The UNLOAD utility can generate LOAD control statements that can be used by the LOAD utility for reloading the unloaded data back into a DB2 table. Use the PUNCHDDN option to specify a DD name for a data set (or template) to define one or more data set names to store these generated LOAD utility control statements.

Specify the Unload Data Encoding Scheme

The UNLOAD utility can specify the encoding scheme to use for the unloaded data. Four options are provided:

• EBCDIC ” the unloaded data will be in EBCDIC format.

• ASCII ” the unloaded data will be in ASCII format.

• UNICODE ” the unloaded data will be in Unicode format.

• CCSID ( n1 , n2 , n3 ) ” the unloaded data will be in the format specified by the three coded character set identifiers. The first integer (n1) specifies the CCSID for SBCS data. The second integer (n2) specifies the CCSID for mixed data. And the third integer (n3) specifies the CCSID for DBCS data. This option is not applied to data with a subtype of BIT .

Bit strings are not converted when using any of these four options ”they remain as bit strings. These four options are mutually exclusive and only one can be specified for each UNLOAD execution. If one of these options is not specified, the unloaded data will be formatted using the encoding scheme of the source data.

Handle Floating Point Data

When floating point data is being unloaded the FLOAT parameter should be used to identify the format in which unloaded floating point numbers should be stored. There are two options:

S390	Binary floating point data is written to unloaded records in System/390 hexadecimal floating point format. This is the default value.
IEEE	Binary floating point data is written in IEEE floating point format. This option requires OS/390 V2 R6 or higher with a G5 or better processor.

Use Parameters to Limit UNLOAD Data

Not every UNLOAD execution will require every row to be unloaded from the requested table(s). Numerous reasons and situations exist where it might make sense to unload only a subset of the total rows. Three options are provided, making it very flexible to specify a limited number of rows to unload: LIMIT , SAMPLE , and WHEN .

The LIMIT parameter can be used to limit the number of rows to be unloaded by the UNLOAD utility.

The SAMPLE parameter can be used to unload a sampling of the data in the table being unloaded instead of the entire table. The SAMPLE parameter takes a decimal condition that specifies the percentage of rows to be sampled. For example, the following parameter indicates that 15.055% of the rows in the table should be unloaded:

 SAMPLE 15.055 

The precision of the decimal condition is ddd.dddd where the value must be between 0 and 100 (inclusive). Sampling is applied per individual table.

CAUTION

If the rows from multiple tables are unloaded with sampling enabled, the referential integrity between the tables will likely be lost.

Finally, the WHEN parameter can be used to supply SQL predicates to the UNLOAD utility such that only certain data is unloaded. For example, the following condition will cause only rows where the SALARY is greater than 50,000 to be unloaded:

 WHEN (SALARY > 50000) 

Be Aware of the Impact of Multilevel Security on UNLOAD

If you use RACF with multilevel security to control access to DB2, be aware of the potential impact it can have on the UNLOAD utility. When unloading from tables protected using multilevel security with row-level granularity, you may not unload all of the rows depending on your security label. A row is unloaded only if your security label dominates the data's security label. If your security label does not dominate the data security label, the row is not unloaded ”and DB2 will not inform you of this. For more information about multilevel security and security labels refer back to Chapter 10, "DB2 Security and Authorization."