Dynamically Selecting ODBC stage Table

[kiran_kom]

I have situation where a given row of input (from one source table) must be passed onto one of a bunch of target tables. The target table selection depends on the input data.

I could do this with multiple Odbc stages and use a constraint in a transformer stage and decide the target.

I was wondering if there is a better way to do this. Is there are way to set the target table's name in the ODBC stage dynamically ?

So that I would use one odbc stage and set the target name dynamically.

All the target tables are exact replicas of each other.

[kduke]

User defined SQL. Use #TableName# where this is a parameter feed into this job.

[vmcburney]

I don't think you can do what you want to do, which is dynamically set the table name based on a value found in the transformer stage. You can't change the value of a job parameter within a job.

One approach is to have multiple output links out of your transformer all leading to a shared or local container which has the ODBC output stage in it. The container would have the table name as a job parameter and in the shared container stage you would hard code the different table names for each link.

[kcbland]

I could do this with multiple Odbc stages and use a constraint in a transformer stage and decide the target.

You supplied your own answer. An alternative is to derive the target tablename as an output column and stream to a sequential file. Then, use an after-job cutter script to separate the single output file into multiple files based on the tablename column. This is advantageous if you have a lot of target tables, but in your case I think it's fixed.

I was wondering if there is a better way to do this. Is there are way to set the target table's name in the ODBC stage dynamically ?

No. The session binds and prepares the SQL and then streams tabular data, there is no monkey business allowed after the prepare.

[santhu]

Hi Kiran,

In DataStage 7.1 Server Edition , there is a new stage named DRS (Dynamic RDMS).

I think this stage is best suited for your scenario.

Regards,
Santhosh S

[kiran_kom]

Hi Kiran,

In DataStage 7.1 Server Edition , there is a new stage named DRS (Dynamic RDMS).

I think this stage is best suited for your scenario.

Regards,
Santhosh S

Will look into that. We do have 7.1 but we'r using 7.0.
Thanks to everyone who replied.