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

Thanks, Please make sure you use html at the end or it cannot find the page. Regards - Lester http://www.advancedatatools.com/Articles/Sysmaster/Sysmaster.html Rupan3rd wrote: > > 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 -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 Visit our Web page: http://www.advancedatatools.com Brio Mid Atlantic User Group: http://www.advancedatatools.com/BrioUserGroup/index.html Washington Area IBM-Informix User Group: http://www.iiug.org/~waiug/ ______________________________________________________________________ sending to informix-list