Originally posted by: SystemAdmin

Hello,

I want to run an Oracle SQL statement with the GET-->Source-->Command of an input card WITHOUT using any MQD file and it works. But when my statement is too big, it fails. What can I do? There is a way to put the query in a text file and call it?

I don’t want to use a MDQ file because we have many different databases and to override dynamically the DBNAME, all these databases have to be defined in the MDQ file. We have also to copy all the queries inside each defined database. I don’t like this strategy.

Please help me!
Eric
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

It is possible to create an input card for your map that has the SQL statement to be called. You would need a Target with a DBQUERY function. You could then write the rule as follows:

=DBQUERY(Blob1, "%DB_Parameters%")
In the Resource file for each server, you would need a "DB_Parameters" entry that would contain either the Data Source information or MDQ information for that server. This would allow you to not have to compile anything into the map itself or rely on the MDQ for the query handling. I prefer to use a Data Source setting in these cases to avoid the map having to open the MDQ to get the username/pw for the db. It's more efficient.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Hi Jennifer,

Tell me if I'm wrong but in your example:

=DBQUERY(Blob1, "%DB_Parameters%")

the Blob1 is the input card that contains a text file which my SQL statement is in it?

Thanks!
Eric
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

That is correct. Blob1 is an element from Input1. The SQL statement doesn't get parsed but is mapped across as is.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Jennifer,

Your method is good but is there a way to execute the SQL not in the output card but in the input card?

It's because I want the result of my statement to be mapped with the map tree of an input card and then I can easliy map the field with the output card.

I hope my question is clear... my english is not perfect.

Thanks again for your quick reply!

Eric
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

You could create your output card to have 2 elements; one that will contain the DBQUERY function and a second with a RUN command. The results from the query would then be an input to your RUN command. This would allow you to use your tree in the RUN map that has the fields defined but still eliminate the MDQ scenario that is causing you problems.

Would this work for you?
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Hi Jennifer,

I'm a new user with TX and I don't yet if by using a RUN command it will work because I use multiple queries for a same map.

I will let you know.
Thanks a lot!!!!!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange