Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49

    Unanswered: Tablespace size verification

    Hi,

    My userspace1 tablespace size is around 11.8 GB. out of which 10.8GB is full. Only 1 GB space is remaining.

    Till now i have record of only 2.5 GB data (TABLE and INDEX).

    I want to know what has occupied rest of the space.

    Request help.


    DB2 V9.5 FP15
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in doc
    Number of usable pages = 3114304
    Number of used pages = 2837664
    check in sysibm.systables (tbspace) which table has been created in this tablespace
    or run db2look and find this tablespace for object definitions
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Hi,

    I have used db2 control center to calculate size of tables and its indexes.. Still max to max it upto 2.5 GB. Where i am going wrong???

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    maybe free space on pages.....
    are stats up-to-date when you calculated size
    usage is 10g
    have you checked db2look ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    How can i check HWM of objects in a tablespace?? Is it possible??

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    db2pd -tablespaces

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by shore View Post
    DB2 V9.5 FP15
    The latest fixpack for V9.5 is FP8.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Ohh i am sorry.. Its FP 5.

  9. #9
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    I want to check HWM of objects in a tablespace not HWM of tablespace.

    Please help me out.

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there is nothing like hwm of objects
    you have npages in catalog indicating datapages for tables
    how can we help if you don't describe in detail the problem or what you need
    please help...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    See, Userspace1 tablespace size is 11.8GB out of which db2top tool is showing me 10.8GB is full. I want to calculate which object has occupied how much space.

    I have individually calculated table size and index size which are in userspace1 tablespace using control center. but the total of table data and index data is something around 2.8 to 3GB.

    So the question is which object has occupied rest of the space in userspace1 tablespace.


    TABNAME COLCOUNT CARD NPAGES FPAGES OVERFLOW
    --------------------------- -------- -------------------- -------------------- -------------------- --------------------
    T_LOGIN_HISTORY_JUNK 4 24887 322 322 0
    T_PASSWORD_CHANGE 2 156701 1121 1121 0
    T_UVAPIDB_OLD 12 15185 448 448 0
    T_LOGIN_HISTORY_JUNK 4 0 0 1 0
    EXPLAIN_INSTANCE 24 0 0 1 0
    EXPLAIN_STATEMENT 17 0 0 1 0
    EXPLAIN_ARGUMENT 12 0 0 1 0
    EXPLAIN_OBJECT 45 0 0 1 0
    EXPLAIN_OPERATOR 22 0 0 1 0
    FED_CACHE_MQTS 7 0 0 1 0
    EXPLAIN_STREAM 22 0 0 1 0
    ADVISE_INDEX 44 0 0 1 0
    ADVISE_WORKLOAD 10 0 0 1 0
    ADVISE_AST 23 0 0 1 0
    T_EMAIL 12 4276593 300122 300122 0
    T_USERINFO 6 49165 841 841 0
    EXPLAIN_INSTANCE 24 0 0 1 0
    EXPLAIN_STATEMENT 17 0 0 1 0
    EXPLAIN_ARGUMENT 12 0 0 1 0
    EXPLAIN_OBJECT 45 0 0 1 0
    EXPLAIN_OPERATOR 22 0 0 1 0
    EXPLAIN_PREDICATE 16 0 0 1 0
    EXPLAIN_STREAM 22 0 0 1 0
    EXPLAIN_INSTANCE 24 6 1 1 0
    EXPLAIN_ARGUMENT 12 71 3 3 0
    EXPLAIN_OBJECT 45 9 2 2 0
    EXPLAIN_OPERATOR 22 17 2 2 0
    EXPLAIN_PREDICATE 16 7 1 1 0
    EXPLAIN_STREAM 22 20 2 2 0
    SMS_FIELD_TEMPLATE_MASTER 21 2249 106 106 0
    SMS_MEMBERSHIP_VALUES 4 15 1 1 0
    SMS_CASE_ID_GENERATION 6 3946 73 73 0
    SMS_BUTTON_TEMPLATE_MASTER 7 367 13 13 0
    T_UVAPIDB 19 40595 2738 2738 0
    EXPLAIN_INSTANCE 24 1 1 1 0
    EXPLAIN_STATEMENT 17 2 1 1 0
    EXPLAIN_ARGUMENT 12 8 1 1 0
    EXPLAIN_OBJECT 45 1 1 1 0
    EXPLAIN_OPERATOR 22 2 1 1 0
    EXPLAIN_PREDICATE 16 2 1 1 0
    EXPLAIN_STREAM 22 2 1 1 0
    EXPLAIN_INSTANCE 24 3 1 1 0
    EXPLAIN_ARGUMENT 12 60 2 2 0
    FED_CACHE_OPTIONS 4 0 0 1 0
    T_EMAIL_BACKUP 12 2885076 193863 193863 0
    EXPLAIN_STREAM 23 12 1 1 0
    EXPLAIN_DIAGNOSTIC 10 0 0 1 0
    EXPLAIN_DIAGNOSTIC_DATA 12 0 0 1 0
    ADVISE_INSTANCE 5 0 0 1 0
    ADVISE_INDEX 52 0 0 1 0
    ADVISE_WORKLOAD 10 0 0 1 0
    ADVISE_MQT 28 0 0 1 0
    ADVISE_TABLE 12 0 0 1 0
    EXPLAIN_PREDICATE 16 0 0 1 0
    EXPLAIN_STATEMENT 17 12 1 1 0
    EXPLAIN_STATEMENT 17 6 1 1 0
    EXPLAIN_OBJECT 46 6 1 1 0
    EXPLAIN_OPERATOR 22 9 1 1 0
    EXPLAIN_PREDICATE 17 3 1 1 0
    ADVISE_PARTITION 20 0 0 1 0

    60 record(s) selected.

  12. #12
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Please check output of query.

    db2 "select TABNAME,colcount,card,npages,fpages,overflow from syscat.tables where tbspaceid=2"
    Attached Files Attached Files

  13. #13
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    pd/psi is good for you..
    this needs to be investigated - there must be a reason - an object - large object...
    I do not have this time to spend on this problem - unless you hire me and I will investigate..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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