Re: Embedded SQL - performance question

On 5/27/2011 7:20 PM, Barbara Morris wrote:

On 5/27/2011 2:47 PM, Bryce Martin wrote:

essentially... the record would need to be added within the amount of time
that it takes to do an "if %found" check...

It's not just the time taken to do the "if %found"; its the time between
when database determined there was no record to the time that your WRITE
request reaches database.

Say some other job was starting a WRITE at around the same time as your
CHAIN, but when when database does the check for the record for your
CHAIN, the WRITE operation in the other job hasn't reached database yet.
When your job finds that the record is not there, it will start its own
WRITE operation, but that other job had a head start, and would have got
the record in first.

This is a great point and it really brings up a larger discussion about application architecture. How you handle this situation truly depends on the application. If, for example, this is a dedicated work file that no other job has access to, clearly you can forego the constraint check. You can even allow a hard halt, since it's obviously some sort of program error (although your willingness to deal with hard halts is a matter of preference).

On the other end of the spectrum, if multiple jobs can reasonably add the same key, then you absolutely need monitor (or error extender), and you may wish to go the belt and suspenders approach of also doing a check prior to the write, although that seems a bit of overkill to me. You then, of course, need extra logic to determine what to do in the case of a duplicate, which will depend on the application.

Many programs, though, fall somewhere in the middle. Let's take, say, order entry, and let's further say you have a control structure (data area, file, whatever) that controls the next order number. It would almost always be a programming error for two different people to be adding the same order at the same time. Customer master maintenance is a little different, in that it would be a procedural error if two different users were entering the same customer. In either case, you should certainly monitor for the error, but does it make more sense to monitor the error at the point of write or globally? Even with order entry, it could be an online order entry program or a batch EDI processor; the error handling might be different. Error at the point of occurence gives you more control at the cost of extra coding, while global monitoring gives you complete coverage but less granularity.

Error handling is always a complex topic. I'm glad RPG gives us so many ways to handle errors; it allows for a robust architecture no matter the situation.

Joe