Truncate and Load in Oracle Stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
bikan
Premium Member
Premium Member
Posts: 128
Joined: Thu Jun 08, 2006 5:27 am

Truncate and Load in Oracle Stage

Post by bikan »

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
nikhilanshuman
Participant
Posts: 58
Joined: Tue Nov 17, 2009 3:38 am

Post by nikhilanshuman »

Nikhil
bikan
Premium Member
Premium Member
Posts: 128
Joined: Thu Jun 08, 2006 5:27 am

Post by bikan »

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.
bikan
Premium Member
Premium Member
Posts: 128
Joined: Thu Jun 08, 2006 5:27 am

Post by bikan »

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.
nikhilanshuman
Participant
Posts: 58
Joined: Tue Nov 17, 2009 3:38 am

Post by nikhilanshuman »

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.
Nikhil
sohasaid
Premium Member
Premium Member
Posts: 115
Joined: Tue May 20, 2008 3:02 am
Location: Cairo, Egypt

Post by sohasaid »

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.
bikan
Premium Member
Premium Member
Posts: 128
Joined: Thu Jun 08, 2006 5:27 am

Post by bikan »

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
bhasannew
Participant
Posts: 31
Joined: Wed Aug 06, 2008 10:54 pm

Post by bhasannew »

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
bhasannew
Participant
Posts: 31
Joined: Wed Aug 06, 2008 10:54 pm

Post by bhasannew »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Chandrathdsx
Participant
Posts: 59
Joined: Sat Jul 05, 2008 11:32 am

Post by Chandrathdsx »

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.
ReachKumar
Participant
Posts: 29
Joined: Wed Jan 06, 2010 7:18 am

Post by ReachKumar »

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
Regards,
Kumar
ReachKumar
Participant
Posts: 29
Joined: Wed Jan 06, 2010 7:18 am

Post by ReachKumar »

Adding one more point to the above post:
Use Sequential file after Target BD stage with reject link.
Regards,
Kumar
mekrreddy
Participant
Posts: 88
Joined: Wed Oct 08, 2008 11:12 am

Post by mekrreddy »

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).
Post Reply