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.
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
