Our example table is starting to look like a proper one! Let’s continue with a couple of typical SQL features: default values for optional columns and audit-related columns. These may seem like simple things, but they’ll save you a ton of time.

Time. That’s more and more the issue, especially when something goes wrong and you need to get things done quickly. So let’s introduce a couple of time-saving features into our example table; you’ll thank me later for them!

Providing Proper Default Values for Optional and Audit-Related Columns

Now that I have created a primary key for this table, let’s see what other enhancements can be performed. A quick inspection of the application’s student enrollment screen shows that the fields Email address, Driver’s license, and Social Security Number have a default value of N/A. However, this is an application-only behavior: if a record is inserted via SQL and a value for these fields is not provided, they’ll be left blank because that was the default value specified on the CREATE TABLE statement. The screen also shows the current user, time, and date, even though those details are not stored with the student’s record. This is a shortcoming of the application, because auditors really like to know this kind of stuff when they come snooping around.

Let’s address all of these issues. Again, I could destroy the table with a DROP statement, fix the CREATE TABLE statement, and recreate the table. However, this would also delete the data in the table, if there is any. Instead, I’ll use another ALTER TABLE statement:

ALTER TABLE UMADB_CHP3.PFSTM

ADD COLUMN STCU VARCHAR(18) DEFAULT USER

ADD COLUMN STCT TIMESTAMP DEFAULT CURRENT TIMESTAMP

   ALTER COLUMN STEM SET DEFAULT 'N/A'   

   ALTER COLUMN STDL SET DEFAULT 'N/A'

   ALTER COLUMN STSN SET DEFAULT 'N/A'

   ALTER COLUMN STSC SET DEFAULT '1'

;

A couple of notes about this statement: First, it shows that you can change more than one thing at a time; I’m adding new columns and changing existing ones at the same time. Second, there are a few keywords, known as special registers, that you can use to refer to “system things,” such as the current user’s name or the current timestamp. There are others, such as CURRENT_DATE, CURRENT_TIME, CURRENT TIMEZONE, CURRENT SERVER, and CURRENT SCHEMA, to name just a few. These will allow you to mimic a typical native application’s behaviors. In fact, storing information about the record-creation user and timestamp is a good practice—and the auditors love stuff like that. I’ll show you later how to do the same for the last update user and timestamp, another auditor-favorite feature of a proper table.

I don’t want to sound repetitive, but remember: SQL is not DDS. If you add new columns to a table, always remember to execute the appropriate LABEL ON statement to provide clear and concise descriptions for your columns. Let’s do that for the three new columns added to PFSTM:

LABEL ON COLUMN UMADB_CHP3.PFSTM

   (

      STID TEXT IS 'Record ID'

      , STCU TEXT IS 'Created by'

      , STCT TEXT IS 'Created on'

   )

;

If you back up a bit, to the last ALTER TABLE statement, you’ll probably notice I also provided a new default for the STSC column. This is just another example of how you can simplify data insertion: specifying that new records are in active status ('1') by default saves me the trouble of including that information on future INSERT statements. Speaking of INSERT statements, let’s see how the table changes I’ve performed impact those statements. Here’s how I’d mimic the native application’s student enrollment functionality before my ALTER TABLE statements:

INSERT INTO UMADB_CHP3.PFSTM

(STNM, STDB, STAD, STPN, STMN, STEM, STDL, STSN, STSC)

VALUES(

'Mailer, Norman'

      , 19750318

      , '2234 Blackstone Avenue, Joyville, South Carolina'

      , '555-001-123'

      , '999-010-469'

      , 'N/A'

      , 'N/A'

      , 'N/A'

      , '1'

)

;

Even though I don’t have a value for the email, driver’s license, and Social Security Number, I still had to mention them on the INSERT in order to force the appropriate “not available” value the application is expecting. I also had to include STSC with the value '1' to indicate that the record is active. Now let’s see how the same INSERT looks after the changes performed via ALTER TABLE:

INSERT INTO UMADB_CHP3.PFSTM

(STNM, STDB, STAD, STPN, STMN)

VALUES(

'Mailer, Norman'

      , 19750318

      , '2234 Blackstone Avenue, Joyville, South Carolina'

      , '555-001-123'

      , '999-010-469'

)

;

The statement is much shorter, but there’s no loss of functionality because the defaults are used by the database engine to fill the columns for which I didn’t specify a value. Additionally, the three new columns (the unique ID, record-creation user, and respective timestamp) are automatically filled in by the system.

Time for Some Practice

Before moving on to the section on views, it’s time for you to practice what you’ve learned so far. Try to modify the CREATE TABLE and LABEL ON statements that were automatically generated for the rest of the tables in order to include the three new columns I added to PFSTM: the unique ID, the creation user, and the creation timestamp. Try following the same naming convention I’m using for the column names: a two-character prefix indicates the table (for instance, CO for courses) followed by two characters that define the type of value the column will hold (for instance, CU for creation user). Let me get you started: here are the modified CREATE TABLE and LABEL ON statements for the Grades table:

CREATE TABLE UMADB_CHP3.PFGRM

   (

      GRID INTEGER

         PRIMARY KEY

         GENERATED ALWAYS

         AS IDENTITY(START WITH 1

               INCREMENT BY 1)

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

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

      , GRCY DECIMAL(4, 0) NOT NULL DEFAULT 0

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

      , GRCU VARCHAR(18) DEFAULT USER

      , GRCT TIMESTAMP DEFAULT CURRENT TIMESTAMP

   )       

   RCDFMT PFGRMR

;

LABEL ON TABLE UMADB_CHP3.PFGRM

            IS 'UMADB - Grades Master File' ;

LABEL ON COLUMN UMADB_CHP3.PFGRM

   (

      GRID TEXT IS 'Record ID'

      , GRSN TEXT IS 'Student name'

      , GRCN TEXT IS 'Class name'

      , GRCY TEXT IS 'Class year'

      , GRGR TEXT IS 'Grade'

      , GRCU TEXT IS 'Created by'

      , GRCT TEXT IS 'Created on'

   )

;

The next task is to copy the data from UMADB_CHP2’s physical files to UMADB_CHP3’s tables. You can either use the traditional CPYF or use what you’ve learned in the previous subseries and issue INSERT ... SELECT statements instead. If you need some help, you can find a file with all the necessary statements here.

Now that our tables are proper SQL tables, we can start working on other things to make our database easier to use: SQL Views. But that’s for the next article…

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

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

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