AUDIENCE:   Database Administrators, Production DBAs, System Administrators, Capacity Planners and Performance Analysts. PREREQUISITES:   At least nine months of database administration related responsibilities in a DB2 environment at a previous release level. DURATION:   2 days. Hands on. OBJECTIVES:   The chief aim of this course is to establish a working knowledge of DB2 v8's new features and enhancements, as they fall under the following headings: How DB2 exploits z/OS Scalability Availability SQL Utilities Multi-level security Locking & concurrency Data sharing Materialised Query Tables Data partitioning & secondary indexing Index options Systems & application performance Planning for migration and fallback 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 conversant with the scope and limits of DB2 V8's new features so as to assist clear thinking as per the topics above. COURSE CONTENT:   1. z/OS REVIEW 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. COMPARATIVE ITEMS (i.e., V7 vs. V8) Scalability Availability SQL Utilities Performance Data sharing e-business Data warehousing Migration and fallback 4. SCALABILITY 64-bit support More virtual storage More partitions More tables per join More log data sets 5. AVAILABILITY Data (table) partitioning ALTER table -- data type Secondary indexes Data partitioning secondary index (DPSI) Dynamic schema evolution DB2 subsystem point-in-time recovery Larger than 4 KB control intervals Dynamic ZPARM management enhancement Clustering index Table space versioning Index versioning 6. SQL Longer object and other names Common table expressions 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 Best practices 7. UTILITIES Online schema Delimited LOAD/UNLOAD UNICODE Distribution statistics Backup/restore DB2 subsystem COPY/RECOVERY of table space Parallelism Restore DB2 subsystem to a point-in-time REORG … SORTDATA REORG & BUILD2 Best practices 8. PERFORMANCE Dissimilar data types comparison Materialized Query Tables (MQTs) Multi-row INSERT/FETCH Cost-based parallel sorts Sparse indexing and star joins Long vs. variable length keys Backward/forward index scan Triggers Volatile tables NOT PADDED index clause Setting VDWQT to 0 Page stealing algorithm Checkpoint frequency Optimising EDM pool space Sorts and VPSEQT threshold Log data set striping -- active logs Stage 1 vs. Stage 2 predicates BIND/REBIND REOPT option Data-partition secondary optimisation Volatile tables Catalog statistics (e.g., LOWKEY, HIGHKEY) Clustering and performance DSMAX Best practices 9. DATA SHARING Lock propagation Data sharing workload Batch updates and index splits Logical Page List (LPL) recovery In doubt units of work IMMEDIATE Write BIND option -DISPLAY GROUPBUFFER POOL Best practices 10. 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 11. LOCKING & CONCURRENCY PERFORMANCE Partition secondary indexes Volatile tables Use of sequence objects Partition table space IRLM (PC and MAXCSA) SKIP UNCOMM INSERTS Isolation level and CURRENTDATA Best practices 12. IMPLEMENTING MATERIALIZED QUERY TABLES (MQTs) Defining Populating Maintaining Automatic query rewrite Best practices 13. EXPLAIN TOOLS PLAN_TABLE DSN_STATEMNT_TABLE DSN_FUNCTION_TABLE Visual Explain 14. UNICODE What is it? Parsing Multiple CCSIDs per SQL statement ODBC support Program preparation Utility DB2 Catalog 15. e-BUSINESS Universal driver for SQLJ & JDBC UNICODE ODBC enhancements XML publishing CURRENT PACKAGE PATH special register SQL procedures 16. DATA WAREHOUSING Sparse indexing In-memory workfile Snowflake handling Join with up to 225 tables Best practices 17. INSTALLATION & MIGRATION Version currency Installation changes Migration modes Catalog changes