Hi,

What does it mean when one says that Global Temp Tables are created per session only? If I have a user logging in to an app, and two different screens call procedures that reference the same temp table, can this be an issue? I have ON COMMIT DELETE ROWS as a part of the definition.

Also, I have DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET errorCode = SQLCODE;

in the sproc to continue executing if the temp table has already been created.

Am just trying to understand what per session means? Also, what happens to the temp tables when another user logs in at the same time and calls the sproc?

Thank you!

A Global Temporary Table is only valid in the DB connection that created it. If two separate connections create a tamp table of the same name, they are two different object.

If you have one connection and are referencing it from multiple places, then you have a problem.

Andy

Physically, they are different tables (if not from the same connection), so each of the two users will have a private instance of that temp table, not visible to the other user.

thanks, that helps a lot... db2sysc goes to 100% when we have too many users logging in and accessing these sprocs...i couldn't find any scenario where the code just 'hangs'... i looked in the db2diag.log file and found this -- i remember seeing this before when i was trying to set up a java based sproc.. and then i just set the DB2_FMP_COMM_HEAPSZ=7680. Is the problem that I set it too low.. or could it really just be the sproc code even though I think it's ok. Thanks!

2008-09-04-20.01.06.111359+000 I293174548G495 LEVEL: Severe
PID : 28776 TID : 4096042688 PROC : db2agent (ENERGY1)
INSTANCE: nrg1 NODE : 000 DB : ENERGY1
APPHDL : 0-296 APPID: D1AC9624.KCE0.0960E4200105
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:60
RETCODE : ZRC=0x8B0F003B=-1961951173=SQLO_NOMEM_UND
"No memory available in 'Undefined Heap'"
DIA8300C A memory heap error has occurred.

2008-09-04-20.01.06.111676+000 I293175044G448 LEVEL: Severe
PID : 28776 TID : 4096042688 PROC : db2agent (ENERGY1)
INSTANCE: nrg1 NODE : 000 DB : ENERGY1
APPHDL : 0-296 APPID: D1AC9624.KCE0.0960E4200105
FUNCTION: DB2 UDB, routine_infrastructure, sqlerAddFmpToPool, probe:20
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0xFFE0AE74 : EEFB FFFF ....

2008-09-04-20.01.06.116712+000 I293175493G642 LEVEL: Error
PID : 28776 TID : 4096042688 PROC : db2agent (ENERGY1)
INSTANCE: nrg1 NODE : 000 DB : ENERGY1
APPHDL : 0-296 APPID: D1AC9624.KCE0.0960E4200105
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:70
MESSAGE : Insufficient memory available for IPC communication with the db2fmp
process. Use the DB2_FMP_COMM_HEAPSZ registry variable to adjust the
amount of memory available for fenced routines.
DATA #1 : Hexdump, 4 bytes
0xFFE0ABC8 : 0000 0000

You can increase it. At your current setting, you are using only 30 MB of memory. To what value becomes the question. And that really depends on what your environment is.

Andy

Thanks Andy...this is what I get if I use top -- any idea what I could set it to by looking at this output? Thanks! Also, I gave this command --

db2 "SELECT procname, fenced FROM syscat.procedures"

which tells me that the above procedures that are causing these problems are not fenced. But the db2diag.log output says this --

MESSAGE : Insufficient memory available for IPC communication with the db2fmp
process. Use the DB2_FMP_COMM_HEAPSZ registry variable to adjust the
amount of memory available for fenced routines.

It just seems like a contradiction since these part. routines aren't fenced. We do have other procs that are fenced... is it possible that the problem lies somewhere else? is there anything else I can test?


top - 16:08:01 up 256 days, 17:26, 3 users, load average: 0.00, 0.01, 0.06
Tasks: 623 total, 1 running, 622 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8126648k total, 8094760k used, 31888k free, 0k buffers
Swap: 7815580k total, 5092k used, 7810488k free, 6481480k cached

It looks like your system memory is about all used up. If you increase this, you will have to also decrease something else. You may want to figure out what SP is causing the messages in your db2diag.log table. Then depending on how critical the SP is, make the appropriate actions.

Andy

I'm just wondering why this is an issue.. let's suppose I have two stored procedures Proc1 and Proc2. Proc1 and Proc2 both call the stored procedure 'SubProc'

In SubProc, I have something like this ---

CREATE PROCEDURE SUBPROC ( IN param1 INTEGER, IN param2 INTEGER)
RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL

BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE errorCode INTEGER;
DECLARE.....
DECLARE....

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET hasNext = 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET errorCode = SQLCODE;

DECLARE GLOBAL TEMPORARY TABLE TEMPVALS (
COL1 INTEGER,
COL2 INTEGER,
) NOT LOGGED IN TEMPTBLSP;

.................
.................
.................



If I have one connection and Proc1 and Proc2 are called via separate web pages.. because I have a continue handler for sqlexception and since it's by default ON COMMIT DELETE ROWS... why would there be any issue? I can call both procedures just fine from the command line in one connection. Thanks!

What I meant is that it would be a problem if you actually care about what is in the GTT.

If Proc1 called SubProc and then later wanted to use the GTT, but somewhere in between PRoc2 is called, then you would have a problem. If the GTT is no longer needed after the call to SubProc, then you are fine.

I would suggest that you add the clause "WITH REPLACE" to the declare global temporary table command so that you do not have to worry if it already exists.


Andy

Got it...in our app, only one ie Proc1 or Proc2 can be called since they are accessed on different screens.

So if I'm on screen1.. Proc1 is called... it creates the GTT, puts data in it, returns data..and deletes rows..

Then I go to screen2.. Proc2 is called.. it attempts to create the GTT.. but fails and the sproc continues execution with the continue handler for sqlexception.. then puts entries in the GTT..returns the data and deletes rows..

Also, the GTT is no longer needed after the call to SubProc in each case.

That sounds OK, I would just make one change and that is use the "WITH REPLACE" clause like I stated earlier. That way your exception handler can handle proper exceptions and not this, which, in my opinion, is not an exception--just a situation.

Andy

Thanks Andy!