Exception with sp using temporary table and cursor

GeorgeLazlo · #1 (**permalink**) 03-31-10, 19:04

Hello,

I have a very stange exception what I simply cannot solve after days of troubleshooting, searching on net, reading forums, etc...

I am using this version of DB2 server installed on a Windows XP SP3 system mainly for learning purpose:

Product identifier SQL09071
Level identifier 08020107
Level DB2 v9.7.100.177
Build level s091114
PTF IP23028

I have done the following steps:
- creating a completely new database with a schema called TEST
- creating a stored procedure with the following code:

Code:

--<ScriptOptions statementTerminator="!"/>
CREATE PROCEDURE TEST_GET ( )
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    
    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
        BEGIN
            SET l_error = SQLSTATE;
            IF SUBSTR(l_error, 1, 1) >= '5'
               AND SUBSTR(l_error, 1, 1) <= '9' THEN 
                RESIGNAL;
            END IF;
        END;

    DECLARE GLOBAL TEMPORARY TABLE SESSION."TMP_TEST_TABLE"(
        TEST_FIELD CHAR(30)
    ) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

    
    BEGIN
        DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT 
             FOR SELECT SESSION."TMP_TEST_TABLE".TEST_FIELD 
                 FROM SESSION."TMP_TEST_TABLE";
        OPEN temp_cursor;
    END;

END!

- 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.

Any help would be appreciated, thank you all!

Lazlo

sathyaram_s · #2 (**permalink**) 03-31-10, 20:58

I have 9.7.0 .. works without problems to me ...

Did you try removing the double quotes, just in case ..

Set the diaglevel to 4 and see what error you get in db2diag.log

Open a PMR with IBM if nothing else works

GeorgeLazlo · #3 (**permalink**) 04-01-10, 16:37

Hi, thank you for your answer!
I removed double quotes, error is the same... still do not work...

How can i set the diaglevel? I try to search with google and I found this:
DB2 UPDATE DBM CFG USING DIAGLEVEL 4

But when I try to run this statement in IBM Data Studio (in the same query windows as I user for creating the procedure), I get this error:

Run SQL

DB2 UPDATE DBM CFG USING DIAGLEVEL 4
An unexpected token "DBM" was found following "DB2 UPDATE ". Expected tokens may include: "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.57.86

GeorgeLazlo · #4 (**permalink**) 04-01-10, 19:39

Finally I could set up log level with DB2 Control Center. The stored procedure is still not working. I attached the full log. To be honest it is chinese for me, but the suspicous part seems:

Code:

2010-04-02-01.09.20.421000+120 I5807286H550       LEVEL: Info
PID     : 3124                 TID  : 3416        PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000         DB   : TESTDB
APPHDL  : 0-372                APPID: 127.0.0.1.1542.100401222123
AUTHID  : GEORGELAZLO
EDUID   : 3416                 EDUNAME: db2agent (TESTDB)
FUNCTION: DB2 UDB, access plan manager, sqlra_compile_var, probe:700
RETCODE : ZRC=0x80310004=-2144272380=SQLNN_E_NFOUND
          "Could not find an internal object required to process the command"

2010-04-02-01.09.20.421000+120 I5807838H868       LEVEL: Info
PID     : 3124                 TID  : 3416        PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000         DB   : TESTDB
APPHDL  : 0-372                APPID: 127.0.0.1.1542.100401222123
AUTHID  : GEORGELAZLO
EDUID   : 3416                 EDUNAME: db2agent (TESTDB)
FUNCTION: DB2 UDB, access plan manager, sqlra_compile_var, probe:750
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -204   sqlerrml: 22
 sqlerrmc: SESSION.TMP_TEST_TABLE
 sqlerrp : SQLNQ1FC
 sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 0x00000000
           (4) 0x00000000      (5) 0xFFFFFFF6      (6) 0x00000000
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
           (7)      (8)      (9)      (10)        (11)     
 sqlstate:

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.

sathyaram_s · #5 (**permalink**) 04-01-10, 20:46

OK, no need to reinstall or even if you do it won't help ;-)

-286 is the problem ... create a user temp tablespace that your user has access to (grant use of tablespace) and this problem should go away ...

your continue handler "DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING" caused the warning to be ignored, I guess ... Do you want to ignore such things ??

HTH
Sathyaram

GeorgeLazlo · #6 (**permalink**) 04-02-10, 04:52

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:

Quote:

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

It states:

Quote:

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:

Quote:

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 support!

Lazlo

sathyaram_s · #7 (**permalink**) 04-02-10, 07:04

Code:

select TBSPACE,PAGESIZE from syscat.TABLESPACES where datatype='U'

will give you a list of user temp tablespaces.

If there are any, then check your user-id has privileges documented in

IBM DB2 9.7 for Linux, UNIX and Windows Information Center

Code:

 db2 get authorizations

will show you if you have sysadm, dbadm or sysctrl.

For tablespace authority,

Code:

select TBSPACE,grantee,GRANTEETYPE,USEAUTH from syscat.tbspaceauth where tbspace in (select TBSPACE from syscat.TABLESPACES where datatype='U')

If you have to create a new one :
CREATE TABLESPACE - IBM DB2 9.7 for Linux, UNIX, and Windows

Code:

CREATE USER TEMPORARY TABLESPACE USERTEMPSPACE1 MANAGED BY SYSTEM USING ('usertempspace1')

GRANT USE OF TABLESPACE  USERTEMPSPACE1  to <your user>

grant stmt is optional if you have db/system authority. but, i guess, the user executing this app in prod will not have these higher level privileges.

HTH

Cheers
Sathyaram

GeorgeLazlo · #8 (**permalink**) 04-03-10, 01:22

Hi,

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!

Lazlo

sathyaram_s · #9 (**permalink**) 04-03-10, 07:41

Glad it worked!

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.

GeorgeLazlo · #10 (**permalink**) 04-03-10, 08:53

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech