RE: How to retrieve DBSPACE where a database is stored?

This won't work directly for you, but may point you in the right direction... To locate what dbspace the database is in, I use: select name, trunc(p.partnum/1048576) as dbspace from sysdatabases ; (but I have consistent 2GB chunks/dbspaces). I rarely use that, but more often: select t.dbsname, trunc(h.partnum/1048576) as tab_dbs, trunc(e.te_physaddr/1048576) as chunk, sum(h.nptotal) as nptotal, sum(h.npused) as npused, sum(h.npdata) as npdata from systabnames t, sysptnhdr h, systabextents e where t.partnum = h.partnum and h.partnum = e.te_partnum group by 1,2,3 order by 1,2,3; The partnum in this query gives me the sum of pages for each dbspace for each database. The "1048576" number of pages per chunk won't work right for you where your chunks vary in size... you will have to find a better way of retrieveing the right dbspace/chunk. syschunks has the info, but I don't think it will be straight-forward to figure it out... could do it in a program, just not in simple query... The "oncheck -pe" solution is probably better given your dbspaces/chunks... Do you have multiple databases in the instances? If so, this is not really want what you're need. I use "onstat -d | grep 'PO' | sort -n +5 | more" to look for low dbspaces, and "oncheck -pe" output to determine what is in a given chunk... then work it from there. (Actually, I run ServerMetrics monitor to watch for low chunks and alert me when one happens, then I use these commands. I have many databases in hundreds of chunks over many machines.) BTW: There are sysmaster differences are between versions... HTH -----Original Message----- From: Rupan3rd [mailto:rupan.nospam.3rd@hotmail.com] Sent: Monday, June 16, 2003 1:51 AM To: informix-list@iiug.org Subject: How to retrieve DBSPACE where a database is stored? Hi there! How can I query SYSMASTER (or SYSUTILS?) database in order to retrieve the information about what DBSPACE is containing my application's database? And also, what CHUNKS do compose a DBSPACE and how much are they filled of data? My aim is to make a script to control space available in the dbspace where application database is stored. This should be dynamic, because I don't manage only one single installation... :) Can anyone help? Thanx in advance Alessandro orc@taz:/> uname -a SunOS taz 5.8 Generic_108528-12 sun4u sparc SUNW,UltraAX-i2 orc@taz:/> onstat -d Informix Dynamic Server 2000 Version 9.21.UC5 -- On-Line -- Up 7 days 01:01:20 -- 253952 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 1810b7d0 1 0x1 1 2 N informix rootdbs 18d785d0 2 0x2001 2 1 N T informix tmp_dbs 18d78718 3 0x1 3 1 N informix log_dbs 3 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname 1810b918 1 1 0 1000000 959570 PO- /dev/informix_root1 18d78180 2 2 0 100000 99947 PO- /dev/informix_tmp 18d782f0 3 3 100000 150000 124947 PO- /dev/informix_tmp 18d78460 4 1 0 1000000 415044 PO- /dev/informix_root2 4 active, 2047 maximum ********************************************************************** Privileged/Confidential information may be contained in this message. If you are not an addressee indicated in this message (or responsible for delivery of the message to such person[s]), you may not copy or deliver this message to anyone. If you have received this message in error, you should destroy and delete it from your computer and notify the sender by reply email. Opinions, conclusions and other information in this message that do not relate to the official business of Diversified Collection Services shall be understood as neither given nor endorsed by the company. Accordingly, Diversified Collection Services disclaims all responsibility and accepts no responsibility for the consequences of any person(s) acting, or refraining from acting, on such information prior to the receipt by that person(s) of subsequent written confirmation. **********************************************************************

Many thanx everybody for the real good quality answers. In the meantime, I found out in an old post the following reference to a really nice document (by Mr. Lester Knutsen): http://www.advancedatatools.com/Articles/Sysmaster/Sysmaster.htm where it suggests a couple of queries that can be of help for me: 1) find what dbspace holds a database: ------------------------------------------------------------------------------------- -- dblist. sql -- use dbinfo function to convert partnum to dbspace SELECT dbinfo("DBSPACE", partnum) dbspace, name database, owner, is_logging, is_buff_log FROM sysdatabases ORDER BY dbspace, name; ------------------------------------------------------------------------------------- -- output example dbspace rootdbs database cds owner orc is_logging 1 is_buff_log 1 ------------------------------------------------------------------------------------- 2) find percentage of free space in a dbspace (independently of the number of chunks used) ------------------------------------------------------------------------------------- -- dbsfree. sql SELECT d.dbsnum, name dbspace, sum( chksize) Pages_size, sum( chksize) -sum( nfree) Pages_used, sum( nfree) Pages_free, round (( sum( nfree)) / (sum( chksize)) * 100, 2) Percent_free FROM sysdbspaces d, syschunks c WHERE d. dbsnum = c. dbsnum AND d. is_blobspace = 0 GROUP BY group by 1, 2 ORDER BY 1; ------------------------------------------------------------------------------------- -- output example dbsnum 1 dbspace rootdbs pages_size 2000000 pages_used 625394 pages_free 1374606 percent_free 68.73 ------------------------------------------------------------------------------------- I think I will mix a bit of all the suggestions to come out with the query I need. Thanx again! ciao Alessandro Zunino, Kathy wrote: > This won't work directly for you, but may point you in the right direction... To locate what dbspace the database is in, I use: > > select name, trunc(p.partnum/1048576) as dbspace from sysdatabases ;> > (but I have consistent 2GB chunks/dbspaces). I rarely use that, but more often: > > select > t.dbsname, > trunc(h.partnum/1048576) as tab_dbs, > trunc(e.te_physaddr/1048576) as chunk, > sum(h.nptotal) as nptotal, > sum(h.npused) as npused, > sum(h.npdata) as npdata > from systabnames t, sysptnhdr h, systabextents e > where t.partnum = h.partnum > and h.partnum = e.te_partnum > group by 1,2,3 > order by 1,2,3; > > The partnum in this query gives me the sum of pages for each dbspace for each database. The "1048576" number of pages per chunk won't work right for you where your chunks vary in size... you will have to find a better way of retrieveing the right dbspace/chunk. syschunks has the info, but I don't think it will be straight-forward to figure it out... could do it in a program, just not in simple query... The "oncheck -pe" solution is probably better given your dbspaces/chunks... > > > Do you have multiple databases in the instances? If so, this is not really want what you're need. I use "onstat -d | grep 'PO' | sort -n +5 | more" to look for low dbspaces, and "oncheck -pe" output to determine what is in a given chunk... then work it from there. (Actually, I run ServerMetrics monitor to watch for low chunks and alert me when one happens, then I use these commands. I have many databases in hundreds of chunks over many machines.) > > > BTW: There are sysmaster differences are between versions... > > HTH > > > -----Original Message----- > From: Rupan3rd [mailto:rupan.nospam.3rd@hotmail.com] > Sent: Monday, June 16, 2003 1:51 AM > To: informix-list@iiug.org > Subject: How to retrieve DBSPACE where a database is stored? > > > Hi there! > > How can I query SYSMASTER (or SYSUTILS?) database in order to retrieve > the information about what DBSPACE is containing my application's > database? And also, what CHUNKS do compose a DBSPACE and how much > are they filled of data? > > My aim is to make a script to control space available in the dbspace > where application database is stored. This should be dynamic, because > I don't manage only one single installation... :) > > Can anyone help? > > Thanx in advance > Alessandro > > orc@taz:/> uname -a > SunOS taz 5.8 Generic_108528-12 sun4u sparc SUNW,UltraAX-i2 > orc@taz:/> onstat -d > > Informix Dynamic Server 2000 Version 9.21.UC5 -- On-Line -- Up 7 > days 01:01:20 -- 253952 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > 1810b7d0 1 0x1 1 2 N informix rootdbs > 18d785d0 2 0x2001 2 1 N T informix tmp_dbs > 18d78718 3 0x1 3 1 N informix log_dbs > 3 active, 2047 maximum > > Chunks > address chk/dbs offset size free bpages flags pathname > 1810b918 1 1 0 1000000 959570 PO- > /dev/informix_root1 > 18d78180 2 2 0 100000 99947 PO- /dev/informix_tmp > 18d782f0 3 3 100000 150000 124947 PO- /dev/informix_tmp > 18d78460 4 1 0 1000000 415044 PO- > /dev/informix_root2 > 4 active, 2047 maximum