Re: Retrieving Last Inserted IDENTITY value

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Best idea I have is for you to go to www.iseries.ibm.com/support and 
put that function name in the search box at the top right. You'll get 
pointers to things in InfoCenter about it. Or go directly to 
InfoCenter under the database menu item. I've never used it - this is 
what I would need to do. The SQL reference has simple examples, along 
with related information. I think it came out first in V5R3.

It's easier to use the support site than to dig through InfoCenter.

HTH
Vern

At 12:51 AM 8/18/2006, you wrote:

> Hope this is not too basic, but it would definitely help me and others
> here...
>
> I have only used Identity fields for a couple of tools on the iSeries, but I
> used the AutoIncrement field a LOT when developing apps for Access.  On the
> iSeries apps I always wound up using the Max() technique to get the last
> inserted.
>
> That said, I am obviously interested in how IDENTITY_VAL_LOCAL is actually
> used.  Can you post a small example?
>
> Many Thanks!
> Joe
>
>
> ----- Original Message -----
> From: "Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>
> To: <midrange-l@xxxxxxxxxxxx>
> Sent: Thursday, August 17, 2006 11:29 AM
> Subject: Re: Retrieving Last Inserted IDENTITY value
>
>
> > Thanks.  That's perfect. RH
> > "Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
> > message
> > news:57FCBE9213FC584692CAAECA3F59426F50B2E8@xxxxxxxxxxxxxxxxxxxxxxxxxx
> >> Ryan,
> >>
> >> You're looking for the IDENTITY_VAL_LOCAL function.
> >>
> >> Note: it returns the last identity value assigned _in_the_job_ calling
> >> the function.
> >>
> >> So if you have multiple jobs running, and one calls IDENTITY_VAL_LOCAL,
> >> it gets the last identity assigned in that job.  It doesn't matter what
> >> identities were assigned in the other jobs.
> >>
> >> HTH,
> >>
> >>
> >> Charles Wilt
> >> --
> >> iSeries Systems Administrator / Developer
> >> Mitsubishi Electric Automotive America
> >> ph: 513-573-4343
> >> fax: 513-398-1121
> >>
> >>
> >> > -----Original Message-----
> >> > From: midrange-l-bounces@xxxxxxxxxxxx
> >> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On
> > Behalf Of Ryan Hunt
> >> > Sent: Thursday, August 17, 2006 2:09 PM
> >> > To: midrange-l@xxxxxxxxxxxx
> >> > Subject: Retrieving Last Inserted IDENTITY value
> >> >
> >> > Is there a DB2/400 function to retrieve the last IDENTITY
> >> > value inserted
> >> > into a table?   For MS SQL there is the SCOPE_IDENTITY()
> >> > function that will
> >> > show the last identity value inserted into any table within
> >> > the scope of the
> >> > current batch.
> >> >
> >> > Is there anything like that for AS400?  I am trying to avoid a select
> >> > MAX(ID) - which is resource intensive and may return
> >> > something inserted
> >> > right after me.
> >> >
> >> >
> >> >
> >> > --
> >> > This is the Midrange Systems Technical Discussion
> >> > (MIDRANGE-L) mailing list
> >> > To post a message email:
> > MIDRANGE-L@xxxxxxxxxxxx
> >> > To subscribe, unsubscribe, or change list options,
> >> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> >> > Before posting, please take a moment to review the archives
> >> > at http://archive.midrange.com/midrange-l.
> >> >
> >> >
> >>
> >> --
> >> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> >> To post a message email:
> > MIDRANGE-L@xxxxxxxxxxxx
> >> To subscribe, unsubscribe, or change list options,
> >> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> >> Before posting, please take a moment to review the archives
> >> at http://archive.midrange.com/midrange-l.
> >>
> >>
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.