AUDIENCE:   IT personnel and developers who have been exposed to using DB2 under z/OS and wish to understand more of the SQL performance and tuning aspects of the product. PREREQUISITES:   Those attending the course should have used DB2 in the past, and preferably attended our DB2 SQL Programming course. DURATION:   3 days. Hands on. OBJECTIVES:   The course has been designed, to consolidate on the students knowledge of SQL, understand all aspects of EXPLAIN, be aware of efficiency, and to understand the new facilities available up to DB2 9. The course will contain numerous practical exercises to consolidate the theory. COURSE CONTENT:   System architecture DBA EDM pool Data transfer Storage Tablespace Global Tables Tables Log Manager Traces Catalog and History Tables Directory Advanced SQL Coding efficient SQL Scalar functions DATE/TIME functions Column functions Special registers FETCH only CASE Table joins SUBSELECTS Correlated SUBSELECTS UNIONS Joins Nested tables Indexes and performance Page Record identifier Indexes Type1/2 Splits Explain & Filter factor Optimizer EXPLAIN DSN_STATEMNT_TABLE Catalog statistics Single column/ Multiple columns Filter factor Access paths Single table access Multiple table access Types of access paths Coding efficient SQL Data Handling Modification of SQL by DB2 Coding additional predicates Using indexes Coding efficient Joins SUBQUERIES VIEWS PLAN analysis Influencing Access Paths Online Performance Guidelines Coding techniques Efficient Browse Multiple columns indexes Online updates Batch performance guidelines Locking & Performance Understanding locking Contention Locking in a shared environment Global deadlocks Packages/Plans Terms/terminology BIND OPTIONS Data structures Triggers Stored Procedures IC07/01