Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2009
    Posts
    6

    Unanswered: retrieving identity column for current insert.

    Do you know of a slick way to do this:

    CREATE TABLE A
    ID INT IDENTITY,
    ID_MIRROR CHAR(20)

    INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || CHAR(A.ID))


    I basically want what ever is in the ID column to be mirrored into the
    ID_MIRROR column as a character, but prefixed with 'PRE'.

    My actual insert is more complex, but if the above works, then I can
    get my example to work.

    I need to do this in one insert rather than a INSERT and UPDATE
    because one of the other columns has a trigger and I do not want the
    trigger to fire twice.

    I tried this:
    insert into A (id_mirror)
    VALUES (CONCAT('PRE', CHAR(identity_val_local())))

    But it fails because identity_val_local() is null. I suspect it is
    returning the previous insert, and because there is no
    previous insert it is returning null.

    I also tried, this:
    insert into A (id_mirror)
    VALUES (CONCAT('PRE', CHAR(scope_identity())))


    But scope_identity() is apparently not a db2 function. Must be for some
    other database.

  2. #2
    Join Date
    Oct 2009
    Posts
    24
    create view a1 as select id, 'PRE'||char(id) as id_mirror from a

  3. #3
    Join Date
    Dec 2009
    Posts
    6

    creating view a good idea, but will not work for my case

    The view will not work for my case because there is already data in the id_mirror column that does not mirror the id column.

    All data going forward will mirror the id column.

    Creating solution tho. I am trying to think of a way to make it work like leaving id_mirror null and then use COLLEASE to have the view only fill it in if it is null.

    I would rather not rename this table and then create a new view tho so I am hoping that someone will respond with the way to get the current identity column value.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please write exactly what you did and what result(including error messages) you got.

    Please don't let me a guess.

    I got error with this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE A
    ID INT IDENTITY,
    ID_MIRROR CHAR(20);
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "CREATE TABLE A ID INT IDENTITY, I" was found 
    following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
    "<create_variable>".  SQLSTATE=42601

  5. #5
    Join Date
    Dec 2009
    Posts
    6

    exact create statement.

    Here is the exact create statement (thanks for calling me out on not providing it in the first place):

    CREATE TABLE A
    ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE
    CACHE 20
    NO ORDER )
    , ID_MIRROR CHAR(20))

    All three of the INSERT statements fail. I would like help finding an INSERT statement that works.

    Thank you

  6. #6
    Join Date
    Dec 2009
    Posts
    6
    Here is a concise repost of the question:

    I have created a table like this:

    CREATE TABLE A
    ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE
    CACHE 20
    NO ORDER )
    , ID_MIRROR CHAR(20))

    I would like to do an insert such ID_MIRROR would be what is in ID, but prefixed with 'PRE'.

    I have unsuccessfully tried the following:
    INSERT INTO A (ID_MIRROR)
    VALUES ( 'PRE' || CHAR(A.ID))

    Error 12/4/2009 6:43:08 AM 0:00:00.296 DB2 Database Error: ERROR [42703] [IBM][DB2/AIX64] SQL0206N "A.ID" is not valid in the context where it is used. SQLSTATE=42703
    1 0


    ------------------------------------------------
    insert into A (id_mirror)
    VALUES (CONCAT('PRE', CHAR(identity_val_local())))

    ID_MIRROR is NULL, subsequent inserts are previous value of ID.

    -----------------------------------
    insert into A (id_mirror)
    VALUES (CONCAT('PRE', CHAR(scope_identity())))

    Error 12/4/2009 6:11:11 AM 0:00:00.234 DB2 Database Error: ERROR [42884] [IBM][DB2/AIX64] SQL0440N No authorized routine named "SCOPE_IDENTITY" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
    1 0

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example tested on DB2 9.7 for Windows:
    Code:
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE A;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE A
    ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE
    CACHE 20
    NO ORDER )
    , ID_MIRROR CHAR(20));
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE1                
    
      1 record(s) selected.

  8. #8
    Join Date
    Dec 2009
    Posts
    6

    Thanks

    That looks like exactly what I need.
    What reference are you looking at to find the functions that are available?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    That looks like exactly what I need.
    What reference are you looking at to find the functions that are available?
    I saw "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1".

    Here is more example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A(ID) VALUES DEFAULT;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE1                
                       2 -                   
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SET INTEGRITY FOR A OFF;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE A
    ALTER COLUMN ID_MIRROR SET GENERATED ALWAYS AS ('PRE' || id);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SET INTEGRITY FOR A IMMEDIATE CHECKED NOT INCREMENTAL FORCE GENERATED FULL ACCESS;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE1                
                       2 PRE2                
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A(id) VALUES DEFAULT;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE1                
                       2 PRE2                
                       3 PRE3                
    
      3 record(s) selected.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ------------------------------------------------
    insert into A (id_mirror)
    VALUES (CONCAT('PRE', CHAR(identity_val_local())))

    ID_MIRROR is NULL, subsequent inserts are previous value of ID.
    You are right!

    The value of identity_val_local() remains until it is replaced by the next assigned by INSERT.
    In my previous test, I did CREATE/INSERT table A before the sample I showed.
    So, the value of identity_val_local() was remained and it was accidentally same as the value of ID.

    I want to recommend to use "GENERATED ALWAYS".

    Here is an example which identity_val_local() is not equal to ID.
    Even if I did DROP/CREATE table, identity_val_local() was not reset.
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.0
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE A;
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0204N  "DB2ADMIN.A" is an undefined name.  SQLSTATE=42704
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE A
    ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE
    CACHE 20
    NO ORDER )
    , ID_MIRROR CHAR(20));
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 -                   
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 -                   
                       2 PRE1                
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE A;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE A
    ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE
    CACHE 20
    NO ORDER )
    , ID_MIRROR CHAR(20));
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE2                
    
      1 record(s) selected.
    This is an example to set GENERATED ALWAYS AS ('PRE' || id) and reset the value of ID_MIRROR.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    SELECT * FROM A
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE2                
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SET INTEGRITY FOR A OFF;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE A
    ALTER COLUMN ID_MIRROR SET GENERATED ALWAYS AS ('PRE' || id);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SET INTEGRITY FOR A IMMEDIATE CHECKED NOT INCREMENTAL FORCE GENERATED FULL ACCESS;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE1                
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO A(id) VALUES DEFAULT;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM A;
    ------------------------------------------------------------------------------
    
    ID                   ID_MIRROR           
    -------------------- --------------------
                       1 PRE1                
                       2 PRE2                
    
      2 record(s) selected.
    Last edited by tonkuma; 12-04-09 at 10:40.

  11. #11
    Join Date
    Dec 2009
    Posts
    6

    GENERATED ALWAYS vs GENERATED BY DEFAULT

    I only want the ID_MIRROR to be updated for new inserts.

    Do I use BY DEFAULT rather than ALWAYS?

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I only want the ID_MIRROR to be updated for new inserts.
    You can use IMMEDIATE UNCHECKED, like this
    SET INTEGRITY FOR A GENERATED COLUMN IMMEDIATE UNCHECKED;

    But, there are long warnings in "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 2".
    v Warning about the use of the IMMEDIATE UNCHECKED clause:
    - This clause is intended to be used by utility programs, and its use by
    application programs is not recommended. If there is data in the table that
    does not meet the integrity specifications that were defined for the table, and
    the IMMEDIATE UNCHECKED option is used, incorrect query results might
    be returned.
    .....
    .....

    Do I use BY DEFAULT rather than ALWAYS?
    I don't think so.
    You can try it yourself.

  13. #13
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    why don't you make ID_MIRROR col generated? in this case you don't have to worry abt inserting into it. Something like this:

    ID_MIRROR CHAR(20) generated always as (‘PRE’ || char(ID))
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Tags for this Thread

Posting Permissions

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