Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: problem with identity_val_local()

    hello! I am pretty new with db2, and I have a question considering the use of the function identity_val_local().
    Let's say I have a table called CUSTOMER, looking like this:

    ID NAME LAST_NAME
    ----------------------------

    where ID is the primary key which is auto generated with auto increment by 1. Now I want to create a query that should look something like this:

    insert into customer (NAME, LAST_NAME)
    values ('f_name', 'l_name')
    select identity_val_local()

    and as you see I would like the query to insert a new row in the table and as a result to return the last inserted value in the identity column (in this case ID). Now this is a very MSSQL 2005 like syntax (because I have used it), and according to me it should be all right. But it isn't!!! If anyone can help me with these problem I would be very grateful!
    Thanks in advance!
    Last edited by killerloop; 06-04-09 at 05:43.

  2. #2
    Join Date
    Jun 2009
    Posts
    2
    problem solved!!

    insert into customer (NAME, LAST_NAME)
    values ('aaa', 'sss');
    values identity_val_local()

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would avoid identity_val_local() because its semantics are not clear if you have multiple identity columns and triggers involved. Just select from the result table:
    Code:
    SELECT *
    FROM NEW TABLE ( INSERT INTO ... )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, you have to be careful with using SELECT IDENTITY_VAL_LOCAL FROM MYTABLE. That is an SQL statement with no WHERE clause. This means you would get every row in the table returned for that query and that could have just a little bit of an impact on your performance, lets say you would get back the identity value of 460002485, do you really that value returned to your app 460002485 times? I just ran across an app that was doing this 2 months ago.
    Dave

Posting Permissions

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