Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Get the size of all tables in a Database

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

New User


Joined: 29 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 18, 2013 2:19 pm    Post subject: Get the size of all tables in a Database
Reply with quote

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?
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 18, 2013 2:35 pm    Post subject:
Reply with quote

Row count you could use COUNT(*)

Size means space utilised by the table data?
Back to top
View user's profile Send private message
Richy12

New User


Joined: 29 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 18, 2013 2:43 pm    Post subject: Reply to: Get the size of all tables in a Database
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 18, 2013 4:41 pm    Post subject:
Reply with quote

But first why do you need this?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Jun 18, 2013 4:51 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Richy12

New User


Joined: 29 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 18, 2013 6:34 pm    Post subject: Reply to: Get the size of all tables in a Database
Reply with quote

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.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2163
Location: @my desk

PostPosted: Tue Jun 18, 2013 7:25 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 18, 2013 7:33 pm    Post subject:
Reply with quote

Extent information should give them some idea to DBA
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 18, 2013 9:24 pm    Post subject:
Reply with quote

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 . . .
Back to top
View user's profile Send private message
Richy12

New User


Joined: 29 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 18, 2013 9:44 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 18, 2013 9:51 pm    Post subject:
Reply with quote

Quote:
IIRC means If I remember correctly icon_smile.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 18, 2013 10:15 pm    Post subject:
Reply with quote

Hello,

Quote:
Yes. I need the total bytes of the actual data stored in the table.
I'm not aware of how to get this automagically . . .

One way would be to "extract" the table(s) in text format and determine the number of bytes unloaded ? If there were not too many tables . . .
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jun 19, 2013 10:13 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jun 19, 2013 6:48 pm    Post subject:
Reply with quote

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.
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 IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Increase the screen size after split mistah kurtz TSO/ISPF 2 Fri Sep 02, 2016 6:39 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts ALTER of an IDMS AREA with NEW/OLD pa... Awanti IDMS/ADSO 2 Mon Mar 14, 2016 10:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us