Results 1 to 4 of 4

Thread: Tempspace

  1. #1
    Join Date
    Feb 2002
    Posts
    96

    Question Unanswered: Tempspace

    Dear all,

    I am using DB2 UDB V7.2(single partition) running in AIX 4.3.
    We have created a tempspace managed by SMS. It can accomodate 50GB. Our Database size is 150GB. Sometimes i am getting the error in db2diag as "tempspace full".

    Is there anyway to monitor which query is using how much tempspace? (In order to tune it). OR is there any formula for allocating tempspace for db size 150GB?

    However, here i can increase the space for tempspace or i can add one more directory directory container(of 25GB).But i would like to know, in what basis we should increase this?

    Your suggestions plse.

    Thank you in advance.

    Regards
    Prakash

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Tempspace

    Looks like your AIX filesystem is not Large file enabled ... Therefore a file cannot exceed 2 GB ...

    Check with your system administrators ...

    BTW, are you OLTP or DSS ?

    Cheers

    Sathyaram



    Cheers
    Sathyaram

    Originally posted by cgprakash
    Dear all,

    I am using DB2 UDB V7.2(single partition) running in AIX 4.3.
    We have created a tempspace managed by SMS. It can accomodate 50GB. Our Database size is 150GB. Sometimes i am getting the error in db2diag as "tempspace full".

    Is there anyway to monitor which query is using how much tempspace? (In order to tune it). OR is there any formula for allocating tempspace for db size 150GB?

    However, here i can increase the space for tempspace or i can add one more directory directory container(of 25GB).But i would like to know, in what basis we should increase this?

    Your suggestions plse.

    Thank you in advance.

    Regards
    Prakash
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2002
    Posts
    96

    Re: Tempspace

    Our DB2 is CRM system. (for siebel application). So its doing both OLTP &DSS. But 80% OLTP functionality.

    I have written a script to monitor the filesystem space. The filesystem space utilization increases gradually and reaches 50GB.(one year ago we have removed the 2GB limit) So finally left with no space. After this i'm getting warning messages in db2diag. After this warning messages, all tempspace is released and 50GB free.

    But no query is killed or aborted here. So i could not come to any conclusion.

    Regards
    Prakash

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Tempspace

    When did this happen ? During the online day or during your DSS queries ?

    If it is during your online day, it is quiet a concern ... a 50 GB tempspace for a 150 GB online system ...

    If this is DSS, it is more acceptable, but still, your queries may need attention ...

    I don't think there is any formula to calculate ..

    But, when you see your tempspace rising, have a look at the queries which are running ...

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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