table name from onlog output
Frank could no longer map onlog output back to table names via a sysmaster sysptprof query after upgrading from IDS 9.4 to 10.00.UC5 on AIX. Replies pointed out the partnum is still in the onlog line (e.g. 8000b5) and can be resolved with 'oncheck -pt 0x8000b5'. The query failed only because HEX() returns uppercase letters; suggested fixes were to uppercase the value in the script (e.g. tr '[a-f]' '[A-F]', optionally converting to decimal with bc or a small C program) before comparing. Problem resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
HI, Folks, We used to be able to figure out the table names from the output of ONLOG unility by the following query from sysmaster database, select trim(dbsname), trim(tabname), partnum, lockreqs, lockwts, pagreads, pagwrites from sysptprof where HEX(partnum) ='$tbnum', where '$tbnum' is the magic number which we could picked up from the output of ONLOG utility. After we upgraded from IDS9.4 to IDS10 UC5(AIX5.3), somehow we lose this capability. For example, the the output now becomes, .................... addr len type xid id link 10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809 saapmgr 10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262 10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1 250 10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12 10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809 saapmgr 10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262 10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1 250 10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12 10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809 saapmgr .......................... Sounds there is NO magic number that you could translate to a table name by the above query. Any idea? Thanks, Frank
In bold 8000b5 is the partnum, use that in the query as $tbnum.
Alternately you could use 'oncheck -pt 0x8000b5' that will also give you
table name.
for more details on onlog output -
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.adref
.doc/adref260.htm
> .....................
> addr len type xid id link
>10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
>saapmgr
>10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
>10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
>250
>10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
>10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
>saapmgr
>10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
>10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
>250
>10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
>10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
>saapmgr
> ...........................
Regards,
- Nilesh -
"FRANK" <yunyaoqu@gmail.com>
Sent by: ids-bounces@iiug.org
03/09/2007 03:18 PM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
table name from onlog output [8635]
HI, Folks,
We used to be able to figure out the table names from the output of ONLOG
unility by the following query from sysmaster database,
select trim(dbsname), trim(tabname), partnum, lockreqs, lockwts, pagreads,
pagwrites from sysptprof where HEX(partnum) ='$tbnum',
where '$tbnum' is the magic number which we could picked up from the
output
of ONLOG utility.
After we upgraded from IDS9.4 to IDS10 UC5(AIX5.3), somehow we lose this
capability. For example, the the output now becomes,
.....................
addr len type xid id link
10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
saapmgr
10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
250
10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
saapmgr
10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
250
10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
saapmgr
...........................
Sounds there is NO magic number that you could translate to a table name
by
the above query.
Any idea?
Thanks,
Frank
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks, Nilesh! oncheck works! But the query does NOT unless we do further
manual work.
For example, we must change 0x8000b5 to 0x8000B5, i.e., b==>B. This is
not good for our simple script. Any options?
Thanks,
Frank
On 3/9/07, Nilesh Ozarkar <nilesho@us.ibm.com> wrote:
>
> In bold 8000b5 is the partnum, use that in the query as $tbnum.
> Alternately you could use 'oncheck -pt 0x8000b5' that will also give you
> table name.
> for more details on onlog output -
>
>
>
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.adref
.doc/adref260.htm
>
> > .....................
> > addr len type xid id link
> >10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> >10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
> >10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
> >250
> >10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
> >10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> >10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
> >10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
> >250
> >10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
> >10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> > ...........................
>
> Regards,
>
> - Nilesh -
>
> "FRANK" <yunyaoqu@gmail.com>
> Sent by: ids-bounces@iiug.org
> 03/09/2007 03:18 PM
> Please respond to
> ids@iiug.org
>
> To
> ids@iiug.org
> cc
>
> Subject
> table name from onlog output [8635]
>
> HI, Folks,
>
> We used to be able to figure out the table names from the output of ONLOG
> unility by the following query from sysmaster database,
>
> select trim(dbsname), trim(tabname), partnum, lockreqs, lockwts, pagreads,
>
> pagwrites from sysptprof where HEX(partnum) ='$tbnum',
>
> where '$tbnum' is the magic number which we could picked up from the
> output
> of ONLOG utility.
>
> After we upgraded from IDS9.4 to IDS10 UC5(AIX5.3), somehow we lose this
> capability. For example, the the output now becomes,
>
> ......................
> addr len type xid id link
> 10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> 10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
> 10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
> 250
> 10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
> 10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> 10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
> 10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
> 250
> 10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
> 10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> ............................
>
> Sounds there is NO magic number that you could translate to a table name
> by
> the above query.
>
> Any idea?
> Thanks,
> Frank
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
I'm assuming that you have a filter of some type which is filtering the=
onlog output so that you can get to the partnum. Since 0x123B is the s=
ame
number as 0x123b is the same as 4667, it might be better to convert the=
hex
partnum (i.e. magic number) into decimal rather than to try to match on=
the
hex string.
Here is a small program to do the conversion...
-----------------------------------------------------------------------=
---------------------
#include <stdio.h>
int
main (int numParms, char *parm[])
{
int tnum;
int tdec;
for (tnum =3D 1; tnum < numParms; tnum++)
{
sscanf(parm[tnum],"0x%x", &tdec);
printf("%d\\
", tdec);
}
return (0);
}
-----------------------------------------------------------------------=
----
=
"FRANK" =
<yunyaoqu@gmail.c =
om> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Re: table name from onlog output=
03/09/2007 03:55 [8637] =
PM =
=
=
Please respond to =
ids@iiug.org =
=
=
Thanks, Nilesh! oncheck works! But the query does NOT unless we do furt=
her
manual work.
For example, we must change 0x8000b5 to 0x8000B5, i.e., b=3D=3D>B. This=
is
not good for our simple script. Any options?
Thanks,
Frank
On 3/9/07, Nilesh Ozarkar <nilesho@us.ibm.com> wrote:
>
> In bold 8000b5 is the partnum, use that in the query as $tbnum.
> Alternately you could use 'oncheck -pt 0x8000b5' that will also give =
you
> table name.
> for more details on onlog output -
>
>
>
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=3D/co=
m.ibm.adref.doc/adref260.htm
>
> > .....................
> > addr len type xid id link
> >10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> >10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
> >10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
> >250
> >10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
> >10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> >10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
> >10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
> >250
> >10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
> >10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> > ...........................
>
> Regards,
>
> - Nilesh -
>
> "FRANK" <yunyaoqu@gmail.com>
> Sent by: ids-bounces@iiug.org
> 03/09/2007 03:18 PM
> Please respond to
> ids@iiug.org
>
> To
> ids@iiug.org
> cc
>
> Subject
> table name from onlog output [8635]
>
> HI, Folks,
>
> We used to be able to figure out the table names from the output of O=
NLOG
> unility by the following query from sysmaster database,
>
> select trim(dbsname), trim(tabname), partnum, lockreqs, lockwts,
pagreads,
>
> pagwrites from sysptprof where HEX(partnum) =3D'$tbnum',
>
> where '$tbnum' is the magic number which we could picked up from the
> output
> of ONLOG utility.
>
> After we upgraded from IDS9.4 to IDS10 UC5(AIX5.3), somehow we lose t=
his
> capability. For example, the the output now becomes,
>
> ......................
> addr len type xid id link
> 10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> 10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
> 10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
> 250
> 10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
> 10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> 10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
> 10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
> 250
> 10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
> 10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> ............................
>
> Sounds there is NO magic number that you could translate to a table n=
ame
> by
> the above query.
>
> Any idea?
> Thanks,
> Frank
>
>
>
>
***********************************************************************=
********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
This should work for you:
echo "0x8000b5" |tr '[a-f]' '[A-F]'
in your script.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
FRANK
Sent: Friday, March 09, 2007 3:55 PM
To: ids@iiug.org
Subject: Re: table name from onlog output [8637]
Thanks, Nilesh! oncheck works! But the query does NOT unless we do
further
manual work.
For example, we must change 0x8000b5 to 0x8000B5, i.e., b==>B. This is
not good for our simple script. Any options?
Thanks,
Frank
On 3/9/07, Nilesh Ozarkar <nilesho@us.ibm.com> wrote:
>
> In bold 8000b5 is the partnum, use that in the query as $tbnum.
> Alternately you could use 'oncheck -pt 0x8000b5' that will also give
you
> table name.
> for more details on onlog output -
>
>
>
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.i
bm.adref.doc/adref260.htm
>
> > .....................
> > addr len type xid id link
> >10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> >10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
> >10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
> >250
> >10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
> >10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> >10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
> >10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
> >250
> >10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
> >10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> >saapmgr
> > ...........................
>
> Regards,
>
> - Nilesh -
>
> "FRANK" <yunyaoqu@gmail.com>
> Sent by: ids-bounces@iiug.org
> 03/09/2007 03:18 PM
> Please respond to
> ids@iiug.org
>
> To
> ids@iiug.org
> cc
>
> Subject
> table name from onlog output [8635]
>
> HI, Folks,
>
> We used to be able to figure out the table names from the output of
ONLOG
> unility by the following query from sysmaster database,
>
> select trim(dbsname), trim(tabname), partnum, lockreqs, lockwts,
pagreads,
>
> pagwrites from sysptprof where HEX(partnum) ='$tbnum',
>
> where '$tbnum' is the magic number which we could picked up from the
> output
> of ONLOG utility.
>
> After we upgraded from IDS9.4 to IDS10 UC5(AIX5.3), somehow we lose
this
> capability. For example, the the output now becomes,
>
> ......................
> addr len type xid id link
> 10e0018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> 10e0044 312 HDELETE 45 0 10e0018 8000b5 104 262
> 10e017c 304 DELITEM 45 0 10e0044 8000b6 104 1 1
> 250
> 10e02ac 40 COMMIT 45 0 10e017c 03/09/2007 12:48:12
> 10e1018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> 10e1044 312 HINSERT 45 0 10e1018 8000b5 104 262
> 10e117c 304 ADDITEM 45 0 10e1044 8000b6 104 1 1
> 250
> 10e12ac 40 COMMIT 45 0 10e117c 03/09/2007 12:48:12
> 10e2018 44 BEGIN 45 7385 0 03/09/2007 12:48:12 4354809
> saapmgr
> ............................
>
> Sounds there is NO magic number that you could translate to a table
name
> by
> the above query.
>
> Any idea?
> Thanks,
> Frank
>
>
>
>
************************************************************************
*******
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Frank,
I believe you are using a shell script to figure out the table names from the
output of ONLOG utility. Here is a simple fix for your problem, you need to
replace the <partnum>,
=====
num=`echo "$<partnum>" | tr '[a-f]' '[A-F]'`
tbnum=`echo "ibase=16; $num" | bc`
dbaccess sysmaster << EOF
select trim(dbsname), trim(tabname), partnum, lockreqs, lockwts,pagreads,
pagwrites
from sysptprof where HEX(partnum) = $tbnum
EOF
=====
Thanks,
Sanjit