Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: Global temporary table

    Hi ,

    I am in the process of writting a stored procedure which will insert some data into the global temporary table and then i will issues a simple select to show the data on the page.However, I created the global temporary table, inserted a row into it to see if its properly created and issued select statement.

    DECLARE GLOBAL TEMPORARY TABLE TESTTAB1(AGENT VARCHAR(30),CLOSECOUNT integer) on commit preserve rows;

    insert into session.testtab1 values('sach',3);

    select * from session.testtab1;

    The result was fine.

    DECLARE GLOBAL TEMPORARY TABLE TESTTAB1(AGENT VARCHAR(30),CLOSECOUNT integer) on commit preserve rows
    DB20000I The SQL command completed successfully.

    ------------------------------ Commands Entered ------------------------------
    insert into session.testtab1 values('sach',3);
    ------------------------------------------------------------------------------
    insert into session.testtab1 values('sach',3)
    DB20000I The SQL command completed successfully.

    ------------------------------ Commands Entered ------------------------------
    select * from session.testtab1;
    ------------------------------------------------------------------------------
    select * from session.testtab1

    AGENT CLOSECOUNT
    ------------------------------ -----------
    sach 3

    1 record(s) selected.


    But when I am writting into my procdure something like,

    delete from session.TESTTAB1; or

    INSERT INTO session.TESTTAB1 SELECT OPTR.PYUSERNAME||'('||AP.PPOP||')' "AGENT",
    COUNT(DISTINCT(CASE WHEN AP.PPOP IS NOT NULL THEN AP.PZINSKEY ELSE NULL END)) "CLOSECOUNT"
    FROM
    AP_WORK AP,PR_OPERATORS OPTR
    WHERE AP.PPOP IS NOT NULL
    AND DATE(AP.PPDATE)
    BETWEEN DATE(FromDateMM||'-'||FromDateDD||'-'||FromDateYY) AND DATE(ToDateMM||'-'||ToDateDD||'-'||ToDateYY)
    AND AP.PPOP=OPTR.PYUSERIDENTIFIER

    GROUP BY OPTR.PYUSERNAME||'('||AP.PPOP||')';

    it gives me error saying TESTTAB1 is an undefined name.Acoording to what I have read,global temporary tables are created in 'session' schema by default.So why can't I access it as 'session.TESTTAB1' inside the procedure?

    How can I refer to the declared global temporary table in that case?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The DGTT must exist in the session before the SP can be created with that session.

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are on 9.7 , check if CREATED GLOBAL TEMPORARY TABLE suits your needs.

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2009
    Posts
    150
    You have to DECLARE Global Temporary Table inside of SP, not outside....

    SP is not a subprogram, you have to know it.

    Cara
    Last edited by DB2Plus; 02-08-10 at 08:11.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The other option is to create the temp table outside the procedure and then use dynamic SQL inside the procedure itself. The thing is that the CREATE statement is analyzed and if DB2 finds a temp object and cannot guarantee that the object always exists (as is the case if the temp table is created inside the procedure), it is an error. If you can guarantee the existence in your transactions, dynamic SQL is the way to hide this from DB2.

    p.s: Executing a procedure is like a sub-program. The definition of one is not.
    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
  •