LANSA Identity System Variable

When working with Tables (or Files in LANSA terminology) you normally always define a primary key. It most cases the simplest and arguable the best case is to create an Identity column and assign that as your primary key. 

While I cannot find any documentation supporting it, it seems that the normal approach is use natural keys. In some cases however, there are no good candidates for a natural key or keys, so a surrogate key needs to be used. 

This is where an Identity concept comes into play.

LANSA unfortunately does not have a developer defined "Identity" column (Field in LANSA terms). It does however create a "Relative Record Number" (RRN) that is defined as an identity column. While you can use the RRN, it is generally not recommended as they can, and do get reorganized especially on the iSeries.

You can however emulate the concept of an Identity via a System Variable. 

Identity System Variables

LANSA has a special system variable evaluation program, M@SYSNUM that when invoked by a system variable, it inspects the name of that variable and uses that information to lock, lookup, increment, store and unlock a value in a data area. 

Creating one is pretty simple since we don't have to create the program associated with the variable. There are a some rules you have to follow though.

  1. The format of the name must be *AUTONUMNNXXXXXXXXX
  2. The name of the System Variable must begin with *AUTONUM
  3. Define the length of the variable at NN
  4. Define the name of the variable (hence the data area)

Identity variable

Above we have define the *AUTONUM10MYIDENTITY as an auto number that is 10 digits long and in the data area "MYIDENTITY". 

Notice the program is a 3GL program named M@SYSNUM.

Using the new variable is as simple as setting the Field that represents your key to the System Variable.

Identity use

You can also use the System Variable as the default value of a Field and not have to do the assignment as above.

Just remember that System Variables span Partitions. So if you have a lot of partitions, you may want to include the Partition identifier in the name.

Using a Guid as an Identity

Some folks like to use Guids or UUIDs as surrogate keys and identities.

There is quite a bit of debate on whether or not you should use Guids as primary keys specifically as they relate to indexes.

A Google search of "using guids as primary keys" shows quite a bit of discussion on the topic. 

While they are very easy to use, the main argument against them is that they can wreck havoc on your index and as you data gets larger, insert performance drops as the index has to get rebuilt with every record.

I myself fall into the "don't use Guids" camp. I've done it before and have experienced some of the down sides to them. Your experience may be different, just be aware of them if you do choose to use them.

If you do choose to use a Guid, LANSA already has a *GUID System Variable for you.

Reference Links

Here are some links to the LANSA documentation that may help.

System Variables for use in System Evaluation Programs

*AUTONUM and *AUTOALP System Variables (Data Areas)

Assign the System Variable as a Default Value

*GUID System Variable

Comments

You can follow this conversation by subscribing to the comment feed for this post.

The comments to this entry are closed.