Hi, i was surprised to get an error message (5753 Numbering for Identity Column %VSTR is over its limit) when inserting a new row from a SQLA session (ODBC) or BTEQ session, while i could insert rows from a TD Administrator query. The identity column is defined as GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 NO CYCLE) with a SMALLINT format.
Whith an increment pace of 10 it works (in SQLA session) on a TD system with ... 10 AMPs ! Is there any relation between the increment pace and the number of AMPs in the configuration ? (i couldn't find in TD documentation). If there is one, what is specific to the Teradata Admin session that affords inserting rows with an increment of 1 ?
Hi Pierre, identity values are assigned by PEs or AMPs based on the type of query: - INSERT VALUES: by the session's PE - INSERT SELECT: by the source AMP - MLoad/FastLoad: by the AMP which receives the data in Acquisition phase
Each AMP/PE requests a batch of values from dbc.idcol: - Whenever you logoff and logon again you're probably assigned to a different PE requesting a new batch of values for INSERT VALUES. - When you submit an INSERT SELECT each AMP will request a batch of values
By default the range of values for a batch is 100,000, thus the first PE already reaches the maximum of 32767 and your second session hits the limit. And no, that default can't be set on a table level, only on system level (this is IMHO quite stupid).
You might use following query to check for the current max value(if you got appropriate rights on dbc):
SELECT dbase.DatabaseName, tvm.TVMName AS TableName, tvfields.FieldName AS ColumnName, tvfields.FieldType AS ColumnType, tvfields.Nullable, tvfields.CommentString, tvfields.TotalDigits(FORMAT 'Z9') AS DecimalTotalDigits, tvfields.ImpliedPoint(FORMAT 'Z9') AS DecimalFractionalDigits, tvfields.IdColType, idcol.AvailValue, idcol.StartValue, idcol.MinValue, idcol.MaxValue, idcol.Increment, idcol.Cyc FROM DBC.TVM JOIN DBC.tvfields ON tvm.tvmid = tvfields.tableid JOIN DBC.Dbase ON tvm.DatabaseId = dbase.DatabaseId JOIN dbc.idcol ON idcol.TableId = tvm.tvmid AND IdColType IS NOT NULL;
Never use SMALLINT for an identity, even INT might be bad: For a 2000 AMP system the first INS SEL will reserve 2,000*100,000 = 200,000,000. Those values are lost uring a restart, thus after 10 restart you might already run out of values.
Hi Dieter, Thank you for your clear and comprehensive comment on the topic ! i was lost on a bad track ... Some requests from two sessions achieved to illustrate the way it works: Identity column is not suitable for small tables with many "insert" sessions.