Reset identity column counter

Database
Enthusiast

Reset identity column counter

Hi, one of our table column is defined as:

INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CYCLE)

The table is loaded full-refresh, before every load we first delete all rows.
Since we only delete rows but not drop/create the table, identity column uses next value since last value used in previous load.
We only load several hundred thousand rows per cycle, however the auto-generated identity column value reaches the 2billion limit within 4-5 full-refresh loads. This happens because the AMPs leave a range of numbers in between and they are unused.

Now, how can i reset the counter value after deleting all rows in a table. We don't want to drop/create the table, because that would mean first dropping the Soft Referential Integrity on the table. Today, we drop SoftRI and dt/ct tables after every 4 cycles to reset the counter, and we want to get rid of this approach.

Is there a built-in stored procedure or command/function to reset identity column counter without drop/create the table?

Thanks,
-srinivas yelamanchili

5 REPLIES
Senior Apprentice

Re: Reset identity column counter

Hi srinivas,
you can't reset an identity, just replace NO CYCLE with CYCLE, if you delete all rows before the next load you'll never get duplicates.

But i'm curious, when it's a staging table why do you use soft RI and identity at all?

Dieter
Enthusiast

Re: Reset identity column counter

For each table that has an identity column, there is a row in DBC.IdCols, and the column named AvailValue in that row has the next available sequence number. You can reset the sequence by updating that column. I am not aware of any standard procedure for doing that, and you would need special access rights, but I have done it experimentally, and it seems to work.
Enthusiast

Re: Reset identity column counter

Thanks Jim and dnoeth.

I simplified my case here to just one table. Infact, the database has several tables and each has an identity column
and some tables are children of others and hence the Soft RI.
Changing from 'No Cycle' to 'Cycle' works, but the modelers do not want to change the ddl

I could query the dbc.idcol table.
How can i join the TableId and DatabaseId with the actual names? Which table holds this mapping?

On TD12 Express edition, the update failed both as 'tdadmin' and 'tduser'
5315: The user does not have UPDATE access to DBC.IdCol.AvailValue.

Thanks,
-srinivas
Enthusiast

Re: Reset identity column counter

A note of caution on doing soft RI on staging tables. This will mean that if you try to validate referential integrity on staging tables by executing an SQL or so, you might end up getting wrong results (optimizer might skip the joins to parent tables).

Is there are reason for not going with Dieter's suggestion of using CYCLE ?

Updating DBC's tables would require explicit privileges to be granted , and most likely even DBC won't have permission on it, so you might have to turn on the diagnostic flags to override those privilege issue etc... (You really don't want to go down that road .. !)

Re: Reset identity column counter

Here's some SQL you can use to get the databasename and table associated with the ID Column Info
SELECT
d.DatabaseNameI,
t.TVMNameI,
i.*
FROM DBC.IdCol i
INNER JOIN DBC.TVM t
ON i.TableID = t.TVMID
INNER JOIN DBC.DBase d
ON i.DatabaseID = d.DatabaseID;

This one will give IDCol info for a specific databasename, tablename
SELECT *
FROM DBC.IDCol
WHERE (DatabaseID, TableID) =
(SELECT t.DatabaseID, t.TVMID
FROM DBC.TVM t
INNER JOIN DBC.dbase d
ON t.DatabaseID = d.DatabaseID
WHERE TVMNameI = 'YourDatabaseNameHere'
AND d.DatabasenameI = 'YourTableNameHere');