dyamic SQL in ODBC stage help

dav_mcnair · Post by **dav_mcnair** » Fri Sep 11, 2009 11:01 am

I have a need to read a daterange from a DB2 table and pass the daterange from the table to an ORACLE PX job which has an ODBC stage. The daterange will be used in the query in the WHERE clause. Anyone have any idea on how to pass the DB2 datarange as a parm for the ORACLE job?

chulett · Post by **chulett** » Fri Sep 11, 2009 12:37 pm

You have a couple of choices. You could dynamically generate the Oracle SQL to a flat file and then use the "SQL File" option in the ODBC, assuming it supports that. Or land just the range and in a Sequence job that runs both jobs pick up the output and pass it through as a job parameter to the second job. You'll need to stick with job parameters if you want to use them in your SQL query.

Another option is to use a small Server job to pull the range and stash it in its USERSTATUS area, this is the easiest way in a Sequence job to get information from one job to another. Search here for DSSetUserStatus for the teeny little routine you'd need for that.

Sreenivasulu · Post by **Sreenivasulu** » Mon Sep 14, 2009 12:10 pm

Hi ,

Checked in odbc connector and odbc enterprise stage.
I do not see any place where you can read the dynamic sql query from a flat file

Regards
Sreeni

chulett · Post by **chulett** » Mon Sep 14, 2009 12:17 pm

It's not always presented as a discrete option. From the manuals:

If the property value begins with FILE=, the remaining text is interpreted as a pathname, and the contents of the file supplies the property value.

Meaning, there are stages where you can say 'User Defined' and then do as above to have it read in the sql from a file.