Index question.

This is a basic one but I am not sure why it's working this way. Basically I have a table with an autonumber ID and I want to insert a row and then do a max(id) to get the id of what I just inserted. The id is the primary key so the DDL for it is

ALTER TABLE TBLDETAILTRANS
ADD CONSTRAINT "DETPRIM1" PRIMARY KEY
("DETAILTRANS_ID");

I ran an explain on the select max(id) and it is over 463,000 timerons .......a min only 50.

I thought the index could be read both ways. Do I have to set up a reverse index or can this contstaint be set up differently?

what does you query look like?

Have you tried using in your query?

The query is SELECT MAX(ID) FROM tblDetail

We are using Enterprise Java Beans to do the updating and on the return we need to send back the id with the data that has been updated. The select is taking about a minute to perform it for each insert. I would think DB2 would be able to go to the last record of the index just as quick as the first record??

Sorry, I was thinking of MS SQL Server. In an insert statement you can return @@identity which is a built-in SQL Server variable that returns the id of the last inserted row when the primary key is an identity field. I'm not sure if there is anything like that for DB2.

Good luck.

I think what you are looking for is
select identity_val_local

See this article for more:
http://www.idug.org/idug/member/journal/may01/articl12.cfm

and try this search also:
http://www.ibm.com/Search?v=11&q=identity_val_local&lang=en&cc=us

fv

select identity_val_local seemed to do it. I put that in the EJB and it returned. Thanks.

Anytime. Seems like in V 8 they added some new syntax that allows you to get it in the same SQL that does the insert, rather than a second query immediately after the insert. Don't have that reference handy, but it should be on IBM Db2 developerworks.

fv