Informix

select st.dbsname, st.tabname, sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsizefrom syspaghdr sp, sysptnhdr pt, systabnames stwhere sp.pg_partnum = pt.partnum    and st.partnum = pt.partnum    and st.dbsname = '(my database)'  --and st.tabname = '(my table)'    -- Commeted out to get all tables    and mod( sp.pg_flags, 2 ) = 1group by 1, 2;

select st.dbsname, st.tabname, sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsizefrom syspaghdr sp, sysptnhdr pt, systabnames stwhere sp.pg_partnum = pt.partnum    and st.partnum = pt.partnum    and st.dbsname = '(my database)'  --and st.tabname = '(my table)'    -- Commeted out to get all tables    and mod( sp.pg_flags, 2 ) = 1group by 1, 2;

Art & company,
my more general version of the query was still running over an hour later so I cancelled it.

I had asked:
> Remind me, please: What is that lowest order bit of syspaghdr.pg_flags?
Answer is in sysmaster:flag_text: It is a data page.
select * from flags_text where tabname = "syspaghfdr"
tabname syspaghdr
flags 1
txt Data Page

tabname syspaghdr
flags 2
txt Partition Descriptor Page

tabname syspaghdr
flags 4
txt Partition Free List Page

etc..
Now I am uncertain if a remainder page is also a data page.  I can run another query to check that out but it's late and I'm getting foggy.  Also, I prefer an explicit bitmap function to using the mod function which works only for the lowest order bit.  So here's the query as I've mangled it.  Art, you have created a monster! ;-)

select st.dbsname,
       st.tabname,
       sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsize
  from syspaghdr sp, sysptnhdr pt, systabnames st
 where sp.pg_partnum = pt.partnum
   and st.partnum    = pt.partnum
   and st.dbsname    = '(database)'
   and st.tabname    = '(table)'
   and bitval(sp.pg_flags, 1) = 1  -- I care only about data pages
   and sp.pg_nslots >= 1           -- with at least 1 row
   and bitval(sp.pg_flags, 512) = 0 -- but not remainder pages
 group by 1, 2
;

It would appear that this able that motivated my original question has no remainder pages.  Interesting waste of space, ain't it?



Another improvement over your original query:  I suspect your query counted empty pages into the avg(pt.nrows). I added the clause:
and sp.pg_nslots >= 1 -- with at least 1 row

select st.dbsname, st.tabname, sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsizefrom syspaghdr sp, sysptnhdr pt, systabnames stwhere sp.pg_partnum = pt.partnum    and st.partnum = pt.partnum    and st.dbsname = '(my database)'  --and st.tabname = '(my table)'    -- Commeted out to get all tables    and mod( sp.pg_flags, 2 ) = 1group by 1, 2;

Art & company,
my more general version of the query was still running over an hour later so I cancelled it.

I had asked:
> Remind me, please: What is that lowest order bit of syspaghdr.pg_flags?
Answer is in sysmaster:flag_text: It is a data page.
select * from flags_text where tabname = "syspaghfdr"
tabname syspaghdr
flags 1
txt Data Page

tabname syspaghdr
flags 2
txt Partition Descriptor Page

tabname syspaghdr
flags 4
txt Partition Free List Page

etc..
Now I am uncertain if a remainder page is also a data page.  I can run another query to check that out but it's late and I'm getting foggy.  Also, I prefer an explicit bitmap function to using the mod function which works only for the lowest order bit.  So here's the query as I've mangled it.  Art, you have created a monster! ;-)

select st.dbsname,       st.tabname,       sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsize  from syspaghdr sp, sysptnhdr pt, systabnames st where sp.pg_partnum = pt.partnum   and st.partnum    = pt.partnum   and st.dbsname    = '(database)'   and st.tabname    = '(table)'   and bitval(sp.pg_flags, 1) = 1  -- I care only about data pages   and sp.pg_nslots >= 1           -- with at least 1 row   and bitval(sp.pg_flags, 512) = 0 -- but not remainder pages group by 1, 2;

It would appear that this able that motivated my original question has no remainder pages.  Interesting waste of space, ain't it?



Another improvement over your original query:  I suspect your query counted empty pages into the avg(pt.nrows). I added the clause:
and sp.pg_nslots >= 1 -- with at least 1 row

select st.dbsname, st.tabname, sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsizefrom syspaghdr sp, sysptnhdr pt, systabnames stwhere sp.pg_partnum = pt.partnum    and st.partnum = pt.partnum    and st.dbsname = '(my database)'  --and st.tabname = '(my table)'    -- Commeted out to get all tables    and mod( sp.pg_flags, 2 ) = 1group by 1, 2;

Art & company,
my more general version of the query was still running over an hour later so I cancelled it.

I had asked:
> Remind me, please: What is that lowest order bit of syspaghdr.pg_flags?
Answer is in sysmaster:flag_text: It is a data page.
select * from flags_text where tabname = "syspaghfdr"
tabname syspaghdr
flags 1
txt Data Page

tabname syspaghdr
flags 2
txt Partition Descriptor Page

tabname syspaghdr
flags 4
txt Partition Free List Page

etc..
Now I am uncertain if a remainder page is also a data page.  I can run another query to check that out but it's late and I'm getting foggy.  Also, I prefer an explicit bitmap function to using the mod function which works only for the lowest order bit.  So here's the query as I've mangled it.  Art, you have created a monster! ;-)

select st.dbsname,       st.tabname,       sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsize  from syspaghdr sp, sysptnhdr pt, systabnames st where sp.pg_partnum = pt.partnum   and st.partnum    = pt.partnum   and st.dbsname    = '(database)'   and st.tabname    = '(table)'   and bitval(sp.pg_flags, 1) = 1  -- I care only about data pages   and sp.pg_nslots >= 1           -- with at least 1 row   and bitval(sp.pg_flags, 512) = 0 -- but not remainder pages group by 1, 2;

It would appear that this able that motivated my original question has no remainder pages.  Interesting waste of space, ain't it?



Another improvement over your original query:  I suspect your query counted empty pages into the avg(pt.nrows). I added the clause:
and sp.pg_nslots >= 1 -- with at least 1 row

select st.dbsname, st.tabname, sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsizefrom syspaghdr sp, sysptnhdr pt, systabnames stwhere sp.pg_partnum = pt.partnum    and st.partnum = pt.partnum    and st.dbsname = '(my database)'  --and st.tabname = '(my table)'    -- Commeted out to get all tables    and mod( sp.pg_flags, 2 ) = 1group by 1, 2;

select st.dbsname[1,10],
       st.tabname[1,20],
       round(avg(sp.pg_nslots), 2) as avg_slots,
     --sum(sp.pg_pagesize - sp.pg_frcnt)/avg(pt.nrows) as avg_rowsize,
       round(avg((sp.pg_pagesize - sp.pg_frcnt)/sp.pg_nslots), 2) as avg_rowsize,
       round(avg(sp.pg_frcnt),2) as avg_free_bytes
  from syspaghdr sp, sysptnhdr pt, systabnames st
 where sp.pg_partnum = pt.partnum
   and st.partnum    = pt.partnum
   and st.dbsname    = '(my database)'
   and st.tabname    = '(my table)'
   and bitval(sp.pg_flags, 1) = 1  -- I care only about data pages
   and sp.pg_nslots >= 1           -- with at least 1 row
   and bitval(sp.pg_flags, 512) = 0 -- but not remainder pages
 group by 1, 2
;
Result:
dbsname         (my database)
tabname         (my table)
avg_slots       11.14
avg_rowsize     95.51    <--- This is more consistent with the page-by-page query
avg_free_bytes  960.61

It is still a mystery to me how more than row gets into a page when MAX_FILL_DATA_PAGES is 0 but that's for me to discuss with the main DBA here.  I have no real access to the database itself so it's not possible (yet) to determine which version is correct.  But when I modified my page-by-page query to check for average row length, I found only 3 rows longer than 125 bytes. (Actually, 121; 4 bytes for the slot-table entry).

Related mystery, but from the other side of the coin, is why there is so much free space in every blessed page.  If MAX_FILL_DATA_PAGES is being ignored many pages should pretty full.  In fact there are exactly 2 pages (out of ~7500) with under 850 free bytes. As Yul Brynner sings: This is a puzzlement! :-)

Still working on it..

Art & company,
my more general version of the query was still running over an hour later so I cancelled it.

I had asked:
> Remind me, please: What is that lowest order bit of syspaghdr.pg_flags?
Answer is in sysmaster:flag_text: It is a data page.
select * from flags_text where tabname = "syspaghfdr"
tabname syspaghdr
flags 1
txt Data Page

tabname syspaghdr
flags 2
txt Partition Descriptor Page

tabname syspaghdr
flags 4
txt Partition Free List Page

etc..
Now I am uncertain if a remainder page is also a data page.  I can run another query to check that out but it's late and I'm getting foggy.  Also, I prefer an explicit bitmap function to using the mod function which works only for the lowest order bit.  So here's the query as I've mangled it.  Art, you have created a monster! ;-)

select st.dbsname,       st.tabname,       sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsize  from syspaghdr sp, sysptnhdr pt, systabnames st where sp.pg_partnum = pt.partnum   and st.partnum    = pt.partnum   and st.dbsname    = '(database)'   and st.tabname    = '(table)'   and bitval(sp.pg_flags, 1) = 1  -- I care only about data pages   and sp.pg_nslots >= 1           -- with at least 1 row   and bitval(sp.pg_flags, 512) = 0 -- but not remainder pages group by 1, 2;

It would appear that this able that motivated my original question has no remainder pages.  Interesting waste of space, ain't it?



Another improvement over your original query:  I suspect your query counted empty pages into the avg(pt.nrows). I added the clause:
and sp.pg_nslots >= 1 -- with at least 1 row

select st.dbsname, st.tabname, sum(pg_pagesize - pg_frcnt)/avg(pt.nrows) as avg_rowsizefrom syspaghdr sp, sysptnhdr pt, systabnames stwhere sp.pg_partnum = pt.partnum    and st.partnum = pt.partnum    and st.dbsname = '(my database)'  --and st.tabname = '(my table)'    -- Commeted out to get all tables    and mod( sp.pg_flags, 2 ) = 1group by 1, 2;