Using job parameter value in Oracle connector stage

[sumesh.abraham]

Hi all,
I have a scenario to implement in a parallel job using Oracle connector stage. Basically, based on the business logic, I will have to generate a SELECT query with a where clause and Oracle connector stage execute that. Since I would need to execute multiple queries and the output columns are the same, I am planning to have a multiple instance job created instead of separate job for each query. I would ideally generate the SQL statements in a Perl script and then invoke the Datastage job with the query as the parameter and let Datastage execute that.

We are inclined towards a parallel job for better performance and anticipated records returned by the queries.

I have few questions here.

1. Is it possible to pass a select query as the job parameter value and let Oracle connector stage execute the query by reading the parameter value?
2. Is there a way to have the Oracle connector stage generate the 'WHERE' clause in the SQL statement if I define a key column in the column list.

Please let me know your thoughts.

[ray.wurlod]

1. Yes.
2. No. But you can provide it as part of the custom SELECT statement.

I have done this in the past, generating the SELECT statement via a query against the Oracle system tables. This was done in an upstream server job and the SQL query saved in that job's user status area.