Sub procedures in DB2?

[patrick.cc.choi]

Hi all,

In Oracle stored procedures, you can declare sub-procedures to help
modularize your code... i.e.

CREATE OR REPLACE PROCEDURE myProcedure is

PROCEDURE b (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
IS
BEGIN
...
INSERT INTO bb
VALUES (field1, field2, field3);
...
END;

PROCEDURE c (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
IS
BEGIN
...
INSERT INTO cc
VALUES (field1, field2, field3);
...
END;

PROCEDURE a (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
AS
BEGIN
b(field1, field2, field3);
c(field1, field2, field3);
END;


What would a DB2 stored procedure look like if it followed the above
Oracle program logic?

[Serge Rielau]

pa************* @gmail.com wrote:

Hi all,

In Oracle stored procedures, you can declare sub-procedures to help
modularize your code... i.e.

CREATE OR REPLACE PROCEDURE myProcedure is

PROCEDURE b (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
IS
BEGIN
...
INSERT INTO bb
VALUES (field1, field2, field3);
...
END;

PROCEDURE c (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
IS
BEGIN
...
INSERT INTO cc
VALUES (field1, field2, field3);
...
END;

PROCEDURE a (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
AS
BEGIN
b(field1, field2, field3);
c(field1, field2, field3);
END;


What would a DB2 stored procedure look like if it followed the above
Oracle program logic?

Just create the sub-procedures outside of the main procedure body.You
can place them in a separate schema not on the PATH to hide them if you
wish (somewhat similar to what you would do in Oracle by a package body).
I admit this is the very first time I see a subprocedure request.
Even in C/C++ this is not a popular feature....

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

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Patrick]

Hi Serge,

Thanks for your reply. I think I know what you're talking about, but I
can't wrap my head around it. Do you mind providing a short code
example?

Currently my DB2 procedures look like this:

CREATE PROCEDURE myProcedure(... )_ IS

-- global variables here currently

-- put sub procedures here? i.e.
-- CREATE PROCEDURE B(...) IS BEGIN ... END;

P1: BEGIN
...
-- would like to call a sub procedure here i.e.
-- B(...)
...
END;

The problem is that the program I'm converting from is over 20k lines
on ONE file (insane) and they use global variables. The procedures in
the existing Oracle stored proc. modify these global variables so it's
a nightmare to convert to in DB2. Sometimes the sub-procedures simply
can't be put into another stored proc because they need to modify the
global variables, and that's what's stopping my progress.

On smaller scripts I'm currently using GOTO statements to control
execution flow with labels, but this is a hack solution that will not
work if there are multiple sub-procedure calls. The absolute worst case
scenario is I copy & paste the Oracle sub-procedures into places where
they are called, and that is so bad I don't want to think about it
right now. =)

Serge Rielau wrote:

pa************* @gmail.com wrote:

Hi all,

In Oracle stored procedures, you can declare sub-procedures to help
modularize your code... i.e.

CREATE OR REPLACE PROCEDURE myProcedure is

PROCEDURE b (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
IS
BEGIN
...
INSERT INTO bb
VALUES (field1, field2, field3);
...
END;

PROCEDURE c (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
IS
BEGIN
...
INSERT INTO cc
VALUES (field1, field2, field3);
...
END;

PROCEDURE a (field1 INTEGER,
field2 INTEGER,
field3 INTEGER)
AS
BEGIN
b(field1, field2, field3);
c(field1, field2, field3);
END;


What would a DB2 stored procedure look like if it followed the above
Oracle program logic?

Just create the sub-procedures outside of the main procedure body.You
can place them in a separate schema not on the PATH to hide them if you
wish (somewhat similar to what you would do in Oracle by a package body).
I admit this is the very first time I see a subprocedure request.
Even in C/C++ this is not a popular feature....

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

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Serge Rielau]

I see...
In DB2 for LUW today you have two options to cope with global variables:
1. Use a DECLAREd GLOBAL TEMPORARY TABLE.
That is declare a DGTT with one row and a column for each variable.
Then simply UPDATE the table instead of SET-ing the variables.
SELECT instead of reading it.
2. Do "the right thing" and pass variables back and forth through the
procedure as INOUT parameters.
This is the preferred way.

Example:
--#SET TERMINATOR !
CREATE PROCEDURE sub1(IN arg INTEGER, OUT res INTEGER,
INOUT globalvar INTEGER)
BEGIN
SET res = arg + 1;
SET globalvar = 5;
END
!

CREATE PROCEDURE sub2(IN arg INTEGER, OUT res INTEGER,
INOUT globalvar INTEGER)
BEGIN
SET res = arg - 1;
SET globalvar = 7;
END
!

CREATE PROCEDURE PROC(IN arg INTEGER, OUT res INTEGER)
BEGIN
DECLARE globalvar INTEGER;
CALL sub1(arg, arg, globalvar);
CALL sub1(arg, arg, globalvar);
SET res = arg + globalvar;
END
!

GRANT EXECUTE ON PROC(INTEGER, INTEGER) TO PUBLIC
!

--#SET TERMINATOR ;
Does that help?
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Patrick]

Hi Serge,

This definitely is a workable solution.

One thing that concerns me is performance. Updating a global variable
say has a cpu/resource cost of 1, anyone remembers asymptotic analysis.
In the classroom, we're always told that constants and O(1) doesn't
matter, however that doesn't translate in the wonderful world of RDMS.

A function call also should have a cost of O(1). So assigning & reading
a global variable also has a cost of O(1), therefore they are
equivalent in this sense. But we all know that if you call a function >
20,000 times when you're processing 5 million records in 2 hours... it
adds up.

So my question is if I have to call these "helper" SPs all the time,
would that seriously impact the performance of the main SP?

In comparison, the script I'm running now in Oracle takes about 5 hours
to finish, and processes about 20 million records. I'm concerned that
with all this function calling, the script will take much much longer
and this would be unacceptable.

Any ideas on this would be appreciated!

- Patrick

Serge Rielau wrote:

I see...
In DB2 for LUW today you have two options to cope with global variables:
1. Use a DECLAREd GLOBAL TEMPORARY TABLE.
That is declare a DGTT with one row and a column for each variable.
Then simply UPDATE the table instead of SET-ing the variables.
SELECT instead of reading it.
2. Do "the right thing" and pass variables back and forth through the
procedure as INOUT parameters.
This is the preferred way.

Example:
--#SET TERMINATOR !
CREATE PROCEDURE sub1(IN arg INTEGER, OUT res INTEGER,
INOUT globalvar INTEGER)
BEGIN
SET res = arg + 1;
SET globalvar = 5;
END
!

CREATE PROCEDURE sub2(IN arg INTEGER, OUT res INTEGER,
INOUT globalvar INTEGER)
BEGIN
SET res = arg - 1;
SET globalvar = 7;
END
!

CREATE PROCEDURE PROC(IN arg INTEGER, OUT res INTEGER)
BEGIN
DECLARE globalvar INTEGER;
CALL sub1(arg, arg, globalvar);
CALL sub1(arg, arg, globalvar);
SET res = arg + globalvar;
END
!

GRANT EXECUTE ON PROC(INTEGER, INTEGER) TO PUBLIC
!

--#SET TERMINATOR ;
Does that help?
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Serge Rielau]

Patrick wrote:

Hi Serge,

This definitely is a workable solution.

One thing that concerns me is performance. Updating a global variable
say has a cpu/resource cost of 1, anyone remembers asymptotic analysis.
In the classroom, we're always told that constants and O(1) doesn't
matter, however that doesn't translate in the wonderful world of RDMS.

A function call also should have a cost of O(1). So assigning & reading
a global variable also has a cost of O(1), therefore they are
equivalent in this sense. But we all know that if you call a function >
20,000 times when you're processing 5 million records in 2 hours... it
adds up.

So my question is if I have to call these "helper" SPs all the time,
would that seriously impact the performance of the main SP?

In comparison, the script I'm running now in Oracle takes about 5 hours
to finish, and processes about 20 million records. I'm concerned that
with all this function calling, the script will take much much longer
and this would be unacceptable.

Any ideas on this would be appreciated!

I don't understand in your original you also have helper procedures.
I am not aware that Oracle derives any performance advantage out of
defining a procedure within a procedure vs. defining it in the same package.
The only difference wrt. the stored process I can see is that Oracle
loads the entire package into memory while in DB2 each procedure will be
loaded independently upon first usage.
There will be a small price to pay in DB2 by passing the global
variables around as argumnets, but assuming you ar enot pushing 2G LOBs
that shouldn't matter. All this is still O(1) btw. just a bigger 1 :-)

In my experience performance regression on migration is in it's first
order generated by the attempt to emulate the source DBMS on too high a
level. You will find sufficient codepath to squeeze out by optimizing
out the emulations. Small things like extra parameters or package level
caching play a second order role in performance at best.

E.g. look at nonsense casts due to VARCHAR2 semantics ('' == NULL).
DATE arithmetic is another juicy one.

search on www.ibm.com for "rielau" you will find an article on an SQL
Procedure tracer. My SQL Procedure Profiler is available for free and
supported in the Developer Workbench. You will find it invaluable to
track performance problems.

Cheers
Serge

PS: I sent you an off line note, did you get it? If not please ping me
with a viable email address.