smart or criminal?

[Truusvlugindewind]

I remember finding this in a program (cobol/db2/mainframe):

exec sql
     INSERT .....
end-sql
if SQLCODE equal -803 *> duplicate key *< 
   exec sql 
        UPDATE .... 
   end-exec
else 
   if SQLCODE not equal zero
      perform db2-error
   end-if
end-if

I get angry seeing such code. In my book it is only valid to test for SQLCODE 0 or 100. The rest al always an error. Treating a DB/2 error code as a valid situation is not done. In my book you should code:

exec sql
     SELECT COUNT ....
end-exec
if w-count greater than zero
   exec sql
        UPDATE ...
   end-exec
else
   exec sql
        INSERT ...
   end-exec
end-if
if SQLCODE not equal zero
   perform DB2-error
end-if

The other programmer argued that my code involves extra database access, so it is less efficient.... Can't argue with that.
Collegues around the world: do I fight windmills or do you agree?

 

[TonHu]

I'd go for the second method, as generating an error usually has bigger impact than performing a second query.
It looks like your' adding/updating a standard table, so performance wouldn't matter much, and if it's processing, usually you'r updating some counters/values, so a different sql query would probably be required anyway...

HTH
TonHu

 

[joepiemeloenie]

checking if something exists costs also something but isn't this more an issue for an sql forum or so?

 

[Crox]

Hi lovely Truus, how are you?

Evaluating things and their efficiency depends on how many times you expect things to happen. So to create the most efficient evaluation, you first test what happens most and after that what happens second most, etc.

With inserts the same. I expect to be in the winning mode. (Do I feel lucky today? ) For example with inserting a timestamp in combination with a key in certain environments where people need a unique key. If you are in the environment I guess you perhaps are (AAB?), you can have even 8 inserts at exactly the same time. Most of the time, the timestamp and some other key (customer, accountnumber, etc.) will be unique enough so you are not going to do unnecessary counts to prevent something that almost never will happen.

Regards,

Crox

 

[Truusvlugindewind]

Hi Crox. I'm fine. Yes, still AAB...

It's all about priciples. As I stated in an earlier posting, good quality programs are portable. You can pick my code and implement on a different computer with a different database. As long as you suppport ANSI cobol + ANSI SQL. In your example (a timestamp as part of the primary key) a returncode other than zero is an error. period.
I think testing for a SQLCODE (and continue processing) other than 0 or 100 in not acceptable.

 

[Crox]

I am sure it happens all the time! It depends on the expectations. It is about efficiency, not about portability. For example: the AAB uses COOL:GEN which generates sql and COBOL. The generated DB2 access is such a poor quality that it is not performing good enough. Stay tuned!

 

[wahlers]

I might be wrong but I am fairly sure of the following...
SQLCODE is not standard!
You should use SQLSTATE to be standard!

Something else...
1. To be stable I use object technology...that is I use SQLSTATE (which is a standard for relational databases only) to generate an independent (object) exception message (the exception message system is a plug-in component (component technology)).
2. I define (code) seperate layers of responsibility (of which the persistent layer is one of them).

For a more detailed description see link:

http://www.programmersheaven.com/2/Art_ObjBasedSystems

Regards, Wim Ahlers.

 

[razalas]

Truus,

I don't work with SQL, so please forgive me if I completely miss the situation here, but...

it seems to me that the INSERT/UPDATE scenario you decry above preserves the data integrity more correctly than the COUNT/INSERT/UPDATE solution you would prefer and is therefore the more appropriate solution.

In other words, do we have to worry about some other process INSERT'ing the same record(s) between the COUNT and INSERT operations in the solution you suggest?

If not, then I definitely think your solution is more "readable" and therefore preferred (except if it can be shown to be bottleneck and therefore needs to be reworked for the sake of efficiency).

"Code what you mean,
and mean what you code!
But by all means post your code!"

Razalas

 

[MarcLodge]

Sorry Truus, but I've got to disagree. I've never coded it the way you champion, and have always coded it the way you dislike. Admittedly it depends on the application, because it might be that an insert must not exist, in which case the program should error.

Coding a COUNT call first is more inefficient, necessitates setting up a count variable in WS, and allows the possibility of someone sneaking in between the count call and the insert, as previously stated. I understand what you are saying about the only 'good' SQLCODEs should be 100 and 0, but there can be exceptions to this rule.

I think maybe you are tilting at windmills.

Marc

 

[Dimandja]

I think testing for a SQLCODE (and continue processing) other than 0 or 100 in not acceptable.

I must disagree. Each application should be evaluated in its own merit. For example, I once coded an application that did nothing but process system errors (hundreds of them). Each individual error was important and meaningful. To blindly go only for 0 or 100, regardless of the requirements, does not make any sense to me.

In this situation, if there are no other requirements specified, I'd go for the Insert. The other way is simply too convoluted for the minimal needs.

Dimandja

 

[TonyJollans]

Hi Truus,

'fraid I must disagree too. I don't think there is a conceptual difference between Error codes and Information codes. The practical difference is that Information codes are returned following some kind of action being taken; Error codes are returned when NO action has been taken.

When I return to my house I try the door - if it is locked (I get an error trying to open it) I get out my key and unlock it and then try again to open it. I don't check out, in some roundabout way, whether my wife has got home before me; I just try the door.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[MarcLodge]

Tony,

Is it considered lazy to ring the doorbell?

Marc

 

[TonyJollans]

Hi Marc,

Well, now, this is partly a performance issue

If my wife is at home she may or may not hear the bell and all I can do is wait for a suitable length of time and then, in the absence of a response, try the door anyway.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Truusvlugindewind]

Thanks for all your feedback! You know: I did some conversion jobs in the past. Porting (COBOL) applications from one OS to another. A lot of work seemed useless. When the original programmer had paid some attention to standards the program could be ported 1:1.
So I told myself: MY programs need no maintenance when the client wants to run the system on a unix/oracle instead of os2/db2. And you can develop & test on a windows workstation.

 

[Glenn9999]

I personally like the first example the original poster posted (checking for -803). I've seen many cases where converting code from the second case to the first case where things were speeded up considerably. I like to use the comparison of memory access to disk access usually to drive home the point.

About a year ago, I made the comparison based on current common speeds. If we compare average time of memory (70ns) to the average time of a hard disk (20ms) to access one block of information. It's been a while, so the numbers aren't exact. If we compare this to known times, if the memory takes one second to access, it takes the hard disk 183 DAYS. The difference between CPU and hard disk is even starker.

The second example contains an extra trip to the database. For performance reasons alone I would veto the second example as a code reviewer. However, as pointed out by another poster, if portability is a concern, code for SQLSTATE and not SQLCODE.