DB2 Performance and Tuning
Duration
Three Days
Relational Concepts and DB2 Architecture
- What is a Relation?Relational and Non-Relational TablesPrimary and Foreign KeysReferential Integrity (RI)The DB2 EnvironmentThe DB2 ComponentsA Recovery ExampleLog Data SetsRelational Data System and Data ManagerBuffer Manager and Buffer PoolsHiperpool, EDM Pool and Sort PoolDB2 DirectoryDB2 Catalog
- Special Registers
DB2 Data Objects
- Base TablesDB2 Data TypesDate, Time and Timestamp Data TypesFrequently Use Data TypesThe ROWID and LOB Data TypesImplementing Referential IntegrityTable SpacesPartitioned Table SpacesSegmented and Simple Table SpacesData Page Layout and Record IDIndexesIndex SpacesLeaf Page LayoutIndex Entry FormatLeaf Page SplitThe Space Map Page (SMP)Databases and Storage GroupsCreating Data ObjectsBasic Create Table Space StatementBasic Create Table StatementBasic Create Index StatementLob Data Type RevisitedLob Data Type: An ExampleDB2 Data Type Limits
- DB2 Data Object Limits
Column, Table, and Table Space Design
- General Column ConsiderationsColumn: Null or Not NullColumn: CHAR or VARCHARArranging VARCHAR ColumnsCompound ColumnsMulti-valued Column and Derived ColumnRepeating ColumnsTable Design: Vertical SplitFlip-Flop TableTable Design: Horizontal SplitTable Partitioning
- Segmented Table Space
Check Constraints, Referential Integrity and Triggers
- Table ConstraintsUnique ConstraintsReferential ConstraintsCheck ConstraintsTo Check or Not To CheckCode Tables, Application-enforced RIWhat is a Trigger?
- The Parts of a Trigger
Special Tables
- Code TablesCheckout and Auditing TablesControl/Restart TablesGlobal Temporary TablesExample: Using a Global Temporary TableDeclared Temporary Tables
- Referencing Declared Temporary Tables
Predicate Analysis and DB2 Access Methods
- Predicate and Predicate TypesMatching a Predicate against an IndexIndexable, Stage 1 and Stage 2 PredicatesIndexable and Stage 1 Predicate TypesOrder of Evaluating PredicatesVariables Used for Access Path ConstructionFilter Factor (FF)DB2 Access MethodsSequential DetectionSequential PrefetchTable Space ScanNon-Matching Index ScanMatching Index ScanMultiple-Index and Index Only AccessNested Loop JoinMerge Scan Join
- Hybrid Join
Index Considerations
- Determine Index Key ColumnsGeneral Index ConsiderationsClustering Index ConsiderationsNon-Unique IndexIndexing to Avoid SortMultiple Column IndexMulti-Column Index or Multiple Indexes
- Indexing Varchar and Length Alphanumeric Columns
The EXPLAIN Statement and The PLAN_TABLE
- The EXPLAIN StatementThe PLAN_TABLEColumns of the PLAN_TABLEExample: Explaining a subqueryExample: Explaining a Join
- Multiple Indexes Access
Online Application Design
- Design for ConcurrencyThe OPTIMIZE FOR n ROWS OptionSQL ConsiderationsHot SpotsOnline Design: A Case StudySurrogate Key
- Using Checkout Table
Batch Application Design
- Batch Update: Process 1Batch Update: Process 2Running Process 1 and Process 2Batch Update: Process 3Batch Update: Process 4Comparing Process 3 and Process 4
- Table Maintenance: Processes 5 and 6
Appendix A. Concurrency Control and Locking
- Concurrency and LocksLock SizesPage and Row Lock ModesLock ProcessingThe Lost Update Problem RevisitedIsolationThe U Lock ModeCompatibility of Lock Modes
- Lock Threshold and Lock Escalation
Appendix B. Plans and Packages
- Program PreparationPrecompile and Compile ListingsBound DBRM MemberPackage NameThe Bind Package CommandBinding a Plan with PackagesAssociating a Program with a PlanExecuting a Program with Plan and PackagesIdentifying Packages During ExecutionUsing CURRENT PACKAGESET: Example 1Using CURRENT PACKAGESET: Example 2
- Using CURRENT PACKAGESET: Example 3
