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

If you want to simplfy your queries, see CREATE VIEW.

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.

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.

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

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.