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?
Executing Mainframe DB2 Procedure multiple times
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 71
- Joined: Mon Jul 09, 2007 1:12 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 71
- Joined: Mon Jul 09, 2007 1:12 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
If you can do it once, the rest is easy and has a number of solutions.
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.
-
- Participant
- Posts: 71
- Joined: Mon Jul 09, 2007 1:12 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.
How can we use While Loop to read the TEXT file and call DB2 procedure for each row.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.