Using DataStage Server Edition, oracle OCI Stage
When selecting how to update a table there are two options I'm interested in. Clear table then insert rows and Truncate table then insert rows.
I know the difference between the delete and truncate functions, but what I would like to know is how DataStage carries out the deletion process.
Does it Delete, Insert, Commit or Delete, Commit, Insert, Commit.
Basically what I want to know is if users will be able to query data as its being updated because if I trunc then load the table there will be a small period where the table will be empty and users' will be querying.
Cheers.
Difference between clear before and truncate before insert
Moderators: chulett, rschirm, roy
As you've asked the question, it does "Delete, Insert, Commit".
The delete is transactional and will be performed before any rows hit the target stage. They will be 'committed' with your first commit, so the exact behaviour will depend on what you have specified for Transaction Size.
For example, with a Transaction Size of 0 you'll have an 'all or nothing' load - users will either see all the new rows once the load succeeds or all the old rows if the load fails. With a truncate, the users will see an empty table if the load fails.
For a Transaction Size greater than 0, your users will lose all the original rows and start to see a portion of the new rows after the first commit.
The delete is transactional and will be performed before any rows hit the target stage. They will be 'committed' with your first commit, so the exact behaviour will depend on what you have specified for Transaction Size.
For example, with a Transaction Size of 0 you'll have an 'all or nothing' load - users will either see all the new rows once the load succeeds or all the old rows if the load fails. With a truncate, the users will see an empty table if the load fails.
For a Transaction Size greater than 0, your users will lose all the original rows and start to see a portion of the new rows after the first commit.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers