DB04 - DB2 z/OS Application Performance and Tuning - 3 Days

DateLocationDurationCostEnrolEnquiry
2022-06-06Virtual3 Days £1200.00 (plus VAT)
Enquire
2022-08-23Virtual3 Days £1200.00 (plus VAT)
Enquire
2022-10-25Virtual3 Days £1200.00 (plus VAT)
Enquire

Course Description


Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.


Course Details

DB2 OBJECTS
The Structure Of Db2 Objects
Database Definition
Tablespaces
Tablespace Creation Syntax
Pages
Tablespace Organisation
Page And Row Organisation
The Simple Tablespace
The Segmented Tablespace
The Partitioned Tablespace
Partition-By-Growth Tablespace
Partition-By-Range Tablespace
Maximum Number Of Partitions
Storing LOB data
The Lob Tablespace
Inline LOBS
Summary of Create Tablespace Parameters
Table Definition
Db2 Column Types
Design Tips for Columns
Null Values
Nulls - Design Tips
Implicitly Hidden Columns
Row Change Timestamps
Row Change Timestamp Selection
Row Change Timestamp Insertion
Table-Controlled Partitioning
Partition Management
Partition Management Examples
Db2 Indexes
Index Definition
Create Index Parameters
Index Organisation - The B Tree Index
Backwards Index Scan
Non-Unique Indexes
Index Clustering
Clustering With Partitioned Tables
Clustering Within Partition
Changing the Clustering Sequence
Partitioned Indexes
Creating a DPSI
Design Considerations - DPSI Problems
Index On Expression
Index On Expression Considerations
Index Design Considerations
Altering Tables and Indexes
Data Type Changes
Alter Data Type - Performance Implications
Alter Data Type - Impact Upon Indexes
Alter Data Type - Index Availability

DML PERFORMANCE REVIEW
Select Statement - Review
The Where Clause - Review
Special Operators - Examples
Sql Built-In Column Functions
Column Function Performance Notes
Using 'Distinct'
Group By Clause
Expressions / Functions in Group By
Having Clause
Order By Clause
Fetch First 'n' Rows Only Clause
The Update Statement
The Delete Statement
The Insert Statement
The Merge Statement
Merge Statement Restrictions
Select from Insert
Select from Insert Example
Select From Insert in a Cursor
Select From Insert - Order By Option
Select from Update
Select from Delete
Select from Merge
Scalar Functions
The Case Statement
Inner Joins
Outer Joins
Join Examples
Union, Intersect and Except
Union / Intersect / Except Examples
Subqueries
Subqueries Using In
Exists
Common Table Expressions
Common Table Expressions - A Complex Example
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
Improved Insert Performance - The Append Parameter
The Truncate Statement
Truncate Examples
Truncate Restrictions
Utility Performance - Row Placement Rules

CLONE TABLES
Clone Tables - Fast Data Replacement
Why Use Clone Tables?
Creating A Clone Table
Dropping A Clone Table
Clone Table Considerations
Creating a Clone - Catalog Activity
Clone Tables - The Exchange Command
Clone Tables - Exchange Considerations
Clone Tables - Locking Considerations

PREDICATE PROCESSING
Predicate Definition
Predicate Evaluation
Predicate Evaluation Table
Predicate Evaluation Table - Notes
Indexable Predicates
Stage 1 And Stage 2 Predicates
Predicate Evaluation Sequence
Use Of And / Or

RUNSTATS
The Runstats Utility
Catalog Statistics Updated By Runstats
Runstats Considerations
Runstats - Distribution Statistics
Runstats - Historical Statistics
Volatile Tables

OPTIMIZATION
The Optimizer
The Optimize Statement
Fetch First 'n' Rows Only Clause
Filter Factors
Filter Factors With Boolean Operations
Performance / Cost Estimation
Example Of Performance / Cost Estimation
Run-Time Reoptimization
Optimization Hints
Planning to use Optimization Hints

TUNING
Db2 Explain
The Explain Statement
Plan Table Layout
Plan Table Column Definitions
The Dsn_Statemnt_Table
The Dsn_Function_Table
The Dsn_Statement_Cache_Table
The Dsn_Struct_Table
The Dsn_Predicat_Table
The Dsn_Detcost_Table
The Dsn_Sort_Table
The Dsn_Sortkey_Table
The Dsn_Filter_Table
The Dsn_Pgrange_Table
The Dsn_Pgroup_Table
The Dsn_Ptask_Table
The Dsn_Viewref_Table
The Dsn_Query_Table
The Dsn_Coldist_Table
The Dsn_Keytgtdist_Table
Explaining the Statement Cache
Interpreting Explain Output
Tablespace Scan
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Prefetch Processing
List Sequential Prefetch
SQL In List Processing - Dynamic Prefetch
Sequential Detection
Query Parallelism Techniques
Enabling Parallelism
Table Join Methods
Merge Scan Join
Nested Loop Join
Hybrid Join
Star Join
Star Join Example

IDENTITY COLUMNS AND SEQUENCES
Identity Columns
Identity Columns - Examples
Altering Identity Columns
Altering Identity Columns - Parameters
Identity Columns - Retrieving the Generated Number
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Sequences and Identity Columns Comparison

JAVA DATABASE ACCESS - JDBC PERFORMANCE
Database Environments
The DB2 Universal Driver
JDBC SQL Statements
Using the Statement Class to Select Rows
Running SQL Statements
Using the Statement Class to Update Rows
Processing Result Sets
Jdbc Cursor Operations
Cursor Scroll Types
Cursor Concurrency Types
Cursor Holdibility Types
Updating Rows using the Cursor
Using the PreparedStatement Class
Making Batched Updates
Isolation Levels
DataSources
Getting Database Connections via a DataSource
JDBC vs Spring JDBC
Spring - Calling Stored Procedures
Spring - Updating
Spring - Updating With Named Parameters
Spring - Batch Updates

COBOL PROGRAMMING FOR PERFORMANCE
Development Cycle With Db2
Db2 Bind
Rebind
Binding Using Jcl
Catalog Information for Plans / Packages
Useful Bind Parameters
Package Management Enhancement
Package Switching
Deleting Old Packages
Retrieving System Registers
The Optimize Statement
Fetch First 'n' Rows Only Clause
Fetch First vs Optimize For
Non-Scrollable Cursors
Scrollable Cursors
Declaring a Scrollable Cursor
Insensitive Cursor
Sensitive Static Cursor with Insensitive Fetch
Sensitive Static Cursor with Sensitive Fetch
Sensitive Dynamic Cursor
Asensitive Cursor
Scrollable vs Non-scrollable Cursors
Updatable Cursors
Declaring a Scrollable Cursor
Fetching from a Scrollable Cursor
Absolute Fetching Examples
Relative Fetching Examples
Sensitive Fetches - Update and Delete Holes
Positioned Updates Using A Sensitive Cursor
Rowset Processing - Multi-row Fetch & Insert
Rowset Processing - Declare Cursor
Rowset Processing - Fetching Rowsets
Rowset Processing - Fetch Examples
Rowset Processing - Host Variable Arrays
Rowset Processing - Update and Delete Holes
Rowset Processing - Partial Rowsets
Rowset Processing - Positioned Update
Rowset Processing - Positioned Delete
Rowset Processing - Multi-row Insert
Rowset Processing - Multi-row Insert Syntax
Rowset Processing - Multi-Row Insert Example
Rowset Processing - Get Diagnostics
Get Diagnostics - Statement Information
Get Diagnostics - Examples
Diagnostic Information for Multi-Row Fetch
Get Diagnostics Fetch Example
Diagnostic Information for Multi-Row Insert
Get Diagnostics Insert Example
Rowset Processing - The Merge Statement
Merge Statement Example
Get Diagnostics - Merge Example
Select from Merge

LOCKING
Ims Resource Lock Manager (Irlm)
Db2 Locking Methods
The Lock Table Statement
Lock Modes
When Locks Are Acquired
When Locks Are Released
Isolation Levels
Skip Locked Data
V10 Locking Enhancement - Access To Committed Data
Keep Update Locks
Declaring Cursors With Hold
Savepoints
Savepoint Definition
Savepoints - Considerations and Restrictions

STORING XML DATA
eXtensible Markup Language Overview
Well Formed Documents
XML Data
Integration of XML Data
XML Serialization Function
XML Publishing Function
XML Data Model
XML Parser
XML Schema Repository (XSR)
XPATH
XPath Axes
XPath Functions
XPath Indexes

MATERIALIZED QUERY TABLES
What Are Materialized Query Tables?
MQT Features
Creating an MQT
Create MQT Example
Altering an MQT
Alter MQT Example
MQT Fullselect Features / Restrictions
Refresh Table
Populating User Maintained MQTs
Automatic Query Rewrite using MQT
Enabling Automatic Query Rewrite
Enabling Automatic Query Rewrite - DDL Options
Enabling Automatic Query Rewrite - Special Registers
AQR - Using Both Registers
Enabling Automatic Query Rewrite - System Properties
MQTs and Referential Integrity
MQTs and RI - Informational Constraints

TRIGGERS
Triggers
Trigger Parts
Before and After Triggers
Trigger Examples
Allowable Combinations
Error Handling
Trigger Cascading
Ordering of Multiple Triggers
Trigger Authorisation
Catalog Information for Triggers
Instead Of Triggers
Instead Of Triggers - Restrictions
Instead Of Triggers - Authorisation
Instead Of Triggers - Catalog Changes
Removing Triggers

REFERENTIAL INTEGRITY
What Is Referential Integrity?
Referential Constraint Rules
Constraint Names
Performance of Referential Integrity
What is Check Pending Status?
How Do I Reset Check Pending Status?

ACCESSING THE CATALOG
Catalog Contents
Catalog Table Reference
Catalog Access Examples


Course Enquiry