DBSpace Use by Database

[red_v...@yahoo.com]

Does anyone have a query, maybe vs. sysmaster, that will return space
usage of a database by dbspace? I'm not looking for dbspace size and
usage, which is well documented, but rather a result set that looks
like this:

Database DBSpacename DBSpaceTotalSize DBSpaceUsedbyDatabase
Accounting indexdbspace1 10,000 MB 500 MB
Accounting tabledbspace1 10,000 MB 1,000 MB
Marketing indexdbspace1 10,000 MB 1,000 MB
Marketing tabledbspace1 10,000 MB 2,000 MB
Marketing tabledbspace2 15,000 MB 3,000 MB
.
.
.

After much fruitless search, many close-but-no-cigars, and a couple of
lame efforts to concoct it myself, I yield to the superior collective
knowledge of the group.

[Bent_Andy]

http://groups.google.com/group/comp.databases.informix/search?group=comp
.databases.informix&q=DBSpace+Usage&qt_g=Search+this+group

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[InDeep]

It's a bit rusty, from '96, but might be of use... word-wrap may have broken some of the lines, you'll have to fix it...

#!/bin/sh
################################################################################

AWK=awk
TMP=/tmp
DBACCESS=dbaccess

DATA_FILE=${TMP}/${LOGNAME}.IDSspaces.dat
RAW_RPT=${TMP}/${LOGNAME}.IDSspaces.raw

>$DATA_FILE
>$RAW_RPT

################################################################################

BUFF_SIZE=`onstat -b | grep "buffer size" | awk '
BEGIN {
tok_cnt=0 ;
buffer_size="" ;
}
{
tok_cnt = split ( $0 , tok_arr, " " ) ;
{ for ( i=0 ; i <= tok_cnt ; i++ )
{

{ if ( tok_arr[i] == "buffer" )
{
buffer_size=tok_arr[i-1]
{ printf ("%s", buffer_size ) }
}
}
}
}
}
'`
page_size=$BUFF_SIZE

################################################################################

match_dbs()
{

$DBACCESS sysmaster 2>/dev/null <<+
SET ISOLATION TO DIRTY READ ;
UNLOAD TO "${DATA_FILE}"
SELECT
sysdbspaces.name,
syschunks.fname,
syschunks.offset,
sysdbspaces.dbsnum,
syschunks.chknum,
sysdbspaces.fchunk,
"X",
syschunks.chksize,
syschunks.nfree,
0
FROM sysdbspaces, syschunks
WHERE sysdbspaces.dbsnum = syschunks.dbsnum
AND sysdbspaces.name matches "$WILDCARD"
ORDER BY 1

+

}

################################################################################

gather_all_dbs()
{

$DBACCESS sysmaster 2>/dev/null <<+

SET ISOLATION TO DIRTY READ ;

UNLOAD TO "${DATA_FILE}"
SELECT
sysdbspaces.name,
syschunks.fname,
syschunks.offset,
sysdbspaces.dbsnum,
syschunks.chknum,
sysdbspaces.fchunk,
"X",
syschunks.chksize,
syschunks.nfree,
0
FROM sysdbspaces, syschunks
WHERE sysdbspaces.dbsnum = syschunks.dbsnum
ORDER BY 1,5

+

}

################################################################################

print_header()
{
echo "Informix DB Space Report `date` "
uname -a
echo "INFORMIXSERVER $INFORMIXSERVER"
echo " ONCONFIG $ONCONFIG"
echo " BUFF_SIZE $BUFF_SIZE"
}

################################################################################
gen_raw_data()
{
${AWK} -F"|" ' BEGIN { page_size='$page_size' ;
name="" ; fname="" ; offset=0 ; dbsnum="" ; chknum="" ;
fchunk="" ; chksize=0 ; nfree=0 ; current="" ;
linkdisk=""; used_pages=0 ;
pct_free=0 ;
ttl_kb=0.00 ; free_kb=0.00 ; used_kb=0.00 ;
ttl_pg=0.00 ; free_pg=0.00 ; used_pg=0.00 ;
used_pages=0 ;
}
{
name=$1
fname=$2
offset=$3
dbsnum=$4
chknum=$5
fchunk=$6
# X goes here
ttl_pages=$8
free_pages=$9
used_pages=$8-$9

# pct_free=((1-free_pages/ttl_pages)*100)

ttl_pg=$8
free_pg=$9

used_pg=(ttl_pg-free_pg)

ttl_kb=(( ttl_pg*page_size))
free_kb=((free_pg*page_size))
used_kb=ttl_kb-free_kb

# used_kb=((used_pg*page_size))

pct_full=((1-free_pg/ttl_pg)*100)

{ printf("%3d %3d %40s %6s %8d %8d %8d %6.2f %14.2f %14.2f %14.2f %s\n", dbsnum, chknum, name, offset, ttl_pages,
free_pages, used_pg, pct_full, ttl_kb, free_kb, used_kb, fname ) }
}

' ${DATA_FILE} > ${RAW_RPT}

}

################################################################################

print_space_info()
{

$AWK ' BEGIN {
title="O"; last_title="O";
avg_free_space=0.00 ;
avg_avg=0.00 ;
cntr=0 ;
disk_drive="" ;
t_ttl_pages=0 ; t_free_pages=0 ; t_used_pages=0 ; t_pct_full=0 ;
t_ttl_kb=0 ; t_free_kb=0 ; t_used_kb=0 ;
}
{

title=$3

avg_free_space+=$9

++cntr
{ if ( NR == 1 )
{
{ printf("%s\n", title ); }
print_header() ;
}
}
{ if ( ( last_title != title ) && ( NR != 1 ) )
{
{ printf("%58s %8d %8d %8d ", last_title, t_ttl_pages, t_free_pages, t_used_pages ) }
{ printf("%6.2f %11d %11d %11d \n", ( (t_used_pages/t_ttl_pages)*100 ), t_ttl_kb, t_free_kb, t_used_kb ) }

t_ttl_pages=0 ; t_free_pages=0 ; t_used_pages=0 ; t_pct_full=0 ;
t_ttl_kb=0 ; t_free_kb=0 ; t_used_kb=0 ;
disk_drive="" ;

{ printf("\n%s\n", title ); }
print_header() ;
cntr=0
}
}

db_num=$1
chk_num=$2
dbspace=$3

offset=$4

ttl_pages=$5
free_pages=$6
used_pages=$7

pct_full=$8

ttl_kb=$9
free_kb=$10
used_kb=$11

fname=$12

#----------------- totals ------------------

t_ttl_pages+=$5
t_free_pages+=$6
t_used_pages+=$7

t_ttl_kb+=$9
t_free_kb+=$10
t_used_kb+=$11

{ printf("%3d %3d %-s\n", db_num, chk_num, fname ) }

{ printf("%30s %8d %8d %8d %6.2f %11d %11d %11d \n", " ", ttl_pages, free_pages, used_pages, pct_full, ttl_kb, free_kb,
used_kb ) }

last_title=$3
last_fname=$12
last_dbspace=$3

}
function print_header()
{
{ printf("%3s %3s %-22s ", "DB", "Chk", "DB" ) }
{ printf("%-8s %8s %-8s %6s ", "--------", "Pages", "--------", "Pct " ) }
{ printf("%11s %11s %11s\n", "--------", "Kilobytes", "-------" ) }

# { printf("%3s %3s %-30s ", "No", "No", "Space Name" ) }

{ printf("%3s %3s %-22s ", "No", "No", "Disk Name" ) }
{ printf("%8s %8s %8s %6s ", "Total", "Free", "Used", "Full" ) }
{ printf("%11s %11s %11s\n", "Total", "Free" , "Used" ) }

# { printf("%s\n", "Disk" ) }
}
END {
avg_free_space = ((t_used_pages/t_free_pages)*100)
{ printf("%30s %8d %8d %8d ", last_title, t_ttl_pages, t_free_pages, t_used_pages ) }
{ printf("%6.2f %11d %11d %11d\n", ( (t_used_pages/t_ttl_pages)*100 ), t_ttl_kb, t_free_kb, t_used_kb ) }
}
' ${RAW_RPT}

}

################################################################################
disp_usage()
{
echo "

IDSspaces [ "wildcard" ]

where:

wildcard - An optional quoted pattern for a dbspace name

"
}
################################################################################
case $# in
1)
WILDCARD=$1
match_dbs
gen_raw_data
print_header
print_space_info
;;
0)
gather_all_dbs
gen_raw_data
print_header
print_space_info
;;

esac

[Art Kagel]

You need to query sysextents and join to syschunks and sysdbspaces in the sysmaster database.  Q&D:

select e.dbsname, d.name, c.chknum, (c.chksize * c.pagesize) as ChnkTotalSpace, sum(e.size * c.pagesize) as DBSpaceUsedbyDatabase
from sysdbspaces d, syschunks c, sysextents e
where d.dbsnum = c.dbsnum
   and c.dbsnum = e.chunk
group by 1, 2, 3, 4
order by 1, 2, 3;

That will get this for you by chunk.  If you want it be by dbspace either save the results into a temp table and select grouped by dbspace or use this query as a derived table.

Now, that's using the features and table structure of IDS 11.50.  Since you did not provide your version and platform information I've used the latest version.  If you have earlier releases that do not support multiple page sizes, substitute 2048 for c.pagesize above.  It is always a good idea to post your version and platform information even if it seems irrelevant.

Art

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

[InDeep]

Art,

Thanks for that SQL!

I took the liberty to use your SQL, and perl-ified it, hope you
don't mind.

*<8o)

Yesterday I just happened to install Informix on Ubuntu x86_64. It was
not a smooth installation but did actually install and is up and running.
( The installation didn't like the -gui, and I had to add the -java_home. )

Today I thought, hmmm, I wonder if any of my crap from 12+ years ago would
still work, and then I sawr the post for a dbspace utilization script. So,
then I said, hmmm, might be interesting to see what I could do in Perl. At
first I wanted to just run dbaccess as-is, without DBI, but then I said,
wtf, why not see if DBI will work, and how much trouble will that be.

Typically in Perl you could simply @data = `myscript.sh` ; and pull in the results
without having to install DBD::Informix or other database, so out of sheer
laziness ( again with the laziness! ) I was a bit reluctant to require the
dependency on Informix. But then I thought, why not give JL's hard work a try.

This too was a bit of a problem installing DBD::Informix. The installation
failed looking for the stores database. I went in to the esqlc stuff,
changed stores to 'sysutils' and was able to manually install it. I had
to go into my .cpan/build directory, change the code, run perl Makefile.PL
and everything went smooth. I think if JL were to change stores to
sysutils it would eliminate the need for the demo database stores.

Oh, there was some strangeness with huge amount of spaces in the output,
thusly why I added in the s/ //. I think this too is something peculiar
to Ubuntu, but not sure.

So anyway without further rambling... here tis...

#!/usr/bin/perl
#################################################################################
#
# Program: dbspaceused.pl
#
# Author: tbs
#
# SQL: Art Kagel
#
# Usage: ./dbspaceused.pl
#
# Requires: DBD::Informix
#
# perl -MCPAN -e 'install Bundle::DBD::Informix'
#
#################################################################################

$ENV{'INFORMIXSERVER'} = "demo_on" ;
$ENV{'INFORMIXDIR'} = "/opt/informix" ;
$ENV{'ONCONFIG'} = "onconfig.demo_on" ;
$ENV{'INFORMIXSQLHOSTS'} = "/opt/informix/etc/sqlhosts.demo_on" ;
$ENV{'LD_LIBRARY_PATH'} = "/opt/informix/lib/esql:/opt/informix:/opt/informix/lib" ;
$ENV{'PATH'} = $ENV{'INFORMIXDIR'} . "/bin:" . $ENV{'PATH'} ;

use DBI ;

$SQL = "select e.dbsname, d.name, c.chknum, (c.chksize * c.pagesize) as ChnkTotalSpace, " ;
$SQL .= " sum(e.size * c.pagesize) as DBSpaceUsedbyDatabase " ;
$SQL .= "from sysdbspaces d, syschunks c, sysextents e " ;
$SQL .= "where d.dbsnum = c.dbsnum " ;
$SQL .= "and c.dbsnum = e.chunk " ;
$SQL .= "group by 1, 2, 3, 4 " ;
$SQL .= "order by 1, 2, 3 " ;

$DATA_BASE = "sysmaster" ;
$DB_USER = "" ;
$DB_PASS = "" ;

my $dbh = DBI->connect("DBI:Informix:$DATA_BASE", "$DB_USER", "$DB_PASS")
or die "Couldn't connect to database: " . DBI->errstr ;

my $ifmx_sth = $dbh->prepare( $SQL ) ;

$ifmx_sth->execute();

printf( "%s\n", "--------------------------------------------------------------------------------" ) ;
printf( "%14s %14s %6s %14s %14s\n", "Database", "DBSpace", "ChkNum", "Total Space", "DB Space Used" ) ;
printf( "%s\n", "--------------------------------------------------------------------------------" ) ;

$ttl_raw_space = 0 ;
$ttl_used_space = 0 ;

while ( @data = $ifmx_sth->fetchrow_array)
{

$data[0] =~ s/ //g ;
$data[1] =~ s/ //g ;
$data[2] =~ s/ //g ;
$data[3] =~ s/ //g ;
$data[4] =~ s/ //g ;

$dbsname = sprintf( "%14s", $data[0] ) ;
$name = sprintf( "%14s", $data[1] ) ;
$chknum = sprintf( "%6s", $data[2] ) ;
$chnktotalspace = sprintf( "%14d", $data[3] ) ;
$dbspaceused = sprintf( "%14d", $data[4] ) ;

$ttl_raw_space += $chnktotalspace ;
$ttl_used_space += $dbspaceused ;

print "$dbsname $name $chknum $chnktotalspace $dbspaceused \n" ;
}

printf( "%s\n", "--------------------------------------------------------------------------------" ) ;
printf( "%14s %14s %6s %14d %14s\n", "Totals", " ", " ", $ttl_raw_space, $ttl_used_space ) ;

##
## END
##

If it works correctly it will produce:

--------------------------------------------------------------------------------
Database DBSpace ChkNum Total Space DB Space Used
--------------------------------------------------------------------------------
rootdbs rootdbs 1 204800000 716800
sysadmin rootdbs 1 204800000 3850240
sysmaster rootdbs 1 204800000 3006464
system rootdbs 1 204800000 32768
sysuser rootdbs 1 204800000 2310144
sysutils rootdbs 1 204800000 2449408
--------------------------------------------------------------------------------
Totals 1228800000 12365824

-ID-

Art Kagel wrote:
> You need to query sysextents and join to syschunks and sysdbspaces in
> the sysmaster database. Q&D:
>

> select e.dbsname, d.name <http://d.name>, c.chknum, (c.chksize *

> c.pagesize) as ChnkTotalSpace, sum(e.size * c.pagesize) as
> DBSpaceUsedbyDatabase
> from sysdbspaces d, syschunks c, sysextents e
> where d.dbsnum = c.dbsnum
> and c.dbsnum = e.chunk
> group by 1, 2, 3, 4
> order by 1, 2, 3;
>
> That will get this for you by chunk. If you want it be by dbspace
> either save the results into a temp table and select grouped by dbspace
> or use this query as a derived table.
>
> Now, that's using the features and table structure of IDS 11.50. Since
> you did not provide your version and platform information I've used the
> latest version. If you have earlier releases that do not support
> multiple page sizes, substitute 2048 for c.pagesize above. It is always
> a good idea to post your version and platform information even if it
> seems irrelevant.
>
> Art
>
>
> On Wed, Oct 8, 2008 at 9:09 AM, red_v...@yahoo.com

> <mailto:red_v...@yahoo.com> <red_v...@yahoo.com

> <mailto:red_v...@yahoo.com>> wrote:
>
> Does anyone have a query, maybe vs. sysmaster, that will return space
> usage of a database by dbspace? I'm not looking for dbspace size and
> usage, which is well documented, but rather a result set that looks
> like this:
>
> Database DBSpacename DBSpaceTotalSize DBSpaceUsedbyDatabase
> Accounting indexdbspace1 10,000 MB 500 MB
> Accounting tabledbspace1 10,000 MB 1,000 MB
> Marketing indexdbspace1 10,000 MB 1,000 MB
> Marketing tabledbspace1 10,000 MB 2,000 MB
> Marketing tabledbspace2 15,000 MB 3,000 MB
> .
> .
> .
>
> After much fruitless search, many close-but-no-cigars, and a couple of
> lame efforts to concoct it myself, I yield to the superior collective
> knowledge of the group.
> _______________________________________________
> Informix-list mailing list

> Inform...@iiug.org <mailto:Inform...@iiug.org>

> http://www.iiug.org/mailman/listinfo/informix-list
>
>
>
>
> --
> Art S. Kagel

> Oninit (www.oninit.com <http://www.oninit.com>)
> IIUG Board of Directors (a...@iiug.org <mailto:a...@iiug.org>)

[Art Kagel]

No problem, enjoy.

Art

Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

[InDeep]

A follow-up for Mac OSX:

I could not get DBD::Informix to install on my Mac... *<8o(

After carefully hacking the Makefile.PL and spending way more
time on it than I really should, I give up!

Seems that there is something wrong with the Makefile.PL knowing
that my Mac is 64bit:

CPAN.pm: Going to build J/JO/JOHNL/DBD-Informix-2008.0513.tar.gz

*** ExtUtils::AutoInstall version 0.63
*** Checking for dependencies...
[Core Features]
- DBI ...loaded. (1.607 >= 1.38)
[High Resolution Timing]
- Time::HiRes ...loaded. (1.9715)
[POD Format Testing]
- Test::Pod ...loaded. (1.26)
*** ExtUtils::AutoInstall configuration finished.

Configuring IBM Informix Database Driver for Perl DBI Version 2008.0513 (2008-05-13) (aka DBD::Informix)
You are using DBI version 1.607 and Perl version 5.008008
Remember to actually read the README file!

Perl: /usr/bin/perl v5.008008 darwin-thread-multi-2level dl_dlopen.xs
System: darwin omen.apple.com 9.0 darwin kernel version 9.0.0b5: mon sep 10 17:17:11 pdt 2007;
root:xnu-1166.6~1release_ppc power macintosh
*** Ideally, you should upgrade to Perl version 5.010000 or later.

Using INFORMIXDIR=/opt/informix and ESQL/C compiler esql
Using IBM Informix CSDK Version 3.50, IBM Informix-ESQL Version 3.50.FC2DE from /opt/informix
Your build will fail because Perl is a 32-bit version but ESQL/C is a 64-bit version
# Looks like your test died before it could output anything.
Running make test
Make had some problems, maybe interrupted? Won't test
Running make install
Make had some problems, maybe interrupted? Won't install

[Campbell, John (GE Money)]

-----Original Message-----
From: informix-l...@iiug.org
[mailto:informix-l...@iiug.org] On Behalf Of
red_v...@yahoo.com
Sent: Wednesday, October 08, 2008 9:09 AM
To: inform...@iiug.org
Subject: DBSpace Use by Database

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

I picked this finely crafted korn shell up from somewhere. It almost
does what you're looking for.
<<How to UNZIP.html>> <<dbspace.ksh.ZIP>>

[red_v...@yahoo.com]

On Oct 8, 10:42 am, "Art Kagel" <art.ka...@gmail.com> wrote:
> You need to query sysextents and join to syschunks and sysdbspaces in the
> sysmaster database.  Q&D:
>
> select e.dbsname, d.name, c.chknum, (c.chksize * c.pagesize) as
> ChnkTotalSpace, sum(e.size * c.pagesize) as DBSpaceUsedbyDatabase
> from sysdbspaces d, syschunks c, sysextents e
> where d.dbsnum = c.dbsnum
>    and c.dbsnum = e.chunk
> group by 1, 2, 3, 4
> order by 1, 2, 3;
>
> That will get this for you by chunk.  If you want it be by dbspace either
> save the results into a temp table and select grouped by dbspace or use this
> query as a derived table.
>
> Now, that's using the features and table structure of IDS 11.50.  Since you
> did not provide your version and platform information I've used the latest
> version.  If you have earlier releases that do not support multiple page
> sizes, substitute 2048 for c.pagesize above.  It is always a good idea to
> post your version and platform information even if it seems irrelevant.
>
> Art
>

> On Wed, Oct 8, 2008 at 9:09 AM, red_val...@yahoo.com
> <red_val...@yahoo.com>wrote:

>
>
>
> > Does anyone have a query, maybe vs. sysmaster, that will return space
> > usage of a database by dbspace?  I'm not looking for dbspace size and
> > usage, which is well documented, but rather a result set that looks
> > like this:
>
> > Database    DBSpacename     DBSpaceTotalSize  DBSpaceUsedbyDatabase
> > Accounting  indexdbspace1         10,000 MB                 500 MB
> > Accounting  tabledbspace1          10,000 MB              1,000 MB
> > Marketing    indexdbspace1         10,000 MB               1,000 MB
> > Marketing    tabledbspace1          10,000 MB              2,000 MB
> > Marketing    tabledbspace2          15,000 MB              3,000 MB
> > .
> > .
> > .
>
> > After much fruitless search, many close-but-no-cigars, and a couple of
> > lame efforts to concoct it myself, I yield to the superior collective
> > knowledge of the group.
> > _______________________________________________
> > Informix-list mailing list

> > Informix-l...@iiug.org

> >http://www.iiug.org/mailman/listinfo/informix-list
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly.  Neither do
> those opinions reflect those of other individuals affiliated with any entity
> with which I am affiliated nor those of the entities themselves.

Art,

Thanks so much for the reply.

In manipulating your query, the result set was suspiciously regular,
so I tweaked the predicate so that this join:

and c.dbsnum = e.chunk

-- became this:

and c.chknum = e.chunk

Seems to be different now, but accurate? Need a verify. Others?

Forgot standard C.D.I. protocol: We're stuck with IDS 9.4x on HPUX 11
due to PeopleSoft; and IDS 10.x on Linux (RHAS/RHEL 3/4/5) elsewhere.