I have got a table like this.
create table "iisuser".buckets
(
cl_id char(8),
bucket_date date,
num_pos integer,
num_cash integer,
num_txn integer,
bucket byte in bucketblob,
txn_bucket byte
) extent size 160000 next size 16000 lock mode row;
revoke all on "iisuser".buckets from "public";



create unique index "iisuser".bucket_new_ind111 on "iisuser".buckets
(cl_id,bucket_date) using btree in im2adbs ;
Note: the column bucket is in bucketblob dbspace.

I have written a generic script to calculate the tablesizes. I am just pasting the exact portion where I am caluculating

echo "select stn.tabname , \
trunc(ti_npused * 2048/1024) used_space_kb, \
trunc(ti_nptotal * 2048/1024) allocated_space_kb, \
'$num_extents' num_extents,
ti_nrows num_rows \
from systabinfo sti, \
systabnames stn \
where sti.ti_partnum=stn.partnum \
and stn.tabname='buckets' \
and stn.dbsname='testdb'" | dbaccess sysmaster

This is the issue, when I run oncheck -pe, I am able to see that column buckets is in a blobspace and is taking 8 GB memory.
When I run my script having above logic it shows me this.

./get_tab_info -s nypps5 -d testdb -t buckets

TABLE used_space(kb) alloted_space(kb) #ofextents num_rows

buckets 160052 176000 2 750445

Even oncheck -pt testdb:buckets is not showing the byte/blob colum details
it only shows

TBLspace Report for testdb:buckets

Physical Address 2:676
Creation date 04/28/2006 06:42:06
TBLspace Flags e02 Row Locking
TBLspace contains BLOBspace BLO
Bs
TBLspace contains TBLspace BLOB
s
TBLspace use 4 bit bit-maps
Maximum row size 136
Number of special columns 2
Number of keys 0
Number of extents 2
Current serial value 1
First extent size 80000
Next extent size 8000
Number of pages allocated 88000
Number of pages used 80026
Number of data pages 57041
Number of rows 750445
Partition partnum 2097825
Partition lockid 2097825

Extents
Logical Page Physical Page Size
0 21:80460 80000
Index bucket_new_ind111 fragment in DBspace im2adbs

Physical Address 2:704
Creation date 04/28/2006 06:42:07
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 136
Number of special columns 0
Number of keys 1
Number of extents 4
Current serial value 1
First extent size 10000
Next extent size 1000
Number of pages allocated 13000
Number of pages used 12891
Number of data pages 0
Number of rows 0
Partition partnum 2097853
Partition lockid 2097825

Extents
Logical Page Physical Page Size
0 8:84263 10000
10000 3:953879 1000
11000 3:963734 1000
12000 3:981409 1000


If there are already some script you may point me to those links, I will look into them.
My requirement is this.
I should be able to calculate the tablesize using system tables/views and display the actual space it is currently taking in the database.

Thanks in advance.

Jagadish

If you system page size is 2 the change it

database sysmaster;

select dbsname,
tabname,
count(*) num_of_extents,
sum (pe_size ) pages_used,
round (sum (pe_size )
* 4 { Your systems page size in KB }
* 1.2 { Add 20% Growth factor })
ext_size, { First Extent Size in KB }
round (sum (pe_size )
* 4 { Your systems page size in KB }
* .2 { Estimated 20% Yearly Growth })
next_size { Next Extent Size in KB }
from systabnames, sysptnext
where partnum = pe_partnum
group by 1, 2
order by 3 desc, 4 desc;

jdara1, please excuse the thread hijack.

artemka: I execute a similar script against sysmaster to determine initial extent/ next extent sizing when defragging and I "consume" memory that is not released when the query finishes. I'm using 9.30.UC6 with AIX 5.1 (4K pages) & upgrading is not an option at this time (software vendor constraints).

Under normal everyday conditions, onstat -g seg will show that I have 15K - 30K blocks in-use. After running this query onstat -g seg shows over 110K blocks are in-use (the "V" segment) and they are not released until I bounce the engine. I have SHMVERTSIZE set large (702688) to accomodate this issue otherwise additional "V" segments would be added and performance would suffer.

I dropped the GROUP BY and ORDER BY from the query thinking they may be creating temp tables in tmp_dbspace but that isn't the case.

Your thoughts and/or suggestions?

Thanks