The most simple and most secure way to delete all rows in any DB2 table is by issuing the SQL statement DELETE FROM <table>. As data security is top priority this operation will take some time unless the underlying tablespace is segmented. Are you in a position where the DB2 table has its own tablespace, you can use the LOAD utility to delete the rows.

Normally it is a good idea to use LOAD when the table contains many rows and is nonsegmented. Depending on row length my assumption is that between 50.000 and 500.000 rows and more will justify the need to use LOAD instead of DELETE. The following job step shows how to do it:

//DELETE   EXEC PGM=DSNUTILB,REGION=6M,
//         PARM='DB2,LOAD.EMPTY'
//SYSIN    DD *
   LOAD LOG NO REPLACE NOCOPYPEND REUSE
   ENFORCE NO SORTDEVT SYSDA
   INTO TABLE MY.TABLE
/*
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSREC   DD DUMMY
//SYSUT1   DD UNIT=VIO,SPACE=(TRK,(1,1))
//SORTOUT  DD UNIT=VIO,SPACE=(TRK,(1,1))

This step removes all rows from all tables in the tablespace where MY.TABLE resides. This is why this method must be used with care. If the tablespace is partitioned, all partitions are emptied. Option REUSE are a version 6 feature and I believe NOCOPYPEND is a version 7 feature, but for some reason REUSE can be used in version 5 and NOCOPYPEND in version 6, but it works only if the right PTF's has been installed. REUSE is highly recommended, because normal DELETE/DEFINE of the underlying VSAM datasets are skipped thereby improving performance. Instead DB2 resets the VSAM dataset. NOCOPYPEND prevents the DB2 tablespace from ending in COPYPENDING status.

You can use the above method on a single partition in a partitioned tablespace. This is an improvement of the tip in week 34, but only when all rows has to be deleted in the partition.If you don't use option NOCOPYPEND the tablespace will be in COPYPENDING status after the LOAD. Then you will have to remove the COPYPENDING status (recommended in test environments only) or you must take an IMAGECOPY (recommended action in production environments). If the table participates in any kind of referential integrity (RI), then some other tables probably has ended in the CHECKPENDING status, and you will have to do something about that.