Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Unanswered: need help in table size calculation

    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
    TBLspace contains TBLspace BLOB
    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

    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

    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 dara

  2. #2
    Join Date
    May 2004
    New York
    If you system page size is 2 the change it

    database sysmaster;

    select dbsname,
    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;

  3. #3
    Join Date
    Dec 2003
    North America
    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?

    Last edited by mjldba; 06-13-06 at 13:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts