Re: Multiple Inserts - does block writing occur?

On 11 Apr 2013 14:49, Dan wrote:

Are you implying the block restriction is a function of RPG native
I/O (and, ergo, SQL can block even with unique keys)?

On Thu, Apr 11, 2013 at 4:42 PM, Charles Wilt wrote:

If the table has a unique key, then RPG won't do block i/o as the
DB needs the records to check for uniqueness.

The following is the way I have long understood the situation; I am not sure how accurate it is:

The only way for the LIC database to evaluate for uniqueness is for the LIC DB to receive [all of the key portions of] the physical row data. If the row data remained in the buffer, then there would be no reasonable way to enforce that the buffered row data is unique, and that would mean that a program would not be informed of the condition immediately upon that request to write the data. If the OS DB had to ask the LIC DB to evaluate each row for uniqueness upon entry to the buffer, then there would be little reason to not just send all of the row data to be evaluated for uniqueness *and* inserted if unique; and that is what is done. The calling down to LIC is the biggest expense, so might just as well pass the entire row image rather than trying to pick out and send down only what of the row image composes all of the unique keys [which could be across multiple access paths besides the file into which the data is being inserted, if even that file is keyed; and some of those AccPth may even be temporary, for which little tracking is done by the OS DB]. Besides, the LIC DB /owns/ the access paths [and data], so it is /only/ correct that the LIC DB should do the work directly, and not depend on the OS DB to correctly try to ask of the LIC DB to do what work needs to be done.

If not forced down to a one-row buffer, that implies buffered rows would allow for multiple non-unique rows to buffer, such that when a request to push the full buffer to the LIC DB eventually transpires, the LIC would process from the buffer until the first non-unique, signal an error, and leave a partial buffer. Having only a partial buffer written due to a [key] mapping error may be deemed a recoverable condition, but the uniqueness of a row is deemed imperative to be known immediately, in order to maintain database integrity; pretty much a tenet of a RDBMS.