× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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?


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.