OVERVIEW
This course provides in-depth study of SQL especially joins and subqueries, and will strengthened these skills through extensive workshops and classroom lecture. A solid introduction to application-based DB2 performance tuning through the use of EXPLAIN to examine the DB2 optimizer’s access path decisions.
OBJECTIVES
- Correctly code all relational operations including join, union, intersect and difference.
- Understand and code outer joins and the null implications
- Code subqueries and correlated subqueries and understand what they do.
- Use all SQL operators, including LIKE, IN, BETWEEN, UNION and EXISTS.
- Use DB2 scalar functions such as SUBSTR, DATE, INTEGER and FLOAT.
- Use SQL aggregate functions such as SUM, MIN and MAX.
- Query the DB2 catalog and gather information on any DB2 object.
- Identify indexable predicates to speed processing.
- Use the DB2 EXPLAIN facility to analyze the performance characteristics of a query.
- Understand the use of indexes and other techniques to code efficient SQL for optimal performance.
- Identify the best choices for DB2 object definition using Data Definition Language (DDL).
- Review the features of procedures, user-defined functions and triggers in DB2.
INTENDED AUDIENCE
This course is designed for people who will be designing, coding, maintaining and/or tuning application programs using DB2, primarily application developers and designers.
PREREQUISITES
Experience with SQL and/or training in SQL and DB2 Programming is needed to get the most out of this class.
FORMAT/MATERIALS
This course includes a combination of lecture, discussion and numerous computer lab exercises. Each participant will receive a copy of the course manual, which will prove to be a valuable reference after class. There is an optional textbook available.
CONTENT
A. Understanding DB2 Architecture
1. DB2 Objects
2. DB2 Catalog
3. Locking and IRLM
4. DBAS and optimization
5. Understanding Foreign keys and referential integrity
6. Using DDL to create objects
B. SELECT Statement Overview
1. Aggregate functions and the GROUP BY clause
2. Scalar functions and the Special Registers
3. Updating with SQL: INSERT, UPDATE and DELETE statements
C. Joining Tables
1. Join 3 or more tables
2. Join table to itself
3. Outer join (left, right, full)
4. Implications of Nulls in outer join
D. Subqueries
1. Simple subqueries
2. Correlated subqueries
3. Use of ANY, ALL, SOME, EXISTS
4. Coding differences
5. Coding intersections
E. Coding the UNION Operation
1. Use of UNIONs for ANSI standard
2. Coding outer join with a UNION
3. Using CASE as an alternative
F. Querying the DB2 Catalog
G. Performance Tuning
1. Role of Indexes
2. Denormalization Techniques
3. Database Guidelines
4. Tablespace Guidelines
5. Locksize Recommendations
6. Freespace recommendations
7. Table Definition Guidelines
8. Indexing Guidelines
9. Relative Join Performance
10. SQL Performance Guidelines
H. Using EXPLAIN to Analyze a Query
I. Miscellaneous – Embedded SQL
J. Miscellaneous – Triggers Procedures, and user-defined functions
K. Miscellaneous – Customized to focus on Customer needs
L. Static vs Dynamic SQL
M. DB Version 8 enhancements from developer view
N. SQL procedures
O. Distributed Access DB2 Connect – overview
P. Locking and Contention issues