After DBM and DB CFG settings have been set properly, whether through the use of automatic settings and STMM or from setting them manually, suboptimal SQL is likely to be your number one performance problem. Db2 contains a plethora of internal tools and facilities to assist you with identifying and tuning suboptimal SQL. Prior to the introduction of the MONREPORT reporting tool, I used a custom script to capture and rank the top 10 SQL in terms of CPU usage, rows read, number of executions, etc. Now I use the MONREPORT CURRENTSQL and PKGCACHE reports to capture suboptimal SQL. I then use either db2exfmt, db2expln or the EXPLAIN_FROM_SECTION stored procedure to review suboptimal SQL. In fact, the EXPLAIN FROM SECTION SP is often my first choice. I then feed the output to db2exfmt to explain the SQL. As a matter of routine, I then use DB2 Design Advisor to see if there are any index recommendations which would provide a significant improvement. I then review existing indexes on tables involved to determine if there are already too many indexes on tables involved, review index recommendations for redundancy and then consider application usage and possible SQL rewrite before applying any recommended indexes. I hope this discussion helps you to develop your own procedures for capturing, identifying and tuning suboptimal SQL.
A presentation I gave at the 2002 Db2 Tech Conference in Anaheim, CA.
This post is based on my perspective with over 25 years of experience with Db2 LUW. First, I still have clients continuously running Db2 for over 17 years that I have continuously supported for all those years. What kind of industries do you see using Db2 on Linux, UNIX and Windows? As previously stated, a client of mine has been running Db2 on Windows for over 17 years in a high volume OLTP environment with HADR and reads on standby. Also, a client is using Db2 to store and analyze WIFI performance data on RedHat Linux. Another client on RedHat Linux uses Db2 as their frontend for their Web Commerce portal with HADR. And finally, a client uses Db2 on many AIX servers for their health benefits and Medicare processing application with some on-prem and some in the cloud (SAAS). Some are still running Db2 10.5 and 11.1, while some have recently upgraded to 11.5.7. In my opinion, whether using Db2 on-prem or in-cloud, Db2 has an offering for you and is going strong. Oh, and the most interesting use of Db2 that I was involved in over the last 25 years was a small gold mine in Ontario using Db2 in their mining process!
Check out the below links for the latest on Db2 for z/OS..
https://www.ibm.com/docs/en/db2-for-zos/13?topic=13-db2-function-levels
https://www.ibm.com/docs/en/db2-for-zos/13?topic=13-new-function-apars-db2
C:\Users\Administrator\GTS_METRICS>db2pd -alldbs -hadr
Database Member 0 — Database XXXXXXXX — Active — Up 989 days 01:35:48 — Date 2022-11-18-09.43.54.177000
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SUPERASYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = REMOTE_CATCHUP
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 10.221.37.1
PRIMARY_INSTANCE = XXX
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 10.221.37.2
STANDBY_INSTANCE = XXX
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 10/29/2022 14:51:25.842500 (1667069485)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 2
HEARTBEAT_EXPECTED = 146013918
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 27
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
PRIMARY_LOG_FILE,PAGE,POS = S0793409.LOG, 23758, 135762673334534
STANDBY_LOG_FILE,PAGE,POS = S0793409.LOG, 23184, 135762670997601
HADR_LOG_GAP(bytes) = 3018
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0793409.LOG, 23184, 135762670997601
STANDBY_RECV_REPLAY_GAP(bytes) = 1781
PRIMARY_LOG_TIME = 11/18/2022 09:43:53.000000 (1668782633)
STANDBY_LOG_TIME = 11/18/2022 09:43:25.000000 (1668782605)
STANDBY_REPLAY_LOG_TIME = 11/18/2022 09:43:25.000000 (1668782605)
STANDBY_RECV_BUF_SIZE(pages) = 4096
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 18750000
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N
New APARs are out for DB2 12 function levels. You can get information on them at the following links:
https://www.ibm.com/docs/en/db2-for-zos/12?topic=12-db2-function-levels
https://www.ibm.com/docs/en/db2-for-zos/12?topic=12-new-function-apars-db2
Get the details here https://www.ibm.com/cloud/blog/announcements/ibm-db2-1158-release
Recently I had a client that was running on an old version of a free version of RedHat Linux which became Oracle Linux at some point. I got involved when they wanted to upgrade to Db2 11.5.7. Client purchased and installed RedHat Enterprise Linux and ported the database to it and we were able to successfully upgrade from Db2 11.1 to 11.5.7 in the new RHEL 8 environment with no problems noted. Although Db2 11.1 ran fine on Oracle Linux it is not supported…..
This release contains enhancements to SQL tuning, job scheduling and monitoring. By using Data Management Console, combined with all the other built-in monitoring capabilities of Db2 11.x.x, DBAs and developers have an arsenal of tools at their disposal to develop, tune and deploy SQL and then monitor performance with historical capability. The ability “to go back in time” and see what was running, at a particular time is a “combat multiplier”.
https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=new-version-317
As we end the year and get ready to begin a new year, now is a good time to plan for the upgrade of any 11.1 installations you have. Get working on that upgrade project plan and checklist. See the withdrawal announcement here: https://www.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&appname=iSource&supplier=897&letternum=ENUS920-049.
