Oracle connector Stage - Delete vs Truncate

[cdp]

Hi,

I have a job with an Oracle stage, that is just used to empty a table:



As you can see, we ask DataStage to run a truncate statement first then a delete * statement on the same table.

But from time to time, the delete statement is executed before the truncate statement has released the lock on the table resulting in an abort (ORA-00054)


I've just noticed that if I only want to run a truncate statement with an Oracle Connector stage, I can only to do it using PL/SQL:




But of course I can also use a DELETE statement:



I usually prefer to go for the more efficient truncate statement, but in this case I would be OK with the delete statement (table has only about 2000 records to remove everyday)

I just wanted to know if you guys would have a preferred method and why.

Thank you !

[SURA]

Sorry , can't see any image

we ask DataStage to run a truncate statement first then a delete * statement on the same table.

Why need a delete statement after the table has been truncated?

Is that means truncate before each load, then Insert / Delete / Update?

Truncate command can be executed in different ways.
1. Truncate load.
2. Run before and after SQL.
3. Proc
4. Keep the command in a file and execute in command line......

It looks like, your concern is before the truncate job finish, the Delete job is getting triggered.

If that is the case, need more detail.

[chulett]

Concur - why? One or the other, zero need for both.

[cdp]

No need to do both indeed.
I will go for the delete statement if there's no difference then

I think the previous developer just got a bit confused. Probably selected the delete method but at the "Table action" step he had to chose something, so preferred "truncate" over "append"...
Or he just wanted to do a truncate only, but could not figure out how to do it.

And I would agree, this menu is a bit confusing...
- It appears the only way to run a truncate statement ONLY is to use PL/SQL (not the option you would expect to have to use for this, especially if you are not too familiar with the PL/SQL syntax...)
- Why give the possibility to have Table Action 'truncate' with write mode = delete, especially when it appears that the delete statement does not always wait for the truncate statement to complete
- Why not give the possibility to just run a custom TRUNCATE statement without having to chose from write mode: delete/write/update only
- Or, like a lot of competitors do (eg. Talend), just add a Truncate box to check

Anyway, sorry, just had to complain a bit, lost half an hour figuring out how to run the truncate statement on its own :D

thanks

[SURA]

No need to do both indeed.
I will go for the delete statement if there's no difference then

Truncate is efficient than delete.

[chulett]

Right. No-one said there was no difference, just that's there's no need to do both.