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?
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.
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.
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 .. !)
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');