Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    9

    Unanswered: DB2-Identity Value

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2007
    Posts
    9
    Quote Originally Posted by Marcus_A
    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..

  4. #4
    Join Date
    Oct 2007
    Posts
    9
    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.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can do this when you create the table ..search for identity-options in the CREATE TABLE stmt
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Oct 2007
    Posts
    9
    Quote Originally Posted by sathyaram_s
    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 ..?

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, RTFM. You can also check out the ALTER TABLE to change the cache or turn cache off.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •