Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Data / Index Space Report

Posted on 2010-08-30
8
Medium Priority
?
565 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 46

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 46

Expert Comment

by:Kent Olsen
ID: 33558621

From the CLP, run

  LIST TABLESPACES SHOW DETAIL


Will that suffice?


Kent
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 46

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 46

Accepted Solution

by:
Kent Olsen earned 2000 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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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 summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

824 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