Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Posts
    68

    Unanswered: how to create index for dynamic tables

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 11-03-03 at 02:32.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: how to create index for dynamic tables

    If my memory serves me right, from V8 onwards, DGTs can have indexes
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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)"

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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>
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jul 2003
    Posts
    30
    I agree with Satyaram. I distinct remember reading that DB2 UDB 8.1 allowed you to create indexes on (Global) Temporary table.
    -soumil

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    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

  8. #8
    Join Date
    Dec 2002
    Posts
    134
    Originally posted by grofaty
    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)
    ;

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Any tip how to use this in select statement?

    Thanks,
    Grofaty

  10. #10
    Join Date
    Oct 2003
    Location
    York UK
    Posts
    9
    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

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    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

  12. #12
    Join Date
    Oct 2003
    Location
    York UK
    Posts
    9
    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

  13. #13
    Join Date
    Nov 2003
    Posts
    68

    global tables

    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.

Posting Permissions

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