Multiple SQL SPs accessed from one SP

opthomas

I'm fairly new to DB2 Stored Procedures so not sure how best to go about this.
I need to generate a csv file that will contain data from approximately 9
different tables [I do not wish to have a single query for this] -
approxinmately 150 columns. This will be accessed via a servlet so the
calling application will be Java. Direct JDBC from the servlet to the tables
is not really an ideal way to go.

As I am from an Oracle Background my first port of call would be to create a
package with a main procedure that has 9 in/out parameters defined as result
sets.

I have not come across anywhere in DB2 the concept of a Package for Stored
Procedures?

I also definitely don't want a procedure with 150 parameters. So how best to
get the data out of the database.

If from Procedure 1 I repeatedly call Procedure 2, 3, 4, 5 etc and each one
of these returns a result set each time with only a single row [I'm going to
use all these result sets to generate my csv file]. Am I best to put this
data into a temporary table then return the data via cursor back to the
Servlet. Should I put the ALL the data into a string and return an aray of
strings.

All help greatly appreciated.

Owen

Apr 11 '06 #1

Serge Rielau

opthomas wrote:

I'm fairly new to DB2 Stored Procedures so not sure how best to go about this.
I need to generate a csv file that will contain data from approximately 9
different tables [I do not wish to have a single query for this] -
approxinmately 150 columns. This will be accessed via a servlet so the
calling application will be Java. Direct JDBC from the servlet to the tables
is not really an ideal way to go.

As I am from an Oracle Background my first port of call would be to create a
package with a main procedure that has 9 in/out parameters defined as result
sets.

I have not come across anywhere in DB2 the concept of a Package for Stored
Procedures?

I also definitely don't want a procedure with 150 parameters. So how best to
get the data out of the database.

If from Procedure 1 I repeatedly call Procedure 2, 3, 4, 5 etc and each one
of these returns a result set each time with only a single row [I'm going to
use all these result sets to generate my csv file]. Am I best to put this
data into a temporary table then return the data via cursor back to the
Servlet. Should I put the ALL the data into a string and return an aray of
strings.

Let's start from teh spot that you are familar with.
How would you do it in Oracle?
A package in Oracle AFAIK is not much more than a namespace, so I'm
unclear how using a package gives you any functional help other than
"hiding" the existenc eof your helper-procs.

In DB2 I'd simply place all the procs into the same schema, granting
execute only to the top procedure.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 11 '06 #2

opthomas

A package gives me a logical grouping of functions and procedures. It's a
tidy way of grouping objects.

Let's ignore that and say we go with a structure of a primary package only
having execute rights. Would you suggest a temporary table as the best
solution? If I am to create a temporary table is my understanding correct
that it has to be created as a Global Temporary Table first before I can
declare and use it withing my stored procedures?

Cheers

Owen

Apr 11 '06 #3

Serge Rielau

opthomas wrote:

A package gives me a logical grouping of functions and procedures. It's a
tidy way of grouping objects.

Let's ignore that and say we go with a structure of a primary package only
having execute rights. Would you suggest a temporary table as the best
solution? If I am to create a temporary table is my understanding correct
that it has to be created as a Global Temporary Table first before I can
declare and use it withing my stored procedures?

Unlike in Oracle global temporary tables in DB2 or LUW are DECLARed.
That is you declare then in your session before exetuting the procedures.
My prepared approach is like this:

--#SET TERMINATOR @
CREATE PROCEDURE declare()
BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP(c1 INT);
END
@
CALL declare()
@
CREATE PROCEDURE P()
BEGIN
CALL declare();
INSERT INTO SESSION.TEMP VALUES 1, 2, 3;
END
@
--#SET TERMINATOR ;
.....
CALL P();

Note how I encapsulate the temp table declararion into an init procedure.
That procedure is called prior to the CREATEs which use the proc.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 11 '06 #4

Liu Liu

If you want to return the data in 9 pre-determined tables, then it's
best to use a view and skip the csv file. If you would like to generate
the csv file, then you should write a stored procedure to generate it
and let the servlet to accesst the csv file directly. If you need a way
to access the csv file through JDBC, then it's best to write a table
function that convert the csv file to a resultset. If you are not going
to use MPP, then the table function can access the 9 tables as well.

Apr 11 '06 #5

sunphan

Liu

I also have same kind of requirement. You mentioned above that "... If you would like to generate the csv file, then you should write a stored procedure to generate it ...", do you know how to do this? It will solve 'opthomas's and my problem.

Thanks
-Sunil P

Apr 11 '06 #6