Besides being a stellar product, Db2 includes everything you need to monitor and tune Db2. I will point out 2 of these facilities in this post, and provide more info on additional capabilities in a later post. For point-based monitoring, Db2 provides for DSMTOP on Linux/UNIX and Windows platforms. It was formerly known as Db2top prior to DB2 10.1. DSMTOP uses the mon_get table functions as its source of performance data. DSMTOP is launched from the Db2 command line. It provides for monitoring of currently executing SQL, sessions and applications along with monitoring of Db2 memory areas and various caches. It can interface with db2explain and enhance SQL tuning. You can use it to identify locking issues and long running SQL. It is a great free tool and you should become familiar with it. Below is an example of a DSMTOP screen show DB level information.
Get more info on it here: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0070123.html.
The MONREPORT reporting tool is very handy for identifying currently running SQL with various top 10 rankings in terms of CPU, IO and Wait time. You can use it to grab suspect running SQL and feed it to an explain and can also farm the package cache for dynamic and static SQL statements. Below is a brief example of some output provided:
Result set 1
TEXT
Monitoring report – current SQL
Database: GC_PROD
Generated: 02/05/2021 12:48:42
— Command options —
MEMBER: All
================================================================================
Part 1 – Summaries by ‘top’ metrics
Top 10 current activities by TOTAL_CPU_TIME
ACTIVITY UOW_ID APPLICATION TOTAL_CPU STMT_TEXT
_ID _HANDLE _TIME
——– —— ———– ——— ————————————–
1 1 12030 209103740 SELECT DATE(MAX(lt.Time)) AS “Date” ,
1 1 5095 0 CALL monreport.currentsql()
63 1 5095 0 SELECT ARRAY_AGG(A.ACTIVITY_ID ORDER B
1 20 10770 0 SELECT Acc_Num, Segment_Code, Type, Se
Top 10 current activities by ROWS_READ
ACTIVITY UOW_ID APPLICATION ROWS_READ STMT_TEXT
_ID _HANDLE
——– —— ———– ————- ———————————-
1 1 12030 0 SELECT DATE(MAX(lt.Time)) AS “Date
63 1 5095 0 SELECT ARRAY_AGG(A.ACTIVITY_ID ORD
1 20 10770 0 SELECT Acc_Num, Segment_Code, Type
1 1 5095 0 CALL monreport.currentsql()
Top 10 current activities by DIRECT READS + DIRECT WRITES
ACTIVITY UOW_ID APPLICATION DIRECT_READS + STMT_TEXT
_ID _HANDLE DIRECT_WRITES
——– —— ———– ————– ———————————
1 1 12030 0 SELECT DATE(MAX(lt.Time)) AS “Dat
63 1 5095 0 SELECT ARRAY_AGG(A.ACTIVITY_ID OR
1 20 10770 0 SELECT Acc_Num, Segment_Code, Typ
1 1 5095 0 CALL monreport.currentsql()
================================================================================
Part 2 – Overall ranking of activities
ACTIVITY UOW_ID APPLICATION TOTAL_CPU ROWS_READ DIRECT_READS +
_ID _HANDLE _TIME DIRECT_WRITES
——– —— ———– ——— ————- ———————-
1 1 12030 1 1 1
1 1 5095 2 1 1
1 20 10770 2 1 1
Output from the pkgcache option of monreport:
^C
C:\Program Files\IBM\SQLLIB_01\BIN>db2 call monreport.pkgcache |more
Result set 1
TEXT
Monitoring report – package cache
Database: GC_PROD
Generated: 02/05/2021 12:50:35
— Command options —
CACHE_INTERVAL: All statements
SECTION_TYPE: Dynamic and static SQL
MEMBER: All
================================================================================
Part 1 – Summaries by ‘top’ metrics
Top 10 statements by TOTAL_CPU_TIME
# TOTAL_ STMT_TEXT
CPU_TIME
— ———– —————————————————————
1 60808862119 with a as ( select b.CASINO_ID ProductCode ,case b.CASINO_I
2 54956843451 with a as ( select b.CASINO_ID ProductCode ,case b.CASINO_I
3 14570462338 SELECT given_name, family_name, username, ca.LOCK AS ACC_LOCK,
4 13923316926 VALUES( GENERATE_UNIQUE())
5 13156703141 SELECT DATE(MAX(lt.Time)) AS “Date” , lt.acc_num , lt.IP , MAX(
6 10642076225 select a.ACC_NUM as CustomerId ,a.DOB as CustomerDateOfBirth f
7 5150965819 WITH target AS ( SELECT segment_code, minimum, maximum, state,
8 4601218295 SELECT h.reward_ext_account_id, SUM(h.wager_amount) AS wager_am
9 2582440580 SELECT HEX(Trans_Num) AS Trans_Num, Acc_Num, Trace_Type, Type_D
10 2322480497 SELECT Acc_Num, HEX(Client_Id) AS ClientId, Casino_Id, Password
Top 10 statements by TOTAL_CPU TIME per exec
# TOTAL_ STMT_TEXT
CPU_TIME
— ———– —————————————————————
5 257974571 SELECT DATE(MAX(lt.Time)) AS “Date” , lt.acc_num , lt.IP , MAX(
11 31699403 select b.CASINO_ID productCode ,case b.CASINO_ID when ‘LS’ the
12 31137800 select b.CASINO_ID productCode ,case b.CASINO_ID when ‘LS’ the
13 30310994 select b.CASINO_ID productCode ,case b.CASINO_ID when ‘LS’ the
14 29718191 select b.CASINO_ID productCode ,case b.CASINO_ID when ‘LS’ the
15 28828984 select b.CASINO_ID productCode ,case b.CASINO_ID when ‘LS’ the
16 27643376 select a.CASINO_ID productCode ,case a.CASINO_ID when ‘LS’ the
17 24991360 select sum(new_cust) new_customers, sum(today_purchase) purchas
18 23088148 select a.CASINO_ID productCode ,case a.CASINO_ID when ‘LS’ the
19 22058541 select ca.ACC_NUM,HAS_PURCHASE,NEW_CAMPAIGN_ID,ACC_STATUS,
Top 10 statements by TOTAL_ACT_WAIT_TIME
# TOTAL_ACT LOCK_WAIT STMT_TEXT
_WAIT_TIME _TIME
— ———– ———– ————————————————–
— More —
The default collection interval for MONREPORT is 10 seconds. Although there are a few more report options with MONREPORT I find these two presented are the ones most often used. The DBSUMMARY option provides some good info on bottlenecks but you will almost always find that writing to logs has the most wait time.
This is just a brief overview of two monitoring facilities provided with Db2. You’ll want to make sure you are familiar with them and using them. For a complete description of MONREPORT options go here:
There is a beta create PDF option in the Db2 Knowledge Center online that enables you to create a PDF from a section or topic of the online documentation. This comes in handy if you want something for reference later when you are not online or want to research a topic in more detail. Check it out, I think it’s quite handy.
Honored to be named an IBM Champion for the 12th time. The Champions are a great group of professionals with highly technical skills along with being social influencers in their area of IBM product expertise. #IBMChampion @IBMChampions
Db2 11.5.5 is designed to take full advantage of IBM Cloud Pak for Data and to work seamlessly with other cloud offerings such as AWS and Azure. Featuring a machine learning (ML) optimizer, Db2 11.5.5 also makes it easier to containerize Db2 databases using its OpenShift Click to Containerize tool. For more info check out the following links: https://searchdatamanagement.techtarget.com/news/252492597/IBM-to-deliver-refurbished-Db2-for-the-AI-and-cloud-era
https://www.ibm.com/cloud/blog/announcements/db2-11-5-5-webinar-series
Fully homomorphic encryption allows data to remain encrypted even while being processed or analyzed in cloud or third-party environments. IBM’s new offering, IBM Security Homomorphic Encryption Services provides tools, services and support to implement the new service. A cloud managed compute environment is available from IBM. To learn more, go to the announcement (https://newsroom.ibm.com/Homomorphic-Encryption-Services) and if you want the details regarding this new encryption there is a a very detailed video at https://www.youtube.com/watch?v=O8IvJAIvGJo.
Review the Gartner study and all all the new webinars in Db2 11.5.5 at the following link: https://video.ibm.com/playlist/635188
The Db2 technical product development team publishes a monthly newsletter which contains important Db2 news and lists upcoming Db2 webinars. You can find it here: https://mailchi.mp/4e109d0258af/ibm-db2-1155-webinar-series
I gave a presentation on db2pd in 2005, IDUG Europe. I believe this was the first presentation given on db2pd. I did a follow-up in Ohio to a Db2 Users Group. Its available for download on this site under presentations….I still use db2pd for a variety of tasks augmented by administrative views, MONREPORT and custom monitoring scripts.
SMS tablespaces have been deprecated for serveral releases. There isn’t a straight forward way to convert these to Automatic Storage Management tablespaces. A fast, reliable way to do this is to create an ASM Tablespace to replace the SMS tablespace, extract the DDL for the table to be moved using db2look, create the new table with a different name in the new tablespace and then use LOAD FROM CURSOR to load the table data into the new table. Upon completion, and verification of row counts from old to new, rename the old to old, and rename the new one to the old name. This method using load from cursor has been around since at least V8.2 of Db2 LUW. So get movin’ before it is too late. The above method can also be used to “rescue” an SMS tablespace that has run out of space..
Recently I have been contacted by several clients reporting various degrees of lock-related problems in OLTP systems. One of the first item I check is the setting of the DB CFG locktimeout configuration parameter. What I find is that many times the default of -1 is used which doesn’t allow for any lock timeouts. This is one of the first parameters you will want to change, a value of 10000 (10 secs) is a good starting point. Additionally, make sure that you are taking advantage of the lock avoidance registry variables ( Covered in a future post). Here are a few links to some info to help you along the way with understanding Db2 lock isolation levels along with JDBC driver settings:
https://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2isolationlevel/
Review the locktimeout setting here: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0000329.html
