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

Virtual or On-site course enquiry (from £165 pp per day)

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.

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

Environment

The course runs in the following environment:

IBM Mainframe
DB2 for z/OS Versions 10, 11 or 12

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 Format

Practical database set-up and SQL explain sessions make up a large part of the course, allowing delegates to demonstrate and reinforce the lectures given. During these sessions the delegate will run many explains on example queries and programs. Delegates are also invited to bring along their own SQL statements to be used as case studies during the course

The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.

Get in touch