Finding table names from the output of onstat -g p
Dave Thacker asked how to map the hex partnum values shown by 'onstat -g ppf' back to table names. Early suggestions were to select tabname, hex(partnum) from the local systables (tabid > 99), but that failed for him — Art Kagel pointed out it misses system catalog tables, tables in other databases, detached indexes and fragments, and recommended querying sysmaster:systabnames instead (which covers all partitions server-wide). Others also noted matching problems caused by a typo/extra zero in the grep string and by hex values being displayed zero-padded (e.g. 0x00100038), so grepping just "100038" works.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm having a brain lapse this morning. How do I convert the partnum in
onstat -g ppf into a table name?
DT
select tabname, partnum, hex(partnum)
from systables
where tabid > 99;
I believe hex(partnum) is what 'onstat' is showing you?
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Dave Thacker
Sent: Wednesday, March 07, 2007 11:56 AM
To: ids@iiug.org
Subject: Finding table names from the output of onstat .... [8592]
I'm having a brain lapse this morning. How do I convert the partnum in
onstat -g ppf into a table name?
DT
This should get you the cross reference you are looking for:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname'
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Dave Thacker
Sent: Wednesday, March 07, 2007 10:56 AM
To: ids@iiug.org
Subject: Finding table names from the output of onstat .... [8592]
I'm having a brain lapse this morning. How do I convert the partnum in
onstat -g ppf into a table name?
DT
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Doesn't work for fragmented tables or for detached indexes.
Art S. Kagel
----- Original Message -----
From: Robert Roussey <ids@iiug.org>
At: 3/07 12:04:03
select tabname, partnum, hex(partnum)
from systables
where tabid > 99;
I believe hex(partnum) is what 'onstat' is showing you?
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Dave Thacker
Sent: Wednesday, March 07, 2007 11:56 AM
To: ids@iiug.org
Subject: Finding table names from the output of onstat .... [8592]
I'm having a brain lapse this morning. How do I convert the partnum in
onstat -g ppf into a table name?
DT
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid >
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dave Thacker
> Sent: Wednesday, March 07, 2007 10:56 AM
> To: ids@iiug.org
> Subject: Finding table names from the output of onstat .... [8592]
>
> I'm having a brain lapse this morning. How do I convert the partnum in
> onstat -g ppf into a table name?>
> DT
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid
>
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid >
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dave Thacker
> Sent: Wednesday, March 07, 2007 10:56 AM
> To: ids@iiug.org
> Subject: Finding table names from the output of onstat .... [8592]
>
> I'm having a brain lapse this morning. How do I convert the partnum in
> onstat -g ppf into a table name?>
> DT
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
You didn't get anything because the affected partnum is one of:
a) A system catalog table.
b) A table in a different database than the one you're attached to.
c) A detached index.
d) A fragmente of a table or detached index.
Use the query on sysmaster:systabnames I posted!
Art S. Kagel
----- Original Message -----
From: Dave Thacker <ids@iiug.org>
At: 3/07 12:23:17
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid >
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dave Thacker
> Sent: Wednesday, March 07, 2007 10:56 AM
> To: ids@iiug.org
> Subject: Finding table names from the output of onstat .... [8592]
>
> I'm having a brain lapse this morning. How do I convert the partnum in
> onstat -g ppf into a table name?>
> DT
>
> ************************************************************************
> *******
> 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.
Well... you do have more zeros in one place than the other:
0x100038 3690 0 0 0 4920 0 0 0 4920 0
Vs.
grep "0x1000038"
--EEM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Dave Thacker
Sent: Wednesday, March 07, 2007 11:25 AM
To: ids@iiug.org
Subject: RE: Finding table names from the output of ons.... [8598]
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid
>
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' |
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid
>
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dave Thacker
> Sent: Wednesday, March 07, 2007 10:56 AM
> To: ids@iiug.org
> Subject: Finding table names from the output of onstat .... [8592]
>
> I'm having a brain lapse this morning. How do I convert the partnum in
> onstat -g ppf into a table name?>
> DT
>
>
************************************************************************
> *******
> 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.
You can try:
> echo "select tabname, hex(partnum) tblsnum from systables where tabid >
> 99;" | dbaccess 'mydbname' | grep "100038"
because the hex(partnum) is displayed as 0x00100038
Good luck !
_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=000000000066
Sorry, I assumed that you knew the DB you were dealing with ... Listen
to Art .. he rarely steers us wrong .. if ever ..:-)
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Dave Thacker
Sent: Wednesday, March 07, 2007 11:23 AM
To: ids@iiug.org
Subject: RE: Finding table names from the output of ons.... [8597]
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid
>
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dave Thacker
> Sent: Wednesday, March 07, 2007 10:56 AM
> To: ids@iiug.org
> Subject: Finding table names from the output of onstat .... [8592]
>
> I'm having a brain lapse this morning. How do I convert the partnum in
> onstat -g ppf into a table name?>
> DT
>
>
************************************************************************
> *******
> 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.
Oh it happens. Too often for my personal happiness. 8-(
Art S. Kagel
----- Original Message -----
From: Joe R. Plugge <ids@iiug.org>
At: 3/07 13:39:26
Sorry, I assumed that you knew the DB you were dealing with ... Listen
to Art .. he rarely steers us wrong .. if ever ..:-)
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Dave Thacker
Sent: Wednesday, March 07, 2007 11:23 AM
To: ids@iiug.org
Subject: RE: Finding table names from the output of ons.... [8597]
On Wed, 2007-03-07 at 12:03 -0500, Plugge, Joe R. wrote:
> This should get you the cross reference you are looking for:
>
> echo "select tabname, hex(partnum) tblsnum from systables where tabid
>
> 99;" | dbaccess 'mydbname'
onstat -g ppf (greatly trimmed for example purposes)
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 25
days 09:11:53 -- 2819744 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100038 3690 0 0 0 4920 0 0 0 4920 0
1230 95
0x1000e2 3654 0 0 0 2436 0 0 0 4872 0 0
100
0x1000e3 0 0 0 0 0 0 0 0 3690 0 0
91
0x1000f0 69892 20 0 0 23315 0 23315 0 69965 23301
23315 100
0x2100003 2496652 70275 0 0 274418 109311 0 109282 2254930
447359 0 100
If I run this:
echo "select tabname, hex(partnum) tblsnum from systables where tabid >
99;" | dbaccess 'mydbname' | grep "0x1000038"
it doesn't return anything.
I've also tried this:
database mydbname;select
systables.tabname,
systables.partnum,
HEX(systables.partnum)
FROM systables, sysmaster:systabinfo
WHERE
HEX(systables.partnum) = "0x100038" AND
systables.partnum = sysmaster:systabinfo.ti_partnum;
Am I joining the wrong table?
DT
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dave Thacker
> Sent: Wednesday, March 07, 2007 10:56 AM
> To: ids@iiug.org
> Subject: Finding table names from the output of onstat .... [8592]
>
> I'm having a brain lapse this morning. How do I convert the partnum in
> onstat -g ppf into a table name?>
> DT
>
>
************************************************************************
> *******
> 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.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g