469,352 Members | 1,677 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Schema size(Size of all the tables in a schema)

Hi ,

I was trying to find the size of a schema (Size of all the tables in a
schema). Can some one tell me an easy way to do this. There are more
than 200 tables in my schema and it is very tedious job for me to
calculate each table size manually.

Thanks in advance,
Kamalnath.V
Jul 7 '08 #1
7 11234
On Jul 7, 10:08 am, Gladiator <vkamalnath1...@gmail.comwrote:
Hi ,

I was trying to find the size of a schema (Size of all the tables in a
schema). Can some one tell me an easy way to do this. There are more
than 200 tables in my schema and it is very tedious job for me to
calculate each table size manually.
If you know how to calculate the size of one table, you can apply that
to all tables in a schema:

db2 "select tabschema, tabname, size_of_table(tabschema, tabname) from
syscat.tables where tabschema = ? and type = T"

For a grand totall as well, something like:

db2 "select tabschema, tabname, sum(size_of_table(tabschema, tabname))
from syscat.tables where tabschema = ? and type = T group by grouping
sets ((tabschema, tabname),())"

should do.
/Lennart


Thanks in advance,
Kamalnath.V
Jul 7 '08 #2
On Jul 7, 2:26 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Jul 7, 10:08 am, Gladiator <vkamalnath1...@gmail.comwrote:
Hi ,
I was trying to find the size of a schema (Size of all the tables in a
schema). Can some one tell me an easy way to do this. There are more
than 200 tables in my schema and it is very tedious job for me to
calculate each table size manually.

If you know how to calculate the size of one table, you can apply that
to all tables in a schema:

db2 "select tabschema, tabname, size_of_table(tabschema, tabname) from
syscat.tables where tabschema = ? and type = T"

For a grand totall as well, something like:

db2 "select tabschema, tabname, sum(size_of_table(tabschema, tabname))
from syscat.tables where tabschema = ? and type = T group by grouping
sets ((tabschema, tabname),())"

should do.

/Lennart
Thanks in advance,
Kamalnath.V
Hi Lennart ,

Thanks for the reply ,

But i dont there is a function/routine called size_of_table in DB2 UDB
for LUW . Instead i guess it is available in Db2 for mainframes. I
need some thing in LUW.

Thanks & Regards,
Kamalnath.V
Jul 7 '08 #3
Gladiator wrote:
On Jul 7, 2:26 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
>On Jul 7, 10:08 am, Gladiator <vkamalnath1...@gmail.comwrote:
>>Hi ,
I was trying to find the size of a schema (Size of all the tables in a
schema). Can some one tell me an easy way to do this. There are more
than 200 tables in my schema and it is very tedious job for me to
calculate each table size manually.
If you know how to calculate the size of one table, you can apply that
to all tables in a schema:

db2 "select tabschema, tabname, size_of_table(tabschema, tabname) from
syscat.tables where tabschema = ? and type = T"

For a grand totall as well, something like:

db2 "select tabschema, tabname, sum(size_of_table(tabschema, tabname))
from syscat.tables where tabschema = ? and type = T group by grouping
sets ((tabschema, tabname),())"

should do.

/Lennart
>>Thanks in advance,
Kamalnath.V

Hi Lennart ,

Thanks for the reply ,

But i dont there is a function/routine called size_of_table in DB2 UDB
for LUW . Instead i guess it is available in Db2 for mainframes. I
need some thing in LUW.
Which version of DB2 for LUW?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 7 '08 #4
On Jul 7, 3:23 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Gladiator wrote:
On Jul 7, 2:26 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Jul 7, 10:08 am, Gladiator <vkamalnath1...@gmail.comwrote:
>Hi ,
I was trying to find the size of a schema (Size of all the tables in a
schema). Can some one tell me an easy way to do this. There are more
than 200 tables in my schema and it is very tedious job for me to
calculate each table size manually.
If you know how to calculate the size of one table, you can apply that
to all tables in a schema:
>db2"select tabschema, tabname,size_of_table(tabschema, tabname) from
syscat.tables where tabschema = ? and type = T"
For a grand totall as well, something like:
>db2"select tabschema, tabname, sum(size_of_table(tabschema, tabname))
from syscat.tables where tabschema = ? and type = T group by grouping
sets ((tabschema, tabname),())"
should do.
/Lennart
>Thanks in advance,
Kamalnath.V
Hi Lennart ,
Thanks for the reply ,
But i dont there is a function/routine calledsize_of_tableinDB2UDB
for LUW . Instead i guess it is available inDb2for mainframes. I
need some thing in LUW.

Which version ofDB2for LUW?
--
Serge RielauDB2Solutions Development
IBM Toronto Lab
Hi Serge ,
---Version 9.5

Thanks,
Kamalnath.V
Jul 7 '08 #5
http://publib.boulder.ibm.com/infoce.../r0052897.html

Since it's an SQL API you can easily roll it up to schema level as
indicated by previous posters.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 7 '08 #6
On Jul 7, 5:32 pm, Serge Rielau <srie...@ca.ibm.comwrote:
http://publib.boulder.ibm.com/infoce...pic/com.ibm.db...

Since it's an SQL API you can easily roll it up to schema level as
indicated by previous posters.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi Serge ,

Thanks a lot for the information.

Is this applicable only for V9.5 ? What about the older versions ? If
i have the same requirement for the databases on V8+. How do i do
this.

One more thing when i used the query given by Lennart , it gave me
the below error
db2 "select tabschema, tabname, size_of_table(tabschema, tabname) from
syscat.tables where tabschema ='PANELSTAGE' and type ='T'"
SQL0440N No authorized routine named "SIZE_OF_TABLE" of type
"FUNCTION"
having compatible arguments was found. SQLSTATE=42884
Thanks in advance ,

Kamalnath.V
Jul 7 '08 #7
Gladiator wrote:
On Jul 7, 5:32 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>http://publib.boulder.ibm.com/infoce...pic/com.ibm.db...

Since it's an SQL API you can easily roll it up to schema level as
indicated by previous posters.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi Serge ,

Thanks a lot for the information.

Is this applicable only for V9.5 ? What about the older versions ? If
i have the same requirement for the databases on V8+. How do i do
this.

One more thing when i used the query given by Lennart , it gave me
the below error
db2 "select tabschema, tabname, size_of_table(tabschema, tabname) from
>syscat.tables where tabschema ='PANELSTAGE' and type ='T'"
SQL0440N No authorized routine named "SIZE_OF_TABLE" of type
"FUNCTION"
having compatible arguments was found. SQLSTATE=42884
size_of_table() does not exist. He just explained how to "roll up" to a
schema assuming you have teh information for a specific table.
So I just gave you that piece. At that point it's time to earn your
living :-)

If you snoop around in the 9.5 information center around the function I
posted you will find "deprecated functions" which have different names.
They obviously were introduced in earlier versions.
I don't know if they were there in DB2 V8.2 or only in DB2 9.1

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 7 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bart Torbert | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.