HELP NEEDED.....

Hi all, I would like to find the frequency of the tables that are being accessed in my database. The reason for this is migration. I want to know what tables are being accessed constantly and what tables are stale (not updated nor rows being changed). I know there is a table called sysptprof in the sysmaster database from which I could get some information from isreads, iswrites columns. But those columns can never become zero unless the engine is bounced. And I don't want to bounce the informix engine everytime coz its a production box. Can anyone let me know how to make these columns to zero without bouncing the informix box. By getting info from sysptprof I can know how the tables in my database are being accessed. Also, does any one have any alternate methode to this. My goal is to know what tables are active(accessed frequently like read/write) and what tables are stale(not accessed frequently like read/write) in the database. Any help on this is greatly appreciated. Thanks, Firoz DBA.

Firoz, The table access values can be reset (zeroed out) via (as informix) issuing the command onstat -z assuming all the proper ENV variables are set. FIROZ MERCHANT wrote: > Hi all, > I would like to find the frequency of the tables that are being accessed in my database. The reason for this is migration. I want to know what tables are being accessed constantly and what tables are stale (not updated nor rows being changed). I know there is a table called sysptprof in the sysmaster database from which I could get some information from isreads, iswrites columns. But those columns can never become zero unless the engine is bounced. And I don't want to bounce the informix engine everytime coz its a production box. Can anyone let me know how to make these columns to zero without bouncing the informix box. > By getting info from sysptprof I can know how the tables in my database are being accessed. > Also, does any one have any alternate methode to this. My goal is to know what tables are active(accessed frequently like read/write) and what tables are stale(not accessed frequently like read/write) in the database. > > Any help on this is greatly appreciated. > > Thanks, > Firoz > DBA. > > -- Scott MacKenzie; Dine' College ISD

Try to run "onstat -z", which normaly reset informix statistics. Other alternative method could be modify your tables adding a trigger which insert your own profile in a table. Juan Jorge Cruces Fernández ADP Clearing MAESTRO C/ Cañada Real de las Merinas, 3 Edificio IV - 2º Planta 28052 MADRID Tel: (34) 91 748 15 50 Fax: (34) 91 748 10 57 e-mail: jorge.cruces@adpclearing.org ----- Original Message ----- From: "FIROZ MERCHANT" <m_firoz@hotmail.com> To: <ids@iiug.org> Sent: Wednesday, July 07, 2004 5:39 PM Subject: HELP NEEDED..... [3205] > Hi all, > I would like to find the frequency of the tables that are being accessed in my database. The reason for this is migration. I want to know what tables are being accessed constantly and what tables are stale (not updated nor rows being changed). I know there is a table called sysptprof in the sysmaster database from which I could get some information from isreads, iswrites columns. But those columns can never become zero unless the engine is bounced. And I don't want to bounce the informix engine everytime coz its a production box. Can anyone let me know how to make these columns to zero without bouncing the informix box. > By getting info from sysptprof I can know how the tables in my database are being accessed. > Also, does any one have any alternate methode to this. My goal is to know what tables are active(accessed frequently like read/write) and what tables are stale(not accessed frequently like read/write) in the database. > > Any help on this is greatly appreciated. > > Thanks, > Firoz > DBA. > >

Dear Mr Firoz, I guess you think on some sort of incremental migration? Which is your source -> target sytem , platform , version ? It won't help to know the busy tables, you rather need to find the large tables. Outline would be: determine you larges tables, for which the unload of the data takes longest. one by one, lock the table, unload it to whatever format is suitable for you. create UPDATE, INSERT , DELETE trigger for the table, which will log any updates , deletes or inserts into a logging table. these entries may be transported to the target system continuously, while the source system is still in production once all the huge tables have been unloaded and loaded into the target system, one final downtime would be required to unload and migrate the rest of the data , which will probably be possible in a much shorter time as the huge tables are already in the target system . Make sure all the entries from the loggin tables have been applied to the target , before you restart the application. The advantage is each downtime is comparatively short. Disadvantage is overhead, and many downtimes instead one. Regards Tilman forum.subscriber@iiug.org wrote on 07/07/2004 17:39:41: > Hi all, > I would like to find the frequency of the tables that are being > accessed in my database. The reason for this is migration. I want to > know what tables are being accessed constantly and what tables are > stale (not updated nor rows being changed). I know there is a table > called sysptprof in the sysmaster database from which I could get > some information from isreads, iswrites columns. But those columns > can never become zero unless the engine is bounced. And I don't want > to bounce the informix engine everytime coz its a production box. > Can anyone let me know how to make these columns to zero without > bouncing the informix box. > By getting info from sysptprof I can know how the tables in my > database are being accessed. > Also, does any one have any alternate methode to this. My goal is to > know what tables are active(accessed frequently like read/write) and > what tables are stale(not accessed frequently like read/write) in > the database. > > Any help on this is greatly appreciated. > > Thanks, > Firoz > DBA. > >