trace to index name
HI,
I tried to use partnum of ouput of "onstat -C clean" ( see below) to get
the index name from sysptprof.
But it seem NOT very right.
select * from sysptprof
where hex(partnum)='0x00e00081'
Any other sysXXXX table has a relation to Partnum of output of "onstat
-C clean" ? or better solution?
Thanks,
Frank
[informix@walter ~]$ onstat -C clean
IBM Informix Dynamic Server Version 11.50.FC8 -- On-Line -- Up 20 days
20:44:00 -- 8359504 KbytesB-tree Scanner Information
Index Cleaned Statistics
=========================
Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec
0x00100013 2 2 0 0 0 0.00
0x00100015 1 6 0 0 0 0.00
0x0010006f 1 27 0 0 0 0.00
0x0010006f 2 189 0 0 0 0.00
--0023544712d0e2289a04d40c79fb
A script written by one of my dba's does the trick if you have perl & DBI.
Example of using it and looking at the sysmaster tables only:
myonstat -C clean |grep sysm
0x00100002 1 0 0 3 2 3.00 sysmaster
0x00100005 2 0 0 6 1011 6.00 sysmaster syscolumns column
0x00100006 1 40 0 0 0 0.00 sysmaster sysindices idxtab
0x00100007 2 55 0 0 0 0.00 sysmaster systabauth tabgtor
0x00100013 2 2 0 0 0 0.00 sysmaster sysprocedures routineididx
0x00100015 1 154 0 0 0 0.00 sysmaster sysprocplan procplan
0x0010001c 1 106 0 0 0 0.00 sysmaster sysfragments fraginfo
We named it myonstat
#!/usr/local/bin/perl
$cmd="onstat";
foreach $arg ( @ARGV ) {
$cmd = $cmd ." ".$arg;
}
print "$cmd\\
";
use DBI;
$dbhs=DBI->connect("dbi:Informix:sysmaster\\\\@$ENV{INFORMIXSERVER}") || die
"Couldnt connect to sysmaster\\\\@$hostnamei($DBI::err)\\
";
$dbhs->{ChopBlanks}=1;
$dbhs->{RaiseError}=1;
$dbhs->do("set isolation dirty read");
$sths=$dbhs->prepare("select dbsname,tabname,is_logging from systabnames
t,sysdatabases d where t.partnum=? and name=dbsname");
$prev_dbsname='firsttime';
open (IN, "$cmd |") || die "cant run cmd $cmd :$!";
while (<IN>) {
chomp $_;
print $_;
if ( $_ =~ /(0x\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w)\\\\s/ ) {
($hexval) = $_ =~ /(0x\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w)\\\\s/;
if ( $hexval ne '0x00000000' ) {
$decval = hex($hexval);
$sths->execute($decval);
$sths->bind_columns(undef, \\\\($dbsname,$sm_tabname,$is_logging));
$sths->fetch;
if ( $prev_dbsname ne $dbsname ) {
$dbh=DBI->connect("dbi:Informix:$dbsname\\\\@$ENV{INFORMIXSERVER}") || die
"Couldnt connect to $dbsname\\\\@$ENV{INFORMIXSERVER}($DBI::err)\\
";
$dbh->{ChopBlanks}=1;
$dbh->{RaiseError}=1;
$dbh->do("set isolation dirty read") if ( $is_logging == 1 );
$sth=$dbh->prepare("select indexname,tabname from sysfragments f,systables t
where f.partn=? and f.tabid=t.tabid");
$sthcdr=$dbh->prepare("select idxname,tabname from sysindexes i,systables t
where t.partnum=? and i.tabid=t.tabid");
}
if (( $sm_tabname =~ /cdr_deltab/ ) || ( $dbsname =~ /^sys/ ) || ( $sm_tabname
=~ /^sys/ )) {
$sthcdr->execute($decval);
$sthcdr->bind_columns(undef, \\\\($indexname,$tabname));
$sthcdr->fetch;
}else{
$sth->execute($decval);
$sth->bind_columns(undef, \\\\($indexname,$tabname));
$sth->fetch;
}
printf("\\\\t%12s %-30s %-s",$dbsname,$tabname,$indexname);
$prev_dbsname=$dbsname;
$tabname=$dbsname=$indexname='';
}
}
print "\\
";
}
close IN;
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK
Sent: Thursday, January 24, 2013 11:53 AM
To: ids@iiug.org
Subject: trace to index name [29405]
HI,
I tried to use partnum of ouput of "onstat -C clean" ( see below) to get the
index name from sysptprof.
But it seem NOT very right.
select * from sysptprof
where hex(partnum)='0x00e00081'
Any other sysXXXX table has a relation to Partnum of output of "onstat -C
clean" ? or better solution?
Thanks,
Frank
[informix@walter ~]$ onstat -C clean
IBM Informix Dynamic Server Version 11.50.FC8 -- On-Line -- Up 20 days
20:44:00 -- 8359504 KbytesB-tree Scanner Information
Index Cleaned Statistics
=========================
Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec
0x00100013 2 2 0 0 0 0.00
0x00100015 1 6 0 0 0 0.00
0x0010006f 1 27 0 0 0 0.00
0x0010006f 2 189 0 0 0 0.00
--0023544712d0e2289a04d40c79fb
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Subject to your confirmation, but hex() will give you an upper case
representation of the partnum... So it doesn't match...
Simply do:
Partnum = '0x000...'
Informix is smart enough to handle it...
Regards
On Jan 24, 2013 5:52 PM, "FRANK" <yunyaoqu@gmail.com> wrote:
> HI,
>
> I tried to use partnum of ouput of "onstat -C clean" ( see below) to get
> the index name from sysptprof.
> But it seem NOT very right.
>
> select * from sysptprof
> where hex(partnum)='0x00e00081'>
> Any other sysXXXX table has a relation to Partnum of output of "onstat
> -C clean" ? or better solution?
>
> Thanks,
> Frank
>
> [informix@walter ~]$ onstat -C clean
>
> IBM Informix Dynamic Server Version 11.50.FC8 -- On-Line -- Up 20 days
> 20:44:00 -- 8359504 Kbytes> B-tree Scanner Information
> Index Cleaned Statistics
> =========================
> Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec
> 0x00100013 2 2 0 0 0 0.00
> 0x00100015 1 6 0 0 0 0.00
> 0x0010006f 1 27 0 0 0 0.00
> 0x0010006f 2 189 0 0 0 0.00
>
> --0023544712d0e2289a04d40c79fb
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5016241321b3804d40eaf50
Thanks Fernando! I later found the same fact. The output format misled
me... :-)
On Thu, Jan 24, 2013 at 3:54 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> Subject to your confirmation, but hex() will give you an upper case
> representation of the partnum... So it doesn't match...
>
> Simply do:
>
> Partnum = '0x000...'
>
> Informix is smart enough to handle it...
>
> Regards
> On Jan 24, 2013 5:52 PM, "FRANK" <yunyaoqu@gmail.com> wrote:
>
> > HI,
> >
> > I tried to use partnum of ouput of "onstat -C clean" ( see below) to get
> > the index name from sysptprof.
> > But it seem NOT very right.
> >
> > select * from sysptprof
> > where hex(partnum)='0x00e00081'> >
> > Any other sysXXXX table has a relation to Partnum of output of "onstat
> > -C clean" ? or better solution?
> >
> > Thanks,
> > Frank
> >
> > [informix@walter ~]$ onstat -C clean
> >
> > IBM Informix Dynamic Server Version 11.50.FC8 -- On-Line -- Up 20 days
> > 20:44:00 -- 8359504 Kbytes> > B-tree Scanner Information
> > Index Cleaned Statistics
> > =========================
> > Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec
> > 0x00100013 2 2 0 0 0 0.00
> > 0x00100015 1 6 0 0 0 0.00
> > 0x0010006f 1 27 0 0 0 0.00
> > 0x0010006f 2 189 0 0 0 0.00
> >
> > --0023544712d0e2289a04d40c79fb
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec5016241321b3804d40eaf50
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf306f7504ba82f604d40f2437
Thanks David! Will have a look. Frank
On Thu, Jan 24, 2013 at 1:07 PM, Link, David A <DALink@west.com> wrote:
> A script written by one of my dba's does the trick if you have perl & DBI.
>
> Example of using it and looking at the sysmaster tables only:
>
> myonstat -C clean |grep sysm
> 0x00100002 1 0 0 3 2 3.00 sysmaster
> 0x00100005 2 0 0 6 1011 6.00 sysmaster syscolumns column
> 0x00100006 1 40 0 0 0 0.00 sysmaster sysindices idxtab
> 0x00100007 2 55 0 0 0 0.00 sysmaster systabauth tabgtor
> 0x00100013 2 2 0 0 0 0.00 sysmaster sysprocedures routineididx
> 0x00100015 1 154 0 0 0 0.00 sysmaster sysprocplan procplan
> 0x0010001c 1 106 0 0 0 0.00 sysmaster sysfragments fraginfo
>
> We named it myonstat
>
> #!/usr/local/bin/perl
> $cmd="onstat";
> foreach $arg ( @ARGV ) {
>
> $cmd = $cmd ." ".$arg;
> }
> print "$cmd\\
";
> use DBI;
> $dbhs=DBI->connect("dbi:Informix:sysmaster\\\\@$ENV{INFORMIXSERVER}") || die
> "Couldnt connect to sysmaster\\\\@$hostnamei($DBI::err)\\
";
> $dbhs->{ChopBlanks}=1;
> $dbhs->{RaiseError}=1;
> $dbhs->do("set isolation dirty read");
> $sths=$dbhs->prepare("select dbsname,tabname,is_logging from systabnames
> t,sysdatabases d where t.partnum=? and name=dbsname");
> $prev_dbsname='firsttime';
> open (IN, "$cmd |") || die "cant run cmd $cmd :$!";
> while (<IN>) {
>
> chomp $_;
>
> print $_;
>
> if ( $_ =~ /(0x\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w)\\\\s/ ) {
>
> ($hexval) = $_ =~ /(0x\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w\\\\w)\\\\s/;
>
> if ( $hexval ne '0x00000000' ) {
>
> $decval = hex($hexval);
>
> $sths->execute($decval);
>
> $sths->bind_columns(undef, \\\\($dbsname,$sm_tabname,$is_logging));
>
> $sths->fetch;
>
> if ( $prev_dbsname ne $dbsname ) {
>
> $dbh=DBI->connect("dbi:Informix:$dbsname\\\\@$ENV{INFORMIXSERVER}") || die
> "Couldnt connect to $dbsname\\\\@$ENV{INFORMIXSERVER}($DBI::err)\\
";
>
> $dbh->{ChopBlanks}=1;
>
> $dbh->{RaiseError}=1;
>
> $dbh->do("set isolation dirty read") if ( $is_logging == 1 );
>
> $sth=$dbh->prepare("select indexname,tabname from sysfragments f,systables
> t
> where f.partn=? and f.tabid=t.tabid");
>
> $sthcdr=$dbh->prepare("select idxname,tabname from sysindexes i,systables t
> where t.partnum=? and i.tabid=t.tabid");
>
> }
>
> if (( $sm_tabname =~ /cdr_deltab/ ) || ( $dbsname =~ /^sys/ ) || (
> $sm_tabname
> =~ /^sys/ )) {
>
> $sthcdr->execute($decval);
>
> $sthcdr->bind_columns(undef, \\\\($indexname,$tabname));
>
> $sthcdr->fetch;
>
> }else{
>
> $sth->execute($decval);
>
> $sth->bind_columns(undef, \\\\($indexname,$tabname));
>
> $sth->fetch;
>
> }
>
> printf("\\\\t%12s %-30s %-s",$dbsname,$tabname,$indexname);
>
> $prev_dbsname=$dbsname;
>
> $tabname=$dbsname=$indexname='';
>
> }
>
> }
>
> print "\\
";
> }
> close IN;
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> FRANK
> Sent: Thursday, January 24, 2013 11:53 AM
> To: ids@iiug.org
> Subject: trace to index name [29405]
>
> HI,
>
> I tried to use partnum of ouput of "onstat -C clean" ( see below) to get
> the
> index name from sysptprof.
> But it seem NOT very right.
>
> select * from sysptprof
> where hex(partnum)='0x00e00081'>
> Any other sysXXXX table has a relation to Partnum of output of "onstat -C
> clean" ? or better solution?
>
> Thanks,
> Frank
>
> [informix@walter ~]$ onstat -C clean
>
> IBM Informix Dynamic Server Version 11.50.FC8 -- On-Line -- Up 20 days
> 20:44:00 -- 8359504 Kbytes> B-tree Scanner Information
> Index Cleaned Statistics
> =========================
> Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec
> 0x00100013 2 2 0 0 0 0.00
> 0x00100015 1 6 0 0 0 0.00
> 0x0010006f 1 27 0 0 0 0.00
> 0x0010006f 2 189 0 0 0 0.00
>
> --0023544712d0e2289a04d40c79fb
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b67862685930504d40f458d