Introduction
QMF for Windows is a graphical application tool that assists
in building queries.
The purpose of using QMF is to give query capability to
Administrative Information Systems (AIS) DB2 tables and warehouses. This tool will allow you to build ad-hoc
query requests to fulfill your informational needs. QMF can be used to create detail or summary
reports and to export data for download purposes.
The AIS Data Dictionary lists all of the DB2 tables you have
access to based on RACF security. It also lists the columns in each table, and
information about those columns.
The Data Dictionary is located at http://www.ais.ilstu.edu
Under the
“Current Projects” category, choose “Data Dictionary”
Enter your
mainframe NVAS signon for the User Name
Enter your
mainframe NVAS password for the Password
QMF
for Windows Start-up
If you have a “QMF for Windows” icon on the desktop, click
it.
If you do not have a desktop icon, click on
Start
button
Programs
>
QMF for
Windows >
QMF for
Windows
QMF will display the following window.
Prompted
Query
To begin building a new query, from the menu bar, choose “File”
then “New”. QMF can build two types of
queries: “SQL Query” and “Prompted
Query”. In “SQL Query”, you write your
own query statement. “Prompted Query” is a graphical representation of the
components that make up a query. It allows you to choose the components you
would like to include in your query. If you are not familiar with SQL syntax,
the Prompted Query window simplifies creating and editing queries. This manual will guide you through the
process of building a query using the Prompted Query facility.
Choose “Prompted Query”.
Add
Table
The screen displayed below is where you will create Prompted
Queries. There are five separate
windows: Tables, Join conditions, Columns, Sort conditions, and Row
conditions. You will use these windows to
build your query. Each window has five
icons available for functionality.
Add 
Change 
Delete 
Move Up 
Move Down
The first step in building a Prompted Query is to review a
list of tables to choose from. The upper
left window is titled “Tables”. Click
the “Add Table” icon.
Add Table
A message window titled “Tables” will appear. This message window allows you to select the
tables and views you wish to query. DB2
tables are grouped by owners. You may
select only those tables and views that have a specific owner, or you may
select all tables and views. A few
common owners are listed below. If you
are uncertain what groups of tables you have access to, check the Data
Dictionary on the web.
sew = Student Enrollment Warehouse
sis = Student Information System
s%=All tables and views whose owner
starts with “s”, i.e. “sew” and “sis”
% = All tables and views
When entering query, form, and table names, you can use the percent
(%) character to match patterns rather than entering a specific character. The
percent character (%) can be used to match a string of any length containing
any characters. For example, to list all tables with an owner beginning with
the letter A, you enter A%.
For this example, on the line titled “Table owner”, enter
“sew”. Either leave “Table name” empty,
or put in a “%”. This will bring back all tables associated with the owner
“sew”. Click the “Add From List” button.
A second message window titled “Set User Information” will
appear. The “User ID” and “Password” are
your mainframe (NVAS) signon and password.
Leave “Account” blank, and click “OK”.
A list of tables will be displayed. As with other lists in QMF, you can resize
the width of any column. You do this by
clicking the dividing line between column headers, and drag the line either to
the right or to the left.
Select the first table by highlighting the table name and
clicking the “Add” button, or by double clicking on the table name.
The selected table now appears in the “Tables” window. Select the second table in the same fashion,
either highlighting the table name and clicking “Add”, or double clicking on
the table name.
Join
Conditions
When you select more than one table, you need to specify the
relationship between the tables. This is
called a “join” statement. In order to join tables, they must have one or more
columns in common. The columns must contain the same type of data, and be in
the same format. Tables that have data about students or employees have an “SSN:”
note that since the conversion, these fields will contain “UID,” the 9-digit
number starting with “8.” Most of the time you will be joining the UID (SSN) from
the first table to the UID (SSN) in the second table.
Select the “SSN” (UID) from both tables and click the “Add”
button.
More about Joins.
Tables are defined by identifying key values. The key is one or more columns in the table
that make one record unique. Most tables
that have data about students or employees have the “SSN” (UID) as either the
entire key, or a portion of the key.
The key of the SEW.SESSION table
is S_SSN and S_CCYYS. The key of the
SEW.ADDRESS table is A_SSN. The data
dictionary on the web displays which columns make up the key sequence. If there
is a number in the key sequence column, that field is part of the table key
definition. When joining tables, select columns that are within the key when
possible. Queries that join tables using columns in the key will be more
efficient than queries that use non-key columns.
The second table now appears in the “Tables” window, and the join appears in
the “Join conditions” window. Select
additional tables in the same fashion as the first and second tables were
selected.
The join relationship between the new table and the other
tables needs to be defined. In the example below, the SEW.SESSION table is
joined to the SEW.DEMOGRAPHIC table. Once these two tables have been joined,
the “Join Tables” window will not appear for these two tables. It will assume
you want to use the same join(s) you used the first time you selected those
tables.
Now that you have chosen two tables to join, you need to
specify which columns in those tables to join. The columns must contain the
same type of data, and be in the same format. Most of the time you will be
joining the “SSN” (UID) from the first table to the “SSN” (UID) in the second
table.
Select the “SSN” (UID) from both tables and click the “Add”
button.
Add
Column
Some tables require multiple joins. An example would be if
both tables contained a Year and Session (CCYYS) in the primary key. If additional joins are necessary, you would
repeat the above process, selecting the CCYYS from both tables. In this
example, you are done creating joins. Close the open “Add Joins” and “Add
Tables” windows.
The data QMF will return can be thought of like a
spreadsheet. Rows make up the vertical axis and columns make up the horizontal
axis. The default is to return all rows
and all columns from all selected tables. Typically, this is much more
information than you need. The “Add Column” icon will allow you to select the
columns that are returned. The column selection is called a “Select clause”.
Add Column
Columns can be included in the report one at a time, or
multiple columns can be included at once.
To add a single column, either highlight the column name and
click the “Add” button, or double click the column name.
To select multiple columns at once, highlight all the
desired column names and click the “Add” button. In order to highlight a consecutive
group of columns, select the first column, then hold down the shift key
and select the last column. This should highlight all columns between the first
and the last column. If you want to select columns that are not consecutive,
hold down the ctrl key when you click on the column.
Add
Row Condition (Filters)
When you are done selecting the columns to report, close the
“Add Columns” window. The report will contain the columns in the order shown in
the Columns window. The move up and down icons can be used to change the order
of the columns. The top column will be the first column horizontally on the
report; the second from the top column will be second on the report; and so on.
Delete column
Move column up
Move column down
Next, you need to specify what rows are to be reported. The
default is to include all rows from all tables. Typically, this is much more
information than you need. The “Add Row Condition” icon will allow you to limit
the rows that are returned.
Add Row Condition
The Row Conditions window allows you to limit the size of
your answer set. You can specify which
conditions must be met in order to include a row. Only those rows that meet the
specified conditions will be returned. This is called a “Where clause”. The
“Where clause” is built by comparing a column in one of the selected tables
against a constant value. The column included in the “where clause” does not
need to be included on the report. There
are four areas in the “Row Conditions” window: Left side, Operator, Right side,
and Connector.
Left side specify the column to compare against
the constant
Operator defines
the relationship between the column value and the constant
Right Side the
constant value
Connector defines
relationship between multiple conditions
When there are multiple conditions, the “Connector” specifies
the relationship between all conditions. Typically you will choose “And”. If
you mix “And” and “Or” connectors, you will need to add parentheses to your
query to define the relationship between the “Where clauses”. Parentheses must be used in matched pairs
For this query, you wish to limit the records returned to
only those who have a Session record for the Fall of 2001, i.e. “where
S_CCYYS=20014”.
Left side S-CCYYS
Operator Equal
to
Right Side 20014
When building a “Where clause”, do not concern yourself with
quote marks. QMF will automatically add them in as appropriate. However, upper
and lower case need to be considered. Most character data, except names, are in
upper case. Names are in mixed case, such as “Smith”. If the constant you supply
does not match the case in the database, it will not find a match.
You wish to further limit the records returned to only those
who are enrolled for the previously selected semester, i.e. “where
S_SEM_ENROLL_IND=Y”. Since you want only
those rows that satisfy the condition specified on the previous window and the
condition specified in this window,
“And” is the appropriate Connector.
Finally, you wish to further limit the records returned to only
those students that have their first major equal to ‘99’. Again, “And” is the
appropriate connector.
Add
Sort Condition
You are now done adding “Row conditions”. Specifying the
order the records are returned can further refine the query. This is called an
“Order by” clause.
Add Sort Condition
Advantages of Sorting.
Even though you may expect to receive one row per person or
account, the query may return multiple rows. Choosing to sort the report by a
unique identifier such as “SSN” (UID) or name will guarantee that multiple
records, if any, are together. This will assist in verifying that the data you
received is what you expected. Generally, your dataset comes back in the order
you want without applying a sort condition. If the dataset is not in the
desired order, apply a sort condition and run the query again.
A goal is to have the QMF query do as much work as possible,
creating efficiencies for repeated runs.
The sort window displays the columns that will appear on the
report. Sort the report by highlighting a column name and clicking the “Add” button,
or by double clicking a column name. Repeat this procedure to sort on
additional columns.
Saving the Query
The prompted query is built. Now would be a good time to
save your query. Under “File” on the menu bar, choose “Save As”.
The default location for saving the query is in the same directory as QMF. You
can choose to save them in a different location. The file name should start
with a letter or a number, and may contain spaces. The file name field does not
have a maximum length restriction. Prompted queries are saved with a file type
of “.pq”, for “Prompted Query”.
Viewing
the SQL
DB2 processes queries using a language called SQL, Standard
Query Language. We have created your
query using Prompted Query. The QMF tool
actually has been constructing your query using SQL in the background.
You can view the SQL that QMF has built by clicking the
“View SQL” icon.
View SQL
The SQL window displays the various clauses that have been
previously discussed. The SQL starts
with the “Select clause”. Next is the
list of tables. This is called the “From clause”. After that, the “Joins
clause” has been added to the “Where clause”, and finally is the “Order By
clause”.
Viewing
the Prompted Query
To return back to the Prompted Query Builder, click the
“View Prompted” icon.
View Prompted
Executing
your Query
To run your query, click the “Run Query” icon.
Run Query
While your query is processing, you will see the “Fetching
data” window.
DB2 has returned data to QMF. Note that the lower left corner of the window
says “Row 1 of 360”. DB2 will return some of the data to QMF, and then will
pause to see if the additional data should be returned. The example below shows
360 rows of data have been returned. If the second number is grayed out (in
this case, 360) that means DB2 is still in the process of returning data. If
DB2 is finished returning data, both numbers will be equally dark. To return
all of the data, slide the far right vertical scroll bar down as far as you
can. The scroll bar will float upwards as additional data is returned and
appended to the end of the answer set.
Data retrieval is done in blocks and your QMF session will
pause in between the retrieval of the data blocks. When you perform an action, such as exporting
the data or selecting all the records, and all the data has not been returned,
QMF will ask what to do. “Yes” will tell DB2 to return the remaining data
before performing the specified action. You will need to perform the action
that caused the message to appear again.
“No” or “Cancel” will keep the data in its current state; some of the
data returned, the remaining waiting to be returned. “No” will allow you to
perform the action that caused the message, such as saving the portion of the
data that has been returned. “Cancel” will cancel the action you have
requested.
Cancel
the Query
At any time before your query is done processing, you can
cancel it.
Cancel Query
Column
Sizing
QMF may display the data in column widths that do not
display all the data, or have an excess amount of blank space. Any individual
column can be resized by dragging the line to the right of the column title
either right or left. You can also have QMF automatically resize all of the
columns. That is done by double clicking the empty cell in the upper left
corner of the data.
These data cells have been automatically resized. All of the
columns now fit within the width of the window.
Having returned the data, you may save the data in order to
use it in another tool, such as Excel or Access. Under “File” on the menu bar,
select “Export Data”.
Saving
the Data
An effective way to interact with QMF data and Excel is to
use the Results: Export to Excel (Ctrl-B). This menu will sned the datasheet to
Excel, preserving formatting in the datasheet.
Alternatively, when saving the data for use by tools such as
Excel and Access, save the data as type “csv”, Comma Separated Value.
The “options” button in the export window gives the ability
to change delimiter values (tab or semi-colon, for example), and to include or
exclude column headers in the dataset.
AIS homepage
