reset identity column after table move

Database
Enthusiast

reset identity column after table move

I'm migrating multiple tables to another machine (2650 to 2690), and moving the tables aspect is very simple.

However, the seeded identity column needs to be modified to reflect the max value +1 of the data I'm moving.

I realize that I can get the data over there, but not wanting to rename table, recreate table with new starting identity column value, insert into new table from hold table, then drop old table.

Isn't the identity column next value stored in the data dictionary?  If that value is stored in the data dictionary, why can't an alter statement be executed to modify it?

How are others migrating data from dev to test to production (and vice versa)?  If I want to restore production data into a 'break-fix' area to resolve a problem, I don't want to have to restore the entire system... I just want one database.  But without the backup/restore of the data dictionary, I have to manually "reset" the identity column by recreating the table?

Any work arounds, macros, procedures, etc. that others have written to help automate this process would be greatly appreciated!

Is this situation being addressed in future releases?

Thanks!

 -Rick

7 REPLIES
Enthusiast

Re: reset identity column after table move

If you are using the Archive Utility (restore or copy commands) to move the tables, the identity column next value should be properly set on the destination system.  However, there was a problem with the restore command (reported as DR 156650) that was fixed in the 13.10.03.05 patch release.  Apparently this problem did not affect the copy command, which therefore can be used as a workaround.

Next values for identity columns are maintained in the DBC.IdCol table.

Enthusiast

Re: reset identity column after table move

After testing the copy command, it appears that Teradata is not using the correct nextval.

1. created a table:

CREATE MULTISET TABLE test.ident_test ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

ident_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY

(START WITH 1

INCREMENT BY 1

MINVALUE -999999999999999

MAXVALUE 999999999999999

NO CYCLE),

column2 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

column3 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)

UNIQUE PRIMARY INDEX ( ident_ID );

 

2. insert 5 rows:

 

4 pqrst fourth test row

5 uvwxy fifth test row

2 fghij second test row

1 abcde first test row

3 klmno thrid test row

3. archmain copy to another db

4.

 

Enthusiast

Re: reset identity column after table move

step 4... insert a row into 'newly copied' table:

5 uvwxy fifth test row

4 pqrst fourth test row

3 klmno thrid test row

1 abcde first test row

2 fghij second test row

100001 test insert in test db

Instead of giving me a 6 in the identity column, it put the 100001....

Not what I was expecting

Enthusiast

Re: reset identity column after table move

Blocks of identity values are assigned to amps in blocks.  It sounds like your system was set up to assign in blocks of 100000.  I believe this is a configurable parameter that can be changed in dbscontrol.  The identity column value will always be unique and sequential by will contain gaps.  This is fully expected.  Hope this helps...

Joe

Enthusiast

Re: reset identity column after table move

That does help, thank you.

It does bring up a question..... if I reset the dbscontrol value to 1, is that a bad thing? I'm coming from an Oracle & DB2 background, so I'm trying to get my hands around this on Teradata.

Enthusiast

Re: reset identity column after table move

I think the minimum is 10000 but not sure...maybe someone else knows?

Teradata Employee

Re: reset identity column after table move

It is possible to set DBSControl to 1 but then all inserts and loads will be single threaded though the id allocator and will severely affect the scalability and performance of inserts or loads of any size.