In a key finding, nearly half of respondents said a lack of partnership between operational units and the IT department was a top roadblock to getting new applications to market. Hmm…seems like I have heard this story before……..https://hbr.org/resources/pdfs/comm/oracle/19018_HBR_Oracle%20Report_rethinking_the_it_business_partnership_to_drive_digital_innovation.pdf
I’m doing a presentation on HADR and TSAMP at IDUG NA 2015 and have many clients running HADR. Over the various releases of DB2 HADR manageability, monitoring, and performance have been significantly improved. With new monitoring elements and log relay delay, along with log spooling, HADR runs like a smoothe running machine, with very few problems and near zero downtime. I recently came across the updated DB2 HADR Performance Wiki updated by lab guru Yuke Zhuge. Make sure you read this before you begin your HADR journey, you’ll be glad you did. Stay tuned!
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/DB2HADR/page/HADR%20perf
Waiting to try out BLU Acceleration on commodity hardware using non-RDMA capable NICs? You can do that in DB2 10.5 fixpak 5 in DB2 WSE….Get it here: http://www-01.ibm.com/support/docview.wss?uid=swg24038828 and read about it at the IBM BLU HUB, http://www.ibmbluhub.com/get-technical/blu-whatsnew-cancun/#Windows
I have received quite a few inquires lately from folks wanting to learn how to utilize the DB2 HADR multiple Standby and log delay feature introduced in DB2 LUW 10.1. Here is a link to a great whitepaper from my friends in the IBM DB2 Toronto Lab. http://public.dhe.ibm.com/software/dw/data/dm-1206hadrmultiplestandby/HADR_Multiple_Standbys_jn20.pdf
This was recently announced and I posted about this the other day. Kelly Schlamb from the Toronto has a good blog write-up about this. I’m very excited about what this offering can bring to my customers. Read about it here: http://ibmdatamanagement.co/tag/purescale/
and remember, stay tuned!
dashDB is a fully managed data warehousing and analytics as a service offering on the cloud. dashDB lets you set up a data warehouse in minutes. It takes the data warehouse infrastructure out of your way so you can focus on the business. Signup for this Jan 29, 215 deep dive here: http://bit.ly/tt2015jan
IBM announced yesterday a new batch of offerings for DB2 LUW at FP5. Get all the details here: http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?infotype=AN&subtype=CA&htmlfid=897/ENUS215-028&appname=USN. And remember, stay tuned!
I’ve mentioned this before so what are you waiting for? 🙂 Not kidding, a lot of folks running WSE aren’t aware of this. Also, range partitioned tables are included, yes I said included in WSE in DB2 10.5. This is a BIG feature that you can now use in WSE at DB2 10.5. Get moving and stay tuned!
Many times I get calls that something is sucking up the server and clients need to know what’s up. A very simple way nowadays is to use the MONREPORT reporting module first provided in DB2 9.7. It should be the first tool in your toolbag. Simply connect to the database where the reported problem is, and from a command line issue the following command:
“db2 call monreport.currentsql > csql.txt”
Examine the contents of the output file and if a query is actually executing it will be captured here. You can run subsequent calls to the module and don’t redirect the output to a file until you “catch” one executing. If you don’t catch one executing after repeatedly calling the monreport.currentsql stored procedure there is nothing running of interest or whatever is running is running sub-second. It is then doubtful SQL is your problem. Believe me, if it is, you will catch the SQL using this technique, guaranteed. There are other ways but this is easiest. Stay Tuned!
I can identify any SQL that is causing a problem by using many different techniques. One I want to share here is the use of the MONREPORT reporting module and EXPLAIN_FROM_SECTION stored procedure. First to identify high cost SQL I run the MONREPORT.PKGCACHE reporting module and review each of the report sections for SQL of interest. See Below example:
C:\Users\Administrator>db2 connect to migamprd
Database Connection Information
Database server = DB2/NT64 9.7.6
SQL authorization ID = ADMINIST…
Local database alias = MIGAMPRD
C:\Users\Administrator>db2 call monreport.pkgcache > mrgame16.txt
Cat or otherwise read the contents of mrgame16.txt and I was interested in the first Statement in
terms of most CPU used.
Result set 1
————–
TEXT
——————————————————————————–
——————————————————————————–
Monitoring report – package cache
——————————————————————————–
Database: MIGAMPRD
Generated: 01/06/2015 08:42:27
— 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 9709892236 select a.ACC_NUM as AccountNumber, b.CASINO_ID,
Next, I went to the bottom of the report and retrieved the executable ID for this statement.
================================================================================
Part 2 – EXECUTABLE_IDs for statements in Part 1
# EXECUTABLE_ID
— —————————————————————————-
1 x’01000000000000001DB603000000000000000000020020150104135354228000′
2 x’0100000000000000CF1E03000000000000000000020020150101090403025000′
3 x’010000000000000024B603000000000000000000020020150104135522743001′
4 x’0100000000000000590000000000000007000000010020141024003001986004′
5 x’0100000000000000BC1E03000000000000000000020020150101090017480000′
6 x’0100000000000000001D03000000000000000000020020150101073549623000′
7 x’0100000000000000F31C03000000000000000000020020150101073511060001′
I then passed the executable ID to the EXPLAIN_FROM_SECTION strored procedure as follows:
C:\Users\Administrator>db2 call explain_from_section (x’01000000000000001DB603000000000000000000020020150104135354228000′,’M’, NULL, 0, NULL, ?, ?, ?, ?, ? )
Value of output parameters
————————–
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : ADMINISTRATOR
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : ADMINISTRATOR
Parameter Name : EXPLAIN_TIME
Parameter Value : 2015-01-06-09.11.02.227000
Parameter Name : SOURCE_NAME
Parameter Value : SYSSH200
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
Return Status = 0
To see the actual access path used at run time I then formatted the explain tables as follows:
C:\Users\Administrator>db2exfmt -d migamprd -e administrator -w 2015-01-06-09.11.02.227000 -n SYSSH200 -s NULLID -t -#0 |more
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material – Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
I’m omitting some extraneous data here…..
Connect to Database Successful.
Enter section number (0 for all, Default 0) ==>
Connecting to the Database.
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material – Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.6
SOURCE_NAME: SYSSH200
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2015-01-06-09.11.02.227000
EXPLAIN_REQUESTER: ADMINISTRATOR
Database Context:
—————-
Parallelism: None
CPU Speed: 3.306410e-007
Comm Speed: 0
Buffer Pool size: 5100628
Sort Heap size: 16000
Database Heap size: 25000
Lock List size: 80000
Maximum Lock List: 22
Average Applications: 1
Locks Available: 563200
Package Context:
—————
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
—————- STATEMENT 1 SECTION 4 —————-
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
——————
select a.ACC_NUM as AccountNumber, b.CASINO_ID,
ltrim(rtrim(upper(a.GIVEN_NAME))) as firstName,
ltrim(rtrim(upper(a.FAMILY_NAME))) as lastName,
ltrim(rtrim(upper(DECRYPT_CHAR(a.ECONTACT, :L0)))) as email,
ltrim(rtrim(upper(a.ADDRESS))) as address,
ltrim(rtrim(upper(a.CITY))) as city,
ltrim(rtrim(upper(a.STATE_PROV))) as state,
ltrim(rtrim(upper(a.COUNTRY))) as countryCode,
ltrim(rtrim(upper(a.ZIP))) as zip, a.DAY_PHONE as DayPhone, a.DOB as
birthday, s.SEGMENT_CODE as GameSegment, a.DATE_ACTIVATED as
DateActivated, b.CONVERSION_DATE as DateConverted,
case
when b.ACC_STATUS = :L1
then :L2
else :L3 end as AccountStatus, REAL_CHIPS as RealBalance, PROMO_CHIPS as
PromoBalance, a.GENDER, b.PASSWORD as password,
case
when b.ACC_STATUS = :L4
then c.REASON_ID
else null end as REASON_ID, b.REAL_PLAYER, a.UPDATE_TS as LastUpdated,
ltrim(rtrim(ip.IP)) as IP
from DB2ADMIN.CLIENT as a inner join DB2ADMIN.CLIENT_ACC as b on a.ACC_NUM =
b.ACC_NUM left outer join DB2ADMIN.CLIENT_SEGMENT as s on a.ACC_NUM =
s.ACC_NUM left outer join
(select ACC_NUM, LOCK, REASON_ID, ACTION, row_number() over(partition by
ACC_NUM
order by LOCK desc) as rankLock
from
(select ACC_NUM, ACTION, LOCK, REASON_ID
from DB2ADMIN.ACC_BLOCKING as a) as b) as c on a.ACC_NUM = c.ACC_NUM
and c.rankLock = :L5 and c.ACTION = :L6 left outer join
(select ACC_NUM, IP, row_number() over(partition by ACC_NUM
order by TIME) as rankIP
from
(select ACC_NUM, IP, TIME
from DB2ADMIN.LOGIN_TRACE
where coalesce(IP,:L7) != :L8
union
select ACC_NUM, IP, LOCK as TIME
from DB2ADMIN.PROXY_LOGIN_TRACE
where coalesce(IP,:L9) != :L10) as a) as ip on a.ACC_NUM = ip.ACC_NUM
and ip.rankIP = :L11
where a.ACC_NUM != :L12 and a.COUNTRY is not null and (
COALESCE(b.CONVERSION_DATE,:L13) >= (current timestamp) – :L14
minutes)
Optimized Statement:
——————-
SELECT Q20.ACC_NUM AS “ACCOUNTNUMBER”, Q20.CASINO_ID AS “CASINO_ID”,
LTRIM(RTRIM(UPPER(Q20.GIVEN_NAME))) AS “FIRSTNAME”,
LTRIM(RTRIM(UPPER(Q20.FAMILY_NAME))) AS “LASTNAME”,
LTRIM(RTRIM(UPPER(DECRYPT_CHAR(Q20.ECONTACT, :L0)))) AS “EMAIL”,
LTRIM(RTRIM(UPPER(Q20.ADDRESS))) AS “ADDRESS”,
LTRIM(RTRIM(UPPER(Q20.CITY))) AS “CITY”,
LTRIM(RTRIM(UPPER(Q20.STATE_PROV))) AS “STATE”,
LTRIM(RTRIM(UPPER(Q20.COUNTRY))) AS “COUNTRYCODE”,
LTRIM(RTRIM(UPPER(Q20.ZIP))) AS “ZIP”, Q20.DAY_PHONE AS “DAYPHONE”,
Q20.DOB AS “BIRTHDAY”, Q20.SEGMENT_CODE AS “GAMESEGMENT”,
Q20.DATE_ACTIVATED AS “DATEACTIVATED”, Q20.CONVERSION_DATE AS
“DATECONVERTED”,
CASE
WHEN (Q20.ACC_STATUS = :L1)
THEN :L2
ELSE :L3 END AS “ACCOUNTSTATUS”, Q20.REAL_CHIPS AS “REALBALANCE”,
Q20.PROMO_CHIPS AS “PROMOBALANCE”, Q20.GENDER AS “GENDER”,
Q20.PASSWORD AS “PASSWORD”,
CASE
WHEN (Q20.ACC_STATUS = :L4)
THEN Q20.REASON_ID
ELSE NULL END AS “REASON_ID”, Q20.REAL_PLAYER AS “REAL_PLAYER”, Q20.UPDATE_TS
AS “LASTUPDATED”, LTRIM(RTRIM(Q20.$C0)) AS “IP”
FROM
(SELECT Q19.$C1, Q10.UPDATE_TS, Q10.GENDER, Q10.DATE_ACTIVATED, Q10.DOB,
Q10.DAY_PHONE, Q10.ZIP, Q10.STATE_PROV, Q10.CITY, Q10.ADDRESS,
Q10.ECONTACT, Q10.FAMILY_NAME, Q10.GIVEN_NAME, Q10.COUNTRY,
Q10.ACC_NUM, Q10.REASON_ID, Q10.SEGMENT_CODE, Q10.REAL_PLAYER,
Q10.PASSWORD, Q10.PROMO_CHIPS, Q10.REAL_CHIPS, Q10.ACC_STATUS,
Q10.CASINO_ID, Q10.CONVERSION_DATE
FROM
(SELECT Q9.REASON_ID, Q5.UPDATE_TS, Q5.GENDER, Q5.DATE_ACTIVATED,
Q5.DOB, Q5.DAY_PHONE, Q5.ZIP, Q5.STATE_PROV, Q5.CITY,
Q5.ADDRESS, Q5.ECONTACT, Q5.FAMILY_NAME, Q5.GIVEN_NAME,
Q5.COUNTRY, Q5.ACC_NUM, Q5.SEGMENT_CODE, Q5.REAL_PLAYER,
Q5.PASSWORD, Q5.PROMO_CHIPS, Q5.REAL_CHIPS, Q5.ACC_STATUS,
Q5.CASINO_ID, Q5.CONVERSION_DATE
FROM
(SELECT Q1.SEGMENT_CODE, Q4.UPDATE_TS, Q4.GENDER, Q4.DATE_ACTIVATED,
Q4.DOB, Q4.DAY_PHONE, Q4.ZIP, Q4.STATE_PROV, Q4.CITY,
Q4.ADDRESS, Q4.ECONTACT, Q4.FAMILY_NAME, Q4.GIVEN_NAME,
Q4.COUNTRY, Q4.ACC_NUM, Q4.REAL_PLAYER, Q4.PASSWORD,
Q4.PROMO_CHIPS, Q4.REAL_CHIPS, Q4.ACC_STATUS, Q4.CASINO_ID,
Q4.CONVERSION_DATE
FROM DB2ADMIN.CLIENT_SEGMENT AS Q1 RIGHT OUTER JOIN
(SELECT Q3.REAL_PLAYER, Q3.PASSWORD, Q3.PROMO_CHIPS,
Q3.REAL_CHIPS, Q3.ACC_STATUS, Q3.CASINO_ID,
Q3.CONVERSION_DATE, Q2.UPDATE_TS, Q2.GENDER,
Q2.DATE_ACTIVATED, Q2.DOB, Q2.DAY_PHONE, Q2.ZIP,
Q2.STATE_PROV, Q2.CITY, Q2.ADDRESS, Q2.ECONTACT,
Q2.FAMILY_NAME, Q2.GIVEN_NAME, Q2.COUNTRY, Q2.ACC_NUM
FROM DB2ADMIN.CLIENT AS Q2, DB2ADMIN.CLIENT_ACC AS Q3
WHERE Q2.COUNTRY IS NOT NULL AND (Q2.ACC_NUM <> :L12) AND
(Q2.ACC_NUM = Q3.ACC_NUM) AND ($C1 <=
COALESCE(Q3.CONVERSION_DATE, $C0))) AS Q4 ON (Q1.ACC_NUM
<> :L12) AND (Q4.ACC_NUM = Q1.ACC_NUM)) AS Q5 LEFT OUTER
JOIN
(SELECT Q8.ACC_NUM, Q8.REASON_ID, Q8.ACTION, Q8.$C3
FROM
(SELECT Q7.ACC_NUM, Q7.REASON_ID, Q7.ACTION, ROW_NUMBER() OVER
(PARTITION BY Q7.ACC_NUM ORDER BY Q7.LOCK DESC)
FROM
(SELECT Q6.ACC_NUM, Q6.LOCK, Q6.REASON_ID, Q6.ACTION
FROM DB2ADMIN.ACC_BLOCKING AS Q6) AS Q7) AS Q8) AS Q9 ON
(Q9.ACTION = :L6) AND (Q9.$C3 = :L5) AND (Q5.ACC_NUM =
Q9.ACC_NUM)) AS Q10 LEFT OUTER JOIN
(SELECT Q18.$C0, Q18.$C1, Q18.$C2
FROM
(SELECT Q17.$C0, Q17.$C1, ROW_NUMBER() OVER (PARTITION BY Q17.$C0
ORDER BY Q17.$C2)
FROM
(SELECT Q16.$C0, Q16.$C1, Q16.$C2
FROM
(SELECT DISTINCT Q15.$C0, Q15.$C1, Q15.$C2
FROM
(SELECT Q11.ACC_NUM, Q11.IP, Q11.LOCK
FROM DB2ADMIN.PROXY_LOGIN_TRACE AS Q11
WHERE (COALESCE(Q11.IP, :L9) <> :L10)
UNION ALL
SELECT Q13.ACC_NUM, Q13.IP, Q13.TIME
FROM DB2ADMIN.LOGIN_TRACE AS Q13
WHERE (Q13.IP <> :L8)) AS Q15) AS Q16) AS Q17) AS Q18) AS Q19
ON (Q19.$C2 = :L11) AND (Q10.ACC_NUM = Q19.$C0)) AS Q20
Explain level: Explain from section
Access Plan:
———–
Total Cost: 331169
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
168272
>MSJOIN
( 2)
331144
NA
/————+————\
168272 0.309234
>MSJOIN FILTER
( 3) ( 14)
96128 234955
NA NA
/———+———-\ |
168272 0.00119964 189385
TBSCAN FILTER FILTER
( 4) ( 11) ( 15)
94872.4 1230.75 234955
NA NA NA
| | |
168272 734.7 4.73462e+006
SORT FILTER TBSCAN
( 5) ( 12) ( 16)
94858.5 1230.75 232639
NA NA NA
| | |
168272 36735 4.73462e+006
>^HSJOIN IXSCAN SORT
( 6) ( 13) ( 17)
94654.9 1205.74 231496
NA NA NA
/——–+——–\ | |
168272 612432 -1 4.73462e+006
^HSJOIN IXSCAN INDEX: DB2ADMIN UNION
( 7) ( 10) GTS1_ACC_BLOCKING ( 18)
75551.1 19003.4 Q6 217249
NA NA NA
/—–+——\ | /——-+——-\
504815 204144 -1 143279 4.59135e+006
TBSCAN TBSCAN INDEX: DB2ADMIN IXSCAN TBSCAN
( 8) ( 9) GTS9CS ( 19) ( 20)
42809.8 32655.5 Q1 7639.33 209610
NA NA NA NA
| | | |
612433 612433 -1 4.59136e+006
TABLE: DB2ADMIN TABLE: DB2ADMIN INDEX: DB2ADMIN TABLE: DB2ADMIN
CLIENT CLIENT_ACC GTS1_PROXY_LOGIN_TRACE LOGIN_TRACE
Q2 Q3 Q11 Q13
As a result I have the actual runtime access plan that is used as well as the reoptimized SQL. Notice the table scan on the client table. This would be my next are to drill down into using the plan details of the db2exfmt output which I have omitted due to size and what I want to show you in a subsequent post. This method produces the actual access path used which is helpful but it does not contain the values for the host variable which is really what I want to see if actuals would affect the access path selection. In the next posting I will show how to obtain the actual data processed versus host variables. Stay tuned….
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 