Let’s start the move from DDS physical files to DDL tables. As you’ll see, there’s more to this move than a change of name. Keep reading to find out what changes!

If DDS physical files are the large container ships that hold your data in a turbulent and ever-changing sea of users, business logic, communications, and programs, SQL tables are the luxury yachts. They provide the same functionality as their DDS counterparts but offer a slew of additional features that IBM keeps increasing with each OS release. The many possibilities provided by the CREATE TABLE SQL instruction are the best proof of this silly little nautical metaphor. In a previous TechTip, I used a made-up Invoice Master physical file to explain the SELECT SQL instruction. In case you don’t remember, here’s a table describing the fields on that file:

Let’s analyze the main features of CREATE TABLE and then see how the InvMst physical file definition would look if it were created as an SQL table.

Here’s the CREATE TABLE syntax:

CREATE TABLE <table SQL name> [FOR SYSTEM NAME <table system name>]

(<column_1_SQL_name> FOR COLUMN <column_1_System_name> <column1_data_type> <column_1_default_options> <colum_1_identity_options> <column_1_constraint_options>],

… ,

<column_N_SQL_name> FOR COLUMN <column_N_System_name> <column_N_data_type> <column_N_default_options> <colum_N_identity_options> <column_N_constraint_options>])

It seems complicated, but it’s just a different way of specifying information. Of course, there are a lot features available, but I’m saving them for later. Let’s apply this syntax to our InvMst physical file:

CREATE TABLE MYLIB/INVMST

(ITEMID     CHAR ( 15) NOT NULL WITH DEFAULT,

LOTNBR      DEC ( 13, 0)      NOT NULL WITH DEFAULT,

EXPDATE     DATE        NOT NULL WITH DEFAULT,

WHID        DEC ( 8, 0)       NOT NULL WITH DEFAULT,

SHELFID     DEC ( 12, 0)      NOT NULL WITH DEFAULT,

ITEMUN      CHAR ( 3)   NOT NULL WITH DEFAULT,

ITEMQTY     DEC ( 9, 2)       NOT NULL WITH DEFAULT)

The indentation is not mandatory, but (as usual) it improves readability and future maintenance, so please use it at all times! Depending on the naming convention used, the first line of this statement can either be as shown above, for the *SYS naming convention, or this for the *SQL naming convention:

CREATE TABLE MYLIB.INVMST

To do things the “totally SQL” way, you’d also replace MYLIB with a schema name, using, for instance, the MYSCHEMA schema created on the previous TechTip. Next, notice the column definitions. I kept the same names, but I could have chosen longer, more-descriptive ones. The data type definitions should look familiar, especially because the PF data types have a direct SQL data type equivalent in this case.

Then, there’s the NOT NULL WITH DEFAULT “column option” after each data type. This is related to the way physical files and tables handle the absence of data. A DDS-defined file assumes, unless you specify otherwise, that the absence of data in a numeric field, regardless of its particular type, should return a zero when the field is read. Things work differently in a DDL-defined table, however. By default, the absence of value is returned as null value. The NOT NULL WITH DEFAULT serves the purpose of forcing the PF’s default behavior onto the table.

Now let’s start adding bells and whistles to the table definition:

• Taking advantage of the fact that SQL allows longer names, I’ll provide more descriptive column names and maintain the existing names as “system names.”
• I know that ItemID, WHID, and ShelfID form this table’s primary key, so I can also add that definition here.
• I also know that the record format name of an SQL table is the same as the table name by default, so I’ll change that to prevent problems when InvMst is used in an RPG module.
• To demonstrate how you can use the table definitions to replace RPG code, I’ll add a Last_Changed timestamp column, common in audited files. The difference here is that I’ll have the database engine take care of the column update operations instead of writing RPG code each time a record is inserted or updated.

Let’s see how the new CREATE TABLE statement looks with these changes:

CREATE TABLE MYSCHEMA.TBL_INVENTORY_MASTER

FOR SYSTEM NAME INVMST

(ITEM_ID          FOR COLUMN ITEMID

CHAR ( 15)             NOT NULL WITH DEFAULT,

LOT_NUMBER             FOR COLUMN LOTNBR

DEC ( 13, 0)     NOT NULL WITH DEFAULT,

EXPIRATION_DATE FOR COLUMN EXPDATE

DATE             NOT NULL WITH DEFAULT,

WAREHOUSE_ID           FOR COLUMN WHID

DEC ( 8, 0)            NOT NULL WITH DEFAULT,

SHELF_ID         FOR COLUMN SHELFID

DEC ( 12, 0)           NOT NULL WITH DEFAULT,

ITEM_IN_UNIT           FOR COLUMN ITEMUN

CHAR ( 3)        NOT NULL WITH DEFAULT,

ITEM_QUANTITY    FOR COLUMN ITEMQTY

DEC ( 9, 2)            NOT NULL WITH DEFAULT,

LAST_CHANGED           FOR COLUMN LSTCHG

TIMESTAMP        NOT NULL

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,

PRIMARY KEY (ITEM_ID, WAREHOUSE_ID, SHELF_ID))

RCDFMT ITMMSTR

The first thing you notice is that this is a much longer statement. In part, this is related to indentation. I chose to divide each column’s definition into two lines to remind you that you’re in no way restricted by column or line restrictions.

Now let’s look at the implementation of those four changes:

• Providing more descriptive names was achieved by changing the column name to longer names (up to 128 characters long) and including a FOR COLUMN definition for each column to maintain compatibility with any programs that might have been using the old, shorter names.
• A clear definition of the table’s primary key is isolated in this statement:

PRIMARY KEY (ITEM_ID, WAREHOUSE_ID, SHELF_ID)

There’s another way to do this, which you’ll see in one of the next examples.

• Overcoming the “table name is the same as the record format name” issue that I hear so many people complaining about is easily achieved with RCDFMT ITMMSTR, which “renames” the table record format to ITMMSTR.
• Finally, I’ve added an audit column that “automagically” keeps track of the row’s last update timestamp, with this definition:

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Note that I could have omitted the data type definition of the LAST_CHANGED column, because the database engine would have figured it out by itself(!), but I chose not to do this because it might be confusing.

I changed the naming convention to SQL and used a schema name (MYSCHEMA) instead of the library name; I also used a longer table name. Let’s put this last feature to the test with an INSERT statement:

INSERT INTO MYSCHEMA.TBL_INVENTORY_MASTER

(ITEM_ID, LOT_NUMBER, EXPIRATION_DATE, WAREHOUSE_ID,

SHELF_ID, ITEM_IN_UNIT, ITEM_QUANTITY)

VALUES('A123', 1, Date('2015-12-31'), 24, 12, 'KG', 100)

Notice anything missing? I didn’t specify the LAST_CHANGED column, but if you run a SELECT after this insert, you’ll see that the database manager took care of its value. This is only the tip of the iceberg; in the next TechTip, I’ll revisit these concepts and add some more!

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. 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