You may already be familiar with Structured Query Language (SQL), an industry-standard language for defining, accessing, and manipulating a database.

By Brian Meyers and Jim Buck

Editor's Note: This article is excerpted from chapter 3 of Programming in ILE RPG, Fifth Edition.

The IBM i operating system uses SQL to create database objects, and ILE RPG programs can incorporate SQL statements to access and update data. The IBM i database principles correspond to SQL standards, but they predate SQL. Consequently, the system also uses an alternative tool, Data Description Specifications (DDS), to define and create database objects. Additionally, IBM i employs DDS to create device files, which SQL does not address.

ILE RPG can process data objects originally created by SQL as well as DDS. SQL can also access and manipulate physical files and logical files that were originally created using DDS. Today, SQL is the preferred tool for creating database objects, and DDS remains the sole means of creating device files. Accordingly, you must learn both SQL and DDS to effectively create all the objects you’ll use in your RPG programs. IBM i and SQL sometimes use different terms for the same or similar things. Figure 3.1 compares the SQL terminology with the equivalent IBM i system names.

Figure 3.1: SQL terminology vs. system terminology

SQL and RPG are two separate topics. Because SQL is an industry standard, information about SQL is available from numerous resources, including the online IBM Knowledge Center. This text covers basic SQL concepts—enough to get you started using it with ILE RPG—but advanced coverage of SQL goes beyond its scope.

SQL groups statements into four major categories:

• Data Definition Language (DDL) statements
• Data Manipulation Language (DML) statements
• Embedded SQL Host Language statements
• SQL Procedural Language (SPL) statements

You use DDL statements to create new objects, such as tables, and to alter the structure or properties of existing database objects (this chapter primarily discusses DDL statements). Some common DDL statements are as follows:

• CREATE TABLE
• CREATE VIEW
• CREATE INDEX
• ALTER TABLE

DML statements retrieve and manipulate the contents of existing database objects (i.e., the data in the tables). The following are regularly used DML statements:

• SELECT
• INSERT
• UPDATE
• DELETE

Embedded SQL statements are SQL instructions that you can incorporate—along with DDL and DML statements—into an RPG program. Chapter 10 deals with some basic DML statements as well as embedded SQL. Here are some regularly used embedded SQL statements:

• DECLARE CURSOR
• FETCH
• SELECT INTO

SPL is a separate SQL-based programming language used to create programs and functions that can be executed from within an SQL environment. Coverage of SPL statements is available from many other sources and goes beyond the scope of this text.

SQL Development Tools

Programmers use a variety of utilities to generate and process SQL statements, but the three primary ones are as follows:

• Interactive SQL
• RUNSQLSTM (Run SQL Statements) command
• IBM Data Studio

The Interactive SQL facility provides you with an SQL command line for directly entering SQL statements into the system. This utility is probably the most commonly used means of executing SQL statements. You can use it to create and alter database objects as well as to retrieve data from existing objects. The STRSQL (Start SQL Interactive Session) command presents a display, as Figure 3.2 illustrates, for entering free-format SQL statements one at a time. Function keys help to enter SQL statements effectively, and a prompting feature allows you to enter just part of an SQL statement and then prompts you for the remaining entries. Interactive SQL retains a history of the SQL statements you enter during a session. If you wish, you can save those statements to a source member for documentation or for later use as a script.

Figure 3.2: Interactive SQL display

The RUNSQLSTM (Run SQL Statements) command provides a scripting facility for executing SQL statements stored in a source file member. Unlike Interactive SQL, which executes single statements individually, an SQL script can execute a series of statements. Not all SQL statements are valid in a script; for example, a script cannot include a simple SELECT statement. But scripts are especially useful for DDL statements to create and alter database objects, and for documenting those statements in a source member. To code the script, you use the same editor (LPEX or SEU) that you use for coding RPG programs. Figure 3.3 shows a typical SQL script. Notice that statements can span several source lines and that every SQL statement ends with a semicolon (;).

Figure 3.3: SQL script for RUNSQLSTM command

IBM Data Studio client is part of the IBM Data Studio software suite, which provides the graphical tools that you need for developing and administering databases. The Data Studio client can be installed alongside the RDi development environment so that the two products can share common features. (RDi also includes a Data perspective with many of the same capabilities as Data Studio.) In Figure 3.4, Data Studio uses a form-fill approach to create and alter data objects. It then generates an SQL script, which you can save and run.

Figure 3.4: IBM Data Studio Properties view

When SQL creates a new table, the database creates a single-member physical file object in a library. Tables are arranged into rows (records) and columns (fields). Appropriately enough, SQL uses the Create Table statement to produce a table:

Character spacing and alignment are generally not important, as SQL is a free-format language, but it’s useful—especially when you are writing SQL scripts—to align the various components and clauses of the statement to make them easier to read and edit. Although the following statement accomplishes the same result as the preceding statement, it is much more difficult to read and analyze:

Both of these Create Table statements contain three major clauses: 1) the name of the table, 2) its column structure (i.e., its fields), and 3) the name of the record format.

Table Name

The first clause names the table to create. The table identifier must begin with an alphabetic character or one of the special characters $, #, or @. In addition to those characters, the remaining characters can be numbers or an underscore (_). A table identifier cannot include embedded blanks.

IBM i object names are limited to 10 characters, and you should limit the table name to 10 characters as well. Specifying a table name longer than 10 characters causes SQL to generate a system object name. For example, if you create an SQL table called LOCALCUSTOMERS, SQL produces an object called LOCAL00001, or some similar nondescript name. In this case, you can use the SQL Rename statement to give the table a more useful object name:

Column Definitions

The second clause in the Create Table statement lists all the columns that make up the table layout. The columns are listed individually inside a single set of parentheses. Each column definition includes at least the column name and a data type. If you specify a column name longer than 10 characters, or with invalid characters, SQL generates a system name.

The following are the most common built-in data types used with IBM i:

• CHAR (fixed-length character)
• VARCHAR (variable-length character)
• DECIMAL (packed numeric)
• NUMERIC (zoned numeric)
• SMALLINT (two-byte integer)
• INTEGER (four-byte integer)
• BIGINT (eight-byte integer)
• DATE
• TIME
• TIMESTAMP

We examine each of the data types in detail later in this chapter.

In addition to the name and data type, each column definition can include optional constraint clauses to further define the column. A constraint specifies a rule for the data in a table. Commonly used constraint clauses include the following:

• NOT NULL
• DEFAULT
• UNIQUE
• PRIMARY KEY

The NOT NULL constraint prevents the column from containing null values. A null value represents the absence of data for a column. You can set a null-capable column to null instead of an actual value. Most IBM i tables specify NOT NULL for each column, forcing that column to always have a value.

The DEFAULT constraint indicates that the column is to contain a default value. The default value is provided for that column to all new rows (records) if no other value is specified. You can specify a constant default value for a column. For example, for a column that includes the constraint DEFAULT 99999, SQL assigns a value of 99999 to that column unless the SQL statement specifically indicates another value. If the constraint doesn’t specify a constant default value, numeric columns default to zero, character columns default to blanks, and date-related columns use the current value. A column, regardless of data type, that is null capable has a default value of null. Column definitions that omit both NOT NULL and DEFAULT clauses are implicitly null capable with a default.

As you might expect, the UNIQUE constraint guarantees that every row in the table has a unique value for that column. A related rule, PRIMARY KEY, combines the NOT NULL and UNIQUE constraints. PRIMARY KEY ensures that a column (or combination of two or more columns) contains a unique value that identifies a specific row in the table. A table can have only one primary key. If the primary key consists of only one column, you can specify the primary key constraint with that column definition:

If the primary key is a combination of two or more columns, you must specify the primary key constraint separately at the end of the column definitions:

Record Format Names

A unique feature of the IBM i database is the requirement that a record format (layout) have a name. Moreover, RPG requires that the record format name be distinct from the table name. To accommodate this requirement, the IBM i version of SQL enhances industry-standard SQL by including a RCDFMT clause to name the format:

When the Create Table statement does not include RCDFMT, the format name is the same as the table name. In that case, the RPG program must rename the format when it declares the file:

(This declaration appears in the RPG program. It is not an SQL statement.)

Qualified Names and Naming Conventions

You can refer to objects either by a simple name, such as Customers, or by a qualified name. An object’s qualified name includes the name of the schema (library) in which the object is stored. Depending upon the naming convention you use, a qualified name might be either Flightproj/Customers or Flightproj.Customers for an object stored in a schema called Flightproj. You can also write the Create Table statement shown earlier as

to explicitly place the table in the Flightproj schema.

Generally, the IBM i operating system uses a slash (/) as a separator character for qualified objects. SQL supports this system naming convention, but only for IBM i objects. The industry-standard SQL naming convention uses a period (.) as the separator character for SQL statements on platforms other than IBM i. The default is the system naming convention. The SQL Set Option statement can change the naming convention for an SQL session:

Specifying qualified names is always unambiguous, regardless of which naming convention you use. But when an SQL statement refers instead to simple names, the naming convention determines how the system finds objects and where it places newly created objects. SQL implicitly qualifies all simple object names with a value contained in a variable called the current schema. The current schema is the default location for creating new objects. The default value of the current schema differs depending upon the naming convention you use. If you use the system naming convention, SQL will search the job’s library list (*LIBL) when it resolves objects with simple names. The SQL naming convention, however, implicitly qualifies simple object names with your user profile name (which SQL calls the authorization identifier).

To summarize:

• *SYS naming uses the library list (*LIBL).
• *SQL naming uses the user profile

The SQL naming convention is more portable, but the system naming convention may be more familiar and convenient.

You can change the value of the current schema to use a specific schema with the SQL Set Schema statement:

Next time: Data Types and Data Storage.  Can't wait? Want to learn more about Programming in ILE RPG?  Pick up the book in the MC Bookstore today!