Chat now with support
Chat with Support

Spotlight on DB2 6.10 - User Guide

Spotlight on IBM DB2 LUW (Linux, Unix, and Windows)
New in This Release Getting started with Spotlight on IBM DB2 LUW Desktop features specific to Spotlight on IBM DB2 LUW Spotlight on IBM DB2 LUW drilldowns
About Spotlight on IBM DB2 LUW drilldowns Buffer Pool Analysis drilldown Client Application Analysis drilldown Database Analysis drilldown Database Manager Summary drilldown Diagnostic Log drilldown FCM Analysis drilldown Tablespace Analysis drilldown Top SQL drilldown Operating System drilldown Workload Management Analysis drilldown
Spotlight on IBM DB2 LUW alarms Spotlight on IBM DB2 LUW Options Tuning SQL statements in Spotlight on IBM DB2 LUW
Spotlight Basics
Spotlight Connections Monitor Spotlight Connections Alarms Charts, Grids And Home Page Components View | Options Troubleshooting
Spotlight History Spotlight on Windows
Connect to Windows Systems Background Information Home Page Alarms Drilldowns View | Options Troubleshooting
Spotlight on Unix About us Third-party contributions Copyright

Log Utilization alarm

The Log Utilization alarm becomes active when the percentage of log space currently used by the database is approaching the total amount of log space available for the database.

The underlying metrics for this alarm can be adjusted to suit your environment by using the Metric Editor.

What you can do

  • Access the Databases drilldown. The Log Status tab identifies the values of key log configuration parameters as well as the log transaction rate and the average amount of log space used per transaction.

  • Consider adjusting the LOGFILSZ , LOGPRIMARY , and LOGSECOND database configuration parameters.

Related topics

Package Cache Hit Rate alarm

The Package Cache Hit Rate alarm becomes active when the package cache hit rate, expressed as a percentage, is low.

Package cache is memory that temporarily stores package and section information required for the execution of static and dynamic SQL statements. When applications obtain static SQL information from package cache, they eliminate I/O on system catalogs. When applications obtain dynamic SQL information from package cache, they avoid the cost of compiling the SQL statements.

Low package cache hit rate means that you are incurring more overhead to reload static SQL and recompile dynamic SQL.

The default thresholds are predefined as follows:

  • Threshold 1—The zero to 69 percent range. This raises High severity alarm stating, The package cache hit ratio is extremely low.

  • Threshold 2—The 70 to 79 percent range. This raises a Medium severity alarm stating, The package cache hit ratio is low.

  • Threshold 3—The 80 to 89 percent range. This raises a Low severity alarm stating, The package cache hit ratio is moderate.

  • Threshold 4—The 90 to 100 percent range. The package cache hit ratio is high. No alarms are raised.

The underlying metricsfor this alarm can be adjusted to suit your environment by using the Metric Editor.

What you can do

You can consider increasing the size of package cache using the PCKCACHESZ database configuration parameter. If your database supports heavy transaction processing volumes, your current package cache size might not be able to accommodate these volumes.

 

Related topics

Package Cache Overflows alarm

The Package Cache Overflows alarm becomes active when package cache inserts have resulted in overflows. An overflow occurs when the limit set by the PCKCACHESZ database configuration parameter is exceeded. The package cache is forced to borrow memory from other entities in database shared memory, causing possible lock list escalation, loss of concurrency, out of memory errors, and overall performance degradation.

This alarm has been predefined with a severity level of 4 (low alarm). The underlying metric for this alarm can be adjusted to suit your environment by using the Metric Editor.

What you can do

Determine whether or not you need to increase the size of the package cache to avoid overflows. Clicking on the Package Cache element opens the Databases drilldown where you can review statistics for the package cache.

Related topics

Lock List Utilization alarm

The Lock List Utilization alarm becomes active when the amount of lock list storage used, expressed as a percentage, approaches its maximum size, as defined by the LOCKLIST database configuration parameter.

Lock list is the memory allocated for locks in a database. Once the lock list is full, database performance degrades. Lock escalation increases, reducing concurrency on shared objects in the database. Additionally, because applications are waiting on a limited number of table locks, more deadlocks between applications can occur, causing transactions to be rolled back.

The thresholds are predefined as follows:

  • Threshold 1—The zero to 69 percent range. The lock list utilization percentage is very low. No alarms are raised.

  • Threshold 2—The 70 to 79 percent range. This raises a Low severity alarm stating, The lock list utilization percentage is moderate.

  • Threshold 3—The 80 to 89 percent range. This raises a Medium severity alarm stating, The lock list utilization percentage is high.

  • Threshold 4—The 90 to 100 percent range. This raises a High severity alarm stating, The lock list utilization percentage is very high.

The underlying metricsfor this alarm can be adjusted to suit your environment by using the Metric Editor.

What you can do

  • Perform frequent COMMITs to release locks.

  • When performing many updates, lock the entire table before updating (using the SQL LOCK TABLE statement). This tactic uses only one lock and keeps others from interfering with the updates. However, it does reduce concurrency of the data.

  • Use the LOCKSIZE parameter of the ALTER TABLE statement to control how locking is performed for a specific table.

  • Access the Databases drilldown. The Locking tab allows you to profile the database’s lock list utilization.

  • Access the Client Applications drilldown. The Locking tab identifies those applications holding locks as well as those waiting for locks.

 

Related topics

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating