hi folks,
I need some help with this tricky requirement. I have a table and one of its columns will have a query. I need to execute that query i.e. the column of the table.
Example: there is a column called XQUERY and its value could be 'select * from emp'. Now, I need to execute this query and populate my result. And the gud part is that there would be only one record at a time i.e. i need to execute only one query, but it would come as a value of the column.
How can i do this in datastage? I know i can do this using unix script and db2 -tvf commands. but is this possible in datastage?
thnx for the help guys....
query in a column
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Re: query in a column
Assuming that the query stored in the table is a uniform query (you know the output of it - means what format the results will be) you can develop two jobs. The second one gets called from the first one. The first one runs and stores the query in a variable and passes it on to the next job. the next job uses this variable as user defined sql and runs the SQL.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Design the second job in a way that the first stage of the job will run a job variable as a user defined SQL. Now when u call this job from the master job u will have to specify the job variable. Pass the query column as the variable when u call the second job.
You can also write a routine which will return the column query as the return value and then u can use this return value in the user defined SQL tab in the job.
You can also write a routine which will return the column query as the return value and then u can use this return value in the user defined SQL tab in the job.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
my question is that your table contains a column in which query is stored, is this query static i mean to say same query always or dynamic which means that table columns contains different query for each execution, if it is different then how are you populating your columns viewed through datastage.