sbspace space monitoring SQL

Dear All, We are working on IDS 14.10 We have added three SBSPACE in our dbspace , and for checking the free space among all dbspace we run below SQL: database sysmaster;select name[1,8] dbspace, -- name truncated to fit on one line sum(chksize) Pages_size, -- sum of all chuncks size pages sum(chksize) - sum(nfree) Pages_used, sum(nfree) Pages_free, -- sum of all chunks free pages round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum group by 1 order by 5 desc; Above SQL shows correct spaces for all dbspace, but same SQL every time shows SBSPACE 5% every time. Like below sbs001 has no space left but when run above SQL, still it shows sbspace 5% free After I added 3 chunks of 2 GB each, and ran the above SQL again, shown the same free space 5%. Can someone please help to get the SQL for checking space for SBSPACE. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------

Amit: Here is the stored procedures that my dbsavail utility installs into sysmaster to do the heavy lifting calculating free space and percentages. Note that blobspaces and smart blobspaces use a different calculation than "normal" dbspaces. Feel free to use this proc, or better yet, just install my utils2_ak package (free), build it, and install dbsavail and use that. It has several features to adjust what is reported to your needs. Anyway, the proc: CREATE PROCEDURE "informix".dbsavail ( scale int default 1 ) returning int as dbsn, char(128) as dbname, char(1) as aflag, int as blob_page_kb, decimal(32,0) as total_size_pgs, decimal(32,0) as free_size_pgs, decimal(32, 0) as total_size_kb, decimal(32, 0)as free_size_kb, decimal(32, 0) as pagesize_sz, decimal(16,3) as free_pct, int as basepage, decimal(32, 0) as used_pgs, decimal(32, 0) as used_kb, decimal(16,3) as used_pct, decimal(16,3) as bfree_pct, decimal(16,3) as bused_pct; define dbname char(128); define aflag char(1); define total_size, free_size decimal(32,0); define total_k_size, free_k_size decimal(32,0); define free_pct, bfree_pct decimal(16,3); define temp, blob, sbspc smallint; define basepage, pagsizeK, blbpagesz, vers, dbsn int; select pagesize into basepage from syschunks_fast where chknum = 1; foreach select d.name, d.is_temp, d.is_blobspace, d.is_sbspace, c.pagesize/1024 blobpageK, sum(chksize) total_size, case when is_blobspace = 1 then sum(nfree * (c.pagesize/2048)) when is_sbspace = 1 then sum(udfree + nfree) else sum(nfree) end as nfree, d.pagesize/1024 pagsizeK, ((sum(nfree)::decimal(32,10) / sum(chksize)::decimal(32,10)) * 100.00)::decimal(16,3) free_pct, ((sum(nfree*(c.pagesize/(select pagesize from syschunks_fast where chknum = 1)))::decimal(32,10) / sum(chksize)::decimal(32,10)) * 100.00)::decimal(16,3) bfree_pct, d.dbsnum into dbname, temp, blob, sbspc, blbpagesz, total_size, free_size, pagsizeK, free_pct, bfree_pct, dbsn from sysmaster:sysdbspaces d, sysmaster:syschunks c,sysmaster:sysdbstab t where d.dbsnum = c.dbsnum and c.dbsnum = t.dbsnum group by 1,2,3,4,5,8,11 order by 7 if (sbspc == 1 and temp == 1) then let aflag = 'U'; let bfree_pct = free_size*100/total_size; elif (sbspc == 1) then let aflag = 'S'; let bfree_pct = free_size*100/total_size; elif (temp == 1) then let aflag = 'T'; elif (blob == 1) then let aflag = 'B'; let free_pct = bfree_pct; else let aflag = 'N'; end if let total_k_size = total_size * (basepage/1024); let free_k_size = free_size * (basepage/1024); return dbsn, dbname, aflag, blbpagesz, total_size, free_size, total_k_size/scale, free_k_size/scale, pagsizeK, free_pct, basepage, (total_size - free_size), (total_k_size - free_k_size)/scale, (100.00 - free_pct), bfree_pct, (100.00 - bfree_pct) with resume; end foreach; end procedure; Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Dear Art, I just changed the my 1st SQL like below : Database Sysmaster; select name[1,8] dbspace, -- name truncated to fit on one lineround(sum((chksize)/1024/1024),2) size_in_GB, -- sum of all chuncks size pages round(sum((udfree)/1024/1024),2) free_space_in_GB, -- sum of all chunks free pages round ((sum(udfree)/1024/1024) / (sum(chksize)/1024/1024) * 100, 2) percent_free from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and d.name matches "sbs*" group by 1 order by 4 desc; This SQL is giving me desired result. I used column "udfree" instead of "nfree" So far I've added 35 chunks in SBSPACES, and few chunks are totally used and few mostly used, but I'm getting correct output. if later it gets fails, so can write an Shell script to read the Chunk Section and grab "size" and "free" column for a particular SPACENAME. Please let me know is above SQL correct or not? Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------

Almost Amit. You have to add in nfree to udfree to include the metadata pages: select name[1,8] dbspace, -- name truncated to fit on one line round(sum((chksize)/1024/1024),2) size_in_GB, -- sum of all chuncks size pages round(sum((udfree+nfree)/1024/1024),2) free_space_in_GB, -- sum of all chunks free pages round ((sum(udfree+nfree)/1024/1024) / (sum(chksize)/1024/1024) * 100, 2) percent_free from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and d.name matches "sbs*" group by 1 order by 4 desc; Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------