Distributed DB2 on z/OS ConceptsWhat is the DDF

Transcript Distributed DB2 on z/OS ConceptsWhat is the DDF

DDF in a Nutshell

Bill Arledge Consulting DB2 Product Manager BMC Software, Inc.

4/24/2020

Overview DB2 on z/OS Distributed Processing Intro DDF (Distributed Data Facility) Introduction Distributed Workflows WLM and enclave SRBs Performance and Tuning Considerations © Copyright 4/24/2020 BMC Software, Inc

Distributed DB2 on z/OS Concepts What is the DDF (Distributed Database Facility)?

DB2 on z/OS Component that supports connectivity with other databases across the network Implements the full DRDA Application Requester / Application Server function types

Application Requester Application Server DBMS Database Server DBMS

Is DB2’s transaction manager for distributed connections and workloads Supported network protocols SNA (VTAM) for DB2 on z/OS to DB2 on z/OS workloads, if desired TCP/IP for everything, recommended approach © Copyright 4/24/2020 BMC Software, Inc

DB2 on z/OS Architecture RRSAF BATCH IMS CICS ALLIED AGENTS AGENTS ALLIED AGENTS DSN1MSTR - System Services DSN1DBM1 -Data Access Services

SPAS

-Locking

IRLMPROC

-Locking DSN1DIST -Distributed Connections 3 DB2 address spaces and many allied agents in the beginning DB2 distributed database support and a new address space DB2 2.2 supported DB2 on MVS to DB2 on MVS only using private protocol DB2 2.3 introduced Remote Unit of Work Support via DRDA protocol (level 1) DB2 V3 introduced Distributed Unit of Work via DRDA protocol DB2 V4 implemented DRDA support of stored procedures, DBAT priorities, increased connections (25,000) DB2 V5 (DRDA level 3) supported TCP/IP, ODBC, CLI and JDBC © Copyright 4/24/2020 BMC Software, Inc

DB2 on z/OS Connection Options DB2 on other platform PRD1 DRDA DRDA DB2 on z/OS PRD1 DRDA

DB2 Connect ESE Data Server Drivers include:

• IBM Data Server Client • IBM Data Server Runtime Client • IBM Data Server Driver for JDBC and SQLJ • IBM Data Server Driver for ODBC and CLI

IBM Data Server Drivers

**** Other vendors provide distributed data access drivers 5

Distributed Fundamentals A Tale of Two Protocols

Private Protocol (PP)

First delivered in DB2 2.2

Uses 3 part names



Select C1,C2,C3 from DB2G.APPL1.TABLE1

Dynamic SQL only



No remote bind DB2 on z/OS to DB2 on z/OS only No stored procedure support DB2 V10 eliminates private protocol Specifying the protocol

•Default can be set at subsystem level using DBPROTCL DSNZPARM value (DRDA or PRIVATE)  With DB2 9.1 PRIVATE can no longer be specified •Can also be specified in the DBPROTOCOL parm of the BIND statement (D or P)  Only option in DB2 9

DRDA (Distributed Relational Database Architecture)

Introduced in DB2 2.3

Supports 3-part names and explicit CONNECT statements Dynamic and Static SQL



Remote bind capabilities Supports stored procedures Supports all RDBMS implemented using DRDA protocol Supports SNA and TCP/IP Is the strategic architecture for distributed

DB2 on z/OS Distributed Implementation A Word About Threads DB2 on z/OS PRD1 DSN1DIST -Distributed Connections Database Access Threads (DBATs) Service distributed workloads Implemented as an MVS WLM enclave running in preemptive SRBs originating in the DIST address space DBAT Types DBAT (Server) DBAT (Dist) © Copyright 4/24/2020 BMC Software, Inc DSN1DBM1 -Data Access Services RRSAF BATCH IMS CICS ALLIED AGENTS AGENTS ALLIED AGENTS Allied Threads Service local attachment facilities Run at the dispatching priority of the requesting application Allied Agent Types Allied Allied Dist (requester)

DDF Implementation DSNZPARM Considerations Specific DDF ZPARMs MAXDBAT – Max remote database access threads CONDBAT – Max remote connections DDF – DDF startup option CMTSTAT – DDF Threads IDTHTOIN – Idle Thread Timeout TCPKPALV – TCP/IP Keepalive POOLINAC – Pool Thread Timeout ACCUMACC and ACCUMUID EXTRAREQ/EXTRASRV – Extra Blocks REQ / SRV Application-related KEEPDYNAMIC(YES) / MAXKEEPD Specified on DSNTIPR and DSNTIP5 © Copyright 4/24/2020 BMC Software, Inc

DDF ZPARM Definitions

DDF ZPARM Definitions – page 2

Enabling Distributed Processing DDF Local Site Information Recorded in the BSDS (Boot Strap Data Set) DDF settings for each local subsystem are built during installation (DSNTIPR) Change Log Inventory utility can be used to modify these values post installation © Copyright 4/24/2020 BMC Software, Inc

DDF Security Considerations Security Mechanisms differ for DRDA and SNA environments DRDA supports authentication using SNA security mechanisms or DRDA mechanisms For DB2 on z/OS as a requester Both SNA and TCP/IP protocols support authentication  User ID only (already verified)   User ID/password User ID and PassTicket For TCP/IP with z/OS Integrated Cryptographic Service Facility  Encrypted user ID and encrypted security-sensitive data For DB2 on z/OS as a server Accepts either SAN or DRDA authentication mechanisms Supports the following authentification    User ID only (already verified) User ID/password User ID and PassTicket Communications database entries tell DB2 what to expect © Copyright 4/24/2020 BMC Software, Inc

DDF Security Considerations For DB2 on z/OS as a server Accepts either SNA or DRDA authentication mechanisms Supports the following authentification  User ID only (already verified)      User ID/password User ID and PassTicket Kerberos tickets Unencrypted user ID and encrypted password Encrypted user ID and encrypted password  User ID, password, and new password z/OS Integrated Cryptographic Service Facility supported authentication mechanisms  Combinations of encrypted user id, password, and security-sensitive data Communications Database entries tell DB2 what to expect © Copyright 4/24/2020 BMC Software, Inc

Enabling Distributed Processing The CDB – Communications Database CDB stored in DSNDB06.SYSDDF tablespace LOCATIONS IPNAMES IPLIST LUNAMES – LULIST LUMODES MODESELECT USERNAMES SYSIBM.LOCATIONS

• • One entry for every location LINKNAME establishes relationship with other CDB table entries SYSIBM.IPLIST

SYSIBM.IPNAMES

SYSIBM.LUNAMES

SYSIBM.LULIST

• Contains list of multiple IP addresses specified for a given location

TCP/IP side of the CDB

• Defines host names and outbound security to connect other systems using TCP/IP • • Identifies remote DB2s for access via VTAM Default row can be used to serve requests from any system not listed • Support AUTHID Translations SYSIBM.USERNAMES

VTAM side of the CDB 14

Communication Database Column Definitions SYSIBM.LOCATIONS

SYSIBM.LUNAMES

SYSIBM.IPNAMES

SYSIBM.USERNAMES

DBAT Processing Modes Mode is defined with the ZPARM CMTSTAT Very critical option for DDF that defines the flow of DBAT processing “DDF Threads” on panel DSNTIPR Two choices: INACTIVE – highly recommended  Provides DBAT pooling for DRDA access   More effective WLM classification per UOW Reduced Resource usage ACTIVE  DBAT created for each new client application   DBAT held through commits Use this only if the applications require it ZPARM Considerations CMTSTAT INACTIVE / ACTIVE

POOLINAC IDTHTOIN Used / not applicable Used / not recommended CONDBAT:MAXDBAT > / = *****CONDBAT should be much higher

DB2 Thread Pooling Driven by CMTSTAT INACTIVE in DSNZPARM As active DBATs are freed they remain in the pool and can be immediately reused Often called Type 2 Inactive Thread support Use the Thread Pooling terminology as it’s more accurate DB2 Connect ESE and JDBC Type 4 Driver also provide thread pooling functions that can (and often) should be used with DDF Thread Pooling

Inactive Connections Pooled DBATs Distributed Connections Benefits

•Reduces CPU required to create and

destroy DBATs

•Reduced number of DBATs decreases

real and virtual storage requirements

•Better scalability for distributed

connections Active DBATs Active Connections

DBAT Workflow CMTSTAT INACTIVE

Yes Reject

Resumed Connection

> CONDBAT?

Pooled DBAT Avail?

No Yes

New Connection

MAXDBAT Reached?

No Yes Queue Create DBAT

• • • • •

CHARACTERISTICS No end user “think time” included Enclave is created when the first SQL is received Enclave is deleted at commit/ rollback (thread complete) New enclave for each UOW, reclassified by WLM Use multi-period response time or velocity goals Reuse DBAT Reply “ready” to client Enclave / Class 1 Yes First SQL / UOW?

No Process SQL Pool DBAT / Inactv. Conn.

End enclave / Write Acctg Yes Commit / Rollback?

DBAT Workload CMTSTAT ACTIVE

New Connection

MAXDBAT Reached?

No Yes Queue Create DBAT Enclave / Class 1 Reply “ready” to client Process SQL Commit / Rollback?

No Yes

• • • • •

CHARACTERISTICS End User “think time” is included Enclave is created when DBAT is created Enclave is deleted at thread termination Only one enclave, no reclassification Can only use a single-period velocity goal Term. DBAT & Connection No Term Thread?

Yes End enclave / Write Acctg 19

WLM Enclaves WLM Enclaves are independent, dispatchable units-of-work that span multiple address spaces and can include combinations of SRBs and TCBs Concept of “Business Unit-of-Work” DB2 on z/OS uses enclaves for work coming through DDF Controlled by WLM Eligible for zIIP processors Thread priority set by WLM workload classification Providing good DDF classifications is vital No DDF workload classification defaults to SYSOTHER which means minimal service © Copyright 4/24/2020 BMC Software, Inc

Setting Service Classes for WLM-Managed Distributed Workloads © Copyright 4/24/2020 BMC Software, Inc

Additional WLM Workload Qualifiers © Copyright 4/24/2020 BMC Software, Inc

More Identifiers from non-z/OS Clients

Clients can flow other identifiers to DB2 for z/OS Values can be set by DDF threads via “Server Connect” and “Set Client” calls  ODBC/CLI/VB (SQLSetConnectionAttr)   Non-OBDC (sqleseti) JDBC (DB2Connection)  DRDA (ACCRDB prddta / sqlstt in EXCSQLSET) Most important IDs supported in V8 with special registers Workstation Client Accounting Workstation Userid

** WLM SPM 1-16 **

Workstation Name

** WLM SPM 17-34 **

Workstation Application

** WLM PC 1-32 **

Setting and Passing Client Identifiers java.sql.Connection.setClientInfo

Sets values for client info properties

Previous set methods are deprecated

recated) setClientInfo (3.5x.xx) DB2 z Special register

setDB2ClientUser setDB2ClientWorkstation ClientUser CURRENT CLIENT_USERID ClientHostname CURRENT CLIENT_WRKSTNNAME setDB2ClientApplicationInformation setDB2ClientAccountingInformation ApplicationName CURRENT CLIENT_APPLNAME ClientAccountingInformation CURRENT CLIENT_ACCTNG © Copyright 4/24/2020 BMC Software, Inc

Websphere Applications

WAS 6.1 - setClientInformation API can be used to pass client registers API is defined on the WSConnection class Part of the com.ibm.websphere.rsadapter package Values that can be passed include WSConnection.CLIENT_ID WSConnection.CLIENT_LOCATION WSConnection.CLIENT_APPLICATION_NAME WSConnection.CLIENT_ACCOUNTING_INFO  Client Information can be reset by calling method with a NULL parameter Trace Specification WAS.clientinfo=all to pass client information to the backend without tracing of client activities.

DBATs and Accounting

ACTIVE mode Only cut at thread termination, not at commit INACTIVE mode DRDA – at “clean” COMMIT or ROLLBACK  “Type 2 inactive” DRDA with KEEPDYNAMIC(YES)  At “clean” commit (DB2 V8 and above) PP DBAT – at commit or termination   At commit, if “Type 1 Inactive” (MAXTYPE1) allowed Else only at termination Active thread is idle too long and is canceled At “Idle Thread Timeout” (IDTHTOIN), if allowed  Checked every 2 minutes © Copyright 4/24/2020 BMC Software, Inc

Accounting and DDF Rollup

Option in DB2 V8 to reduce accounting volume Turned on if ZPARM ACCUMACC > 1 Just going to V8 the ACCUMACC parm defaults to 10 which means it’s on  You could see a big drop in SMF 101 records Data accumulated for specified # of threads For matching IDs, based on ACCUMUID Combination of the 3 workstation IDs   Total of 10 combinations in V8 7 new options in DB2 9 Accounting written when “Too old” (staleness threshold) “Too much” (internal storage threshold reached) “Just enough” (limit threshold reached) One accounting record reflects one or more threads Currently no DDF statistics (QLAC) or QMDA accounting Only one “ROLLUP” package Active thread data only shows the current thread counts © Copyright 4/24/2020 BMC Software, Inc

DBAT Thread Status

Assigned to a remote client (RA or RX)

Actively processing SQL or waiting for more Waiting for more work after “clean” commit, if:   INACTIVE mode – only: – KEEPDYNAMIC(YES) – all resources & DBAT kept ACTIVE mode – even after commit – All resources & DBAT kept until thread termination -

Pooled

(DA) DRDA clients only, with INACTIVE mode  Freed or newly created DBATs are pooled (also called ‘DBAT slots’) Available for reuse by any new / resumed request Still uses resources (esp. DBM1 storage) Still shown and counted as “active threads”   But connection name is “DISCONN” Can be terminated if not used (POOLINAC) © Copyright 4/24/2020 BMC Software, Inc

DISPLAY THREAD Command Output

Inactive Connections

Inactive connections (R2)

Associated with a remote requester Waiting for more work Speeds up response to additional SQL

Previously called Inactive Type 2 DBATs

Enclave Views

(MAINVIEW for z/OS)

Analyzing DDF Thread Data

The accounting data is the first source Still analyze other application considerations Elapsed and CPU times, I/O, SQL counts . .. But in addition: Elapsed time inside / outside the DB2 server Number of messages and blocks sent / received Batch reports summarized by The important DDF identifiers for your workloads © Copyright 4/24/2020 BMC Software, Inc

Thread Accounting – DBAT Specifics Summary Information

DBAT Accounting Metrics “Application” processing is occurring in the workstation client Class 2 – Class 1 CPU measures time not processing SQL in DB2  Includes parts of thread creation and termination and moving data from and to communication buffers zIIP Metrics Actual CPU time on the zIIP  Not included in “standard” class 1 & 2 CPU times CPU eligible for the zIIP © Copyright 4/24/2020 BMC Software, Inc Distributed Indicators/ Identifiers

Thread Accounting – DBAT Specifics DDF Summary

Tracing Distributed Workloads Additional focus on one workload Summary exception trace (accounting) Detail trace with important event IFCIDs All the usual qualifiers are available For DDF, important to reduce the data: Filter by requesting location Filter by Workstation ID(s)  In V9, DB2 also allows qualification by these IDs Exception Filters can be used to keep only threads that may need analysis (high In-DB2 elapsed, etc.) © Copyright 4/24/2020 BMC Software, Inc

Detail Traces Detail traces can include selected event groups Basic thread flow and SQL Also can choose to add scans, I/O, locks Another group to include specific DDF events The volume can be high Use it only when needed To understand the conversation flow Includes IFCIDs 157 – 163 IFCID 163, for example, traces events like  DBAT creation     Commit request from coordinator Backout request from coordinator DBAT creation queued Deallocation initiated Each event has a pop-up view with the IFCID details © Copyright 4/24/2020 BMC Software, Inc

DDF Statistics

The next place to look are the statistics Global statistics Critical DB2 subsystem tuning information Location statistics Application impact on DB2 and network  DRDA_Remote_Locs (combined)  Private Protocol locations (separate) DDF Address Space CPU usage TCB and SRB © Copyright 4/24/2020 BMC Software, Inc

Global DDF Statistics - STDISTD

Extended Reporting

Distributed workloads are often volatile

Less insight and control Can be useful to track activity over time

Store and query summary data in DB2 tables When needed, distributed traces and monitoring z/OS reporting on WLM can be helpful

Enclaves – SMF 30

Workloads by service class – SMF 72

Summary

SQL is SQL, but:

• Distributed Workloads are increasing at most DB2 shops • DDF processing has a unique set of characteristics that differ from traditional DB2 applications • New technologies (for DBAs) like WLM © Copyright 4/24/2020 BMC Software, Inc