Skip to Content
author's profile photo Former Member
Former Member

Full Pushdown help with Netezza

Greetings,

I have a dataflow and I'm troubleshooting why I can't get full pushdown. To simplify it, I've constructed a simple dataflow with a single source, query, and target. Source and target are in the same datastore (and in the same database on the database server). I've traced the problem to a DECODE statement:

DECODE(

STG_FC_CO.CO_NBR = '1234567890'

,'fred'

,'test'

)

With the DECODE statement, I get the following when I look at the Optimized SQL:

SELECT "STG_FC_CO"."CO_NBR"

FROM "DEV_E100557".."STG_FC_CO" "STG_FC_CO"

If I changed the mapping for the column from the DECODE statement to:

STG_FC_CO.CO_NBR

and then save, I get this when I look at Optimized SQL:

INSERT INTO "DEV_E100557".."MLH_PUSHDOWN_TEST" ( "EXCLUSION_REASON" )

SELECT "STG_FC_CO"."CO_NBR"

FROM "DEV_E100557".."STG_FC_CO" "STG_FC_CO"

I've tried lots of variations on the DECODE statement including using ifthenelse instead of decode, but I can't get full pushdown.

I'm using DS 4.1, Netezza 6. I've tried selecting different Netezza versions when setting up the datastore, but that didn't make a difference.

STG_FC_CO.CO_NBR is VARCHAR(10). The target field for the DECODE statement is VARCHAR(50).

Any suggestions about where I would look to resolve this?

Thanks,

Mike Hayes

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 07, 2013 at 12:48 AM

    Mike,

    What is STG_FC_CO.CO_NBR = '1234567890'? Are you trying to match this value to see if it exists?

    Arun

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      The contents of the DECODE statement aren't at issue. I've tried variations on the statement. I'm just trying to construct a simple DECODE statement that can be pushed down to the database through the datastore that I'm using. Simple DECODE statements like this have worked for me in the past with DS 4.0. I'm trying to figure out why they're not working in 4.1.

  • Posted on May 07, 2013 at 08:54 AM

    Hi Mike,

    As per my knowledge , function that have their correspondent in database can be pushdown to database,since i have tried for MSSQL server and i was able to pushdown.For "Decode function" ,we have "case" as correspondent in MSSQL server.

    So here i would like to say that please check do you have the same database correspondent for Netezza and if you don't find any correspondent then i think it can not be pushdown. 😊

    Regards,

    Shiva

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Mike,

      One small question, are you using only DECODE function in your dataflow or some other function also that can not be push down like Lookup or the transform like Table_Comaprision etc.

      As per my knowledge there are some function and transform available in BODS that can not be pushdown. So if there is no issue ,could you please explain your logic or the requirement,you have implemented in you Dataflow.

      Regards,

      Shiva Sahu

  • author's profile photo Former Member
    Former Member
    Posted on May 13, 2013 at 11:36 AM

    Hello Mike,

    DECODE push downs the operation but not nested if then else. If we add an audit point prior to an operation that is usually pushed down to the database server, performance might degrade because pushdown operations cannot occur after an audit point. I'm not pretty much sure of the functionality you are trying to implement. Try Data Transfer Transform for push down if it might help.

    Thanks,

    Yusuf.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 06:22 PM

    Do you have IfThenElse support enabled in your Netezza datastore?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.