Servizio Monitoraggio e Capacity - epvtech.com

EPV User Group 2016 Roma 29 settembre

CONSORZIO OPERATIVO GRUPPO MPS

Roberto Gioi

Servizio Monitoraggio e Capacity

EPV for DB2 in MPS

2 © 2016 Montepaschi. All rights reserved.

Some history

2012 – Production 2 CEC 11 LPARs

36 DB2 subsys


36 DB2 subsys

2014 – Production 1 CEC 5 LPARS

28 DB2 subsys


36 DB2 subsys


28 DB2 subsys


20 DB2 subsys Add 6 DB2 subsys in SAP-dedicated LPAR (2014)

Mainframe Consolidation

We don’t mention Test environment ( DevOps project still on-going )


…and in MPS ?

EPV for DB2 as natural extension of

EPV for z/OS

Initially intended to be used by DBA’s

Eventually adopted by Capacity Mgmt.

& Performance Optimisation

System programmers: - daily check: exceptions, critical events, configuration changes - periodical checks: resources, EDM pools, BP/GBP pools, correspondent Daily Trends Performance analysts: System AS, Workloads, Daily, Weekly and Monthly Trends DBA: Workloads, User plans and packages, Accounting reports (optional DB2PM-like reports in HTML pages)

Why EPV for DB2 ? Great support to many IT figures


We won’t deal with usual DBA topics like BPs, Locks and so on…

We’ll go over some real life situations we came

across

…and see how EPV for DB2 helped in

understanding and solving the issues


Critical Events Overview page: you better start from here!


A closer look into System Address Spaces

Why ?



Migration from DB2 v.10 to

DB2 v.11 CM



What’s that spike ?

Geolocation SW deployed


A closer look into System Address Spaces leads to…

Migration from DB2 v.10 to

DB2 v.11 CM

Geolocation SW deployed



REALSTORAGE_MANAGEMENT in macro DSN6SPRM The REALSTORAGE_MANAGEMENT subsystem parameter specifies whether DB2® should manage real storage consumption. Acceptable values: ON, OFF, AUTO Default: AUTO DSNZPxxx: DSN6SPRM REALSTORAGE_MANAGEMENT ON DB2 always discards unused real storage frames. Discarding the frames results in some CPU overhead, and this option is intended for systems in which the availability of real storage is limited. This value would most likely be appropriate for LPARs that have many DB2 subsystems, such as a development LPAR. OFF DB2 does not discard unused real storage frames until one of the following conditions is met: The LPAR had reached an auxiliary critical state. The total real and auxiliary storage has reached 80% of the value of the REALSTORAGE_MAX subsystem parameter. AUTO DB2 discards unused real storage frames when a significant amount of paging activity is detected. By discarding frames, DB2 tries to bring the system to a point where paging is limited or nonexistent. However, it might not be possible to bring the system to that point if other applications on the same LPAR cause the shortage of real storage frames. AUTO is the default value.

…leads to DSNZPARM

Spatial function responsible Two PTFs: UI36156 UI37237


A closer look into System Address Spaces leads to…

MSTR average CPU before 670 mips MSTR average CPU after 210 mips

-68%

CPU savings


Concentrate Statements With Literals (CSWL)

Starting from DB2 10 in NFM, more SQL statements can be reused in the cache across users. Dynamic SQL statements can now be shared with an already cached dynamic SQL statement if the only difference between the two statements is literal values; when stored in the cache, literals are replaced with an ampersand ("&") that behaves similar to parameter markers. This avoids a full PREPARE and can provide a performance improvement similar to what is gained by coding SQL statements with parameter markers.


Concentrate Statements With Literals (CSWL)


…and what about QMF users ?

Alive and kicking!


…and what about QMF users ?

THANK YOU

EPV User Group 2016 Roma 29 settembre

QUESTIONS ?

Servizio Monitoraggio e Capacity - epvtech.com

Documents

Transcript of Servizio Monitoraggio e Capacity - epvtech.com