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

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Function 'add_months()' can not be pushed down to DB
1 members found this topic helpful
Goto page 1, 2  Next
 
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
kfyme
Principal Member
Principal Member



Joined: 10 Jul 2007

Posts: 115



PostPosted: Tue Apr 08, 2008 1:02 am 
Post subject: Function 'add_months()' can not be pushed down to DB

Like said in title, we use oracle 10g as the target DB and DI 11.7.3 is the used version, but the function 'add_months()' can not be pushed down to DB, What a pity !
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Tue Apr 08, 2008 1:53 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

It is pushed down cuss.gif

when I do a

select * from al_funcinfo where name = 'add_months'

I get two lines, one for the SOURCE=ACTA (the engine version) and one for SOURCE=Oracle version. And therefore it will get pushed down or should at least. I did not build a dataflow yet.

Is it possible there are some datatype conversions that actually prohibit the pushdown? e.g. add_months(string_column, 1) might not get pushed down as the implict conversion from string to date is done inside the engine....just guessing.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
kfyme
Principal Member
Principal Member



Joined: 10 Jul 2007

Posts: 115



PostPosted: Tue Apr 08, 2008 2:01 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

At first, I found this issue in a complex DF. And then I test the function add_months() in a very simple DF, then the case shows the result that this function can not be pushed down to DB, also there is no conversion conflict...

So perhaps that is a limitation or bug?
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Tue Apr 08, 2008 3:02 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

First, check the repo table with my sql statement. Then send the ATL.
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
kfyme
Principal Member
Principal Member



Joined: 10 Jul 2007

Posts: 115



PostPosted: Tue Apr 08, 2008 5:47 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

I execute that SQL, but just one record (SOURCE=ACTA) returned.

my DI repo was created successfully. crazy.gif
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Tue Apr 08, 2008 7:38 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Agree crazy.gif

That table is maintained by importing the rep_funcinfo.atl during the repo creation

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
kfyme
Principal Member
Principal Member



Joined: 10 Jul 2007

Posts: 115



PostPosted: Tue Apr 08, 2008 10:46 pm 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

So Werner, my repo is correct or not? I think the function add_months should be pushed down to Oracle.

If there are some problem in my repo, then how can I improve this? to update the table al_funcinfo in the repo? If so, can you send me the SQL for pushed-down to Oracle ? Or can you send me the new file 'rep_funcinfo.atl'?
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Wed Apr 09, 2008 12:13 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

First thing would be to go to C:\program files\bo\di\admin\repo\rep_funcinfo.atl and search for add_months. If it is there but not in the repo, the next question would be to look at any traces of the repo creation, log files whatsoever.
And if we a brave we just import that atl via Designer, restart Designer and see what happens then.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.


Last edited by Werner Daehn on Thu Apr 10, 2008 2:03 am, edited 1 time in total
Back to top
kfyme
Principal Member
Principal Member



Joined: 10 Jul 2007

Posts: 115



PostPosted: Thu Apr 10, 2008 1:10 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

As a testing, I inserted a records for function Add_months() for Oracle push-down option into the table al_funcinfo by imitating other functions exist in the tables, then it works, the function Add_months() was push-down to DB as I saw it appears in the optimized SQL.

According to your suggestion, I checked the file rep_funcinfo.atl, but I just find the folow info about add_months():
---------------------------------------------------
CREATE __AL_REPO_FUNCTION add_months ("Original date" DATETIME IN, "Months to add" INT IN)
SET(database_type = 'ACTA',
function_type = 'Date_Function',
DB_FunctionName = 'add_months',
Description = 'Adds a given number of months to a date.',
Parallelizable = 'yes',
External_name = 'add_months',
return_param_dep = '1',
return_datatype = 'null',
return_datatype_size = 'null' ,
param0 = 'Original Date.',
param1 = 'Number of months to add to the Original Date.' )
---------------------------------------------------

So, I think the file don't contains the information about pushing the function down to Oracle DB. But when I check on other functions like floor, then I found many groups of information for pushing down to different DBs like Oracle, DB2, Sybase IQ etc...
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Thu Apr 10, 2008 2:06 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

So we have our answer. It is a bug in your version that the add_months function is not pushed down. Your repository got created correctly, it is just that the rep_funcinfo.atl was not complete and that was corrected again in my version at least.
Your patched repo is fine, you won't have issues with that.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
kfyme
Principal Member
Principal Member



Joined: 10 Jul 2007

Posts: 115



PostPosted: Thu Apr 10, 2008 9:57 pm 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

OK, thanks Werner !
Back to top
jerome_dal
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 78
Location: Grenoble


flag
PostPosted: Thu Jan 12, 2012 8:41 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Hi,

I was expecting a translation of add_months() for SQL Server databases, for instance with DATEADD function.
I didn't find anything about that in the al_funcinfo table.

Is there any way to push down the add_months function on a SQL Server database?

Thanks and regards,
Jerome
Back to top
eganjp
Forum Addict
Forum Addict



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


flag
PostPosted: Fri Jan 13, 2012 5:55 pm 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

I'm mainly posting this to see what Werner says in response to the post above mine.

The question raised in the post above mine falls into the category of "Just because you can, doesn't mean you should." You can add rows to the repository table to add completely new functionality to Designer. The function in your database that you want to use in a Query transform doesn't show up in Designer? Add it to the al_funcinfo table. Assuming you know what you're doing and you get everything right it will work. But then you have to make that same change in every other local repository in development, qa and production. Miss just one repository and eventually someone will spend HOURS trying to figure out why something works in production/qa/dev but not in dev/production/qa.

Plus, when you bring up a new version of DS your tweak may not survive the upgrade (though I suspect upgrades will be OK).

In my opinion, the risk is not worth it. Better to put the screws to SAP to get the functionality formally added than to start hacking around in the repository tables.

_________________
Jim Egan
Mantis Technology Group-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaking at Business Objects User Conference 2013
-Session 0214:My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Session 0209: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
ganeshxp
Forum Addict
Forum Addict



Joined: 17 Jul 2008
ASUG Icon
Posts: 3656
Location: Denver, Colorado, USA


flag
PostPosted: Sat Jan 14, 2012 6:56 pm 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Yup. For example, in Teradata, there is no NVL() function. So whenever we just use a NVL() in our Query Transform, it won't do a Pushdown SQL. So we had to add it to AL_FUNCINFO for COALESCE
_________________
Cheers
Ganesh
Back to top
eganjp
Forum Addict
Forum Addict



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


flag
PostPosted: Sat Jan 14, 2012 8:02 pm 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Here's an idea to keep the repository straight...

At the beginning of your job check to see if the manually added function is in the repository. If it isn't then add it. This will work for a production situation but not so well for dev. Then again, the job may validate before it runs and if the function isn't there then you are SOL. In that case it would have to be a standalone job that adds the function.

_________________
Jim Egan
Mantis Technology Group-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaking at Business Objects User Conference 2013
-Session 0214:My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Session 0209: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 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
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.0492 seconds using 17 queries. (SQL 0.0033 Parse 0.0354 Other 0.0105)
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