Howto get auto generated values from rows created by SQL inserts in DB2

Normally you know exactly which values are in the columns of a newly inserted row in a database table. But when your table has an automatic generated identity column which is the primary key of the table you will want the automatic generated key value from your insert statement. But the normal insert command does not give back values. Fortunatly newer DB2 Version have the "Select from final Table" clause to solve the problem.

For example we have an address table created by the following commands:

CREATE TABLE PETTER/ADRESSEN (
    "KEY" INTEGER GENERATED ALWAYS AS IDENTITY (
    START WITH 1 INCREMENT BY 1
    NO MINVALUE NO MAXVALUE
    NO CYCLE NO ORDER
    CACHE 20 ),
    NAME1 CHAR(20) CCSID 273 NOT NULL ,
    NAME2 CHAR(20) CCSID 273 NOT NULL ) ;
 
LABEL ON COLUMN PETTER/ADRESSEN
( "KEY" TEXT IS 'Primary Key' ,
    NAME1 TEXT IS 'Name1' ,
    NAME2 TEXT IS 'Name2' ) ;

And here is the code to add a row and get the automatic created identity number back in one step.

   
   PreparedStatement ps = con
     .prepareStatement("Select * from final table (Insert into petter/adressen( name1,name2) values('Ralf','Petter'))");
   ResultSet rs = ps.executeQuery();
   rs.next();
   System.out.println(rs.getBigDecimal(1));

With the "Select from final table" clause you can retrieve values which are set by trigger prgograms or other advanced Db2 features too.