ubi’s way to efficiency by db2 for z/os ‘clones' consolidation

DB2 USERS GROUP ITALIAMilan – Rome | March 2018


UBI’s Way to Efficiency

by DB2 for z/OS ‘Clones' Consolidation

Mauro Contessa

UBI Banca S.p.A.

Milan - Tuesday, 13 March 2018

Rome - Wednesday, 14 March 2018

1


Agenda

� UBI group and UBI.SS

� Single Bank Project Drivers

� How we did it : Project Steps

� Go live and results

� Managing historical data with Db2 Analytics Accelerator


Who are we?

4

UBI SYSTEMS AND SERVICES

UBI Systems and Services Scpa., headed by UBI Banca

Holding is the single provider of services to the group in the

areas of ICT, Back Office, logistics, purchasing and

general services.

�Number of employees and external collaborators

working for UBI Systems and Services: approx. 1800.

� It manages the design and the creation of IT

applications and operational processes to

�supply high quality services at competitive costs

�provide technology innovation to the advantage of

UBI Banca Group’s clients


OCT 2016

Group ProfileData at February 2017 -

project end of date

4th largest Italian

commercial banking

Group in terms of market

capitalization

>5% Market share

1,447 branches mainly

concentrated in the

wealthiest areas of the

country

over 17,000 staff

listed on the Milan Stock

Exchange and included in

the FTSE/MIB index.


Agenda







Why consolidating? Context and Drivers

7

• UBI group decided to transform its own

corporate, organizational and technical to a

model more centralized (Single Bank) to be

able to gain a stronger and more distinctive

go to market approach, to streamline the

achievement of Business objectives, simplifing

its IT infrastructure

• The target model is based on the adoption of

the Clone used for the instance of UBI Banca

as a target platform for all the 8 clones for the

brand banks

Improved Effectiveness and

Cost Reduction:

• “Single Bank" Project fully

executed 4 months in advance

compared to Business Plan

expectations

• I Wave ( Oct 2016)

• II Wave (Feb 2017)

• ~70M€ of cost savings expected in

2017 linked to the Single Bank

project


Agenda







• Preliminary Analysis

• Actions (pre-post definitions )

• Big Bang (Go live)

• Problems met and related solutions (Go live)

9

How we did it : Steps


• Assessment – Joint Activity UBI.SS and IBM teams

• DB2 Subsystem

• Active and Archive LOG rate

• Buffer Pool efficency

• Lock suspension

• DSNZPARM parameters• DDF thread rate measurement

• CICS thread rate measurement

• CICS subsystem management

• MQ subsystem management

• BATCH environment 10

How we did it: Preliminary Analysis


• Data has been collected in June 2016

• DB2 measures

• Statistic Records for all DB2 source clones (8 subsystems)

• Accounting Records for DDF / CICS for all DB2 source clones (8 subsystems)

• Main indicators used

• Transaction Rate, hourly average transactions per second ( CICS and DDF)

• Active Log Rate

• Daily Archive Log

• Buffer Pool Hit Ratio

11

How we did it : DB2 subsystem – Assessment


• Transaction Rate includes local thread and DDF thread

• Local Transaction Rate

• The sum of CICS local transactions executed in all network banks is

about 800 transactions per second

• DDF Transaction Rate

• The sum of DDF jdbc thread executed in all network banks is about

1200 transactions per second

• As a whole in UBI Group the total rate is about 2000 tran per second

12

How we did it : DB2 subsystems – Measurements Transaction rate


13

How we did it : DB2 subsystems – Measurements Log activity

• LOG

• Peak value for active LOG records writing is in the night time during the

batch phase . Average rate during this phase is about 11 MBytes/s with

peaks at 50 Mbytes/s � IBM DS 8000 it’s no longer a problem.

• Archive LOG number per day , our Active LOG are of about 5600 cyls gets

up to 200.

• Since max log number defined for DB2 V11 is 93, we were not able to

have a full day for Active LOG and this is a problem.


How we did it : Major areas of improvement

• Protected threads and RELEASE(DEALLOCATE)• Performance tests and related measurements

• Implementation

• Buffer pool size optimization• Simulate and test increasing the size of existing BPOOLS

• Create additional BPOOLS for selected Tablespaces and Indexspaces

• Use 1M memory pages and page-fix

• Data Sharing: Online environment CICSPLEX and JDBC

• DB2 Subsystem – DSNZPARM customization

• Log management optimization

14


DB2 / CICS – Protected Threads & RELEASE(DEALLOCATE)Implementation

• Results of a PoC with two core business CICS applications to measure

CPU savings:

• Activating protected threads, the cpu saving is taken from SMF 110

records where the avg cpu time per transaction is at TCB L8 of CICS in

microsec. The cpu saving is more than 20% .

• Cpu saving possible if activating RELEASE(DEALLOCATE) would have not

been as relevant as for activating protected threads and is reported in the

DB2 accounting record.

15


16

DB2 / CICS – Protected Threads & RELEASE(DEALLOCATE)Test results on a transaction subset (see J.Campbell’s notes on L8 and Deallocate)

1 2

1 Protected Threads implemented 2 RELEASE(DEALLOCATE) implemented


Protected Threads & RELEASE(DEALLOCATE): John Campbell NotesThread Reuse / Protected threads

Anyway, how do you measure the cost of thread create and terminate or the avoidance of the cost of thread create and terminate?

Well, first of all, the cost of thread create and terminate and the avoidance thereof is not captured in the DB2 accounting traces. It's not reflected in the class 2 TCB time.

The cost of thread create and terminate may be recorded in the CICS accounting record, which is the SMF 110 record. It will be clocked against the L8 TCB time. With

an open transaction environment, CICS uses the L8 TCB to access DB2 no matter if the application is threadsafe or not.

However if you're not really in an OTE environment, then the CPU time for thread create and terminate will go as uncaptured time.

When you are using thread reuse in CICS and achieving that thread reuse and you're not using the release deallocate, then using thread storage contraction by setting

the DB2 system parameter CONTSTOR=YES is a recommended option to try and constrain the amount of DBM1 31-bit virtual storage.

RELEASE(DEALLOCATE)

Having achieved thread reuse, another option then to use would be to use the bind option RELEASE(DEALLOCATE).

Unless you are achieving thread reuse, then RELEASE(DEALLOCATE) versus RELEASE(COMMIT) is a mute point for discussion. You have to have the thread reuse first

before RELEASE(DEALLOCATE) starts to become a potential benefit.

So once you combine RELEASE(DEALLOCATE) with thread reuse, you have the potential for even more savings in terms of reducing CPU resource consumption. Because

what you're doing here is now avoiding the repetitive cost per transaction of allocating and freeing private and stack storage, EDM objects, and also parent L-locks. So

what you need to do here is investigate the selective use of the bind option RELEASE(DEALOCATE) for the high-volume packages associated with high-volume

transactions. But it's always a trade-off.

By having a persistent thread RELEASE(DEALLOCATE), activities such as bind, rebind, and DDL activity cannot break in. You also have to be concerned about increased

potential for virtual and real storage consumption. You most definitely do not want to use RELEASE(DEALLOCATE) as a one size fits all strategy. I also need to point out

that some locks are held beyond commit until thread termination. So these are things like mass delete locks where you have an SQL DELETE request without a WHERE

clause or a gross level lock is taken on behalf of the application by the application giving an SQL LOCK TABLE statement. Gross level locks are no longer a problem for lock

escalation. Even though a gross level lock may be taken through lock escalation, that gross level lock is deescalated at the next commit.

….. (omissis) …

Now if you want to measure or monitor the benefit of RELEASE(DEALLOCATE), then the savings or the benefits thereof are reflected in DB2 Accounting Class 2 TCB time.17


DB2/CICS Protected Threads applied in Single Bank Project

• Given the impressive results gotten, our strategy was to activate Protected

Threads for all the most relevant cics/db2 transactions of ours. We used the

DB2TRAN technique applying the protect thread parameters for

homogeneous groups of transactions instead of for single transactions.

• The strategy has been successful : the saving achieved on average global

cics consumption have been of 400 mips on about 6000 mips.

• Since the results with protected threads have been so weighty, we have not

yet activated the Release(Deallocate) bind option. New ZPARM v11

parameter “PKGREL_COMMIT” is setting = “YES”.

18


PLANZ

DB2/CICS Protected Threads applied in Single Bank Project

19

DB2TRAN 2

DB2TRAN 3

.

DB2TRAN n

DB2TRAN 1

2

DB2ENTRY x APPLICATION Y

1

4 5

TRAN 2

TRAN 3

.

TRAN n

TRAN 1

3

• We used the DB2TRAN

technique applying the protect

thread parameters for

homogeneous groups of

transactions (application)

instead of for single

transactions

A DB2TRAN has a one-to-one relationship with a TRAN

One DB2TRAN belongs to only one DB2ENTRY

One TRAN belongs to only one APPLICATION

Each DB2ENTRY refers to only one PLAN

Each APPLICATION refers to only one PLAN

1

4

3

2

5


20

DB2 BufferPool tuning: Strategy and Actions

• Took advantage of available memory

• Actions done, our strategy:

• Increased total BP size from 14 to 170 Gb

• Increased five times the size of the 2 major BPs after analysis of potential

benefits using new DB2 11 BP simulation function

• Defined four additonal major Bpools for critical TS and IX with reported

very high sync I/O rate

• Isolated low priority TS into an additional dedicated Bpool

• Used 1 Mb memory pages (LFAREA) and long-term page fixing


• BPool Tot. 8 Clones

• BP0 0,2 Gb Catalog

• BP1 7 Gb TS globali

• BP2 7 Gb IX globali

• BPool Total 14 Gb

21

How we did it : DB2 Subsystem – BufferPool definition

Before Consolidation After Consolidation

• BPool Tot 1 Clone

• BP0 0,2 Gb Catalog





• BP11/13/14 50 Gb TS special

• BP21/23/24 50 Gb IX special

• BP3 2 Gb low priority

• BPool Total 170 Gb


DB2 BPOOL Size increase and its benefits

22

BP1

BP2

BP1

BP2 BP11BP1

2.000.896

BP2

2.000.896

BP21

2.000.896

(specialized)

BP11

2.000.896

(specialized)

milliseconds

0

500,000

1,000,000

1,500,000

2,000,000

2,500,000

3,000,000

1.000

2.000

3.000

4.000

5.000

6.000

7.000

8.000

9.000

10.000

11.000

12.000

16/01/2017

18/01/2017

20/01/2017

22/01/2017

24/01/2017

26/01/2017

28/01/2017

30/01/2017

01/02/2017

03/02/2017

05/02/2017

07/02/2017

09/02/2017

DB2 TCB DB2 IO Elapsed Synch Read I/O Tor. Trx.

BP Size

increase

Transaction averages, CICS Only

Time interval: 10:00 – 13:00


Evolution and follow-on…

23

1.000

2.000

3.000

4.000

5.000

6.000

7.000

8.000

9.000

10.000

11.000

12.000

02/01/2…

09/01/2…

16/01/2…

23/01/2…

30/01/2…

06/02/2…

13/02/2…

20/02/2…

27/02/2…

06/03/2…

13/03/2…

20/03/2…

27/03/2…

03/04/2…

10/04/2…

17/04/2…

24/04/2…

01/05/2…

08/05/2…

15/05/2…

22/05/2…

29/05/2…

05/06/2…

12/06/2…

19/06/2…

26/06/2…

03/07/2…

10/07/2…

17/07/2…

24/07/2…

31/07/2…

07/08/2…

14/08/2…

21/08/2…

AVG DB2 TCB AVG DB2 IO Elapsed AVG SYNC_READ_IO

milliseconds

BP Size BP Size BP Size Big Bang

Transaction averages, CICS Only – Time interval: 10:00 – 13:00

8 Gb 170 Gb


24

Buffer Pool – Other actions: adjust VPSEQT

• All the BPs have VPSEQT set to 80% and Parallel version of this

threshold set to 50%.

• For BPs where there is significant random access we want the most

buffers possible

• In prime time interval we reducing VPSEQT to 50% and in night

interval setting VPSEQT to 80%.

• We changed the VPSEQT to those buffer pools where in prime time

the recidency time of the Sequential access is greater than recidency

time of the Random access.


25

Saving BPOOL pages � and cpu by Statement SQL optimization:

Name search “transparent” optimization & savings

• Name search basics• Usually based on intersecting name components pertaining to same PERS_ID

• Intersection usually done by Inner Joins

• ExampleSELECT * FROM PERSONS P

WHERE P.ALIVE = ‘Y’

AND P.ID IN

(SELECT ID

FROM NAME_TOKENS T1

, NAME_TOKENS T2

WHERE T1.TOKEN = ‘FERRARI’

AND T2.TOKEN = ‘MARIA’

AND T1.ID = T2.ID )

TOKEN ID

FERRARI P2

FERRARI P6

FERRARI Pm

Etc.

MARIA P1

MARIA P6

MARIA P13

MARIA Pn


26

Name search “transparent” optimization & savings

• ALTER TABLE NAME _TOKENS• ADD COLUMN ALIVE CHAR(1) NOT NULL WITH

DEFAULT

• Defined Index on Expressions • Index Key TRIM(TOKEN), ALIVE, ID

• Defined View VNAME_TOKENS AS SELECT ID, TRIM(TOKEN) AS TOKEN

FROM NAME_TOKENS

WHERE ALIVE = ‘Y’

• Replaced Alias with View name in queries

• Will use INSERT & UPDATE Triggers for

maintenance of ALIVE column

Results comparing 20 test

searches with 2 tokens and 14

with 3 tokens on 10% of the data

• CPU consumption dropped

60%

• Getpages dropped from 30K to

5K


27

How we did it : DB2 subsystem – Data sharing architecture

• Definition of single bank db2 as 2-ways Data Sharing group on 2

LPARs.

•The DS group is used by

• CICSPLEX in 90 10 proportion, which supports :• Web-bank application

• Branch application

• Other Core business applications

• DRDA protocol for JDBC applications in 50 50 proportion • Web-bank application

• Other Core business applications


How we did it : DB2 Subsystem – DSNZPARM customization

• CTHREAD

• CTHREAD the value has been increased to support the increased workload

• IDBACK batch parallelism has been increased to support the increase of

Partitioned TS with “DEGREE ANY” sql statement

• MAXDAT the values has been increased to support the increase in the

CONDBAT distributed workload

• OUTBUF has been increased to support log write in high workload situations

• PCLOSET/N reduced with the implementation of data sharing and alter

all TS with CLOSE=YES

28


• Active LOG on disk in dual copy, Archive LOG not catalogued on tape with 15

days retention

• About # 200 LOG archived daily, max # for Db2 subsystem V11 is 93: not

enough to cover a full day

29

How we did it : DB2 subsystem – LOG management

Before Consolidation

After Consolidation • Optimization of batch high write log, getting subsequent reduction log writing

activity

• ARCHIVE LOG change: from tape to disk catalogued on storage group and after 2

days migrated by CA-DMS on TAPE with 15 days retention


Agenda







• No relevant issues with nightly batch

• Generalized slowdown of OLTP transactions• Average elapsed time of relevant transactions > 1 minute

• Issues not found during stress tests

• DB2 for z/OS monitoring was key in diagnosing the issues

• High lock contention in some critical packages caused by

• old fashioned unique key management ( unique key updated using an

old technique )

• Read / update hot spots on relatively small tables

• Exponential increase of CPU consumption of queries using many BETWEEN

predicates and/or OR predicates of type “:h = ‘something’ ”31

20/02/2017: Big Bang issues �.


32

… and related solutions

• On-site team immediate actions to alleviate / fix issues

• Serialization of CICS transaction affected by critical lock contention

• (define n°transaction class cics ad hoc)

• Old fashioned unique key management

• Implemented use of SEQUENCEs

• Lock contention in some SELECT statement avoided, adding the “WITH

UR” clause, when compatible with application semantics

• High CPU consumption and high elapsed time by queries

• New indexes added

• REOPT hints defined for some queries with many BETWEEN predicates

• SQL statements of some other queries rewritten


33

20/02/2017: Big Bang issues ….

Package EXS02002, used by more than 20 Plans,

did read a single row from a table with FOR

UPDATE OF clause and update. The table was

dropped and a SEQUENCE used instead.

The other Packages, used by more than 50 Plans,

were experiencing lock contention in simple

SELECT statement. The issue was fixed by adding

a “WITH UR” clause to the query as potential

reading of of uncommitted changes was not and

issue from an application standpoint.

Top programs / plans by number of Timeouts (and Deadlocks) during prime time.

PROGRAM DEADLOCK TIMEOUT

EXS02002 170 428

EWR10005 0 81

EXS00021 12 69

CTS01009 2 67

Z3UCGE33 9 53

LKICN9AB 0 53

G2SAQ06 2 46

EWR95002 0 26

EXS90009 0 24


After fixing Big Bang issues...

34

• Some issues were fixed within a couple of days

• Implementing SEQUENCES for all involved packages took a couple of weeks

• By March 10, all serious issues were fixed

PROGRAM DEADLOCK TIMEOUT

EXS90100 0 5

CTS01009 2 4

Z3UCGE33 3 3

LQS33005 2 3

PNBAFE01 0 3

EMR00204 0 2

EWR61003 0 2

EWR10005 0 1

EXS02002 0 0

0.0

1.0

2.0

3.0

4.0

5.0

6.0

7.0

0.0

100.0

200.0

300.0

400.0

500.0

600.0

01/02/2017

03/02/2017

05/02/2017

07/02/2017

09/02/2017

11/02/2017

13/02/2017

15/02/2017

17/02/2017

19/02/2017

21/02/2017

23/02/2017

25/02/2017

27/02/2017

01/03/2017

03/03/2017

05/03/2017

07/03/2017

09/03/2017

11/03/2017

13/03/2017

15/03/2017

17/03/2017

19/03/2017

21/03/2017

23/03/2017

25/03/2017

27/03/2017

29/03/2017

31/03/2017

avg resp (msec)

avg cpu (msec)

Cics transactions trend


Single Bank Project: Final Mainframe Partition Layout

3535

LPAR1

• UBI Banca - primary

LPAR5

• Banks Historical environments

LPAR2

• UBI Banca - secondary

LPAR3• Quality Assurance

• Education

• Certification

• Systemistic

LPAR6

• Application Development

Legenda

• Production

Hystorical

Quality Assurance

Application Development

LPAR4

• IWBANK

• Product Companies System 2

zEC13-2964-704

6.040 Mips

740 MSU

4 CP

4 zIIP

2 ICF

2 IFL

3 Tb RAM

System 1

zEC13-2964-718

21579 Mips

2584 MSU

18 CP

10 zIIP

4 ICF

4 IFL

3 Tb RAM

Sysplex

CF

DB2 – RACF - MQ

CPU1 CPU2

External CF

2965 – 204 ICF

280 Gb RAM


UBISS Software Platform

#2

Banks Environment

CICS v5.2

Cicsplex 1

TOR/AOR

DB2 Data-sharing

v.11 CM

GBpool #

CSQ1 v71

sharing

CDC

v.10.1

ORACLE

#1 Multibank Holding

Environment CICS v5.2

Cicsplex 2

TOR/AOR

Intranet Internal Portal Internet

Web Bank

CTG - IPICCTG - IPIC

Accel*1 Accel*2

CICSPLEXCICSPLEX90% 10%

DB2

MEMBER-1

Bpool #

DB2

MEMBER-2

Bpool #

CSQ2 v71

sharing

BATCH

SCHEDULED

DB2 Connect V 10

JDBC, Native

Stored Procedure

Data at february 2017 – project end of date

Transaction types Avg Volumes per day

CICS Banking tran (branches + back office) 25.000.000

CICS Internet Banking tran 11.000.000

JDBC Internet banking threads 40.000.000

IDAA threads 40.000


Agenda







38

• Why?

• Law and Regulation

• Trend Analysis and Forecasting

• Better services for customer satisfaction and loyalty

• Applications which are currently benefitting from acceleration

• Current Account Transactions

• Bank Daily Journal Log

• “Portafoglio Clienti”

• “Liquidazione Contabile”

• DB2 and CICS Statistics and Accounting data

An innovative way of managing historical data


DB2

Table

39

Goal 24x7 without DB2 Analytics Accelerator

Tape (VTS)

Hot data

InquiryRequest

Inquiry/Report

Historical DataUser

Batch Data

Retrieval Process

>= 1 Day

REORG

DISCARD

Historical data


DB2

Table

40

Goal 24x7 with DB2 Analytics Accelerator

Hot data

&

Historical data

Inquiry

User

DB2 Analytics

Accelerator

Loader

Historical data

Db2 Analytics

Accelerator

Benefits:

• Online and Historical data always

available to the user

• Near zero latency for historical inqury

• Near zero CPU consumption for

historical inqury

• Delete the cost and risk associated

with older data retrieval process (e.g.

increased customer satisfaction)

Online (CICS and

JDBC) & Batch

Applications



UBI’s Way to Efficiency

by DB2 for z/OS ‘Clones' Consolidation

Mauro Contessa

UBI Banca S.p.A.

41

Milan - Tuesday, 13 March 2018

Rome - Wednesday, 14 March 2018

ubi’s way to efficiency by db2 for z/os ‘clones' consolidation

Documents