DSXchange

Hi,

I have a SQL query which is nearly 360 lines,i am writing in DB2 Enterprise stage but it's accepting only 120 lines of SQL query.

So any body's idea about this?
It's urgent.


Thanks in advance
Beeram

There are many options

1) You could embed the query in a view and use the same DB2 read stage, just accessing the view instead of running the actual query.

2) Put the query into a stored procedure and return a result set. Not sure which version of PX you are using, but I think hte latest (7.5) has a stored procedure stage. We're using PX 7.1r2, so I have never tried this - I have seen a lot of postings about it, do a search on 'stored procedure' if you want to look into it.

3) why is so long? Is it because of inline transformations (renaming fields, doing conversions or calculations as part of the query, etc.) or are you just pulling that many fields? If you are doing ETL in your query, try pulling those out and doing the ETL in Transformers, modify's or other PX stages instead of within the query.

4) Create 2 database pulls that run simulataneously. First query pull the first half of the fields, 2nd query pulls the rest of the fields. Make sure both queries pull the unqiue keys so that the resulting datastreams can be joined back together within PX.

HTH.

Brad.

Hi Brad,

Sinece my Team decided to not to use Views/Procedure,i have option using splitting the job into two parts,this already i implimented.Other than this is there any way to do this.

This query is like all Case Statements,i have 360 Input columns so for each column i have condition and calculation,finally output will goes to 360 columns.

Instead of writing query in DB2 Stage can we pass a parameter?


Thanks
Beeram

You can put a query in a parameter, but you will probably run into a similar problem due to the length of the query. Off the top fo my head, I don't know what the max length of a parameter is but I'd be willing to bet that it is shorter than your query.

Another possibility, one that my project uses a lot, is to run the query from a script to load a work table. Then DataStage simply reads the table in its entirety.

Is there a reason why you can't embed the case/calculation logic within a Transformer or buildop instead of keeping it in the query?

Hi Brad,

bcaz i have 360 conditions and 360 columns,it's compulsory to check condition and pass the O/P for corresponding O/P column.

If i use Transformer also expensive bcaz i need to check 360 conditions in Transformer.Performance point of view i'm using SQL query.


Thanks
Beeram