Smart Blob Space Size

I'm reviewing some space monitors that were previously set up in our=0D=0As= ystems for notification when we hit a percentage threshold for=0D=0Adiffere= nt dbspaces=2E I'm curious about the calculations that were put in=0D=0Afo= r Smart BLOB space usage=2E=0D=0A=0D=0AWhat "space usage" reports should ex= ist for an sbspace and what is the=0D=0Acorrect formula for each? Do we ne= ed separate one for each of the=0D=0Afollowing?=0D=0A=0D=0A Meta Data Us= ed/Free=0D=0A User Data Used/Free=0D=0A Total Data Used/Free=0D=0A=0D= =0AI looked online and I've seen several threads for space usage queries,= =0D=0Abut didn't find any that clearly answered this item specifically=2E = (If=0D=0Ayou know of one, just point me there=2E) I found this thread that= uses=0D=0Audsize, but it's quite out of date (Dec=2E 2000, version 9=2E21)= and I=0D=0Awasn't sure if the SQL was correct=2E=0D=0A=0D=0Ahttp://groups= =2Egoogle=2Ecom/group/comp=2Edatabases=2Einformix/browse_thread/thr=0D=0Aea= d/21dc4618526fa087/4ca3dfabf7d283f6?hl=3Den&lnk=3Dgst&q=3Dudsize#4ca3dfabf7= d=0D=0A283f6=0D=0A=0D=0AI didn't see anything in the IIUG software reposito= ry with the meta data=0D=0Acalculations=2E I also saw no mention of udsize= in the Admin Guide or=0D=0AAdmin Reference Manual while looking for infor= mation in the Informix=0D=0Alibrary:=0D=0A=0D=0Ahttp://www-306=2Eibm=2Ecom/= software/data/informix/pubs/library/ids_100=2Ehtml=0D=0A=0D=0AMy environmen= t information:=0D=0A=0D=0AIDS: 10=2E00=2EUC7X1=0D=0AO/S: SunOS 5=2E10 Gener= ic_125100-05=0D=0A=0D=0ASysmaster Query (output for dbsnum 9, the only sbsp= ace):=0D=0A=0D=0A SELECT C=2Edbsnum, name, is_sbspace, SUM(chksize) ChkS= ize,=0D=0A SUM(nfree) NFree, SUM(mdsize) MdSize,=0D=0A = SUM(udsize) UdSize, SUM(udfree) UdFree=0D=0A FROM sysmaster:sysdbsp= aces d, sysmaster:syschunks c=0D=0A WHERE d=2Edbsnum=3Dc=2Edbsnum=0D=0A= GROUP BY 1, 2, 3=0D=0A ORDER BY 1=0D=0A=0D=0A Dbs name is_= sbspace chksize nfree mdsize udsize udfree=0D=0A =2E=2E=2E=0D=0A 9= sbdefault 1 1050000 50505 141817 908121 318216=0D=0A =2E= =2E=2E=0D=0A=0D=0Aonstat -d output:=0D=0A=0D=0A dbs offset size = free bpages=0D=0A =2E=2E=2E=0D=0A 9 0 500000 = 49994 441831=0D=0A Metadata 58116 0 5811= 6=0D=0A 9 500000 250000 99992 233145=0D=0A = Metadata 16852 8 16852=0D=0A 9 500000 250000 = 168230 233145=0D=0A Metadata 16852 500 16852= =0D=0A 9 750000 50000 0 0=0D=0A Metad= ata 49997 49997 49997=0D=0A =2E=2E=2E=0D=0A=0D=0AOnstat -d show= s a total size of 1050000, same as chksize above=2E=0D=0AOnstat -d shows a = total free space of 318216, same as udfree above=2E=0D=0AOnstat -d shows a = Metadata size of 141817, same as mdsize above=2E=0D=0AOnstat -d shows a Met= adata free space of 50505, same as nfree above=2E=0D=0A=0D=0AThere is no on= stat -d output that matches the udsize (though subtracting=0D=0Amdsize from= chksize leaves a difference of 8 pages - overhead maybe)=2E=0D=0A=0D=0AWhe= n calculating metadata used space for an sbspace, this seems straight=0D=0A= forward=2E=0D=0A=0D=0A Meta Data Used: (141817 - 50505) / 141817 =3D 64= =2E39%=0D=0A=0D=0AShould there be a difference between user space used and = total space=0D=0Aused?=0D=0AWhich of the following should be used for each = calculation?=0D=0A=0D=0A A=2E (mdsize-nfree)/mdsize =3D> (141= 817-50505)/141817 =3D=0D=0A64=2E39%=0D=0A B=2E (chksize-udfree)/chksiz= e =3D> (1050000-318216)/1050000=3D=0D=0A69=2E69%=0D=0A C=2E (chk= size-(udfree+nfree))/chksize =3D> (1050000-368213)/1050000=3D=0D=0A64=2E93%= =0D=0A D=2E (udsize -udfree)/udsize =3D> (908121-318216)/908121= =3D=0D=0A64=2E96%=0D=0A E=2E (udsize -(udfree+nfree))/udsize =3D> (90= 8121-368213)/908121 =3D=0D=0A59=2E45%=0D=0A=0D=0AI made the assumption tha= t calculation A is accurate for meta data=0D=0Aspace=2E=0D=0AI made the ass= umption that calculation C is accurate for total space=0D=0Ausage=2E=0D=0AI= made the assumption that calculation D is accurate for user space=0D=0Ausa= ge=2E=0D=0A=0D=0ACalculation B seems to be looking at the full chunk size, = but only=0D=0Asubtracting the user data space used=2E=0D=0A=0D=0ACalculatio= n E seems to be using the user data size, but subtracting both=0D=0Auser an= d meta data space used=2E=0D=0A=0D=0AAre my assumptions correct? If so, sp= litting sbspaces into "META" and=0D=0A"USER" data (I didn't put in the TOTA= L calculation for the sbspaces),=0D=0Aand all non sbspaces as "REGULAR" spa= ces, does this UNION query bring=0D=0Aback accurate data for all of the dif= ferent dbspaces on the server?=0D=0A=0D=0Aselect name dbspace,=0D=0A = is_sbspace,=0D=0A "REG" space_type,=0D=0A sum(chksize) chunk_si= ze,=0D=0A sum(nfree) chunk_free,=0D=0A sum(chksize)-sum(nfree= ) userdata_used,=0D=0A (((sum(chksize)-sum(nfree)) / sum(chksize)) * = 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysmaster:sysdbspaces= d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbsnum=0D=0A AND i= s_sbspace <> 1=0D=0Agroup by 1,2=0D=0AUNION=0D=0Aselect name dbspace,=0D=0A= is_sbspace,=0D=0A "USR" space_type,=0D=0A sum(udsize) us= erdata_size,=0D=0A sum(udfree) userdata_free,=0D=0A sum(udsize)= -sum(udfree) userdata_used,=0D=0A (((sum(udsize)-sum(udfree)) / sum(u= dsize)) * 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysmaster:s= ysdbspaces d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbsnum=0D= =0A and is_sbspace=3D1=0D=0Agroup by 1,2=0D=0AUNION=0D=0Aselect name dbsp= ace,=0D=0A is_sbspace,=0D=0A "MET" space_type,=0D=0A sum(= mdsize) metadata_size,=0D=0A sum(nfree) metadata_free,=0D=0A su= m(mdsize)-sum(nfree) metadata_used,=0D=0A (((sum(mdsize)-sum(nfree)) = / sum(mdsize)) * 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysm= aster:sysdbspaces d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbs= num=0D=0A and is_sbspace=3D1=0D=0Agroup by 1,2=0D=0Aorder by 1,2;=0D=0A= =0D=0AAny thoughts or suggestions would be appreciated=2E=0D=0A=0D=0AThank = you,=0D=0AGary Andrus=0D=0A=0D=0AThis e-mail may contain confidential or pr= ivileged information=2E If=0Ayou think you have received this e-mail in err= or, please advise the=0Asender by reply e-mail and then delete this e-mail = immediately=2E=0AThank you=2E Aetna

I am going to look into this myself, as the amount of free space listed seems to be far too small right from the start, even on systems that have no smart blobs. If I find anything out, i will let you know. Walter Lowich "Andrus, Gary" <andrusg@aetna.com> wrote: I'm reviewing some space monitors that were previously set up in our=0D=0As= ystems for notification when we hit a percentage threshold for=0D=0Adiffere= nt dbspaces=2E I'm curious about the calculations that were put in=0D=0Afo= r Smart BLOB space usage=2E=0D=0A=0D=0AWhat "space usage" reports should ex= ist for an sbspace and what is the=0D=0Acorrect formula for each? Do we ne= ed separate one for each of the=0D=0Afollowing?=0D=0A=0D=0A Meta Data Us= ed/Free=0D=0A User Data Used/Free=0D=0A Total Data Used/Free=0D=0A=0D= =0AI looked online and I've seen several threads for space usage queries,= =0D=0Abut didn't find any that clearly answered this item specifically=2E = (If=0D=0Ayou know of one, just point me there=2E) I found this thread that= uses=0D=0Audsize, but it's quite out of date (Dec=2E 2000, version 9=2E21)= and I=0D=0Awasn't sure if the SQL was correct=2E=0D=0A=0D=0Ahttp://groups= =2Egoogle=2Ecom/group/comp=2Edatabases=2Einformix/browse_thread/thr=0D=0Aea= d/21dc4618526fa087/4ca3dfabf7d283f6?hl=3Den&lnk=3Dgst&q=3Dudsize#4ca3dfabf7= d=0D=0A283f6=0D=0A=0D=0AI didn't see anything in the IIUG software reposito= ry with the meta data=0D=0Acalculations=2E I also saw no mention of udsize= in the Admin Guide or=0D=0AAdmin Reference Manual while looking for infor= mation in the Informix=0D=0Alibrary:=0D=0A=0D=0Ahttp://www-306=2Eibm=2Ecom/= software/data/informix/pubs/library/ids_100=2Ehtml=0D=0A=0D=0AMy environmen= t information:=0D=0A=0D=0AIDS: 10=2E00=2EUC7X1=0D=0AO/S: SunOS 5=2E10 Gener= ic_125100-05=0D=0A=0D=0ASysmaster Query (output for dbsnum 9, the only sbsp= ace):=0D=0A=0D=0A SELECT C=2Edbsnum, name, is_sbspace, SUM(chksize) ChkS= ize,=0D=0A SUM(nfree) NFree, SUM(mdsize) MdSize,=0D=0A = SUM(udsize) UdSize, SUM(udfree) UdFree=0D=0A FROM sysmaster:sysdbsp= aces d, sysmaster:syschunks c=0D=0A WHERE d=2Edbsnum=3Dc=2Edbsnum=0D=0A= GROUP BY 1, 2, 3=0D=0A ORDER BY 1=0D=0A=0D=0A Dbs name is_= sbspace chksize nfree mdsize udsize udfree=0D=0A =2E=2E=2E=0D=0A 9= sbdefault 1 1050000 50505 141817 908121 318216=0D=0A =2E= =2E=2E=0D=0A=0D=0Aonstat -d output:=0D=0A=0D=0A dbs offset size = free bpages=0D=0A =2E=2E=2E=0D=0A 9 0 500000 = 49994 441831=0D=0A Metadata 58116 0 5811= 6=0D=0A 9 500000 250000 99992 233145=0D=0A = Metadata 16852 8 16852=0D=0A 9 500000 250000 = 168230 233145=0D=0A Metadata 16852 500 16852= =0D=0A 9 750000 50000 0 0=0D=0A Metad= ata 49997 49997 49997=0D=0A =2E=2E=2E=0D=0A=0D=0AOnstat -d show= s a total size of 1050000, same as chksize above=2E=0D=0AOnstat -d shows a = total free space of 318216, same as udfree above=2E=0D=0AOnstat -d shows a = Metadata size of 141817, same as mdsize above=2E=0D=0AOnstat -d shows a Met= adata free space of 50505, same as nfree above=2E=0D=0A=0D=0AThere is no on= stat -d output that matches the udsize (though subtracting=0D=0Amdsize from= chksize leaves a difference of 8 pages - overhead maybe)=2E=0D=0A=0D=0AWhe= n calculating metadata used space for an sbspace, this seems straight=0D=0A= forward=2E=0D=0A=0D=0A Meta Data Used: (141817 - 50505) / 141817 =3D 64= =2E39%=0D=0A=0D=0AShould there be a difference between user space used and = total space=0D=0Aused?=0D=0AWhich of the following should be used for each = calculation?=0D=0A=0D=0A A=2E (mdsize-nfree)/mdsize =3D> (141= 817-50505)/141817 =3D=0D=0A64=2E39%=0D=0A B=2E (chksize-udfree)/chksiz= e =3D> (1050000-318216)/1050000=3D=0D=0A69=2E69%=0D=0A C=2E (chk= size-(udfree+nfree))/chksize =3D> (1050000-368213)/1050000=3D=0D=0A64=2E93%= =0D=0A D=2E (udsize -udfree)/udsize =3D> (908121-318216)/908121= =3D=0D=0A64=2E96%=0D=0A E=2E (udsize -(udfree+nfree))/udsize =3D> (90= 8121-368213)/908121 =3D=0D=0A59=2E45%=0D=0A=0D=0AI made the assumption tha= t calculation A is accurate for meta data=0D=0Aspace=2E=0D=0AI made the ass= umption that calculation C is accurate for total space=0D=0Ausage=2E=0D=0AI= made the assumption that calculation D is accurate for user space=0D=0Ausa= ge=2E=0D=0A=0D=0ACalculation B seems to be looking at the full chunk size, = but only=0D=0Asubtracting the user data space used=2E=0D=0A=0D=0ACalculatio= n E seems to be using the user data size, but subtracting both=0D=0Auser an= d meta data space used=2E=0D=0A=0D=0AAre my assumptions correct? If so, sp= litting sbspaces into "META" and=0D=0A"USER" data (I didn't put in the TOTA= L calculation for the sbspaces),=0D=0Aand all non sbspaces as "REGULAR" spa= ces, does this UNION query bring=0D=0Aback accurate data for all of the dif= ferent dbspaces on the server?=0D=0A=0D=0Aselect name dbspace,=0D=0A = is_sbspace,=0D=0A "REG" space_type,=0D=0A sum(chksize) chunk_si= ze,=0D=0A sum(nfree) chunk_free,=0D=0A sum(chksize)-sum(nfree= ) userdata_used,=0D=0A (((sum(chksize)-sum(nfree)) / sum(chksize)) * = 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysmaster:sysdbspaces= d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbsnum=0D=0A AND i= s_sbspace <> 1=0D=0Agroup by 1,2=0D=0AUNION=0D=0Aselect name dbspace,=0D=0A= is_sbspace,=0D=0A "USR" space_type,=0D=0A sum(udsize) us= erdata_size,=0D=0A sum(udfree) userdata_free,=0D=0A sum(udsize)= -sum(udfree) userdata_used,=0D=0A (((sum(udsize)-sum(udfree)) / sum(u= dsize)) * 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysmaster:s= ysdbspaces d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbsnum=0D= =0A and is_sbspace=3D1=0D=0Agroup by 1,2=0D=0AUNION=0D=0Aselect name dbsp= ace,=0D=0A is_sbspace,=0D=0A "MET" space_type,=0D=0A sum(= mdsize) metadata_size,=0D=0A sum(nfree) metadata_free,=0D=0A su= m(mdsize)-sum(nfree) metadata_used,=0D=0A (((sum(mdsize)-sum(nfree)) = / sum(mdsize)) * 100)::DECIMAL(5,2)=0D=0Apct_userdata_used=0D=0A from sysm= aster:sysdbspaces d, sysmaster:syschunks c=0D=0A where d=2Edbsnum=3Dc=2Edbs= num=0D=0A and is_sbspace=3D1=0D=0Agroup by 1,2=0D=0Aorder by 1,2;=0D=0A= =0D=0AAny thoughts or suggestions would be appreciated=2E=0D=0A=0D=0AThank = you,=0D=0AGary Andrus=0D=0A=0D=0AThis e-mail may contain confidential or pr= ivileged information=2E If=0Ayou think you have received this e-mail in err= or, please advise the=0Asender by reply e-mail and then delete this e-mail = immediately=2E=0AThank you=2E Aetna ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search.

Thanks for the response Walter. Sorry about the original format. I guess that's what I get for sending email through the firewall instead of just posting on the IIUG website. To clear things up, here's the original message, hopefully formatted in a readable fashion ---------------------------- I'm reviewing some space monitors that were previously set up in our systems for notification when we hit a percentage threshold for different dbspaces. I'm curious about the calculations that were put in for Smart BLOB space usage. What "space usage" reports should exist for an sbspace and what is the correct formula for each? Do we need separate one for each of the following? Meta Data Used/Free User Data Used/Free Total Data Used/Free I looked online and I've seen several threads for space usage queries, but didn't find any that clearly answered this item specifically. (If you know of one, just point me there.) I found this thread that uses udsize, but it's quite out of date (Dec. 2000, version 9.21) and I wasn't sure if the SQL was correct. http://groups.google.com/group/comp.databases.informix/browse_thread/thr ead/21dc4618526fa087/4ca3dfabf7d283f6?hl=en&lnk=gst&q=udsize#4ca3dfabf7d 283f6 I didn't see anything in the IIUG software repository with the meta data calculations. I also saw no mention of udsize in the Admin Guide or Admin Reference Manual while looking for information in the Informix library: http://www-306.ibm.com/software/data/informix/pubs/library/ids_100.html My environment information: IDS: 10.00.UC7X1 O/S: SunOS 5.10 Generic_125100-05 Sysmaster Query (output for dbsnum 9, the only sbspace): SELECT C.dbsnum, name, is_sbspace, SUM(chksize) ChkSize, SUM(nfree) NFree, SUM(mdsize) MdSize, SUM(udsize) UdSize, SUM(udfree) UdFree FROM sysmaster:sysdbspaces d, sysmaster:syschunks c WHERE d.dbsnum=c.dbsnum GROUP BY 1, 2, 3 ORDER BY 1 Dbs name is_sbspace chksize nfree mdsize udsize udfree ... 9 sbdefault 1 1050000 50505 141817 908121 318216 ... onstat -d output: dbs offset size free bpages ... 9 0 500000 49994 441831 Metadata 58116 0 58116 9 500000 250000 99992 233145 Metadata 16852 8 16852 9 500000 250000 168230 233145 Metadata 16852 500 16852 9 750000 50000 0 0 Metadata 49997 49997 49997 ... Onstat -d shows a total size of 1050000, same as chksize above. Onstat -d shows a total free space of 318216, same as udfree above. Onstat -d shows a Metadata size of 141817, same as mdsize above. Onstat -d shows a Metadata free space of 50505, same as nfree above. There is no onstat -d output that matches the udsize (though subtracting mdsize from chksize leaves a difference of 8 pages - overhead maybe). When calculating metadata used space for an sbspace, this seems straight forward. Meta Data Used: (141817 - 50505) / 141817 = 64.39% Should there be a difference between user space used and total space used? Which of the following should be used for each calculation? A. (mdsize-nfree)/mdsize => (141817-50505)/141817 = 64.39% B. (chksize-udfree)/chksize => (1050000-318216)/1050000= 69.69% C. (chksize-(udfree+nfree))/chksize => (1050000-368213)/1050000= 64.93% D. (udsize -udfree)/udsize => (908121-318216)/908121 = 64.96% E. (udsize -(udfree+nfree))/udsize => (908121-368213)/908121 = 59.45% I made the assumption that calculation A is accurate for meta data space. I made the assumption that calculation C is accurate for total space usage. I made the assumption that calculation D is accurate for user space usage. Calculation B seems to be looking at the full chunk size, but only subtracting the user data space used. Calculation E seems to be using the user data size, but subtracting both user and meta data space used. Are my assumptions correct? If so, splitting sbspaces into "META" and "USER" data (I didn't put in the TOTAL calculation for the sbspaces), and all non sbspaces as "REGULAR" spaces, does this UNION query bring back accurate data for all of the different dbspaces on the server? select name dbspace, is_sbspace, "REG" space_type, sum(chksize) chunk_size, sum(nfree) chunk_free, sum(chksize)-sum(nfree) userdata_used, (((sum(chksize)-sum(nfree)) / sum(chksize)) * 100)::DECIMAL(5,2) pct_userdata_used from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum AND is_sbspace <> 1 group by 1,2 UNION select name dbspace, is_sbspace, "USR" space_type, sum(udsize) userdata_size, sum(udfree) userdata_free, sum(udsize)-sum(udfree) userdata_used, (((sum(udsize)-sum(udfree)) / sum(udsize)) * 100)::DECIMAL(5,2) pct_userdata_used from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and is_sbspace=1 group by 1,2 UNION select name dbspace, is_sbspace, "MET" space_type, sum(mdsize) metadata_size, sum(nfree) metadata_free, sum(mdsize)-sum(nfree) metadata_used, (((sum(mdsize)-sum(nfree)) / sum(mdsize)) * 100)::DECIMAL(5,2) pct_userdata_used from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and is_sbspace=1 group by 1,2 order by 1,2; Any thoughts or suggestions would be appreciated. Thank you, Gary Andrus