When organizations look to migrate from Oracle to PostgreSQL database, they usually see the advanced features, high performance, flexible open-source licensing, data integrity and easy availability from public cloud providers. But the way to migration is not easy, and you need to know how to avoid the obstacles if you are to be successful..
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:
In this tables, I relate general terminology, versions, utilities and views from IBM DB2 and Oracle databases..
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?
It’s relatively simple and unfamiliar tool for the novices: db2top.
Using the option “T” (tables) and sorting the list (key “z”) using column 1.
The first position will be for the most accessed table.
If there is much difference between that and the rest during an appreciable period of normal use of the database, these tables would be candidates to improve their access or optimice their access to improve the overall performance,
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.
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:
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
In this shosrt post we will show how to obtain the N first rows from a Query on IBM DB2.
With Oracle SQL would be with ROWNUM, and with SQL Server we'd use TOP.
This is the Top 10 query syntax:
select * from My_Table fetch first 10 rows only
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:
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 Unported License.