Time to start a new subseries! This time, we will work on making the database more user-friendly, or, if you prefer, less “IBM i idiosyncratic”. As we open our DB2/400 database to the outside world, more and more people who are not accustomed to 10-letter column and table names will want to use it. Let’s help them by providing longer, more human-readable names while also maintaining the ones we (and our programs) are used to.

Written by Rafael Victoria-Pereira

Following up on the previous subseries on Data Definition Language (DDL), this one will focus on making the database more user-friendly by using longer and more descriptive names for tables and their respective columns, and explaining how to create views to hide database complexity from users, among other things.

The previous subseries led us to the first step toward an accurate database. Still, it retained most of the hallmarks that make a DB2/400 database very “user-unfriendly”: the table and column names are short, in the typical and old-fashioned manner of the AS/400. However, the demands of end users regarding data queries have evolved significantly in recent years. The users of our UMADB are particularly data-hungry and are overtaxing the university’s IT staff labor resources. This added burden on the IT staff is something that often occurs when you “open up” the database to the end users. The problem is that the database is not always ready to be used by someone not used to short, cryptic names, and this ends up causing additional stress on the IT staff, because it requires extra time and effort to “explain the database” to users and help them navigate the nearly indecipherable table and column names.

Using Longer Table and Column Names

The next step is to fix that by providing longer names that users can relate to. As you probably guessed, this will require changes to the tables’ definition. Just as was explained in the previous subseries, this operation can be performed with a DROP TABLE/CREATE TABLE combination.

Let’s help the university’s IT staff and enhance the Grades table. However, first, make sure to duplicate the UMADB_CHP3 library to a new UMADB_CHP4 library. This way, you can always “go back” and repeat any of the statements mentioned in this subseries. Previously, all tables were changed to include a primary key and some auditing information. After these changes, PFGRM looks like Table 1.

Table 1: The Grades table structure

Now we’re going to make it a bit more usable for non-IT people. Time for some hands-on reading! When you’re ready, type the following statement:

DROP TABLE UMADB_CHP4.PFGRM;

And issue the new CREATE TABLE statement, with longer names for the table and its columns:

CREATE TABLE UMADB_CHP4.TBL_GRADES

   (

      GRADE_ID FOR COLUMN GRID INTEGER

         PRIMARY KEY

         GENERATED ALWAYS

         AS IDENTITY(START WITH 1

               INCREMENT BY 1)

      , STUDENT_NAME FOR COLUMN GRSN CHAR(60) CCSID 37 NOT NULL DEFAULT ''

      , CLASS_NAME FOR COLUMN GRCN CHAR(60) CCSID 37 NOT NULL DEFAULT ''

      , CLASS_YEAR FOR COLUMN GRCY DECIMAL(4, 0) NOT NULL DEFAULT 0

      , GRADE FOR COLUMN GRGR CHAR(2) CCSID 37 NOT NULL DEFAULT ''

      , CREATED_BY FOR COLUMN GRCU VARCHAR(18) DEFAULT USER

      , CREATED_ON FOR COLUMN GRCT TIMESTAMP DEFAULT CURRENT TIMESTAMP

   )       

   RCDFMT PFGRMR

;

Now let’s test it:

SELECT * FROM UMADB_CHP4.TBL_GRADES;

You should get no results, because we’ve just created the table, but the statement shouldn’t end in error. Notice, however, that the column names have changed: you now see longer, human-readable column names instead of the cryptic four-letter acronyms. The “magic” is being provided by the FOR COLUMN instruction on each column specification. The syntax is quite simple: instead of simply specifying the column name, as we did before, we’re now saying something like “this SQL column has a system name of yyy,” where the yyy is the cryptic four-letter name, and the SQL column name is the more extended version. Let’s review an example to make this more straightforward. Look at the student name line:

, STUDENT_NAME FOR COLUMN GRSN CHAR(60) CCSID 37 NOT NULL DEFAULT ''

Here, you have the SQL name, STUDENT_NAME, followed by the FOR COLUMN instruction, which in turn is followed by what was already present in the table creation statement from the previous subseries: the “old” column name, GRSN, and the rest of the column definition.

As explained previously in this series, in the section discussing view creation, it is possible to access the column using both the “old” and “new” names. This is beneficial because programs can continue to use the short names, while people can now use the longer ones. Unfortunately, this doesn’t apply (not yet, anyway) to the table name: the following statement will produce an error, complaining that the table doesn’t exist:

SELECT * FROM UMADB_CHP4.PFGRM;

Why is that? Well, it’s because we created an SQL table with the columns of PFGRM, but it has a different name. PFGRM doesn’t exist on UMADB_CHP4—yet. To keep all the programs that use PFGRM working and recognizing TBL_GRADES as the PFGRM that they “know,” we’ll need to create an alias, like this:

CREATE ALIAS UMADB_CHP4.PFGRM FOR UMADB_CHP4.TBL_GRADES;

If the new table name were not a valid system name, this extra step wouldn’t be necessary. In this particular case, it is because TBL_GRADES is 10 characters long and doesn’t start with a “forbidden” character. After issuing this CREATE ALIAS statement, you should be able to run the SELECT statement using the “old” name of the grades table.

The subseries will continue next time, with a quicker method for performing this process. We will apply it to the STUDENTS table, in a single step. Curious about how it’s done? Keep an eye out for the next article in this subseries! In the meantime, feel free to comment, correct, or suggest using the Comments section below.

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