Archives of the TeradataForum

Message Posted: Mon, 07 Apr 2014 @ 16:27:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I've just created a Table with an Identity Column, with a Maximum setting on the Column definition to 32000 (Column Datatype set to SMALLINT).

All was looking well until my colleague hit the following error:

7545 - Numbering for Identity Column %TVMID.%FLDID is over its limit.

When I looked on the Table, however, I had three rows in it!

So, I've simulated this situation with a slightly simplified Table:

     CREATE MULTISET TABLE REPORT_SUBSCRIPTION
          (
           SUBSCRIPTION_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
                (START WITH 1
                 INCREMENT BY 1
                 MINVALUE 1
                 MAXVALUE 5
                 NO CYCLE),
           CUSTOMER_TYPE_CD CHAR(4) CHARACTER SET UNICODE NOT CASESPECIFIC,
           EXTL_EMAIL_ADDR_TX SYSUDTLIB.EMAIL_ADDRESS_A01
     )
     UNIQUE PRIMARY INDEX REPORT_SUBSCRIPTION_UPI ( SUBSCRIPTION_ID );

I think it's unrelated, but this Table does have my first every Array Column, with the following definition :

     CREATE TYPE SYSUDTLIB.EMAIL_ADDRESS_A01  AS VARCHAR(256) CHARACTER SET LATIN ARRAY [20];

If I inspect the DBC Table for the newly created Identity Column I get this:

     sel * from DBC.IdCol where maxvalue = 5;

So, correct, as expected.

But, when I insert the first row :

     INSERT INTO REPORT_SUBSCRIPTION
     (
     -- SUBSCRIPTION_ID ,
     CUSTOMER_TYPE_CD,
     EXTL_EMAIL_ADDR_TX
     )
     VALUES (
     'RPAR'
     ,NEW EMAIL_ADDRESS_A01('[email protected]','[email protected]','')
     );

and then inspect the DBC Table again, I get this :

     sel * from DBC.IdCol where maxvalue = 5;

You'll notice that AvailValue has been set to 5, not 2 ! (even if I logoff and log back on again)

When I look at the data in the data Table itself, I see this :

     SEL * FROM REPORT_SUBSCRIPTION ;

Just to make the whole thing inconsistent - oh no, it just couldn't be consistent - when I now insert more rows, it's quite happy up until its defined maximum so, sure enough, another 5 tries and it correctly detects the limit of my range.

So, whilst I can't reproduce the original problem, this AvailValue does seem strange to me.

Any ideas ?

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

	<Prev	Next>		<<First	<Prev	Next>	Last>>