master db performance with wlm

DB2’S GOT

MASTER DB PERFORMANCE WITH WLM

Raja

DB2 V10.1 on AIX

Hybrid multi TB Database

Tables with 3080761021 rows (> 3 Billion)

Failure of reports (36%)

Business user (Sponsor) queries – SLOW

(41%)

Problem Statement

First Impression

INVESTIGATION

H/W as per Firm standards

Load on DB increased

DBA’s killing low priority sessions

Resource hogging on the server

Business users were NOT happy

DB2 WLM is a functionality that o Maintains SLA’so Prevents resource hoggingo Optimizes system to avoid overload

oControls, monitors and analyses DB activity

Workload Manager …… in a nutshell

DB2 WLM introduces the entities called oService Super Class – Logical Grouping of SCoService classo Thresholdo Workloado Work Class Seto Work Action Set

WLM…… continued

Service class - Applies Conditions

WLM…… continued

Threshold – Sets Limits

DATA TAG – Tag for the data (New Feature in 10.1)o CREATE TABLESPACE "VOTE_4RAJA“

.... .... DATA TAG 1...;o ALTER TABLESPACE "VOTE_4RAJA“

... ... DATA TAG 1 ....;

WLM……new functionality

CREATE SERVICE CLASS CLASS1 CREATE SERVICE CLASS “LOW_PRIORITY“ UNDER “CLASS1“

.....

CPU LIMIT 25

PREFETCH PRIORITY LOW

BUFFERPOOL PRIORITY LOW; CREATE SERVICE CLASS “HIGH_PRIORITY“ UNDER

“CLASS1” …..

CPU LIMIT NONE

PREFETCH PRIORITY HIGH

BUFFERPOOL PRIORITY HIGH

WLM DDL

CREATE THRESHOLD "LOW_TO_HIGH"

FOR SERVICE CLASS “LOW_PRIORITY"

WHEN DATATAGINSC IN (1,...)

REMAP ACTIVITY TO “HIGH_PRIORITY“

ALTER THRESHOLD ………

……………………………

WHEN DATATAGINSC IN (1,...)

REMAP ACTIVITY TO “HIGH_PRIORITY“

WLM DDL

WLM……continued

LOW_PRIORITY SC

HIGH_PRIORITY SC

THRESHOLD

NOYES

DATA TAG TBSP

Optimizer

First ImpressionBest Impression

Streamlined resource usage

Sponsor queries given priority – No failures

Reports generation for priority data on time

New Features offer multiple solutions

Happy Customer

Useful Link :http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.wn.doc/doc/c0052271.html

Thank You

Twitter : raja_iafLinkedin : www.linkedin.com/in/lrajasekhar/