Single stage for running script

Munish · Post by **Munish** » Wed Feb 14, 2007 5:00 pm

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

DSguru2B · Post by **DSguru2B** » Wed Feb 14, 2007 5:03 pm

You can use either one. You might have more control in a routine as your can do error handling and produce appropriate messages in logs. If you dont need all that, then go for execute command stage.

chulett · Post by **chulett** » Wed Feb 14, 2007 5:08 pm

Why not have a job that does this? Why do you 'need' to run sql scripts?

Munish · Post by **Munish** » Wed Feb 14, 2007 5:20 pm

Why not have a job that does this? Why do you 'need' to run sql scripts?

How to do that in a single job.
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

ray.wurlod · Post by **ray.wurlod** » Wed Feb 14, 2007 6:27 pm

Row Generator stage can generate as many 'Y' values as you desire.

Munish · Post by **Munish** » Wed Feb 14, 2007 6:44 pm

My ETL has 40 sequences.
This is going to be the last sequence where once all the prior (40) sequences finishes successfully.
I need to update column in 2 tables in Oracle with value as 'Y' and 'Successful'.

I was just wondering what might be the best way to do it.
Thanks,
Munish

chulett · Post by **chulett** » Wed Feb 14, 2007 7:26 pm

Munish wrote:
Why not have a job that does this? Why do you 'need' to run sql scripts?
How to do that in a single job.
OCI stage does not do anything of its own. I need to add another stage to OCI to get that working.

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 · Post by **Munish** » Wed Feb 14, 2007 8:35 pm

Thanks Champs,
I got it.
Closing the thread.
Regards,
Munish

Munish · Post by **Munish** » Thu Feb 15, 2007 5:24 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Feb 15, 2007 1:53 pm

Munish wrote:This script works fine in Toad but no row is updated.

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

What is the Generator property in the Row Generator stage? Do make sure that you generate one row.

narasimha · Post by **narasimha** » Thu Feb 15, 2007 2:07 pm

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 ?

Auto commit could be disabled in TOAD. Do an explicit commit and check if the rows get updated.

Munish · Post by **Munish** » Thu Feb 15, 2007 5:41 pm

Thanks Everyone,
It worked fine this time.

I was missing:
I was not passing any record from Row Generator.
We have to make sure that it should be atleast 1.

Thanks again,
Closing the thread,

Regards,
Munish