DSXchange

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....

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.

Hi Deepak, what do you mean here by a variable? when i fetch that query using DB2 stage, now where should i send this resultset? should i send it to a file or a job sequence? what stage should i use for this variable?

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.

Investigate the SDK utility routine UtilityRunJob().

Hi Ray,

How would the UtilityRunJob help? It just provides me the job information. IS this info to make use of it in calling my second job?

Pass the query to the job run under control as a job parameter. Use a reference to this job parameter as the user-defined SQL in that job.

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.