Learning Objectives
After completing the session, you will be able to:
Access DB2 data using DB2I, SPUFI and QMF Interaction with DB2: Overview
An environment is connected to a DB2 subsystem by an attachment facility. Whenever there is a need of interaction with DB2, a thread will be established.
A thread is a control structure used to:
Send requests to DB2
Send data from DB2 to the requestor
Communicate the status of each SQL statement after it is executed
TSO as a Door to DB2:
You use TSO (Time-Sharing Option) environment as a door that provides access to DB2 data.
The TSO Attachment Facility provides access to DB2 resources in two ways:
Online mode, in the TSO foreground, using ISPF (Interactive System Productivity Facility) panels
Batch mode using the TSO Terminal Monitor Program - IKJEFT01 (or IKJEFT1B)
Common types of TSO foreground users include:
DB2I
QMF
DB2I
DB2I (DB2 Interactive) is a TSO-based DB2 application. It consists of a series of ISPF panels, programs, and CLISTs enabling rapid access to DB2 services and data. DB2I increases the TSO DB2 developer's productivity.
How to access DB2I:
Log on to TSO as you normally would. The ISPF main menu appears.
Choose Option 8 (DB2 - Perform DATABASE 2 Interactive Functions) in Cognizant Mainframe.
DB2I Main menu appears as shown in the following screenshot.
DB2I Primary Option Menu
Following Options are available with DB2I:
o SPUFI: SQL Processor Using File Input.
o DCLGEN: Declaration Generator.
o PROGRAM PREPARATION: Prepares a program containing embedded SQL for execution.
o PRECOMPILE: Program containing embedded SQL is parsed to retrieve all SQL and replace it with calls to a runtime interface to DB2
o BIND/REBIND/FREE: Provides the capability to bind a DB2 plan & package, rebound a plan & package, remove plan & package from the system
o UTILITIES: Provides panels that ease the administrative burdens of DB2 utility processing
o DB2I DEFAULTS:
Lets you modify parameters that control the operation of DB2I
Be sure that the proper DB2 subsystem is specified in the “DB2 Name”
parameter
Be sure that the proper language to be used for preparing DB2 programs in the “Application Language” parameter
A valid job card needs to be supplied in the “DB2I Job Statement”
parameter.
o EXIT: Leaves DB2I SPUFI
The first option in the DB2I main menu is SPUFI (SQL Processor Using File Input).
SPUFI is intended primarily for application programmers who wish to test the SQL portions of their programs or administrators who wish to perform SQL operations.
SPUFI reads SQL statements contained as text in a sequential file or in a member of a PDS, processes those statements and places the results in an ISPF browse session. By specifying the input and output data sets and selecting the appropriate options, we can execute SQL statements in an online mode.
The SPUFI Panel is as follows.
Input Dataset
Output Dataset
Other Options
You need to enter Input data set name and Output data set name Input Data Set
Input dataset must be allocated before invoking SPUFI. This can be a member of Partitioned Data Set or a Sequential Data Set. This can be empty and can be edited as part of the SPUFI session.
It is recommended to maintain a partitioned data set to keep track of SQL statements used.
Input dataset can be defined as a fixed, blocked data set with an LRECL of 80.
SQL statements can be written in all but the last 8 bytes of each input record; this area is reserved for sequence numbers. This can contain multiple SQL statements, as long as they are separated by semicolons. Comments are preceded by two hyphens. If the input file contains multiple SQL statements, SPUFI will stop execution of those statements as soon as it encounters an error in any one of them.
Sample SQL statements in the input data set as follows:
--DELETE FROM THYD001.TEST --WHERE TEST_NO = 'A114';
-- SELECT * FROM THYD001.TEST;
--
Output Data Set
The output data set need not be allocated before using SPUFI. If the output data set does not exist, SPUFI creates a virtual, blocked sequential data set with an LRECL of 4092. The output file will contain a sequence of results, one for each statement (including the relevant SQLCODE), followed by a summary of the overall execution (including, in particular, an indication as to which of Commit and Roll Back occurred). There are some specific defaults to be set for Output data set.
When the SQL is executed and browsed, an output data set like the following appears:
Query
Query Result
Information about the Query Result
Other Options:
After entering Input data set name and Output data set name, we need to specify following options.
CHANGE DEFAULTS: When Y is specified, the SPUFI defaults panel appears as follows:
Isolation Level
Options of “CURRENT SPUFI DEFAULTS”:
o Typically, defaults are changed only once—the first time someone uses SPUFI.
ISPF saves the defaults entered from session to session.
o Be sure to specify the following defaults:
Isolation Level: Always set this option to CS (Cursor Stability).
(Note: You will study about Isolation Level in detail in your forthcoming sessions.)
Max Select Lines: Set to an appropriate number. If we will be selecting from large tables that return more than 250 rows, the installation default value of 250 is insufficient. SPUFI stops returning rows after reaching the specified limit, and it issues a message indicating so.
o All the remaining installation defaults are appropriate. So keep them as they are.
o EXECUTE: When Y is specified, the SQL in the input file is read and executed.
o AUTOCOMMIT:
When Y is specified, a COMMIT is issued automatically after the successful execution of the SQL in the input file.
When you specify N, SPUFI prompts you about whether a COMMIT should be issued. If the COMMIT is not issued, all changes are rolled back.
Note: You will study about commit and rollback in our forthcoming sessions.
o BROWSE OUTPUT: When Y is specified, SPUFI places you in an ISPF browse session for the output data set. You can view the results of the SQL that was executed.
Note: Specifying Y for all these options (from 6 to 9) except Change Defaults (5) is common.
QMF
QMF (Query Management Facility) is an interactive query tool used to produce formatted query output. It is similar to SPUFI but has much more advanced features to produce formatted output.
In Cognizant mainframe, choose the option “TS.QMF” in the ISPF main menu; you will get the QMF Home Panel as follows:
Options
Command Line
Notice the numbered options in the bottom portion of the screen. These numbers correspond to QMF functions:
There are three basic QMF objects to produce formatted reports of DB2 data:
Queries
Forms
Procs Queries
You will see how to produce a simple report:
Press F6 to navigate to the QMF Query panel, which is initially blank.
Type the following statement at the COMMAND prompt and press Enter DRAW <Table Name>
Following panel will appear:
Query
Options
To run this query, press F2 which will produce this report.
Query Result as Report
You can save a query by typing the following in the Command prompt SAVE AS <Query Name>
Press F4 to print the report.
You can format this report using Forms by pressing F9.
Forms
Forms are used to format the report of the query output.
Press F9 to go to a Form.
A default form is generated for each query when it is run.
QMF Forms enable us to perform the following:
o Code a different column heading
o Specify control breaks
o Code control-break heading and footing text
o Specify edit codes to transform column data (for example, suppress leading zeroes or display a currency symbol)
o Compute averages, percentages, standard deviations, and totals for specific columns
o Display summary results across a row, suppressing the supporting detail rows
o Omit columns in the query from the report QMF Form Panel is as follows:
Form Panel
Procs
A QMF query can contain only one SQL statement in contrast with SPUFI, which can contain multiple SQL statements as long as they are separated by a semicolon.
QMF Proc is used to execute multiple SQL statements at one time.
QMF Procs contain QMF commands that are tied together and executed serially.
By pressing F10, you can enter into PROC and can execute the queries as follows:
Proc Panel
Following is a typical QMF user's session. If you type a single SQL statement and press a few function keys, then an end-user report is generated.
Summary
An environment is connected to DB2 by an attachment facility by establishing a thread.
TSO interacts with DB2 through:
o Online mode
o Batch mode
The most common online mode includes:
o DB2I
o QMF
DB2I (DB2 Interactive) is a TSO-based DB2 application, which increases the TSO DB2 developer's productivity.
DB2I helps application programmers and administrators to:
o Run queries (SPUFI)
o Generate declare code (DCLGEN)
o Prepare programs (precompile, bind, rebind, free, run)
o Issue DB2 commands
o Prepare DB2 utilities for execution
SPUFI supports the online execution of SQL statements from TSO terminal.
o By specifying an input and output data set and selecting the appropriate options, you can execute SQL statements in an online mode.
QMF (Query Management Facility) is an interactive query tool used to produce formatted query output.
o QMF objects to produce formatted reports of DB2 data are:
Queries to execute a query to produce report Forms to format the query output (report)
Procs to execute multiple queries to perform series of actions
Test Your Understanding
1. What is an attachment facility?
2. What is a thread?
3. How does TSO interact with DB2?
4. List the common TSO Online modes through which DB2 interaction happens.
5. What is DB2I?
6. List the different options in DB2I.
7. What is SPUFI?
8. Explain the different options in SPUFI.
9. What is QMF?
10. Explain the important functionalities available in QMF.