Overview
Review SQL - Basic Select, Scalar Functions, Joins, SubQueries, Unions, and Insert/Update/Selects.
Learn / practice features of the current version of DB2 and look into the next version of DB2.
Review and practice Advanced SQL features.
LEARNING OBJECTIVES
As a result of this class, the attendee will be able to -
- SELECT rows of data from DB2 tables
- Use QMF and/or SPUFI to "run" SQL statements
- Use the WHERE clause to SELECT specific rows of a DB2 table
- Build queries using the ORDER BY, GROUP BY, HAVING clauses
- JOIN tables together to get information from each
- "Change" tables using the INSERT, UPDATE, DELETE statements
- Describe the security provided by the GRANT and REVOKE
- Discuss the design considerations of REFERENTIAL INTEGRITY
- Discuss overall DB2 PERFORMANCE considerations
- Run the EXPLAIN PLAN SQL statement and analyze results
- Discuss additional SQL features of the latest DB2 Feature
- Review the DB2 Catalog and Runstats Utility
- Discuss Concepts in DB2 Efficiency
- Discuss the DB2 Explain and Visual Explain
- Discuss other Advanced Topics -Recursive SQL, Group By - CUBE, ROLLUP
Audience
Experienced Data Processing personnel who have used SQL and need to refresh / deepen their current skills and learn and use "advanced" SQL topics.
Prerequisites
At least six months experience using DB2 SQL.
Course duration
2 days
Course outline
|
DAY 1
|
| |
- Introduction to DB2
- Course Introduction
- 1. DB2 - Concepts, and Terminology
- Structured Query Language (SQL)
- SQL 1 - The SELECT Statement
- Database 2 Interactive
- SQL 2 - Special Features
- SQL 3 - ORDER BY, GROUP BY, HAVING
- SQL 4 - Join, Sub-select, UNION
- SQL 5 - Data Definition Language
- SQL 6 - INSERT, UPDATE, DELETE
- SQL 7 - Data Control Language
- SQL Programming V - Other Programming Considerations
|
|
DAY 2 |
| |
- DB2 Performance Considerations - Overview
- DB2 Catalog and Runstats
- Concepts In DB2 Efficiency
- DB2 Explain and Visual Explain
- DB2 Additional Version 6 features - User Defined Distinct Types / User Defined Functions (Appendix K), Triggers (Appendix M), New Scalar/ColumnFunctions (Appendix J)
- Other Advanced Topics - Recursive SQL, Group By - CUBE, ROLLUP
- Hands-on Lab - SQL Review and Refinement
- Question and Answer Session
|
Appendices
|
| Appendix A - IBM Sample Tables |
Appendix J - Version 6 Functions Summary |
| Appendix B - Bibliography |
Appendix K - User Defined Data Types (UDT) and User Defined Functions UDF |
| Appendix C - Advanced SQL Workshop |
Appendix L - Cast Testing |
| Appendix D - EXPLAIN PLAN Statement and PLAN_TABLE and DSN_STATEMNT_TABLE |
Appendix M - Trigger Introduction |
| Appendix E - Hints to Success |
Appendix N - DB2 for WINDOWs Overview |
| Appendix F - SQLCODES / SQLSTATES |
Appendix V6 - Summary of Changes - DB2 UDB Version 6 |
| Appendix G - Query Management Facility (QMF) Overview |
Appendix V7 - Summary of Changes - DB2 UDB Version 7 |
| Appendix H - Stored procedures - Version 5 |
Appendix V8 - Summary of Changes - DB2 UDB Version 8 |
| Appendix I - "Common" SQL Errors |
|
|
|
