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
 
  1. Introduction to DB2
            Course Introduction
        1. DB2 - Concepts, and Terminology
  2. 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
 
  1. DB2 Performance Considerations - Overview
  2. DB2 Catalog and Runstats
  3. Concepts In DB2 Efficiency
  4. DB2 Explain and Visual Explain
  5. DB2 Additional Version 6 features - User Defined Distinct Types / User Defined Functions (Appendix K), Triggers (Appendix M), New Scalar/ColumnFunctions (Appendix J)
  6. Other Advanced Topics - Recursive SQL, Group By - CUBE, ROLLUP
  7. Hands-on Lab - SQL Review and Refinement
  8. 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  


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy