long query

ratna · Post by **ratna** » Thu Feb 21, 2008 4:36 am

hello guys,
i have a problem here..
i have a pretty loooong query.. and when i paste to the oracle stage, it only can accept half my query... what should i do?

thanks.

ArndW · Post by **ArndW** » Thu Feb 21, 2008 4:45 am

I think you get a Windows error message when you do that, something to the effect that the data size exceeds the allocated space and it tells you the number of bytes. There is no solution but to reduce the query size. You can shorten the query (Use x.KeyCol instead of FullyQualfiedTablePathAndNameAsUsedInDevelopmentV2.KeyCol) or perhaps store the query in Oracle and reference that by name.

throbinson · Post by **throbinson** » Thu Feb 21, 2008 5:45 am

If this is a complex query with a lot of joins, subqueries, etc. versus many, many columns, you could also review the design of the query itself. If there are many joins perhaps these could be done in DataStage instead of in Oracle. SQL that cannot fit within the Oracle stage probably has some maintainability issues due to it's complexity. I can see all the heavy lifting being done in a single humongous SQL because of perceived performance reasons or resource constraints but is this really the case? Can you simplify by re-writing pieces in DataStage? Will the resources of your DS Server support doing that? Right size it.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 21, 2008 6:27 am

And, if none of those works, assemble your long query into a stored procedure and call the stored procedure from DataStage.

chulett · Post by **chulett** » Thu Feb 21, 2008 8:21 am

Or, more simply, a view.

dspxlearn · Post by **dspxlearn** » Thu Feb 21, 2008 3:47 pm

If you have Unions split it into multiple stages. And if there are Joins in the Sql, use the stages as required.

dspxlearn · Post by **dspxlearn** » Thu Feb 21, 2008 3:48 pm

If you have Unions split it into multiple stages. And if there are Joins in the Sql, use the stages as required.