Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: how to create temp table in db2

    I kind of lost while reading "created global temp table" and "declared global temp table".

    It will be very appreciated if someone can help clarify the following confusions:

    1) What's difference between "created global temp table" and "declared global temp table"?

    2) What's mean "global"? if I just want to simpfy my queries(just for manually test purpose, not for application), do I need this "global"?

    3) Could I simply do something like
    "create temp table tmp1 as select * from table1"
    or "select * from table1 into temp table tmp1"?
    Since I don't want to define the columns in this temp table.


    Thanks a lot
    Jinse

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want to simplfy your queries, see CREATE VIEW.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Posts
    22
    Maybe you can not use "create global temporary table" if you do not use DB2 for z/OS.
    The declared temporary table description does not appear in the system catalog.
    It is not persistent and cannot be shared with other sessions.
    Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table.
    When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.
    **************************************
    Make progress everyday
    Mail: hoo.jimmy@gmail.com
    **************************************

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by huyuhui
    Maybe you can not use "create global temporary table" if you do not use DB2 for z/OS.
    The declared temporary table description does not appear in the system catalog.
    It is not persistent and cannot be shared with other sessions.
    Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table.
    When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.
    Based on the requirement of "wanting to simplify the queries," I think that VIEWS are probably what is needed, not temporary tables.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    One reason for using a temp table in an application (and the reason we have been using one a few years ago) is to simulate a UNION: insert the result of one SELECT into the temp table, then insert the result of a second select into the same table, then select * from the temp table.
    Two executions of the application, even concurrently, will not see each other's data.

    Why use a temp table and not a UNION in this case?
    Our reason was performance (speed) at that time; but that was DB2 v6 or before; now we don't use the temp table anymore since the UNION is slightly more performant now.

    Other reasons could be to simplify application logic, e.g. to pass data from one part of a program to an other part; or to have a "select * from temptable" without having to specify the column names with "AS .."; or to force materialisation before start a fetch cursor loop. You could think of other situations where a temp table is exactly what you need, even if a standard single SELECT solution would do. E.g., to simulate CTEs ("WITH ...") in before-v8 DB2.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Aug 2006
    Posts
    33
    Quote Originally Posted by Marcus_A
    Based on the requirement of "wanting to simplify the queries," I think that VIEWS are probably what is needed, not temporary tables.
    Sorry, I want to simplify the queries, and improve the performance since I have huge data. So is temporary table more suitable?

    Could I use "create temp table tmp1 as select .."?

    Thanks,
    jinse

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is difficult to know what you want, because you have not explained in detail what you are trying to do.

    However, it is unlikely that a global temporary table is what you want. You probably either want a view, nested table expression, or a materialized query table (or some combination of these).

    I would do some Googling or reading the manuals on the DB2 implemenation of views, nested table expressions, and materialized query tables in order to decide which one you want. Keep in mind that in other database, these same terms may have significantly different meanings, so make sure you are looking DB2 for Linux, UNIX, Windows documentation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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