syschunks.nfree and 'onstat -d' free column

Hi over there, Can someone explain why the 'select' statement on sysdbspaces,syschunks gives different value than that on free column of 'onstat -d', knowing siglobdbs1 is a lob dbspace (see below) select name[1,9] dbspace, chknum, chksize, nfree from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and name = 'siglob1dbs1' dbspace chknum chksize nfree siglob1db 81 2621438 40792 siglob1db 82 2621440 0 siglob1db 83 2621440 173028 siglob1db 84 2621440 173028 siglob1db 94 2621440 173028 informix@hmba04:hcoifcoi_tcp:/bases/informix/hcoifcoi/admin/DBA> onstat -d | grep sig | grep lo 700000122e3e8f8 35 0xe8001 81 5 N SB informix siglob1dbs1 700000122e0d1c0 81 35 0 2621438 524276 2448360 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf 700000122e0d358 82 35 0 2621440 524284 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf 700000122e0d4f0 83 35 0 2621440 1048568 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d03/sig1_lo_03.dbf 700000122e0d688 84 35 0 2621440 524284 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d04/sig1_lo_04.dbf 700000122e0e688 94 35 0 2621440 2441905 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d05/sig1_lo_05.dbf Thanks

Hi. According to Information Center: *nfree* integer Number of free pages in the chunk (in units of system default page size) Maybe it´s because you have different page sizes on your dbspaces (?) Best regards. Em 18/01/2011 12:43, GILLES TCHAPPI escreveu: > Hi over there, > > Can someone explain why the 'select' statement on sysdbspaces,syschunks > gives different value than that on free column of 'onstat -d', knowing > siglobdbs1 is a lob dbspace (see below) > > select name[1,9] dbspace, chknum, chksize, nfree > from sysdbspaces d, syschunks c > where d.dbsnum = c.dbsnum > and name = 'siglob1dbs1' > > dbspace chknum chksize nfree > > siglob1db 81 2621438 40792 > siglob1db 82 2621440 0 > siglob1db 83 2621440 173028 > siglob1db 84 2621440 173028 > siglob1db 94 2621440 173028 > > informix@hmba04:hcoifcoi_tcp:/bases/informix/hcoifcoi/admin/DBA> onstat -d | > grep sig | grep lo > 700000122e3e8f8 35 0xe8001 81 5 N SB informix siglob1dbs1 > 700000122e0d1c0 81 35 0 2621438 524276 2448360 POSB > /bases/informix/hcoifcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf > 700000122e0d358 82 35 0 2621440 524284 2448409 POSB > /bases/informix/hcoifcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf > 700000122e0d4f0 83 35 0 2621440 1048568 2448409 POSB > /bases/informix/hcoifcoi/app/deltasigv32/dat/d03/sig1_lo_03.dbf > 700000122e0d688 84 35 0 2621440 524284 2448409 POSB > /bases/informix/hcoifcoi/app/deltasigv32/dat/d04/sig1_lo_04.dbf > 700000122e0e688 94 35 0 2621440 2441905 2448409 POSB > /bases/informix/hcoifcoi/app/deltasigv32/dat/d05/sig1_lo_05.dbf > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11

Hi, My server is a 9.4 version. In this version, aren't all dbspaces fixed page sized ? Regards

Yes all of the dbspaces are based on a 4k page size for IBM AIX and Windows and 2K for all of the others . Page size bacame configurable starting v10. You can go up to 16K per dbspace except for the critical dbspaces (rootdbs and any dbspace that contains logs). You also need the buffer pool that goes with it. regards, Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 26/01/11 10:10, GILLES TCHAPPI a écrit : > Hi, > > My server is a 9.4 version. In this version, aren't all dbspaces fixed page > sized ? > > Regards > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Yes. Art On Jan 26, 2011 2:10 AM, "GILLES TCHAPPI" <ntgilfr@voila.fr> wrote: > Hi, > > My server is a 9.4 version. In this version, aren't all dbspaces fixed page > sized ? > > Regards > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf30433fd073629a049ac276da

Hi, When, in dbaccess, I run the following statement : select a.name[1,16], sum(b.chksize) sum_chksize, sum(b.nfree) sum_nfree from sysdbspaces a, syschunks b where a.name like 'sig%' and a.name not like 'sigda%' and a.name not like 'sigsb%' and a.dbsnum = b.dbsnum group by 1 order by 1 asc; I get : name sum_chksize sum_nfree siglob1dbs1 13107198 559876 And when I run onstat as below : informix@hmba04:hcoifcoi_tcp:/bases/informix/hcoifcoi> onstat -d | grep sig | grep -v sigda | grep -v sigs| grep -v _da_ | grep -v _sb_ I get : 70000012dced8f8 35 0xe8001 81 5 N SB informix siglob1dbs1 70000012dcbc1c0 81 35 0 2621438 524276 2448360 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf 70000012dcbc358 82 35 0 2621440 524284 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf 70000012dcbc4f0 83 35 0 2621440 1048568 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d03/sig1_lo_03.dbf 70000012dcbc688 84 35 0 2621440 524284 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d04/sig1_lo_04.dbf 70000012dcbd688 94 35 0 2621440 2437148 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d05/sig1_lo_05.dbf thus having siglob1dbs1 (sblobspace) total size of (2621438+2621440+ 2621440+2621440+2621440) = 13107198 which equals to 'sum_chksize' (the value returned by the sql statement) but siglob1dbs1 total free space of (524276+524284+1048568+524284+243714)= 2865126 which is different from the value (sum_nfree ) 559876 (returned by the sql statement). Why this difference between a sblobspace free size given by onstat and the one given by the sql statement, provided that in 9.4 informix engine version, all dbspaces have the same 4k page size (on AIX) and that for a non sblobspace this free size has the same value for 'onstat' and 'sql statement' as it can be seen below : select a.name[1,16], sum(b.chksize) sum_chksize, sum(b.nfree) sum_nfree from sysdbspaces a, syschunks b where a.name like 'sigda%' and a.dbsnum = b.dbsnum group by 1 order by 1 asc; name sum_chksize sum_nfree sigdat1dbs1 262142 262089 sigdat2dbs1 1048574 551104 informix@hmba04:hcoifcoi_tcp:/bases/informix/hcoifcoi> onstat -d | grep sig | grep -v sigs | grep -v sigl | grep -v _lo_ | grep -v _sb_ 70000012dce88f8 32 0xa0001 77 1 N informix sigdat1dbs1 70000012dced028 33 0xe0001 79 1 N B informix sigdat2dbs1 70000012dcbbb50 77 32 0 262142 262089 PO-- /bases/informix/hcoifcoi/app/deltasigv32/dat/d01/sig1_da_01.dbf 70000012dcbbe80 79 33 0 1048574 551104 PO-B /bases/informix/hcoifcoi/app/deltasigv32/dat/d02/sig2_da_01.dbf Regards

The space you are looking at is a SmartBlob Space. You need to include the udsize and mdsize columns in your calculation. Stuart McCann -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of GILLES TCHAPPI Sent: Tuesday, 1 February 2011 2:27 AM To: ids@iiug.org Subject: Re: syschunks.nfree and 'onstat -d' free column [22639] Hi, When, in dbaccess, I run the following statement : select a.name[1,16], sum(b.chksize) sum_chksize, sum(b.nfree) sum_nfree from sysdbspaces a, syschunks b where a.name like 'sig%' and a.name not like 'sigda%' and a.name not like 'sigsb%' and a.dbsnum = b.dbsnum group by 1 order by 1 asc; I get : name sum_chksize sum_nfree siglob1dbs1 13107198 559876 And when I run onstat as below : informix@hmba04:hcoifcoi_tcp:/bases/informix/hcoifcoi> onstat -d | grep sig | grep -v sigda | grep -v sigs| grep -v _da_ | grep -v _sb_ I get : 70000012dced8f8 35 0xe8001 81 5 N SB informix siglob1dbs1 70000012dcbc1c0 81 35 0 2621438 524276 2448360 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf 70000012dcbc358 82 35 0 2621440 524284 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf 70000012dcbc4f0 83 35 0 2621440 1048568 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d03/sig1_lo_03.dbf 70000012dcbc688 84 35 0 2621440 524284 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d04/sig1_lo_04.dbf 70000012dcbd688 94 35 0 2621440 2437148 2448409 POSB /bases/informix/hcoifcoi/app/deltasigv32/dat/d05/sig1_lo_05.dbf thus having siglob1dbs1 (sblobspace) total size of (2621438+2621440+ 2621440+2621440+2621440) = 13107198 which equals to 'sum_chksize' (the value returned by the sql statement) but siglob1dbs1 total free space of (524276+524284+1048568+524284+243714)= 2865126 which is different from the value (sum_nfree ) 559876 (returned by the sql statement). Why this difference between a sblobspace free size given by onstat and the one given by the sql statement, provided that in 9.4 informix engine version, all dbspaces have the same 4k page size (on AIX) and that for a non sblobspace this free size has the same value for 'onstat' and 'sql statement' as it can be seen below : select a.name[1,16], sum(b.chksize) sum_chksize, sum(b.nfree) sum_nfree from sysdbspaces a, syschunks b where a.name like 'sigda%' and a.dbsnum = b.dbsnum group by 1 order by 1 asc; name sum_chksize sum_nfree sigdat1dbs1 262142 262089 sigdat2dbs1 1048574 551104 informix@hmba04:hcoifcoi_tcp:/bases/informix/hcoifcoi> onstat -d | grep sig | grep -v sigs | grep -v sigl | grep -v _lo_ | grep -v _sb_ 70000012dce88f8 32 0xa0001 77 1 N informix sigdat1dbs1 70000012dced028 33 0xe0001 79 1 N B informix sigdat2dbs1 70000012dcbbb50 77 32 0 262142 262089 PO-- /bases/informix/hcoifcoi/app/deltasigv32/dat/d01/sig1_da_01.dbf 70000012dcbbe80 79 33 0 1048574 551104 PO-B /bases/informix/hcoifcoi/app/deltasigv32/dat/d02/sig2_da_01.dbf Regards ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Land and Property Management Authority. This email message has been swept by MIMEsweeper for the presence of computer viruses. *************************************************************** Please consider the environment before printing this email.