In my opinion identity columns are one of the cool things in Db2 for i. Basically an identity column is a column in a table that is updated by Db2 with the next sequential value. Depending upon how I code the identity column I can guarantee that they will never repeat, and provide a great unique key that can be used as a foreign key in another table. Most of time I do not have to bother with what the identity column's value as I know it will be unique and the only way I can reuse numbers is to reset the identity column.
I have to admit at times I am curious as to what the last value used in a table's identity column. I stumbled across a SQL function that will return to me the last used identity column for the table. But before I describe how to use this function I need a table and some data within it to show how to use the function.
My table is simple, it just contains two columns:
- Identity column
- Another column that will contain some data
01 CREATE TABLE QTEMP.TESTTABLE ( 02 ID_COLUMN BIGINT 03 GENERATED ALWAYS AS IDENTITY 04 NOT NULL, 05 SOMETHING_ELSE VARCHAR(20) 06 ) ; |
Lines 3 and 4: This is the simplest way I know to define an identity column. In this example I am not going to be generating lots of data so there is no concern that the values will reach the maximum allowed.
Let me insert some data into the table:
INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('ALLISON'),
('BRYAN'),
('CATHLEEN'),
('DAVID'),
('ELLEN'),
('FRANK') ;
|
All I need to do is to give the value I am putting into the SOMETHING_ELSE column, Db2 generates the values in the identity column for me. If I list the contents of the table I see that values have been assigned to the identity column.
ID_COLUMN SOMETHING_ELSE
--------- --------------
1 ALLISON
2 BRYAN
3 CATHLEEN
4 DAVID
5 ELLEN
6 FRANK
|
I guess if I wanted to know the last value used in the identity column I could use the following statement:
SELECT MAX(ID_COLUMN)
FROM QTEMP.TESTTABLE
MAX ( ID_COLUMN )
-----------------
6
|
If I delete the sixth row, Frank's row, I could not use this method to determine the last used identity value as the SQL statement would return:
MAX ( ID_COLUMN )
-----------------
5
|
Which is wrong, we all know the correct value is 6.
This is where the SQL function IDENTITY_VAL_LOCAL() gives us the answer I am looking for. When the function is called no parameters are passed the parentheses ( () ) contain no value.
I thought it best to show a number of scenarios to help illustrate how interpret the result from this function. Rather than show whole programs I am just going to show snippets of free format RPG.
Let me start with just adding another row to the table and checking what the last identity value was:
01 exec sql INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('GEORGINA') ;
02 exec sql SET :LastId = IDENTITY_VAL_LOCAL() ;
03 dsply ('1. LastId = ' + %char(LastId)) ;
|
Line 1: Just a standard SQL insert statement. I only insert a value to >tt>SOMETHING_ELSE as Db2 for i takes care of the identity value for me.
Line 2: I am using a SQL Set statement to move the value from the IDENTITY_VAL_LOCAL function into the RPG variable LastId.
Line 3: I am using the RPG display operation code to display the value in the variable LastId.
DSPLY 1. LastId = 7
ID_COLUMN SOMETHING_ELSE
--------- --------------
1 ALLISON
2 BRYAN
3 CATHLEEN
4 DAVID
5 ELLEN
6 FRANK
7 GEORGINA
|
The function returned the value 7 as that is the last identity value used with this table, and I can see seven rows in the table.
Now I want to delete the row I just inserted, and check that the identity value remains unchanged.
01 exec sql DELETE FROM QTEMP.TESTTABLE
WHERE SOMETHING_ELSE = 'GEORGINA' ;
02 exec sql SET :LastId = IDENTITY_VAL_LOCAL() ;
03 dsply ('2. LastId = ' + %char(LastId)) ;
|
As I expected the identity value remains unchanged, even though the seventh row was deleted.
DSPLY 2. LastId = 7
ID_COLUMN SOMETHING_ELSE
--------- --------------
1 ALLISON
2 BRYAN
3 CATHLEEN
4 DAVID
5 ELLEN
6 FRANK
|
Now I want to insert another row, and check if the identity value has incremented.
01 exec sql INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('HENRY') ;
02 exec sql SET :LastId = IDENTITY_VAL_LOCAL() ;
03 dsply ('3. LastId = ' + %char(LastId)) ;
|
The results, below, show that the identity value was incremented to eight. There will never be a identity value of seven again, unless the identity column is reset.
DSPLY 3. LastId = 8
ID_COLUMN SOMETHING_ELSE
--------- --------------
1 ALLISON
2 BRYAN
3 CATHLEEN
4 DAVID
5 ELLEN
6 FRANK
8 HENRY
|
What happens if I clear the table, does that reset the identity value?
01 exec sql CALL QSYS2.QCMDEXC('CLRPFM QTEMP/TESTTABLE') ;
02 exec sql INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('ISABELLE') ;
03 exec sql SET :LastId = IDENTITY_VAL_LOCAL() ;
04 dsply ('4. LastId = ' + %char(LastId)) ;
|
Line 1: Here I am using the SQL QCMDEXC procedure to execute a Clear physical file member command of my table.
Line 2: I insert a new row into the table, so that it contains some data.
When I look at the identity value it is nine, which is what I would expect as the identity column has not been reset.
DSPLY 4. LastId = 9
ID_COLUMN SOMETHING_ELSE
--------- --------------
9 ISABELLE
|
The code snippet below shows how to reset the identity column's value:
01 exec sql CALL QSYS2.QCMDEXC('CLRPFM QTEMP/TESTTABLE') ;
02 exec sql ALTER TABLE QTEMP.TESTTABLE
ALTER COLUMN ID_COLUMN RESTART WITH 1 ;
03 exec sql INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('JAMES') ;
04 exec sql SET :LastId = IDENTITY_VAL_LOCAL() ;
05 dsply ('5. LastId = ' + %char(LastId)) ;
|
Line 1: I want to clear the table before I reset the identity column.
Line 2: I use the ALTER TABLE and ALTER COLUMN to perform the reset. If you would like more information about this I wrote a post about resetting the identity column in an earlier post.
DSPLY 5. LastId = 1
ID_COLUMN SOMETHING_ELSE
--------- --------------
1 JAMES
|
As you xcan see the identity value was reset to one, therefore, the inserted record has that identity value.
You can learn more about the IDENTITY_VAL_LOCAL() function from the IBM website here.
This article was written for IBM i 7.4, and should work for some earlier releases too.
Real World Use: This is an important function when you use identity columns and pull PROD data into TEST data environments.
ReplyDeleteThere is a special register holding the counter. You can give it a base line on TEST so you wont get duplicates (set the initial value to 9 billion or something like that) or when you move prod data to TEST you check the value for the highest brought over and increase the register to that number +1 so you have no duplicates.
Its a little dance but I love the identity columns and RDB structure with system managed columns.
another excellent post
ReplyDeleteGood post, Simon ... Usually I use FINAL TABLE to get last ID... something like
ReplyDeleteSELECT ID_COLUMN FROM FINAL TABLE (
INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('ROBERTO') );
Excellent post.
ReplyDeleteI have been using identity columns for a while now and have a couple of observations.
1. When I use an identity column I usually set it as the primary key. That forces the system to have unique values in this field in case the next identity value gets out of sync by either copying the table or changing the next identity value.
2. If you are using copyf to copy from prod to test then the original value of the identity gets copied. If you use insert with select then the default is to generate new identity values. You will need to deal with this. I usually use the copyf command and then I have a command that will change the next identity value to the last identity value + 1.
3. Another way to retrieve the identity value on insert is using the combination select and insert in one statement. It is more powerful than the identity_val_local function in my opinion. It looks like this:
select my_identity, my_last_change_user
from final table ( insert into mytable
(field1, field2)
values (123, 'ABC')
);
On inserting the record into the table it can retrieve the identity column and any other system generated values such as the row change timestamp or the user id.
4. Database modernization techniques promote the use of identity columns as surrogate keys. For example if you have a customer master table you should use the identity column as the surrogate key instead of using the customer number. So your order header will have a customer_identity field instead of a customer_number. The theory is that if you ever need to change the customer number you can still do that. You could change the customer number for ACME Supplies from ACME001 to C00001 and not have a problem in your application.
5. Then you can also start using foreign key constraints and all the power that gives you.
Doug
We have found that using the final table method to get the last identity value is less efficient than using the UDF IDENTITY_VAL_LOCAL() UDF. I verified this by looking at the resulting C code under the covers...the FINAL TABLE requires more "expensive" steps.
DeleteI often use unnatural primary keys (Identity columns), but they are often misused. One way they are misused is that people think they take the place of a natural primary key. They do not. Our tables have both natural and unnatural PK's. Natural keys must be sacrosanct and should "never" be changed. Then there is the problem with some shops that only assign unnatural keys even as there are obvious natural keys. For example, if your only PK is an identity, you could have two customer rows with the same exact customer number. You cannot have 2 PK's? Technically that is true but we get around this by making the Identity the PK and the natural key a "PK" via the UNIQUE constraint.
ReplyDelete