BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: Upcoming Events: SAP BOBJ User Group DC: Nov 30.

BODS 4.2 job took very long to complete


 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
saieshranjan
Forum Associate
Forum Associate



Joined: 16 Feb 2011

Posts: 613



PostPosted: Mon Jul 17, 2017 3:23 am 
Post subject: BODS 4.2 job took very long to complete

Hi,

We have one job that is run weekly on weekends and two dataflows took 4.6 and 7.8 hours to process 94785525 and 16506039 records respectively but it takes less than 20 minutes normally.
Please can you advise what would be the cause. Which logs I should look at the detailed analysis.

In one EIMAdaptiveProcessingServer log, I see some error lines like:-


|5D462A3F656946EF8E32DC49AE817CE72|2017 07 09 04:57:52.793|+0900|Error| |>>|E| |aps_BODS.EIMAdaptiveProcessingServer|11208| 176|service builder-1| ||||||||||||||||||||||2017/07/09 4:57:52 com.sap.dataservices.rfcservice.server.RFCServiceLogger logTrace
|5D462A3F656946EF8E32DC49AE817CE73|2017 07 09 04:57:52.793|+0900|Error| |>>|E| |aps_BODS.EIMAdaptiveProcessingServer|11208| 176|service builder-1| ||||||||||||||||||||||���: DS.RFCService: Startup...
|5D462A3F656946EF8E32DC49AE817CE74|2017 07 09 04:57:52.793|+0900|Error| |>>|E| |aps_BODS.EIMAdaptiveProcessingServer|11208| 176|service builder-1| ||||||||||||||||||||||2017/07/09 4:57:52 com.sap.dataservices.dsjoblauncher.service.JobLauncherLogger logMessage


|5D462A3F656946EF8E32DC49AE817CE71f7|2017 07 09 04:57:58.902|+0900|Error| |==|E| |aps_BODS.EIMAdaptiveProcessingServer|11208| 241|pool-6-thread-1 | ||||||||||||||||||||com.acta.db.ActaConnectionPool||2017/07/09 4:57:58 com.acta.db.ActaConnectionPool setMaxLimit


Fatal: A server with type pjs is not found in the CMS BODS 01.comp.local: 6400 and the cluster @ BODS.comp.local: 6400 of DS.RF CService. Such a server may be down or it may be invalidated by the administrator. (FWM 01014)
Org.apache.axis2.AxisFault: Server with type pjs service is not found in CMS BODS.comp.local: 6400 with service DS.RFCService and cluster in cluster @ BODS.comp.local: 6400. Such a server may be down or it may be invalidated by the administrator. (FWM 01014)

But these does not help why it took long time for job to complete.
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4257
Location: Denver Area


flag
PostPosted: Tue Jul 18, 2017 12:37 pm 
Post subject: Re: BODS 4.2 job took very long to complete

I usually blame the DBA for running memory/cpu/network intensive operations on the weekend. icon_biggrin.gif

Seriously, check for that issue FIRST. It's the easiest thing to troubleshoot. A database backup running at the same time as an ETL job can certainly cause performance problems.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
cbonilla
Forum Member
Forum Member



Joined: 18 Jul 2017

Posts: 6



PostPosted: Tue Jul 18, 2017 3:08 pm 
Post subject: Re: BODS 4.2 job took very long to complete

Hi,

Also check the trace to see in what point of the flow take so long. Check the ping (in ms) between your sap ds and your DB.
Back to top
saieshranjan
Forum Associate
Forum Associate



Joined: 16 Feb 2011

Posts: 613



PostPosted: Wed Jul 19, 2017 1:36 am 
Post subject: Re: BODS 4.2 job took very long to complete

Hi Jim,

Thanks for the response

The database logs(SQL Server 2012) are huge and will continue to explore icon_mad.gif
There is some log in event viewer I could see .

Database access error. Reason: [Microsoft] [ODBC SQL Server Driver] [SQL Server] transaction (process ID 64) deadlocked with other processes on locked resources, this transaction was subject to that deadlock. Please re-execute the transaction. (FWB 00090).
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4257
Location: Denver Area


flag
PostPosted: Wed Jul 19, 2017 9:48 am 
Post subject: Re: BODS 4.2 job took very long to complete

I can see where a database looks like it is running slow but what's really happening is that it is waiting for locks to be resolved.

I've seen this in Oracle shops where a developer opens SQL Developer, runs a query and lets it sit there (causing a read lock). Then in another session someone tries to truncate the table. The truncate will just sit there waiting for the read lock to be released. That's the simple description.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
saieshranjan
Forum Associate
Forum Associate



Joined: 16 Feb 2011

Posts: 613



PostPosted: Thu Jul 20, 2017 4:12 am 
Post subject: Re: BODS 4.2 job took very long to complete

Thanks Jim thumbsup.gif
Back to top
saieshranjan
Forum Associate
Forum Associate



Joined: 16 Feb 2011

Posts: 613



PostPosted: Mon Jul 24, 2017 2:17 am 
Post subject: Re: BODS 4.2 job took very long to complete

I got to know that this error happened for quite sometime earlier too and there was no database backup activity done on weekends when the job took long time to complete.
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4257
Location: Denver Area


flag
PostPosted: Mon Jul 24, 2017 9:32 am 
Post subject: Re: BODS 4.2 job took very long to complete

Check with the sysadmin group to see if an O/S backup was being done. Have you looked at the timing of the error? Does it always happen at about the same time?
_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
saieshranjan
Forum Associate
Forum Associate



Joined: 16 Feb 2011

Posts: 613



PostPosted: Tue Jul 25, 2017 12:05 am 
Post subject: Re: BODS 4.2 job took very long to complete

Hi Jim,
No OS backup was also done on that day.
This issue happened just once and we need to find root cause analysis on this.
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1814
Location: Orlando, FL


flag
PostPosted: Mon Aug 21, 2017 9:27 pm 
Post subject: Re: BODS 4.2 job took very long to complete

Late reply, but root cause analysis is nearly impossible without incomplete data. Are you running logging (like Nagios) on the DB and DS servers? That would at least give you a chance.

- E

_________________
Please Search before you post.
___________________________
Ernie Phelps
Senior Manager
Protiviti
http://www.protiviti.com
Back to top
saieshranjan
Forum Associate
Forum Associate



Joined: 16 Feb 2011

Posts: 613



PostPosted: Fri Sep 01, 2017 12:06 am 
Post subject: Re: BODS 4.2 job took very long to complete

Sorry for late response.
I have had look at SQL Server execution plan and found following:-

Delayed DF2- INNER JOIN-COST 21%, CLUSTERED INDEX SCAN-COST 30%(TBBL_BO_HAL_MST_INF)-CLUSTERED INDEX SCAN-COST 16%-

Normal DF2- INDEX INSERT(TBL_BO_CHAIN_WEEK..)-27%,CLUSTERED INDEX INSERT(TBL_BO_CHAIN_WEEK..)-47%,



Delayed DF3-INNER JOIN-COST 35%,INNER JOIN-COST 16%,CLUSTERED INDEX SCAN-COST(TBL_SFA_ZBI1000)-26%

Normal DF3-INDEX INSERT(TBL_BO_CHAIN..) 27%, CLUSTERED INDEX INSERT(TBL_BO_CHAIN..) 47%,


If possible, please help.
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4257
Location: Denver Area


flag
PostPosted: Fri Sep 01, 2017 8:52 am 
Post subject: Re: BODS 4.2 job took very long to complete

Are you inserting into this table when it is empty or are you adding to it?

Tell me about the clustered index and the data going into it.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0227 seconds using 17 queries. (SQL 0.0027 Parse 0.0007 Other 0.0194)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo