Changes on a table
I'm on a 11.50.UC5 database server. What is the best way to gather statistics on the transactions per table. Is this saved somewhere in the sysmaster? Ideally have the deleted, inserted and updated actions all counted separately. Thanks! Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users Group Board of Directors A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratliffe
Hi Kate,
The data you want is in sysmaster:sysptprof. Each fragment for both
tables and indexes are in there. I use the following query a lot as a
starting point.
select first 15
substr(tabname,1,18) as table
, sum(isreads) reads
, sum(iswrites) writes
, sum(isrewrites) updates
-- , sum(isdeletes) deletes
from sysmaster:sysptprof
where tabname not like "sys%" and
-- dbsname = "pperfect" -- and
(isreads >0 or iswrites > 0)
group by tabname
order by reads desc,
writes desc
, updates desc
-- , deletes desc
;
Cheers,
Dick Snoke
IBM ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
http://www.ibm.com/partnerworld
From: "kate" <kate@iiug.org>
To: ids@iiug.org
Date: 11/08/10 02:27 PM
Subject: Changes on a table [21899]
Sent by: ids-bounces@iiug.org
I'm on a 11.50.UC5 database server. What is the best way to gather
statistics on the transactions per table. Is this saved somewhere in the
sysmaster? Ideally have the deleted, inserted and updated actions all
counted separately.
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
A computer lets you make more mistakes faster than any invention in human
history - with the possible exceptions of handguns and tequila.
Mitch Ratliffe
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Kate:
Just wanted to add that in version 11.7 the number of updates,inserts
and deletes for each table (or fragment of a table) is maintained since
the tables was created (or moving to version 11.70).
The sysptprof table only contains the data since it the database server
was started (or stats were reset).
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 11/08/2010 11:39:48 AM:
> [image removed]
>
> Re: Changes on a table [21900]
>
> Richard Snoke
>
> to:
>
> ids
>
> 11/08/2010 11:40 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi Kate,
>
> The data you want is in sysmaster:sysptprof. Each fragment for both
> tables and indexes are in there. I use the following query a lot as a
> starting point.
>
> select first 15>
> substr(tabname,1,18) as table
>
> , sum(isreads) reads
>
> , sum(iswrites) writes
>
> , sum(isrewrites) updates
> -- , sum(isdeletes) deletes
> from sysmaster:sysptprof
> where tabname not like "sys%" and
> -- dbsname = "pperfect" -- and
>
> (isreads >0 or iswrites > 0)
> group by tabname
> order by reads desc,
>
> writes desc
>
> , updates desc
> -- , deletes desc
> ;
>
> Cheers,
> Dick Snoke
> IBM ChannelWorks
>
> dsnoke@us.ibm.com
> (404) 487-1595
> http://www.ibm.com/partnerworld
>
> From: "kate" <kate@iiug.org>
> To: ids@iiug.org
> Date: 11/08/10 02:27 PM
> Subject: Changes on a table [21899]
> Sent by: ids-bounces@iiug.org
>
> I'm on a 11.50.UC5 database server. What is the best way to gather
> statistics on the transactions per table. Is this saved somewhere in the
> sysmaster? Ideally have the deleted, inserted and updated actions all
> counted separately.
>
> Thanks!
> Kate Tomchik [ kate@iiug.org ] www.iiug.org
> International Informix Users Group Board of Directors
>
> A computer lets you make more mistakes faster than any invention in human
> history - with the possible exceptions of handguns and tequila.
> Mitch Ratliffe
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
How about SP drop and create? Does anything track that now in 11.50.FC5?