Results 1 to 5 of 5

Thread: Table Size

  1. #1
    Join Date
    May 2004
    Location
    New York
    Posts
    248

    Unanswered: Table Size

    Very quick question

    Select count(*), sum(size), tabname
    from sysextents
    where dbname = 'my database'
    group by 3
    order by 1

    the size that it reports is in PAGES or Kbytes ?

    Also, doing a dbschema -d dbname -t tabname -ss that shows table creations in Kbytes right?

    Appreciate the response

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    This information can you obtain of $INFORMIXDIR/etc/sysmaster.sql.
    In dbexport and dbimport the size this in KBytes.

    Gustavo.

  3. #3
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    the size column in sysextents table
    is that in pages or kbytes

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi,

    It will give you the space on disk of your tables in pages. (All allocated pages)

    An improved SQL statement to findout the size of a database would look similar the the following:

    SELECT sum(nptotal) pg_allocated, sum(npused) pg_used, sum(npdata) pg_data
    FROM sysmaster:sysptnhdr
    WHERE partnum IN (SELECT partnum FROM sysmaster:systabnames
    WHERE dbsname = "your_db")

    Results are reported in pages.

    Regards,
    Shriyan
    Last edited by vpshriyan; 05-18-04 at 08:10.

  5. #5
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146

    Table Sizing

    Hi,

    I use the following to determine # of extents and # of 4K pages, then ftp the pipe delimited output to Excel & use to obtain initial extent/next extent sizing for table defragging after a dbexport:

    { run this script against the sysmaster database }

    unload to "output_file" delimiter "|"

    select b.dbsname,
    b.tabname,
    a.ti_nextns, { total # of extents }
    a.ti_nptotal { total # of 4K pages }
    from systabinfo a,
    systabnames b,
    fnupg:systables c { change DB name }
    where a.ti_partnum = b.partnum and
    a.ti_partnum = c.partnum

    and b.dbsname = "fnupg" { change DB name }

    and c.tabtype = "T" { make sure it's a table, not an index }
    and a.ti_nptotal > a.ti_fextsiz { act. size in KB > initial extent size }
    and a.ti_nextns >= 3 { table must have 3 or more extents }
    order by a.ti_nptotal desc

    Good luck with this, standard disclaimers apply

    Mike

Posting Permissions

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