DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
abhilashnair
Participant



Joined: 13 Oct 2006
Posts: 279

Points: 2740

Post Posted: Tue Oct 08, 2019 1:54 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
We have a scenario with two ETL jobs(Lets call them Job A and Job B) belonging to different subject areas with no dependency among each other, but operating on same table.

job A has the following statement

ALTER TABLE <TABLENAME> TRUNCATE PARTITION (PNAME) UPDATE INDEXES

job B does a simple select * from the same table above

Now the issue is this. On rare occasions they kind of overlap or run simultaneously causing the below error in second batch job (which has the select statement)

Error message: ORA-08103: object no longer exists.

We are aware that the root cause is because it is trying to select from the table when the partition is being truncated. We even were able to reproduce it in the TEST environment where we ran the job B and on a separate toad session we ran the Truncate Partition query at the same time

As I said this happens on only once or twice a month when these jobs run simultaneously. This is completely not in our control as we cant set dependencies between these 2. As i said they belong to different domains and we cant just make 1 job wait for another

In this case, is there something which can be done in the Job B , oracle connector stage SELECT query such that this error does not happen ? Something which will delay the Select thus not causing the job to abort?

Version Info:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 43026
Location: Denver, CO
Points: 222086

Post Posted: Tue Oct 08, 2019 6:15 am Reply with quote    Back to top    

I'm not aware of anything you can do inside the connector to "delay the select" when the object is in the midst of not existing. But you could certainly do something outside of it.

First thing that popped into my head was a simple semaphore / in-use mechanism that Job A sets and unsets and which Job B can check for. This could be a simple as an empty flat file on the server or a record in a table both can access. Job A creates it when it starts and removes (or updates) it when it completes. Something (sequence job?) checks for the presence of this blocking object and does a looping check until it is released before it allows Job B to start. You could also establish something similar for Job B so that Job A doesn't run while Job B is running.

We've used this successfully over the years (both ways) to avoid situations like you describe or to simply stop a new instance of a very long running process (several hundred jobs) from being started if for some reason the previous iteration hasn't finished yet.

_________________
-craig

Peaches come from a can, they were put there by a man
If I had my little way, I'd eat peaches every day
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2873
Location: USA
Points: 21812

Post Posted: Tue Oct 08, 2019 6:17 am Reply with quote    Back to top    

See this topic for an auto-retry workaround to a similar issue that you could use as well. This can be done at the sequence job level.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
abhilashnair
Participant



Joined: 13 Oct 2006
Posts: 279

Points: 2740

Post Posted: Thu Oct 10, 2019 1:59 am Reply with quote    Back to top    

Did a workaround setting up dependency between the 2 jobs
Rate this response:  
Not yet rated
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