Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Global Temporary Tables

    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!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2user
    what happens to the temp tables when another user logs in at the same time and calls the sproc?
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Dec 2002
    Posts
    123
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Dec 2002
    Posts
    123
    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
    Last edited by db2user; 09-11-08 at 20:04.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Dec 2002
    Posts
    123
    Quote Originally Posted by ARWinner
    If you have one connection and are referencing it from multiple places, then you have a problem.
    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!

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  10. #10
    Join Date
    Dec 2002
    Posts
    123
    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.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  12. #12
    Join Date
    Dec 2002
    Posts
    123
    Thanks Andy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •