0

After truncating table, and inserting new values in table, auto-increment values are not set to started value 1. When inserting new values it's remember last index-ed value of auto-increment.

Colum in table named: ID

Index: PRIMARY,

Initial Value: 1

Cache size: 1

Increment: 1

[checked on IBM DB2 Control Center]

This query:

TRUNCATE TABLE ".$this->_schema.$table." DROP STORAGE IGNORE DELETE TRIGGERS IMMEDIATE

table is EMPTY.

After INSERT NEW VALUES example: INSERT INTO DB2INST1.db (val) VALUES ('abc') it's INSERT with LAST

ID | val

55 | abc

But it SHOULD BE:

ID | val

1  | abc
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Marin Sagovac
  • 3,932
  • 5
  • 23
  • 53

2 Answers2

1

I'm guessing here that your question is "how do you restart the IDENTITY sequence?" If that is the case, then you can reset it with the following SQL:

ALTER TABLE <table name> ALTER COLUMN <IDENTITY column> RESTART WITH 1

However, like @Ian said, what you are seeing is the expected behavior of a TRUNCATE.

bhamby
  • 15,112
  • 1
  • 45
  • 66
  • Yes, but I wan't detect with DESCRIBE TABLE for searching Increment column and select as . Mean, automatically search for name of IDENTITY COLUMN, but DESCRIBE TABLE not find for increment column name. – Marin Sagovac Aug 29 '12 at 18:25
  • Shortly, want find a name of via searching of name Increment column and than put this name of column into – Marin Sagovac Aug 29 '12 at 18:33
  • It's not my question, I don't accept the answer. You have to wait so many days before you can accept your own answer to your own question. – bhamby Aug 29 '12 at 19:06
0

First select in TABLE SCHEMA WHERE is name of IDENTITY column:

Query 1:

SELECT COLNAME FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'DB2INST1' AND TABNAME = 'DB' AND IDENTITY = 'Y'

Then, truncate table and return it's example: ID for altering index:

Query 2:

This ID puts on query for reset and altering index identity:

ALTER TABLE DB2INST1.DB ALTER COLUMN ID RESTART WITH 1

Change ID above returned from Query 1, which returns name of ID to Query 2.

SOLVED!

Marin Sagovac
  • 3,932
  • 5
  • 23
  • 53