Difference between clear before and truncate before insert

[SPA_BI]

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.

[chulett]

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.

[SPA_BI]

Thank you very much you've answered my question.

If I want users to constantly be able to query the table I need to use the delete, rather than truncate function.

Cheers.