In week 6/2010 I wrote a tip about the DB2 cross loader. In this tip I emphasized the possibility of using SQL statement as a part of the input stream for DSNUTILB. I have come to think about a very useful way of exploiting this feature. You can use it for simulating LOAD REPLACE of a table that is located in a tablespace containing more than one table. As some of you know and others of you have found out the hard way a LOAD REPLACE removes the data from all tables in the tablespace you are loading and not just the table you want to load.
Instead of using LOAD REPLACE you can use a SQL DELETE or in version 9 and later TRUNCATE followed by a LOAD RESUME YES as in this example:
Remember to replace SSID in the PARM= with the name of the DB2 system where table MY.TABLE is located. And you must of course also remember to specify which data from dataset MY.TABLE.DATA goes into which columns and with which datatype. Read more about that in Utility Guide and Reference. The link is to the DB2 version 8 manual. If the LOAD statement fails then data in MY.TABLE will be deleted. You cannot terminate the utility and beleive that the original data in MY.TABLE will be available. You just have to hurry up and get your data loaded as fast as possible. You may consider creating a backup before you run the above step. Then you can restore the backup if something goes wrong.
I have written several tips about deleting data fast in a DB2 table. The suggested delete will normally execute pretty quick because tablespaces containing more than one table are typically created as segmented tablespaces. A characteristic of segmented tablespaces is precisely that a DELETE of all rows in a table is carried out very quick unless the table is defined with DATA CAPTURE CHANGES. With DATA CAPTURE CHANGES all deleted rows are written to the log and that may take quite some time.