AS/400 Chapter 4: SQL

Now that we've created a library and saw some basic AS/400 features let's have a look at how we can create and manipulate tables using SQL.

You must create the DEMO library and define it has your current library to proceed with this chapter.

Type the following Start SQL command on the system prompt:

STRSQL

This way you begin a SQL session in AS/400. This application accepts most of the common SQL syntax (CREATE,INSERT, DELETE, SELECT, DROP, etc).

Creating a table with SQL

From the SQL command line we're going to create the CLIENTS table and then insert some rows. For this tutorial let's assume that all phone numbers have, at most, 9 digits.

Press F3 to leave the table view and type the following commands. Type:

CREATE LIBRARY DEMO/CLIENTS( id_cli numeric(10) PRIMARY KEY, name_cli char(50), birth_cli date, phone_cli numeric(9) )

Note: You can place the code in separate lines (like in the example) or in a continued fashion (without indentation) as long as you don't press the Enter key before you finish the command.

A message will appear saying that the table was created.

If you want to see more empty command lines just press Page Down or move the mouse scroll down. Now Type:

SELECT * FROM CLIENTS

This way you can see that the table was created and it has no information in it. So let's insert some new rows.

Press F3 to leave the table view and type the following commands.

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (1, 'Mary', '12/09/1967', '999999999')
INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (2, 'Tom', '09/01/1979', '123456789')

Do the SELECT command again to check if the rows were correctly inserted.

Press F3 to leave the SQL command line. A set of options will appear. On the field you should write the option number. Try typing a number outside the option range (for instance 5) and then press Enter. As one would expect, an error message appears on the bottom of the screen. Try pressing F3 or inserting another value. It won't work because the screen is blocked. You will probably find some blocking errors and when you do press Esc the screen will go back to normal.

Now select the option 1 to exit the application, saving the session.

SQL Built-In Functions

There are some very useful SQL built-in functions on the AS/400 system.

Basic Functions

If you know SQL you may already be familiar with these functions.

Example (returns the maximum ID_CLI value from all the rows in the table CLIENTS):

SELECT MAX(ID_CLI) FROM CLIENTS

Numeric Functions

String Functions

Date and Time Functions