DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
bikan



Group memberships:
Premium Members

Joined: 08 Jun 2006
Posts: 128

Points: 1241

Post Posted: Wed Feb 17, 2010 4:47 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Parallel
OS: Unix
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



Joined: 17 Nov 2009
Posts: 58

Points: 723

Post Posted: Wed Feb 17, 2010 5:05 am Reply with quote    Back to top    

check this link...

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

_________________
Nikhil
Rate this response:  
Not yet rated
bikan



Group memberships:
Premium Members

Joined: 08 Jun 2006
Posts: 128

Points: 1241

Post Posted: Wed Feb 17, 2010 5:17 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
bikan



Group memberships:
Premium Members

Joined: 08 Jun 2006
Posts: 128

Points: 1241

Post Posted: Wed Feb 17, 2010 5:19 am Reply with quote    Back to top    

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.
Rate this response:  
nikhilanshuman
Participant



Joined: 17 Nov 2009
Posts: 58

Points: 723

Post Posted: Wed Feb 17, 2010 5:20 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
sohasaid



Group memberships:
Premium Members

Joined: 20 May 2008
Posts: 115
Location: Cairo, Egypt
Points: 1476

Post Posted: Wed Feb 17, 2010 5:39 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
bikan



Group memberships:
Premium Members

Joined: 08 Jun 2006
Posts: 128

Points: 1241

Post Posted: Wed Feb 17, 2010 5:49 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
bhasannew
Participant



Joined: 06 Aug 2008
Posts: 31

Points: 222

Post Posted: Wed Feb 17, 2010 6:57 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
bhasannew
Participant



Joined: 06 Aug 2008
Posts: 31

Points: 222

Post Posted: Wed Feb 17, 2010 6:58 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Feb 17, 2010 8:58 am Reply with quote    Back to top    

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

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
Chandrathdsx
Participant



Joined: 05 Jul 2008
Posts: 59

Points: 617

Post Posted: Wed Feb 17, 2010 9:48 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ReachKumar
Participant



Joined: 06 Jan 2010
Posts: 29

Points: 172

Post Posted: Fri Feb 26, 2010 12:29 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
ReachKumar
Participant



Joined: 06 Jan 2010
Posts: 29

Points: 172

Post Posted: Fri Feb 26, 2010 12:36 am Reply with quote    Back to top    

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

_________________
Regards,
Kumar
Rate this response:  
Not yet rated
mekrreddy
Participant



Joined: 08 Oct 2008
Posts: 88

Points: 624

Post Posted: Fri Feb 26, 2010 12:40 am Reply with quote    Back to top    

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).
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours