clean database

rafidwh · Post by **rafidwh** » Mon Jan 02, 2006 3:54 am

I need to clean database for repeated testing the data,
I used two oci stage and a transformer and in the target oci i gave the update action as "clear the table and insert",when I tried to run the job,it shows in the director that it is running,but in the Datastage job the if we check the performance statistics it shows 0 rows populating nad the job still continue to run without deleting the records,

Best suggestions please.I know it is very silly ,but I need your utmost suggestions
Thanks in advance

kcshankar · Post by **kcshankar** » Mon Jan 02, 2006 4:02 am

Hi,
Clear Table then insert rows options will be slower because of transaction logging.Try Truncate table then insert rows options.

regards
kcs

ray.wurlod · Post by **ray.wurlod** » Mon Jan 02, 2006 5:49 am

I believe that "clear table" implies "DELETE FROM table;" which can be hugely slow if there are lots of referential integrity constraints. If these are made into a single transaction nothing at all will appear to occur until the COMMIT is transmitted.

chulett · Post by **chulett** » Mon Jan 02, 2006 8:27 am

I'd also be curious if your design is to 'clean' a single table? In other words, are you sourcing from the same table you are clearing?

I know it sounds silly, but I've seen people try to do that - they will only write back 'the records they want to keep' - hence the question. And if that is the case, suggest you break the job up into two jobs - one that lands the 'clean' data you want to keep to a flat file and a second that truncates/clears the table and then reloads it from the file.

And yes - 'clear' is a transactional 'delete all records' and as such it could take quite some time before anything appears to happen in the monitor.