All Forums Database
ysrinu 44 posts Joined 04/07
07 Nov 2008
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 2147483647NO 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

dnoeth 4628 posts Joined 11/04
07 Nov 2008

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


Jim Chapman 449 posts Joined 09/04
07 Nov 2008

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.

ysrinu 44 posts Joined 04/07
08 Nov 2008

Thanks Jim and dnoeth.I simplified my case here to just one table. Infact, the database has several tables and each has an identity columnand 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 ddlI 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

joedsilva 505 posts Joined 07/05
09 Nov 2008

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 .. !)

nathan.hagemann 2 posts Joined 02/09
19 Feb 2009

Here's some SQL you can use to get the databasename and table associated with the ID Column InfoSELECT d.DatabaseNameI, t.TVMNameI, i.* FROM DBC.IdCol iINNER JOIN DBC.TVM tON i.TableID = t.TVMIDINNER JOIN DBC.DBase dON i.DatabaseID = d.DatabaseID;This one will give IDCol info for a specific databasename, tablenameSELECT * FROM DBC.IDColWHERE (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');

You must sign in to leave a comment.