compuwarecorporation sccmg irvine, ca may 4th, 2007

CompuwareCorporation

SCCMG

Irvine, CA

May 4th, 2007


The Trilogy of DB2’s “Originating” Address Spaces – Mainframe DB2, DDF and Stored

Procedures

Thomas A. Halinski, Compuware Corporation

SCCMG – May 4th, 2007


DB2 SQL executes using three main DB2 address spaces, SSAS, DBAS and IRLM. To measure and see how it performs, we need to capture its activity from its “originating” address space. This varies depending on the source of the SQL: Mainframe, Distributed or Stored Procedures - making a trilogy. Let’s see what each is and how they work. With a good methodology we can “tune” any of them.

SCCMG – May 4th, 2007


The DB2 Trilogy

The three main ways DB2 SQL is initiated: • On the Mainframe via application programs• Originating off the Mainframe (Distributed System or

another Mainframe)• Via stored procedures


DB2 SQL originating on the MainframeSingle MVS system: • The MVS System• The database• Application code

• SQL

Terminal

DB2 Database

CICS

IMS DC

TSOOnline BatchApp'n App'n

TSO/ISPFQMF SPUFI

Mainframe DB2 Subsystem

Allied Address Spaces


DB2 SQL originating off the Mainframe/another Mainframe• An MVS platform linked to another MVS platform• Modern distributed environment linked to an MVS platform


DB2 SQL via stored procedures• An MVS platform linked to another MVS platform• Modern distributed environment linked to an MVS platform - Stored procedures called from on/off the Mainframe -


DB2 Address Spaces


Typical DB2 Address Spaces

Database Services Address Space

(DSNDBM1)


(DSNDBM1)

Systems Services Address Space

(DSNMSTR)


(DSNMSTR)

DB2DataDB2Data

Intersystem Resource Lock Manager(IRLMPROC)



Systems Services Address Space:

Responsible for attaching DB2 to other subsystems (CICS, TSO, IMS), and for logging activity (default address space name is DSNMSTR)


Database Services Address Space:

Responsible for the execution of SQL, and contains the core logic of the DBMS. DBAS consists of 3 pieces: Relational Data System (RDS), Data Manager (DM) and Buffer Manager (BM) (default address space name is DSNDBM1)

Responsible for locking support (default address space name is IRLMPROC)

Intersystem Resource Lock Manager Address Space:


Measuring DB2 SQL originating on the Mainframe


Measuring DB2 SQL originating on the Mainframe

CICS


(DBAS)


(DBAS)

IMS DC

TSO/ISPFQMF SPUFI


(DSNMSTR)


(DSNMSTR)

DB2DataDB2Data

Allied Address Spaces

Threads



Allied Address Space Measure the allied address space to view DB2 activity taking place on behalf of the SQL

Modules in the DB2 address spaces are typically executed in cross-memory mode by the allied address spaces

Execution of the DB2 modules is charged to the allied address space requesting the service

You can measure the DB2 system services address space, but you’ll see little activity

CICS

IMS DC


TSO/ISPFQMF SPUFI


Measuring DB2 SQL originating off the Mainframe/another Mainframe


DB2 SQL originating off the Mainframe/another Mainframe


DB2 SQL originating off the Mainframe/another Mainframe

Real world distributed application scenarios supported by DB2 for z/OS and OS/390:

• Query and reporting programs, which tend to stay connected for extended periods of time, and issue occasional SQL queries followed by a commit

• Workstation client/server programs, which also tend to stay connected for extended periods of time, and then execute multiple SQL statements in a transaction model

• Web server applications, which are usually designed to use a permanently connected link to DB2 for z/OS and OS/390 on behalf of individual Web requests, and execute transactions as required




(DSNDBM1)


(DSNDBM1)


(DSNMSTR)


(DSNMSTR)

DB2DataDB2Data



Distributed Data Facility(_ _ _ _DIST)



The DDF Veil – Measuring it

With DDF, a veil hangs between seeing the SQL performance and the Allied Address Space

That veil is the fact that DDF runs as an additional address space in the DB2 subsystem and needs to be measured there in order to gather performance statistics.

The address space name is xxxxDIST, where xxxx is the DB2 subsystem name.


The DDF Veil – Measuring it continued…

DDF uses SRBs (Service Request Blocks) instead of TCBs, which reduces CPU time.

MVS enclaves are used in exchanging data across address

spaces. This enables management by Workload Manager (WLM) of the work coming into DB2 through DDF.

Complexity of capturing performance statistics DICTATES the need for special tools!


Technical Description of DDF (Key components & their relationship to MVS)


DB2 DDF

DDF: Distributed Data Facility – a network interface in to DB2– Introduced in DB2 for MVS™/ESA V2 R2 (1988)

– Initially for interconnectivity on DB2 for MVS systems only


DDF - component of DB2 • Provides the connectivity to and from other

databases - [or servers like DB2 Connect over the network]

• Supports two database communication protocols • DB2 Private Protocol• Distributed Relational Database Architecture (DRDA)

• Supports two network protocols, SNA and TCP/IP• Is the transaction manager for distributed

database connections

DB2 DDF continued…


DB2 DDF continued… DDF is the transaction manager for distributed database

connections– Distributed Relational Database Architecture (DRDA)– Introduced in DB2 V2R3– Adopted by Open Group as standard in 1999– With DRDA, connections to DB2 come from anywhere within

the TCP/IP or SNA network

Many enhancements to DDF / DRDA since its introduction – Each new release of DB2 had added capabilities within DDF /

DRDA / DB2 Connect– Keeping up with the DRDA standard implementations– Adding new management and transaction processing

capabilities– Security enhancements


Key components of DDF – DRDA

Application requester (AR) functions support SQL and program preparation services from applications. The AR is SQL neutral, so it can accept SQL that is supported on any DBMS.

Application server (AS) functions support requests that application requesters have sent, and routes requests to database servers by connecting as an application requester.

Database Server (DS) functions. This is to support requests from application servers. It supports the propagation of special register settings, and can forward SQL requests to other database servers.


DRDA - Transaction Manager for distributed database connections

Unlike CICS or IMS, connections via DDF are not localized to a few address spaces within z/OS.

With DRDA, connections can come from literally anywhere within the bounds of the SNA or TCP/IP network. (This is the major reason why capturing the performance of a DDF application is so difficult. )

This is also the reason DDF has developed very mature thread management strategies to be able to handle thousands of connections from anywhere.


Measuring DB2 SQL via stored procedures


DB2 SQL via stored procedures

Local threadvia SSAS

Remote threadvia SSAS


DB2 SQL via stored procedures

1. A thread must be created for each application that needs DB2 services:• Remote client - thread created via SQL CONNECT statement• Local - thread created via first SQL statement execution

2. When a client application issues an SQL CALL statement, the stored procedure (sp) name and the I/O parameters are passed to DB2.

3. When DB2 receives the SQL CALL statement:• Searches SYSIBM.SYSROUTINES for sp name• Obtains the sp load module and the run environment information• Searches SYSIBM.SYSPARMS for parameter information

4. Stored procedures are executed in address spaces that run fenced away from the DB2 code:• For DB2 Version 7 - single address space SPAS or multiple WLM

address spaces• For DB2 Version 8 - all newly created sp’s must use the WLM-

established stored procedures address space


DB2 SQL via stored procedures…continued5. The sp address space:

• Thread reused CPU cost low Accounting on behalf of client application

• Uses the LE/370 product libraries to load and execute sp’s

6. Control passed to sp with input and output parameters – sp can issue most SQL statements

7. The sp assigns values to output parameters, returns control to DB2

8. DB2 copies output parameters to the client application and returns control to it

9. Calling program receives the output parameters and continues the same unit of work, implicitly or explicitly issues COMMIT statement

10. DB2 returns control to the invoking program.




(DSNDBM1)


(DSNDBM1)


(DSNMSTR)


(DSNMSTR)

DB2DataDB2Data





Stored Procedure ASWLM

(wlm-as)

Stored Procedure ASWLM

(wlm-as)


Tuning Approaches


Tuning Approaches – Mainframe

The Mainframe tuning areas :• Have the “systems” people tune the MVS System (VTAM, CICS, etc.)• Have the capacity planners review DASD and MIPS performance and

upgrade as needed• Have the DBAs tune the database subsystem (Bufferpools, Tablespaces,

DBMS parameters)• Have the developers review and change their application code to “run

efficiently”• Have the developers and DBAs review and improve the performance of the

application SQL


Tuning Approaches – Distributed System

The “client server architecture” tuning areas :• Have the client/server “systems” people, usually the administrators, tune

that arena• Have the network people, usually the network administrators, tune the

network, including hardware upgrades• Have the client developers review and change their application code to

“run efficiently”


Tuning Approaches – MF & Distributed

The “single computer system” linked to the “client server architecture” :• Same tuning approach as in the single computer system• Same tuning approach as in the client server architecture• Have “whichever expert required” tune the “anything that has changed”

due to the combined environment architecture―The Distributed Data Facility (DDF) Address Space - where the

Client/Server application SQL executes―The stored procedure Address Space - where the Client/Server

application SQL executes


Tuning Approaches – All

General approach for tuning all environments :• “Systems” tuning as in both the “single” and the “distributed”

environment• Applications tuning (best be approached from a top down, highest to

lowest “resource consumptive” investigation)

• SQL tuning – must “see” what’s occurring (measure it first) !


Tuning SQL


Tuning SQL – Non DDF & DDF

The general approach for tuning :

Step Approach Tools

1 Be sure DB2 Runstats has been run. DB2 Utility

2 Identify SQL that consumes most of the resources – measure it.

Apptune or STROBE

3 Get a current listing of the SQL statement. DB2I or STROBE

4 Get a DB2 catalog listing of the DB2 objects related to the SQL

SPUFI or STROBE


Tuning SQL – Non DDF & DDF continued…

The general approach for tuning :

Step Approach Tools

5 Determine the DB2 Optimizer’s access path (Plan Table “Explain”).

DB2I, Mainview or STROBE

6 Analyze the predicates for access paths creation & avoid Stage 2.

TAKMI or STROBE

7 Use “artificial intelligence” software to initially analyze/tune SQL.

STROBE

8 Review SQL and DB2 Objects with the DBA or Industry Expert for performance

Actual People


References

• IBM Redbook “Distributed Functions of DB2 for z/OS and OS/390,” January, 2003, SG24-6952-00 – chapter on the history of DDF, DRDA and DB2Connect enhancements.

• IBM Redbook “DB2 for z/OS Stored Procedures: Through the Call and Beyond,” February, 2006, SG24-7083-00 – chapter on the importance of stored procedures, stored procedures overview.

• Various IBM Redbooks on DB2 performance topics for various DB2 versions, MVS and z/OS.


Additional Tuning


Tuning SQL – Access Paths

Determine Optimizer’s access path (Plan Table “Explain”):

• Tablespace Scan• Segmented Tablespace Scan• Non-Matching Index Scan• Matching Index Scan• Index-Only Scan


Tuning SQL – Access Paths continued…Performance is based upon how the predicate is set up :

Matching Index ScanMatching Index Scan

Tablespace ScanTablespace ScanCustomer_Table Customer_Index

Non-Matching Index Scan Non-Matching Index Scan

Index-Only ScanIndex-Only Scan

Select Name, Address, StateFrom Customer _TableWhere Name = “ABC”

Select Name, Address, StateFrom Customer _TableWhere Name = “ABC”

Customer_NoNameAddressStateZipOrder_No

Customer_NoNameAddressStateZipOrder_No

Customer_NoOrder_No

Customer_NoOrder_No

Select Name, Address, StateFrom Customer _TableWhere Order_No = 999

Select Name, Address, StateFrom Customer _TableWhere Order_No = 999

Select Name, Address, StateFrom Customer_TableWhere Customer_No = 123

Select Name, Address, StateFrom Customer_TableWhere Customer_No = 123

Select Customer_No, Order_NoFrom Customer_TableWhere Customer_No = 123

Select Customer_No, Order_NoFrom Customer_TableWhere Customer_No = 123


Tuning SQL – SQL Flow

Stage 1 versus Stage 2 predicates:

DBAS

Database Functions

Buffering

DSNDBM1

Data Manager

Stage 1 predicatesIndexable predicates

LockingVarious data

manipulations

Set-level Orientation Row-level Orientation Physical Data Access

Relational Data System

Stage 2 predicatesSQL statement

checkingSorting

Optimizer

Buffer Manager

Data movementto and from DASD

Bufferpools

APPLICATION

PROGRAM

SQL Flow – Pass 1: Program to RDS to DM to BM to PDA (VSAM Media Manager) SQL Flow – Pass 2: BM to DM (Stage 1 processing) to RDS (Stage 2 processing)

to the Program


Tuning SQL – SQL Flow continued…Stage 1 versus Stage 2 predicates – Pass 1:

• Enters RDS• checks authorization• resolves data element names• checks syntax• optimizes SQL• generates access path• passes SQL to the DM

• DM •analyzes the request for the data (table or index rows)•calls BM to satisfy request

• BM•data in buffer pools - accesses data•data not in buffer pools - calls the VSAM Media Manager•returns it to the DM


Tuning SQL – SQL Flow continued…Stage 1 versus Stage 2 predicates – Pass 2:

• DM •apply Stage 1 predicates only (reduces answer set) •returns to RDS

• RDS•applies Stage 2 predicates•performs sorting (if needed)•passes data back to application program

If only stage one predicates are used, the extra overhead of the Stage 2 Predicate process done in the RDS will be omitted - DB2’s system services module DSNXGRP will not be called.


Tuning SQL – ConclusionReview SQL & DB2 Objects for performance improvements:

• Business-critical applications with high SQL consumption√ experienced DB2 DBA (s)√ tool that has built in “artificial intelligence” for DB2√ use DB2 Tuning Guru’s

compuwarecorporation sccmg irvine, ca may 4th, 2007

Documents