In article <4scncd$2cei@news-s01.ny.us.ibm.net> CUPAC writes:
>Dear all,
>
>Here is a line from the output of lockstat under the Locks by lock list
>section:
>
>Id:009C0003 Rsb:00970003 ... KEY(SV_PAGE,DB=2FD9714A,TABLE=[3,0],PAGE=6)
>
>Is there anybody who knows how to find out the database name and the table
>name from the KEY fields? I have tried to guess the table name from the
>iirelation table but it seems that the table id here is not the direct
>value of reltid, not like errlog.log.
>
>Thanks in advance.
>Toby Lam
>(Cathay Pacific Airways, Hong Kong)
You need to "join" the lockstat data with the logstat data. Search for
the part of DB= in the output of logstat.
Cheers,
--
|---------------------------------------------------|
| Michael Leo York & Associates, Inc. |
| mal@winternet.com (612) 921-8083 (voice) |
| Minneapolis, MN, USA |
|---------------------------------------------------|
| NAIUA = North American Ingres Users Association |
| http://www.naiua.org |
| |
| Ingres FAQ is at ftp.naiua.org in /pub/ingres |
|---------------------------------------------------|
From: "Beedle,Paul(STP)"
Subject: RE: Lockstat output
Date: 16 Jul 1996 10:48:51 -0400
When Table=[3,0], try:
select table_name, table_owner
from iitables
where table_reltid = 3
and table_reltidx = 0
\g
----------
Toby,
You have to figure it out by using the output of logstat and lockstat
both.
1)
The lockstat line which you have specified is followed by transaction line
like this. Something like this
Id: 00A20891 Tran_id: 000031C07415B072 R_llb: 00D80FB5 R_cnt: 0 Wait: 00000000 L
ocks: (1,0/180) Status:
Id: 066A0E6B Rsb: 02DB05EA Gr: S Req: S State: GR PHYS(1) KEY(CONTROL,D
B=30A265C6,TABLE=[3354,0])
2)
You have to look for Tran_id: 000031C07415B072 in the output of logstat.
Tx_id: 0428005B Tran_id: 000031C07415B072 Database: 02B4000A
Process: 0001001F Dis_tran_id: 0000000000000000 Session: 4050E880
First: <0,0,0> Last: <0,0,0> Cp: <834696173,34879,1708>
Write: 0 Split: 0 Force: 0 Wait: 0
Status: INACTIVE,PROTECT
User:
3)
You have to look for Database: 02B4000A at the top of logstat
Id: 02B4000A Database: (unit302,unitadm) Status: FAST_COMMIT
Tx_cnt: 5 Begin: 8998 End: 8993 Read: 0 Write: 2967 Force: 17 Wait: 321
Location: /data8/ingres/data/default/unit302
Journal Window: <0,0,0>..<0,0,0>
Start Backup Location: <0,0,0>
NOTE: You have to fire lockstat and logstat at the same instant.
However I am appending a ingperl script I have written to achieve this.
Hope this helps,let em know if you have any questions.
Thanks
Gajendra
------X Starts HereX--------------------------------------
#!/usr/contrib/bin/ingperl
############################################################################
# inglock
############################################################################
# This script shows locks on various tables taken by users. One can
# specify database name on which to look for locks.
#
# Options : inglock [-ddatabase_name]
#
# Output: Lock output
#
# Limitation: The person looking for locks on a database should have
# access to that database.
#
# Written By: Gajendra Jain
############################################################################
require "getopt.pl";
&Getopt("d");
open(LOG,"logstat|") || die "Failed to execute LOGSTAT.....\n";
open(LOCK,"lockstat|") || die "Failed to execute lockstat.....\n";
while () {
if ((/Database:/) && (/Status:/)) {
$_ =~ m/^.*Id: (.*) Database: \((.*),.*\).*/;
$dataid{$1} = $2;
undef $1,$2;
}
if ((/Database:/) && (/Tran_id:/)) {
$_ =~ m/^.*Tran_id: (.*) Database: (.*)/;
$tran_id{$1} = $dataid{$2};
$id = $1;
undef $1,$2;
}
if (/Session:/) {
$_ =~ m/^.*Session: (.*)$/;
$tran_id{$id} .= ".".$1;
undef $1,$2;
}
if (/User:/) {
$_ =~ m/^.*<(.*)>.*/;
$tran_id{$id} .= ".".$1;
delete $tran_id{$id} if ($tran_id{$id} =~ /\$/);
undef $1,$2;
}
}
while () {
last if (/Locks by resource/);
if (/Tran_id:/) {
$analock = "";
$_ =~ m/^.*Tran_id: (.*) R_llb:.*Status:(.*)/;
if ($tran_id{$1}) {
@datauser=split(/\./,$tran_id{$1});
$dbname = $datauser[0];
$uname = $datauser[2];
$session = $datauser[1];
if ($opt_d) {
next if ($dbname ne $opt_d);
}
$analock = "TRUE";
print "Database: ",$dbname," User: ",$uname;
print " Session: ",$session," Status: ",$2,"\n";
next;
}
undef $1,$2;
}
if ($analock) {
if ((/KEY\(TABLE/) || (/KEY\(CONTROL/)) {
$_ =~ /^ Id:.*(Gr:.*)KEY.*TABLE=\[(.*),.*\].*/;
$statlock = $1;
if (!$tablist{$dbname.$2}) {
&tabproc($dbname,$2);
}
$key1 = $dbname.$2;
print "\t",$statlock, " Table: " ,$tablist{$dbname.$2},"\n";
}
}
}
#
# Procedure to get the table name corresponding to a relid.
#
sub tabproc {
&sql("connect ".$_[0]) || return;
$sql_stmt = "
select trim(relid)
from iirelation
where reltid = $_[1]";
&sql($sql_stmt) || return;
while ($table_name = &sql_fetch()) {
$key = $_[0].$_[1];
$tablist{$key} = $table_name;
}
&sql_exec("commit");
&sql("disconnect");
return;
}
------------------Cut Here------------------------------------------
--
********************************************************************
Do not follow where the path may lead . Go instead where there is
no path and leave a trail.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Gajendra Jain
DBA,TRIS Division Voice.(217)351-8250 Ext:2381
CSC CIS - TRIS Division Fax.(217)351-7420
115 N. Neil St. Suite 409 Email. gjain@csci.csc.com
Champaign, IL 61824-0770
********************************************************************
From: tsmith@ingres.co.uk (Tony Smith)
Subject: Re: Lockstat output
Date: 15 Jul 1996 11:31:50 GMT
Hi Toby,
The DB ID is the numerical id given to every database on its creation. You
can select this id from the iidatabase catalog in the iidbdb database, but
it will be in decimal. You can also see it in hex in the output of infodb.
Once you know which database you're dealing with the next step is to
identify the table. A table (or index) is uniquely identified by it's
reltid and reltidx identifiers. The reltid is the id of the table, and
the reltidx the id of a secondary index. In the output above,
TABLE=[3,0], 3 is the reltid, 0 is the reltidx.
reltid > 0 and reltidx = 0 => Base table
reltid > 0 and reltidx > 0 => Secondary Index
To find the table in question, connect to the database and :
select *
from iirelation
where reltid = 3
and reltidx = 0
That should tell you which table is being accessed. In this case, as the
reltid is 3, I can confidently predict that it is the system catalog
iiattribute which is being locked. As the type of lock is an SV_PAGE lock,
it's not a user lock, but rather that the page is being locked into a
fast_commit server's cache.
Hope this helps,
Tony.
*****************************************************************************
Tony Smith
CA (UK) Technical Support
*****************************************************************************
-
Ingres Q & A
-
To William's Home Page
© William Yuan 2000
Email William