Informix Statistic

Hi all, I need some input from you all. What are the statistic that I can capture from informix (ie. number of transaction, i/o for each dbspaces etc ). My purpose here is to capture the statistic and plot out a graph. Thanks and regards, Tham

Coincidentally I am working the same problem. So far I have a cron job that collects info from systabnames, sysptprof, and sysptnhdr in sysmaster; then resets the statistics. It runs at 05:00 and 17:00 to log usage of all tables (by dbspace) during general OLTP and batch hours. Using perl with the GD module to generate trend plots of the busiest by reads and writes, and growth of the largest tables. Other reporting will be tables that grow more than some percentage over time. One of the original purposes of this collection was to determine where to locate the tables within dbspaces. Initial analysis has shown that of 1600 tables almost half are NEVER used! Same for the indexes. One odd bit has shown one table with five indexes, has the same number of index reads (on all 5 indexes) as the number of rows in the table. (I suspect an ugly HAVING clause.) I can send you the scripts to collect the data; the graph generator is still very rough. Tham Huei Hwan wrote: > Hi all, > > I need some input from you all. What are the statistic that I can > capture from informix (ie. number of transaction, i/o for each dbspaces > etc ). My purpose here is to capture the statistic and plot out a graph. > > Thanks and regards, > > Tham

B. Dana wrote in message <3A674D42.A792645B@hotmail.com>... >Coincidentally I am working the same problem. > >..... > >I can send you the scripts to collect the data; the graph generator is >still very rough. Oooooh! Yes please! Need some kinda monitor like this.

In the year of Our Lord Thu, 18 Jan 2001 12:08:34 -0800, "B. Dana" <SbPgAdanaM@hotmail.com> spake, saying: >Coincidentally I am working the same problem. >So far I have a cron job that collects info from systabnames, sysptprof, >and sysptnhdr in sysmaster; then resets the statistics. It runs at 05:00 >and 17:00 to log usage of all tables (by dbspace) during general OLTP >and batch hours. >Using perl with the GD module to generate trend plots of the >busiest by reads and writes, and growth of the largest tables. Other >reporting will be tables that grow more than some percentage over time. > >One of the original purposes of this collection was to determine where >to locate the tables within dbspaces. >Initial analysis has shown that of 1600 tables almost half are NEVER >used! Same for the indexes. One odd bit has shown one table with five >indexes, has the same number of index reads (on all 5 indexes) as the >number of rows in the table. (I suspect an ugly HAVING clause.) > >I can send you the scripts to collect the data; the graph generator is still > >very rough. Why not put it on the iiug.org website?

You can get data via scripts or perl scripts using onstat, oncheck or scripts sql dayly with cron, then send data to an informix database (by example using load )and then, use any tools for graph, using odbc. I get many statistics in this form, and print many graph about the informix resources monthly, isam read, writes, % cache, tables with too much extents, bad index, read ahead.... etc... But most important is that you get historical data for best tunning in future In other hand, you can use the informix tools for get graphical about the system online, but so you, can overhead your system In article <943689$5g6$1@news.xmission.com>, Tham Huei Hwan <hhtham@apis.dhl.com> wrote: > > Hi all, > > I need some input from you all. What are the statistic that I can > capture from informix (ie. number of transaction, i/o for each dbspaces > etc ). My purpose here is to capture the statistic and plot out a graph. > > Thanks and regards, > > Tham > > Sent via Deja.com http://www.deja.com/

Hi , I would be like to get a copy of these scripts as well. I still a novice where perl is concerned. Cheers Pankaj In article <3A674D42.A792645B@hotmail.com>, "B. Dana" <SbPgAdanaM@hotmail.com> wrote: > Coincidentally I am working the same problem. > So far I have a cron job that collects info from systabnames, sysptprof, > and sysptnhdr in sysmaster; then resets the statistics. It runs at 05:00 > and 17:00 to log usage of all tables (by dbspace) during general OLTP > and batch hours. > Using perl with the GD module to generate trend plots of the > busiest by reads and writes, and growth of the largest tables. Other > reporting will be tables that grow more than some percentage over time. > > One of the original purposes of this collection was to determine where > to locate the tables within dbspaces. > Initial analysis has shown that of 1600 tables almost half are NEVER > used! Same for the indexes. One odd bit has shown one table with five > indexes, has the same number of index reads (on all 5 indexes) as the > number of rows in the table. (I suspect an ugly HAVING clause.) > > I can send you the scripts to collect the data; the graph generator is still > > very rough. > > Tham Huei Hwan wrote: > > > Hi all, > > > > I need some input from you all. What are the statistic that I can > > capture from informix (ie. number of transaction, i/o for each dbspaces > > etc ). My purpose here is to capture the statistic and plot out a graph. > > > > Thanks and regards, > > > > Tham > > Sent via Deja.com http://www.deja.com/

Please comment on the script before it is submitted to iiug.org. (Conversion to perl/GDGraph/DBI will happen eventually.) #!/bin/ksh ########################### # onstat -z commented out # ########################### # # Collect table and index access info and reset counters after. # (Shows table space name for each table too!) # # Need to add owner, rowsize, nindexes, locklevel, ? # (Where can this be found in sysmaster?) # # crontab (5AM-5PM OLTP; 5PM-5AM batch): # 00 05,17 * * * su - informix -c /dba/tabstats.sh >> /dba/logs/tabstats.log 2>&1 # # The run-log (same as in crontab): TABSTATS_LOG=/dba/logs/tabstats.log # The time now (and to build the output file name): NOW=`date` CR_DATE=`date +"%C%y:%m:%d:%H:%M:%S"` # Where to stick the output: STATS_OUT=/dba/logs/tabstats.$CR_DATE # Database name: DBNAME=rel # Is Informix up? INF_STATE=`onstat - | awk '/On-Line/ {print "1"}'` # Log and exit if not: [[ $INF_STATE != "1" ]] && { echo "$NOW Informix is down." \\ >> $TABSTATS_LOG; exit 1 ; } # Log scriptus interruptus: trap 'echo "$NOW tabstats interrupted." >> $TABSTATS_LOG' INT QUIT # Log the last onstat -z time: echo "$NOW tabstats started." >> $TABSTATS_LOG dbaccess //$DBNAME/sysmaster - <<SQL >> $TABSTATS_LOG output to pipe "cat " without headings select 'stats were reset (onstat -z) ', round((sh_curtime - sh_pfclrtime)/3600, 1), ' hours ago' from sysshmvals; SQL # Collect the stats: dbaccess //$DBNAME/sysmaster - <<SQL2 > $STATS_OUT 2>&1 select unique systabnames.tabname, -- Should be able to get the same info as in online1:systables -- (e.g. owner, rowsize, nindexes, locklevel, ?) -- (nrows was found in sysmaster:sysptnhdr.) nrows, dbinfo("DBSPACE", pe_partnum) dbspace, lockreqs, lockwts, deadlks, lktouts, isreads, iswrites, isrewrites, isdeletes, bufreads, bufwrites, seqscans, pagreads, pagwrites from sysptnext, outer systabnames, outer sysptprof, sysptnhdr where pe_partnum = systabnames.partnum and pe_partnum = sysptprof.partnum and pe_partnum = sysptnhdr.partnum order by tabname;SQL2 # Reset the counters: ### /usr/informix/bin/onstat -z # Remove the "Database selected.", "row(s) retrieved.", and "Database closed." lines: egrep -v "Database selected|row\\(s\\) retrieved|Database closed" $STATS_OUT > $STATS_OUT.clean # Reformat the output to columns using David Cortesi's awk script: # (lpp=lines_per_page, forces column headings too, want only one heading line so lpp is big) # (get rid of "TBLSpace" lines too): nawk -f /dba/reform.awk lpp=10000 $STATS_OUT.clean | grep -v "^TBLSpace" > $STATS_OUT.cols # Remove the (interrum) cleaned-up file: rm $STATS_OUT.clean Obnoxio The Clown wrote: > In the year of Our Lord Thu, 18 Jan 2001 12:08:34 -0800, "B. Dana" > <SbPgAdanaM@hotmail.com> spake, saying: > > >Coincidentally I am working the same problem. > >So far I have a cron job that collects info from systabnames, sysptprof, > >and sysptnhdr in sysmaster; then resets the statistics. It runs at 05:00 > >and 17:00 to log usage of all tables (by dbspace) during general OLTP > >and batch hours. > >Using perl with the GD module to generate trend plots of the > >busiest by reads and writes, and growth of the largest tables. Other > >reporting will be tables that grow more than some percentage over time. > > > >One of the original purposes of this collection was to determine where > >to locate the tables within dbspaces. > >Initial analysis has shown that of 1600 tables almost half are NEVER > >used! Same for the indexes. One odd bit has shown one table with five > >indexes, has the same number of index reads (on all 5 indexes) as the > >number of rows in the table. (I suspect an ugly HAVING clause.) > > > >I can send you the scripts to collect the data; the graph generator is still > > > >very rough. > > Why not put it on the iiug.org website?

Here is some quick perl with DBI to get the same output as previously posted with ksh, grep, awk, etc. (must have DBD/DBI) #!/usr/bin/perl -w # # Don't forget to set INFORMIXDIR and INFORMIXSERVER use DBI; $dbh = DBI->connect("DBI:Informix:sysmaster"); $sth = $dbh->prepare(q%SELECT unique systabnames.tabname, nrows, dbinfo("DBSPACE", pe_partnum) dbspace, lockreqs, lockwts, deadlks, lktouts, isreads, iswrites, isrewrites, isdeletes, bufreads, bufwrites, seqscans, pagreads, pagwrites FROM sysptnext, outer systabnames, outer sysptprof, sysptnhdr WHERE pe_partnum = systabnames.partnum AND pe_partnum = sysptprof.partnum AND pe_partnum = sysptnhdr.partnum ORDER BY tabname %); $sth->execute; $ref = $sth->fetchall_arrayref(); print " tabname, nrows, dbspace, lockreqs, lockwts, deadlks, lktouts, isreads, iswrites, isrewrites, isdeletes, bufreads, bufwrites, seqscans, pagreads, pagwrites \\n"; for $row (@$ref) { print " $$row[0], $$row[1], $$row[2], $$row[3], $$row[4], $$row[5], $$row[6], $$row[7], $$row[8], $$row[9], $$row[10], $$row[11], $$row[12], $$row[13], $$row[14], $$row[15] \\n"; } $dbh->disconnect; pankaj1000@my-deja.com wrote: > Hi , > > I would be like to get a copy of these scripts as well. I still a > novice where perl is concerned. > > Cheers > > Pankaj > In article <3A674D42.A792645B@hotmail.com>, > "B. Dana" <SbPgAdanaM@hotmail.com> wrote: > > Coincidentally I am working the same problem. > > So far I have a cron job that collects info from systabnames, > sysptprof, > > and sysptnhdr in sysmaster; then resets the statistics. It runs at > 05:00 > > and 17:00 to log usage of all tables (by dbspace) during general OLTP > > and batch hours. > > Using perl with the GD module to generate trend plots of the > > busiest by reads and writes, and growth of the largest tables. Other > > reporting will be tables that grow more than some percentage over > time. > > > > One of the original purposes of this collection was to determine where > > to locate the tables within dbspaces. > > Initial analysis has shown that of 1600 tables almost half are NEVER > > used! Same for the indexes. One odd bit has shown one table with five > > indexes, has the same number of index reads (on all 5 indexes) as the > > number of rows in the table. (I suspect an ugly HAVING clause.) > > > > I can send you the scripts to collect the data; the graph generator > is still > > > > very rough. > > > > Tham Huei Hwan wrote: > > > > > Hi all, > > > > > > I need some input from you all. What are the statistic that I can > > > capture from informix (ie. number of transaction, i/o for each > dbspaces > > > etc ). My purpose here is to capture the statistic and plot out a > graph. > > > > > > Thanks and regards, > > > > > > Tham > > > > > > Sent via Deja.com > http://www.deja.com/

Hi , Thanks for script should give a little insight into perl and DBI. Pankaj Sent via Deja.com http://www.deja.com/