Post-3:How to calculate the table size

[malla kishore]

Hi Team,

 

I need to calculate the table size in my database can any one try out and help me out

 

Thanks in advance

KishoreMalla

[Vijay Bhasker Chebolu]

Hi,

I think this is the command to know the table
size, we have to give the total pages in the query
given below and we have the change the database name
according to the database we have.

db2 "select (SUM(total_pages)*4)/(1024.0*1024)
TOTAL_ALLOCATED_SPACE_IN_GB from table
(snapshot_tbs_cfg('sample',-1)) TBS_SPCE"

Thanks & Regards
vijay bhasker chebolu,
DataBaseTeam,
Miracle Software Systems, Inc.
Mail id: vche...@miraclesoft.com
Phone: 9441425575

------- Original Message -------
From : malla kishore[mailto:mallak...@gmail.com]
Sent : 8/30/2007 7:51:12 AM
To : mirac...@googlegroups.com
Cc :
Subject : RE: Post-3:How to calculate the table size

[Sanjay kumar akasapu]

Hi Kishore,
You can calculate Table size by multiplying page size(db2 list tablespaces show
detail) and NPAGES(NPAGES is a column in syscat.tables).If there is any issue with my
solution,let me know.
Cheers.

Thanks & Regards
Sanjay kumar Akasapu,
Jr.Database Administrator,
Miracle Software Systems India(P)Ltd,
Mail:saka...@miraclesoft.com,
Mobile:
phone:248-233-31854.

[Satish Mullapudi]

Hi,

 

Though both the solutions provided gives the answer, it appears that Bhasker's sol. is more apt.

Sanjay : ur sol. is in a more crude form.
Bhasker : Can you pls. explain that query which you have provided.

Thanks & Regards,

Satish Mullapudi,
IBM Certified Database Associate,
IBM Information Management (DB2 UDB),  
Miracle Software Systems Inc.
email: smull...@miraclesoft.com
Mobile: +919923020321

[malla kishore]

Hi sanjay/Bhaskar

 

Nice to see relplies .Satis is right and better sanjay can u give out some example

 

Thanks in advance

Kishore

 

[Gokul Sagi]

Hi Kishore,

Inorder to calculate tablesize of a table in database in bytes.

method1: reorgchk update statistics on table schemaname.tablename
where we find a column with tsize in bytes gives the size of a table.

method2: db2 "select a.CARD * (sum(b.AVGCOLLEN)+10) from syscat.tables as a, syscat.columns as b
where a.TABNAME = 'TABLENAME' and b.TABNAME = 'TABLENAME' group by a.CARD"

my example for staff table :
method1:
C:\>db2 "reorgchk update statistics on table miracle.staff"

Doing RUNSTATS ....

Table statistics:

F1: 100 * OVERFLOW / CARD 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: MIRACLE.STAFF
MIRACLE STAFF 35 0 1 1 - 1575 0 - 100 ---
----------------------------------------------------------------------------------------

method2 :
C:\>db2 "select a.CARD * (sum(b.AVGCOLLEN)+10) from syscat.tables as a, syscat.columns as b where
a.TABNAME = 'STAFF' and b.TABNAME = 'STAFF' group by a.CARD"

1
--------------------
1575

1 record(s) selected.

Thanks & Regards,

Gokul.Sagi
Miracle Software Systems pvt.ltd
Web:www.miraclesoft.com
contactno:+91-9290063929
email:gs...@miraclesoft.com

------- Original Message -------
From : malla kishore[mailto:mallak...@gmail.com]

Sent : 9/3/2007 2:14:59 AM
To : mirac...@googlegroups.com
Cc :
Subject : RE: Re: Post-3:How to calculate the table size

Hi sanjay/Bhaskar

Nice to see relplies .Satis is right and better sanjay can u give out some
example

Thanks in advance
Kishore

On 8/31/07, Satish Mullapudi wrote:
>
> Hi,
>
> Though both the solutions provided gives the answer, it appears that
> Bhasker's sol. is more apt.

> *Sanjay *: ur sol. is in a more crude form.
> *Bhasker* : Can you pls. explain that query which you have provided.

--
కిశోర్ మల్ల

[Ramesh Babu Chejarla]

Hi Gokul Sagi,

 

 Can you please paste the description of the table -"Miracle.Staff" - you used here.

 

Thanks,

Ramesh.

 

Cheers,
Ramesh.

[Gokul Sagi]

hi Ramesh,

Here is the description of the table miracle.staff

C:\>db2 "describe table staff"

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
ID SYSIBM SMALLINT 2 0 No
NAME SYSIBM VARCHAR 9 0 Yes
DEPT SYSIBM SMALLINT 2 0 Yes
JOB SYSIBM CHARACTER 5 0 Yes
YEARS SYSIBM SMALLINT 2 0 Yes
SALARY SYSIBM DECIMAL 7 2 Yes
COMM SYSIBM DECIMAL 7 2 Yes

7 record(s) selected.

Thanks & Regards,

Gokul.Sagi
Miracle Software Systems pvt.ltd
Web:www.miraclesoft.com
contactno:+91-9290063929
email:gs...@miraclesoft.com

------- Original Message -------
From : Ramesh Babu Chejarla[mailto:rameshbab...@gmail.com]
Sent : 9/5/2007 12:31:20 AM

Hi Gokul Sagi,

Thanks,
Ramesh.

--
Cheers,
Ramesh.