We have a job having multiple workflows running in parallel, almost 20, which in turn have 2 WF’s each within (sometimes connected sequentially) and these having DF’s within. The job never executes in parallel, The sequential run is successful though.
We have tried everything from changing the no. of processors from 8 to 25, to changing the DOP of the DF’s to 4 from the actual 0. But nothing turned out fruitful.
Appreciate any help!!
Additional info:
DB: Oracle
We have recently migrated the BODI 11.7 job to BODS 4.0
But anyway, in case you have any other option in solving the issue I’ve mentioned, do let me know. I don’t want to stick on to the fact that this patch is what can solve this.
“We have a job having multiple workflows running in parallel, almost 20, which in turn have 2 WF’s each within (sometimes connected sequentially) and these having DF’s within. The job never executes in parallel, The sequential run is successful though.”
Is it the case where you are trying to run two instances of the job in parallel? Or, are you trying to run the Workflows within the job in parallel?
Increasing the number of engines is one of the first things I would have recommended. But you already took care of that. Is it possible that you are overloadig the operating system in which the Job Server is running?
On the database side are you seeing the multiple requests from the Job Server? Is it possible that the database server is the holdup?
I’m trying to run the Workflows within the job in parallel.
The job is something like,
“there are 4 major WF’s connected in series, each of these WF’s have around 20 WF’s within, and each of these 20 WF’s have another 2 WF’s in them and then each have 1 or 2 DF’s and a whole load of scripts calling out functions, etc., etc. here and there.”
We also made sure that the OS was not overloaded, by assigning a separate job server to this job.
As usual in the begining all fingers were pointed to the DB, and they also made sure that there was no delay or holdup from their side.
Th ultimate solution after each try was the patch.
ADAPT01622848 - Just thought it might be this one though it has teradata instead of oracle, found it in the release notes. Like I’ve mentioned before, not sure how the job runs fine with this patch, maybe it isn’t listed like you said, might come out later, but it solves my purpose.
If you still got some more ideas, then do let me know.
Nah does not seem to be any connection between bulk loading Teradata and this case…
I just thought… could there be a table lock or that BODS is waiting on free resource at the db level? I had once a case where that happened with no error. BODS just kept waiting… and waiting etc.
There is a feature with Oracle tablespaces where if they fill up then the connection/ETL job will get suspended and wait for the DBA to add more space. The time it is allowed to wait is configurable by the DBA.
What we have found is that just because the DBA gets around to adding more space doesn’t mean that the job will continue as if nothing happened. Some times it will, some times it won’t.
HI,
we are processing many iDocs from SAP system (00s, 000s) using BODS - and its taking huge huge time to process them - Niether can we have them in parallel as the system hangs - I mean all RFCs hang.
Also, having 000s of iDocs sometimes results in huge queues and also the system fails. The manual mentions to disable ‘dispatch immediate’ and using higher SAP packets’. But is there a good work arond - with respect to tuning?
I’m not trying to run as many workflows as have been mentioned, but I’m in the process of building a job that will become the master sequence for the organisations data warehouse. We have an eight hour window in where we can run our batch and at the moment everything runs perfectly in sequence one after the other, taking a total of about 6 hours to run. Problem is that this is only the start of the project and there are still 100’s of WF’s to be added and they will take more than the 2hours I have left.
So I need to run in parallel. When I do this, I get one of two issues.
If I try and run 2 WF’s each with about 5 WF’s in them, the job will run about 7 of the WF’s and then it will just hang. There are no jobs running in the DB, and the Data Services does not complete.
If I try and run more than 2 WF’s at the same time, the job fails with a nasty message 'System Exception occured.
So I should also mention that I’m pretty new to DS and haven’t come across this sort of issue before.
I have heard people menion that the reason that this fails is that there are no enough connections available to the database to be able to run parallel workflows as each workflow consumes 1 (or more?) connections to the database and they are not released again until the entire workflow has completed. I don’t know what our maximum number of connections is, I need to understand if this is a DB or DS issue and if so how and where can I fix this.
I do know that if I create multiple jobs, I can run workflows in parallel, though each workflow within each job is in sequential order.
Please let me know if you resolved this and what you did. Failing that I will setup another thread to see if anybody else might be able to assist.
Workflows don’t consume database connections unless you have a script object that uses the sql() function.
The majority of the processing takes place in the Dataflow. It can consume a large number of database connections if you don’t design the Dataflow just right. Use the Display Optimized SQL to verify how many connections are being used and how much of the SQL is pushed down to the database.
Sorry yes I did realise that it is the DF’s that use the connections to the DB. Most of the DF’s will use a minimum of two connection, one for source, one for destination.
I do wonder if there is a limit to the number of connections that are available to DS. I have heard a conversation between people here that the limit is 15 connections in non-prod and prod is supposed to be unlimited. This concerns me as if this is true, there is no way to test in non-prod and I am certainly not going to test in production.
So I guess it sounds like the issue probably isn’t actually DS, but a number of connections to the DB. Is this a setting somewhere in DS that I can get the DS Admins to change or is this a DBA setting?
(BOB member since 2012-05-17)
(BOB member since 2007-09-12)
(BOB member since 2012-03-20)
