Solved

Data / Index Space Report

Posted on 2010-08-30
8
556 Views
Last Modified: 2012-06-21
How can i generate Data / Index Space Report  i am on db29.5/aix
Thanks
0
Comment
Question by:sam2929
  • 4
  • 3
8 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33557729
Hi Sam,

DB2 for Z/OS has such a report as one of its standard tools, but I'm not aware of it being ported to UDB/LUW.

What are you trying to see in the report?


Kent
0
 

Author Comment

by:sam2929
ID: 33558338
Looking for
============================================
Tablespaces with less than 15% free!!!!!!!!! and more then 15% free
============================================

TABLESPACE_ID TABLESPACE_NAME    USEABLE_PAGES USED_PAGES  FREE_PAGES  PCT_FULL    TS_LIMIT
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33558621

From the CLP, run

  LIST TABLESPACES SHOW DETAIL


Will that suffice?


Kent
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:sam2929
ID: 33560641
Hi Kent,
How can i get how much free space we have from imformation below(DATA_01) .

 Tablespace ID                        = 8
 Name                                 = DATA_01
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2613248
 Useable pages                        = 2613096
 Used pages                           = 1524132
 Free pages                           = 1088964
 High water mark (pages)              = 2479236
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 12
 Prefetch size (pages)                = 12
 Number of containers                 = 8

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33560896

The Free Page count is 1,088,964 and the page size is 32,768.

That means that there is at least 32GB free in the tablespace.


Kent
0
 
LVL 2

Expert Comment

by:gsprince
ID: 33561733
select DBPARTITIONNUM, substr(TBSP_NAME ,1,30) name, TBSP_ID, TBSP_PAGE_SIZE, TBSP_TYPE type, TBSP_CONTENT_TYPE cont_type, TBSP_TOTAL_SIZE_KB tot_kb, TBSP_USABLE_SIZE_KB usable_kb, TBSP_USED_SIZE_KB used_kb , TBSP_FREE_SIZE_KB free_kb, TBSP_UTILIZATION_PERCENT util_perc from SYSIBMADM.TBSP_UTILIZATION ;

You can put a case statement to get the maximum limit allowed for a particular page_size or use  FS_TOTAL_SIZE_KB of SYSIBMADM.CONTAINER_UTILIZATION or FS_TOTAL_SIZE of SYSIBMADM.SNAPCONTAINER to get how much space is available in the particular FS/container

The following views would provide most of the details you are interested

db2 "describe table SYSIBMADM.TBSP_UTILIZATION"

db2 "describe table SYSIBMADM.CONTAINER_UTILIZATION"

 db2 "describe table SYSIBMADM.SNAPCONTAINER"
0
 

Author Comment

by:sam2929
ID: 33579372
Below shows that 32gb is free  and how can i see how much space is allocated to DATA_01

Tablespace ID                        = 8
 Name                                 = DATA_01
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2613248
 Useable pages                        = 2613096
 Used pages                           = 1524132
 Free pages                           = 1088964
 High water mark (pages)              = 2479236
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 12
 Prefetch size (pages)                = 12
 Number of containers                 = 8

0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 33579895
Hi Sam,

  ((Total pages) * (Page size))

That should give you the total amount of storage assigned to that tablespace.

So in this case, 2.6M * 32K, or about 80GB.


Kent
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question