Articles & Content

Archives

STATS to the MAX -- Accesspath (and Real-Time, too) (A05)

Topic: 2007 NA

Subtopic: DB2 for z/OS



DATE: 2007-5-8 (09:20 AM - 10:20 AM)
SPEAKERS: Terry Berman (DST Systems Inc.)

Accesspath Statistics are essential inputs to Optimizer decision-making, so best practices are needed to collect and maintain them. Are we providing the right cardinalities and frequencies for our SQL predicate columns (singly and in combination), as is now possible (in V8 RUNSTATS)? This presentation describes an innovative stats-gathering mechanism: REXX automation driving DSNUTILS RUNSTATS. Along with its operational advantages (window-driven throughput, using parallelism) and low maintenance (wildcarded workloads), we get the ability to customize column statistics to actual predicate usage and degree of non-uniformity ("heuristic data skew"). As a bonus, the process "seeds" Real-Time Statistics (RTS), triggering near-real-time row counts shown for all tables (which DB2 does not do otherwise). Finally, closing the loop, RTS cardinalities are polled periodically to shown when Accesspath Statistics need to be updated.

EXP. LEVEL: Intermediate,Advanced

OBJECTIVES:

Optional column statistics, its new enabling V8 RUNSTATS syntax, and how our access paths benefit

A "Stats Advisor" for static workloads: all columns in predicates, heuristic data skew frequencies

REXX/DSNUTILS Stats-gathering: how the automation works, its many advantages over traditional methods

Real-Time Statistics: basics - and how to "seed" them so that DB2 will produce a complete picture

How a Real Time Statistics "time series" can alert you to a data change requiring an Accesspath Stats update



This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!

Download File
Click to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.