473,577 Members | 3,334 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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_t ables condition for sqlstate '56098';
declare v_rowcount integer;
declare SQLCODE_TOKEN_S TRING_OUT VARCHAR(100);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY 1;
declare continue handler for c_create_temp_t ables
begin

declare global temporary table session.product _search_oids
like pstmp.product_s earch_template_ oids
on commit preserve rows
not logged;

declare global temporary table session.product _search_ps
like pstmp.product_s earch_template_ ps
on commit preserve rows
not logged;

declare global temporary table session.product _search_prices
like pstmp.product_s earch_template_ prices
on commit preserve rows
not logged;

declare global temporary table session.product _search_uplifts
like pstmp.product_s earch_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.
Nov 12 '05 #1
7 5214
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_t ables condition for sqlstate '56098';
declare v_rowcount integer;
declare SQLCODE_TOKEN_S TRING_OUT VARCHAR(100);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY 1;
declare continue handler for c_create_temp_t ables
begin

declare global temporary table session.product _search_oids
like pstmp.product_s earch_template_ oids
on commit preserve rows
not logged;

declare global temporary table session.product _search_ps
like pstmp.product_s earch_template_ ps
on commit preserve rows
not logged;

declare global temporary table session.product _search_prices
like pstmp.product_s earch_template_ prices
on commit preserve rows
not logged;

declare global temporary table session.product _search_uplifts
like pstmp.product_s earch_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.


Nov 12 '05 #2
Philip Sherman <ps******@ameri tech.net> wrote in message news:<95******* **********@news svr33.news.prod igy.com>...
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.
Nov 12 '05 #3
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******@ameri tech.net> wrote in message news:<95******* **********@news svr33.news.prod igy.com>...
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.


Nov 12 '05 #4
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
Nov 12 '05 #5
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

Nov 12 '05 #6
DELETE FROM SESSION.UNLOGGE D_TEMP WHERE some_col =
CAST(raise_erro r('78000', 'Truncate me') AS <sometype>)

Cheers
Serge
Nov 12 '05 #7
Here is the example:

DROP PROCEDURE init()
$
DROP PROCEDURE destroy()
$
DROP PROCEDURE driver_filltemp _tuned()
$
DROP PROCEDURE driver_filltemp _untuned()
$
DROP PROCEDURE filltemp_untune d
$
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_untune d()
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_erro r('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_untune d();
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
$
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5266
by: Jeff Shannon | last post by:
I'm having some difficulty getting my logging configuration set correctly. I'm using a config file (copied at end of post), with the intent of setting several loggers which write to a combination of a file, stderr (for debugging), and the NT Eventlog, but I don't seem to be getting the right combination of handlers called. The desired...
6
2818
by: Tim Fooy | last post by:
Hi all, I have the following problem. In my page i have a large <div> with tags inside it that have event handlers on them (onclick etc.). When i run div.innerHTML = moreText + div.innerHTML, either before or after the page has fully loaded, all of those event handlers are dropped: the actions for the event are not executed anymore. I...
0
369
by: Paul Reddin | last post by:
Hi, I posted a problem I was having before and got a little side tracked into a solution for that particulcar problem, but there seems to be a more general problem with CONTINUE Handlers in V8.2 Here's the symptom with a SP containing CONTINUE handlers: If I build the SP via the CLP i.e db2 CREATE PROCEDURE .... , the procedure runs...
1
1318
by: Jan van Schalkwyk | last post by:
Hi, I want to replace an event handler on an outside control with a new handler, and then later swap back to the old handler again... Is there any way to do it? (I'll have to store the old handler, but how on earth can I do it?) If it may help, what I want to do is the following: I have a GIS map, and I want to create a few tools that...
0
1656
by: R Reyes | last post by:
Hi. I'm trying to make some event handlers for buttons that are nested within datalists, however I keep getting errors trying to access them and a blank page shows w/o any real error message. How can I access these nested controls correctly? For some reason I always have to use a foreach loop to search for a control within a control. ...
0
1231
by: Oenone | last post by:
My VS2005 Edit and Continue function appears to have stopped working in my VB project. It definitely was working, but now when I run my project (a WinForms project) and break into it, I am unable to edit the source. I simply get a message telling me that I "Cannot currently modify this text in the editor. Is is read-only." I've ensured...
3
1470
by: lauralucas | last post by:
Hi I'm creating controls programatically. On postback, the events of the controls are not triggered correctly. I need to not only rebind the controls to their datasource on every load, but reassign the event handlers. but how? I should do this in the Page's Init event handler rather than in the Page_Load
5
1939
by: jrett | last post by:
I'm working on a windows forms application and some of my controls have multiple event handlers assigned to them. I haven't figured out how to add multiple event handlers via the properties window, so I do it manually. However, it seems that whenever I bring up a properties window, VS removes one of my handlers, from each control that has...
2
2047
by: Arun Srinivasan | last post by:
Is there a way to declare exit/continue handler for what ever errors that happen in a compound statement?The reason I am asking this is, I am doing more than one dcl in the statement, I may get a 'table already exists' or 'parent row not found' errors. I just want to log them in a table instead of running the SP and piping the output to a...
0
1005
by: \(O\)enone | last post by:
I'm working on some code which dynamically adds WinForms controls to a form. It's all working well but I'm having to manually call AddHandler repeatedly for each event I am using each time I add new control to hook up all the event handlers. As I already have an initial control on my form with all the event handlers applied by the...
0
7846
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8123
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7870
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8145
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5341
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3797
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1388
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1111
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.