Overview of LOCK ISSUES in DB2

In this post we are going to discuss about three main lock issues:

•        > Lock Escalations
•        > Lock Waits
•        > DeadLocks

Lock Escalations:

How many of you know that locks will also use memory? Yes they uses a a certain amount of memory and CPU. Sometimes there may be many row level locks present on a single table. So it may lead to more usage of memory and CPU. This this kind of scenarios DB2 automatically acquire a table level lock by replacing all the row level locks to free up the lock memory. This is know as "Lock Escalation".

Read more

How to Identify the version and service level of DB2 product?

To know the DB2 product version and service level we can use "db2level".
The output of the "db2level" command will be as below.

The combination of the four informational tokens uniquely identify the precise service level of your DB2 instance. This information is essential when contacting IBM Software Support for assistance.

For JDBC or SQLJ applications, if you are using the IBM DB2 Driver for SQLJ and JDBC, you can determine the level of the driver by running the db2jcc utility:

db2jcc -version
IBM DB2 JDBC Driver Architecture 2.3.63

Read more

Troubleshooting tools for Performance Tuning: Analyzing db2diag log files using db2diag tool

Primary log files are for the use of database and administration notification log are for system administrators. The db2diag log files are for the use of IBM Software Support for troubleshooting.

Even administration notification log messages are also logged to the db2diag log files.

The db2diag tool serves to filter and format the volume of information available in the db2diag log files. Filtering db2diag log file records can reduce the time required to locate the records needed when troubleshooting problems.

Read more

Troubleshooting tools for Performance Tuning: DB2DART

Overview of the db2dart tool

The main tool that is used to verify the database architecture and the objects that are belonging to a database. The contents of database control files will be displayed to extract data from tables.

db2dart will create a report file with a default name <databaseName>. RPT. The report file will be created in the current directory for a single-partition database. If it is a multiple-partition database file will be created under the diagnostic directory. The subdirectory is called DART<dbpartition number>.

Read more

Checking archive log files with the db2cklog tool

Checking archive log files with the db2cklog tool

Checking archive log files with the db2cklog toolChecking your archive log files ensures that known good log files are available in case a rollforward recovery becomes necessary and that the recovery operation does not fail because of a problem with a log file. The db2cklog tool reads either single log files or a range of numbered log files and performs checks on the internal validity of the files. Log files that pass validation without any error messages or warnings are known good files and you can use them during a rollforward recovery operation. If an archive log file fails validation with an error message or if a warning is returned, then you must not use that log file during rollforward recovery.

Read more

Establishing a performance tuning strategy

The Design Advisor

The DB2 Design Advisor is a tool that can help you significantly improve your workload performance. The task of selecting which indexes, materialized query tables (MQTs), clustering dimensions, or database partitions to create for a complex workload can be daunting. The Design Advisor identifies all of the objects that are needed to improve the performance of your workload.

Given a set of SQL statements in a workload, the Design Advisor generates recommendations for:

Read more

Self-tuning memory in partitioned database environments

There are few factors to determine the self tuning feature will tune the system appropriately in partitioned database environment.

When self-tuning memory is enabled for partitioned databases, a single database partition is designated as the tuning partition, and all memory tuning decisions are based on the memory and workload characteristics of that database partition.

After tuning decisions on that partition are made, the memory adjustments are distributed to the other database partitions to ensure that all database partitions maintain similar configurations.

The single tuning partition model assumes that the feature will be used only when all of the database partitions have similar memory requirements. You have to go through the following cases when determining whether to enable self-tuning memory on your partitioned database.

Read more