Truncate or Recreate in DB2

rasi · Post by **rasi** » Wed May 07, 2003 3:15 am

Hi,

What is the best thing in db2 while doing full refresh of a table. Is it better to drop the table and recreate it using datastage or simply truncate the table and insert records.

Thanks
Rasi

ray.wurlod · Post by **ray.wurlod** » Wed May 07, 2003 3:24 pm

DataStage doesn't care, and it doesn't really make any functional difference. Your DB2 DBA may prefer the TRUNCATE option, since this involves fewer difficulties with spaces, privileges and so on. You also need to consider these alternatives if the table has any index associated with it.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

chulett · Post by **chulett** » Wed May 07, 2003 3:43 pm

I'm not sure when it would ever be preferable to drop and rebuild a table rather than truncate it, just for a refresh. Perhaps if the size of your table was varying wildly from load to load you might consider dropping the table, if it can then be recreated intelligently based on the needs of the current load. But as Ray pointed out, any associated objects (like indexes) would get dropped and would need to be rebuilt as well. Truncate should be fine, I would think.

Don't know about DB2, but (as an FYI) Oracle has an option in the truncate to either reuse or drop the storage associated with the table. You'd have to use custom sql to take advantage of that, I believe.

You may also have to worry about privledges on the table (which is why 'clear table' exists), but if you've got permissions to drop and rebuild the table, you can certainly truncate it. [:D]

-craig