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.
This default deadlock event monitor has been deprecated for quite some time, since at least Db2 9.7. However, most places I run into still have it defined and it is running but many times its output is not used or it is still being used instead of the new LOCKING event monitor that took it’s place. To see how to create the new LOCKING event monitor, drop the old db2detaildeadlock and format the new event monitor data, refer to this link: https://www.ibm.com/docs/en/db2/11.1?topic=statements-create-event-monitor-locking. If Db2 was installed correctly and the DB2 Java is installed, you shouldn’t have any problem compiling and using the db2evmonfmt.java program. Follow this link on how to set it up https://www.ibm.com/docs/en/db2/11.1?topic=tables-db2evmonfmt-tool-reading-event-monitor-data.
Recently I’ve been getting a lot of questions on HADR so I updated my presentation a bit and uploaded to the presentations page.
For the last 19 years Gunning Technology Solutions, LLC has been providing a myriad of services for Db2 on Linux, UNIX and Windows…This year with COVID and it’s impact on the workforce we have been extremely busy helping clients succeed with Db2. We have a very large financial services client for 15 years running that continues to meet business objectives running Db2 on a large Windows Server, running over 10 billion transactions a year with zero downtime in an HADR environment with Reads-On-Standby.
Another client uses Db2 to store analytical data regarding Wi-Fi quality of service data for large hospitals and financial services companies. A plethora of analytics are provided to the clients to understand and review all aspects of there wireless network infrastructure. We have done lots of SQL tuning, index redesign, Db2 configuration updates and improved their ability to purge obsolete data using Db2 Stored Procedures and referential integrity with cascade deletes. We also right-sized there Db2 versions which were a mixture of Db2 Express-C, Db2 Express and DB2 Workgroup Server Edition.
We have a client that is very large in the music business, offering sheet music worldwide. This offering uses Db2 on Linux RedHat to provide clients with access to music products worldwide. Most of our work here has been upgrading to a supported release of Db2, firefighting (tuning problematic SQL) in real-time, and educating the client support personnel on Db2 operations and support. We provide support on a retainer basis for this client.
Another client uses Db2 on AIX as there platform of choice for Healthcare benefits enrollment. This client has in-house servers and also installed software clients on premises. Major work here has been providing 24×7 support for the production environment which consists of Power9 servers running AIX 7.2 with many LPARS. We’ve tuned SQL, conducted extensive index redesign, fixed DB2 configuration problems, automated reorgs for only those objects that need it and continue to provide Db2 database administration support for production.
So this year we haven’t been as visible on social media because we are just busy doing Db2. And, all Db2 and Security related conferences which were initially planned for onsite were changed to virtual conferences due to the ongoing COVID issues.
We participated in calls with the IBM Db2 Toronto Lab team, Customer Advisory Councils for both Db2 on LUW and z/OS and other IBM product offerings that are of interest to our clients.
In summary, we continue to help people succeed with Db2!!!
Although you may be a Db2 database professional, you are always concerned about data access, authorities and privileges along with unauthorized database object access attempts. Well, IBM’s Cloud Pak for Security can make your job as a Db2 professional easier to accomplish. And the documentation has been updated similarly to Db2 documentation in an easy to find, all inclusive manner. For example, using Cloud Pak for Security you can track an attempted unauthorized access back to the originating IP address and geographical location, and using QRadar, you may find that the unauthorized access attempt is tied to an existing hacker organization. You can then quickly change firewall and router configs to deny access. For more information on the packaging, take a look at the overview, demo and documentation here: https://www.ibm.com/products/cloud-pak-for-security and here: https://www.ibm.com/docs/en/cloud-paks/cp-security/1.7.0?topic=cloud-pak-security
IBM has been a leader in AI and Machine Learning as recognized by Gartner previously. IBM’s Cloud Pak for Data includes all the tools you need to begin your journey to AI in a single solution. With Cloud Pak for Data you get the tools to build, deploy and manage AI at scale in hybrid cloud environments. Get more information here: https://www.ibm.com/products/cloud-pak-for-data and find a detailed overview and documentation here: https://www.ibm.com/docs/en/cloud-paks/cp-data/3.5.0?topic=overview. Now get ready to run on the cloud, on premises and connect from anywhere. #IBMChampion #Db2
Your friendly former Db2 Knowledge Center has a new name and format. Now known as Documentation, you can easily find everything you need to know about a particular product. For example, you can find links to IBM Support should you need to open a service request, change between versions of the product, or even find Redbooks of interest. I find it very handy since it has links to everything Db2. Here is the link to the Db2 11.5 Documentation: https://www.ibm.com/docs/en/db2/11.5
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.
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 