Re: After an SQLCLI INSERT, is there a way of getting the relative record number of the record inserted?

Regarding SELECT and FETCH of the inserted row and suggesting "Need I say that this doesn't perform very well"... Was the test of performance over just one insert or over many inserts within the same job? Also there is the single-row implicit fetch with SELECT INTO, which would seem more appropriate to the task than a separate FETCH.?:

select rrn(a) into :hv :iv
from inserted_into
where keycolumn = :inserted_key
/* and keycol2 = :inserted_key2 */
with NC

An LF which omits the unsupported fields, is eligible for native I\O; i.e. omit all columns of a data type for which native I\O would be disallowed. As I recall, a read similarly updates the I\O feedback to give the RRN of the row just read. So for example, leave the logical file open and position by key to the just-inserted row versus using that key value to SELECT & FETCH the RRN. An logical file could be replaced by a query ODP, be it OPNQRYF or SQL. Created properly and the open maintained over various insert activity, being sure to effect FOR READ ONLY and parameterized for the key values [i.e. index positioning enabled], the native I\O via the query ODP should perform very well and a SQL parameterized query with new key values should also perform very well as a pseudo-closed SQL query ODP\cursor. Activating the query activity for just one insert would indeed be costly, but over many inserts in the same job the average can drop quickly after performance features like pseudo-closed cursors are established and utilized.

An after-insert trigger program can communicate the RRN of the inserted row, to the program that has just performed that insert.

A pending insert under isolation will have its row locked. The record lock information includes the RRN.

The journal records the RRN of the inserted row and the job that performed the write activity.

Regards, Chuck

Kevin Wright wrote:

It's a long story of generated RPG code on S/38 that could return
the RRN, then being emulated by generated C & SQL (including
emulating an RRN!) on OS/2, then Windows, then being ported back
to iSeries and using "real" RRNs, then for performance reasons,
where possible being re-coded using the _Rxxxx native I/O APIs.

Unfortunately we now support I/O to files that cannot be accessed
via native I/O (containing CLOBs & BLOBs etc), so that we are
left needing to find out what the RRN of the just INSERTed record
is.

The short answer is no, we need the RRN.

Dennis Lovelady wrote:

Kevin Wright wrote:

When an RPG WRITE or a C _RWrite is performed, the relative
record number of the record just inserted is available in
file i/o feedback areas accessible to the RPG and C
programmer. When an SQLCLI INSERT is performed we are not aware of a way to retrieve the relative record number of the
record just inserted, except by reading (SELECTing and
SQLFetching) it immediately. Need I say that this doesn't
perform very well?

Since SQL doesn't really have a concept of record number, I
doubt that you're going to find a better-performing answer.
But if I may be so bold, why do you need this? Can the RR#
be replaced by a sequence and work as well for your needs?