DSXchange

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.

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.

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.

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

Or, more simply, a view.

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

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