DB2 v7 z/OS: Our performance monitor has a SQL error summary that shows
thousands of SQL error codes per hour. Most of these errors are handled in
the application code (COBOL CICS) and do not result in an abend in which the
user is aware of. My question is how much CPU overhead is there in
processing
these errors, and would it be prudent to have the worst offending code
changed to prevent them. I am most concerned with the -305 and -811 and
to a lesser extent the -803 errors.
Example:
SQLCODE ERRORCNT
------- --------
-305 53592
-811 39446
-803 607
-104 8
-501 3
-911 3
-180 2
-551 2
QUESTION POSED ON: 29 JUN 2004
QUESTION ANSWERED BY:
Craig Mullins
Well, the straightforward answer to this question is "fix the application
code - it is broken." Your problem is more than just the issue of CPU
overhead for processing SQL errors - it is the total overhead of all of the
code that had to run to get you to the point of the error, at which point
the program stops and the end user gets no answer. That is all wasted
processing time - and there is really no way for me to tell you how much
time is wasted doing that because it depends on how much code is executed
each time before you get your error.
Why do I say your application is broken? Because it is not coded properly
given the type of data in your database, for one thing. A -305 states "THE
NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number
BECAUSE NO INDICATOR VARIABLE IS SPECIFIED." That means that your
applications are accessing data that can contain NULLs without being
prepared for the NULL to be returned. That is a programming error and needs
to be fixed.
OK, what about the -811? DB2 throws that error when "THE RESULT OF AN
EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE
STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A
BASIC PREDICATE IS MORE THAN ONE VALUE." In other words, the program is
coded such that it expects one row to be returned, but DB2 is returning
multiple rows. Once again, a programming mistake.
Same for the -803. That is returned when "AN INSERTED OR UPDATED VALUE IS
INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS
OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS."
In other words, most likely the program is trying to insert a duplicate row
into a table that has a primary key. Another example of a programming
mistake.
Just cleaning up those three types of problems will result in almost 100,000
fewer errors for whatever timeframe your monitor report was for (53592 +
39446 + 607 = 93,645).
The bottom line here is that your programs are written incorrectly. Clean up
the code so that it conforms to the data needs of the database (and the
business) and your resource usage will be much, much better. And your users
will be happier, too. For More Information
Related Topics:
Search Advice from more than 250 TechTarget Experts
Your question may have already been answered! Browse or search more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice
ASSOCIATED RSS FEEDS
 |
|
Expert Answer Center: Expert Q&As |
|
View all RSS Feeds | RSS Info
|
