Store procedure in DataStage

[peppinodicapri]

I have tried to execute one procedure in stage ODBC: User-defined
SQL=call nameprocedure(); The procedure is executed several times
(loop). How I can avoid the loop?

Thanks

[roy]

Hi,
please post the structure of the job.
I used a stored procedure via ODBC but it never run more then once.
so I wonder how come your's does?
does the SP have a recursive call?, or does it simply contains a bad join which results in a cartezian result set (5 rows from table a with no join on table b having also 5 rows will return 5*5 = 25 rows)???

IHTH,

[lpadrta]

Hello peppinodicapri,

I have also come across a situation where I ran a DataStage job one time that called a stored procedure one time via an ODBC connection, and the DataStage job logs show one execution, but tracing on the SQL server shows the stored procedure executed multiple times. And the same thing happens with other stored procedures. I even ran one of the stored procedure from Query Analyzer and the stored procedure executed multiple times.

That seems to suggest there is something interesting about the stored procedures, not DataStage.

Did you ever find out why yours did that?

Thanks,

Lynda

[ray.wurlod]

I have tried to execute one procedure in stage ODBC: User-defined
SQL=call nameprocedure(); The procedure is executed several times
(loop). How I can avoid the loop?

Thanks

The stored procedure is called once for each row processed by the job.

The only way you can "avoid the loop" is by processing only one row.