Data / Index Space Report

How can i generate Data / Index Space Report  i am on db29.5/aix
Thanks
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
sam2929Author Commented:
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
Kent OlsenDBACommented:

From the CLP, run

  LIST TABLESPACES SHOW DETAIL


Will that suffice?


Kent
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

sam2929Author Commented:
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
Kent OlsenDBACommented:

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
gsprinceCommented:
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
sam2929Author Commented:
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
Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.