Akiko Hoshikawa 12/14/2016 - …

1 DB2 11 and 12 for z/OS Performance Update Baltimore Washington DB2 RUGA kiko Hoshikawa 12/14/2016 Please noteIBM sstatements regarding its plans, directions, and intent are subject tochange orwithdrawalwithout notice and at IBM ssole regardingpotential future products is intended to outline our general productdirection and it should not be relied on in making a purchasing information mentionedregarding potential future products is not a commitment, promise, orlegal obligation to deliver any material, code orfunctionality.

2 information about potential futureproducts maynot be incorporated into any , release, and timing of any future features orfunctionality described for our products remains at our sole is based onmeasurements and projections using standard IBM benchmarks in acontrolled environment. The actual throughput or performance that anyuser will experience willvary depending upon many factors, including considerationssuch as the amount of multiprogramming in the user s job stream, theI/O configuration, the storage configuration, and theworkloadprocessed.

3 Therefore, no assurance can be given that an individualuser will achieve results similar to those stated of Watson 2016 2 Performance Updates DB2 H/W Synergy Updates Recent Performance PMR Experience Performance Topics in DB2 11DB2 11 Performance Features in DB2 11 New Function Mode Performance Topics in DB2 12 System level Performance Feature 3 Agenda Challenges Ever increasing transactions and data. Can DB2 handle? Yet, we need to reduce IT costs4 Out of the Box CPU savingadded first 64 bit supportWhat Have We Done?

4 No more low hanging fruits Finished 64 bit conversionPCI Processor Capacity Index (IBM MIPS)141-wayCustomer ProcessorsPCI for 1-way1695 Memory10 TBSystem I/O Bandwidth832 GB/Sec* TB512 GB1202902600288 GB/sec* GB/sec*15143 TB384 GB/Sec*101-wayz10 ECz9 ECz196zEC12z13 IBM z13 Will larger buffer pool really helpmy workload? Depend on, Access pattern Active working set sizeBuffer Pool Simulation -DB2 11 CM with PI22091 Benefit from increasing size of buffer pools varies depending on the workload Data access pattern, current buffer pool configuration, etc.

5 Provides accurate simulation results from increasing buffer pool size as you run your production workloads control blocks for simulated pools Simulation pools = SPSIZE Buffer poolcontrol blocks Buffer pools = VPSIZE How To Use Simulation Example Simulate BP1 additional 500,000 for BP1 with seq. prefetchthreshold of 30 % instead of current 60 % Collect statistics data or DISPLAY BUFFER POOL DETAIL command outputs Recommend to reset SPSIZE(0) when simulating different SPSIZE Recommend to take enough samples (2-3 hours ) per SPSIZE Cost Storage : SPSIZE * , CPU.

6 Approx1% per Simulated Pool -ALTER BPOOL(BP1) SPSIZE(500000) SPSEQT(30)-DISPLAY BUFFER POOL (BP1) DETAIL continue to run your workloads (>1hour) -DISPLAY BUFFER POOL (BP1) DETAIL) Measurement IntervalUse this output as simulation resultSimulation Output DSNB432I -CEA1 SIMULATED BUFFER POOL ACTIVITY -AVOIDABLE READ I/O -SYNC READ I/O (R) =25463982 SYNC READ I/O (S) =81181 ASYNC READ I/O =15470503 SYNC GBP READS (R) =11172099 SYNC GBP READS (S) =4601 ASYNC GBP READS =1181076 PAGES MOVED INTO SIMULATED BUFFER POOL =53668641 TOTAL AVOIDABLE SYNC I/O DELAY =35321543 MILLISECONDSD isplay output from DIS BPOOL (BP1)

7 DETAIL or statistics trace class 1, 2 for the measurement interval Sync I/Os/ interval = avoidable sync I/O per second (25463982 + 81181)/360 = 70958 I/O per sec Customer Example : Avoidable I/O by Member01000200030004000500060007000DP30D P31DP32DP33DP34DP35DP36DP37DP38DP39DP3 ADP3 BAvoidable Sync I/O per sec 1X2X5X10X20X40 XSystem z Synergy Features 12zHyperWriteLog Write Response Improvement with PPRC zHyperWritefunction for DB2, z/OS and DS8870 with GDPS or TPC-R HyperSwap Response time reduced up to 58% Benefit percentage varies with distanceRequirements z/OS and DFSMS APARs DB2 10 and DB2 11 SPE APAR PI25747 Set ZPARM (REMOTE_COPY_SW_ACCEL)

8 IBM DS8870 Storage Subsystem MCL -DS8870 242x model 961DB2 ResponseReduction with zHyperWrite050100150200250300350400450 ElapsedCommit Waittime in secHyperWrite Performance in Data Sharing / Multi Threads Base PPRCH yperWrite-22%-40% DB2 concurrent insert batch jobs (commit every 10 insert) in data sharing shows 40% reduction in commit response time and 22% elapsed time reduction Additional CPU in master SRB for triggering additional I/O request DB2 11 zIIP eligible z13 Simultaneous Multithreading (SMT) SMT-2 allows instructions from two threads to execute on zIIP or IFL core Designed to deliver overall capacity (throughput)

9 Improvement Capacity gain per core will vary z/OS team s benchmarks observe 10 to 40% capacity increase With SMT, each threads run slower but more tasks can be dispatched Available for tasks running under zIIPs System parmMT_ZIIP_MODE=1|2 active threads per online zIIPcore 158050 SMT-2 Eligible = DB2 zIIPE ligible DB2 DRDA DB2 Query parallelism (parallel tasks) DB2 Utilities (selective phases and sort) DB2 System tasks running under enclaved SRBs DB2 10 (prefetch reads and deferred writes) DB2 11 (most of asyncSRB works including in DBM1 and MSTR address spaces) DB2 XML parsing JCC T2 Application (JVM)

10 16 SMT-2 shows throughput improvement of up to 20% with DRDA workload compared to SMT-1 on z13 No elapsed time impact observed in DRDA/OLTP workload zIIPcapacity increase General CPU time reduction when there are not enough zIIPcapacity IIPCP > 0 Benefits likely depend on the type of workloads and configuration With C4Z4, zIIP Capacity factor was DRDA Workload (IBM Brokerage OLTP) (Controlled measurement environment, results may vary)Transaction CPU time (ms) Throughput (ITR trx/sec) Shared Memory Communications-Direct Memory Access (SMC-D)over Internal Shared Memory (ISM) zEC12 introduced Shared Memory Communications over RDMA (SMC-R) Using RoCEexpress feature z/OS LPAR to z/OS LPAR, could be across CECs Z13 GA2 introduces Shared Memory Communications-Direct Memory Access (SMC-D) Direct LPAR to LPAR communications within CEC Very similar to SMC-R (over RoCE)