dyamic SQL in ODBC stage help
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 35
- Joined: Thu Apr 19, 2007 12:42 pm
dyamic SQL in ODBC stage help
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?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
It's not always presented as a discrete option. From the manuals:
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.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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers