This article is an excerpt from Mastering IBM i (MC Press, 2011).

Structured Query Language (SQL) has become the mainstay for data manipulation on most platforms today. It is important for today's IBM i professional to understand the traditional DDS tools, but most new database systems are created and manipulated using SQL. The intent of this series of articles is to expose the reader to both the traditional tools and the latest tools and concepts. SQL is a complete database language with data-definition, data-manipulation, and data-control components.

Whole books are written about SQL—some quite large. This series provides just a taste of SQL to give you a hint of its capabilities. SQL is a powerful language, and you should learn it well before you try to apply it to a production database. Our scope here is limited to that part of the Data Manipulation Language (DML) that lets you retrieve information from the database and change (update, insert, and delete) records in the database. Also included is a short introduction to Data Definition Language (DDL) Create statements, which let you create SQL objects, and DROP statements, which let you delete SQL objects.

A Short History of SQL

Andrew Richardson, Donald C. Messerly, and Raymond F. Boyce at IBM developed the precursor of today's SQL in the 1970s. This version, called SEQUEL, was developed to manipulate data stored in IBM's original database product, System R. System R, which IBM patented in 1985, was a research project that was the first to demonstrate that a relational database management system (RDBMS) could provide good transaction-processing performance. The American Institute of Standards and Technology standardized SQL in 1986 as SQL-86, and in 1987 the International Organization for Standardization (ISO) standardized SQL. Although the original SQL standard designated the official pronunciation of SQL as "es queue el," many people still mispronounce the abbreviation as "sequel."

Introduction to SQL Terms

Before we continue, we will compare the terminology of SQL and the traditional DB2 system file-access methods. Table 13.1 lists the common terms for both methods of access.

Introduction to SQL

In the IBM i OS, we can use SQL on physical and logical files created by compiling DDS source code, as well as on SQL-created tables (physical files) and views (logical files). This is an important feature because many Power Systems using the IBM i OS contain DB2 databases that were not created with SQL. In today's programming environment, we need to use SQL's powerful query and update capability to access these databases.

SQL statements can exist in and be executed from several different environments on the system. In this series, we limit our discussion to what IBM calls dynamic SQL statements submitted to the interactive Run SQL Scripts interface, which is part of IBM i Navigator. In addition to interactive SQL, in which you type a statement on an SQL command line (similar to a CL command), you also can use SQL from within a C, COBOL, Java, or RPG program. The statements can be static SQL, in which the statements are hard coded but typically use host program variables for comparison values, or dynamic SQL, in which the program prepares the SQL statement as a character string that is then passed to the IBM i DB2 database manager for execution. When used from within a program, SQL typically takes over all or much of the traditional file input/output (I/O) and data-access responsibilities from the program logic.

Interfaces Used to Enter SQL Commands

The traditional STRSQL interface, used within a 5250 session, lets you enter, run, and display the results of SQL statements, as you see in Figure 13.1.

Figure 13.1: STRSQL Interface Using a 5250 Session

Although the examples in this series will work with traditional tools such as the 5250 interactive STRSQL program, we will be using the Run SQL Scripts interface, part of the IBM i Navigator product, here. Figure 13.2 shows this interface.

Figure 13.2: Run SQL Scripts Interface

We will not discuss all the features of the Run SQL Scripts interface. For our purposes here, you should consider the interface as an editor, which allows you to run and display the results of SQL statements.

An additional feature of this editor is the capability to run CL commands that do not require an interactive display. Figure 13.3 shows the CPYF CL command after it has been entered and executed. The messages from the command are also displayed in the lower pane. The complete command is

CL: CPYF INTROCLASS/EMPPF CIS001/EMPPF CRTFILE(*YES)

Figure 13.3: CPYF Command Entered in the Run SQL Scripts Interface

You can enter numerous commands in this window at the same time. All commands must end with a semicolon (;), and you must designate CL commands by starting them with CL:.

Starting the Run SQL Scripts Interface

Let's assume we have connected to our system using IBM i Navigator and have expanded the Databases icon, right-clicked the local database icon (on this system, S191ff5c), and clicked Run SQL Scripts, as Figure 13.4 shows.

Figure 13.4: Starting the Run SQL Scripts Interface

Figure 13.5 shows the open Run SQL Scripts interface with the "input" and "output" panes. We use the upper, input pane (labeled 1) to create and edit SQL and CL commands. The lower, output pane (2) displays the results from running an SQL or CL command.

In the figure, we have selected Options from the toolbar at the top of the window and then Display Results in Separate Window. Selecting this option will open another window to display the results of an SQL command. After we have selected this option, the output pane will display any messages resulting from running a command.

If we had not selected this feature (note the arrow), we could have clicked the tabs at the bottom of the window to switch panes and see messages and results.

Figure 13.5: The Run SQL Scripts Interface

In Figure 13.6, we have run our simple SQL statement again. The results are displayed in a new window, and we now can see any messages that result from running the commands.

Figure 13.6: Run SQL Scripts Interface, Continued

Run SQL Scripts uses Java Database Connectivity (JDBC) to connect to the IBM i DB2 database to run commands. You might have noticed in the preceding figures that the SQL command we used was qualified. This means that we included the schema and table name, as follows:

Select * From cis001.EMPPF

We needed to qualify the table because when we started Run SQL Scripts, we were at the logical database level and not on a specific schema. We can change the default schema by clicking the Connection option from the toolbar at the top of the window and then selecting Use Temporary JDBC Settings, as in Figure 13.7.

Figure 13.7: Change Connection Settings

Selecting the Use Temporary JDBC Settings option displays the window in Figure 13.8. Here, we have changed the Default SQL schema to our student library, CIS001.

Figure 13.8: Change Connection Settings, Continued

A number of tabs at the top of the window let you change additional defaults. For this example, we will change the default schema and click Connect. This change will enable us to write unqualified SQL statements, such as

Select * From EMPPF

SQL Syntax

The basic form of an SQL statement to obtain information from a database table or tables is

Select field-list

From file-list

Where conditional-expression

We will look at each of the values (in italics) more carefully.

Select field-list

In its simplest form, you can replace a field list with an asterisk (*), which means all fields from the specified file (or files). So if you coded

Select *

From EMPPF

you would be instructing SQL to display data for all fields in the record format of file EMPPF.

If you did not want the entire record format, you could name the fields that you did want, in any order, each separated from the next by a comma. SQL uses the comma (,) as an item separator for any kind of list. You would need to know the field names as they are recorded in the record format of the externally described file. For field names and SQL keywords, case does not matter: select * is the same as SELECT *. Only when you are dealing with quoted strings (e.g., 'Human Res') does case matter.

In a field list, you can rename a field temporarily (just for the output of that statement) using an AS clause. You also can perform arithmetic operations on numeric fields and string operations on alphanumeric field values to create new "alias" fields. For example, the following statement creates a virtual column named RAISE in the result table:

Select EMPNO, LASTNAME, SALARY,

SALARY * .035 As RAISE

From EMPPF

RAISE does not exist in the base table. It is calculated for each row of the result table as 3.5 percent of that employee's salary, by multiplying each record's current value of SALARY by the constant value .035. You can also use addition, subtraction, and division operators in this type of arithmetic expression.

Functions that work on the value of a field for each selected record are called scalar functions. You can use scalar functions, as well as constants and arithmetic or string operators, to create virtual columns. For example, to create a field called NAME, which consists of the first initial plus the last name, and display it along with employee number, you could code

Select Substring(FIRSTNAME,1,1) Concat '. ' Concat

LASTNAME As NAME, EMPNO

From EMPPF

Figure 13.9 displays the result of entering this SQL command in the input window and selecting the Run icon (note the arrow).

Figure 13.9: Display Output of Virtual Column NAME and EMPNO from File EMPPF

Instead of spelling out Concat (concatenation operation), an alternative is to specify the two bar or pipe symbols (||). (Shift+Backslash provides the bar character on most keyboards.) The Substring scalar function has three arguments (as in Query for i5/OS)—source string, starting position, and length—but you use commas to separate the arguments.

SQL has many scalar functions for numeric and alphanumeric fields, data-type conversion, date and time operations, and trigonometric operations. You can find explanations of these functions at the IBM i Information Center or in IBM's DB2 for i SQL Reference.

We will continue our discussion of SQL syntax in the next article, exploring how to use the From file list and specify join operations, as well as how to code the Where clause.