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.  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. 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. 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. 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. 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.

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. 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. After the DB2 Table-Name is fixed, you must SELECT the Columns that you would like to display, in the Result. Now, enter 2 for specifying SELECT Columns. Further, enter an 'S' against each column, you would like to be displayed on the Report. 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. The Filter-Condition, I wish to apply is SALARY > 3000. The next Pop-Up Window alerts you to enter the Value, against which the SALARY is to be compared. The rough sketch of the entire Query is shown on the Screen. Upon pressing the Run Program Function Key PF2, the query  is executed, and the data-results are displayed on the Screen.