SQL on the iSeries

Structured Query Language (SQL) is the industry standard language for database access. While the American National Standards Institute (ANSI) has a defined SQL standard, within that standard, each relational database platform has its own unique "flavor" of SQL. This can include things as simple as command syntax and as complex as supported functions. In this chapter, we'll explore what makes the iSeries implementation of SQL unique. This chapter acts as an introduction to the SQL functionality on the iSeries. If you're already somewhat familiar with that functionality, you may want to skip ahead to chapter 2.

Software Requirements

To use the examples in this book, you'll need to have the DB2 Query Manager and SQL Development Toolkit licensed program installed on your iSeries. This licensed program gives you a handful of useful tools, including the Query Manager report writing tool as well as the precompilers, which allow you to embed SQL code within other programming languages (SQL RPG, for example).

This licensed program also gives you the STRSQL interactive SQL command line utility. This utility comes in handy if you need to run ad hoc queries (i.e., action queries to modify records in a database on the fly). Throughout this book, many of the examples we cover will be executed from within this utility. Later in the book, we'll also be exploring the use of functions and procedures from client applications developed in Microsoft Access, Visual Basic (VB), and Active Server Pages (ASP).

SQL Tools

Throughout this book, we'll use several utilities (both GUI and green -screen) that make it easier to work with SQL statements on the iSeries. The STRSQL command can be executed from an iSeries command line. This command starts the interactive SQL utility. When you enter this utility, press the F4 key to display a list of the available SQL commands that can be executed from within the interactive SQL session. When a SELECT statement is entered, the resulting data can be either displayed to the screen or sent to a printer or output file. This selection is made by pressing F13 from within STRSQL to display the Interactive SQL Session Services menu and selecting option 1 for Current Session Attributes. Figure 1.1 shows the screen that is displayed.

This screen shows that option 1 has been selected for the "SELECT output" field. If you select option 2, you'll be prompted for the printer name. If you select option 3, you'll be presented with parameters for the file and library to contain the output data. Notice that you can specify other options here as well, such as the data and time format and the naming convention.

iSeries Navigator also includes an SQL tool. You can access this utility by first expanding the Databases option and clicking on your system name and then selecting Run an SQL Script link from the right side of the lower pane in iSeries Navigator. The window shown in Figure 1.2 will be displayed.

You can use this screen in much the same way that you'd use STRSQL. The Examples drop-down list contains templates of SQL statements to perform common tasks. Later, we'll examine using this application to create functions and procedures. As we examine sample SQL statements throughout this book, either of these two tools can be used.

Exploring SQL Statements

Let's take a look at the components and functions of the various SQL statements.

Starting with the Basics

Probably the single most common individual SQL statement is the SELECT statement, which allows you to define a set of data to be retrieved from a database. Figure 1.3 shows the basic structure of a SELECT statement.

Within this example, the FROM clause is required. However, the other clauses shown are optional. The field_list value would be replaced by a comma-delimited list of fields or simply an asterisk to return all fields from the defined table(s). The table_definition value can be a list of joined tables, along with a definition of how they are to be joined, or simply a physical or logical file name. (We'll examine joining tables a little later on.) The condition_expression on the WHERE clause determines exactly which records will be returned from the defined table or tables. The group_field_list used with the GROUP BY clause indicates that the data set returned is to be summarized by the list of fields provided. When a GROUP BY clause is specified, all of the column names appearing in the field_list either must appear in the group_field_list or must be a column function (SUM, AVG, MIN, MAX, etc.). The sort_field_list defined on the ORDER BY clause defines the sort order for the result set. The group_level_conditions defined on the HAVING clause is used in much the same way that the WHERE clause is used except that the condition defined here is checked only after a GROUP BY is processed. This allows you to use a column function as part of a condition.

As you can see, the SELECT statement can take on many forms. The code below shows a very simple form of a SELECT statement.

SELECT
FROM QSYS2.SYSTABLES

This example uses the SQL naming convention in place of the SYS file naming convention, which would use the format library/file. This statement would return all columns (or fields) from the file SYSTABLES in library QSYS2. A slightly more complex version of this statement could be used to select only required fields under specified conditions, as shown below:

SELECT TABLE_NAME, TABLE_TYPE
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'QSYS'
ORDER BY TABLE_NAME

This example would display only the columns TABLE_NAME and TABLE_TYPE for records with a TABLE_SCHEMA value of 'QSYS'. The result set would be sorted by the TABLE_NAME column. To further elaborate on this example, we can add grouping clauses to create a summary result set, as shown below:

SELECT TABLE_SCHEMA, COUNTY(*) AS TABLE_COUNT, SUM(ROW_LENGTH) AS
TOTAL_ROW_LEN
FROM QSYS2.SYSTABLES
WHERE TABLE_TYPE = 'P'
GROUP BY TABLE_SCHEMA
HAVING COUNT(*)>10

This example would display library names (TABLE_SCHEMA) and the number of physical files within each library, along with the total length of rows between all tables within the library. The HAVING clause is used to include only libraries containing more than 10 physical files.

A slight variation of the SELECT statement can be found in SELECT INTO. This statement is used to pass values from a defined SELECT statement into host variables in a stored procedure or embedded SQL within an RPG program.

When using SELECT INTO, no more than one row can be returned at a time. This means that your statement must return results containing unique results.

While the SELECT statement is used to retrieve data from a database, other statements are used to manipulate a database. The DELETE statement, as its name would suggest, is used to remove rows from a table. Figure 1.4 shows the syntax used with the DELETE...