Set tables activity
The poster wanted to know whether a set of tables was seeing any activity (selects, inserts, updates, deletes) and where such information is recorded. Replies pointed to the sysmaster table sysptprof (joined with sysptnhdr on partnum), which shows isreads, bufreads, pagreads, iswrites, bufwrites, pagwrites and lockreqs per table; Art Kagel noted TSTATS must be enabled in the ONCONFIG. Follow-ups confirmed the counters accumulate since instance startup or the last 'onstat -z', which resets them, and that running UPDATE STATISTICS inflates the read counters for the tables it touches.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good morning everyone I want to know if a set of tables have activity (select, insert, update and delete over these tables). Anybody know if exists these information in the sysmaster or systems catalog? Thanks a lot Servio Velasco
You are looking for the information that is stored in sysptprof:
SELECT dbsname, tabname,
isreads, bufreads, pagreads,iswrites, bufwrites, pagwrites, lockreqs,
FROM sysptprof, sysptnhdr
WHERE sysptprof.partnum = sysptnhdr.partnum
--AND dbsname =
ORDER BY 3 desc, 1,2;
-----Ursprüngliche Nachricht-----
Von: "SERVIO VELASCO"
Gesendet: Jan 27, 2011 2:35:02 PM
An: ids@iiug.org
Betreff: Set tables activity [22596]
>Good morning everyone
>
>I want to know if a set of tables have activity (select, insert, update and
>delete over these tables).
>
>Anybody know if exists these information in the sysmaster or systems catalog?
>
>Thanks a lot
>Servio Velasco
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
___________________________________________________________
Empfehlen Sie WEB.DE DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.web.de
Info is in sysptprof if you have TSTATS set in the Onconfig. Art On Jan 27, 2011 6:35 AM, "SERVIO VELASCO" <servio_velasco@hotmail.com> wrote: > Good morning everyone > > I want to know if a set of tables have activity (select, insert, update and > delete over these tables). > > Anybody know if exists these information in the sysmaster or systems catalog? > > Thanks a lot > Servio Velasco > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf3054a5b12475d7049ad48a8e
Thanks Gerd and Art
I have a question. The information in sysptprof is from the moment of start up
the instance until the current time.
And Can I clear that table with onstat -z?
Thanks a lot
Servio Velasco
Hello anybody Other question. Update statistics modified that table? Thank Servio Velasco
Yes, "onstat -z" will reset the values.
... and "update statistics" can bring a lot of traffic into these counters.
-----Ursprüngliche Nachricht-----
Von: "SERVIO VELASCO"
Gesendet: Jan 27, 2011 3:55:17 PM
An: ids@iiug.org
Betreff: Re: Set tables activity [22602]
>Thanks Gerd and Art
>
>I have a question. The information in sysptprof is from the moment of start up
>the instance until the current time.
>
>And Can I clear that table with onstat -z?
>
>Thanks a lot
>
>Servio Velasco
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
___________________________________________________________
Empfehlen Sie WEB.DE DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.web.de
Since startup or onstat -z
On Jan 27, 2011 7:55 AM, "SERVIO VELASCO" <servio_velasco@hotmail.com>
wrote:
> Thanks Gerd and Art
>
> I have a question. The information in sysptprof is from the moment of
start up
> the instance until the current time.
>
> And Can I clear that table with onstat -z?
>
> Thanks a lot
>
> Servio Velasco
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--20cf30433fd0c176d9049ad5be00
It will affect the read stats for each table updated, yes. Art On Jan 27, 2011 8:11 AM, "SERVIO VELASCO" <servio_velasco@hotmail.com> wrote: > Hello anybody > > Other question. Update statistics modified that table? > > Thank > > Servio Velasco > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf3054a4a1af1dfd049ad60b94