Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2005
    Posts
    14

    Unanswered: Tempspace1 is full.

    I am using Db2 9.7 ESE FP4 on AIX 7.1 TL1 SP5 using Power HA Active-Passive. I am getting following message often.

    ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on container
    "/db2/temp/mmdb/tempspace.dat" (ID "0"). The underlying file system
    is full or the maximum allowed space usage for the file system has been
    reached. It is also possible that there are user limits in place with respect
    to maximum file size and these limits have been reached.

    /db2/temp/mmdb/ is set to 100 GB. TEMPSPACE1 is a 8k page size SMS tablespace.

    I have checked ulimit -a in AIX. It shows all parameters including file is set to unlimited. On DB, it creates more sessions continuously and tempspace becames full very often and it slows down DB. After 30 minutes, once tempspace1 reaches 100%, DB restarted automatically. On SQL, most of the SQL are finetuned. This DB is used on OLTP environment.

    Please Advise how to rectify or troubleshoot further ?

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Ask your sysadmin to (temporarily) increase the size of the filsystem that contains /db2/temp/mmdb (beyond 100GB, particularly if the filsystem is shared for other purposes or other databases).
    At the same time, ask a DBA to find the SQL that's using most/largest temporary tables (db2top can help with this, study the documentation for details, other tools are available, such as monitoring, db2pd etc). Once those SQL statements are identified (or packages if mostly static SQL is in use) then analyze the access plans to see if the temporary tables can be avoided by adding indexes or other performance structures. Also check for spilled sorts, and plan how to reduce them.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am assuming that TEMPSPACE1 is a temporary tablespace (user or system). Something is growing in it to exceed the 100GB. If it is a user temporary tablespace, then someone (could be more than one) is filling a global temporary table beyond 100GB. If it is system, you probably getting one or more sort overflows or intermediary tables that fill it up.

    Andy

  4. #4
    Join Date
    Aug 2005
    Posts
    14
    Quote Originally Posted by ARWinner View Post
    I am assuming that TEMPSPACE1 is a temporary tablespace (user or system). Something is growing in it to exceed the 100GB. If it is a user temporary tablespace, then someone (could be more than one) is filling a global temporary table beyond 100GB. If it is system, you probably getting one or more sort overflows or intermediary tables that fill it up.

    Andy
    TEMPSPACE1 is a system temporary tablespace. It contains SQL*.TDA file. Whenever there is a background OS process like Backup or copying huge files is happening, This directory is having lot of SQL files piled up and reach 100 GB.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to figure out what is creating stuff that is filling up the filesystem, and either eliminate it, spread them out if there is a combination of things affecting this, or make the FS larger.

    Andy

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    did you find your offending SQL statement?
    Dave

  7. #7
    Join Date
    Aug 2005
    Posts
    14
    I have find few SQL Statements and optimized it. But still the same problem exists. During certain time of office hours ( For e.g morning 9 am - 10 am), No of concurrent sessions increasing rapidly and makes tempspace1 full. Also once tempspace1 is full, it makes database to restart automatically.

    All the time, this DB has 350 db sessions with 4-10 concurrent sesssions. During 9 am - 10 am every day, it goes to 50 - 200 concurrent sessions and no of db sessions goto 1200 sessions. When no of concurrent sessions goto 200 and tempspace is full, database restarts automatically.

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You first posted this issue on 05/Feb, and today is 16/feb - seems a long time to me to have such a simple issue unresolved.

    A good DBA with help of a sysadmin should be able to workaround this quickly for a production environment by increasing the FS size. This was suggested twice already, but you did not respond.

    Separately, If using WLM, DBA should be able to limit the temp space via appropriate thresholds, and choose appropriate action if the threshold is violated.

    Otherwise for non-WLM, and non-production environment, DBA can limit temp space use via DB2_OPT_MAX_TEMP_SIZE and thus help to find SQL statements that need optimising (the statements will get warnings, the apps should report these warnings). This can help if the workload on non-prod is similar in functionality (not scale) to the prod environment.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2mor View Post
    You first posted this issue on 05/Feb, and today is 16/feb - seems a long time to me to have such a simple issue unresolved.

    A good DBA with help of a sysadmin should be able to workaround this quickly for a production environment by increasing the FS size. This was suggested twice already, but you did not respond.

    Separately, If using WLM, DBA should be able to limit the temp space via appropriate thresholds, and choose appropriate action if the threshold is violated.

    Otherwise for non-WLM, and non-production environment, DBA can limit temp space use via DB2_OPT_MAX_TEMP_SIZE and thus help to find SQL statements that need optimising (the statements will get warnings, the apps should report these warnings). This can help if the workload on non-prod is similar in functionality (not scale) to the prod environment.
    In addition, you know when it happens every day. You should be actively monitoring the system at that time to see where the anomalies are coming from and what they are doing.

    Andy

  10. #10
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Hi,

    There might not be any problem with individual SQLs. The problem might be with application. We used to have a web application which used to open new connection for each transaction, open several cursors and randomly leave cursors and connections open due to poor coding practices. If the cursor uses temporary table space, it will not release it until cursor/connection is closed. With several thousand open cursors, you can fill up temporary table space. You might want to check whether this is the cause for your current situation.

    ..Satya..

  11. #11
    Join Date
    Aug 2005
    Posts
    14
    Thanks to all your replies.
    FS Size increased from 30 GB to 100 GB and then 200 GB. There is no use as 200 GB is filling up. Also once db reaches 50 concurrent sessions, db slows down and Application users feel very slow on getting data.

    On SQL Snapshot, few sql taking more time to process due to concurrent execution. Optimizing those SQL and creating threshold. On slowness issue, is there anything to troubleshoot further ?

  12. #12
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What are the facts regarding change-history?
    When did this problem symptom start?
    Was there a time when the application was stable and did not fill sys temp space? (check historical db2diag.log files and notification logs).
    What changes to the application or db2 or system were made just before the symptom started?

    If the problem began after a change to the application, from a DBA viewpoint you can work-around the issue by adding more temp space until the application team resolves the issue - even if you have to allocate triple or more space temporarily (disk storage is cheap compared to costs of outages). You can also insist on load-testing on a non-production environment that limits temp space usage as previously suggested. You can use monitoring to get more insight into application behaviour, particularly if cursors are being left opened or transactions failing to commit frequently enough.

    If the problem begain after a change to db2 or to the system, then research to discover what to back out or whether a subsequent fixpack addresses the issue.

    For general performance tuning in DB2, there are multiple sources of information. Careful study of the output of the monreport procedures taken during the time when the syptom is starting and peaking can be revealing. Having Appropriately-skilled resources is a pre-requisite, as ever.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Over on Planet DB2 last week there was a blog entry that you may find useful. http://db2commerce.com/2015/02/12/db...package-cache/
    you can play around with that SQL statement to maybe get stuff that was sorting, that reading too much, that was......and that will point out what it was that you couldn't find.
    Dave

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The MON_GET_PKG_CACHE_STMT () table function returns, among other metrics, POOL_TEMP_DATA_L_READS and POOL_TEMP_INDEX_L_READS, which can help you find the statements that use the temporary tables most actively.
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Aug 2005
    Posts
    14
    Thank you for all your replies. This issue is resolved by decreasing SOFTMAX and optimizing SQL.

Tags for this Thread

Posting Permissions

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