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!
Jim is the president and founder of imPower Technologies, where he provides professional IBM i training and consulting services. He is active in the IBM i community, working to help companies train their employees in the latest IBM technologies and develop the next generation of IBM i professionals.
MC Press books written by Jim Buck available now on the MC Press Bookstore.
 |
||
 |
|
Control Language Programming for IBM i Master the A-Z of CL, including features such as structured programming, file processing enhancements, and ILE. List Price $79.95 Now On Sale
|
|
||
 |
|
Mastering IBM i Get the must-have guide to the tools and concepts needed to work with today's IBM i. List Price $85.95
Now On Sale
|
|
||
 |
|
Programming in ILE RPG Get the definitive guide to the RPG programming language. List Price $95.95
Now On Sale
|
|
||
 |
|
Programming in RPG IV Understand the essentials of business programming using RPG IV. List Price $79.95
Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online