Db2 for z/OS QMF Workshop

Duration: 3.0 days

Method: Instructor led, Hands-on workshops

Price: $1800.00

Course Code: DB1060

Audience

This course is intended for anyone who is interested in understanding the capabilities and uses of the Query Management Facility (QMF).

Description

This course provides a comprehensive presentation of the capabilities and uses of QMF. In addition, the course addresses developing QMF queries using the Structured Query Language (SQL) and QMF’s “Prompted Mode” capability. Additional QMF topics include the development and uses of QMF forms, procedures, and Global Variables. Detailed examples are provided throughout the student text, and numerous hands–on workshops are provided to reinforce what is covered in class.

Objectives

Upon successful completion of this course, the student will be able to:

Understand how to develop/execute an SQL or Prompted Mode query
Understand how to develop a QMF query that contains an Expression(s) and/or a Variables(s)
Understand how to develop/execute a QMF form
Understand how to develop a form that contains Breakpoints
Understand how to develop a form that contains Calculations
Understand how to develop a form that contains Group and/or Across capabilities
Understand how to develop/execute a QMF Procedure
Understand how to establish and use Global Variables
Complete numerous “hands–on workshops”

Prerequisites

A basic knowledge of TSO/ISPF.

Topics

I. QMF Overview
- What is QMF?
- QMF’s Operation
- QMF’s Naming Conventions
- QMF’s Query panels
- QMF’s Form panels
II. QMF’s Home, Help and Profile Panels
- QMF’s Home panel
- QMF’s Help panels
- QMF’s Profile panel
III. Creating QMF Queries using SQL
- Creating a query using SQL
- Using a variable within a query
- Executing a query
- Saving (and optionally sharing) a query
- Saving data resulting from executing a query
- Use of the LIST QUERIES command
- Use of the IMPORT and EXPORT commands
- Using TSO/ISPF’s Editor
IV. Simple Select Statements (SQL) used to Develop QMF Queries
- Simple SELECT statements
- SELECT statement with a WHERE clause
- Compound WHERE clause
- SELECT statement with an ORDER BY clause
- Select statement with Literals and Generated columns
- Select statement that supports “Join” operations
- Use of BETWEEN syntax
- Use of IN syntax
- Use of LIKE syntax
- Use of IS NULL syntax
V. Creating QMF Queries using Prompted Mode
- Creating a query using Prompted Mode
- Entering Db2 table information
- Selecting the “columns” option
- Selecting data for the query
- Selecting the “row conditions” option
- Specifying a test condition for the query
- Selecting the “sort” option
- Specifying sort information for the query
- Exit from the Specify Box
- Prompted Query panel
- Show SQL option
- Execute the Prompted Mode query
- Reviewing output and saving the Prompted Mode query
VI. Modifying Prompted Mode Queries/Including an Expression or a Variable in a Prompted Mode Query
- A Prompted Mode query
- Modifying an existing Prompted Mode query
- Including an Expression within a Prompted Mode query
- Including a Variable within a Prompted Mode query
VII. QMF’s Form panels: Features, Usage Codes, Edit Codes
- Features associated with QMF’s Form panels
- Usage Codes associated with FORM.MAIN
- Edit Codes associated with FORM.MAIN
VIII. Using the FORM Panels to Create a Report Layout
- Creating a report layout (Form)
- FORM.MAIN (define a report’s characteristics)
- Designing a custom report layout
- Using the SHOW command panel
- FORM.PAGE (defining page headings and footings)
- FORM.FINAL (defining final totals)
- FORM.BREAKn (defining break information)
- FORM.OPTIONS (defining the report’s appearance)
- Results of executing the Query and Form
- Use of the LIST FORMS command
IX. Using FORM.CALC
- Report results using the FORM.CALC feature
- Developing a query in conjunction with FORM.CALC
- Results of executing the query
- FORM.MAIN panel
- FORM.CALC panel
- FORM.BREAK1 panel
- FORM.FINAL panel
X. Creating QMF Procedures (PROCS)
- Creating a Procedure (PROC)
- PROC panel
- Use of the LIST PROCS command
XI. Global Variables
- Naming Conventions
- Coding Guidelines
- Example: a Global Variable referenced in a Query
- Example: a Global Variable referenced in a Form
- Resetting a Global Variable (RESET GLOBAL … )
- Using the SHOW GLOBALS command
XII. Using FORM.DETAIL
- Report results using the FORM.DETAIL feature
- Developing a query in conjunction with FORM.DETAIL
- Results of executing the query
- FORM.MAIN panel
- FORM.DETAIL panel
- FORM.CALC panel
- FORM.PAGE panel
- FORM.BREAK1 panel
- FORM.FINAL panel
XIII. Aligning Headings/Column Data
- Developing a query in conjunction with the Alignment feature
- Results of executing the query
- FORM.MAIN panel
- Alignment Feature
XIV. Using GROUP and/or ACROSS
- Developing a query in conjunction with GROUP and/or ACROSS
- Results of executing the query
- FORM.MAIN panel (using GROUP)
- Report output using GROUP
- FORM.MAIN panel (using GROUP and ACROSS)
- Report output using GROUP and ACROSS
- Suppressing Summary totals
- Suppressing Final totals
XV. Automatic Reordering of Report Columns
- Developing a query in conjunction with the Automatic Reordering feature
- Results of executing the query
- FORM.MAIN panel
- FORM.BREAK1 panel
- Report output (not using Automatic Reordering)
- Specifying the Automatic Reordering feature
- Report output (using Automatic Reordering)
XVI. QMF Command Syntax Handout
- Each student will be provided with a handout that contains a listing of QMF Commands, including each command’s associated parameters (if any) and syntax requirements.