hi

how to create index for dynamic tables.
we have one db2 server on linux.
total tables 200+.
total data of 3 yerars.
some table it will contain more that 3 laks records.
we are using "DECLARE GLOBAL TEMPORARY TABLE" concept
for giving reports fastly.
but still reports will give out put very very slow.
some reports will talke 2 or 3 hours.
because on live server we are running reports.
some tables will locked exceptly while doing online bill transctions.
that time we are using same tables in report s. i think
that's it is very slow.

pl give me solution how improve our system perpormance.

pl give me any good sagesion to improve or maintain database.

i will give u what information u want for improve my system .

pl give me reply as early as poissible.

i will give u ANY information FROM MY SIDE.

PL TELL ME.

Hi,

Please specify the operating system and db2 version and fixes. Please also read thread Must Read before posting for more tips.

According to my knowleadge indexes can't be used on temporaly tables. But I have heard something from IBM that they will implement this feature in near future.

Please read the performance thread already discussed in this forum. You can also post the "reply" to above thread. You can also search this forum, because many many things have already been answered.

You can try to specify summary tables. New term in DB2 version 8 for summary tables is materialized query tables (MQT).

Hope this helps,
Grofaty

If my memory serves me right, from V8 onwards, DGTs can have indexes

I am not sure about the answer, but I found this in the SQL Reference V8 for DECLARE GLOBAL TEMPORARY TABLE:

"Restrictions on the Use of Declared Global Temporary Tables: Declared
Global Temporary tables cannot:

– Be specified in an ALTER, COMMENT, GRANT, LOCK, RENAME or
REVOKE statement (SQLSTATE 42995).
– Be referenced in a CREATE ALIAS, CREATE FUNCTION (SQL Scalar,
Table, or Row), CREATE INDEX, CREATE TRIGGER, or CREATE VIEW
statement (SQLSTATE 42995).
– Be specified in referential constraints (SQLSTATE 42995)."

In the DB2 for OS/390 V7 SQL guide, it only says that an ALTER INDEX cannot be used, which implies that CREATE INDEX is OK:

"In addition, do not refer to a declared temporary table in any of the following statements.

ALTER INDEX
ALTER TABLE
COMMENT ON
CREATE ALIAS
CREATE FUNCTION (TABLE LIKE)
CREATE PROCEDURE (TABLE LIKE)
CREATE TRIGGER
CREATE VIEW
GRANT (table or view)
LABEL ON
LOCK TABLE
RENAME TABLE
REVOKE (table or view)"

mmmm ...

What's New in V8 says

Enhancements to declared global temporary tables (DGTTs) include:
v Index support: the ability to create indexes using the CREATE INDEX
statement.
v Undo logging, to support the rollback of data changes to DGTTs.
v Statistics support: improved performance as a result of using the
RUNSTATS command to update statistics about the physical characteristics
of a temporary table and its associated indexes.



<quote>
"Restrictions on the Use of Declared Global Temporary Tables: Declared
Global Temporary tables cannot:

– Be referenced in a CREATE ALIAS, CREATE FUNCTION (SQL Scalar,
Table, or Row), CREATE INDEX, CREATE TRIGGER, or CREATE VIEW
statement (SQLSTATE 42995).

</quote>

I agree with Satyaram. I distinct remember reading that DB2 UDB 8.1 allowed you to create indexes on (Global) Temporary table.
-soumil

Hi,

Can somebody post some very simple sample how to do this.
Just simple sample of creating temporaly table and simple index on it.

Thanks,
Grofaty

declare global temporary table test ( id int not null) not logged with replace
;
create unique index session.u_test on session.test(id)
;

Hi,

Any tip how to use this in select statement?

Thanks,
Grofaty

Two points to be added to be remember while declaring temporary table
1. To declare global temporary table you need to have user temporary tablespace before hand. i.e you have to create user temporary tablespace
2. Schema for temporary tables has to be session.
so if I modify chuzhoi's SQL it will look like following

declare global temporary table session.test ( id int not null) not logged preserve rows on commit in mytempsapce

Preserve rows on commit is required else rows are lost even on the same session.

A normal select will work like
select * from session.test

Thanks

Hi,

1. I created the user temporaly tablespace with nam mytempsapce.
2. I executed: declare global temporary table session.test ( id int not null) not logged preserve rows on commit in mytempsapce

DB2 returns error:
"DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "preserve rows on commit in mytempsapce" was
found following "not null) not logged". Expected tokens may include:
"<space>". SQLSTATE=42601"

Any idea?

Thanks,
Grofaty

Sorry Grofaty,
I gave you the wrong syntax. Correct sytax is
ON COMMIT PRESERVE ROWS

Complete SQL would be like

DECLARE GLOBAL TEMPORARY TABLE session.temp1
LIKE employee
ON COMMIT PRESERVE ROWS
NOT LOGGED
IN mytempspace

Regards

hi to all

DECLARE GLOBAL TEMPORARY TABLE session.temp1
LIKE employee
ON COMMIT PRESERVE ROWS
NOT LOGGED in mytablespace.

how to describe above table .

i want to see list tables of prarticular tablespace.

if i give like thiis
list tables -> all tables will display
i want to see on mytablespace tables.