| Course Code: |
RT 499 |
| Course Abstract: |
This course teaches the intricacies of SQL using an interactive approach on a z/OS mainframe. Many of the skills learned herein are also applicable to a Unix/Linux/Windows DB2 UDB server. This courseware is designed for people with SQL experience. It covers effective SQL design, intermediate and advanced SQL topics as well as an overview of DB2 for z/OS version 9 facilities. Participants need access to a current release of DB2 in the z/OS mainframe environment via SPUFI. |
| Audience: |
This course is designed for DB2 application DBAs and DB2 mainframe programmers that need an understanding of how SQL is effectively and efficiently used to access data in a z/OS DB2 environment and what effect the revolutionary version 9 ‘viper’ engine will have on application design and performance. |
| Duration: |
4 days |
| Learning Outcomes: |
Upon completion of this course, the participant will be able to:
> Utilize SQL features
> Identify DB2 version 9 facilities |
| Course Topics: |
Effective design
The 'thought process' of developing an effective query
The SQL common denominator among different engines
The use of the elusive ALL, ANY, and EXIST parameters
The efficiencies and the inefficiencies in the SQL language
What correlation variables can do to simplify query processing
When SCALAR and AGGREGATE FUNCTIONs should be used
How to use the many types of sub query constructs most effectively
When which one of the four JOIN types should be used for efficiencies
The typical SQL environments, Unix/Linux/Windows, and Mainframes
Boolean constructs, CASE constructs, and multiple condition constructs
How NULLs are helpful to reduce processing time and query complexity
When to utilize the 'nested table expression' to improve query performance
What and when the UNION operation is most effective vs. other techniques
The runtime LOCKING issues with INSERT, UPDATE and DELETE operations
When to (and not to) use DISTINCT, IN, LIKE, BETWEEN and other predicates
How DATE and TIME data types can effectively be used for audits and compliance
How XQUERY and other DB2 v9 facilities will effect future application |
| Prerequisites: |
Previous SQL experience is required. |