Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    25

    Unanswered: Temporary Tables in DB2

    Hi,
    Can anyone please suggest me how to wite and use temporary table in DB2.

    I have written follwoing query:


    DECLARE GLOBAL TEMPORARY TABLE session.fred
    (dept SMALLINT NOT NULL
    ,avg_salary DEC(7,2) NOT NULL
    ,num_emps SMALLINT NOT NULL);

    select * from session.fred;

    insert into session.fred values (1,1200,45);
    insert into session.fred values (2,4560,56);
    insert into session.fred values (1,8500,96);
    insert into session.fred values (3,2699,36);
    insert into session.fred values (2,4566,25);
    select count(*) from session.fred;

    select * from session.fred;
    drop table session.fred;

    It is not shwoing me the data in final output

    Even it is shwin 0 as count(*) query.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how are you running all of this? command line? program? spufi? script?
    Dave

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Turn off autocommit.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    Rows of temporary table was deleted on commit (autocommit):

    Code:
    DECLARE GLOBAL TEMPORARY TABLE session.fred
    (dept SMALLINT NOT NULL
    ,avg_salary DEC(7,2) NOT NULL
    ,num_emps SMALLINT NOT NULL)
    on commit preserve rows;
    Lenny

  5. #5
    Join Date
    Jun 2010
    Posts
    25
    Thanks!!! It works.
    Last edited by smartcooldevil; 07-19-10 at 17:07.

Posting Permissions

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