Using the IBM command DSPFD with the *MBR option to create an output file containing information for your most business critical database libraries, would be an easy approach. Using a query tool, you can analyze this output file and the "Activity Statistics" for all of the physical files used by your application software.

The information of highest importance is the data regarding "Logical Reads". The database files on your system with the most logical reads are likely some of the largest transaction history files and the most queried files, with the poorest database and SQL tuning.

For existing Workload Performance Series v7.0 customers, our Disk Navigator tool automates this process of analyzing "Disk Activity Statistics". Another approach for environments with extensive SQL based application software would be to use the IBM command STRDBMON. This command starts the IBM "Database Monitor" which intercepts every query or SQL request that runs on your system. This data can be sent to an output file and queried for analysis purposes. The ENDDBMON command stops the operating system process and leaves you with lots of data to analyze.

Using a query tool, you can retrieve various kinds of information about the queries and SQL running on your system. Looking at the "Index Advised" information is of critical importance. Analyzing the SQL "Elapsed Time" statistics can be of enormous value as well.

Our Query Optimizer tool automates this data collection and analysis process, with built in controls to ensure that the negative impact of running the STRDBMON command on Production systems is minimized.