Executing Mainframe DB2 Procedure multiple times

horserider · Post by **horserider** » Sun Apr 27, 2008 7:06 pm

I have a stored procedure that will take 1 parameter and will return some output. I will have the parameter values in a text file. Below is an example:-

(1) My Data file FinancialYear.txt will have below values
1995
1996
1997
2001
2008

(2) DB2 Stored Procedure will accept above 4 character (year) as value
and will return Year and Total Revenue in Dollars.

WHAT I NEED

From a parallel job, I need to call Stored Procedure for every row, 5 times
in this case, passing 1995 till 2008 as inpit parameter and then store the values in a table.

(a) What Stage to use to call the stored procedure?
(b) How to design a paralle job something like a while loop that will start
with 1995 row and end with 2008 row, each time executing the stored
procedure?

ray.wurlod · Post by **ray.wurlod** » Sun Apr 27, 2008 10:26 pm

How (that is, by what method) do you propose to execute a mainframe DB2 stored procedure from DataStage parallel job?

horserider · Post by **horserider** » Mon Apr 28, 2008 6:54 am

I guess use of STORED PROCEDURE STAGE will do the job, but how to do a while loop so that for every row in a text file this Stored Procedure Stage is executed?

ray.wurlod · Post by **ray.wurlod** » Mon Apr 28, 2008 4:31 pm

Before you even get to that you need to establish whether you can even call the mainframe DB2 stored procedure even once from DataStage running on UNIX.

If you can do it once, the rest is easy and has a number of solutions.

horserider · Post by **horserider** » Mon Apr 28, 2008 8:02 pm

I have not used Stored Procedure Stage before, but I see that it has the option to select DB2 in the database type select drop down. So assuming that we actually can call a DB2 stored procedure from within DataStage,

How can we use While Loop to read the TEXT file and call DB2 procedure for each row.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 28, 2008 8:31 pm

My issue is that DataStage is not where the DB2 database is. One is on UNIX, the other is on the mainframe. Are you using DB2 Connect software to access DB2 on the mainframe? If so then you can probably use the Stored Procedure stage.

Obtain the year value by reading the file with a Sequential File stage, and provide this as input to the Stored Procedure stage. That should be all you need to do, though an output link from the Stored Procedure stage so that you can capture the output from the stored procedure is probably a good idea.