Target Audience
This training course teaches the delegate all SQL statements required to read, manipulate and join DB2 tables. In addition the course shows how QMF (Query Management Facility) can be used to create queries, reports, forms and procedures. It is aimed at those who have little or no previous QMF and SQL experience.
Pre-requisites
A working knowledge of the z/OS ISPF host environment is advantageous but not essential.
Course Objectives
The aim of this course is to provide the delegate with the necessary skills to perform simple and complex queries using QMF. It also covers QMF report production features in detail.
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
-
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
-
use QMF to create a QUERY, FORM, PROCEDURE and REPORT
-
use QMF to EXPORT and IMPORT data
Course Environment
Development will be performed using:
-
QMF running on an IBM Mainframe
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
- 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'
- 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
- 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
- Subqueries
- Subqueries Using in
- Exists
- The 'All' Subquery
- The 'Any' Or 'Some' Subquery
- QMF
- QMF Home Panel
- The Retrieve Command
- QMF Storage Areas
- Saving an Object
- Retrieving an Object
- Export and Import
- Printing reports
- WRITING QUERIES
- Introduction
- Entering Queries
- The DRAW Command
- The EDIT Command
- QBE Commands
- Enlarge/Reduce
- Using Elements in QBE
- QBe Conditions Box
- Joining Tables in QBE
- Using Variables in SQL Or QBE
- The QMF Table Editor
- FORMATTING REPORTS
- The FORM.MAIN Panel
- The FORM.COLUMNS Panel
- The FORM.PAGE Panel
- The FORM.DETAIL Panel
- The FORM.FINAL Panel
- The FORM.BREAKn Panel
- The FORM.OPTIONS Panel
- The FORM.CONDITIONS Panel
- The FORM.CALC Panel
- The SHOW Command
- PROCEDURES
- Introduction to Procedures
- Creating a Procedure
- Variables in Procedures
Course Format
The course contains many practical exercises to ensure familiarity with the product. Delegates write many queries to read, join and manipulate DB2 data. Using QMF the delegate will write many queries, forms and procedures.
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.
|