Monitor Informix Database Statistics Collection Created by: Colin Dawson Firmdata Systems Limited The Monitor database design and associated program code are the intellectual property of Firmdata Systems Limited. You may use the programs on any number of computers. Any modifications required or made to the programs and code supplied should be sent via email to monitor@firmdata-systems.co.uk c 1998, 1999 Firmdata Systems Limited. The Monitor database is designed to hold historical data on the state of all tables in every Informix database found in a particular RDBMS configuration. The design has been enhanced to include performance statistics. Data is collected using shell scripts run at predetermined intervals to extract the necessary statistical information from the database catalog tables for storing within the monitor database. An 'UPDATE STATISTICS' must have been run before running the monitor script to ensure the latest information is available in the database catalog tables. The collected data will provide an historical representation of table and dbspace utilisation. The performance statistics will give an indication of how each table is being accessed. The accumulated data can be used to provide supporting evidence for any proposed application or database change. Monitor Database The database consists of 5 main tables: 1. Tabhist - History of table data 2. Dbshist - History of dbspace information 3. Svrhist - History of Informix server data 4. Syshist - History of Unix system data 5. Profhist - History of individual table access profiles A 6th table called tabsumm is a monthly summary of tabhist data to avoid excessive amounts of table history data. This table is not critical to the operation of Monitor and can be ignored (the script mon_summ.ksh should not be run). To fully understand the data captured, some knowledge of Informix database and Unix server tuning is necessary. Monitor scripts There are four programs maintaining the database. These programs are written as Korn shell scripts. The scripts are: 1. monitor.ksh 2. mon_summ.ksh 3. mon_profile.ksh 4. mon_sys.ksh Monitor.ksh is the main data collector script for all table and dbspace information. It should be run as frequently as is deemed necessary to capture the changing state of the tables in the database. This script uses tables tabhist and dbshist. A database name must be supplied when running the script. Warning: It is important that an 'Update Statistics' as documented in the Informix Guide to SQL Volume 2 has been run prior to the monitor.ksh script execution, as the process for data collection uses tables in the sysmaster database as well as the application database catalog tables that may only be updated through the update statistics command. Mon_summ.ksh script summarises the table and dbspace data on a monthly basis. Its purpose is to keep the volume of table data to a reasonable level. It is not a requirement for the normal operation of the database. This script uses tables tabhist, dbshist and tabsumm. Mon_profile.ksh is a data collector for table usage information. The data should be used for monitoring data access for possible improvements to program code and index creation. It is recommended that this script is only run several times each day and for a limited number of days as the data volumes generated are significant, especially if there is a large number of tables in the monitored database. At the end of the script the command onstat -z is used to reset the profile statistics for the next execution. This script uses tables profhist and svrhist Mon_sys.ksh takes a snapshot of the system usage. It uses the vmstat command to store CPU usage, memory allocation and run queue size. This script uses table syshist The monitor scripts have been written for use on Unix servers using the Korn shell; A Windows NT command file alternative is under consideration and may be written if there is any demand for it. The scripts have been verified on AIX V4.x using Informix Dynamic Server versions 7.23 and 7.31.