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
clean database
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers