Using a PROCEDURE to EXECUTE other PROCEDUREs

[Brian Tkatch]

[DB2/SUN 8.1.6]

I've been asked to time about forty PROCEDUREs in the database, and
keep a history of them for comaprison. So, i figured i'd quickly try it
in the database itself, so anyone could execute it easily. Here's the
basics:

CREATE TABLE Timings_Call
(
Id INTEGER GENERATED ALWAYS AS IDENTITY,
Group VARCHAR(0050),
Name VARCHAR(0050),
Text VARCHAR(0256)
)

CREATE TABLE Timings_Log
(
Id INTEGER GENERATED ALWAYS AS IDENTITY,
Call INTEGER,
Start TIMESTAMP,
Stop TIMESTAMP
)

In both cases, Id is the PK, and CALL is an FK.

Then tried this:

DROP PROCEDURE Test_Timings_Ca ll

CREATE PROCEDURE Test_Timings_Ca ll
(
OUT OUT_SQLCODE INTEGER,
OUT OUT_SQLSTATE CHAR(0005)
)
SPECIFIC Test_Timings_Ca ll
BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(0005) DEFAULT '00000';
DECLARE Start_Time TIMESTAMP;
DECLARE Stop_Time TIMESTAMP;

DECLARE CONTINUE HANDLER FOR
SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN
SET OUT_SQLCODE = SQLCODE;
SET OUT_SQLSTATE = SQLSTATE;
END;

FOR Current_Call AS SELECT Id, Text FROM Timings_Call
DO

SET Start_Time = CURRENT TIMESTAMP;
EXECUTE IMMEDIATE Current_Call.Te xt;
SET Stop_Time = CURRENT TIMESTAMP;

INSERT INTO Timings_Log(Cal l, Start, Stop)
VALUES(Current_ Call.Id, Start_Time, Stop_Time);

END FOR;

IF OUT_SQLSTATE IS NULL THEN

SET OUT_SQLCODE = 0;
SET OUT_SQLSTATE = '00000';

END IF;

END

Using the CLP: CALL Test_Timings_Ca ll(?, ?)

The problem comes in when its done and i issue a COMMIT or CONNECT
RESET.

SQL0774N The statement cannot be executed within an ATOMIC compound
SQL

TERMINATE works.

The other issue is speed. I know they don't run this quickly, the
PROCEDURE even took a few minutes, yet:

db2 => SELECT * FROM Timings_log

ID CALL START STOP
----------- ----------- --------------------------
--------------------------
81 1 2006-02-01-13.23.16.773902
2006-02-01-13.23.36.204279
82 2 2006-02-01-13.23.36.207569
2006-02-01-13.24.16.037312
83 3 2006-02-01-13.24.16.038008
2006-02-01-13.24.16.196470
84 4 2006-02-01-13.24.16.196978
2006-02-01-13.24.16.452116
85 5 2006-02-01-13.24.16.452686
2006-02-01-13.24.19.762783
86 6 2006-02-01-13.24.19.763450
2006-02-01-13.24.24.302802
87 7 2006-02-01-13.24.24.303569
2006-02-01-13.24.27.670360
88 8 2006-02-01-13.24.27.671175
2006-02-01-13.24.32.166419
89 9 2006-02-01-13.24.32.167163
2006-02-01-13.24.34.127496
90 10 2006-02-01-13.24.34.128259
2006-02-01-13.24.41.198766
91 11 2006-02-01-13.24.41.199463
2006-02-01-13.24.41.213446
92 12 2006-02-01-13.24.41.213939
2006-02-01-13.24.41.217095
93 13 2006-02-01-13.24.41.217468
2006-02-01-13.24.41.254833
94 14 2006-02-01-13.24.41.255385
2006-02-01-13.24.41.282525
95 15 2006-02-01-13.24.41.283041
2006-02-01-13.24.41.286024
96 16 2006-02-01-13.24.41.286396
2006-02-01-13.24.44.756141
97 17 2006-02-01-13.24.44.756880
2006-02-01-13.24.44.757890
98 18 2006-02-01-13.24.44.758297
2006-02-01-13.24.51.798671
99 19 2006-02-01-13.24.51.799408
2006-02-01-13.24.55.223971
100 20 2006-02-01-13.24.55.224786
2006-02-01-13.24.58.821576
101 21 2006-02-01-13.24.58.822333
2006-02-01-13.24.58.842636
102 22 2006-02-01-13.24.58.844363
2006-02-01-13.24.58.866712
103 23 2006-02-01-13.24.58.867420
2006-02-01-13.24.58.893308
104 24 2006-02-01-13.24.58.894157
2006-02-01-13.24.58.911025
105 25 2006-02-01-13.24.58.911564
2006-02-01-13.24.59.689057
106 26 2006-02-01-13.24.59.689840
2006-02-01-13.24.59.711142
120 40 2006-02-01-13.24.59.799941
2006-02-01-13.24.59.806985

This is just odd.

B.

[Brian Tkatch]

I think i figured out the main part of the problem. Many of the
PROCEDUREs DECLARE a GLOBAL TEMPORARY TABLE, and by default i named the
first one Temp_1, and incrementing as required. They are just
intermediate steps, so i figured a better name would probably just be
more confusing.

Being the CURSORs are DECALREd WITH RETURN TO CLIENT, it is holding a
lock on the TABLE, and when the next PROCEDURE tries to DECLARE it with
the same name (WITH REPLACE), it causes an error that i am trying to
DROP a TABLE that is still needed.

As simple example:

DROP PROCEDURE AA
CREATE PROCEDURE AA()
BEGIN
DECLARE GLOBAL TEMPORARY TABLE A(A INT) NOT LOGGED WITH REPLACE;
BEGIN
DECLARE A CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM SESSION.A;
OPEN A;
END;
END

DROP PROCEDURE BB
CREATE PROCEDURE BB() BEGIN CALL AA; CALL AA; END

CALL BB

DROP PROCEDURE AA
DROP PROCEDURE BB
COMMIT

results in

SQL0910N The SQL statement cannot access an object on which a
modification is pending. SQLSTATE=57007

Which is understandable.

Without renaming all the objects, is there a way to test this?

B.

[Brian Tkatch]

OK, got it to work. Changed the PROCEDURE to execute one comamnd at a
time, and used the shell.

DROP PROCEDURE Test_Timings_Ca ll

CREATE PROCEDURE Test_Timings_Ca ll(IN_Id INT)
SPECIFIC Test_Timings_Ca ll
BEGIN

DECLARE Start_Time TIMESTAMP;
DECLARE Stop_Time TIMESTAMP;
DECLARE Query VARCHAR(256);

SELECT Text INTO Query FROM Timings_Call
WHERE Timings_Call.Id = IN_Id;

SET Start_Time = CURRENT TIMESTAMP;
EXECUTE IMMEDIATE Query;
SET Stop_Time = CURRENT TIMESTAMP;

INSERT INTO Timings_Log(Cal l, Start, Stop)
VALUES(IN_Id, Start_Time, Stop_Time);

END

eval "$(db2 -x "SELECT 'db2 +o \"CALL Test_Timings_Ca ll(' || CHAR(Id)
|| ')\"' FROM Timings_Call")"

B.