Re: Buffering

-- Funnily enough I've always believed that to be the case - that RPG won't
-- buffer output to a file with unique keys, because the DB had to
-- enforce uniqueness, but not so long ago I had cause to doubt myself and
-- wonder if this was true or just an assumption on my part over the years.

I believe you may find that both are true -- DB will enforce or DB will not
enforce uniqueness from a point of view of allowing the RPG program to
"think" a record has been written (moved successfully to the buffer) but
still fail when the buffer is full. Check out the help text for OVRDBF and
in particular the Number of records element of the SEQONLY keyword:
Granted most never code this element...

"If a program performs sequential-only output processing and does not
handle output errors (such as duplicate keys and conversion mapping errors)
that may occur when the records in the buffer are written to the database,
records in the buffer after the first record in error are not written."

Also the second bullet at
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/dbp/rbafoicons.htm
which
discusses buffering and journaling.

On Thu, Nov 21, 2019 at 1:59 PM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Jon - thanks for the reminder re Feod(N)

Charles - thanks for the info.
Funnily enough I've always believed that to be the case - that RPG won't
buffer output to a file with unique keys, because the DB had to
enforce uniqueness, but not so long ago I had cause to doubt myself and
wonder if this was true or just an assumption on my part over the years.

I think when I read the literature here:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/dbp/rbafoocons.htm
-
I wasn't quite certain if that was the case or not, so it's good to hear
you re-affirm what I've always believed.

I don't think there is any commitment control on in use with this
particular issue, I'll check tomorrow.

OK, well I think I can assume based on your responses and the journal
entries that I can eliminate what looked like the most likely culprit.

I am rather puzzled though as to what might cause this.
I'm seeing the SQL return SqlCode 100 - no rows selected (and therefore no
record inserted into another table)
I expect rows to be returned.

Re-running the transaction (not immediately, because currently there are
some manual actions to try to reprocess the transaction, but fairly soon
afterwards) works - the next run does select rows for the same transaction
that just failed.

There are roughly a dozen JOINS in the SQL and maybe 7 of them are LEFT
JOINS (so their rows are fine to not exist) and looking that the data I
don't see any obvious problems with the other tables, but I'll keep working
through it.

Thanks as always for your time and info - much appreciated
Craig

On Thu, 21 Nov 2019 at 18:25, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

In answer to your question, yes RPG's buffering is prior to the record
being passed to the Db and thus prior to journal entries being created...

Also note that RPG won't buffer records output to a file with unique keys
(as the Db has to have them to enforce the uniqueness)

Is commitment control being used by chance?

Charles

On Thu, Nov 21, 2019 at 11:00 AM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Hi All,

I'm trying to track down an infrequent but troublesome bug.

My best guess at the moment is that it is caused by an RPG program
buffering output so that not all records have hit the disk when an
asynchronous process wants to read them and run some SQL joins.

However, looking at the journal receivers, according to the sequence
numbers - the records are being written in the correct order.

Apologies in advance if this is a stupid question but my assumption is

that

if an RPGLE program is buffering output, there won't be journal entries

for

writes to that file until the data is actually written to disk.
But I'm not certain of this...

Maybe an example would be clearer:

Program A - Batch Server Process (stays resident)
Writes Order Line.
Writes Order Header.
Sends Data Queue Entry to wake up Program B running in another Batch

Server

Process

Program B - another Batch Server Process (stays resident)
Requires the Order Header and the Order Line records in order for the

SQL

join to select some rows.

Looking in the journals, I can see, in sequence number order:
Program A Write to Order Line
Program A Write to Order Header.

Is this journal sequence conclusive proof that the Order Line was

present

on disk before the Order Header was?

If Program A was buffering output, such that the Order Line records,
written chronologically first, sat in Program A's buffer until some

time

after the Order Header records hit the disk - would the journal

sequence

numbers indicate that?

Obviously I can use FEOD or Block(*No) in Program A for file Order Line

but

there is no point in doing that if the journal sequence numbers are

already

telling me that the records from the Order Line got there before the

Order

Header.

thanks kindly,
Craig Richards
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com