DB2 Application Performance and Tuning

Case Training
Bristol, Edinburgh, Leeds, London, United Kingdom

Course Format: Classroom | E-learning | Virtual Class | Online | On-site | Blended | Self-paced

Course Description:

'' This course provides advanced DB2 training, and is aimed at staff requiring a detailed knowledge of the issues involved in writing, monitoring and tuning DB2 SQL queries and applications to perform efficiently. In addition, the course covers some wider areas that can affect application performance, such as Cursor Operations, Locking and Referential Integrity.

Pre-requisites
The student should be familiar with writing SQL statements SELECT, INSERT, UPDATE and DELETE and should also have a working knowledge of Application Program construction.

Course Objectives
The course provides information relating to the coding of efficient SQL statements together with the wider issues that affect the performance of DB2 z/OS applications. The major part of the course focuses on ensuring that applications perform well in a production environment. The DB2 Explain facility is used extensively to demonstrate how SQL performance may be monitored and tuned. On completion of this course the student will be able to:

understand the performance issues involved in constructing a system
implement an efficient tablespace, table and index design
understand DB2 locking issues
choose the correct locking strategy
design and use Referential Integrity
design and use Materialized Query Tables
understand how Cursors are processed
understand and use Plans and Packages effectively
understand the importance of RUNSTATS
understand the process of optimisation
use optimisation hints
describe the various access path techniques that DB2 is able to use
run and understand the Explain Facility
use Indexable and Stage 1 predicates to write efficient SQL statements
Course Environment
The course runs in the following environment:
IBM Mainframe
Course Details
PHYSICAL DATABASE PERFORMANCE ISSUES
The Structure Of DB2 Objects
Database Definition
Tablespaces
Pages
Tablespace Organisation
Page And Row Organisation
The Simple Tablespace
The Segmented Tablespace
The Partitioned Tablespace
Maximum Number Of Partitions
The Lob Tablespace
Create Tablespace Parameters:
Alter Tablespace
Table Definition
DB2 Column Types
Design Tips for Columns
Null Values
User Defined Default Values
Nulls - Design Tips
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables - Comparisons
Table-Controlled Partitioning
DB2 Indexes
Index Organisation - The B Tree Index
Backwards Index Scan
Index Clustering
Clustering With Partitioned Tables
Clustering Within Partition
Changing the Clustering Sequence
Non-Unique Indexes
Index Definition
Data Partitioned Secondary Indexes
Creating a DPSI
Design Considerations - DPSI Problems
Partitioning Indexes
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
Row Placement Rules
Select Statements
The Where Clause
Special Operators
Special Operators - Examples
Is Not Distinct From
SQL Built-In Column Functions
Column Function Performance Notes
Using ’Distinct’
Multiple 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
Select from Insert
Select from Insert Example
Result Table Rows from the Insert Statement
Select From Insert in a Cursor
Select From Insert - New Order By Option
Select From Insert - Error Processing
Scalar Functions
Function Examples
The Case Statement
Outer Joins
Outer Join Syntax
Outer Join - Joining More Than 2 Tables
SQL Union
Subqueries
Subqueries Using In
Exists
Common Table Expressions
Writing a Common Table Expression
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
IDENTITY COLUMNS AND SEQUENCES
Identity Column Review
Identity Columns - Examples
Identity Column Enhancements
Altering Identity Columns
Identity Columns - New Version 8 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
Sequence Authorities
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Consumed Values / Gaps in a Sequence
Duplicate Sequence Values
Sequence Cycle Considerations
Defining a Constant Sequence
Cache Considerations
Sequences and Identity Columns Comparison
PROGRAMMING FOR PERFORMANCE
Development Cycle With DB2
DB2 Bind
Rebind
Packages Overview
Explanation Of Packages
Advantages Of Using Packages
Bind Parameters
Binding Using Jcl
Identifying A Collection Within A Program
Cursor Operations
Declare Cursor
Open Cursor
Fetch A Row
Row Update
Row Deletion
Close Cursor
With Hold Option
The Optimize Statement
Fetch First ’n’ Rows Only Clause
Fetch First vs Optimize For
Multi-row Fetch
New Syntax for Declare Cursor
Fetching Rowsets
Fetch Examples
Host Variable Arrays
Catering for Update and Delete Holes
Partial Rowsets
Rowsets - SQLCA
Positioned Update
Positioned Delete
Multi-row Insert
Multi-row Insert Syntax
Multi-Row Insert Example
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
Retrieving System Registers
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 Dynamic Cursors - Fetching Rows
Sensitive Fetches - Update and Delete Holes
Positioned Updates Using A Sensitive Cursor
Scrollable Cursor Considerations
LOCKING
Implications Of Concurrent Processing
Ims Resource Lock Manager (IRLM)
DB2 Locking Methods
The Lock Table Statement
Lock Modes
When Locks Are Acquired
When Locks Are Released
Isolation Levels
Controlling The Isolation Level At SQL Level
Keep Update Locks
Unit Of Work In TSO
Commit And Rollback
Declaring Cursors With Hold
CICS Issues
IMS Issues
Savepoints
Savepoint Definition
Savepoints - Considerations and Restrictions
Transaction Deadlocks
Design Considerations
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
Enabling Automati ...''

Phone:01275 859666

School Address:

+ 44 (0) 1275 859666

Jobs & Resumes: Bristol, Edinburgh, Leeds, London
Houses & Roommates: Bristol, Edinburgh, Leeds, London

CA-Easytrieve Basic Reporting
CA-Easytrieve Consolidation
CA-Easytrieve Reporting and File Manipulation
CA-TELON Advanced Programming
CICS Introduction for Programmers
CICS for Applications Programmers (using VSAM)
COBOL
DB2 Advanced Programming
DB2 Application Data Recovery
DB2 Development for Applications Programmers
DB2 SQL Workshop
DB2 UDB Development for Applications Programmers
DB2 UDB Development for Database Administrators
E-Commerce
Enterprise JavaBeans Workshop
File-AID for DB2
File-AID for z/OS
HPS / AppBuilder Applications Development

Notice: The course description on this page was captured from the Internet as historical reference or submitted by visitors. It was archived statically and not updated from day to day.
 

Tips: Schools can use this unlimited free web marketing service by submitting their course descriptions and school introductions. Two outbound links will be granted to each posting.