Single stage for running script
Moderators: chulett, rschirm, roy
Single stage for running script
Hi All,
I need to
run SQL sripts to update tables in Oracle (Update flag to 'Y')
after successful ETL run.
I want to do in one stage.
What might be best options.
1. Using 'Execute Command' activity.
2. Using Routine.
or
something else.
Thanks for your advice(s) in advance,
Regards,
Munish
I need to
run SQL sripts to update tables in Oracle (Update flag to 'Y')
after successful ETL run.
I want to do in one stage.
What might be best options.
1. Using 'Execute Command' activity.
2. Using Routine.
or
something else.
Thanks for your advice(s) in advance,
Regards,
Munish
MK
How to do that in a single job.Why not have a job that does this? Why do you 'need' to run sql scripts?
OCI stage does not do anything of its own. I need to add another stage to OCI to get that working.
Is there a way out.
I was thinking of using scripts just to get away with this, otherwise I am more than happy with using a job.
Thanks,
Munish
MK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
As Ray noted, a Row Generator can feed the stage. In a Server solution, all you need is a Transformer before the OCI stage to feed it rows. The technique has been discussed here many times. Or if you'd rather, select rows from dual to feed it. Several ways to accomplish this.Munish wrote:How to do that in a single job.Why not have a job that does this? Why do you 'need' to run sql scripts?
OCI stage does not do anything of its own. I need to add another stage to OCI to get that working.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi Everybody,
I tried this
Stage:Dummy RowGenerator, 0 record >>>
OCI:Upsert Mode = User Defined Update Only
I have written my own script to update one Column.
Script:
UPDATE
EDX_RPT_ACCOUNT_FACT
SET
CUST_VIEW_FLAG = 'N'
WHERE
CUST_VIEW_FLAG = 'Y'
This script works fine in Toad but no row is updated.
WHat is that I am missing ?
Thanks and regards,
Munish
I tried this
Stage:Dummy RowGenerator, 0 record >>>
OCI:Upsert Mode = User Defined Update Only
I have written my own script to update one Column.
Script:
UPDATE
EDX_RPT_ACCOUNT_FACT
SET
CUST_VIEW_FLAG = 'N'
WHERE
CUST_VIEW_FLAG = 'Y'
This script works fine in Toad but no row is updated.
WHat is that I am missing ?
Thanks and regards,
Munish
MK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Would you like to clarify that statement? How can it "work fine" if an UPDATE statement updates no rows? Answer: if there are no rows that match the WHERE condition. In that case, DataStage would achieve the same "result".Munish wrote:This script works fine in Toad but no row is updated.
What is the Generator property in the Row Generator stage? Do make sure that you generate one row.
Last edited by ray.wurlod on Thu Feb 15, 2007 2:13 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Auto commit could be disabled in TOAD. Do an explicit commit and check if the rows get updated.Munish wrote:I have written my own script to update one Column.
Script:
UPDATE
EDX_RPT_ACCOUNT_FACT
SET
CUST_VIEW_FLAG = 'N'
WHERE
CUST_VIEW_FLAG = 'Y'
This script works fine in Toad but no row is updated.
WHat is that I am missing ?
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.