V8.2 - SQL/PL Continue handlers not working?

[Paul Reddin]

Hi,

Having just upgraded one of our servers to V8.2, the CONTINUE HANDLERs
in Stored procedures seem to be behaving differently?

i.e Instead of continuing at the next statement (as they did before on
V8.1)
they now exit immmediately ????

e.g from 1 SP
declare c_create_temp_tables condition for sqlstate '56098';
declare v_rowcount integer;
declare SQLCODE_TOKEN_STRING_OUT VARCHAR(100);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;
declare continue handler for c_create_temp_tables
begin

declare global temporary table session.product_search_oids
like pstmp.product_search_template_oids
on commit preserve rows
not logged;

declare global temporary table session.product_search_ps
like pstmp.product_search_template_ps
on commit preserve rows
not logged;

declare global temporary table session.product_search_prices
like pstmp.product_search_template_prices
on commit preserve rows
not logged;

declare global temporary table session.product_search_uplifts
like pstmp.product_search_template_uplifts
on commit preserve rows
not logged;
end; -- Continue handler for when temp tables don't exist

-- Empty the temp tables incase any data is still in them from the
last time this procedure was called.
delete from session.product_search_oids;
On V8.1 this would create the GTTs if the delete failed, i.e first
time of running

Now, it exits with an error on the first GTT DECLARE ??

Is there something that needs running/binding after the upgrade?

Thanks.

Paul.

[Philip Sherman]

Your logic has some potential flaws. If session.product_search_oids does
not exist and any of the other tables exist; the continue handler should
fail. SQLSTATE 56098 can occur for other things besides a non-existing
table in a delete statement.

I've usually done this by always declaring the tables in the stored
procedure and using the "WITH REPLACE" clause. This will create new if
needed and replace, deleting all existing rows, when the table is
already defined. I haven't looked at the performance differences between
the two approaches.

Phil Sherman
Paul Reddin wrote:

Hi,

Having just upgraded one of our servers to V8.2, the CONTINUE HANDLERs
in Stored procedures seem to be behaving differently?

i.e Instead of continuing at the next statement (as they did before on
V8.1)
they now exit immmediately ????

e.g from 1 SP
declare c_create_temp_tables condition for sqlstate '56098';
declare v_rowcount integer;
declare SQLCODE_TOKEN_STRING_OUT VARCHAR(100);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;
declare continue handler for c_create_temp_tables
begin

declare global temporary table session.product_search_oids
like pstmp.product_search_template_oids
on commit preserve rows
not logged;

declare global temporary table session.product_search_ps
like pstmp.product_search_template_ps
on commit preserve rows
not logged;

declare global temporary table session.product_search_prices
like pstmp.product_search_template_prices
on commit preserve rows
not logged;

declare global temporary table session.product_search_uplifts
like pstmp.product_search_template_uplifts
on commit preserve rows
not logged;
end; -- Continue handler for when temp tables don't exist

-- Empty the temp tables incase any data is still in them from the
last time this procedure was called.
delete from session.product_search_oids;
On V8.1 this would create the GTTs if the delete failed, i.e first
time of running

Now, it exits with an error on the first GTT DECLARE ??

Is there something that needs running/binding after the upgrade?

Thanks.

Paul.

[Paul Reddin]

Philip Sherman <ps******@ameritech.net> wrote in message news:<95*****************@newssvr33.news.prodigy.c om>...

Your logic has some potential flaws. If session.product_search_oids does
not exist and any of the other tables exist; the continue handler should
fail. SQLSTATE 56098 can occur for other things besides a non-existing
table in a delete statement.
Yes, valid points we have considered, but not top priority at this
time.
I've usually done this by always declaring the tables in the stored
procedure and using the "WITH REPLACE" clause. This will create new if
needed and replace, deleting all existing rows, when the table is
already defined. I haven't looked at the performance differences between
the two approaches.

Phil, I believe we tried using this method way back. And I think the
problem
with your approach is that it invalidates the plans in the cache for
the SP.
i.e
Presumably, because the dependent temp. table object has effectively
been dropped.
Hence, a performance hit on recompiling.

Maybe somebody could clarify if this is true ?
Also, this seems to be a general problem across many SPs i.e that
CONTINUE handlers EXIT. I am investigating further ... because one 8.2
server on RedHat doesn't exhibit this problem, but another 8.2 Server
on SLES does??
with identical Databases on each?

Paul.

[Philip Sherman]

I don't think I had a problem with the plans being invalidated because
the tables I used had real definitions in the SESSION schema. The
manuals state that the DECLARE for the table in the procedure will
"replace" access to the one that exists in the SESSION schema, giving
each connection its own private "copy" of the table. I don't recall
anything about this requiring a rebind.

Phil Sherman
Paul Reddin wrote:

Philip Sherman <ps******@ameritech.net> wrote in message news:<95*****************@newssvr33.news.prodigy.c om>...

Your logic has some potential flaws. If session.product_search_oids does
not exist and any of the other tables exist; the continue handler should
fail. SQLSTATE 56098 can occur for other things besides a non-existing
table in a delete statement.

Yes, valid points we have considered, but not top priority at this
time.

I've usually done this by always declaring the tables in the stored
procedure and using the "WITH REPLACE" clause. This will create new if
needed and replace, deleting all existing rows, when the table is
already defined. I haven't looked at the performance differences between
the two approaches.

Phil, I believe we tried using this method way back. And I think the
problem
with your approach is that it invalidates the plans in the cache for
the SP.
i.e
Presumably, because the dependent temp. table object has effectively
been dropped.
Hence, a performance hit on recompiling.

Maybe somebody could clarify if this is true ?
Also, this seems to be a general problem across many SPs i.e that
CONTINUE handlers EXIT. I am investigating further ... because one 8.2
server on RedHat doesn't exhibit this problem, but another 8.2 Server
on SLES does??
with identical Databases on each?

Paul.

[Serge Rielau]

Whenever a a SESSION object is being altered/dropped/recreated DB2 will
invalidate the plans for all SQL statements using the object, resulting
in an automatic rebind next time the statement is hit (VALIDATE(RUN)
semantics).
The reason is that the altered table may look completely different with
e.g. altered datatypes, indexes. etc.
So, yes, there is a performance impact to replacing temp tables.
I have an SQL Performance article in the works which should be up on
developerWorks/DB2 in a few weeks.

Find below the relevant chapter. (my news server doesn't allow me to
post the screenshots....)

Using Temporary Tables
DB2 supports Declared Global Temporary Table (DGTT). In SQL lingo
DECLARE means that the object being defined will not have an entry in
the catalog. Therefore it is by definition private. By contrast Created
Global Temporary Tables as supported for example by DB2 UDB for zOS are
defined in the catalogs. While their content is, of course, private,
their definition is not.

The flexibility of having a declared object (no DBA cares what the app
does as long as user temporary table space is readily available) comes
with a downside: For a statement to be compiled the object must exist.
If the connection is lost or the table is dropped any statement
referring to the DGTT needs to be recompiled every time the table is
re-declared.

Again let’s work through an example:
The code above is, of course, non sense, but its structure is common so
it shall serve to illustrate the point. In this case the procedure is
called frequently and the DGTT is used only locally. So, for reasons of
encapsulation the procedure declares its own temporary table and then
drops it when the temp is no longer needed.

What is wrong with this picture? The insert statement between the
DECLARE and the DROP needs to be recompiled every time because DB2
cannot know whether the DGTT will have the same properties next time
around. In reality there will be any number of statements between the
DECLARE and the DROP massaging the result set of this temp until it
finally has served its purpose.

To avoid this compilation frenzy it is much more appropriate to move the
declaration of the temp into a separate procedure which is executed once
when the workload is started.
As you can see above the result is astounding. There is more to see here
though. Observe the DELETE statement which has replaced the DROP
statement. It will always fail with a user provoked error, which is then
thrown away by a continue handler. What’s going on here?

Temporary tables are optimized for speed. Because of that DB2 does not
bother finding free space within a temp when inserting rows. Instead the
table behaves as APPEND ONLY. While a regular DELETE would delete the
rows it would not actually cause DB2 to recycle the space. The
application will keep on consuming more and more user temporary table space.

Ironically another quirk comes to our help here. Virtually all DGTT are
declared NOT LOGGED. After all, the content of the temp can be
reconstructed easily enough. If a NOT LOGGED table encounters an
execution error during a data changing statement there is only one
choice for DB2: Truncate the table. And that is what the DELETE
statement is doing.
Cheers
Serge

[Paul Reddin]

Serge,

What is the " Observe the DELETE statement which has replaced the DROP
statement. It will always fail with a user provoked error, " You mention?

Sounds like we should be using this, it could explain why our SP performance
goes horribly wrong after several days use?

Thanks.

Paul.

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...

As you can see above the result is astounding. There is more to see here
though. Observe the DELETE statement which has replaced the DROP
statement. It will always fail with a user provoked error, which is then
thrown away by a continue handler. What’s going on here?

Temporary tables are optimized for speed. Because of that DB2 does not
bother finding free space within a temp when inserting rows. Instead the
table behaves as APPEND ONLY. While a regular DELETE would delete the
rows it would not actually cause DB2 to recycle the space. The
application will keep on consuming more and more user temporary table space.

Ironically another quirk comes to our help here. Virtually all DGTT are
declared NOT LOGGED. After all, the content of the temp can be
reconstructed easily enough. If a NOT LOGGED table encounters an
execution error during a data changing statement there is only one
choice for DB2: Truncate the table. And that is what the DELETE
statement is doing.
Cheers
Serge

[Serge Rielau]

DELETE FROM SESSION.UNLOGGED_TEMP WHERE some_col =
CAST(raise_error('78000', 'Truncate me') AS <sometype>)

Cheers
Serge

[Serge Rielau]

Here is the example:

DROP PROCEDURE init()
$
DROP PROCEDURE destroy()
$
DROP PROCEDURE driver_filltemp_tuned()
$
DROP PROCEDURE driver_filltemp_untuned()
$
DROP PROCEDURE filltemp_untuned
$
DROP PROCEDURE filltemp_tuned
$
DROP TABLE T
$

CREATE TABLE T
(pk INTEGER NOT NULL PRIMARY KEY,
c1 VARCHAR(2000) NOT NULL,
c2 INTEGER)
$

CREATE PROCEDURE filltemp_untuned()
BEGIN
DECLARE GLOBAL TEMPORARY TABLE session.scratch LIKE t NOT LOGGED;
INSERT INTO session.scratch
SELECT * FROM t;
DROP TABLE session.scratch;
END
$

CREATE PROCEDURE init()
BEGIN
DECLARE GLOBAL TEMPORARY TABLE session.scratch LIKE t NOT LOGGED;
END
$
CALL init()
$
CREATE PROCEDURE filltemp_tuned()
BEGIN
INSERT INTO session.scratch
SELECT * FROM t;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '78000'
BEGIN END;
DELETE FROM session.scratch
WHERE pk = CAST(raise_error('78000', '') AS INTEGER);
END;
END
$

CREATE PROCEDURE destroy()
BEGIN
DROP TABLE session.scratch;
END
$

CALL destroy()
$

CREATE PROCEDURE driver_filltemp_untuned()
BEGIN
DECLARE iter INTEGER;
SET iter = 0;
WHILE iter < 100 DO
CALL filltemp_untuned();
SET iter = iter + 1;
END WHILE;
END
$
CREATE PROCEDURE driver_filltemp_tuned()
BEGIN
DECLARE iter INTEGER;
CALL init();
SET iter = 0;
WHILE iter < 100 DO
CALL filltemp_tuned();
SET iter = iter +1;
END WHILE;
CALL destroy();
END
$

INSERT INTO t
(pk, c1, c2)
WITH rec(pk, c1, c2) AS (values (1, 100, 200)
UNION ALL
SELECT pk + 1, c1 + 100, c2 + 200
FROM rec WHERE pk < 100)
SELECT pk, char(c1), c2 FROM rec
$