DB2 graphic

QMF Version 8

Contents

About this book

How to order DB2 QMF books

How to send comments

Getting acquainted with QMF

Tables, columns, and rows

QMF sample tables

Accessing your data

Starting from the QMF home panel

Ending a QMF session

Issuing QMF commands

Entering a command on the command line

Entering commands using a function key

Entering commands on the command prompt panel

Retrieving a command

Setting up and changing your QMF user profile

Saving and retrieving objects in the database

Saving a QUERY, FORM, or PROC object

Saving a profile

Saving a DATA object as a table

Retrieving an object from the database

Navigating in QMF help

Getting help after making an error

QMF in three quick lessons

Lesson One: Finding the data you need

Lesson Two: Choosing specific data you want

Selecting the columns to display

Selecting the rows to display

Lesson Three: Customizing a report

Where do you go from here?

Displaying a list of database objects

Displaying a list of database objects using the List key

Displaying a list of database objects using the LIST command

Choosing specific objects using selection symbols

Entering commands on the list of database objects

Commands you can use on the list of database objects

Using a placeholder on the list of database objects

Correcting errors when you enter an incorrect command

Returning to the list from another QMF panel

Viewing the data in the database using prompted query

Prompted query panels

Starting prompted query

Selecting tables and columns

Creating a column using expressions

Narrowing row selection using multiple row conditions

Sorting the rows in a query

Displaying data from more than one table

Long table names

Eliminating duplicate rows from the report

Joining multiple tables

Joining multiple columns

Making your query reusable with substitution variables

Running a query and displaying a report

Saving a new query

Canceling a running query

Making changes to a saved query

Retrieving a query from the database

Correcting a query that does not successfully run

Adding information to a query

Changing information in a query

Deleting information from a query

Erasing a saved query

Viewing the SQL equivalent of a prompted query

Converting a prompted query to an SQL query

Viewing the data in the database using SQL statements

Entering an object name on more than one line in the SQL query window

Format of SQL queries

Starting a SQL query

Entering SQL statements and running the query

Selecting columns and tables

Creating a column using expressions

Selecting rows that have no data

Selecting rows using specific character values

Selecting rows using conditions

Selecting rows using opposite conditions

Narrowing row selection using selection symbols

Narrowing row selection using multiple row conditions

Selecting rows If both conditions are true

Selecting rows If either condition is true

Grouping row conditions

Selecting rows using the IN predicate

Eliminating duplicate rows

Sorting the rows in a query

Adding or deleting lines in a SQL query

Displaying data from more than one table

Joining columns in two or more tables

Merging data from multiple tables into a single column

Creating a subquery to retrieve data from more than one table

Writing correlation names

Working with a set of values using SQL column functions

Working with single data values using SQL scalar functions

Converting a value from one data type to another

Formatting dates and times

Allowing the database requestor to control date and time format

Isolating the day, month, or year portion of a date

Isolating the hour, minute, second, or microsecond portion of a time

Finding the length of a value

Displaying parts of a value

Replacing null values in the report with other values

Nesting SQL functions

Nesting scalar functions within scalar functions

Nesting scalar functions within column functions

Nesting column functions within scalar functions

Adding and subtracting dates and times

Rules for date/time addition

Rules for date/time subtraction

Finding the number of days between two dates

Accounting for months with different numbers of days

Using durations to represent date/time intervals

Incrementing and decrementing dates by durations

Subtracting dates

Incrementing and decrementing times by durations

Subtracting times

Incrementing and decrementing timestamps by durations

Using the concatenation operator

Rules for concatenation

Examples using concatenation

Making your query reusable with substitution variables

Saving a new query

Customizing your reports

QMF form panels

Using the QMF default report format

Changing the columns on your report

Adding a new column to a report

Changing the order in which columns are displayed

Changing column headings

Changing column widths and space between columns

Changing the alignment of the column heading and the data

Specifying punctuation for the values in a column

Specifying the way QMF uses values in a column

Adding subtotals to a report

Breaking on a value to add subtotals

Specifying text for the subtotal line on a report

Adding page headings and footings

Specifying fixed columns on a report

Displaying a representative report before you select data

Refining page headings and footings

Using a global variable in a heading or footing

Adding the date, time, and page number to a heading or footing

Changing the alignment of page headings and footings

Adding break segments and text to your report

Adding break heading and footing text to a report

Refining the format of your report with detail blocks

Specifying text to appear at the end of your report

Calculating values to use in a report

Displaying a calculated value on a report

Displaying special conditions on your report

Mixing tabular data with reformatted text

Showing totals across rows in a report

Correcting errors on a form before displaying a report

Saving the report form

Resetting the values on a form panel

Printing your report

CC (carriage control)

Creating a report containing a limited number of ordered entries

Displaying your report as a chart

QMF chart formats

Where QMF report data appears on a chart

How QMF spaces data along the X-axis

Where data appears on pie charts

Chart data size limits

Displaying report data as a chart

Changing a chart using QMF forms

Changing a chart in the ICU

Changing a chart format

Saving a chart format

Specifying a new default chart format

Fixing problems with charts

Printing your chart

Creating a procedure to run QMF commands

Creating a linear procedure

Guidelines for writing linear procedures

Creating a procedure with logic

Guidelines for writing procedures with logic

Running a procedure

Sharing a procedure with other QMF users

Creating reusable procedures with substitution variables

Specifying values for variables on the RUN command

Specifying values for variables using global variables

Specifying values on the RUN command prompt panel

Using REXX variables in procedures with logic

Specifying REXX variables using SAY statements and PULL statements

Passing values to a procedure with logic

REXX variable and substitution variable differences

Using REXX error-handling instructions in procedures with logic

Branching to error-handling subroutines

Using messages with the REXX EXIT instruction

Calling REXX programs from a procedure with logic

Calling REXX programs without substitution variables

Calling REXX programs that contain substitution variables

Connecting to a remote location from a procedure

Writing a procedure that creates a query

Writing a template SQL statement

Passing variables to the template query

Writing a procedure that sets global variables for the template query

Running procedures in batch

Writing batch-mode procedures

Using IMPORT/EXPORT commands

Using the EXIT command in QMF procedures

Effect of errors

Making QMF objects reusable

Creating, changing, and deleting variables from the global variable list

Changing a variable value

Adding a new variable

Removing a variable

Using the global variable list and the CASE option

Creating, changing, and deleting global variables using commands

Creating tables

Planning for your table

Creating a table

Saving and appending to a table

Creating a copy of a table

Creating a view of a table

Creating a synonym for a table or view

Creating an alias for a table or view

Deleting tables, views, synonyms, and aliases

Maintaining the data in your tables

Using the Table Editor

Deciding when to save your data

Specifying column default and null indicator

Redefining the column default and null indicators

Adding data to long fields

Using the previous row as a model

Changing rows in a table using the Table Editor

Selecting the rows to display

Making changes to the rows in a table

Deleting rows from a table using the Table Editor

Ending a Table Editor session

Adding rows to a table using SQL statements

Using the QMF DRAW command to add rows

Writing your own query to add rows

Changing rows in a table using SQL statements

Using the QMF DRAW command to change rows

Writing your own query to change rows

Deleting rows from a table using SQL statements

Copying rows from one table into another using SQL statements

Adding a new column to a table using SQL statements

Working with BLOB, CLOB, and DBCLOB data

Greater than 32 KB support for LOB data type columns

Authorizing access to your tables using SQL statements

Giving users access to your tables

Allowing users to update specific columns in your tables

Revoking access to a table

Entering date and time values by using QMF

Exporting and importing objects

Exporting QMF objects

Exporting QMF objects into TSO

Exporting QMF objects into CMS

Exporting QMF objects into CICS

Exporting QMF reports for use on the Internet

Importing QMF objects

Importing QMF objects from TSO

Importing QMF objects from CMS

Importing QMF objects from CICS

Accessing data at a remote database

Accessing data at a remote database using Remote Unit of Work

Connecting to a remote database using the QMF CONNECT command

Connecting to a remote database using the DSQSDBNM program parameter

Viewing the current database location

Reconnecting to a location

Accessing data at a remote database using distributed unit of work

Using QMF when connected to a database through remote unit of work

Tips and techniques

Function keys and synonyms

Procedures, forms, and queries

Using the QMF CONNECT command to connect to databases

Example 1: How connecting to a new location affects your user ID

Example 2: Connecting to like databases

Example 3: Connecting to unlike databases

Example 4: Connecting to a new location using remote unit of work and distributed unit of work

National Language support in QMF

Bilingual command support

Exporting and importing bilingual forms

Defining DBCS data

How DBCS data looks when displayed

How DBCS data changes the length of names and fields

Data types you can use with DBCS data

Using DBCS data in QMF

Using DBCS data in commands and procedures

Using DBCS data in Input fields

Using DBCS data in queries

Using DBCS in form panels

Exporting DBCS data

Importing DBCS data

Printing DBCS reports

Using QMF with other products

Using DXT end user dialogs

Editing objects from outside QMF using ISPF

Using ISPF from QMF

Inserting a QMF report in a document

Formatting the report

Inserting a report

Information about your editor

Restrictions on the document interface

Using the QMF document interface

Accessing QMF from a VM editor

Accessing a VM editor from QMF

Accessing QMF from a z/OS editor

Accessing a z/OS editor from QMF

Using DB2 stored procedures with QMF

Developing the stored procedures to run from QMF

Running a CALL statement from the SQL QUERY panel

Format of the CALL statement

Examples of running a stored procedure

Running a stored procedure that returns no output parameters

Running a stored procedure that contains input and output parameters

Running a stored procedure that returns a result set

Running a stored procedure that returns multiple result sets

Using QMF forms with result sets returned from stored procedures

QMF sample tables

Appendix A. Query-by-example

Displaying the QBE query panel

Running and saving queries

Listing queries

Drawing example tables

Presenting all the columns of a table

Presenting certain columns of a table

Changing the order of columns

Presenting certain rows of a table

Presenting rows that contain a certain value

Defining example elements

Writing expressions

Using unnamed columns in an example table

Adding conditions to the example table

Adding a CONDITIONS box

Adding a target table

Eliminating duplicate rows

Presenting data from more than one table

Writing queries to be shared

Substitution variables

The USER variable

QMF commands specific to QBE

CONVERT command

ENLARGE command

Keyword reference

ALL. -- Display duplicate rows

AND -- Present on two conditions

Two conditions on one row

AO., AO(n). -- Sort rows in ascending order

Order by more than one column

AVG. -- Calculate the average value

BETWEEN x AND y -- Present values within a range

COUNT. -- Count the number of values in a column

D. -- Delete rows from a table

DO., DO(n). -- Sort rows in descending order

Order by more than one column

G. -- Grouping data

I. -- Insert rows into a table

IN (x,y,z) -- Present certain values in a set

LIKE -- Present on part of a value

Like any single character (underscore)

Like any number of characters (percent sign)

Data-type dependencies

MAX. -- Calculate the maximum value

MIN. -- Calculate the minimum value

NOT -- Present on the opposite of the condition

NULL -- Present rows with missing entries

OR -- Present on either of two conditions

P. -- Present data in a table

Present all the columns in a table

Present some of the columns in a table

Present some of the rows in a table

Present data from multiple tables

Present data dependent on non-presented data

SUM. -- Calculate the total

U. -- Update a row in a table

UNQ. -- Eliminate duplicate rows

USER -- Present rows with a value equal to a user identification

+, -, *, / -- Calculated values

=, ¬=, >, < -- Equality and inequality

QBE exercises with solutions

Solutions to exercises

Appendix B. QMF functions that require specific support

QMF functions not available in CICS

Appendix C. The DB2 QMF High Performance Option

QMF HPO/Manager

QMF HPO/Compiler

Appendix D. Notices

Glossary of Terms and Acronyms

CICS publications

COBOL publications

DB2 Universal Database for z/OS publications

Document Composition Facility (DCF) publications

Distributed Relational Database Architecture (DRDA) publications

Graphical Data Display Manager (GDDM) publications

HIgh Level Assembler (HLASM) publications

Interactive System Productivity Facility (ISPF) publications

OS/390 publications

OS PL/I publications

REXX publications

VM/ESA publications

VSE/ESA publications

Downloads | Library | Support | Support Policy | Terms of use | Feedback

Copyright IBM Corporation 1982,2004

Copyright IBM Corporation 1982, 2004
timestamp

timestamp

Last updated: March, 2004