Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: Temporary tables and unique indexes

    DB2 V8.1 on AIX V5.2

    Does anyone know of any issues with regard to unique indexes on temporary tables ?

    I have a .cpp application using ODBC for database interaction.

    I create the temp table thus :
    "DECLARE GLOBAL TEMPORARY table x_tab (x_col VARGRAPHIC(10)) ON COMMIT PRESERVE ROWS NOT LOGGED"

    The index thus :
    "CREATE unique index SESSION.x_ind on SESSION.x_tab(x_col)"

    I then execute my insert statements :
    "insert into SESSION.x_tab (x_col) values (G'x')"
    -- successful
    "insert into SESSION.x_tab (x_col) values (G'x')"
    -- successful ????? - this should fail

    "select count(*) from SESSION.x_tab"
    - this returns 2 ?

    Seems to be ignoring my unique index ?

    I created a User Temporary table space through Control Center - Show SQL was :

    CONNECT TO X;
    CREATE USER TEMPORARY TABLESPACE XUSERTEMP PAGESIZE 8 K MANAGED BY SYSTEM USING ('/home/XUSERTEMPCONTAINER' ) EXTENTSIZE 8 OVERHEAD 10.5 PREFETCHSIZE 8 TRANSFERRATE 0.14 BUFFERPOOL "BUF8K";
    COMMENT ON TABLESPACE XUSERTEMP IS '';
    CONNECT RESET;

    Any help would be most appreciated.

    Andy.

  2. #2
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Re: Temporary tables and unique indexes

    I have found the problem to this.

    When we initialy ported to DB2 it was to V7.2 - at the time this did not support indexes on temporary tables - this caused the confusion.

    Sorry to have wasted anybody's time.

    P.S - I do still have an issue with temporary tables ("Data loss with duplicate records") posted today (30/01/04). Any help on this would be good.

    Andy.

Posting Permissions

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