Resetting the identity column in a SQL table

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:

• CREATE TABLE statement
• ALTER TABLE statement

 

This article was written for IBM i 7.1.