Having gotten an answer to a Java problem I had last week (thanks!), I'm now on to a new problem with DB2... The Java app I'm working on takes data from a web form and puts in in a DB2 file (database). The SQL string that is passed to the Java PreparedStatement object is:
insert into EVCRPTFL (
AUID, URLEX, URNEX, URNAME, URRCPT, URRUN, URRECT, URRECS, URRDYS, URRWKS, URRMHS, URRMTH, URRDAY, URRTHE, URRWHT, URRWDY, UREXPT, UROCRM, UROCRN, UREXPR, URSTTS, URACTV, URRPT, URD1YR, URD1YN, URD1MR, URD1MN, URD1DR, URD1DN, URD2YR, URD2YN, URD2MR, URD2MN, URD2DR, URD2DN, URRPAR, URLANG, URCTRY
)
values (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
Here's the table creation script:
CREATE TABLE EVCRPTFL (
URID int NOT NULL,
AUID decimal(11,0) NOT NULL,
URLEX timestamp,
URNEX timestamp,
URNAME varchar(40) NOT NULL,
URRCPT varchar(500) NOT NULL,
URRUN timestamp,
URRECT char(1) NOT NULL,
URRECS int NOT NULL,
URRDYS int NOT NULL,
URRWKS int NOT NULL,
URRMHS int NOT NULL,
URRMTH int NOT NULL,
URRDAY int NOT NULL,
URRTHE int NOT NULL,
URRWHT int NOT NULL,
URRWDY int NOT NULL,
UREXPT int NOT NULL,
UROCRM int NOT NULL,
UROCRN int NOT NULL,
UREXPR timestamp,
URSTTS char(1) NOT NULL,
URACTV timestamp,
URRPT int NOT NULL,
URD1YR int,
URD1YN int,
URD1MR int,
URD1MN int,
URD1DR int,
URD1DN int,
URD2YR int,
URD2YN int,
URD2MR int,
URD2MN int,
URD2DR int,
URD2DN int,
URRPAR varchar(1000),
URLANG char(2),
URCTRY char(2)
);
CREATE UNIQUE INDEX EVCRPTFL ON EVCRPTFL(URID);
As you can see, it's passing in all the values in the SQL string except the first one, URID. After a record is inserted, I can see it in the database with an ID number, which implies to me that the database is adding the URID automatically (that is, the ID is not being generated in the Java code and then passed to the database). In my previous experience with Oracle and PHP, I used Oracle Sequences to make autonumbered fields, and I'm aware that there are broadly similar features in MySQL, Postgres, and probably other database systems, but I haven't been able to find how autonumbering works in DB2.
My coworker who is more familiar with DB2 is also puzzled; he says there are no Triggers set up on the table according to what he looked at, and that generally in the RPG code here it sounds like ID numbers are generated in the code and then passed to the database, rather than being generated by the database itself.
So my question is: Does the Unique Index on URID also autogenerate ID numbers, or is there something else I need to be looking for either in the database or in the Java code?
And my followup question is: I will eventually need to be using the URID number for insertion into another table storing related data. In Oracle, this was done with "insert into returning into " and then getting the data back out of the parametrized id_variable in the code. Is there a similar feature in DB2? I found documentation on an IDENTITY_VAL_LOCAL function, but it doesn't look like I can always guarantee that it will return the correct ID, for instance if another insert happens from the web form between the first insert and the invokation of IDENTITY_VAL_LOCAL...
I can provide code samples or other database info if needed. Thanks!
