Facing some difference in size between 'onstat -d' , 'oncheck -ce' and 'syschunks' table values for 8k page size dbspaces and chunks

Hi, I have written a query to find Total, Used and Free Space in all chunks. All the columns in query are fetched from syschunks and sysdbspaces tables from sysmaster database. I know onstat -d will show Total and Free Space Details. But I would like to see Total, Used and Free Space in KB, MB and GB format. That is why I have written my own query using SMI tables. Default Page Size in Linux is - 2k Default Page Size in Windows is - 4k In Windows default installation we will get 8k page size dbspace automatically for datadbs and tmpdbspace. Example: Dbspaces address number flags fchunk nchunks pgsize flags owner name 0000000080E0A9A0 7 0x20001 7 1 8192 N BA informix data8dbs1 0000000080E0ABF0 8 0x20001 8 1 8192 N BA informix data8dbs2 00000000825A5030 9 0x20001 9 1 8192 N BA informix data8dbs3 00000000825A5280 10 0x2001 10 2 8192 N TBA informix tmpdbspace I am facing some difference in value between onstat -d, oncheck -ce and syschunks table values for 8k page size dbspaces and chunks. onstat -d and oncheck -ce command shows same value.But in syschunks table it is showing double of that value. Please find the details below and kindly let me know why this difference is there. Results From onstat -d Command: Dbspaces address number flags fchunk nchunks pgsize flags owner name 0000000080E0A9A0 7 0x20001 7 1 8192 N BA informix data8dbs1 0000000080E0ABF0 8 0x20001 8 1 8192 N BA informix data8dbs2 00000000825A5030 9 0x20001 9 1 8192 N BA informix data8dbs3 Chunks address chunk/dbs offset size free bpages flags pathname 00000000825AB030 7 7 0 8192 8139 PO-BED D:\\Informix\\storage\\ol_informix1210_1_data8dbs1_p_1 00000000825AC030 8 8 0 8192 8139 PO-BED D:\\Informix\\storage\\ol_informix1210_1_data8dbs2_p_1 00000000825AD030 9 9 0 8192 8139 PO-BED D:\\Informix\\storage\\ol_informix1210_1_data8dbs3_p_1 Results From oncheck -ce Command: Validating extents for Space 'data8dbs1' ... Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p) 7 D:\\Informix\\storage\\ol_informix1210_1_data8dbs1_p_1 8 8192 53 8139 Validating extents for Space 'data8dbs2' ... Chunk Pathname Pagesize(k) Size(p ) Used(p) Free(p) 8 D:\\Informix\\storage\\ol_informix1210_1_data8dbs2_p_1 8 8192 53 8139 Validating extents for Space 'data8dbs3' ... Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p) 9 D:\\Informix\\storage\\ol_informix1210_1_data8dbs3_p_1 8 8192 53 8139 Results From syschunks Table: chknum 7 dbsnum 7 nxchknum 0 pagesize 8192 chksize 16384 offset 0 nfree 16278 mdsize -1 udsize -1 udfree -1 is_offline 0 is_recovering 0 is_blobchunk 0 is_sbchunk 0 is_inconsistent 0 is_extendable 1 flags 139332 fname D:\\Informix\\storage\\ol_informix1210_1_data8dbs1_p_1 mfname moffset mis_offline 0 mis_recovering 0 mflags ------------------------------ chknum 8 dbsnum 8 nxchknum 0 pagesize 8192 chksize 16384 offset 0 nfree 16278 mdsize -1 udsize -1 udfree -1 is_offline 0 is_recovering 0 is_blobchunk 0 is_sbchunk 0 is_inconsistent 0 is_extendable 1 flags 139332 fname D:\\Informix\\storage\\ol_informix1210_1_data8dbs2_p_1 mfname moffset mis_offline 0 mis_recovering 0 mflags ------------------------------ chknum 9 dbsnum 9 nxchknum 0 pagesize 8192 chksize 16384 offset 0 nfree 16278 mdsize -1 udsize -1 udfree -1 is_offline 0 is_recovering 0 is_blobchunk 0 is_sbchunk 0 is_inconsistent 0 is_extendable 1 flags 139332 fname D:\\Informix\\storage\\ol_informix1210_1_data8dbs3_p_1 mfname moffset mis_offline 0 mis_recovering 0 mflags ------------------------------ SHANGEETHA T ------------------------------ #Informix

onstat -d shows the number of pages in units of the pagesize of the dbspace. So a chunk with an 8K pagesize will show in onstat -d as the number of 8K pages.syschunks (for a regular dbspace) shows the number of pages in units of the DEFAULT pagessize of the system that you are on, regardless of the pagesize of the dbspace. So on Windows with a 4K pagesize, syschunks will show as the number of 4K pages, which is why the values you see in syschunks are double what you see in onstat -d. If you need to get the system default pagesize (for example when writing a "free space" script to be used on a variety of OSes), then you can get this from sysmaster also: select sh_pagesize from sysshmvals;------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Yeah, not intuitive what's stored in syschunks or syschunks_fast (querying syschunks will trigger recalculating free BLOBSPACE pages like running "onstat -d update", syschunks_fast is what onstat queries if you don't add the "update" option). Since version 11.10 the chksize and nfree columns in syschunks/syschunks_fast have reported in default page size (so 4K on Windows & AIX 2K elsewhere). Prior to that they reported in the pagesize of the chunk). You do know that this problem is solved already. Just download and build the dbsavail utility from my utils2_ak package (free & open source). Download from my web site at My Utilities . Here's a sample run: $ dbsavail Sort by: Free KB.Dbspace Number 2K Pages 2K Pages Free Total KB Free KB ------------------ --------------- ------------- ------------ ------------ indexdbs 35000 880 70000 1760 (PgSz: 2K) plogspace 111611 1617 223222 3234 (PgSz: 2K) llogspace 397220 2167 794440 4334 (PgSz: 2K) indexdbs_1 248304 8876 496608 17752 (PgSz: 8K) cdrspace 50000 16641 100000 33282 (PgSz: 2K) rootdbs 150000 77051 300000 154102 (PgSz: 2K) datadbs_2 897496 195745 1794992 391490 (PgSz: 2K) datadbs_1 1148531 205867 2297062 411734 (PgSz: 2K) sbspace 550000 422241 1100000 844482 SBsp(PgSz: 2K) tempdbs 602400 602336 1204800 1204672 Temp(PgSz: 2K) ------------------ --------------- ------------- ------------ ------------ Totals: 4190562 1533421 8381124 3066842 There are also several options including changing from free KB to free % and to sort on different columns: $ dbsavail -? Usage: dbsavail [-H server] [-f] [-p] [-u] [-R] [-F|P|S|N] dbsavail [-V|-h] -H - Connect to server. -p - print %free (Default: KB/MB/GB Free).-u - print KB/MB/GB Used or Percent Used instead of Total KB. -f - do not update blob chunk statistics (may be less accurate) -d - include chunk detail by dbspace. -F - sort the report by free size in KB/MB/GB (default). -P - sort the report by percent free. -S - sort the report by total size in KB/MB/GB. -N - sort the report by dbspace name. -R - Reverse sort order. -V - print copyright notice and exit. -K - Display sizes in KB (default). -M - Display sizes in MB. -G - Display sizes in GB. -h - print usage and exit. By default the report is ordered by free space in KB/MB/GB. I have not tried to compile this on Windows, though it should be compilable using the Windows release of the Informix CSDK. But, if you also have Linux, you could run it on a Linux box with INFORMIXSERVER pointed at the Windows based engine. That will work. For what it's worth, here's what the percent free report looks like: $ dbsavail -p Sort by: Free KB.Dbspace Number 2K Pages 2K Pages Free Total KB Pct Free ------------------ --------------- ------------- ------------ ------------ indexdbs 35000 880 70000 2.51 (PgSz: 2K) plogspace 111611 1617 223222 1.45 (PgSz: 2K) llogspace 397220 2167 794440 0.55 (PgSz: 2K) indexdbs_1 248304 8876 496608 3.58 (PgSz: 8K) cdrspace 50000 16641 100000 33.28 (PgSz: 2K) rootdbs 150000 77051 300000 51.37 (PgSz: 2K) datadbs_2 897496 195745 1794992 21.81 (PgSz: 2K) datadbs_1 1148531 205867 2297062 17.92 (PgSz: 2K) sbspace 550000 422241 1100000 4.89 SBsp(PgSz: 2K) tempdbs 602400 602336 1204800 99.99 Temp(PgSz: 2K) ------------------ --------------- ------------- ------------ ------------ Totals: 4190562 1533421 8381124 ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Oh, I forgot, BLOB spaces and SmartBlob spaces are different from normal dbspace chunks. BLOB spaces report in the number of blob pages which you will have to use the pagesize column in syschunks/syschunks_fast to convert to KB or to number of default pages. For SBSpaces use the 'udfree' column instead of the 'free' column and treat that the same as regular chunk 'free' count. Anyway, WAY easier to just take advantage of the madness I went through to get dbsavail reporting correctly and use that. FWIW, dbsavail creates two stored procedures in sysmaster the first time it is run (or if it detects that the exiting versions of them are outdated due to an upgraded dbsavail.ec source): dbsavail() and dbsavail_fast() which do all the heavy lifting. So if you don't like the report formatting from the full utility, you could just run the procs and format the results yourself. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------