Solved

Data / Index Space Report

Posted on 2010-08-30
8
552 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:Kdo
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:Kdo
ID: 33558621

From the CLP, run

  LIST TABLESPACES SHOW DETAIL


Will that suffice?


Kent
0
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Kdo
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:
Kdo 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now