Analysing Pushdown

[Former Member]

I wonder if anyone can explain to me just HOW Data Services will decide to pushdown a certain function to the database. I thought I knew but I'm now not so sure. I thought that it was ALL specified through the AL_FUNCINFO table but I now believe that not to be the case as I have been looking into the Date_Diff function which seems to work without issue in SQLAnywhere yet not in SAP IQ. It was at this point that the mystery seemed to take a turn for the worse as upon investigating the AL_FUNCINFO table in my repository as to why this might be the case there seems to  only 1 record for the date_diff function which is for the engine itself and referfences a 'db_func' field called date_diff (not surprisingly).

Yet... If I use a date_diff expression against SQL Anywhere it knows to use the DateDiff function in the sql for pushdown

How on earth is it making this assumption given that there is no record within the data-table?  Is there another table I should be looking at too?

[virginia_hagen]

I reached out to the owners of the note and they have released it again.  This should provide insight to what is a push down operation and what is not.  Please take a look at the note that was mentioned and see if it helps provide the information that you need. 

Note 221730 - SAP Data Services push down operators, functions and transforms. 
 

[vnovozhilov]

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_perf_opt_en.pdf

5.1 Push-down operations

+

NOTE 2212730 - SAP Data Services push-down operators, functions, and transforms.

The latter is currently 'In Progress', therefore you won't see it until released by processor.

Thank you,

Viacheslav.

[Former Member]

This doesn't answer the question at all... And I cannot see the anything to do with note 221730. I get an error message saying doc is released.

[vnovozhilov]

As I stated above the note is currently not released to customers, thus you won't be able to see it until processor publishes it. The note is maintained by DEV and aimed to help understanding which functions can be pushed down to database and which won't be.

I very much doubt that you will get any official response on pushdown logic or a repository tables. AFAIK, historically that data has never been shared. That is why I have shared with you the knowledge I have got in hands.

Could other SCN Community members have to add to this you may have some further help.

Viacheslav.

[former_member187605]

Until further notice, AL_FUNCINFO contains the correct information for me.

By the way, date_diff is not pushed down to MS SQL and Sybase ASE Server. I don't have access to SQL Anywhere, I cannot verify.

[Former Member]

re Al_FuncInfo, That's what I thought! However looking at the al_funcinfo table for my repository I have no records other than the ACTA source record defined for the Date_Diff function and yet when I run a transformation with a date_diff in it against SQLAnywhere it definitely,definitely,definitely gets pushed down. I cant see how DS. can make this decision if what you have said is true?

ps. I agree that with SQL Server this does not get pushed down

SQL Generated for SQL Server

SELECT  "ORDERS"."Order Date"  ,  "ORDERS"."Ship Date"

FROM "DBO"."ORDERS" "ORDERS"

SQL Generated for SQL Anywhere

SELECT datediff(day,  "Orders"."Order Date" ,  "Orders"."Ship Date" )

FROM "dba"."Orders" "Orders"

[former_member187605]

I've said "until further notice". And that's now, you've proven the opposite .

I have never seen this before and have no explanation for this behavior either. , do you know more?

[former_member18162]

Sorry guys. No clue