In the previous article, I showed how to make a table and its columns more user-friendly for people who are not used to IBM i’s 10-character naming limitations. Let’s continue that process with a faster approach.

Let’s repeat the process we performed on the previous article for PFSTM, the Students table, so that I can show you how to do the renaming, or more accurately, double-naming process, in one step. The current Students table looks like Table 1.

Table 1: The Students table structure

Again, we’re going to use DROP TABLE to get rid of the existing PFSTM:

DROP TABLE UMADB_CHP4.PFSTM;

And then we’ll issue a modified CREATE TABLE statement to create a table recognizable by its SQL and system names:

CREATE TABLE UMADB_CHP4.TBL_STUDENTS

   FOR SYSTEM NAME PFSTM

   (

      STUDENT_ID FOR COLUMN STID INTEGER

         PRIMARY KEY

         GENERATED ALWAYS

         AS IDENTITY(START WITH 1

               INCREMENT BY 1)

      , NAME FOR COLUMN STNM CHAR(60) CCSID 37 NOT NULL DEFAULT ''

      , DATE_OF_BIRTH FOR COLUMN STDB DECIMAL(8, 0) NOT NULL DEFAULT 0

      , HOME_ADDRESS FOR COLUMN STAD CHAR(60) CCSID 37 NOT NULL DEFAULT ''

      , HOME_PHONE_NBR FOR COLUMN STPN CHAR(15) CCSID 37 NOT NULL DEFAULT ''

      , MOBILE_NBR FOR COLUMN STMN CHAR(15) CCSID 37 NOT NULL DEFAULT ''

      , EMAIL_ADDRESS FOR COLUMN STEM CHAR(60) CCSID 37 NOT NULL DEFAULT 'N/A'

      , DRIVERS_LICENSE FOR COLUMN STDL CHAR(20) CCSID 37 NOT NULL DEFAULT 'N/A'

      , SOCIAL_SEC_NBR FOR COLUMN STSN CHAR(11) CCSID 37 NOT NULL DEFAULT 'N/A'

      , STUDENT_STATUS FOR COLUMN STSC CHAR(1) CCSID 37 NOT NULL DEFAULT '1'

      , CREATED_BY FOR COLUMN STCU VARCHAR(18) DEFAULT USER

      , CREATED_ON FOR COLUMN STCT TIMESTAMP DEFAULT CURRENT TIMESTAMP

   )

   RCDFMT PFSTMR

;

Here, the long names are truly descriptive: SOCIAL_SEC_NBR is a bit abbreviated, but it’s far more apparent than STSN. Note that I’ve added a FOR SYSTEM NAME PFSTM instruction (see the second line of the statement), which removes the need for a separate CREATE ALIAS statement. This is very practical, but not always possible: you can only do this if you use an SQL name that’s not a valid system name, as I mentioned before.

This table is particularly interesting because it has some moderately long names that users might or might not want to use. I’m bringing this up to show you that you can refer to short and long names on the same statement; the database engine will figure out to which column you’re referring and show the appropriate data. Here’s an example:

SELECT STUDENT_ID

      , NAME

      , STDB

      , HOME_ADDRESS

      , STPN

FROM UMADB_CHP4.TBL_STUDENTS

;

As you can see here, I’m using a mix of short and long names in this statement. This can be especially useful when the long names are too long or you’re in a hurry to get things done. The RPG programs will still work as before, because they “see” the column system names, which are the same as the “old” DDS field names. You (and your end users) get the best of both worlds with this simple technique, but keep in mind that you need to backup the table’s data before performing the DROP operation and restore it (using an INSERT statement with a nested SELECT, for instance) once you’ve created the table with the appropriate table and column names.

Time for Some Practice

By now, you’ve realized the power of longer and more descriptive names. Before changing the tables in your application, why don’t you practice with the rest of UMADB’s tables? If you’ve been following this series, you should have the complete set on the UMADB_CHP3 library by now.

There’s something I’ve neglected to mention, and it’s a bit important: naming conventions. I followed a simple naming convention for the table names, prefixing them with “TBL_”. I’ll do the same later with the views, using the “VIEW” prefix, and the indexes, resorting to the “Index” prefix. I followed a looser naming convention for the column names, but adhered to the so-called underscore convention: I used an underscore character (_) to separate the words in the column name. It’s a bit longer, but it’s far more clear than camel case, mainly because SQL tends to return column names in all caps when you query it. I followed a few other, more subtle conventions regarding the column names: for instance, all the ID and status columns were prefixed with the name of the table; whenever a table contains data from another table, I indicated where the data is coming from—for instance, there’s a “student name” column on the Grades table, so its long name is Student_Name.

With this information, you should be able to create all the tables on the UMADB database. That’s all for now. Next time, we will be talking about ways to make the database more accessible by hiding its complexity and mapping it in a user-friendly way.

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