The aim of the course is to give the participants an understanding of the major performance issues in the DB2 application domain.
This course is for the experienced DB2 developer, this course focuses on the advanced SQL and performance related issues. Additionally, the DB2 EXPLAIN facility is the central tool used when choosing amongst competing SQL and design alternatives.
- DML Review. Select, Column functions, group by, joins, nested expressions, union and sub queries
- Efficient SQL. Joins vs. sub queries, distinct vs. group by, control sorts, stage 1 vs. stage 2 predicates, EXISTS vs. IN, correlated vs. non-correlated sub queries, cursor repositioning, massive batch updates and commie frequency
- Locking and Concurrency. Lock modes, duration, application vs. DBA effects, controlling SQL codes -911, -913 and -904. Bind and rebind implications
- The optimizer, interpretation and analysis. Select, joins, unions, sub queries
- Table Design. When to use views, when to partition data, when to use as an index, when to use VARCHAR, when to use NULLS, when to use composite index, how and when to use referential integrity and normalize and denormalize
- Practical. Coding SQL, creating indexes, EXPLAIN
English speaking instructor.