Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Data / Index Space Report

Posted on 2010-08-30
8
555 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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