|
Index
Usage for Performance
 Index Structure Details
How
Indexes are Used
The
Clustering Index
Using An Index
to Avoid a Sort
Index
Lookaside
1-Fetch
Index Scan
Use
of Multiple Indexes
Use of
Composite Indexes
Direct Row Access without
Index
Usage and Tablespace Scan
Concurrency
Control
The Purpose and Need for Locks
Timeouts
and Deadlocks
Cost
and Benefit of Row Locking
Duration
of Locks and Latches
CS/RR/RS/UR by Statement
Acquire/ReleaseTable/Tablespace
Lock
Avoidance
Monitoring Locks and Latches
Locking Trade-Offs
Program Development
Testing SQL Statements
Processing Before First Row
Testing for Existence
Commit Frequency
Updating After a Commit
Cursor Repositioning
Scrollable Cursors
Optimize for "n" Rows
Cursor with Hold
For Update of and Performance
Identity Column
Connecting to Remote Sites
Triggering
Actions in DB2
What are
Triggers
BEFORE Trigger
to Validate Data
Activation
Time and Events
Granularity
and Mode
Transition
Variables
WHEN Cond.
& Trigger Body
SIGNAL
SQLSTATE
Examples of
Trigger Usage
AFTER Triggers
Trigger
Package
Contrast
with Constraints
|
Stored Procedures
Advantages of
Stored Procedures
Return Result Sets
Procedures in
the Catalog Tables
 SAVEPOINT Management
 Access to Non-DB2 Resources
 Create and Declare Global
Temporary Tables
 Preparation of External Procedures
 SQL Procedures
 Procedural Statements
 Stored Procedure Builder
User Defined Functions and
Data Types
Creating and
Using Distinct Types
Cast To and
From Distinct Types
Sourced and
External UDF
Explanation of
Create Function
Information in the Catalog
Table UDF
UDF Program
Preparation
Use of
DSN_FUNCTION_TABLE
DB2 Supplied
UDF
Batch Processing
Case Study for Batch Processing
Index
Usage vs. Tablespace Scan
Disallowing
the Use of an Index
LOAD Utility vs. Program INSERTs
Deleting
Many Rows with Reorg
Concurrent
Batch and Online
Restart
Alternatives
Inner & Outer Join Performance
Efficient Joins on Large Tables
Merge,
Nested loop, & Hybrid Join
Outer
Joins - Full, Left, and Right
Apply
Local Predicates Before Join
Influencing
the Join Method
Subselects & Table Expressions
Subselect Compared with a Join
In-Memory
Sparse Index
Transformation
to an IN (list)
Correlated
Subselects
Update Based on Subselect
|
Table Expressions and with
Outer
Joins and Local Predicates
Materialization
of Partial Results
Programming for Performance
Stage
1 and 2 Predicates
Data
Type Effect on Index Usage
Column
& Non-Column Exp.
Arithmetic
and Scalar Functions
CASE
Expressions
FF
for ORed Range Predicates
LIKE
use of Indexes
Order
of Predicate Evaluation
Impact of Host Variables on
Access Paths
SQL that Benefits from Reopt.
Composite
Indexes & Range Pred.
Skewed
Distribution of Values
Program
Choice of Access Path
FFs
Using the Requested Value
Generalized
Reporting
Embedded
Dynamic SQL
Minimizing
the Cost of Binds
Caching
of Dynamic SQL
Sorts and List Prefetch
Functions Requiring Sorted Rows
Sorts
Can be Avoided
Use
of Multiple Indexes
List
Prefetch Thresholds
Pros
and Cons of List Prefetch
Sort
and RID Pool Sizing
Explain the Access Paths Chosen
 Getting
Info. on Paths Chosen
How EXPLAIN is
Used
Processor Time Estimate
Use of
Parameter Markers
Sorts & Sequential Prefetch
Multiple Indexes & List Prefetch
Join Explanations
Subselect Explanations
Locking Explanations
Updates and Deletes
Managing Production Explains
Optimization Hints
|
