Re: How to check when database is getting full?
>
> Is there any way -- preferably with an SQL command -- to know how much
> space remains in a database space (other than looking with tbmonitor).
> If there's no SQL command, is there some ESQL function? If not, is
> there some Informix utility that can be run with system()?
>
> Thanks in advance, Mark
With Online 6 and higher you can check the sysmasters tables. Otherwise use
tbstat -d. The following code snippets do that. In the first
(chk_dbsp_fr()) you must set $pcnt_th (percent threshold) before invoking.
In the second (chk_chunk()) (a stored procedure which works against the
sysmasters database) you invoke it with a verbosity of 0, 1 or 2. With 0
it returns only errors. With 1 it returns status. With 2 it returns config
information. Space free is returned for both 1 and 2.
Hope that helps.
cheers
j.
###############################################################################
# Disk usage (alert when dbspace full)
chk_dbsp_fr()
{
tbstat -d | awk '
BEGIN { p_flg = 0 }
/Chunks/ {p_flg = 4}
/active,/ {p_flg = 0}
{
if (p_flg > 1) p_flg--
if (p_flg == 1 && $7 != "MO-" ) {
p_free = $6/$5*100
if (p_free < pcnt_th)
printf("Warning: Chunk %d of disk %s is %d percent free\\n",
$2, $8, p_free);
}
}' pcnt_th=$pcnt_th
p_free=""
}
------------------------------------------------------------------------
DROP PROCEDURE chk_chunk;
CREATE PROCEDURE chk_chunk(wordy SMALLINT)
DEFINE p_cmd CHAR(2000);
DEFINE p_fname CHAR(128);
DEFINE p_chksize INTEGER;
DEFINE p_nfree INTEGER;
DEFINE p_isoff SMALLINT;
DEFINE p_isrec SMALLINT;
DEFINE p_isinc SMALLINT;
DEFINE p_mfname CHAR(128);
DEFINE p_chknum INTEGER;
IF wordy = 0 THEN -- errors only
SELECT fname, mfname
INTO p_fname, p_mfname
FROM syschunks
WHERE is_offline != 0; LET p_cmd = 'echo "Chunk " || p_fname || " is off line. " >> /tmp/DBAwarn';
system p_cmd;
SELECT fname, mfname
INTO p_fname, p_mfname
FROM syschunks
WHERE mis_offline != 0; LET p_cmd = 'echo "Mirror " || p_mfname || " is off line. " >> /tmp/DBAwarn';
system p_cmd;
END IF
IF wordy = 1 THEN -- status only
FOREACH SELECT fname, chksize, nfree, is_offline, is_recovering,
is_inconsistent, mfname, chknum
INTO p_fname, p_chksize, p_nfree, p_isoff, p_isrec, p_isinc, p_mfname,
p_chknum
FROM syschunks
LET p_cmd = 'echo "Chunk : "'|| p_chknum || '\\\\n' ||
'" : "'|| p_fname || '\\\\n' ||
'"Mirror : "'|| p_mfname || '\\\\n' ||
'"Size : "'|| p_chksize || '\\\\n' ||
'"Free : "'|| p_nfree || '\\\\n' ||
'"Offline : "'|| p_isoff || '\\\\n' ||
'"Recovering : "'|| p_isrec || '\\\\n' ||
'"Inconsistent : "'|| p_isinc || '\\\\n\\\\n' ||
'>> /tmp/DBAwarn';
system p_cmd;
END FOREACH
END IF
IF wordy = 2 THEN -- config
FOREACH SELECT fname, chksize, nfree, mfname, chknum
INTO p_fname, p_chksize, p_nfree, p_mfname, p_chknum
FROM syschunks
LET p_cmd = 'echo "Chunk : "'|| p_chknum || '\\\\n' ||
'" : "'|| p_fname || '\\\\n' ||
'"Mirror : "'|| p_mfname || '\\\\n' ||
'"Size : "'|| p_chksize || '\\\\n' ||
'"Free : "'|| p_nfree || '\\\\n\\\\n' ||
'>> /tmp/DBAwarn';
system p_cmd;
END FOREACH
END IF
END PROCEDURE;
-------------------------------------
________________________________________________________________________
Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA (x65388)
jparker@boi.hp.com Currently on loan to CTSB/DTC
________________________________________________________________________
Outside of a dog a book is a man's best friend.
Inside of a dog it's too dark to read. (Groucho Marx)
________________________________________________________________________
Any opinions expressed herein are my own and not those of my employers.
________________________________________________________________________