Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    18

    Unanswered: Which system tables keeps information of TABLESPACE CONSUMTION?

    Hi,

    I want write a script to reflect the status of tablespace consumed by the database . Can anyone please tell me which SYS table contains the information of free pages and used pages.? I am aware of table SYSTABLESPACES but it contains only static information.
    Also it would be great if anyone can tell where(in which sys tables) i can see log space consumption and temp space consumption, and max agents reached details/

    Thanks
    Kunal

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What you need is snapshot table functions (e.g. snap_get_db(), snap_get_tbsp())
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by kunal
    I am aware of table SYSTABLESPACES but it contains only static information.
    That doesn't answer your question (n_i already mentioned snapshot functions), but you should never use the SYSIBM.SYS* tables on DB2 for LUW (except SYSIBM.SYSDUMMY1). Those tables are not documented and may change at any time. Instead, rely on the catalog views in the SYSCAT schema.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2006
    Posts
    18
    Thanks . I can see that there is SYSCAT schema which contains SNAP views which will serve the purpose to some extent.

    But i can see that it will give me infomration only AT THE MOMENT snapshot is taken . I will not get information like what is the resourse consumed over the batch window of say 4 hrs i.e what is total memory consumed and whats MOMENTARY MAX limiit reached..etc .from where would i get that statistics over period?

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    if you need to study tablespace behaviour over a period create Event Monitor

    CONNECT TO DBNAME ;
    CREATE EVENT MONITOR as FOR TABLESPACES WRITE TO TABLE BUFFERSIZE 4 BLOCKED MANUALSTART ;
    CONNECT RESET;

    this will create a table tablespace_as where u can see the details
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    May 2006
    Posts
    18
    Thanks .It was great advice .

    But this will be enough for monitoring TABLESPACE consumption . Could you please also tell similer thing to monitor log space and temp space consumption?

    Thanks
    Kunal

  7. #7
    Join Date
    May 2006
    Posts
    18
    Hi ,
    I have created event monitor ...started it and stopped after some time...now when i want to analyse the data ... i dont see any data in the table TABLESPACE_AS...any ideas whats gone wrong?

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kunal
    any ideas whats gone wrong?
    Nothing. Except, may be, the fact that you didn't read the manual, which says that "the event monitor records a table space event for each table space when the last application disconnects from the database".
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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