Wednesday, June 25, 2014

Resetting the identity column in a SQL table

reset identity column sql db2 for i

I was recently asked the following question:

I generate an auto incremental key in a table through create statement. But I am unable to reset the key back to 1 after a CLRPFM.
Any suggestions?

For those who are not familiar with the "auto incremental" identity column/field in a SQL table let me describe how to code it, and then how to reset it.

The identity column/field is a database function that does not exist in DDS files. It is a column/field that increments every time a row/record is inserted/written into the table/file, without needing to be incremented by the program or statement performing the insert/write.

Below is a very simple test SQL table I created, with just two fields:

  1. UNIQUE_COLUMN – the identity column/field.
  2. FIELD_1 – a character field.

This is the source code I created to make my simple test table:

01  CREATE TABLE MYLIB.SQL_TABLE_FOR_TEST (
02    UNIQUE_COLUMN FOR UNIQUE NUMERIC (10, 0)
03       GENERATED ALWAYS AS IDENTITY
04                (START WITH 1, INCREMENT BY 1, NO CYCLE) ,
05    FIELD_1 FOR FLD01 CHARACTER (10) NOT NULL
06  ) RCDFMT SQLTESTR ;

07  RENAME MYLIB.SQL_TABLE_FOR_TEST TO SYSTEM NAME SQLTEST ;

On line 1 I have given my table a long SQL name, SQL_TABLE_FOR_TEST, I give a shorter name that it will also be known as, SQLTEST, on line 7.

On lines 2-4 is where I define the identity column, UNIQUE_COLUMN. On line 2 I define its SQL name, UNIQUE_COLUMN, its "system" name, UNIQUE which is the name this field will be known by in a RPG program, and that it is a numeric field. Identity columns always have to be defined as numeric, although they can be defined with a size smaller than the 10,0 I defined it. Line 3 is the part of the statement that defines this column/field as an identity field. Line 4 is the minimum I used to define how the identity column/field works. I want the first row/record inserted/written to this table to have 1 in this field, I want to increment this value by 1 every time I insert/write a row/record, and when the identity column/field reaches 999,999,999 I do not want it to cycle back to 1. There are other values I could have used here, but I find them unnecessary in this example. I am giving a link at the bottom of this post how to define an identity column/field, all the other values I have not used will be defined there.

On line 5 I define my character column/field FIELD_1 or FLD01. I have used the NOT NULL as I do not want this field to contain null. If there is no value it will contain a blank.

The parenthesis ( ) ) on line 6 closes out its opposite on line 1. I have also stated that the record format name for this table will be SQLTESTR.

I created the table using the RUNSQLSTM command.

When I insert/write records to this table I do not put any value in the UNIQUE_COLUMN column/UNIQUE field.

I could insert rows into this table using an SQL statement or I could do it using RPG. This first example I am just going to use a SQL statement:

  INSERT INTO MYLIB.SQL_TABLE_FOR_TEST
         (FIELD_1)                     
         SELECT ORDNBR FROM ORDHDRP

Notice how I do not define the identity column/field in the fields to be inserted, just FIELD_1.

When I look at the data in the table I find that the first 3 rows/records contain the following data:

       UNIQUE_COLUMN   FIELD_1
000001             1   MK14680
000002             2   MK14690
000003             3   ML01501

Now I clear the file. I can either use the CLRPFM command, or as I am doing this in SQL I use the following:

  DELETE FROM MYLIB.SQL_TABLE_FOR_TEST

When I run the insert again and I look at the data I find the following:

       UNIQUE_COLUMN   FIELD_1
000001         8,076   MK14680
000002         8,077   MK14690
000003         8,078   ML01501

The identity column/field was not reset to 1 by the delete, the same happens when the CLRPFM is used.

This brings me to the question that was asked at the start of this post. How do I reset the identity column/field back to 1?

I use the following statement:

  ALTER TABLE MYLIB.SQL_TABLE_FOR_TEST
    ALTER COLUMN UNIQUE_COLUMN RESTART WITH 1

After running this statement if I clear the table and run the insert statement again the first identity column/field is 1.

I could put this in a CL program using the RUNSQL command, and I have used the "system" names instead of SQL:

  PGM                                                            
  RUNSQL  SQL('delete from mylib/sqltest') COMMIT(*NONE)

  RUNSQL  SQL('alter table mylib/sqltest alter column +
               unique restart with 1') COMMIT(*NONE)
  ENDPGM 

I wrote about the RUNSQL in an earlier post, Run SQL statements in your CL.

 

You can learn more about these from the IBM web site:

 

This article was written for IBM i 7.1.

10 comments:

  1. Sequences are backed by data areas when used as unique understanding columns so you can also change the data area directly I believe. Also, you can use the db2 PASE command to run SQL in CL programs more flexibly.

    ReplyDelete
    Replies
    1. That should read 'identifying' not 'understanding' doh!

      Delete
  2. I also have the same issue. I use alter statement before every run to reset the counter or dltf if its just a work file.

    ReplyDelete
  3. In 7.2 you can use the new "Truncate" SQL command with the "RESTART IDENTITY" clause. See http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafztruncate.htm?lang=en for details

    ReplyDelete
  4. DELETE followed by an ALTER command.
    Is it same using a CLRPFM followed by RGZPFM ??

    ReplyDelete
    Replies
    1. As it says the DELETE is like a CLRPFM.

      The ALTER and RGZPFM do different things.

      Delete
  5. We reset them all the time, but can anyone tell me WHY it needs to be reset? Other than basically saying "it gets out of whack". I have run into situations we have the auto generated ID as primary key but then a program will try and WRITE to it and it fails with a duplicate record. Something causes that ID to get out of whack and when the WRITE occurs and it tries to assign a new one, that ID apparently already exists and the WRITE fails with duplicate record attempt. So is there an sql system file you can look at to see which files may be "out of whack" in this respect? We'd like to see which ones need to be reset before it happens.

    ReplyDelete
  6. I have the same problem. I Think the system distinguish between sql insert and an rpg-write or cpyf. When I filled a table with records through an cpyf and later I made sql inserts, it startet again by 1

    ReplyDelete
  7. This is useful if you ever do a CPYF with MBROPT(*ADD) option with these types of files.

    ReplyDelete
  8. Only time you may not want to reset identity columns would be if they are used as a key in another table.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.