Identity Value skip to 10th value

[VenkatSQL]

DB2 Experts

I am newbie to DB2. I have one table called Phy_data, it has one primary key 'phy_id' as identity column. For this table i have to import the values from a textfile.

Assume the text file has 10 records ok..?

First time identity column value starts automatically with 1 to ends with 10. It works fine.

If i try load another 10 records, identity value starts with (skip to) 21 instead of 11.

I don't know how to solve this.?

Can any one help me on this?

Thanks in advance

venkat

 

[MarcLodge]

venkat,
That shouldn't happen, as far as I am aware. I would report it to the DBAs who set up the table as that seems rather odd to me.
Marc

 

[blom0344]

I'd say the most likely cause is an import without commiting. The gap would then be induced by the rollback action and with the next committed import the gap shows up.

Did you try to load the textfile into a temp table and then use SQL to insert into the table with the identity column. What happens then?

Ties Blom

 

[sathyarams]

I guess you disconnect all applications from the database and reconnect before doing the next load.
By default, the CACHE value for identity column is 20. Hence 20 values are cached and when you disconnect, you loose the unused values.

From performance perspective, having cache value is desirable. If for a business reason you want continuous numbers, then set the cachevalue to 0.

But remember, if a row is inserted into the table and then the transaction rolls back, you will loose the value. $

HTH





For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[VenkatSQL]

Thanks to all.

The above problem is happening only one tables in my database.
Field struce of the the table
--------------------------
table name ==> PHY_Data
--------------------------
Structure
--------------------------------------------------------
PHY_ID - Integer Identity
USER_ID CHARACTER 10 (Default value)
TS TIMESTAMP 10 (Default value)
--------------------------------------------------------

While importing file, User_ID and TS should be default value i.e user id and Timestamp. So my text file look
like
--------------------------------------------------------
1
2
3
4
5
--------------------------------------------------------

and
my script file is

CONNECT TO UDB;

IMPORT FROM "/db2/PHY_Data.txt" OF DEL
MODIFIED BY IDENTITYIGNORE CHARDEL"" COLDEL, METHOD P (1)
MESSAGES "/db2/logs/PHY_Data.msg"
INSERT INTO DBM0.PHY_Data (PHY_ID);

CONNECT RESET;


Sorry for not giving the field struce earlier. In my table has only 3 fields one is Identity column and other 2 is default value.

Please help me....

P.S We can't change the table structure.

 

[sathyarams]

For anyone following this thread, the 'discussion' continues at

http://www.dbforums.com/showthread.php?t=1624457

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp