Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: DB2 Temporary Table existence problem

    Hi,
    I'm having some problems with temporary table in DB2, web application, dll and stored procedure.
    Here is the environment:
    Machine1 - BD Server
    Machine2 - Web (IIS and web application).

    During the execution of the web application in a C# DLL, it calls a Delphi DLL (that creates all the temporary tables) and calls a stored procedure (sp1 who calls sp2 e it goes on). Inside the sp, a Delete statement from the temporary table is executed, but when it will be done an error occurs saying that the temporary table doesn't exists.

    Does anyone have any idea why it's happening? Could it be some session problem (the creation of the temporary table occurs on one session inside the web machine, and the execution of the SP occurs in another session on the BD machine) ?

    Does DB2 have any "global" temporary table like MS SQL Server (represented by ##), and if it exists, it will help me??

    Thank you.

    []s

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are correct. it is a session problem. The temporary tables of one session cannot be used by another. No there is no structure outside a normal persisted table that all sessions can access.

    Andy

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    But even if I'm on the same connection? Or the execution of the procedure "looks like" another session/connection?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Declared temporary tables (created by DECLARE GLOBAL TEMPORARY TABLE) are accessible only on the same connection that they are created in. If two separate connections each issue the exact DECLARE GLOBAL TEMPORARY TABLE statement, they are each accessing their own object and cannot even see the other connection's object.

    So if you are creating a temp table like this in one session and another session cannot see it, then there are only two possible answers. 1) the sessions are on different connections or 2) the temporary table was dropped, either implicitly through a disconnect or explicitly with a drop table command. If it was dropped implicitly, then you are using a different connection anyway.

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let's clarify the term "connection" here. Temp tables are bound to the connection _to_ DB2, i.e. the connection that the web server establishes to the database. It is not the connection from your web client to the web server. With that in mind, you will have to make sure that the web server doesn't use multiple different threads to answer a web client's request because each thread will (typically) have a different DB2 connection.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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