How do I determine if my database has performance problems?

This page describes using commands and utilities to check and maintain your Rational solution for Collaborative Lifecycle Management (CLM) databases.

DB2

DB2 utilities

db2top

There are several tools that you can use to help you diagnose DB2 problems and database performance issues. The db2top utility is a tool that comes with DB2 (supported on AIX, Linux, HP-UX, and Solaris operating systems) and can be utilized to quickly identify global problems, or specific database partition problems in the system. The DB2 problem determination using db2top utility article on developerWorks covers the advantages this tool offers, and how to use it for monitoring and troubleshooting. In addition, it has two sample cases that illustrate how to use this tool to diagnose real problems in a production environment.

For the db2top command syntax and parameters, see db2top - DB2 monitoring tool command in the DB2 knowledge center.

Health Monitor

The health monitor captures information about the database manager, database, table space, and table space containers. The health monitor calculates health indicators based on data retrieved from database system monitor elements, the operating system, and DB2 database. For more information about Health Monitor, see Monitoring database health in the DB2 knowledge center.

DB2 commands

You can also use some DB2 commands to diagnose and troubleshoot database issues. Use the DB2 Command Window (db2cmd) to run these commands. To connect to a remote server, you can use Telnet or SSH. Ensure that you are connected as the db2inst1 user by running the su db2inst1 command. Also make sure that Db2 is started before running these commands. The db2start command, starts DB2.

Memory tracker command

The db2mtrk command provides a complete report of memory status, for instances, databases, agents, and applications. This command outputs the following memory pool allocation information:

• Current size
• Maximum size (hard limit)
• Largest size (high water mark)
• Type (identifier indicating function for which memory will be used)
• Agent who allocated pool (only if the pool is private)
• Application

Sample db2mtrk output

By combining all memory usages, you can determine the amount of memory a database consumes. This calculation must be done for all databases on the database server. For example, if your database server is hosting a separate database for JTS, CCM, QM, and Data Warehouse, run the db2mtrk command on each database to get the memory usage. A typical Jazz Team Server database needs 2 GB of memory. So only for all Jazz Team Server databases you need 8 GB of memory, plus the amount memory that is needed for the operating system and other applications and processes on your database server.

For more information about the memory tracker command syntax, parameters, and other examples, see db2mtrk - Memory tracker command.

RUNSTATS command

Updates statistics about the physical characteristics of a table and the associated indexes. These characteristics include the number of records, the number of pages, and the average record length. You can use these statistics when determining access paths to the data. Issue this command when a table has had many updates or after reorganizing a table.

For more information about the RUNSTATS command, see RUNSTATS command in the DB2 knowledge center.

Performance Health Check widget

Another place you can check to see how your database is performing, is the Performance Health Check widget on the Jazz Team Server dashboard. More information about this widget and how to use it can be found here: Performance Health Check widget

DB2 problem determination using AIX commands and utilities

The DB2 problem determination using AIX commands and utilities article on developerWorks covers the AIX utilities and commands to administer and troubleshoot issues that affect the operation of your DB2 database such as CPU usage, orphan processes, memory leaks, hangs, and more. This article also discusses the data collection process, which you can use to troubleshoot problems yourself or to send the collected data to IBM Technical Support.

Oracle

Oracle memory monitoring:

• Monitoring Memory
• Monitoring Real-Time Database Performance
• Oracle Must Gather will guide you on how to see problematic queries and the information IBM will need to process these

SQL Server

SQL Server memory monitoring:

• Monitor Memory Usage
• SQL Server memory related performance counters

Related topics: Why is my database consuming so many resources

External links:

• Maximizing performance

Additional contributors: None