Truncate and Load in Oracle Stage
Moderators: chulett, rschirm, roy
Truncate and Load in Oracle Stage
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
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
-
- Participant
- Posts: 58
- Joined: Tue Nov 17, 2009 3:38 am
-
- Participant
- Posts: 58
- Joined: Tue Nov 17, 2009 3:38 am
Try executing truncate from Sqlplus/Rapidsql.If there also same error is coming then contact your dba and get the session killed.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.
DBA would be able to see and kill the active locks on the table which is not getting truncated.
Nikhil
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.
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 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
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 59
- Joined: Sat Jul 05, 2008 11:32 am
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.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
-
- Participant
- Posts: 29
- Joined: Wed Jan 06, 2010 7:18 am
-
- Participant
- Posts: 29
- Joined: Wed Jan 06, 2010 7:18 am