DB07 - QMF / SQL Workshop - 3 Days

Course Description

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.

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

Environment

Development will be performed using:

  • QMF running on an IBM Mainframe

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

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.

Course Enquiry