AUDIENCE:   Developers, Programmers and DBAs using the IBM DB2 UDB product. PREREQUISITES:   At least six months experience as a developer with SQL experience at a previous release level of DB2. DURATION:   2 days. Hands on. OBJECTIVES:   This presentation aims at developing a clear understanding of DB2 v8's SQL and programming enhancements in terms of: z/OS concepts DB2 V8 exploitation of z/OS Interactive and static SQL Application programming changes Multiple level security Enhanced table design options Enhanced index design options Locking and concurrency Explain tools, including Visual Explain Query tuning guidelines Developer friendly utilities As DB2 V7 is a requisite to DB2 V8, each lesson conveniently discusses the differences in and similarities to the other. Therefore, participants with a limited knowledge of DB2 V7 should not be handicapped. Upon completion of this presentation, the delegate should be able to code interactive and static SQL to exploit the z/OS architecture. Additionally, best practices and tuning tips will be discussed as these relate to design and programming. COURSE CONTENT:   1. INTRODUCTION TO z/OS Services Hardware Base features Optimal features Security Storage management UNIX services 64-bit addressing Size and number notations 2. DB2 EXPLOITATION OF z/OS Hardware/software requisites DB2 limits DBM1 address space considerations 64-bit virtual SQL enhancement summary Materialized Query Tables (MQT) Very Large Database (VLDB) UNICODE Schema evolution Large buffer pools 3. SQL & PROGRAMMING Longer object and other names Common table expressions Recursive SQL Dynamic scrollable cursors Row-set positioned cursors Multi-row FETCH and INSERT GET DIAGNOSTICS Scalar fullselect Select from insert Qualified column names in insert/update GROUP BY expressions Multiple DISTINCTS Sequence objects vs. identity columns Security granularity Compile-time ASCII flag Maximum tables per join Maximum SQL size DISTINCT predicate in comparisons Best practices 4. MULTI-LEVEL SECURITY What is it? Users and objects Security labels Dominance relationship Write-down control How to implement with DB2 Row granularity DB2 data access Utility considerations Global temporary table considerations Materialized Query Table (MQT) considerations Constraint considerations Trigger considerations 5. IMPLEMENTING MATERIALIZED QUERY TABLES (MQTs) Defining Populating Maintaining Automatic query rewrite Best practices 6. TABLE DESIGN CONSIDERATIONS Data partitioning Clustered indexes Secondary indexes Data Partitioning Secondary Index (DPSI) Index/Table space versioning ALTER TABLE -- data types Volatile tables Global temporary tables vs. declared temporary tables Best practices 7. LOCKING & CONCURRENCY Partitioned tables Isolation levels, which one? SKIP UNCOMM INSERTS BIND/REBIND options (e.g., CURRENTDATA) Best practices 8. EFFICIENT SQL GUIDELINES SUMMARY Number of SQL calls Number of rows searched Number of columns retrieved Stage 1 vs. Stage 2 predicates Static vs. dynamic SQL Joins Local vs. Join predicate Nested table expressions CASE vs. UNION Singleton select vs. Cursor OPTIMIZE FOR n ROWS OPTIMIZE FOR FIRST n ROWS Controlling sorts 9. EXPLAIN TOOLS PLAN_TABLE DSN_STATEMNT_TABLE DSN_FUNCTION_TABLE Visual Explain 10. DEVELOPER-FRIENDLY UTILITIES Online LOAD RESUME REORG DISCARD REORG UNLOAD EXTERNAL SQL Within Utilities Utilities Best Practices