If you are planning to use or already are using the new DB2 UNLOAD utility in version 7 then you can skip reading this tip. Otherwise I will tell you that in version 6 IBM added an option to the REORG utility called REORG UNLOAD EXTERNAL. This option is also available in version 5 through some PTF stuff, but I am not familiar with the correct PTF numbers. Somebody discovered a fatal error in the option (I discoved it, too, but three weeks later) that took down the whole DB2 subsystem. Therefore you have to ensure, that PTF UQ39687 is installed if you are using FIELDPROC's on VARCHAR columns, that you are going to unload. This should not be a problem, but I am telling you just in case. Now let me show you how to use the option:

//UNLOAD   EXEC PGM=DSNUTILB,REGION=6M,
//         PARM='DB2,UNLOAD.EXTERNAL'
//SYSIN    DD *
    REORG TABLESPACE MYDBNAME.MYTSNAME LOG NO SORTDATA
    UNLOAD EXTERNAL NOPAD FROM TABLE MY.TABLE
/*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DISP=SHR,DSN=MY.LOAD.STMT
//SYSREC   DD DISP=SHR,DSN=MY.LOAD.DATA
//UTPRINT  DD SYSOUT=*

This example unloads the table MY.TABLE in tablespace MYDBNAME.MYTSNAME. After completion of the UNLOAD, dataset MY.LOAD.STMT will hold a LOAD utility statement almost similar to the statement that DSNTIAUL produces. Dataset MY.LOAD.DATA contains a record pr. row in the unloaded table. Compared to the DSNTIAUL unload format these rows are prefixed with six bytes, where two of these bytes contains the OBID. In the generated LOAD utility statement the extra six bytes are taken care of and only records with the correct OBID will be loaded. Notice the use of the parameter NOPAD. This parameter prevents padding VARCHAR columns with blanks to their full length. This option can have a significant influence on the performance on the UNLOAD EXTERNAL, so use it.

I have used REORG UNLOAD EXTERNAL to unload about 300 production tables in less than an hour. Ten to fifteen of these tables contained more than 10 million rows and one of them contained 70 million rows. There were four parallel REORGS running at the same time. This is a FAST unload utility. It has a negative side, unfortunately. The unloaded tablespaces are read only while REORG UNLOAD EXTERNAL is running. Therefore you must be very careful if you already now consider a conversion from DSNTIAUL. It is of course possible to unload only one partition of a partitioned tablespace. You can also limit the number of rows unloaded by specifying a WHEN clause. The WHEN clause can operate only on data from the table being unloaded. This is due to the fact that REORG UNLOAD EXTERNAL bypasses normal SQL execution an goes directly to the data in the VSAM file. You can read a lot more about all this stuff in the Utility Guide and Reference manual.

Notice that the example may not work on your installation. Maybe you have to supply a STEPLIB DD-card and the DB2 subsystem name is probably also different. For sure you have to replace the MY-names with your own and make your own allocations of SYSPUNCH and SYSREC.