Space Available for Next Extent
Clifton Bean asked for a script/query to check whether a table's dbspace still has room for its next extent. CJ Butcher posted a Perl script parsing 'onstat -d' for per-dbspace used/free percentages with email alerts; Art Kagel gave an SQL query joining systabnames, sysptnhdr, sysdbspaces, syschunks and syschfree to list free contiguous chunk extents at least as big as the table's nextsiz (with sysfragments for fragmented tables); Robert Sosnowski supplied a 'critical_sizes' stored procedure reporting all tables at once. Bean adopted the procedure; Sosnowski noted it only reflects system-table counters, so non-contiguous/reclaimed space and partly empty pages make the figures approximate.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix v5/v9/v10 AIX 4.2/4.3.3/5.2 Has anyone written or know of a shell script that will review the space currently available within a table's dbspace and determine whether there is space available for the table's next extent to be created within that dbspace? If you do, would you mind publishing it or letting me know of its location. I scanned the IIUG repository but did not see anything that I would recognize as doing this task. Thanks in advance. Clifton Bean _________________________________________________________________ With Windows Live Hotmail, you can personalize your inbox with your favorite color. www.windowslive-hotmail.com/learnmore/personalize.html?locale=en-us&ocid=TXT_TAG LM_HMWL_reten_addcolor_0607
This is a perl script I wrote to run on my windows server. Could easily be
converted to Linux/Unix.
#!/bin/perl -w
sub create_onstat_d_out_files
{
system("onstat -d > $LOGDIR\\\\\\\\onstat_d.out");
system("type $LOGDIR\\\\\\\\onstat_d.out | find \\\\"informix \\\\" | find /V \\\\"tmpdbs\\\\" |
find /V \\\\"logdbs\\\\" | find /V \\\\"physdbs\\\\" > $LOGDIR/onstat_d_dbspaces.out");
system("type $LOGDIR\\\\\\\\onstat_d.out | find \\\\"sapdata\\\\" >
$LOGDIR/onstat_d_chunks.out");
open(dbspace_h, "$LOGDIR/onstat_d_dbspaces.out");
open(dbspace_h2, ">$LOGDIR/onstat_d_dbspaces2.out");
open(chunks_h, "$LOGDIR/onstat_d_chunks.out");
open(low_db_h, ">$LOGDIR/low_dbspace_info.out");
}
sub print_header
{
printf dbspace_h2 ("%-3s%-15s%-15s%-15s%-15s%-5s\\
\\
", No, Name, Size_KB,
Free_KB, Used_KB, '%Used');
}
sub create_dbspace_report
{
while (<dbspace_h>)
{
@dbspace_array=split(' ', $_);
$size_var=0;
$free_var=0;
$used_var=0;
open(chunks_h, "$LOGDIR/onstat_d_chunks.out");
while (<chunks_h>)
{
@chunks_array=split(' ', $_);
if ( $dbspace_array[1] == $chunks_array[2] )
{
$size_var= $size_var + $chunks_array[4];
$free_var= $free_var + $chunks_array[5];
}
}
$size_var=4*$size_var;
$free_var=4*$free_var;
$used_var=$size_var - $free_var;
$percentage_used=int($used_var/$size_var * 100);
printf dbspace_h2 ("%-3s%-15s%-15s%-15s%-15s%-5s\\
", $dbspace_array[1],
$dbspace_array[7], $size_var, $free_var, $used_var, $percentage_used);
if ($percentage_used > $percentage_threshold)
{
print low_db_h "$hostname_var:$date_var:$time_var: $dbspace_array[7] is
$percentage_used\\\\% full \\
";
$LOGVOLUMES=1;
}
}
close(dbspace_h);
close(dbspace_h2);
close(chunks_h);
close(low_db_h);
}
sub aytest
{
open(dbinfo_h, "DbspaceInfo.out") || die "Can't open the file: $!";
while (<dbinfo_h>)
{
@array_dbspace=split(' ', $_);
print "element2= $array_dbspace[2]\\
";
}
close(dbinfo_h);
}
sub send_mail
{
`blat $LOGDIR/onstat_d_dbspaces2.out -to dba@yourmail.com -subject "informix
Alert:dbspace usage report on $hostname_var"`;
if ($LOGVOLUMES==1)
{
`blat $LOGDIR/low_dbspace_info.out -to dba@yourmail.com -subject "informix
Alert:low dbspace usage report on $hostname_var"`;
}
}
################# Main #############################
# set the environment here.
$LOGVOLUMES=0;
$LOGDIR="C:\\\\informix\\\\scripts";
$hostname_var=`hostname`;
chomp $hostname_var;
$date_var=`date /t`;
chomp $date_var;
$time_var=`time /t`;
chomp $time_var;
$percentage_threshold=90;
create_onstat_d_out_files;
print_header;
create_dbspace_report;
send_mail;
select stn.dbsname, stn.tabname, trim(sds.name), sds.dbsnum, sch.chknum,scf.leng
from systabnames as stn
join sysptnhdr as sph
on stn.partnum = sph.partnum and tabname = 'portfolio_detail'
and dbsname = 'plhdb'
join sysdbspaces as sds
on sds.dbsnum = round(sph.lockid / 1048576)
join syschunks as sch
on sch.dbsnum = sds.dbsnum
join syschfree as scf
on sch.chknum = scf.chknum
where scf.leng > sph.nextsiz
order by scf.leng asc;
For IDS10.00+ you can add the name of the fragment for fragmented tables by
outer joining to <dbsname>.sysfragments for the partition column there.
Art S. Kagel
----- Original Message -----
From: Clifton Bean <ids@iiug.org>
At: 6/21 14:52:28
Informix v5/v9/v10
AIX 4.2/4.3.3/5.2
Has anyone written or know of a shell script that will review the space
currently available within a table's dbspace and determine whether there is
space available for the table's next extent to be created within that dbspace?
If you do, would you mind publishing it or letting me know of its location. I
scanned the IIUG repository but did not see anything that I would recognize as
doing this task.
Thanks in advance.
Clifton Bean
_________________________________________________________________
With Windows Live Hotmail, you can personalize your inbox with your favorite
color.
www.windowslive-hotmail.com/learnmore/personalize.html?locale=en-us&ocid=TXT_TAG
LM_HMWL_reten_addcolor_0607
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Explanation: The SQL below prints the free continguous extents in each chunk of
the dbspace(s) that contain data for a table which are at least as large as the
table's next extent size (I changed the '>=' below from the original strictly
'>' I previously posted) order ascending which should be the order in which
they are likely to be allocated.
select stn.dbsname, stn.tabname, trim(sds.name), sds.dbsnum, sch.chknum,scf.leng
from systabnames as stn
join sysptnhdr as sph
on stn.partnum = sph.partnum and tabname = 'portfolio_detail'
and dbsname = 'plhdb'
join sysdbspaces as sds
on sds.dbsnum = round(sph.lockid / 1048576)
join syschunks as sch
on sch.dbsnum = sds.dbsnum
join syschfree as scf
on sch.chknum = scf.chknum
where scf.leng >= sph.nextsiz
order by scf.leng asc;
For IDS10.00+ you can add the name of the fragment for fragmented tables by
outer joining to <dbsname>.sysfragments for the partition column there.
Art S. Kagel
----- Original Message -----
From: Clifton Bean <ids@iiug.org>
At: 6/21 14:52:28
Informix v5/v9/v10
AIX 4.2/4.3.3/5.2
Has anyone written or know of a shell script that will review the space
currently available within a table's dbspace and determine whether there is
space available for the table's next extent to be created within that dbspace?
If you do, would you mind publishing it or letting me know of its location. I
scanned the IIUG repository but did not see anything that I would recognize as
doing this task.
Thanks in advance.
Clifton Bean
_________________________________________________________________
With Windows Live Hotmail, you can personalize your inbox with your favorite
color.
www.windowslive-hotmail.com/learnmore/personalize.html?locale=en-us&ocid=TXT_TAG
LM_HMWL_reten_addcolor_0607
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Clifton Bean wrote:
> Informix v5/v9/v10
> AIX 4.2/4.3.3/5.2
>
> Has anyone written or know of a shell script that will review the space
> currently available within a table's dbspace and determine whether there is
> space available for the table's next extent to be created within that
dbspace?
>
> If you do, would you mind publishing it or letting me know of its location. I
> scanned the IIUG repository but did not see anything that I would recognize
as
> doing this task.
>
> Thanks in advance.
> Clifton Bean
>
> _________________________________________________________________
> With Windows Live Hotmail, you can personalize your inbox with your favorite
> color.
>
>
www.windowslive-hotmail.com/learnmore/personalize.html?locale=en-us&ocid=TXT_TAG
LM_HMWL_reten_addcolor_0607
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
I've wrote stored procedure (attached) which shows available space. I' m
using it this way:
execute procedure critical_sizes();
select *
from critical_sizes;
Returned sizes are in pages. You can get info about: first extent size
(fext), next extent size (next), allocated pages (nptotal), free pages
(unallocated yet) in dbspace (nfree). Last column: 'critical_size' is a
sum of nfree and free space inside tablespace (nptotal-npused),
Besides we have some reports which alerts about possibility of
exhausting available space. They are using temporary table
critical_sizes which is created by the procedure. I can't share these
reports but they have very simple rules:
List all tablespaces which:
- next extent size is greater than nfree more than 10 % (next>1.1*nfree)
and
- free space inside allocated extent is less than 10% (
(nptotal-npused)/nptotal <0.1)
and
- tablespace is not fragmented (hardcoded list of such tables);
For fragmented tables critical_size must be more than 10% of used space
(npused).
HTH,
Robert Sosnowski
create DBA procedure "sosnowsr".critical_sizes(fdbsnamemask char(32) default
'*', ftabnamemask char(32) default '*', fkb integer default 0, ftemptable
integer default 1)
returning char(32), -- dbsname
char(32), -- tabname
integer, -- total allocated by table
integer, -- used by table
integer, -- data in table
integer, -- next extent size
integer, -- first extent size
integer, -- number of extents
integer, -- allocated for chunks
integer, -- free (unallocated) within the chunks
integer, -- number of chunks;
integer; -- critical free;
Define fdbsname, ftabname char(32);
Define fnptotal, fnpused, fnpdata, fdbsnum, fchksize, fnfree, fnchunks,
fnextsiz, ffextsiz, fnextns, fcriticalfree Integer;
Define fpagesize Integer;
begin
on exception
end exception with resume;
drop table ttabnames;
drop table tdbspaces;end;
if fkb<>0 then
select sh_pagesize
into fpagesize
from sysmaster:sysshmvals;end if;
if ftemptable <>0 then
begin
on exception
end exception with resume;
drop table critical_sizes;
drop table ttabnames;
drop table tdbspaces;end;
create temp table critical_sizes (
dbsname char(32), -- dbsname
tabname char(32), -- tabname
nptotal integer, -- total allocated by table
npused integer, -- used by table
npdata integer, -- data in table
nextsiz integer, -- next extent size
fextsiz integer, -- first extent size
nextns integer, -- number of extents
chksize integer, -- allocated for chunks
nfree integer, -- free (unallocated) within the chunks
nchunks integer, -- number of chunks;
criticalfree integer -- critical free;
) with no log;
end if;
select tabname, partnum, sysmaster:partdbsnum(partnum) dbsnum
from sysmaster:systabnames
where tabname matches ftabnamemask
into temp ttabnames with no log;
select name, dbsnum, nchunks
from sysmaster:sysdbspaces
where name matches fdbsnamemask
into temp tdbspaces with no log;
update statistics high for table ttabnames;
update statistics high for table tdbspaces;
if ftemptable <>0 then
foreach
select distinct s.name dbsname, n.tabname, h.nptotal, h.npused, h.npdata,h.nextsiz, h.fextsiz, h.nextns, s.dbsnum, s.nchunks
Into fdbsname, ftabname, fnptotal, fnpused, fnpdata, fnextsiz, ffextsiz,
fnextns, fdbsnum, fnchunks
-- from sysmaster:systabnames n, sysmaster:sysdbspaces s, sysmaster:sysptnhdr h
from ttabnames n, tdbspaces s, sysmaster:sysptnhdr h
where n.dbsnum=s.dbsnum
and n.tabname matches ftabnamemask
and s.name matches fdbsnamemask
and h.partnum=n.partnum
and n.tabname<>'TBLSpace'
select sum(c.chksize), sum(c.nfree)
Into fchksize, fnfree
from sysmaster:syschktab c
where c.dbsnum=fdbsnum;
Let fcriticalfree=fnptotal - fnpused + fnfree;
if fkb<>0 then
Let fnptotal=trunc(fnptotal*fpagesize/1024);
Let fnpused=trunc(fnpused*fpagesize/1024);
Let fnpdata=trunc(fnpdata*fpagesize/1024);
Let fnextsiz=trunc(fnextsiz*fpagesize/1024);
Let ffextsiz=trunc(ffextsiz*fpagesize/1024);
Let fchksize=trunc(fchksize*fpagesize/1024);
Let fnfree=trunc(fnfree*fpagesize/1024);
Let fcriticalfree=trunc(fcriticalfree*fpagesize/1024);
end if;
insert into critical_sizes values( fdbsname, ftabname, fnptotal, fnpused,
fnpdata, fnextsiz, ffextsiz, fnextns, fchksize, fnfree, fnchunks,fcriticalfree);
End Foreach;
else
foreach
select distinct s.name dbsname, n.tabname, h.nptotal, h.npused, h.npdata,h.nextsiz, h.fextsiz, h.nextns, s.dbsnum, s.nchunks
Into fdbsname, ftabname, fnptotal, fnpused, fnpdata, fnextsiz, ffextsiz,
fnextns, fdbsnum, fnchunks
-- from sysmaster:systabnames n, sysmaster:sysdbspaces s, sysmaster:sysptnhdr h
from ttabnames n, tdbspaces s, sysmaster:sysptnhdr h
where n.dbsnum=s.dbsnum
and n.tabname matches ftabnamemask
and s.name matches fdbsnamemask
and h.partnum=n.partnum
and n.tabname<>'TBLSpace'
select sum(c.chksize), sum(c.nfree)
Into fchksize, fnfree
from sysmaster:syschktab c
where c.dbsnum=fdbsnum;
Let fcriticalfree=fnptotal - fnpused + fnfree;
if fkb<>0 then
Let fnptotal=trunc(fnptotal*fpagesize/1024);
Let fnpused=trunc(fnpused*fpagesize/1024);
Let fnpdata=trunc(fnpdata*fpagesize/1024);
Let fnextsiz=trunc(fnextsiz*fpagesize/1024);
Let ffextsiz=trunc(ffextsiz*fpagesize/1024);
Let fchksize=trunc(fchksize*fpagesize/1024);
Let fnfree=trunc(fnfree*fpagesize/1024);
Let fcriticalfree=trunc(fcriticalfree*fpagesize/1024);
end if;
Return fdbsname, ftabname, fnptotal, fnpused, fnpdata, fnextsiz, ffextsiz,
fnextns, fchksize, fnfree, fnchunks, fcriticalfree With Resume;
End Foreach;
end if;
drop table ttabnames;
drop table tdbsp
Thank you. Something that worked for all tables at once was JUST what I was
looking for.
I do have a question though, brought up by some of the other responses: how
does this routine handle space that once was occupied but has now been freed
up -- meaning the free space is not contiguous. If I have two spaces of 100
pages each free and I have a table with a next extent of 150, will this
routine flag that table as not being able to properly grow into the dbspace?
Take care.
Clifton> To: ids@iiug.org> From: robsosno@gmail.com> Subject: Re: Space
Available for Next Extent [9420]> Date: Thu, 21 Jun 2007 16:58:24 -0400> >
Clifton Bean wrote: > > Informix v5/v9/v10 > > AIX 4.2/4.3.3/5.2 > > > > Has
anyone written or know of a shell script that will review the space > >
currently available within a table's dbspace and determine whether there is >
> space available for the table's next extent to be created within that >
dbspace? > > > > If you do, would you mind publishing it or letting me know of
its location. > I > > scanned the IIUG repository but did not see anything
that I would recognize > as > > doing this task. > > > > Thanks in advance. >
> Clifton Bean > > > >
_________________________________________________________________ > > With
Windows Live Hotmail, you can personalize your inbox with your favorite > >
color. > > > > >
www.windowslive-hotmail.com/learnmore/personalize.html?locale=en-us&ocid=TXT_TAG
LM_HMWL_reten_addcolor_0607 > > > > > > >
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
> > > > I've wrote stored procedure (attached) which shows available space. I'
m > using it this way: > execute procedure critical_sizes(); > select * > from
critical_sizes; > > Returned sizes are in pages. You can get info about: first
extent size > (fext), next extent size (next), allocated pages (nptotal), free
pages > (unallocated yet) in dbspace (nfree). Last column: 'critical_size' is
a > sum of nfree and free space inside tablespace (nptotal-npused), > >
Besides we have some reports which alerts about possibility of > exhausting
available space. They are using temporary table > critical_sizes which is
created by the procedure. I can't share these > reports but they have very
simple rules: > List all tablespaces which: > - next extent size is greater
than nfree more than 10 % (next>1.1*nfree) > and > - free space inside
allocated extent is less than 10% ( > (nptotal-npused)/nptotal <0.1) > and > -
tablespace is not fragmented (hardcoded list of such tables); > > For
fragmented tables critical_size must be more than 10% of used space >
(npused). > > HTH, > > Robert Sosnowski > > create DBA procedure
"sosnowsr".critical_sizes(fdbsnamemask char(32) default > '*', ftabnamemask
char(32) default '*', fkb integer default 0, ftemptable > integer default 1) >
returning char(32), -- dbsname > > char(32), -- tabname > > integer, -- total
allocated by table > > integer, -- used by table > > integer, -- data in table
> > integer, -- next extent size > > integer, -- first extent size > >
integer, -- number of extents > > integer, -- allocated for chunks > >
integer, -- free (unallocated) within the chunks > > integer, -- number of
chunks; > > integer; -- critical free; > Define fdbsname, ftabname char(32); >
Define fnptotal, fnpused, fnpdata, fdbsnum, fchksize, fnfree, fnchunks, >
fnextsiz, ffextsiz, fnextns, fcriticalfree Integer; > Define fpagesize
Integer; > > begin > > on exception > > end exception with resume; > > drop
table ttabnames; > > drop table tdbspaces; > end; > > if fkb<>0 then > select
sh_pagesize > into fpagesize > from sysmaster:sysshmvals; > end if; > > if
ftemptable <>0 then > begin > > on exception > > end exception with resume; >
> drop table critical_sizes; > > drop table ttabnames; > > drop table
tdbspaces; > end; > create temp table critical_sizes ( > > dbsname char(32),
-- dbsname > > tabname char(32), -- tabname > > nptotal integer, -- totalallocated by table > > npused integer, -- used by table > > npdata integer, --
data in table > > nextsiz integer, -- next extent size > > fextsiz integer, --
first extent size > > nextns integer, -- number of extents > > chksize
integer, -- allocated for chunks > > nfree integer, -- free (unallocated)
within the chunks > > nchunks integer, -- number of chunks; > > criticalfree
integer -- critical free; > ) with no log; > end if; > > select tabname,
partnum, sysmaster:partdbsnum(partnum) dbsnum > from sysmaster:systabnames >
where tabname matches ftabnamemask > into temp ttabnames with no log; > >
select name, dbsnum, nchunks > from sysmaster:sysdbspaces > where name matchesfdbsnamemask > into temp tdbspaces with no log; > > update statistics high for
table ttabnames; > update statistics high for table tdbspaces; > > if
ftemptable <>0 then > foreach > > select distinct s.name dbsname, n.tabname,
h.nptotal, h.npused, h.npdata, > h.nextsiz, h.fextsiz, h.nextns, s.dbsnum,
s.nchunks > > Into fdbsname, ftabname, fnptotal, fnpused, fnpdata, fnextsiz,
ffextsiz, > fnextns, fdbsnum, fnchunks > -- from sysmaster:systabnames n,
sysmaster:sysdbspaces s, sysmaster:sysptnhdr > h > > from ttabnames n,
tdbspaces s, sysmaster:sysptnhdr h > > where n.dbsnum=s.dbsnum > > and
n.tabname matches ftabnamemask > > and s.name matches fdbsnamemask > > and
h.partnum=n.partnum > > and n.tabname<>'TBLSpace' > > select sum(c.chksize),
sum(c.nfree) > > Into fchksize, fnfree > > from sysmaster:syschktab c > >
where c.dbsnum=fdbsnum; > > Let fcriticalfree=fnptotal - fnpused + fnfree; > >
if fkb<>0 then > > Let fnptotal=trunc(fnptotal*fpagesize/1024); > > Let
fnpused=trunc(fnpused*fpagesize/1024); > > Let
fnpdata=trunc(fnpdata*fpagesize/1024); > > Let
fnextsiz=trunc(fnextsiz*fpagesize/1024); > > Let
ffextsiz=trunc(ffextsiz*fpagesize/1024); > > Let
fchksize=trunc(fchksize*fpagesize/1024); > > Let
fnfree=trunc(fnfree*fpagesize/1024); > > Let
fcriticalfree=trunc(fcriticalfree*fpagesize/1024); > > end if; > > insert into
critical_sizes values( fdbsname, ftabname, fnptotal, fnpused, > fnpdata,
fnextsiz, ffextsiz, fnextns, fchksize, fnfree, fnchunks, > fcriticalfree); >
End Foreach; > else > foreach > > select distinct s.name dbsname, n.tabname,
h.nptotal, h.npused, h.npdata, > h.nextsiz, h.fextsiz, h.nextns, s.dbsnum,
s.nchunks > > Into fdbsname, ftabname, fnptotal, fnpused, fnpdata, fnextsiz,
ffextsiz, > fnextns, fdbsnum, fnchunks > -- from sysmaster:systabnames n,
sysmaster:sysdbspaces s, sysmaster:sysptnhdr > h > > from ttabnames n,
tdbspaces s, sysmaster:sysptnhdr h > > where n.dbsnum=s.dbsnum > > and
n.tabname matches ftabnamemask > > and s.name matches fdbsnamemask > > and
h.partnum=n.partnum > > and n.tabname<>'TBLSpace' > > select sum(c.chksize),
sum(c.nfree) > > Into fchksize, fnfree > > from sysmaster:syschktab c > >
where c.dbsnum=fdbsnum; > > Let fcriticalfree=fnptotal - fnpused + fnfree; > >
if fkb<>0 then > > Let fnptotal=trunc(fnptotal*fpagesize/1024); > > Let
fnpused=trunc(fnpused*fpagesize/1024); > > Let
fnpdata=trunc(fnpdata*fpagesize/1024); > > Let
fnextsiz=trunc(fnextsiz*fpagesize/1024); > > Let
ffextsiz=trunc(ffextsiz*fpagesize/1024); > > Let
fchksize=trunc(fchksize*fpagesize/1024); > > Let
fnfree=trunc(fnfree*fpagesize/1024); > > Let
fcriticalfree=trunc(fcriticalfree
Clifton Bean wrote: > Thank you. Something that worked for all tables at once was JUST what I was > looking for. > > I do have a question though, brought up by some of the other responses: how > does this routine handle space that once was occupied but has now been freed > up -- meaning the free space is not contiguous. If I have two spaces of 100 > pages each free and I have a table with a next extent of 150, will this > routine flag that table as not being able to properly grow into the dbspace? > > Take care. > Clifton The procedure just reports what is in the system tables. So more correct question is "how Informix handle space ..". As far as I know nptotal (space allocated for the table) never decreases after deletes. I'm not sure about npused (number of pages containing something), probably it behaves the same as nptotal (I just don't remember). And for npdata (number of pages containing data; for index tablespaces npdata is 0) we've observed that after deleting all records from the page npdata decreases. So if you have not contiguous free space then nptotal and npused counts it as a non-free. There is also one another factor making space calculation inexact. It may happen that delete works in such a way that removes records from many pages leaving few remaining on each page. Then you see many pages allocated and used by data despite the fact that you have much free space inside each page. Robert
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape