Guarantee Unique Key Values

Hey, Ted:

I agree that using surrogate keys causes more problems than it solves ["Pros and Cons of Surrogate Keys"]. Besides obscuring the key values, you need to have an application that creates the surrogate key, to ensure uniqueness among all of the related files.

--Jim

What you need is an identity column. An identity column is an auto-incrementing field to which you do not have to assign values. In fact, you can't assign values to them. Identity columns were added to DB2/400 in V5R2, and they are yet another one of those features that you can't take advantage of through DDS.

In the following table, the primary key field, MemberID, is defined as an identify column. The first record inserted into the table will be assigned the number 10001.

create table mylib/members
  (MemberID dec (5)
            not null
            generated always
            as identity
            (start with 10001),
   MemberName char (20),
   primary key (MemberID))

To add a row to the table, assign values to any of the non-key fields. In this example, there's only one non-key field: MemberName.

insert into mylib/members (membername) values('Joe Smith')
insert into mylib/members (membername) values('John Smythe')

You can guess that Joe Smith and John Smythe become members 10001 and 10002 respectively.

select * from members
MEMBERID  MEMBERNAME
 10,001   Joe Smith
 10,002   John Smythe

Now, back to the subject of surrogate keys. To use an identity column as a foreign key in another table, you've got to know what value the system assigned to the inserted row. Use the VALUES command with the IDENTITY_VAL_LOCAL( ) special register to load the assigned value into a host variable, as shown in the following RPG program fragment. After VALUES runs, the ID variable contains the member ID number that was assigned to the new row.

D Name            s             20a
D ID              s              5p 0

C/exec sql
C+     insert into mylib/members (membername)
C+            values(:Name)
C/end-exec
C/exec sql            
C+     values identity_val_local()
C+       into :ID
C/end-exec

--Ted

Sponsored By WORKSRIGHT SOFTWARE

600 Billion That's how much a recent independent study estimated U.S. businesses spend on dirty data. How much of that 600 billion is spent by your company? Cleanse your dirty ZIP Codes and mailing addresses with our software and save big bucks. WorksRight Software, Inc. Phone: 601-856-8337 E-mail: software@worksright.com Web site: www.worksright.com