Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    54

    Unanswered: temporary tablespace file system full

    I am using DB2 7.2 on AIX, I have all temporary tablespaces on separate folder, suddenly the folder went to 100% and all developers felt hung connection.

    Two dead locks were there I tried to kill them but I nothing happened, I tried to force application all, but it did not succeed, and tried db2stop force it hung, finally from aix I killed all process for this particular instance and issued db2stop and db2start it worked, now I checked the temporary tablespace folder it is only 4% used.

    Actually what could be the problem and what is the correct way to fix this.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Temporary tablespaces are used by DB2 during an SQL statement. When the SQL statement is finished, the space is released. When DB2 is shutdown, all temporary tablespace allocations should be released.

    Based on your queries (or utilities) that are running on your system, you need more room for temporary tablespaces. You should have enough space in your temporary tablespace directory (obviously SMS tablespaces in your case) that is at least as large as the largest table, and more if you can afford it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    You also want to check the ulimits for the db2 id that your using to start the instance, also make sure the file system is large file enabled. If its a SMS type file system, increase the size of the directory.
    David Quigley

  4. #4
    Join Date
    Dec 2004
    Posts
    54
    I have 1.2 GB space for the particular file system. temp space is SMS.

  5. #5
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    IBM recommends atleast 1.5 times your largest tables, this way it would big enough for sorts/reorgs/etc. And if you have allot of users on your system at the same time, doing any work that requires temporary space, you should increase it. That seems small to me. Most of our system, even ones with less than 100 gig of data have atleast 10 gig of swap space and our largest one has over 100 gig. We have seen it use all of it at times. Its a trade off, disk space vs Cost.
    David Quigley

Posting Permissions

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