db2 for zos architecture v1.2 - disco. · pdf filethe bank of china, a ... db2 for z/os...

1

®

IBM Software Group

© 2008 IBM CorporationDISCO, Bicocca University, Milan – 11 Dec. 2008

DB2 for z/OS:Architecture and Functions

A ngelo S ironiE xecutive IT A rchitect IB M Ita lia S .p.A .

IBM Software Group | DB2 information management software

2

The IBM Academic Initiative

2


3

Agenda

�z/System�z/Hardware

�z/OS

�DB2 for z/OS (V9)�Architecture�Memory Structure�Address Spaces Interaction�Subsystem (Re-)Start�DB Structures

�Concurrency Control & Locking�SQL Processing�DB2 Optimizer & Access Path Strategies�Buffer Pool Management�Query Parallelism�Logging�Autonomic Features�Security

®

IBM Software Group


z/System Platform

3


5

z/Hardware

� Also known as Mainframe

� Most recent incarnation

�z10 Family

� Z10 Major Characteristics

�4.4 Ghz Microprocessors

�Quad-core chips

�Max. Number of Core = 77

� User Core(*) = 64

� SAP (System Assist Processor) = 11

� Spare Core = 2

�Max. memory Size = 1.5 Tb

�Hardware Accelerators

� Cryptography

� Data Compression

� Decimal Floating Point arithmetic(*) Std., ICF, IFL, zIIP, zAAP


6

Mainframe Evolution

4


7

Mainframe Platform: The z/Architecture

Expanded Storage

MainStorage

CP

CP

Crypto

ETR… …

DynamicSwitch

DynamicSwitch

CU

…

…

…

…

Serial Channel Paths

CU CU

CU

Channel Subsystem

CU

CU…

CU …

CU …

…

………Parallel Channel Paths


8

z/OS: Address Space Concept

224

z/OS data

5

®

IBM Software Group


DB2 for z/OS


10

DB2 for z/OS

�“Born” in 1983

�RDBMS for z System platform�Exploits the z platform

�Hybrid engine�Relational data

�ORDBMS extensions

�XML documents

�Parallel query engine

�Cluster support on Parallel Sysplex�Data Sharing architecture

�High Availability & Parallelism

�Distributed Data support

6


11

Facts and Figures

� Used by:

– The top 56 banks in the world

– 23 of the top 25 U.S. retailers

– 9 of the top 10 global life/health insurance providers

� Performance, Performance, Performance

– Delivered the largest banking benchmark ever at the Bank of China, a record 9,445 transactions per second

– Supports the world’s largest known peak database workload - 1.1 billion SQL statements per hour at UPS

– The world’s largest known transaction processing database – 23.1 TB at the UK Land Registry


12

DB2 for z/OS vs. DB2 for LUW Architecture

Subsystem DB2A

RDS Optimizer Lock Mgr.

Log Mgr. Buffer Mgr. I/O Mgrs.

Other Stor. I/O Buffers Log Buffers

DB2 Code & Storage Areas

Databases

DB2 Catalog

DB2 Directory

System

UserDB1

UserDBn

UserLog Files

LogncLog2

Log1

z/OS – OS/390

DB2 UDB Instance DB2A

Database-1

DB2 Catalog User Tables LogncLog2Log1

I/O Buffers Log Buffers

RDS Optimizer Lock Mgr.

Log Mgr. Buffer Mgr. I/O Mgrs.

DB2 Code & Storage Areas

DBM Storage Areas

Database-2

DB2 Catalog LogncLog2Log1

I/O Buffers Log Buffers

User Tables

Linux, Unix, Windows

DB2 for z/OS Architecture

7


13

DB2 for z/OS: A z/OS Sub-system

ssnmWLMx

WLMManaged

StoredProcs.

ssnmWLMx

WLMManaged

StoredProcs.

ssnmWLMx

WLMManaged

Stored

Procs.

ssnmWLMx

WLMManaged

Stored

Procs.

IRL

M

ssnmMSTR

SystemServices

ssnmDBM1

RDS,Data &Buffer

Services

ssnmDIST

Distr.Data

Facility(DDF)

ssnmWLMx

WLMManaged

StoredProcs.

z/OS


14

Memory Structure: DB2 Address Spaces (1 of 2)

� MSTR (System Services AS)�General Command processor

�Subsystem support

�Agent services manager

�Storage manager

�Message generator

� Initialization procedures

� Instrumentation facilities

�System parameter manager

�Recovery manager

�Recovery log manager

�Group manager

�Distributed transaction manager

� IRLM (Internal Resource Lock Manager AS)� Lock Manager

xxxxWLMx

WLM

Managed

Stored

Procs.

xxxxWLMx

WLM

Managed

Stored

Procs.

xxxxWLMx

WLM

Managed

Stored

Procs.

xxxxWLMx

WLM

Managed

Stored

Procs.

IRL

M

ssnmMSTR

System

Services

ssnmDBM1

RDS,

Data &

Buffer

Services

ssnmDIST

Distr.

Data

Facility

(DDF)

ssnmWLMx

WLM

Managed

Stored

Procs.

z/OS

� DBM1 (Database Manager AS)

�Service controller

�Data manager

� Large-object manager (LOBM)

�Data space manager

�Relational data system (RDS)

�Stored-procedures manager

�Utilities (work with associated code in an allied address space)

�Buffer manager

8


15

Memory Structure: DB2 Address Spaces (2 of 2)

� DIST (Distributed Data Facility)

� DRDA (Distr. Relational Database Arch.) Support

� Data Communications Resource Manager (DCRM)

� Distributed Data Interchange Services (DDIS)

� Distributed Relational Data System Manager (DRDS)

� Distributed Transaction Manager (DTM)

� WLM (Workload Manager AS

for Stored Procedures)

�Stored Procedures

�External User-Defined Functions (UDF)

xxxxWLMx

WLM

Managed

Stored

Procs.

xxxxWLMx

WLM

Managed

Stored

Procs.

xxxxWLMx

WLM

Managed

Stored

Procs.

xxxxWLMx

WLM

Managed

Stored

Procs.

IRL

M

ssnmMSTR

System

Services

ssnmDBM1

RDS,

Data &

Buffer

Services

ssnmDIST

Distr.

Data

Facility

(DDF)

ssnmWLMx

WLM

Managed

Stored

Procs.

z/OS


16

Allied Address Spaces

DBM1 MSTR IRLM

DIST

TSO

Batch

CICS

IMS

Stored Proc

WebSphere

9


17

Programming Interfaces

� Developer communities

�COBOL, PL/I, C, C++,

�REXX™, APL2®, Assembler,

Fortran

�Java™ (JDBC / SQLJ)

�.NET (C#, VB .NET)

�PHP

�Perl

�Python

�Ruby on Rails

�Toad for DB2


18

DB2 System Address Spaces Interaction

RelationalData System

Data/IndexManager

BufferManager

Log/RecoveryManager

LockManager

AttachmentFacility

IRLMMSTR

SPAS

SP/UDFExecution

DBM1

VSAM MediaManagerStorage

CICS

Trx. Server

10


19

DB2 Subsystem Restart

DSNZ002I = DSNZINIT SUBSYSTEM V81A SYSTEMPARAMETERS LOAD MODULE NAME IS V81AZDSN

DSNY001I = SUBSYSTEM STARTING

DSNJ127I = SYSTEM TIMESTAMP FOR BSDS= 03.013 14:32:14.45DSNJ001I = DSNJW007 CURRENT COPY 1 ACTIVELOG DATA SET ISDSNAME=DSNC810.LOGCOPY1.DS02,STARTRBA=000001FF3000,ENDRBA=0000029CAFFFDSNJ001I = DSNJW007 CURRENT COPY 2 ACTIVELOG DATA SET ISDSNAME=DSNC810.LOGCOPY2.DS02,STARTRBA=000001FF3000,ENDRBA=0000029CAFFF

DSNJ099I = LOG RECORDING TO COMMENCE WITHSTARTRBA=00000265F000

S V81ADBM1

DSNR001I = RESTART INITIATEDDSNR003I = RESTART...PRIOR CHECKPOINTRBA=000002656090DSNR004I = RESTART...UR STATUS COUNTSIN COMMIT=0, INDOUBT=0,INFLIGHT=1, IN ABORT=0, POSTPONEDABORT=0DSNR007I = RESTART...STATUS TABLE

T CON-ID CORR-ID AUTHID PLAN S URID DAY TIME

------- ------- ------ -------- - ------------- --- --------

B TSO SYSADM SYSADM DSNESPRR F 003C20AC0141 042 04:35:54

Log Init Current Status Rebuild (CSR)


20

DB2 Subsystem Restart

DSNI029I = DSNIFLAI THE FAST LOG APPLY PROCESS IS ACTIVE AND THE AVAILABLE

V81ADBM1 STORAGE IS 00000000 BYTES

DSNI028I = DSNIFLAF THE NUMBER OF QUALIFIED LOG RECORDS READ DURING THE FAST LOG APPLY PROCESS IS 45AND THE NUMBER OF FAST LOG APPLY BUFFERS PROCESSED ARE 1

DSNR005I = RESTART...COUNTS AFTER FORWARD RECOVERY IN COMMIT=0, INDOUBT=0

DSNR018I = RESTART...BACKWARDRECOVERY PROCESSED FROM RBA 00000265DE5B TO RBA 000002658912

DSNR006I = RESTART...COUNTSAFTER BACKWARD RECOVERYINFLIGHT=0, IN ABORT=0,POSTPONED ABORT=0

Forward Recovery Backward Recovery

End Restart

DSNR002I = RESTART COMPLETED

11


21

DB Structures: Overview

�Database�A DB2 Catalog entry

�A collection of physical objects

�Table space�A collection of datasets (files) for storing one or more

Tables

�Table

� Index�B-tree structure and associated Index space

� Index space�A collection of datasets (files) for storing an Index

�View


22

Data Structures in DB2

Storage group Physical DISK devices

Database

Table space

Table

Index

Logical concept

Physical data sets

12


23

DB Structures: Table Space

�Simple�One or more table per table space�Individual pages may contain rows from multiple tables�Deprecated in latest DB2 Release (DB2 for z/OS V9)

�Segmented�One or more table per table space�Segments allocated to single table�Segment size (pages) = a multiple of 4 btw. 4 and 64

�Partitioned�Single-table table space� Multiple partitions (files) by data-values range�Indexes can be partitioned or not

�Universal�Both Segmented & Partitioned

� Partition by growth

� Partition by range


24

Data & Index Compression

� Data Compression

�Defined at the table space level

�Using the Ziv-Lempel algorithm

�Requires a compression dictionary to be built

�Based on the CMPSC hardware instruction

� Newer generation of z processors implement instructions directly in

circuits (instead of microcode), yielding lower overhead. Instruction design favors decompression over compression.

�Reduce disk space, channel and memory occupancy

� Index Compression

�New in DB2 V9

�Not using Ziv-Lempel algorithm nor a compression dictionary

�Prefix compression used, instead

13


25

Concurrency & Integrity: Locking

� DB2 for z/OS provides comprehensive locking to ensure data integrity

� Locks are used on a variety of data objects, including

�Rows

�Pages

�Tables

�Table space segments

�Table space partitions

�Entire table spaces

�Databases

� Locking granularity affected by

�DBA chosen LOCKSIZE on Database objects

�Application chosen ISOLATION Level

� Lock Escalation

N.B. No locking on Indexes – Latching used, instead.


26

Concurrency Control: Locking

� Lock Modes & Granularity

�Table, Partition, Table space

� IS – Intent Share

� IX – Intent Exclusive

� S – Share

� U – Update

� X – Exclusive

� SIX – Share with Intent Exclusive

�Page, Row

� S - Shared

� U - Update

� X - Exclusive

NoNoNoX

NoNoYesU

NoYesYesS

XUSLock ModeLock Modes

Compatibility

Matrix

14


27

DB2 Isolation Level

�UR - Uncommitted Read�For read only queries, no record locking

�Will see uncommitted changes by other transactions

�CS - Cursor Stability�Locks and unlocks the row or page, where the application is

positioned

�Guaranteed to only return data which was committed at the time of the read

�RS - Read Stability�Will keep all qualifying rows locked until the transaction is

completed

�Does release locks on rows that do not satisfy query predicates

�RR - Repeatable Read�Will lock all rows visited and keep locks until the transaction is

completed


28

SQL Processing: Static SQL

�Usually used for batch and transactional applications

�Data structures and query semantics know at coding time

�Bound once at program preparation time (before or even long before execution time)�CPU savings

�Easier security control

�Dynamic re-optimization available

�Coding example�EXEC SQL SELECT NAME INTO :custname

FROM CUSTOMERS

WHERE ID = :custid

�Java example: SQLJ

host variable

host variable

15


29

SQL Processing: Dynamic SQL

� Usually used for Query & Reporting, and Business Intelligence applications

� Used when data structures and/or query semantics unknown before execution

� Statement built / stored into program area

� Bound at execution time � Re-bound before each execution unless cached (usage of parameter

markers required)

� Caching reduces CPU consumption

� Dynamic re-optimization still available

� Example (w. parameter marker)� SELECT NAME

FROM CUSTOMERS

WHERE ID = ?

� Dynamic SQL interfaces: ODBC (.Net), JDBC (Java)


30

Application Packages and Plans

� Application Package�Contains control structures that DB2 uses when it runs

SQL statements

� Control Structure = operational form of an SQL statement

�Generated at program BIND time

�Each package derived from a single source program

�Supports Versioning & Switch-back

�Packages are grouped into Collections

� Application Plan�Relates an application process to a local DB2 instance

�Contains a list of package names (and/or Collection names)

�Execution requires specific authorization

16


31

Access Path Major Strategies� Join Methods�NLJ – Nested Loop Join�MS – Merge Scan�HJ – Hybrid Join�SJ – Star Join

� Data Access�Relation Scan / Partition Scan�No-matching Index Scan�Matching Index Scan�Index Screening

� Index access may / may not involve table access

�Multiple Index Usage (Union / Intersection)

� I/O Methods�Synchronous Read�Pre-fetch�Dynamic Pre-fetch�List Pre-fetch


32

Retrieving Access Path Information

� EXPLAIN statement

�Dynamic Execution

�Accepts Parameter markers

�Applies to single SQL query at a time

�Access Path info stored into PLAN_TABLE and two optional tables

� EXPLAIN Option of BIND Command

�For Static SQL queries

�Applies to all queries in module

�Access Path info stored into PLAN_TABLE and two optional tables

� EXPLAIN data in Perf. Trace records

�Applies to Dynamic SQL Queries

�Proper Perf. Trace must be enabled

17


33

Analyzing Access Path

� Access Path graph display� Using Visual Explain

� Similar to function available on DB2 for LUW

� Tabular representation in set of tables� Most relevant info in PLAN_TABLE

� Example

QRY QBL PL TAB AC MTCH ACCESS IX SORT SORC J

NO KNO NO MTH TNAME CARD NO TY COLS NAME O UJOG UJOG T

---+---+--+---+------+---------+---+--+----+---------+--+----+----+-

100 1 1 0 CAMPG 61 3 I 2 IX_CM1 N NNNN NNNN

1 2 1 PERS 2,051,585 5 I 0 IX_CRM_1 N NNNN NNNN

1 3 1 PERSIN 324,769 2 I 2 IX_PE1 N NNNN NNNN

1 4 1 CONTA 327,645 1 I 1 IX_CON_3 N NNNN NNNN

1 5 1 PHNE 39,005 6 I 3 IX_PH_2 N NNNN NNNN L

1 6 3 - 0 0 N NNNN NNYN


34

Query Parallelism

�For Dynamic SQL

�Enabled / disabled by SET CURRENT DEGREE statement

�Applies to queries subsequently prepared

�For Static SQL

�Enabled / disabled by DEGREE option of BIND

�Applies to all queries in application program

�Characteristics

�Final decision up to DB2

�Type of parallelism (CPU or I/O) chosen by DB2

�Degree of parallelism chosen by DB2

�Installation can set max. degree

18


35

Buffer Pools

� Created in the DB2 DBM1 AS when first dataset opened� Deleted when all referenced data sets are closed

� Different page size of buffer� 4, 8, 16, 32 KB

� Table space and BP association is done with SQL� CREATE / ALTER TABLESPACE or INDEX

� Buffer management� Services Data Mgr. and Index Mgr. get page requests

� Locates a page in the buffer pool

� If not found, loads page from DASD to buffer pool

� Returns the address of page to DM or IM

� Deferred write pages to DASD

� Manages buffer pools usage

� Page latch for page integrity

� Data set management� Open and close

� Pre-format disk space


36

Buffer Pools Management Strategy

� Search

�Hashing table

� Replace

�Least recently used (LRU)

�Most recently used (MRU)

�First-in-first-out (FIFO)

� Write

�Deferred write

19


37

Buffer Pools Management Queues

Buffer manager

Hash table

DS1 DS2 DS3

Deferred write queue

LRU chain head

LRU chain tailSLRU chain head

SLRU chain tail

Buffer pools

Table or Indexspace


38

Logging Overview

� The DB2 log�used to register data changes and other significant DB2 events

� DB2 uses the log to �Recover database objects

�Rollback transactions

�Perform restart recovery

�Record certain diagnostic data

� Major log record types include�System checkpoint records

�Unit of Recovery (UR) control records: e.g. Begin UR, commit

�Database undo/redo records

�Database Exception Table records

� Exception states, e.g. LPL, RECP, STOPped, etc.

�Pageset control records – e.g.

� Pageset/Partition open/close

20


39

Logging Overview

Dual Copy recommended for

�BSDS

�Active Logs

�Archive Logs

Output Log Buffers

Threshold (user defined)

Reusable

BSDSAutomatic

Offload

Reusable

Notreus.

Notreus. Active

Logs

Archive

Logs

Input Log Buffers

Data Before/After Image


40

Anatomy of the Log

� Output buffers

�In MSTR virtual memory

�All log records written initially to the output buffers

�Size and write thresholds user-controlled

� Input buffers�In MSTR virtual memory

�Used when DB2 needs to read back records from active/archive logs

� Active log�Most recently written log records – written when

� Application Commits � Output Buffers threshold reached� Database write� System checkpoint

� Archive log�Contains log records "offloaded" from the active logs

� Bootstrap Dataset (BSDS)�Contains control information needed to manage logging / recovery

21


41

Active Log� Must reside on DISK

�Anywhere from 2 to 93 active log datasets

�Max size per dataset = 4GB

� Used in "wrap around" mode. Dataset reused when archived

� Can have dual copies, Copy1/Copy2

�Highly recommended for availability reasons

� DB2 switches to next active log dataset when:

�Space in current active log dataset has been exhausted

�I/O error forces a "truncate and switch"

�ARCHIVE LOG command ("truncate and switch")

� An active log dataset can have one of five possible states:

�NEW: newly added dataset that has yet to be formatted�REUSABLE: new dataset, or dataset that has been offloaded

�NOTREUSABLE: dataset full but not yet offloaded

�STOPPED: DB2 has taken this dataset offline due to errors�TRUNCATED: not full due to I/O error or ARCHIVE LOG

command


42

Active Log Offload

� Offload is the process of copying an active log dataset to an archive log dataset

� Offload is initiated by one of the following events:

�When the active log dataset has filled (normal case)

�When the last available active log dataset is 75% full, plus increments of 5% thereafter

�When an I/O error on the active forces truncation

�When the ARCHIVE LOG command is issued

� DISPLAY LOG command can be used to monitor offload activity

� Information about the dataset written to BSDS when offload completes

�Inventory wraps such that oldest entry is deleted automatically

22


43

DB2 for z/OS Autonomic Features

�DB2 Base Autonomic Features

�Deep integration with z/OS WLM

� WLM-managed Stored Procedures AS

� WLM-managed I/O priority

� WLM-managed Buffer pools size

�Dynamic IX Creation for query processing (in-memory WF)

�DB2 Optimization Service Center (OSC)

�New no-charge offering

�Available via web download

�Additional Autonomic Features

�Included into DB2 Optimization Expert for z/OS (OE)

�New OTC (one Time Charge) offering


44

DB2 OSC / OE Features

YesAccess Path Advisor

YesWorkload Query Advisor

YesQuery Advisor

YesWorkload Index Advisor

YesQuery Index Advisor

YesYesProfile based Monitoring

YesYesWorkload Statistics Advisor

YesYesQuery Statistics Advisor

YesYesVisual Plan Hint

YesYesAccess Plan Graph

YesYesQuery Formatter, Annotation

Yes

Optimization Service Center

YesQueries from Cache, Catalog

Optimization Expert

Function

23


45

Security: Basics� Can be managed inside or outside DB2

�Inside DB2 using GRANT / REVOKE SQL statements

�Outside DB2 using OS-based security tool (IBM RACF, CA ACF2, etc.)

� Privileges

�Collection

�Database

�Function / Procedure

�Package / Plan

�Schema

�Sequence

�System

�Table / View

�Type / JAR (e.g. SQLJ)

�Use


46

Security: DB2 Trusted Context & DB2 Role

�DB2 Trusted Context

�Allows a unique set of privileges to be associated with an application preventing the misuse of privileges when not accessing through the trusted context

�Example:

� User Paul is allowed to retrieve information from

PERSONNEL table only when accessing DB2 from a

specified Host Name or IP Address

�DB2 Role

�A role is a object that can be granted any authority or privilege

�A role is only associated with a DB2 process when the application is associated with a trusted context

24


47

DB2 Trusted Context & DB2 Role: Example

CREATE ROLE CTXROLE ;

CREATE TRUSTED CONTEXT CTX1

BASED UPON CONNECTION

USING SYSTEM AUTHID ADMIN1

DEFAULT ROLE CTXROLE

WITH ROLE AS OBJECT OWNER

ATTRIBUTES (ADDRESS ‘9.67.40.219’)

ENABLE ;

GRANT DBADM TO CTXROLE ;


48

Multilevel Security

� Security Policy

�a set of laws, rules and practices that regulate how an organization manages, protects and distributes its sensitive data

�Used by the system to decide whether a particular subject can access a particular object

� Multilevel Security (MLS)

�A security policy that allows the classification of data and users based on a system of hierarchical security levels combined with a system of non-hierarchical security categories

�Has two primary goals

1.Controls must prevent unauthorized individuals from accessing

information at a higher classification than their authorization (read up)

2.Controls must prevent individuals from declassifying information(write down)

�Hierarchy established in the security server (i.e. IBM RACF)

25


49

Multilevel Security: Example

426Z3BLUE

19834I4INDIGO

183U5VIOLET

11994Q6BLUE

112U7RED

653G8ORANGE

239874R9RED

1375W10YELLOW

2351B2

1985A1

Conf_InfoValueIDSEC_LABEL

RAINBOW

SUNSET GREEN PASTEL

RED ORANGE YELLOW BLUE INDIGO VIOLET

SallySECLABEL=‘RAINBOW’

JoeSECLABEL=‘SUNSET’

SUNSET

Sam PASTEL

SECLABEL=‘PASTEL’


50

References (available on the Web)

[1] K.E. Plambeck et al., Development and Attributes of z/Architecture, IBM

J. RES. & DEV. VOL. 46 NO. 4/5 July/September 2002

[2] Paul Roger et al., ABCs of z/OS System Programming Volume 10, IBM

Redbook SG24-6990-03

[3] D.J. Haderle, R.D. Jackson, IBM Database 2 Overview, IBM Systems Journal, Vol. 23, No. 2, 1984 (original paper written shortly after DB2 Announcement)

[4] Paolo Bruni & al., DB2 9 for z/OS Technical Overview, IBM Redbook SG24-7330

[5] DB2 for z/OS Documentation (e.g. SC18-9847 Introduction to DB2 for

z/OS) available from

http://www-01.ibm.com/software/data/db2/zos/library.html

[6] Redbooks on z/OS and DB2 for z/OS available from

www.redbooks.ibm.com

[7] The IBM Academic Initiative (Italia) – Pls. visit Internet page at

http://www-304.ibm.com/jct01005c/university/scholars/it/scholars_program.html

db2 for zos architecture v1.2 - disco. · pdf filethe bank of china, a ... db2 for z/os...

Documents