DB2, from version 9.7, allows to know easily what indexes are not being used in a Database. The query is also for tables and packages.

This is a useful tool for tuning indexes and detecting problems in their use.

After version 9.7 DB2 includes a new LASTUSED field in the SYSCAT.INDEXES, SYSCAT.TABLES and SYSCAT.PACKAGES tables.

This field indicates the date of last use of indexes, tables or packages.

For example, to query unused indexes since 1/1/2019, you could use this simple query:

This mini tip show you how to know when was the last time you restart a DB2 server on a UNIX / LINUX.

> db2 get snapshot for dbm | grep Start

Start Database Manager timestamp = 04/02/2011 13:45:20.516473

Simple, isn't it?

Below are a list with the more common status of IBM DB2 tablespaces

The most used method to show the status of the tablespaces is, from the console command line:

Db2look is an utility to extract the definition of database objects. It also allows some really interesting statistics to extract objects for export to other test environments / integration plans and get the same access to objects.

In this article we will see more frequent usefulness.

DDL extraction of the objects in my schema:

db2look -d MI_BBDD -a -e -x -o FICHERO_SALIDA.txt

Extraction of the DDL of the DEPT table:

db2look -d MI_BBDD -t DEPT -a -e -x -o FICHERO_SALIDA.txt

To include statistics to have to use the “-m” option.

The cache hit ratio is one of the indicators used for tuning tasks. It Measures the percentage of hits in memory, specifically in the bufferpools (each of them).

To get the cache hit ratio for each buffer pool configured to run this query:

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME,
TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT,
INDEX_HIT_RATIO_PERCENT
FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM
 

This article shows the query to obtain the history of DB2 versions and patching. That is, not only shows the current version, but since when is installed / updated.

select versionnumber, version_timestamp

from sysibm.sysversions

The result of the query shows the version history / patch installation:

To tune a database is useful to create snapshots that capture information from different structures/components of the instance.

In this post we show how they can be used to perform an analysis of tuning.

First of all, the monitor switches must be on to collect data from the different structures.

The state monitors are available as follows: