Eliminating IO or making it as fast as possible is a tenet of operational databases. As a DBA it is important to understand how SSD technology can drastically improve your database performance and what tradeoffs can be made, for example more or faster SSD and reduced RAM for bufferpools, and recovery considerations. Slower SATA technology may still be appropriate for historical data or archives that are seldom accessed. Learn more from this article: https://www.kingston.com/en/ssd/what-is-nvme-ssd-technology#:~:text=PCIe%204.0%20doubled%20that%20to,communicate%20data%20with%20storage%20devices.
A picture is worth a thousand words…..
Sometimes a new application gets into production with, should I say, not fully tested with undesirable access plan and the first the DBA is aware of this is when you see sudden growth in the temporary storage tablespace filesystem. This can also fill the filesystem and cause application failures across the database. There is a very easy way to find the culprit. Launch db2pd for the database in question as shown in the following example:
pgunning@[lhp200hs]:
(/home/hsprd/sqllib/db2dump)> db2pd -d <dbname> -tcbstats > tcb1101.txt
pgunning@[lhp200hs]:
(/home/hsprd/sqllib/db2dump)> cat tcb1101.txt | grep -i TEMP | more
The schema column, in this case shows the application handle <41555> is using two temp storage tables along with how much space is being used. You can use this iteratively to see if space usage is growing an determine if action is needed to preclude the tempspace1 filesystem from filling up. You can identify the application by using dmctop, db2pd or mon_get table function.
Although this document is Db2 SAP specific, there are many useful recommendations that apply to a non-SAP environment. You can get the pdf here https://help.sap.com/doc/769ab46cf5ff405084e5d3a821705e52/12_1/en-US/DB6_Upgrade_12_1.pdf
I recently came across a good article on how to do this. Check it out at https://www.idug.org/news/encrypting-a-db2-database-with-minimal-downtime-using-hadr
I’ve been working with Db2 for Linux, UNIX and Windows for almost 30 years. The monitoring capabilities provided with the product are substantial and can be used to provide all the monitoring and performance data one needs to ensure Db2 is performing as expected. I’ve had scripts that capture and rank the top suboptimal SQL for all of those years, along with scripts using either snapshot or table functions to gather the other data such as bufferpool, tablespace and table performance. With STMM controlling DBM and DB memory for the most part, many configuration problems have disappeared and STMM does a good job at keeping it that way. So, based on my experience, most performance problems nowadays come down to suboptimal SQL. There are many ways to capture suboptimal SQL and one of them is by using the db2mon tool that comes with Db2. You can use it to augment the MONREPORT reporting module and MON_GET table functions. There is an excellent article written by a member of the Db2 Toronto Lab performance team on the capabilities of db2mon. Check it out you will be glad you did. Here is the link: https://www.idug.org/news/an-introduction-to-db2mon.
Check-out all the excellent presentations slated for this fall in Dusseldorf, Germany. I’ll be presenting on Db2 LUW Performance and Tuning, offering 30 years of insights and experience with Db2 from the earliest releases to the present. See the grid here: https://assets.noviams.com/novi-file-uploads/idug/emea_grid_30_06_2025.pdf
See the announcement here:
With the release of DB2 12.1 IBM has incorporated AI into an already stellar product. The advanced Optimizer incorporates additional AI to assist companies with improved performance from continuous feedback. Support for Object Storage and increased AWS integration and offerings will continue to place Db2 in the forefront of Cloud Based database solutions. Its been a great ride and I’ve seen so many improvements over the years to make Db2 very easy for DBAs to implement, support, and monitor all aspects of database performance. With most settings set to automatic and with automatic storage tablespaces, companies can get up and running very fast with Db2 and get good performance right out of the box. With advanced optimization and AI, developers can spend less time optimizing SQL queries while Db2 takes care of it in the background. These improvements enable DBAs to spend more time on high availability, clustering, backup and recovery, disaster recovery planning and ways to exploit all the capabilities offered in Db2 12.1. For more information refer to my previous post on this release. Cheers!
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 