I have done the following steps:
- creating a completely new database with a schema called TEST
- creating a stored procedure with the following code:
CREATE PROCEDURE TEST_GET ( )
DYNAMIC RESULT SETS 1
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
DECLARE GLOBAL TEMPORARY TABLE SESSION."TMP_TEST_TABLE"(
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT SESSION."TMP_TEST_TABLE".TEST_FIELD
- running the stored procedure
- the result is:
TEST.TEST_GET - Run started.
Data returned in result sets is limited to the first 500 rows.
Data returned in result set columns is limited to the first 100 bytes or characters.
TEST.TEST_GET - Calling the stored procedure. TEST.TEST_GET - Exception occurred while running:
A database manager error occurred.SQLCODE: -727, SQLSTATE: 56098 - An error occurred during implicit system action type "5". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "SESSION.TMP_TEST_TABLE".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.57.86
TEST.TEST_GET - Roll back completed successfully.
TEST.TEST_GET - Run failed.
The origin of the problem: in a more complex database I have several stored procedures returning result sets using global temporary tables and cursors, i have the same structures (declaring temporary table, doing something (for e.g. filling with values), and returning a cursor refers the temporary table). All of these procedures worked, except one.
I couldn't localize the problem, the structure of the non-working stored procedure was absolutely the same as a working one. So step-by-step I started to strip specific parts of the wrong procedure; I created it in a completely new database, cut statements one by one, until a very simple code remained, what still causes this stange error.
So at the end I had this very simple procedure you can see above, and I simply do not understand why it causes an error. It does absolutely nothing, it should return an empty resultset and that's all.
My next step would be to reinstall the whole DB2 server and try the same absolutely from scratch... but I hope there should be some kind of explanation and solution to my problem.
I will try to re-install the whole DB2, I hope it will help, but I am absolutely annoyed; in a productive environment it mustn't be a solution; this stored procedure code is absolutely a simple code, should work without any kind of errors (as it worked for sathyaram_s); I simply cannot predict when it will happen again even after a re-install if I do not know the exact reason.
At first thank you very much for your help, cause I spent a lot of time to troubleshoot this (due to lack of my DB2 knowledge).
Sorry for the beginner questions (I am new to DB2), but I still have some:
create a user temp tablespace that your user has access to (grant use of tablespace)
1. how I can do it? (exact SQL statement)
2. why it is necessary? I had a more complex database with stored procedures using the same structures (returning cursor for a created temporary table), all procedures are working well, when I tried to do a new very similar one, I get this error; how this tablespace went wrong or disappeared? I read this article: DB2 Universal Database
Stores declared global temporary tables. Note that no user temporary table spaces exist when a database is created. At least one user temporary table space should be created with appropriate USE privileges, to allow definition of declared temporary tables.
I never created this in the past, and my other procedures in the original database work perfectly. I guess this tablespace existed in the past, but then how it disappeared? And if its disappeared, how it is possible that in my original (not test) database, other stored procedures using temporary tables work perfectly, just one specific causes problems?
And an answer:
your continue handler "DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING" caused the warning to be ignored, I guess ... Do you want to ignore such things ??
I migrated an MSSQL database with IBM DB2 Migration Toolkit, this tool pasted this code to all stored procedures; to be honest I just recognized that this relates to some kind of exception handling, but until everything worked fine I was not interested in what it does really; I relied on that the Migration Toolit works fine and put the necessary code in place.
Thank you for your help so far, it solved my problem in the test database, and with this information I solved a similar problem in my original database as well. Although the root cause of the problem was not exactly what can be figured out from my test case described in this thread, but with your help I could solve the original problem as well.
The situation was the following:
- In the original database I had a user temporary table space SYSTOOLSTMPSPACE
- I created a stored procedure what declared a global temporary table what was too wide (on wide I mean the width of a row in bytes)
- The table has been created but some columns (8-10) missed from its end (I got no error or warning during creation ?! or it was just ignored by the statements you pointed eariler "DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING") ?!
- Other parts of the procedure tried to update the missing fields, I guess that leaded to the exception in the original environment, not directly the lack of user temporary tablespace
- When I created a testdb and tried to simplify the original stored procedure to a very simple one what still causes the same exception I run into the problem what you solved
- Then I created the same schema in the testdb as I have in the original db and tried to run the original stored procedure, the exception occured again, then I stripped the procedure line by line when I recognized that the global temporary table was not fully created, some fields existing in the declaration was not created
- My guess was the there are some limitations concerning rowsize of temporary tables, I googled some information concerning this
- I created a new user temporary table space in the original db with pagesize 32KB (rather than 4KB)
- I modified the stored procedure code to explicitly declare the global temporary table in that new tablespace
And finally, now it works properly.
Thanks again, I learned a lot about temporary table handling in DB2!
As you are 9.7, you may consider created global temporary table too .. In this case, you do not have to declare the table in the stored proc, but you can have it pre-defined and the defn is stored in the system catalog tables.
Visit the new-look IDUG Website , register to gain access to the excellent content.
Thx, it depends on what is the penetration of different versions in productive environments, I would like to be compatible with earlier versions as well (if they are still significantly penetrated). For e.g. the MSSQL codes are compatible from MSSQL 2000.