Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: DMS system temporary tablespace

    DB2 V9.7
    OS: AIX
    Multipartitioned environment

    I have a system temporary tablespace which was created as a DMS tablespace and it spans across all the partitions [10] in the database. I noticed that the highwater mark for this tablespaces is getting changed without altering the tablespace or without running db2dart. Highwater mark is getting increased when the used pages are going up and it is getting decreased once the pages are released. Is this a common phenomenon for system temporary tablespace ?

    Any help for this will be highly appreciated

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    System temporary tablespaces should be SMS. If you chose automatic storage, they will be created as SMS by the automatic storage system. This is for performance reasons.

    When you run complex queries, DB2 uses system temporary tablespaces as a work are for sorting, etc, during the life of a query, and the high water mark will reflect that.
    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
    Jun 2009
    Posts
    272
    This is how it is created currently. DMS and system temporary so the question is about high water mark. This tablespace usage went up to as high as the total number of pages but still the high watermark is shown as 64.

    Tablespace ID = 3
    Name = TEMP1
    Type = Database managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1966080
    Useable pages = 1966048
    Used pages = 64
    Free pages = 1965984
    High water mark (pages) = 64
    Page size (bytes) = 16384
    Extent size (pages) = 32
    Prefetch size (pages) = 192
    Number of containers = 1

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This is normal. But why are you worried about the HWM of temp tablespace?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    We had application team reporting us their application was failing becuase of tablespace full issue. So we wanted to make sure if this is the tablespace which has caused it. If we had highwater mark for this, we could make sure that this tablespace has reached to its peak level at one point

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    OK..... Doesn't your db2diag.log say which tablespace it is ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Code:
    db2pd -d <dbname> -tablespaces
    I assume the info you want is the maxhwm

    Tablespace Statistics:
    Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
    0x070000009766CD20 0 94208 94204 91600 0 2604 91600 91600 0x00000000 0 0 No
    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
  •