When troubleshooting SQL performance problems, the Db2 provided procedure, Explain_From_Section is your friend. Simply obtain the executable ID from the MONREPORT.PKGCACHE report and pass it to the Explain_From_Section procedure. Then, use db2exfmt to format the explain tables and the explain will be generated for further review. Of course you could feed the SQL as input to the Design Advisor to see if lack of indexes are the problem, else possible rewrite required. Note that if the Explain_From_Section procedure fails to find the SQL associated with the executable ID because it is no longer in the package cache, you can use the MON_GET_ACTIVITY table function in conjunction with the MON_GET_PKG_CACHE_STMT and MON_GET_SECTION table functions for detailed information.
AI, as everyone knows is a rapidly expanding technology. Steve Piper, CEO of CyberEdge Group and Editor-in-Chief of Security Buzz has prepared a brief entitled “Top Five Cybersecurity Predictions for 2026”. He discusses how acquiring AI experience is critical for IT professionals to acquire for long term job security. I see Agentic AI as an area where DBAs should expand their skills. After all, its all about the data, and protecting it. Read all about it here: https://www.brighttalk.com/webcast/260/634971
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
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 