I've been using SQL to create files for years now. I've used IDENTITY columns (love them!) as well as the PRIMARY KEY clause to create a S/38-like keyed physical file. But I never bothered until recently, to worry about the UNIQUE keyword in DDS when creating physical files. I have used it, but so rarely that it has never come up, until recently. [Without the UNIQUE keyword in DDS, files are created with non-unqiue access paths. This is what I was working with.]

I needed to create a table with a unique key. Why unique? Because I wanted the SQL INSERT command to fail if the user attempted to add a new record to the file with a key that already existed in the file. For example, when two identical INSERT statements are issued; the first would succeed, the second sould fail.

There is a new MERGE statement that works on the very latest releases (latest TR's) of the OS. The MERGE statement works more like the old RPG I/O "Test if record exists, if so, update it, otherwise write it to the file". What I need is something that just works system-wide, with worrying about CHAIN/UPDATE/WRITE or MERGE being used properly; I need UNIQUE primary keys.

Most of you are familiar with creating a file using SQL DDL, if not here's a quick example:

CREATE TABLE QGPL/README (
  textData varchar(50) CCSID 37 NOT NULL DEFAULT '',
  crtdate DATE NOT NULL DEFAULT CURRENT_DATE
 );

This creates a 2-column table (a file with two fields) named README. The two fields are TEXTDATA and CRTDATE. The CRTDATE field is automatically populated with the date the record is added to the file, while TEXTDATA is presumably provided by the user.

If I wanted to add a key to this file so I could, for example, remove certain rows of data directly, I could use some sort of application generated index, or preferably I would use an IDENTITY column as follows:

CREATE TABLE QGPL/README (
 LINENO INTEGER PRIMARY KEY 
           GENERATED ALWAYS AS IDENTITY 
        (START WITH 31, INCREMENT BY 1), 
 textData varchar(50) CCSID 37 NOT NULL DEFAULT '',
 crtdate DATE NOT NULL DEFAULT CURRENT_DATE
 );

The field LINENO (Line Number) has been added. The "GENERAETED ALWAYS AS IDENTITY" causes the database to automatically set the value of this field, incrementing it by 1 upon an INSERT operation. Note that I've started LINENO at 31 just for fun. I use IDENTITY columns all the time.

But now back to the original reason for this post. I need to create a file with a UNIQUE Primary Key. Note that in the README file, the IDENTITY column includes the "PRIMARY KEY" clause. This creates an index over the file by LINENO. The system automatically generates the nexxt LINENO value by incrementing by 1. This insures you always have the next LINENO and a unique key value with which to access the individual rows (records) in that file. But what if I have another field, like Customer Number or Vendor Code that needs to also be unique in the file?

For customer numbers, you can use either a SEQUENCE object or an IDENTITY column as I've done here. But for text-based values that are also user-supplied (not auto-generated by the database), so you have to code things a bit differently.

In this scenario, I have a file named ITEMS containing a 5-character ITEM "number" that is a alphanumeric. I also want an IDENTITY column, just to make it really easy to delete specific records from the file. To make this happen, the table would need to look something like this:

CREATE TABLE QGPL/ITEMS (
 CID INTEGER GENERATED ALWAYS AS IDENTITY,
 ITEM CHAR(5) CCSID 37 NOT NULL, 
 TITLE VARCHAR(50) CCSID 37 NOT NULL DEFAULT '',
 UNIQUE(ITEM) 
 );

The CID field is the identity column and the database takes care of populating it with the next available value. The ITEM field is a 5-character field whose value must be unique within the file. The UNIQUE(ITEM) keyword forces that to occur. Unlike Primary key fields, UNIQUE key fields may be NULLable. So the NOT NULL required by PRIMARY KEY, doesn't apply to UNIQUE keys. The last field, TITLE, is user-supplied information that can be anything.

To add records to the ITEMS file, the INSERT statement (or RPG WRITE opcode) may be used.

INSERT INTO QGPL/ITEMS (ITEM,TITLE) VALUES('RPG', 'The Modern RPG Language');

Great! Now we have 1 record in the file. Let's add two more:

INSERT INTO QGPL/ITEMS (ITEM,TITLE) VALUES('SQL', 'The Modern SQL Language');
INSERT INTO QGPL/ITEMS (ITEM,TITLE) VALUES('RPG', 'Writing Subfiles with RPG');

Oh no! The last INSERT contains the same value for ITEM as the first INSERT operation. What happens? The system will issue an SQL error code, and that 2^nd record, it will go away--it is not added to the file.

This is the advantage of keyed tables vs non-keyed tables.

Pretty cool, huh?