Now that we have basic SQL tables that are still RPG-friendly, let’s start to make them more recognizable (and usable) for non-RPG developers by adding some typical SQL features, like keys, default values for optional columns, etc. In this article we’ll cover adding a unique key and we’ll look at the other features later.

So far in this subseries, we have generated CREATE TABLE statements from old-style physical files and created a new schema with these statements, after a few tweaks. It’s time to start adding those features that make our tables part of a recognizable database.

Moving a Step Closer to a Real SQL Table

Now that we have the PFSTM table created in UMADB_CHP3, let’s start enhancing it. The objective is to make the data handling less application-dependent by “pulling down” to the database level some application features that impact the way data is stored and handled in the system. This allows other, non-native applications to handle the database as the native RPG application would. As you’ll see, this is not a simple process, and I’ll just scratch the surface in this subseries, with a few changes. But I promise to get back to it at a later date, so don’t worry!

Adding a Unique, Self-Managed Key

The first step is to create a unique key, controlled by the database. Remember, the table already has a key, the student name column (STNM), but its uniqueness is enforced by the application. This quickly becomes a problem when other, non-native applications start using the database. In order to maintain the database, a consistent and coherent unique key, enforced at the table level, is required. In an ideal situation, you’d go a step further and implement a key that is transparent for the outside world: a key that automatically gets a new and unique value every time a record is inserted. DDS can enforce this with the UNIQUE keyword. However, it can’t automatically generate a value that’s unique. SQL can specify unique keys that are generated by the database engine. Let’s implement one on the Students table and make it the table’s primary key.

Instead of using DROP to destroy the table, let’s change the table structure with the ALTER TABLE instruction. But don’t worry, I’ll discuss DROP later. The ALTER TABLE instruction is to an SQL table what the Change Physical File (CHGPF) CL command is to a DDS-defined physical file: it allows you to change certain characteristics of the table without destroying and recreating it. In this case, I’ll use it to add a primary key column named STID. Keep in mind that I’m changing an empty table in UMADB_CHP3, but this could be performed over any SQL table. Here’s the statement to add a primary key to PFSTM:

ALTER TABLE UMADB_CHP3.PFSTM

   ADD COLUMN STID INTEGER

         PRIMARY KEY

         GENERATED ALWAYS

         AS IDENTITY(START WITH 1 INCREMENT BY 1)    

;

As this might be new to you, let me go over it line by line. The ALTER TABLE statement is fairly straightforward up to the third line, where I’m defining STID as this table’s primary key. The fun starts on the following line: GENERATED ALWAYS ensures that a value for this column is generated automatically by the database engine. Then AS IDENTITY indicates that this is an auto-incrementing column to which you do not have to assign values. In fact, you’re not allowed to assign values to an auto-incrementing column unless you override this restriction on an INSERT using the OVERRIDING SYSTEM VALUE keyword (which overrides GENERATED ALWAYS so it works like GENERATED BY DEFAULT instead for the duration of the INSERT).

The rest of the fifth line of the statement defines how the database engine will assign those values. Note that defining this column as INTEGER limits the maximum value of the unique key to 2,147,483,647. If you feel that this is not enough, can use a BIGINT instead, which has a max value of 9,223,372,036,854,775,807, according to IBM’s DB2 for i SQL Reference manual. If this weren’t a primary key column, you could specify the CYCLE keyword, which would cause the number generation to reset to the initial value (1 in this case) when the maximum possible value is reached.

Identity columns were added to DB2 for i in V5R2 and are immensely useful, but some people don’t even know they exist. Because they simplify application development, all tables should be defined with a unique primary key, and identity columns are often an excellent choice for tables without a non-changing natural key. If you’re not familiar with the concept, don’t worry; I’ll explain it in detail later in this series.

Next time, I’ll explain how to add default values for optional columns and audit-related columns, which you’ll be very appreciative of when you’re digging through the data at some point in the future.

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