IDS Forum

Re: Indexes usage

Posted By: FRANK
Date: Tuesday, 29 December 2009, at 1:37 p.m.

In Response To: Re: Indexes usage (CARLOS BOCCIO)

I had a short PERL script( Running on IDS11.10, AIX5.3) to query the
sysmaster database. It will show both index and table usages.

You can chose to sort by read, writes, locks...

The following are sample output and the script.

Thanks,
Frank

informix@erika $ tbio
Usage: tbio 2(sort by lock),3(r),4(w),5(pr) 6(pw)
informix@erika $ tbio 5

tabname lockreqs isreads, iswrites
pagreads pagwrites

file_receipt_info 106199886 89230181 82344
4706489 37264

lin_file_info 38060930 2922778 575699
4142726 257744

hist_file_receipt_info 374024 62878 79132
1080610 18672

file_change_notice 19228713 266393 153628
652298 46498

hist_dataset_info 360009 66525 77168
636295 11391

file_met -897984453 35756 17139
580155 3645

cman_cat_deliv 46638700 44097574 202206
501897 29115

dataset_info_idx1 11561803 543512 0
466919 4624

dataset_info 49152952 890684 75004
456750 24158

order_spec 11705929 70416 64216
428507 78627

cman_cat_deliv_idx1 1185007 222968 0
419769 99117

#!/usr/local/bin/perl -w
#
#-------------------------------------------------------------

use DBI;

$connect = 'dbi:Informix:sysmaster';

$dbh = DBI->connect($connect) or die "could not connect to database\n";

$dbh->{ChopBlanks} = 1;

$dbh->{RaiseError} = 1;

$dbh->do("Set lock mode to wait 60");

$output = *STDOUT;

$rows = 80;

$sort = $ARGV[0];

# check Usage

$argNum=@ARGV;

if ($argNum!=1) { print "Usage: tbio 2(sort by
lock),3(r),4(w),5(pr) 6(pw)\n"; exit; }

$where_clause=" tabname matches '[a-z]*' and tabname not like 'sys%'";
# $where_clause="dbsname='noaa' and tabname matches '[a-z]*' and tabname
not like 'sys%'";
# and tabname not like '%idx%' and tabname not like 'cdr%' and tabname
not matches '*_ipk'";

$stmt= "select first $rows tabname, lockreqs, isreads, iswrites,
pagreads, pagwrites from sysptprof where $where_clause order by $sort desc";

$reptab = $dbh->prepare($stmt);

$reptab->bind_columns(undef,\$tabname,\$lockreqs,\$isreads,
\$iswrites,\$pagreads, \$pagwrites);

$reptab->execute;

printf $output "\n%25s %10s %12s, %12s %10s %10s \n", "tabname",
"lockreqs", "isreads","iswrites", "pagreads", "pagwrites";

while ($reptab->fetch) {

printf $output "\n%25s %10s %12s %12s %10s %10s \n", $tabname,
$lockreqs, $isreads, $iswrites, $pagreads, $pagwrites;

}

On Tue, Dec 29, 2009 at 12:37 PM, CARLOS BOCCIO <cboccio@yahoo.com.br>wrote:

> Hi, Mr. Alexandre,
>
> The Informix version is 10.00.FC8W4X5 and the plataform is AIX version 5.3.
>
> I want to know the most acessed (used) indexes.
>
> Regards.
>
> Carlos E. Boccio
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--00504502c14d0916b6047be24fb7

Messages In This Thread

• Indexes usage
  CARLOS BOCCIO -- Tuesday, 29 December 2009, at 8:26 a.m.
  • Re: Indexes usage
    Alexandre Marini -- Tuesday, 29 December 2009, at 11:10 a.m.
    • Re: Indexes usage
      CARLOS BOCCIO -- Tuesday, 29 December 2009, at 12:37 p.m.
      • Re: Indexes usage
        John Miller iii -- Tuesday, 29 December 2009, at 1:05 p.m.
        • Re: Indexes usage
          CARLOS BOCCIO -- Tuesday, 29 December 2009, at 2:17 p.m.
          • AW: Indexes usage
            Andreas.KUTSCHE@spar.at -- Wednesday, 30 December 2009, at 4:31 a.m.
            • Re: AW: Indexes usage
              CARLOS BOCCIO -- Wednesday, 30 December 2009, at 9:23 a.m.
      • Re: Indexes usage
        FRANK -- Tuesday, 29 December 2009, at 1:37 p.m.
        • Re: Indexes usage
          John Miller iii -- Tuesday, 29 December 2009, at 3:58 p.m.
  • Re: Indexes usage
    John Miller iii -- Tuesday, 29 December 2009, at 11:41 a.m.
  • Re: Indexes usage
    Art Kagel -- Tuesday, 29 December 2009, at 12:08 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.