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.

4 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

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.