DB2 SQL Workshop

Course Objectives
The aim of this Course is to provide the delegate with the necessary skills to perform simple and complex queries using DB2I / SPUFI. On completion of this Course the student will be able to:

• understand how tables are defined
• describe the data types available when defining DB2 columns
• describe the importance of an Index for certain queries
• use DB2I and SPUFI
• write SELECT, UPDATE, DELETE and INSERT SQL statements
• join tables together
• use inner joins and outer joins
• write non-correlated and correlated subqueries
• use DB2 functions and the CASE statement

Course Details

• INTRODUCTION TO DB2
• The Relational Model
• Data Representation
• The DB2 Environment
• Data Definition Language
• The DB2 Table
• Column Types
• INTEGER
• SMALLINT
• CHAR
• VARCHAR
• DATE
• DATE formats
• TIME
• TIMESTAMP
• Null Values
• Default Values
• Indexes
• Index Columns
• Tablespace scan vs. Index access
• DB2 INTERACTIVE (DB2I)
• DB2I Options
• SPUFI - SQL Processor Using File Input
• Running Queries
• SPUFI Defaults
• SPUFI - Setting AUTOCOMMIT to NO
• DB2 Commands
• DB2 Utilities
• The DB2I Defaults Panel
• DATA MANIPULATION LANGUAGE
• SQL - Structured Query Language
• DB2 Environments
• SQL Features
• SQL Query Results
• The SELECT Statement
• The ''As'' Clause
• Column concatenation
• Expressions
• Functions
• Special Registers
• The WHERE Clause
• Special Operators
• NOT Operand
• IN Operand
• LIKE Operand
• BETWEEN Operand
• Statements Using Nulls
• Column Functions
• Using ''Distinct''
• Multiple Column IN Predicate
• Multiple Column Subselect
• Multiple Column Basic Predicate
• The GROUP BY Clause
• The HAVING Clause
• The ORDER BY Clause
• Fetch First ''n'' Rows Only Clause
• Special Registers
• Current Date
• Current Time
• Current Timestamp
• User Keyword
• The UPDATE Statement
• Update with Subselect
• The DELETE Statement
• Self-Referencing UPDATE / DELETE
• The INSERT Statement
• The Mass Insert Statement
• Functions
• Scalar Functions
• Function Examples
• Date, Time and Timestamp Functions
• The Case Statement
• JOINS
• Cartesian Joins
• Inner Joins
• Outer Joins
• Nested Table Expression
• The UNION Statement
• Fullselects and Subselects
• Union Example in a View
• Union Example in a Table Specification
• Union Example within a Where Clause
• Union Example within an Insert or Update
• Subqueries
• Subqueries Using in
• Exists
• The ''All'' Subquery
• The ''Any'' Or ''Some'' Subquery
• Fetch First ''n'' Rows Only Clause
• Fetch First vs Optimize For
• Using Fetch First for Singleton Selects
• SQL VERSION 8 ENHANCEMENTS
• Select from Insert
• Select from Insert Example
• Result Table Rows from the Insert Statement
• Select From Insert in a Cursor
• Select From Insert - New Order By Option
• Select From Insert - Error Processing
• Select From Insert - Using Cursors With Hold
• Select From Insert - Using Savepoints
• Updates and Deletes against Result Table Rows
• Select From Insert - Considerations
• Expressions / Functions in Group By
• Qualified Column names in Insert and Update
• Is Not Distinct From
• Multiple Distinct
• Scalar Fullselect
• Scalar Fullselect Examples
• Scalar Fullselect Restrictions
• Common Table Expressions
• Writing a Common Table Expression
• Common Table Expression Example
• Common Table Expression Considerations
• Recursive SQL
• Recursive SQL Example
• Recursive SQL - Controlling Depth of Recursion
• Read Only Using Update Locks
• Session Variables
• New Special Registrers
• Course Environment Development will be performed using:

• IBM Mainframe

Course Format: The course contains many practical exercises to ensure familiarity with the product. Students write many queries to read, join and manipulate DB2 data. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course. An IBM SQL REFERENCE SUMMARY HANDBOOK is provided free to all attendees as part of the training course.