Brief: DDS is not the only method of defining data on the AS/400. SQL is an

alternative worth considering. This article will show you how to create

database files with SQL.

Do you ever get confused when reading articles about the future of our industry

in the trade journals? I certainly do!

One prophet predicts that proprietary operating systems will soon be a thing of

the past and only open systems (i.e., UNIX) will remain. Others agree that

proprietary ways of computing are doomed, but swear that PC networks will

replace them. As to which network OS will make all others extinct, however,

they don't agree at all.

Is C++ the future for programmers? Will Visual Basic win the client/server

wars? Read a dozen articles, get a dozen contradictory opinions.

I don't know what products will win this war called data processing, but if I

had to choose one language that I believe will be around for a while and that I

think is worth putting on my resume, I'd choose SQL. Whatever I'm doing five or

ten years from now, whatever hardware I'm using, whatever OS, whatever DBMS,

whatever programming language, whatever my job title, I believe SQL will very

likely be an important part of my work.

SQL, pronounced either "S-Q-L" or "sequel," is an acronym for Structured Query

Language. Don't let the word "query" fool you. Besides retrieving data from a

database, with SQL, you can define a database, write, update and delete data,

and control authority to the data. (For more information, see "An Introduction

to SQL," MC, April, 1995.)

In this article, I will explain the data definition capabilities of SQL and

give you some reasons why you might want to consider using SQL rather than DDS

to define physical and logical files on the AS/400.

Why SQL?

Defining your database with SQL offers several advantages. First, you can do

things with views that you can't do with logical files. For example, you can

use math functions and operators to define new fields. Later on, we'll see a

field derived by multiplying two other fields.

A second advantage is that SQL is portable, whereas DDS is not. You could

create a duplicate database on a PC (stand-alone or network server) using one

of many PC-based database management systems. Users could use it for inquiries,

or you might use it for your own experimentation. You could take a database off

the AS/400, move it to a UNIX system, and write new client/server applications

to replace the existing green screen code.

Another reason may seem selfish, but you need to be aware of it. SQL experience

makes you more valuable to your employer or clients, or more marketable if you

ever need a job. You may currently work in an environment with only an AS/400

and some PCs, but your future may require you to work with UNIX systems, PC

LANs, or mainframes. The more knowledgeable you are about SQL, the better off

you will be.

In the following paragraphs, I'll illustrate SQL data definition on the AS/400

by creating portions of a sales system for the XYZ Corporation. The SQL

commands I'll use are all available in V2R3 of OS/400 and will work on most

existing SQL implementations. (Further on, I'll mention some V3R1 enhancements,

which are still not widely supported on other platforms). I'll give you some

equivalent DDS to help relate the SQL code to what you already know so well.

You'll see another advantage: SQL is easier to use and read than DDS.

A Rose by Any Other Name...

SQL is designed to work with relational databases, like DB2/400, but the SQL

world uses terms different from those AS/400 programmers are used to.

? On the AS/400, we create libraries to store objects. In SQL systems, objects

are stored in databases.

? Data is stored in physical files on the AS/400. In the SQL world, these are

known as tables.

? The AS/400 uses logical files to present the same data in alternate ways. SQL

databases use views and indexes.

? AS/400 database files consist of records, each of which is divided into

fields. SQL tables and views consist of rows divided into columns.

Don't let these terms throw you. When you create a table, for example, you'll

really be creating a physical file, which you can process with RPG programs,

clear with a Clear Physical File (CLRPFM) command, and in general treat as you

would any physical file.

One very important difference is in object qualification. The AS/400 convention

is LIBRARY/OBJECT, but the SQL convention calls for a period instead of a

slash. You may use either convention with SQL/400 by specifying *SYS or *SQL in

the NAMING parameter of the Start SQL (STRSQL) and Run SQL Statements

(RUNSQLSTM) commands, or in the OPTION parameter of the Create SQL (CRTSQLxxx)

commands. The default value is *SYS, and that's what I'll use in the examples

below.

SQL Commands that Create Objects

AS/400 programmers are accustomed to building libraries for the storage of

objects. SQL objects are stored in databases, which are implemented on the

AS/400 as a special type of library, called a collection. You can create a

collection with any of three SQL statements: CREATE DATABASE, CREATE

COLLECTION, or CREATE SCHEMA. CREATE DATABASE and CREATE COLLECTION are

equivalent, while CREATE SCHEMA allows you to create other objects at the same

time. The syntax to delete an object uses the DROP verbùfor example, DROP

DATABASE.

I will use CREATE DATABASE in these examples.

The Create Library (CRTLIB) command creates an empty library, CREATE DATABASE

creates a library with certain objects in it. These objects are a journal, a

journal receiver, and a set of logical files that support the SQL concept of a

catalog. The journal and journal receiver are for saving changes to the tables

in the collection.

The catalog consists of a set of tables (physical files on the AS/400) and

views (logical files on the AS/400) containing information about the data

defined in the database. You will find these system tables and views in most

SQL systems. On the AS/400 the catalog tables can be found in library QSYS and

include:

? QADBXREF

? QADBPKG

? QADBFDEP

? QADBXRDBD

Some of the views and their descriptions in the catalog are listed in 1.

Some of the views and their descriptions in the catalog are listed in Figure 1.

The following SQL statement illustrates how to create a database called

XYZCORP.

create database xyzcorp

The corresponding CRTLIB command would be:

CRTLIB LIB(XYZCORP)

Creating a database creates a place for you to store data, but it does not

define the data. To do that, you'll have to create tables. Since you just

learned that the SQL command to create a database is CREATE DATABASE, you'll

probably guess that the SQL command to create a table is CREATE TABLE.

The first thing you must tell the CREATE TABLE command is what the table name

will be and what database you intend to store it in. Then list the columns of

the table, separated by commas, in parentheses. You must specify what each

column is to be named and what type of data the column will store.

2 lists the data types you can use. Some data types, like VARCHAR,

Figure 2 lists the data types you can use. Some data types, like VARCHAR,

require that you specify a size. With other types, such as DECIMAL, the size is

optional. Yet other data types (DATE, for instance) do not allow you to specify

a field size.

Unlike fields defined by DDS, columns are allowed to contain the null value by

default. You may prohibit a column from being null by adding the phrases NOT

NULL or NOT NULL WITH DEFAULT. These two phrases differ in that NOT NULL

requires you to specify a value for a column when inserting a row into the

table, while NOT NULL WITH DEFAULT will supply a default value if you don't

specify one. As you would expect, the defaults are zero for numeric fields,

blanks for fixed length fields, and empty strings for variable length character

fields.

I won't spend a lot of time talking about the difference between null and

default values, but they are not the same. Let me give you an example. In a

table of hospital patients, there could be a column indicating whether a

patient is pregnant or not. For females, this column would be true or false;

for males, it would be null.

3 contains a CREATE TABLE command for a table of sales transactions.

Figure 3 contains a CREATE TABLE command for a table of sales transactions.

Compare the SQL CREATE TABLE statement to the equivalent DDS in 4.

Compare the SQL CREATE TABLE statement to the equivalent DDS in Figure 4.

SQL is a free-format language and case is not significant. I've aligned the

field definitions and used lowercase characters to make these statements more

readable.

Indexes

SQL indexes have traditionally been defined separately from their tables. You

can probably guess that creating an index over a table requires the CREATE

INDEX command. In SQL programming, indexes are used to speed up queries and to

prevent inserting duplicate primary keys and duplicate rows into a table.

Like all SQL commands, CREATE INDEX is simple in syntax. It begins with the

words CREATE INDEX or CREATE UNIQUE INDEX, followed by an index name (which

will be the name of the logical file on the AS/400). Specifying unique tells

the system not to allow duplicate values. This is followed by the word ON and

the name of the table. The last part of CREATE INDEX is a list of index

columns, separated by commas, in parentheses. Each column name may be followed

by ASC or DESC to indicate ascending or descending sort order. I've summarized

the CREATE TABLE syntax for you in 5.

the CREATE TABLE syntax for you in Figure 5.

The AS/400 creates these indexes as keyed logical files. All fields in the

physical file are included in the logical file.

6 shows some CREATE INDEX commands that might be used with the sales

Figure 6 shows some CREATE INDEX commands that might be used with the sales

table created in 3. The first one ensures that no two customers ever

table created in Figure 3. The first one ensures that no two customers ever

have the same customer number. The other two speed up retrieval against the

sales file. 7 contains DDS corresponding to the first CREATE INDEX

sales file. Figure 7 contains DDS corresponding to the first CREATE INDEX

command in 6.

command in Figure 6.

Views

A view is an alternate way of looking at data. Views provide a way to see only

certain rows and/or columns of a table. For instance, you might create a view

over a payroll file that omits rows with a certain department number, or you

could create a view that contains all columns in that payroll file except for

the salary column. Users who were not permitted to see such sensitive

information would use those views rather than the actual tables.

Like indexes, views are implemented as logical files on the AS/400. If the view

references two or more files, the system builds a join logical file.

I'm sure you've figured out that the SQL command to create a view is CREATE

VIEW. The rest of the CREATE VIEW command is also very easy (see 8 for a

VIEW. The rest of the CREATE VIEW command is also very easy (see Figure 8 for a

brief description of CREATE VIEW).

The column list is not required if there are no duplicate column names or

derived columns (columns calculated from other fields) in the view.

9 shows three examples of how you might create SQL views. In the first

Figure 9 shows three examples of how you might create SQL views. In the first

example, the view selects a subset of records. This would be similar to using

Select and Omit specifications in the DDS of a logical file.

In the second example, the sales transaction file is joined to the customer

master file on a common customer ID number. You could do this with DDS by

creating a join logical file.

The third example contains a command to create a view with a derived field. The

quantity sold is multiplied by the unit price to yield the extended price.

There is no DDS equivalent for this, but OPNQRYF can yield the same results.

SQL Commands that Document Data

The data definition commands we've looked at so far don't allow a way to add

comments. Documentation is handled by two other SQL commandsùLABEL ON and

COMMENT ON.

The LABEL ON command adds text descriptions to tables, views, and columns. With

a table or view, LABEL ON corresponds to the TEXT parameter of the Change

Physical File (CHGPF) and Change Logical File (CHGLF) commands.

For columns, LABEL ON has two forms. One form is equivalent to the TEXT keyword

used at the field level in DDS. The other is the same as the COLHDG keyword.

COMMENT ON is more portable, being found in most implementations of SQL.

COMMENT ON puts descriptions of the table and their columns in the REMARKS

column of views SYSTABLES and SYSCOLUMNS.

3 contains LABEL ON and COMMENT ON commands for the sales transactions

Figure 3 contains LABEL ON and COMMENT ON commands for the sales transactions

table. Compare them to the TEXT and COLHDG keywords in the DDS in 4.

table. Compare them to the TEXT and COLHDG keywords in the DDS in Figure 4.

V3R1 Enhancements

V3R1 of OS/400 has added a lot to the data definition commands of SQL/400.

These extensions bring SQL/400 closer to industry standards.

CREATE TABLE now allows you to specify primary key fields, specify a default

value, define referential constraints, and allocate storage for variable

character and graphic variable strings.

V3R1 also supports the ALTER TABLE command, which allows you to add or drop a

constraint to a table. For example you could add a referential constaint to a

transaction table so that the item number (TRITEMNO) in a transaction row must

exist as an item number (ITEMNO) in the item table. One of the most common uses

of ALTER TABLE on other systems is to dynamically add, remove, rename, and

redefine columns of existing tables and views. Although this powerful column

definition capability is not supported in V3R1 it is planned for the next

release.

Running SQL on Your System

If you have SQL/400, the easiest way to define a database is to store the SQL

statements in a member of a text file and execute the member with the RUNSQLSTM

command. The text file may contain commentsùlines that begin with a double

hyphenùand SQL commands, which may span more than one line and are terminated

with a semicolon. The SQL code in 3 could be executed by RUNSQLSTM.

with a semicolon. The SQL code in Figure 3 could be executed by RUNSQLSTM.

If you don't have SQL/400 on your system, you can use Query Management. This is

cumbersome, as only one SQL statement may be placed in a source member, and

these source members must be compiled with the Create Query Management Query

(CRTQMQRY) command before they can be executed. An alternative is to use MC's

EXCSQLSTM utility (see "The EXCSQLSTM Utility," MC, December 1994).

Choose Your Standard

Some wag has remarked that the great thing about standards is that there are so

many to choose from. SQL is no exception, in spite of the efforts of

organizations like the ANSI X3H2 Database Standards Committee. SQL varies from

platform to platform, as vendors choose not to implement parts of the standard

that don't conform to their systems, and add nonstandard extensions to take

advantage of the features of their architecture. Except for LABEL ON, the SQL

statements I've given you should work on just about any platform you find.

I recommend that SQL enthusiasts read Joe Celko's monthly "SQL Explorer" in

DBMS magazine (published by Miller Freeman, Inc.). Celko, a consultant,

educator, and member of X3H2, comes up with a lot of interesting things to

ponder.

Whether to use DDS or SQL to define databases is a decision that each shop must

make for itself. SQL is not going to give you everything DDS does when it comes

to defining data. For example, you can't reference column definitions from

other tables with SQL like you can reference field definitions from other files

with DDS.

But, with V3R1, SQL is more robust than ever before; most of what DDS can do

can be done with SQL. If you want to allow for extending your database

definition beyond the AS/400 or you want to use an industry standard tool, SQL

may be a good choice.

Ted Holt can be reached through Midrange Computing.

REFERENCES:

V2R3: AS/400 SQL/400 Programmer's Guide (SC41-9609-03, CD-ROM QBAK7F03).

V2R3: AS/400 SQL/400 Reference (SC41-9608-03, CD-ROM QBKA7H03).

V3R1: DB2/400 SQL Programming V3R1 (SC41-3611-00, CD-ROM QBKAQ800).

V3R1: DB2/400 SQL Reference V3R1 (SC41-3612-00, CD-ROM QBKAQ900).

Defining Data with SQL

Figure 1Data Definition Views in the SQL Catalog

 systables describes each table and view in the collection sysviews describes each view in the collection (includes the SQL statement used to generate the view) syscolumns describes columns in the collection sysindexes describes indexes in the collection (including indexes over the catalog) syskeys describes key fields in the indexes in the collection 
 
 Defining Data with SQL
 
 Figure 2SQL Data Types
 
 SMALLINT Binary integer (2 bytes) INT or Binary integer (4 bytes) INTEGER REAL or Single precision real number FLOAT(n) n is between 1and 24 FLOAT or Double precision real number FLOAT(n) or n is between 25 and 53 DOUBLE PRECISION DEC Packed decimal number DECIMAL n is the number of digits (default is 5) DEC(n) m is the number of decimal positions (default is 0) DECIMAL(n) DEC(n,m) DECIMAL(n,m) NUMERIC Zoned decimal number NUMERIC(n) n is the number of digits (default is 5) NUMERIC(n,m) m is the number of decimal positions (default is 0) CHAR Fixed length character field CHARACTER n is the length (default is 1) CHAR(n) CHARACTER(n) VARCHAR(n) Varying length character field n is the maximum length DATE Date TIME Time TIMESTAMP Timestamp GRAPHIC Fixed length graphic field GRAPHIC(n) n is the length (default is 1) VARGRAPHIC(n) Varying length graphic field n is the maximum length 
 
 Defining Data with SQL
 
 Figure 3SQL to Create a Table for Sales Transactions
 
 -- create the table create table xyzcorp/sales (salenbr numeric(9) not null, saledate date not null, custid dec(7) not null, itemid char(10) not null, qtysold dec(5) not null, price dec(7,2) not null with default, comment varchar(20), terms char(1)); -- add a text description to the physical file label on table sales is 'Sales transactions'; -- add a text description to the catalog comment on table sales is 'Sales transactions'; -- add text descriptions to the fields in the file label on xyzcorp/sales (salenbr text is 'Transaction number', saledate text is 'Date of sale', custid text is 'Customer number', itemid text is 'Item number', qtysold text is 'Quantity sold', price text is 'Unit price', comment text is 'Comment', terms text is 'Terms code'); -- add column headings label on xyzcorp/sales (salenbr is 'Xact number', saledate is 'Date of sale', custid is 'Customer number', itemid is 'Item number', qtysold is 'Qty sold', price is 'Unit price', comment is 'Comment', terms is 'Terms code'); -- add comments to the data dictionary -- comment on one column only comment on column sales.salenbr is 'Serial transaction number'; -- comment on a group of columns comment on sales ( saledate is 'Date of sale', custid is 'Customer number', itemid is 'Item number', qtysold is 'Quantity sold', price is 'Unit price', terms is 'Terms code'); 
 
 Defining Data with SQL
 
 Figure 4DDS to Create a Sales Transactions File
 
 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A R SALES A SALENBR 9S 0 TEXT('Transaction number') A COLHDG('Xact' 'number') A SALEDATE L TEXT('Date of sale') A COLHDG('Date' 'of' 'sale') A CUSTID 7P 0 TEXT('Customer number') A COLHDG('Customer' 'number') A ITEMID 10A TEXT('Item number') A COLHDG('Item' 'number') A QTYSOLD 5P 0 TEXT('Quantity sold') A COLHDG('Qty' 'sold') A PRICE 7P 2 TEXT('Unit price') A COLHDG('Unit' 'price') A COMMENT 20A TEXT('Comment') A COLHDG('Comment') A VARLEN ALWNULL A TERMS 1 TEXT('Terms code') A COLHDG('Terms' 'code') A ALWNULL *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 
 
 Defining Data with SQL
 
 Figure 5Syntax of CREATE INDEX Command
 
 create [unique] index INDEX on TABLE (COLUMN LIST) INDEX = name of index TABLE = name of table over which index is created COLUMN LIST = list of column names with optional "asc" or "desc" to indicate order, separated by commas 
 
 Defining Data with SQL
 
 Figure 6Examples of CREATE INDEX Commands
 
 create unique index xacts on sales (salenbr) create index sales001 on sales (custid, itemid) create index sales002 on sales (price desc, itemid) 
 
 Defining Data with SQL
 
 Figure 7DDS to Create a Logical File over the Sales Transa
 
 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A UNIQUE A R SALES PFILE(SALES) A K SALENBR 
 
 Defining Data with SQL
 
 Figure 8Syntax of the CREATE VIEW Command
 
 create view VIEW [(COLUMN LIST)] as SELECT VIEW = name of view COLUMN LIST = optional list of column names separated by commas SELECT = an alternative way of looking at the data in one or more tables 
 
 Defining Data with SQL
 
 Figure 9SQL View Examples
 
 -- Example 1 create view xyzcorp/cashsales as select * from sales where terms = 'C' -- Example 2 create view xyzcorp/custsales as select x.custid,name,itemid,saledate,qtysold,price from sales x, customer cm where x.custid=cm.custid -- Example 3 create view xyzcorp/salesdata (xact, xactdate, custid, itemid, qty, price, xprice) as select salenbr,saledate, custid, itemid, qtysold, price, qtysold * price from sales 

Ted Holt is IT manager of Manufacturing Systems Development for Day-Brite Capri Omega, a manufacturer of lighting fixtures in Tupelo, Mississippi. He has worked in the information processing industry since 1981 and is the author or co-author of seven books. 

---

MC Press books written by Ted Holt available now on the MC Press Bookstore.

		Complete CL: Fifth Edition Become a CL guru and fully leverage the abilities of your system. List Price $79.95 Now On Sale
		Complete CL: Sixth Edition Now fully updated! Get the master guide to Control Language programming. List Price $79.95 Now On Sale
		IBM i5/iSeries Primer Check out the ultimate resource and “must-have” guide for every professional working with the i5/iSeries. List Price $99.95 Now On Sale
		Qshell for iSeries Check out this Unix-style shell and utilities command interface for OS/400. List Price $79.95 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions