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.
Dynamically Selecting ODBC stage Table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Re: Dynamically Selecting ODBC stage Table
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.kiran_kom wrote:I could do this with multiple Odbc stages and use a constraint in a transformer stage and decide the target.
No. The session binds and prepares the SQL and then streams tabular data, there is no monkey business allowed after the prepare.kiran_kom wrote: 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 ?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Re: Dynamically Selecting ODBC stage Table
santhu wrote:
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
Re: Dynamically Selecting ODBC stage Table
Will look into that. We do have 7.1 but we'r using 7.0.santhu wrote:santhu wrote:
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
Thanks to everyone who replied.