Get the size of all tables in a Database

Richy12 · New User Joined: 29 May 2013 Posts: 22 Location: India

I need to get the rowcount and size(preferably in kB or mB) of all the tables in a Database.

I use DB2 V8.

Can some one help me with this?

Pandora-Box · Posted: Tue Jun 18, 2013 2:35 pm

Row count you could use COUNT(*)

Size means space utilised by the table data?

Richy12 · New User Joined: 29 May 2013 Posts: 22 Location: India

Yes. The space utilized by the table data and its index.

I am aware of COUNT(*). I presume we would get the size info from some SYSIBM.* tables. So along with that, can we get the row count as well for the all the tables in a database instead of writing individual COUNT(*) queries for the tables.

Pandora-Box · Posted: Tue Jun 18, 2013 4:41 pm

But first why do you need this?

sushanth bobby · Posted: Tue Jun 18, 2013 4:51 pm

Hi Richy,

If you have RTS turned on in V8, you can get information from,
For Table Details, check the table SYSIBM.SYSTABLESPACESTATS columns totalrows, datasize

For Index details, check the table sysibm.sysindexspacestats column space.

Otherwise,
From table sysibm.systablespace column Spacef and join with sysibm.systables and column cardf will give you total number of rows.

Remember, DB2 Catalogs are updated with space info only if you have run RUNSTATS utility with UPDATE SPACE option, otherwise it will have -1

Regards,
Sushanth

Richy12 · New User Joined: 29 May 2013 Posts: 22 Location: India

The CARDF and SPACEF values are exponential and seems to be incorrect. Also, for most tables its -1.0000000000000000E+00.

Is there any other way? Can we get this details from the DB* volumes where these table VSAM files reside?

@Pandora - there is a requirement for me to get these details in the test regions. Then we would decide whether or not to free up these tables.

Arun Raj · Posted: Tue Jun 18, 2013 7:25 pm

Richy,

Did you have a look at the SYSTABLESPACESTATS table as suggested above? IIRC, it used to show the row count instead of having to run a count(*) sql.

Pandora-Box · Posted: Tue Jun 18, 2013 7:33 pm

Extent information should give them some idea to DBA

dick scherrer · Posted: Tue Jun 18, 2013 9:24 pm

Hello,

I am unsure whether you want space allocated on dasd or the size of the bytes of actual data stored. They are typically different . . .

Richy12 · New User Joined: 29 May 2013 Posts: 22 Location: India

I could not find SYSTABLESPACESTATS and SYSINDEXPACESTATS. May be RTS is turned off.

Arun - can you throw some light on IIRC?

Dick - Yes. I need the total bytes of the actual data stored in the table.

Pandora-Box · Posted: Tue Jun 18, 2013 9:51 pm

dick scherrer · Posted: Tue Jun 18, 2013 10:15 pm

Hello,

sushanth bobby · Posted: Wed Jun 19, 2013 10:13 am

Hi Richy12,

If Runstats have not been, getting the total number of rows would be tedious.

Next set of options would be for getting the space information is,
* If you know the naming conventions of underlying DB2 datasets, you can do a listcat for ALL the tablespaces(too much work) OR write a rexx to do that.
* Best idea would be to contact the storage and give them the naming convention of the DB2 datasets and you will get the space information.

Regards,
Sushanth

dick scherrer · Posted: Wed Jun 19, 2013 6:48 pm

Hi Susanth,

If i understand the question, what is wanted is the number of bytes used rather than allocated . . .

As rows with VarChar columns are not always the same length, even row count might not be enough for what is wanted.