Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Current Size of a Database

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Thu Apr 03, 2014 2:59 pm    Post subject: Current Size of a Database
Reply with quote

Hi,

I need to check the current size of a database (DB2 V10). I have searched the forum and found that we can achieve that by making a query like

Quote:
SELECT DBNAME, SUM(SPACE) AS SIZE_IN_KB
FROM "SYSIBM".SYSTABLEPART
WHERE ( DBNAME = 'DBXXXXX' )
GROUP BY DBNAME
;


But this will only give the total allocated size of all the tablespaces in that particular database. I believe it will not give the total size of the database because in the above query we are not considering the space for INDEXspace.

I have written below query to add the index space size also. Can anyone (DB2 Expert) in this forum advise me if the following approach is correct to get the size of a database

Quote:
SELECT 'KB='||CHAR(SUM(A.KB+B.KB)),
'MB='||CHAR(SUM(A.KB+B.KB)/1024)
FROM
(SELECT SUM(SPACE) AS KB
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME ='DBXXXXXX'
AND TSNAME IN ( SELECT TSNAME FROM SYSIBM.SYSTABLES
WHERE DBNAME ='DBXXXXXX')) A,
(SELECT SUM(SPACE) AS KB
FROM SYSIBM.SYSINDEXPART
WHERE IXNAME IN (SELECT NAME FROM SYSIBM.SYSINDEXES
WHERE DBNAME = 'DBXXXXXX'
AND CREATOR ='YYY')) B


Thanks in advance
Prasun
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Apr 03, 2014 9:16 pm    Post subject:
Reply with quote

You may wants to see this information, and it may or may not need an update as per DB2 V10.

Option 1: Get the total number of rows multiplied by row length.. What if you have variable columns...
Option 2: Get the total number of pages allocated for individual tables and multiply by 4K. What if there are large number of tables in a databases??
Option 3: List the underlying VSAM dataset of tablespace and indexspace. Get the Hi Used RBA and Low Used RBA and calculate the difference. Same Constrains as avove option.

Here you go ...
IBM provided a stand alone alone utility which will calculate the space for you within minutes.
The Utility name is STOSPACE. All you need to give the input is the Storage Group of the underlying dataset of spacific table or index. This will update the catalogue tables of DB2 regarding space information from where you can get by firing simple Select query.

Authorization required:
STOSPACE privilege SYSCTRL or SYSADM authority

This JCL step should be like:
Code:
//*---------------------------------------------------------------------
//* STOSPACE UTILITY TO UPDATE INFORMATION IN DSN1 CATALOG
//*---------------------------------------------------------------------
//DH011510 EXEC DSNUPROC,SYSTEM=DSN1,UID='TSG11510',UTPROC=''
//*
//SYSPRINT DD SYSOUT=*
//DSNUPROC.SYSIN DD *
STOSPACE STOGROUP (GDSN1)
//*


After Executing the utility, you may get following messages in SYSPRINT
Information you get in SYSPRINT

Code:
DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DH011510
DSNU050I DSNUGUTC - STOSPACE STOGROUP(GDSN1)
DSNU640I - DSNUGSPC - DATA SET= 0 OF TABLESPACE= SPRODDET IN DATABASE= DISSUES2 HAS BEEN MIGRATED BY HSM
DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

After successful execution of STOSPACE utility you can query the catalogue tables as below and add the tablespace and index's space value to calculate the size of a database.
Calculating all index space in a database:

Code:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

Calculating all index space in a database:
Code:

SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSINDEXES
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

You can query SYSIBM.SYSSTOGROUP to findout total space in KB aquired by a particular storage group.

More about STOSPACE Utility:

STOSPACE output stored in columns as mentioned below:

The output from STOSPACE consists of updated values in the columns and tables in the following list. In each case, an amount of space is given in kilobytes (KB).
SPACE - in SYSIBM.SYSINDEXES shows the amount of space that is allocated to indexes. If the index is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLESPACE shows the amount of space that is allocated to table spaces. If the table space is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSINDEXPART shows the amount of space that is allocated to index partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLEPART shows the amount of space that is allocated to table partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSSTOGROUP shows the amount of space that is allocated to storage groups. STATSTIME in SYSIBM.SYSSTOGROUP shows the timestamp for the time at which STOSPACE was last executed.

Note: If the value is too large to fit in the SPACE column, the SPACEF column is updated.

Concurrency and compatibility for STOSPACE:
STOSPACE does not set a utility restrictive state on the target object.
STOSPACE can run concurrently with any utility on the same target object. However, because STOSPACE updates the catalog, concurrent STOSPACE utility jobs or other concurrent applications that update the catalog might cause timeouts and deadlocks.

Terminating or restarting STOSPACE:
You can terminate a STOSPACE utility job with the TERM UTILITY command if you have submitted the job or have SYSOPR, SYSCTRL, or SYSADM authority.
You can restart a STOSPACE utility job, but it starts from the beginning again.

You can also refer,
https://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_stospacesamples.htm
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Comparision with current time arunsoods DFSORT/ICETOOL 5 Thu Nov 09, 2017 10:37 am
This topic is locked: you cannot edit posts or make replies. Comparing current time with the time ... arunsoods SYNCSORT 1 Mon Oct 30, 2017 4:07 pm
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us