Module 2: QMF Queries

QMF- Queries

A QMF query is a saved SQL statement (or a guided prompted query) stored in the QMF catalog. You write it once, run it any time with RUN QUERY, share it with coworkers, and use it as the data source for reports and procedures. This module uses the QMF sample tables Q.STAFF and Q.ORG.

SQL query vs prompted query

SQL query: you type the SQL yourself on the SQL QUERY panel — full power of Db2 SQL, the normal choice for anyone who knows SQL.
Prompted query: QMF walks you through step-by-step panels (choose tables, choose columns, choose row conditions, choose sort) and builds the SQL for you — ideal for beginners or quick ad-hoc questions.
A prompted query can be converted to SQL at any time, so you can start guided and finish by hand-editing.
Both kinds are saved the same way and run with the same RUN QUERY command.

Writing a query on the SQL QUERY panel

From the home panel, type your SQL on the SQL QUERY panel (or type RESET QUERY first to clear any previous query).
Press Enter to check the syntax without running; use the RUN function key (or type RUN on the command line) to execute it.
The answer rows appear on the REPORT panel, formatted by the current form (FORM.MAIN by default).

SELECT ID, NAME, DEPT, SALARY FROM Q.STAFF WHERE DEPT = 38 ORDER BY SALARY DESC

A second example: join and aggregate

SELECT O.DEPTNAME, COUNT(*) AS HEADCOUNT, AVG(S.SALARY) AS AVG_SALARY FROM Q.STAFF S, Q.ORG O WHERE S.DEPT = O.DEPTNUMB GROUP BY O.DEPTNAME ORDER BY HEADCOUNT DESC

Any valid Db2 SQL works: joins, subqueries, GROUP BY, HAVING, scalar functions, and set operators like UNION.
Column labels you define with AS become the report column headings until a form overrides them.

Running a query

RUN (command line or PF key) runs the query currently on the SQL QUERY panel.
RUN QUERY Q1 runs a saved query named Q1 without displaying it first.
RUN QUERY Q1 (FORM=F1 runs the query and formats the report with form F1 instead of the default.
After the run, the REPORT panel shows the rows; use the UP/DOWN PF keys (or UP/DOWN commands) to scroll.

Saving a query

SAVE QUERY AS Q1 SAVE QUERY AS Q1 (REPLACE=YES

SAVE QUERY AS name stores the current SQL in the QMF catalog under your ID; the name can be up to 18 characters.
To overwrite an existing query, add (REPLACE=YES — without it, QMF refuses to clobber the old version.
Saved queries can be shared: SAVE QUERY AS Q1 (SHARE=YES lets other users run (not change) it.

Managing queries: display, list, erase

DISPLAY QUERY Q1 LIST QUERIES LIST QUERIES (OWNER=JONES ERASE QUERY Q1 (CONFIRM=NO RESET QUERY

DISPLAY QUERY name brings a saved query back onto the SQL QUERY panel for viewing or editing.
LIST QUERIES shows your saved queries; add (OWNER=someone) to see another user's shared queries.
ERASE QUERY name deletes a saved query (CONFIRM=NO skips the confirmation panel).
RESET QUERY clears the SQL QUERY panel so you can start a fresh query.

Backward

Forward

Let's understand Mainframe