db2 update day 2015 managing db2 with ibm db2 tools svenn aage

© 2015 IBM Corporation

zAnalytics DB2 Update Day 2015 – March 23-27, 2015

1

DB2 Update Day 2015

Svenn-Aage SønderskovSystems ProgrammerJN Data A/S, [email protected]



2

Managing DB2 with IBM DB2 Tools



3

Agenda

�Who is JN Data ?

�Setting the scene – JN Data Consolidation

–How did we do it ?

�Focus on User productivity

–Moving the ”goodies” into the product

�Questions



4

JN Data – Where are we located

Silkeborgdatacenter

RoskildeKompetencecenter



5

Numbers for thought

We give our customers competitive power through a safe, stable and efficient IT

platform

We give our customers competitive power through a secure, stable and efficient IT

platform

• JN Data's customers have a market share of approximately 35% of the total banking market in terms of balance-sheet total

• More than 200 banks use JN Data's infrastructure

• JN Data's customers have a market share of more than 50% of the total mortgage-credit market

• +40,000 computers are connected to JN Data's network. This means that approx. 42% of the employees in the financial sector use systems which are operated by JN Data

• JN Data administer approx. 10 Petabyte data.

• The operation and Infrastructure support the work of +2000 developers

• 4 Systemic risk Financial Service Institutes run on the infrastructure and so does the Danish National bank (Clearing and RTGS systems)

• The Danish Financial Service Authority have concluded that JN Data operates one of the absolute most important datacenters in the country.



6

8 of 10 Service Prices are lower with the Reference Group average and 2 are slightly higher



7

8 of 10 Service Prices are within the “33% percentile”

On 8 out of 10 areas JN Data outperforms the best comparison companieser.

91,5 % af cost in scope is within the best third of the groups. JN Data’s official goal is 80%.



8

Why Consolidation ?

� Many vendors/products - Reduction needed

� Part of cutting cost by 3. digit million DKK p.a.

� We had to keep the same or better service/performance

� We had to move many years of experience from one set of products to the chosen ones.

� We had to do it with 1/3 of the previous resources due to manpower consolidation.



9

The Job

� Streamlining the DBA-tools Suite for Change, Edit, Catalog and Application Tuning.

� We had to reduce the number of vendors from 3 to 1 and products accordingly.

� We had to do it within 12 Months from decision to end of contract for the first product line.

� We had to make a significant cost reduction for JN Data’s Partners.



10

What did we do at Analyze time?

� We had to be very fact-based

– Can the vendor solve the task both on a Short and Long Term basis ?

– Can the products do the job ?

– Can the products support the current and new releases of the base DB2 software ?

– Setting a multi-question and answer was the chosen approach.



11

Decisions on the way

� DB2 Change Management is a critical service due to constraints like 24 by 7, Data volumes

etc.

� We have 2 implementations for doing Change Management.

– User-driven where the user request production turnover

– DBA driven, where the DBA’s do the production Turnover.

� Both in house developed frameworks building on top of the vendor product lines – Kept for

future use

– We might have to Re-Choose

� We chose to maintain both with the chosen product line as engine. This offers us the ability

to change to another product line without affecting our users.

� We Chose to go for the Change Management Method



12

We analyzed the Solutions

� We evaluated 3 vendors solutions

– Could they support the current and future DB2 Features ?

– Could they be embedded in our home-grown solutions and be operated in Batch?

– Could they solve a given number of specified tasks – Defined by all participants on a

“NEED TO HAVE” / “NICE TO HAVE” basis?

– Could they support the new features of DB2 10 (that was the one we started at).

– We summarized what each single JN Data Partner would gain/loose from the

consolidation and what effort they would need to be put into the transformation.



13

What if ?

� If we did not consolidate

– Would we pay for 3 solutions on a big MIPS footprint (Money)?

– We would have 3 contracts instead of one

– We would have to Maintain 3 solutions

– If one productline did not support the new releases of DB2 – should we wait until all 3 did

before upgrading to the new release of DB2?

– We decided to do consolidation even if one of the other product lines should turn out to

be better.



14

What did we do with Change ?

� Change - Built the chosen products into our home-grown framework – This was the

time/resource consuming part.

– Seen from the user the task performed has to behave as before.

– DDL Files in – Change/validation Rules Objectt Comparison – Worklist out

– We feel that we are ready for future choices ☺

� Catalog/Edit/Application Tuning

– This was the easy part.

– Consolidation on the chosen product-line, Education (New commands had to be

learned)

– Converted our homegrown commands from the previous solutions into the new toolset

to ensure productivity at the user-level and thus upgrading all our partners with all the

goodies.



15

DB2 Administration Tool

� Basically DB2 Administration Tool is a standard ISPF application, which like the others can

help administering DB2 on System z.

� DB2 Administration Tool can navigate the catalog and do most of the tasks needed

� The setup can be a little challenge when operating on many clients with different setups and

many DB2 members and Datasharing Groups therefore we have had to make some new

things:

– Init REXX – Initialisation of dialogues variables, Libraries etc

– We have had to make changes to the behaviour on Templates and some of the

dialogues (Tables, Packages, Primary Commands) to get our need for behaviour

supported



16

Extentions implemented on CMDS

� Commands

– <JN> JN Data Homegrown Commands

– DROPALL ISPF SELECT CMD(%JNADBDRA &CPANEL &ÅTABLE)

– OID ISPF SELECT CMD(%JNADBOID)

– REBALL ISPF SELECT CMD(%JNADBRBA &CPANEL &ÅTABLE)

– FREEALL ISPF SELECT CMD(%JNADBFRA &CPANEL &ÅTABLE)

– GETSORT ISPF SELECT CMD(%JNADBSRT)

– <IBM> System Commands

– QTAB ISPF SELECT CMD(%ADBEDIAG QUERYTAB)

– TOOLS ISPF SELECT CMD(%ADBEDIAG)

– SHOWPAN ISPF SELECT CMD(%ADBEDIAG SHOWPANEL)



17

Sample Rexx/* REXX sample REXX for the DROPALL function */ ARG CPANEL ÅTABLE If Wordpos(CPANEL,'ADB21S ADB21T ADB21VV ADB21X') = 0 then do

Say 'DROPALL is not supported in this panel ' Say 'Operation Aborted ' Exit

end Address ISPEXEC "VGET (DB2N) SHARED" if DB2N = 'DRDA' then do Say 'DROPALL is only possible Locally' Say 'Operation Aborted ' exit

end if cpanel = 'ADB21S' then do "TBSORT " ÅTABLE " FIELDS(TYPE,C,A,NAME,C,A)"

end "VPUT (CPANEL) SHARED" "SELECT CMD(%CMEDIT TABLE("ÅTABLE") SKELETON(JNADBDRA)" "VERASE (CPANEL) SHARED"

Exit



18

Sample CMEDIT

PROC 0 TABLE() TABLE02(NONAME) TABLE03(NONAME) TABLE04(NONAME) + TABLE05(NONAME) SKELETON() MACRO() STCLIST()

IF &STCLIST ^= &STR() THEN DO ISPEXEC SELECT CMD(%&STCLIST TABLE(&TABLE))

END IF &MACRO ^= &STR() THEN DO SET MACRO = MACRO(&MACRO.)

END IF &TABLE ^= &STR() THEN DO ISPEXEC TBQUERY &TABLE ROWNUM(ROWNUM)

END

ISPEXEC FTOPEN TEMP ISPEXEC FTINCL &SKELETON ISPEXEC FTCLOSE

ISPEXEC VGET (ZTEMPN) ISPEXEC LMINIT DATAID(DATAID) DDNAME(&ZTEMPN) ISPEXEC EDIT DATAID(&DATAID) &MACRO ISPEXEC LMFREE DATAID(&DATAID)



19

Sample Skeleton&ADBJ1

&ADBJ2

&ADBJ3

&ADBJ4

&ADBJ5

)TB 66

)SET CMDSG = &DB2SYS

)IM CMDSNTEP

)DOT &TABLE SCAN)SEL &CPANEL = ADB21S

DROP TABLESPACE &DBNAME..&NAME.;

)ENDSEL

)SEL &CPANEL = ADB21T

)SEL &TYPE NE A

DROP TABLE &CREATOR..&NAME.;

)ENDSEL

)SEL &TYPE = A

DROP ALIAS &CREATOR..&NAME.;

)ENDSEL

)ENDSEL

)SEL &CPANEL = ADB21VV

DROP VIEW &CREATOR..&NAME.;

)ENDSEL

)SEL &CPANEL = ADB21X

DROP INDEX &CREATOR..&NAME.;

)ENDSEL

COMMIT;

)ENDDOT

END



20

The Catalog Tool Extentions

� Commands directly in Panels (Maintenance)

– DIS Display in Table Panel

– ICS See Image Copy from table panel

– KCU Packages with Contoken/Last Used

� External Commands

– DUT Homegrown Adhoc Utility Engine

– DUS Homegrown Utility Automation

– UHIS, RTSX Homegrown RTS Solution

– UNL, UNLO, LOA, XLOAD – Unload/Load on homegrown versions.

� Other

– Suppression of Templates because we use SYSTEMPL as a good solution to keep

company defaults local

– DUT – Database Utility Tool



21

JN --------------------------- DB2 Utility Generator --------------------------

Option ===>

Database ==> XDB05411 Userid - TEKSAS

Tablespace ==> % Time - 20:05

Partition ==> Terminal - 3278A

Part level ==> Y (Y/N) Pf keys - 24

Job-divide ==> N (Y/N) Subsystem- DBX0

Recall ==> Y (Y/N) Catalog - SYSIBM

Spanned ==> Y (Y/N)

Where ==>

Option Option

CHD Check Data REC - RECover

CHI Check Index RECV - RECover Value (Advanced)

IC Image Copy With Template REP - REPort

IIC Incremental Image Copy with template REO - Reorg

ICE Image Copy with Template i own name RES - Display REStrict/RESolve

INI INItial Load - Load Dummy RIX - Rebuild IndeX

LOA LOAd tables from UNL-function RUI - Recover Without indexes

LRSN Calculate LRSN RUN - Runstats

QUI QUIesce STA - Start database

RCA ReCAll tapes from VTV STO - Stop Database

REBIND Rebind all packages including DB/TS UNL - UNLoad tables

UNLO - UNload and Generate LOad

SE - SEtup Session-variables

Press END to end DB2 Utility generator.



22

Useful ”hidden” Commands

� CAT Catalog commands (Hyperjump)

– CAT K BATCH.PROGRAM1

– CAT T Creator.Table

� PROMPT SEL ON – Shows the SQL

� SQL – Jumps to the SQL Editor or executes a given sql-statement

� LIKE ON/OFF (turn the wildvardsetting off/on)

� SAVE ON/OFF (turn remember on/of)

� B/BR Primary Command (uses BRIF interface to quickly show table content)

� WSL – Jumps to Work Statement List

� SSID <db2 subsys> change DB2 Subsystem

� VER ON/OFF i Package panel



23

Are We Happy ?



24

db2 update day 2015 managing db2 with ibm db2 tools svenn aage

Data & Analytics

jn data jn data

ibm db2 tools

areas jn data

20151db2 update day

jn datas partners

jn datas network

managing db2

platform jn datas customers