Function 'add_months()' can not be pushed down to DB

kfyme · Principal Member Joined: 10 Jul 2007 Posts: 115

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 !

Werner Daehn

It is pushed down

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.

kfyme · Principal Member Joined: 10 Jul 2007 Posts: 115

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?

Werner Daehn

First, check the repo table with my sql statement. Then send the ATL.

kfyme · Principal Member Joined: 10 Jul 2007 Posts: 115

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

my DI repo was created successfully.

Werner Daehn

Agree

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

kfyme · Principal Member Joined: 10 Jul 2007 Posts: 115

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'?

Werner Daehn · Last edited by Werner Daehn on Thu Apr 10, 2008 2:03 am, edited 1 time in total

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.

kfyme · Principal Member Joined: 10 Jul 2007 Posts: 115

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

Werner Daehn

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.

kfyme · Principal Member Joined: 10 Jul 2007 Posts: 115

OK, thanks Werner !

jerome_dal

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

eganjp

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.

ganeshxp

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

eganjp

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.