slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
v1.3 15/2/2014 Jeroen Meeussen System Engineer DB2 on z /OS & LUW PowerPoint Presentation
Download Presentation
v1.3 15/2/2014 Jeroen Meeussen System Engineer DB2 on z /OS & LUW

Loading in 2 Seconds...

play fullscreen
1 / 32

v1.3 15/2/2014 Jeroen Meeussen System Engineer DB2 on z /OS & LUW - PowerPoint PPT Presentation


  • 345 Views
  • Uploaded on

DB2 Connectivity @ KBC. v1.3 15/2/2014 Jeroen Meeussen System Engineer DB2 on z /OS & LUW. Content. Access channels DB2 z /OS @ KBC Naming our distributed users DB2 connect Communication flows Base rules Pooling/ concentrating mechanisme Most imporant tuning params

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'v1.3 15/2/2014 Jeroen Meeussen System Engineer DB2 on z /OS & LUW' - paniz


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

DB2 Connectivity @ KBC

v1.3

15/2/2014

Jeroen Meeussen

System Engineer DB2 on z/OS & LUW

content
Content
  • Access channels DB2 z/OS @ KBC
  • Namingourdistributed users
  • DB2 connect
    • Communication flows
    • Base rules
    • Pooling/concentrating mechanisme
    • Most imporanttuningparams
    • Specs servers/clients
    • Infrastructure DB2 connect gateways/servers
    • DRP of gateways
    • monitoring
    • Issues
    • Tests
content1
Content
  • IBM DB2 Analytics Accelerator (IDAA)
    • Connections
    • Infrastructure
    • connectivity issues
    • Connectivity tests
  • Q & A
naming our distributed users
Namingourdistributed users
  • end users via standard workstation tools (Office: excel, access, …)
  • Programming languages: fe. Perl, Visual Basic
  • Business Object
  • Software packages as Bars, Unica, Affinium, Loan IQ, Papyrus, Documentum, RAD/EGL developmenttool, IBM Data Studio …
  • MS .net development platform/applications
  • Transaction Application servers : Websphere/Tomcat

offices, KBC Online, ATMs, cash machines …

DB2 connect (servers)= RTO 1 : service for super-criticalapplications

naming our distributed users1
Namingourdistributed users
  • Allowall SQL (also changes)
  • Running stored procedures
  • Use of distributedconnections,
  • insteadof QMF, spufi.
  • Goal:
    • the zIIPusage (offload of the GP)
    • the IDAA accelerator possibilities
db2 connect communication flows
DB2 Connect: Communication flows

WLM weight %

Under control operations, not WLM auto mngt !

Usedto drain out lparforplanned changes

round robin 50%-50%

Every 5 sec: port listen ?

db2 connect rules
DB2 Connect: rules

Basic rulefor new applications: connect via DB2 connect gateway

  • odbc or jdbc type 2 connection
  • easier management (fe. changing a location or db2 subsystem name)
  • more control towards dist access: gradually block distributed access (DDOS)
  • No needtodistributelicencestoclients
  • Connection pooling/concentrator

Except: ‘transactions’ via WAS or tomcat

  • jdbc type 4 connections
  • developedown pooling mechanisme (notcontrolledbyour team)
  • Performance, in the time when the animalsspoke…
  • Driver/licence part of application: responsiblefor upgrading ?
connection principles
Connection principles

DB2 Connect Server

Gateway

Client

DB2 z/OS

Any distributed access to DB2 requires:

- a DDF connection (CONDBAT)

- a DB2 DBAT thread(MAXDBAT)

most imporant tuning params
Most imporanttuningparams
  • DB2 connect server:
    • max_coordagents, max_connections
    • # cores/cpu’s (> 1 if > 100 concurrent users)

db2 get dbmcfg

Priority of agents (AGENTPRI) = SYSTEM

Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)

Initial number of agents in pool (NUM_INITAGENTS) = 0

Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)

Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAG

ENTS)

Keep fenced process (KEEPFENCED) = YES

Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)

Pooling, no concentrating

most imporant tuning params1
Most imporanttuningparams
  • DB2 z/OS: DSNZPARMS

maxdbat, condbat

  • z/OS: WLM settings= very important for DB2 performance!

 DIST STC , PRIORITY as high as MSTR and DBM1

 DDF default classificationrule= discretionary= second lowestavailablepriority !

db2 connect specs servers clients
DB2 Connect: Specs servers/clients
  • DB2 connect gateways/servers
      • Before 2009: Sun Solaris ‘expensive, depricated’ HW
      • Internalstudystability/TCO : LoZ, Linux on VMWare, Windows on VMWare
      • After 2009: Windows 2008 SP2, VMWare x86 architecture
      • Now: DB2 Connect Unlimited Enterprise Edition v10 fp3
  • DB2 connectclients
      • Windows: 2003, 2008, 2012, Vista, Windows 7
      • Unix: Solaris (depricated), HP-UX
      • Linux: Red Hat (standard linuxdistribution in KBC)
      • 32 or 64-bit
db2 connect what if drp1
DB2 Connect: Whatif DRP ?

HA/DRP situation (fase 1)

  • load balancerwillsendalltrafictoproduktieserver2 (= scaledto do so) (max. 4 uur)
  • no data recovery time : all the DB2 z/OS systems are defined on all DB2 connectservers
db2 connect what if drp2
DB2 Connect: Whatif DRP ?

HA/DRP situation (fase 2)

  • start PRODUKTIE SERVER 1’ in DRP mode
  • rerout the DNS alias of the initial PRODUKTIE SERVER 1 toPRODUKTIE SERVER 1’
db2 connect on demand monitorring script
DB2 Connect : on demand monitorring script

Question to the audience:

How do you monitor the E2E DB2

distributedconnectivity ?

  • Also
  • z/OS: othersources as SMF, SQL monitor, system monitor…
  • Windows: basic checks: service, memory, cpu, disk space, VM-ware tools
db2 connect challenges
DB2 Connect: challenges

“DB2 connectchallenges” as a title Jan ?

We’rerunning smooth last 4 years, we have no challangesanymore,

untilyourannouncement of this GSE …

db2 connect challenges1
DB2 Connect: challenges

16.08.29 STC32933 DSNL074I -DPC1 DSNLILNR NUMBER OF CLIENT CONNECTIONS 041

041 HAS EXCEEDED 80% OF THE MAXIMUM ALLOWED

16.08.46 STC32933 DSNL074I -DPC1 DSNLILNR NUMBER OF CLIENT CONNECTIONS 263

263 HAS EXCEEDED 90% OF THE MAXIMUM ALLOWED

16.09.00 STC32933 DSNT500I -DPC1 DSNGEPLC RESOURCE UNAVAILABLE 606

606 REASON 00C900D9

606 TYPE 00000606

606 NAME EDM RUNTIME BELOW LIMIT

16.10.20 STC32933 DSNT500I -DPC1 DSNGEPLC RESOURCE UNAVAILABLE 393

393 REASON 00C900D9

393 TYPE 00000606

393 NAME EDM RUNTIME BELOW LIMIT

16.12.38 STC32933 DSNT376I -DPC1 PLAN=DISTSERV WITH 698

698 CORRELATION-ID=db2jcc_appli

698 CONNECTION-ID=SERVER

698 LUW-ID=GA406588.DEB8.CCC60D2A75AF=31232

698

698 THREAD-INFO=JD34563:s089811u.servers.k:JD34563:db2jcc_applica

698 AMIC:951080:*:*

698 IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DIST

698 WITH

698 CORRELATION-ID=db2jcc_appli

698 CONNECTION-ID=SERVER

698 LUW-ID=GA4065B5.E1A5.CCC60D2A4033=31225

698

698 THREAD-INFO=JD34563:s0898447.servers.k:JD34563:db2jcc_applica

698 AMIC:951080:*:*

698 ON MEMBER DPC1

27/2:

Our major DB2 production system refusedany new distributedconnection !!

What’shappened ?

Space extendingproblemwith a UTS (PBG)

The WAS transaction server (straight throughconnection) didn’t handling the sql error correctlyandstartedmassivesessionsto DB2.

Normally 100 concurrent, now 1800 (over 2 DS DB2 systems)

 MAXDBAT reached…

For solving the space issue, we need the object exclusive (reorg)

 All the WAS connectors must been stopped

db2 connect challenges2
DB2 Connect: challenges

12.16.36 STC31515 DSNB209I -DPC1 DSNB1CHK - BUFFER MANAGER 763

763 TABLESPACE/INDEXSPACE

763 CLEANUP ROUTINE (DSNB1CFC)

763 HAS BEEN SCHEDULED FOR

763 DBNAME=CBDOIP01

763 SPACENAME=CBSX1225

763 INSTANCE=1

763 PSID=X'01E400B1'

12.31.17 STC31515 DSNT376I -DPC1 PLAN=DISTSERV WITH 474

474 CORRELATION-ID=db2jcc_appli

474 CONNECTION-ID=SERVER

474 LUW-ID=GAA01911.O3EE.CCD7773D25A2=53675

474

474 THREAD-INFO=CDOCDBP:s0025410.servers.k:CDOCDBP:db2jcc_applica

474 AMIC:559863:*:*

474 IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=

474 WITH

474 CORRELATION-ID=010.CHKCFC01

474 CONNECTION-ID=DPC1

474 LUW-ID=BEBBCM01.A3DPC1.CCD774E755B6

474 THREAD-INFO=SYSOPR:*:*:*:*:* :*:*

474 ON MEMBER DPC1

13.07.00 STC31515 DSNB219I -DPC1 DSNB1CFC - BUFFER MANAGER 644

644 TABLESPACE/INDEXSPACE

644 CLEANUP PROCESS HAS

644 BEEN COMPLETED FOR

644 DBNAME=CBDOIP01

644 SPACENAME=CBSX1225

644 INSTANCE=1

12/3 – 14/3 – 17/3 until 26/3:

Distributed connectionshanging !!

What’shappened ?

We stilldon’tknow.

At the same time: IMS connectsabended + DB2 connects are losingconnections

Resultfor DB2:

sessions are hanging (active in netw) withlocks on LOB TS

 new sessionscan’t get the lock on TS

DB2 had problemswriting data from TB andscheduledcleanup

 DB2neededexclusivelocktocleanup

DB2 cleanup process :

when unable to write page

from BP to DASD after 10

checkpoints:

DB2 drains object to flush BP

db2 connect challenges3
DB2 Connect: challenges

Instead of waiting 2 hours (IP KEEPALIVE) to get rid of locks, clean up yourself:

  • cancel ddf thread(xxx) is notworking, status ‘threadsactive in network’
  • Display thread(*) luwid(xxxxx) detail
  • Stop tcpipsession (IP, port, socket=remote port) via tool or commands

13.04.19 STC31515 DSNL022I -DPC1 DDF THREAD '121177' WAS PREVIOUSLY CANCELLED

DSNV402I -DPC1 ACTIVE THREADS -

NAME ST A REQ ID AUTHID PLAN ASID TOKEN

SERVER RA * 6590 db2jcc_appli XXXXXXX DISTSERV 00BC 114474

V437-WORKSTATION=s08988ky, XXXXXXXX

APPLICATION NAME=db2jcc_application

V441-ACCOUNTING=JCC03630s08988ky

V482-WLM-INFO=JBAT510:1:5:13

V445-GAA0199E.BEBB.CCDEC69F921A=114474 ACCESSING DATA FOR

( 1)::10.160.25.158

V447--INDEX SESSID A ST TIME

V448--( 1) 40215:48827 W R2 1407708031454

Port:socket

db2 connect challenges4
DB2 Connect: challenges

LPL problems

  • Of course, just start the object
  • Took 5 hoursto complete:
    • ‘-dis use’ or ‘-display thread(*) type(system)’ : DSNRST00 system thread

Token 0= can’t cancel thread !

    • Is ithanging: ‘-dis archive’  progression
db2 connect issues
DB2 Connect : Issues
  • We are System Engineers DB2 z/OS,

andnot Unix, Linux, Windows experts 

    • Workshops
    • ‘Massive’ Knowledge collection document
    • Automatedscripting
  • Keeping software up to date (# of clients) for:
    • Latest features fe. for IDAA
    • Fixes
    • Out of support dates
  • Be aware: licence matrix could change when upgrading version

andyouacceptedduringinstallation!

db2 connect issues1
DB2 Connect : Issues
  • Whatifproblemoccurs/tracebility :
    • Trace db2 connectclient
    • Trace db2 connect servers
    • DRDA trace system z

→ timeconsuming,

→almostimpossiblewhen no simulation

  • minor ones:
    • Out of memory MSQuery/Excel
    • Point as decimalsign, depending on regionsconfig workstation: price Insurance x 1000 !
    • Names of the DB2 connecteditions are changingeveryversion
db2 connect issues2
DB2 Connect : Issues
  • Whenusingautomated scripts / refreshes in excelwithhardcoded user/password
  • When user password is changed (RACF rules)
  • don’tforgetto change program !!
  • After 3 tries: user revoked in RACF !
db2 connect connectivity tests
DB2 Connect : connectivity tests
  • Daily scheduled E2E test scripts fromclient:
    • In Perl
    • Up and running db2 connect gateways ?
    • Load balancerworking ?
    • DB2 on z/OS running ?
  • After upgrade/change db2 connectclient software or application software:
    • Scripts thatconnecttoalldefined databases
    • Java program whichcounts the connection spread (fe 40% - 60%)
idaa connections
IDAA: Connections

More info: GSE dec 2013

Presentation NamikHrle

idaa infrastructure
IDAA: infrastructure

Question to the audience:

Do you have a IDAA running or is your company

investigatingthistechnology ? We’relookingforexchangingapplicationexperience.

idaa connectivity issues
IDAA: connectivity issues
  • First exploiter in ourdatacenter :
    • 10 GbOSA’s (onlyfor IDAA), fibres (not 1 Gb)
    • Large IP packages (jumbo frames size)
  • During start DB2, IDAA is automatic started, but DNS servers are notreachableforinternal DB2 DNS name (only 1 sec later :-)

 set up internalz/OS DNS routing table

  • When M1 (production) down: no connectionto IDAA anymore:
    • no acceleration : beware of extra CPU load
    • desynchronisation data in DB2 <-> IDAA
  • No automated monitoring from IBM, no automatic dialout or call home function, only events via DSNX8* messages in DB2 log
idaa connectivity tests
IDAA: connectivity tests
  • IVP: afterevery DB2 start:

call SP SYSPROC.ACCEL_TEST_CONNECTION

    • connectivitytest
    • speedtest
  • Every 15 min: display accel(*) detail
    • If no response  automatic incident + escalation
    • Interprete output: queuedqueries, max % space, status
    • WHY: when IDAA loosesconnectionwith system z
      • no DSNX * messagesanymore!!
      • no monitoring
slide32

Speak up now or besilentforever,

or just mail me 

jeroen.meeussen@kbc.be