Mainframes 360
The one stop destination for System Z professionals

Wednesday, April 1, 2009

DB2 QMF How to

Q. What is QMF?
QMF(Query Management Facility) tool used to issue SQL Queries against the IBM DB2 Database System. It is not merely a Data-extraction tool, the data-results can be formatted and presented as Reports, Charts etc. thus offering valuable insight. QMF also has Wizards that can help you to extract data, without even having to write SQL Statements.

There are three different ways, in which you can supply Input to QMF – (1) Entering SQL Query in the Query Editor (2) By using Prompted Queries (3)Query By Example(QBE).
Q. How do I enter an SQL Query?
On your Mainframe System, once you invoke QMF, the welcome screen splashes before you. On the Welcome Screen, press the Program Function Key PF6, to go to the Query Editor

image

Upon pressing the SQL Query Editor starts. The empty area of the screen is where you must type your SQL Statements. You can press the HOME Key on your keyboard, to reposition the cursor to the first line.

image

I have entered a SELECT SQL Query in the Query Editor. You must now press the RUN Program Function Key, set to PF2 to execute the SQL Query.

image

On execution of the SQL Query, the data-results retrieved from the DB2 Database, are displayed on the Report-Screen. The results are displayed a tabular format. The Column-Headings give detailed-information about the data-item.

image

What if you don't know, about the SQL Columns in the DB2 Table? The DRAW Command comes in quite handy, in such situations. Just enter the command DRAW followed by the Table-Name, on the Command-Line, and QMF generates a ready-made SELECT SQL Statement for you. I have entered DRAW SYSADM.EMPLOYEE on the command-line.

image

Upon executing the QMF Command, it generates a basic SELECT SQL Query, with all columns present, on your table. You can now edit the SELECT SQL Query, and tailor it to fetch your desired results.

image
Q. How do I use Prompted Queries?
Prompted Queries follow a step-by-step wizard-based approach to building and framing an SQL Query. The default entry method of the Query-Editor is SQL. You need to reset the Language of the Query Editor, to Prompted Queries. To do this, enter
RESET QUERY(LANG=PROMPT command on the command-line.

image

When you reset the Language to Prompted Query, a Prompt-Window appears. Pop-up Windows are used to collect information from the user, about the data you wish to search for and extract. In the picture below, the window alerts you to type the Table-Name. Enter your DB2 Table-Name in the pop-up box.

image

After the DB2 Table-Name is fixed, you must SELECT the Columns that you would like to display, in the Result.

image

Now, enter 2 for specifying SELECT Columns. Further, enter an 'S' against each column, you would like to be displayed on the Report.

image

You may specify one or more WHERE conditions, to filter out the rows. I would like to filter rows by the SALARY Column of the EMPLOYEE Table.

image

The Filter-Condition, I wish to apply is SALARY > 3000.

image

The next Pop-Up Window alerts you to enter the Value, against which the SALARY is to be compared.

image

The rough sketch of the entire Query is shown on the Screen.

image

Upon pressing the Run Program Function Key PF2, the query  is executed, and the data-results are displayed on the Screen.

To many people who are thrown to work at a mainframe computer on their first job, they feel lost. Mainframe people seem to speak a completely different language and that doesn't make life easy. What's more, the books and manuals are incredibly hard to comprehend.

"What on earth is a Mainframe?" is an absolute beginner's guide to mainframe computers. We'll introduce you to the hardware and peripherals. We'll talk about the operating system, the software installed on a mainframe. We'll also talk about the different people who work on a mainframe. In a nutshell, we'll de-mystify the mainframe.

Readers based in India, can buy the e-book for Rs. 50 only or the print book. International readers based in the US and other countries can click here to purchase the e-book.