Database Management 101
TECHNICAL ARTICLES -> PERFORMANCE ARTICLES
[ Back ] [ Next ]

The best defense is a potent offense! This is especially true of database administration. At Software Concepts International, LLC, we don’t just sit and wait for problems to occur to the databases we manage, we actively monitor these databases to anticipate problems before they happen.

An essential part of the management process is monitoring the usage of all storage areas. This monitoring process is facilitated using RMU/ANALYZE (Rdb) and DBO/ANALYZE (DBMS). As part of our Remote DBA Service, we perform weekly analyses of every database that we manage. The information that we gather tells us how full each sector is, as well as the number of rows and their average length. However, knowing this information at a specific point in time provides only limited information. The real power of this tool is in its ability to analyze how usage changes over time.

At SCI, we plot this information so that we get a visual picture of how the database changes. After capturing a few week’s information utilizing the /ANALYZE command, a very clear picture of important trends emerges.

By analyzing the current rate of growth, we are able to predict where the database will be at a specific point in the future. Based on this analysis, we recommend the most beneficial archives to avoid over utilization or extending of the areas (which degrades the performance of the database). If archiving is insufficient to prevent the over utilization of the areas, then they must be resized to accommodate the projected data volumes. By analyzing the trends in our area usage charts, we are able to select the optimal area size when restructuring. Sizing is then based on educated analysis rather than a lottery. As downtime proves very costly with any high-availability site, the advance warning that this procedure provides is very desirable and allows customers to plan accordingly.

If there are particular files that require more careful monitoring, the “/AREAS” qualifier allows one to limit the analysis to specific areas (the default is all areas). Running the /ANALYZE command generally does not take long (although the size of the database is a determining factor) but it is an essential tool for forecasting. 

The /ANALYZE command is very flexible, allowing a company to tailor the output to its requirements. It can be run /ONLINE, gathering information at any time without causing disruption to the database users or run in batch at a predetermined time. 

In DBMS, the default “ANALYZE” captures very detailed information about all sets (relationships) in a database. However, the set information is generally excessive for ongoing reporting requirements and causes the job to run significantly longer. As a result, we recommend /NOSET for ongoing monitoring of a DBMS database.