MVSFORUMS.com

Balanim · Beginner Joined: 29 Jan 2004 Posts: 21 Topics: 8

HI

I have created many tables in a perticular database, i have loaded all these tables with respective data, Now I want to find out the space occupied by each table, can anyone give some query..??

I have tried these
1)select SPACEF from SYSIBM.SYSTABLES where NAME ='table name' and CREATOR='creator';
2) select SPACE from SYSIBM.SYSTABLESPACE where NAME ='tablespace';

I am doing this on mainframe

Thanks

Bala

kolusu · Posted: Mon May 10, 2004 7:51 am Post subject:

Bala,

The space utilization of the table data is not stored in any catalog tables. When you load the table the rows are actually stored in a VSAM LDS cluster. When you created the tables did you also create the VSAM LDS clusters for the tables? If you know the names of the LDS clusters , you can find the space usage in file-aid.

choose option UTILITIES (3) from the file-aid main menu

choose option VSAM (5) from the next menu

give the name of the LDS cluster for " dataset name" and hit enter twice.

In there you will get the space usage.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Manas Biswal · Posted: Mon May 10, 2004 8:24 am Post subject:

You can know the DASD space (in KB) used at the tablespace level. This information is stored in the SPACE column of the SYSIBM.SYSTABLESPACE catalog table. If this column does not give you any value, then you may have to run the STOSPACE utility. Check this link for further information -
http://www.naspa.com/PDF/2003/1203/T0312010.pdf

Do you have the DB2 Admin tool at your installation (we simply call it the ADM tool). If you have that, you can also have the space utilization at table level. I am not sure from where that information is retrieved or whether it actually shows what you intend. You can try that out though.

Regards,
Manas

Balanim · Beginner Joined: 29 Jan 2004 Posts: 21 Topics: 8

HI Kolusu Manas

Thanks for your valuable inputs, Infact i was looking for actual space occupied by the table and your inputs could able to give the solution.

Tahnks again

Bala

programmer1 · Beginner Joined: 18 Feb 2004 Posts: 138 Topics: 14

Hi,

Is there a way to find out the VSAM clusters, if we just know the table name ?
_________________
Regards,
Programmer

kolusu · Posted: Tue Feb 01, 2005 8:34 am Post subject:

Programmer1,

You need to contact your DBA for the names of the vsam clusters. Every shop has their own standard of naming the vsam clusters of the tables.

Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

programmer1 · Beginner Joined: 18 Feb 2004 Posts: 138 Topics: 14

Thanks Kolusu Smile

_________________
Regards,
Programmer

umapathyv · Beginner Joined: 22 Feb 2005 Posts: 6 Topics: 1

Although, I never tried before, I think, you can try this to find out the VSAM cluster :-

1. Using SQL, find out the table space name for the table.
2. Using SQL, find out the database name, which holds the tablespace.
3. Using SQL, find out the storage group of the database, which will give information about the volumes.
4. Using File-Aid options for the volume, display list of filles allocated on the volume.
5. To identify your VSAM cluster, you have to investigate from the list of files.

Normally, the standard conventions for DB2 VSAM datasets will look like

HLQ.DSNDBx.databasename.tablespacename.*

Am I correct?

Thanks and Regards
Umapathy V

umapathyv · Beginner Joined: 22 Feb 2005 Posts: 6 Topics: 1

I have found this posted on some website regarding calculating the size of a table

To find the size in bytes you can multiply the number of pages in a table by the pagesize for the tablespace:

SELECT tab.npages * ts.pagesize as bytes
FROM syscat.tables tab, syscat.tablespaces ts
WHERE tab.tabname = 'INSERTTABNAMEHERE'
AND tab.tbspace=ts.tbspace

Thanks and Regards
Umapathy V