1 / 32

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

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

paniz
Download Presentation

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

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 Connectivity @ KBC v1.3 15/2/2014 Jeroen Meeussen System Engineer DB2 on z/OS & LUW

  2. 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

  3. Content • IBM DB2 Analytics Accelerator (IDAA) • Connections • Infrastructure • connectivity issues • Connectivity tests • Q & A

  4. Access channels DB2 z/OS @ KBC

  5. 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

  6. 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

  7. 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 ?

  8. 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 ?

  9. 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)

  10. Connection pooling

  11. Connection concentrator

  12. 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

  13. 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 !

  14. 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

  15. DB2 Connect: Whatif DRP ? Normal situation

  16. 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

  17. 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’

  18. 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

  19. DB2 Connect: challenges “DB2 connectchallenges” as a title Jan ? We’rerunning smooth last 4 years, we have no challangesanymore, untilyourannouncement of this GSE …

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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!

  25. 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

  26. 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 !

  27. 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%)

  28. IDAA: Connections More info: GSE dec 2013 Presentation NamikHrle

  29. IDAA: infrastructure Question to the audience: Do you have a IDAA running or is your company investigatingthistechnology ? We’relookingforexchangingapplicationexperience.

  30. 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

  31. 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

  32. Speak up now or besilentforever, or just mail me  jeroen.meeussen@kbc.be

More Related