Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001
    Posts
    46

    Unanswered: IDENTITY column sequence problem

    Hi I'm new to DB2 and I've encountered a problem when using IDENTITY COLUMN.

    I'd really appreciate it if someone could shed some light on my problem.

    Here's the background to what I'm trying to achieve:
    1) I have a routine that auto-generates random numbers and writes them to a .txt file
    2) I want to create a DB2 table with 2 columns...
    2a) Column 1 should be an auto generated sequence number
    2b) Column 2 should be the .txt file loaded in as the primary key and duplicates dropped

    I created a table, here's the script output...

    connect to UNIQUENO user db2admin using
    Database Connection Information
    Database server = DB2/NT 7.2.3
    SQL authorization ID = DB2ADMIN
    Local database alias = UNIQUENO

    SET SCHEMA UNIQUENO
    DB20000I The SQL command completed successfully.

    create table UNIQUENOS ( SEQUENCE_NO INTEGER not null GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE), UNIQUE_NO VARCHAR(4) not null)
    DB20000I The SQL command completed successfully.

    ALTER TABLE UNIQUENO.UNIQUENOS ADD PRIMARY KEY (UNIQUE_NO) DATA CAPTURE NONE NOT VOLATILE
    DB20000I The SQL command completed successfully.

    The .txt file I want to load includes two duplicates to be dropped...

    "YTVG"
    "BKJ5"
    "9KZ3"
    "9KZ3"
    "YTVG"
    "NZ39"
    "7DZ8"
    "BKJ9"

    Here is the script output from the data load...

    connect to UNIQUENO user db2admin using
    Database Connection Information
    Database server = DB2/NT 7.2.3
    SQL authorization ID = DB2ADMIN
    Local database alias = UNIQUENO

    LOAD CLIENT FROM C:\UniqueNos\AutoGenerated\8MTest\UniqueNosTest.tx t OF DEL MODIFIED BY IDENTITYMISSING pagefreespace=0 totalfreespace=0 chardel"" coldel, decpt. METHOD P (1) MESSAGES db2load.msg INSERT INTO UNIQUENO.UNIQUENOS ( UNIQUE_NO ) COPY NO INDEXING MODE AUTOSELECT

    Number of rows read = 8
    Number of rows skipped = 0
    Number of rows loaded = 8
    Number of rows rejected = 0
    Number of rows deleted = 2
    Number of rows committed = 8

    Selecting the records from the table using the CLP window gives...

    db2 => select * from uniquenos

    SEQUENCE_NO UNIQUE_NO
    ----------- ---------
    1 YTVG
    2 BKJ5
    3 9KZ3
    6 NZ39
    7 7DZ8
    8 BKJ9

    6 record(s) selected.

    In Summary:
    1) I'm happy with the unique_no column... I wanted the data to be loaded in the order it was auto-generated and duplicates dropped
    2) I'm not happy with the sequence_no column... I don't want any break in sequence number, I want it to read 1, 2, 3, 4, 5, 6
    3) Lastly, If you imagine that I have a .txt file to load each day... I want the table to grow by this new data and the sequence_no to increment consistently

    Apologies for the long winded explanation - I hope someone can help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the easiest thing to do in this situation is either

    1. drop your duplicates before loading them

    or

    2. live with the gaps in identity numbers

    can you give any practical reasons why the gaps bother you? are these rows joined to some other table by the identity number?


    rudy

  3. #3
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    Hey,

    in my opinion these are the best suggestions, because from what Ive read in the manuals about Identity Columns, DB2 maintains a counter, and everytime an attemp to insert into the table a new value is generated for the identity field and the counter is incremented (or decremented according to what you specified) even if the transaction is rolled back. Based on this information youd better choose between the information bellow.

    Hope this cleared out your doubts,
    Fernando

    Originally posted by r937
    the easiest thing to do in this situation is either

    1. drop your duplicates before loading them

    or

    2. live with the gaps in identity numbers

    can you give any practical reasons why the gaps bother you? are these rows joined to some other table by the identity number?


    rudy

  4. #4
    Join Date
    Jun 2002
    Posts
    3
    Try to load data into one-column table then
    INSERT INTO table-with-identity
    SELECT DISTINCT * FROM first_one

Posting Permissions

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