DB2 for z/OS SQL Advanced

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)
1. SQL 1 - The SELECT Statement
2. Database 2 Interactive
3. SQL 2 - Special Features
4. SQL 3 - ORDER BY, GROUP BY, HAVING
5. SQL 4 - Join, Sub-select, UNION
6. SQL 5 - Data Definition Language
7. SQL 6 - INSERT, UPDATE, DELETE
8. SQL 7 - Data Control Language
9. 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