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

S.V

DB2 caches the next available number in memory for performance reasons. The default is 20. You can alter the table to set the cache to zero (or no cache). See the DB2 SQL Reference Vol 2, ALTER TABLE syntax.

Every time the database is activated (the first application connecting activates the database unless the db is explicity activated with activate command). When no connections exist, it is deactivated, and DB2 looses the cached values that were stored in memory.

Thanks for your Quick reply..

Let me check..

Hi

I can't able to rest cache to 0. It says you must have min value 2..
---
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.

There is no problem in generating identity column value for other tables

Please help me....

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

You can do this when you create the table ..search for identity-options in the CREATE TABLE stmt

Sathyaram

I can't understand.. Can you please explain ..?

Sorry, I can't resist: Sathyaram tells you to RTFM.

Specifically, have a look at the CREATE TABLE statement in the SQL reference: http://publib.boulder.ibm.com/infoce...c/r0000927.htm

Yes, RTFM. You can also check out the ALTER TABLE to change the cache or turn cache off.