DSXchange

Hi,

I am trying to truncate and load a Oracle table using Upsert Strategy. The reason being that I need to capture rejected records after being.

So, I am using user-defined insert and update option and am giving the Update and then Insert option.

In Update, the query is TRUNCATE TABLE <<TABLE_NAME>>
In Insert, the query is INSERT into TABLE <<TABLE_NAME>> etc

When I am running the job, am getting the following error.

ociStgRostFF,1: Update prepare failed:
update is: TRUNCATE TABLE STG_ROST_LKP_FIELD_FORCE
sqlcode is: -54
esql complaint: ORA-00054: resource busy and acquire with NOWAIT specified


Please let me know how this can be resolved or is there any better way to implement this logic.

"Truncate and Load with rejected records captured"

Thanks

check this link...

http://www.shutdownabort.com/errors/ORA-00054.php

We know this error but we wanted to know why it is coming and to get rid of this error.
We want to perform truncate and load option in orcale stage.

We know this error but we wanted to know why it is coming and to get rid of this error.
We want to perform truncate and load option in oracle stage.

bikan wrote:We know this error but we wanted to know why it is coming and to get rid of this error.
We want to perform truncate and load option in orcale stage.

Try executing truncate from Sqlplus/Rapidsql.If there also same error is coming then contact your dba and get the session killed.

DBA would be able to see and kill the active locks on the table which is not getting truncated.

Regarding your technique at 'Truncate & Insert', you put the truncate statement at the Update query and the inset one at the insert query.
How can you guarantee that update query will be executed before the insert one? in other words, truncation before insertion talking into consideration the PK constraints?

Regards.

Hi,

I am doing same. I am taking upsert strategy and in that i have taken first update then insert and in update query i have written truncate statement.
but still facing the same problem.

Thanks

Hi,

I think you can do this.

If you are using a transformer in the job, you can divert one flow to the Oracle stage with delete query and with the constraint @INROWNUM=1 to that flow, so that the rows will be deleted (i mean truncated, ie., in this case deletion followed by auto commit since deletion is being handled by Datastage)

Thanks,
bhasan

Hi,

I think you can do this.

If you are using a transformer in the job, you can divert one flow to the Oracle stage with delete query and with the constraint @INROWNUM=1 to that flow, so that the rows will be deleted (i mean truncated, ie., in this case deletion followed by auto commit since deletion is being handled by Datastage)

Thanks,
bhasan

You can't do this... it doesn't even make any sense as there is a possibility of the TRUNCATE being executed for every row. And as you've seen, once the stage "starts" and the table gets to be "in use" the truncate fails. Do the truncate in the OPEN command of the stage.

And while the delete with a contraint of one row *could* be made to work, you'd have to work around it wanting to happen once on each node and it would be a transactional delete of all records and thus logged and (more than likely) very slow.

bikan wrote:Hi,

I am doing same. I am taking upsert strategy and in that i have taken first update then insert and in update query i have written truncate statement.
but still facing the same problem.

Thanks

Have you considered the option of truncate in a separate job that runs prior to your upsert job? in that case in your upsert job you may want to give a dummy update statement.

Hi,

I understand your requirement is Truncate and Load.
If my understanding is correct then select the following options in Target DB stage,

Write Method=Load
Write Mode=Truncate

Adding one more point to the above post:
Use Sequential file after Target BD stage with reject link.

Best way of doing it is truncate using a shell script, and just use dummy update in next job in your upsert, call both in your seq job.(Keeping in mind you have to capture the rejects).
Seq_job (Exe-command -->Job_activity).