What is SQL?

Structured Query Language (SQL) is a high-level language that is used to specify what information a user needs without having to know how to retrieve it. The database is responsible for developing the access path needed to retrieve the data. SQL works at a set level, meaning that it is designed to retrieve one or more rows. Essentially, it is used on one or more tables and returns the result as a results table.

SQL has three categories based on the functionality involved:

• DML - Data manipulation language used to read and modify data
• DDL - Data definition language used to define, change, or drop DB2 objects
• DCL - Data control language used to grant and revoke authorizations

Several tools can be used to enter and execute SQL statements. Here we focus on SPUFI, which stands for SQL Processing Using File Input. SPUFI is part of the DB2 Interactive (DB2I) menu panel, which is a selection from your ISPF panel when DB2 is installed. (This, of course, depends on how your system people set up your system's menu panels.)

SPUFI is most commonly used by database administrators. It allows you to write and save one or more SQL statements at a time. DBAs use it to grant or revoke authorizations; sometimes even to create objects, when that needs to be done urgently. SPUFI is also often used by developers to test their queries. This way they are sure that the query returns exactly what they want.

Another tool that you might encounter on the mainframe is the Query Management Facility (QMF), which allows you to enter and save just one SQL statement at a time. QMF's main strength is its reporting facility1. It enables you to design flexible and reusable report formats, including graphs. In addition, it provides a Prompted Query capability that helps users unfamiliar with SQL to build simple SQL statements. Another tool is the Administration Tool, which has SPUFI capabilities as well as a query building facility.

Entering SQL using SPUFI shows how SQL is entered using SPUFI. It is the very first selection on the DB2I panel. Note that the name of this DB2 subsystem is DB8H.

Entering SQL using SPUFI

SPUFI uses file input and output, so it is necessary to have two data sets pre-allocated:

• The first, which can be named ZPROF.SPUFI.CNTL, is typically a partitioned data set in order to keep or save your queries as members. A sequential data set would write over your SQL.
• The output file, which can be named ZPROF.SPUFI.OUTPUT, must be sequential, which means your output is written over for the next query. If you want to save it, you must rename the file, using the ISPF menu edit facilities.

In Assigning SPUFI data sets you can see how that fits in.

Assigning SPUFI data sets

Notice option 5, which you can change to YES temporarily to see the default values. One value you might want to change is the maximum number of rows retrieved.

With option 5 at NO, if you press the Enter key, SPUFI will put you in the input file, ZPROF.SPUFI.CNTL(DEPT), in order to enter or edit an SQL statement. By entering recov on in the command and pressing Enter, the warning on top of the screen will disappear. This option is part of the profile, mentioned earlier in this book. The screen is shown in Editing the input file.

Editing the input file

If your profile is set to CAPS ON, the SQL statement you have just typed will normally change to capital letters at the Enter. But this is not needed.

Notice that we have mentioned DSN8810.DEPT as table name. This is the qualified name of the table, since we want to use the sample tables, which are created by user DSN8810.

If you enter just one SQL statement, you do not need to use the SQL terminator, which is a semi-colon (;), since this is specified in the defaults (but you can change this; remember option 5 of the previous screen). However, if you enter more than one SQL statement, you need to use a semicolon at the end of each statement to indicate that you have more than one.

At this point, you need to go back to the first panel of SPUFI by pressing the F3 key. You will then see Returning to the first SPUFI panel.

Returning to the first SPUFI panel

Notice that there is an asterisk (*) for option 6 since you just finished editing your SQL. At this point, if you press Enter, you will execute your SQL statement and you will automatically be put into your output file, since BROWSE OUTPUT is set to YES. The first part of the output is shown in First part of the SPUFI query results.

First part of the SPUFI query results

To get the second (and in this case, final) result screen, press F8, and you will see Second result screen.

Second result screen

Notice that you have a result table with just one column. This is what was specified in SELECT, just DEPTNO. We have retrieved the DEPTNO from all the (14) rows in the table. There are a few messages. One gives the number of rows retrieved. Another indicates that SQLCODE (an SQL return code indicating success or not) is 100, which means end of file, therefore no more results to sho