Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Angry Unanswered: WARNING: temporary space tempdbs is full

    We use IDS 9.4 on AIX. We recently discovered that products such Crystal Reports and MS Access are not dropping tables created in tempdbs. This only came to light when a particularly large table was being created (300m rows) 1.9gig on a 2 gig tempdbs. This effectively filled up the tempdbs.

    Can anyone provide details of how to identify the table owners? The database associated is not SORTTEMP but a recognisable database.

    Also can anyone give me commands to clear these tables without having to restart IDS as we did?

    Can I drop and recreate tempdbs without stopping IDS?

    Please note that there were no session connected to the database and therefore the calling process orphaned the tables instead of dropping them.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    http://www-1.ibm.com/support/docview...=utf-8&lang=en
    How to find out the temporary tables that are currently created

    And check your parameter DBSPACETEMP in your ONCONFIG... create more than on dbspace to Temp...

    The temp tables must be dropped by application... if they don't do it ...when conection is closed the IDS automaticly droped and clean the temp tables...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Jul 2003
    Posts
    23

    Smile

    Thanks for the link. This part answers what I want.

    When I ran the query it gave me the following.

    tabname dbsname owner db_with_log dbspace created table_using_log num_usedpages num_pages
    ---------- ---------- -------- -------------- ---------- ---------------- ------------------ ---------------- ------------
    _temptable moisys informix Y tempdbs 23/07/2007 09:40 N 118646 118960

    The process has been killed but has left the table behind. The only way we know how to remove this is to restart Informix.

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    what process? the application? or the user thread into informix??

    check if the session still open ... if yes, kill them with onmode -z

    maybe this can be a bug...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Jul 2003
    Posts
    23

    Smile

    I'm killing the session using AGS Server Studio. The session was started by a Crystal Report that obviously decided that it needed a temporary sort file. This file filled our tempdbs and hence caused problems in IDS. I then terminated the session but it did not clear up the file in tempdbs.

    I have logged this as a bug with IBM but was hoping for help from any quarter in the meantime.

    Your query has been a great help as it will identify users who are leaving tables behind. We will change the report login ID's so we can track failed temp table clear ups.

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    My suggestion... for now... is create another temp dbspace and set in DBSPACETEMP in ONCONFIG... OR create this temp dbspace and change the enviroment from Crystal Report , setting the DBSPACETEMP variable to this dbspace, in this way only the Crystal will be use this temp dbspace and not affect another users...


    Sorry my poor english...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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