Results 1 to 4 of 4

Thread: temp tables

  1. #1
    Join Date
    May 2004
    Location
    New York
    Posts
    248

    Question Unanswered: temp tables

    Maybe someone can shine some light on this for me.
    running Informix 7.31 TC5 on WIN NT

    I have a few stored procedures that create TEMP tables,
    after the procedure exits the TEMP table still hangs out in the tempdbs, but I can not select from it. So the next time the procedure runs and tries to create the TEMP table it fails. The tables are created with NO LOG.
    The way i found that the tables are still in the dbspace is by doing this query

    database sysmaster;

    select dbinfo( "DBSPACE" , pe_partnum ) dbspace,
    dbsname[1,10],
    tabname,
    pe_phys start,
    pe_size size
    from sysptnext, outer systabnames
    where pe_partnum = partnum
    order by dbspace, start;

    And looking at tablenames, the TEMP’s are still in the tmpdbspace.

    If I run oncheck -pe there is nothing in the TEMP dbspaces

    If I reboot the server everything is fine.

    Has anybody seen anything like this?
    Last edited by artemka; 05-25-04 at 11:29.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi artemka,

    This is correct, the temps tables, created with "WHIT NO LOG" or not, remain during life of session.
    Into the stored procedure can you error handler whit "ON EXCEPTION".
    The table only can access the owner of session.

    Gustavo.

  3. #3
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    I know that the tables are created for the life of the sesion.

    for some reason they sit in sysmaster database after the sesion finishes.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi Armtenka,

    Please, run the following scripts for look temp tables and owner's.
    __________________________________________________ ____
    {
    dbaccess sysmaster 2>/dev/null << EOF
    SELECT substr(n.dbsname,1,15) type,
    substr(n.owner,1,15) users,
    substr(n.tabname,1,18) table,
    substr(dbinfo('DBSPACE', i.ti_partnum),1,15) dbspace,
    round(i.ti_nptotal*2,0) kb
    FROM systabnames n, systabinfo i
    WHERE (bitval(i.ti_flags, 32) = 1
    OR bitval(i.ti_flags, 64) = 1
    OR bitval(i.ti_flags, 128) = 1)
    AND i.ti_partnum = n.partnum
    into temp ggtmp with no log;
    SELECT type[1,12], users, table, dbspace[1,12], kb
    from ggtmp
    order by 1,2,3,4
    EOF
    } |more
    __________________________________________________ ____

    Gustavo.

Posting Permissions

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