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

**Lennart** · Jul 7 '08, 09:35 AM

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

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(t abschema, tabname) from
syscat.tables where tabschema = ? and type = T"

For a grand totall as well, something like:

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

should do.

/Lennart

Thanks in advance,
Kamalnath.V

**Gladiator** · Jul 7 '08, 09:55 AM

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

On Jul 7, 2:26 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

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(t abschema, tabname) from
syscat.tables where tabschema = ? and type = T"
>
For a grand totall as well, something like:
>
db2 "select tabschema, tabname, sum(size_of_tab le(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

**Serge Rielau** · Jul 7 '08, 10:25 AM

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

Gladiator wrote:

On Jul 7, 2:26 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>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(t abschema, tabname) from
>syscat.table s where tabschema = ? and type = T"
>>
>For a grand totall as well, something like:
>>
>db2 "select tabschema, tabname, sum(size_of_tab le(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

**Gladiator** · Jul 7 '08, 11:55 AM

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

On Jul 7, 3:23 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Gladiator wrote:

On Jul 7, 2:26 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

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(tabschem a, tabname) from
syscat.tables where tabschema = ? and type = T"

>

For a grand totall as well, something like:

>

>db2"select tabschema, tabname, sum(size_of_tab le(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_t ableinDB2UDB
for LUW . Instead i guess it is available inDb2for mainframes. I
need some thing in LUW.

>
Which version ofDB2for LUW?
--
Serge RielauDB2Soluti ons Development
IBM Toronto Lab

Hi Serge ,
---Version 9.5

Thanks,
Kamalnath.V

**Serge Rielau** · Jul 7 '08, 12:35 PM

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

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/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

**Gladiator** · Jul 7 '08, 12:55 PM

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

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(t abschema, tabname) from

syscat.tables where tabschema ='PANELSTAGE' and type ='T'"

SQL0440N No authorized routine named "SIZE_OF_TA BLE" of type
"FUNCTION"
having compatible arguments was found. SQLSTATE=42884

Thanks in advance ,

Kamalnath.V

**Serge Rielau** · Jul 7 '08, 02:15 PM

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

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(t abschema, tabname) from

>syscat.table s where tabschema ='PANELSTAGE' and type ='T'"

SQL0440N No authorized routine named "SIZE_OF_TA BLE" 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

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment