Beginner question: a lock without exceptions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Maks Romih - 23 Nov 2005 14:19 GMT

I'm not experienced in db2, but I have to write some rather complex
processing with stored procedures for db2 on as400.

The database consists of some twenty tables and is prepared in advance
so it does not change, while I run my procedures. I call the
procedures over ODBC from a Microsoft Access and Microsoft Windows
client. The procedures also doesn't write anything into this database,
but all the output goes into "global temporary tables". At the end the
results are sent over ODBC to the client. There are also other users
working on the same database at the same time. I don't know how many
of them and what they do, but it is sure, that they don't change the
data in the tables as well.

Now the problem is, that I sometimes get different results from one
run to another although the database is not changend in the
meantime. The database is changed only over the night.

Is it possible, that someone locks some data and my procedures
silently skips such data without raising any expcetion? I didn't give
any special options in may SQL. I also didn't write any code to catch
the exceptions, so they would stop the program, if they occured.

The processing is rather lenghty, about half an hour, and the error
happens so rarely that I'm quite in a despair to catch it by trial and
error.

Maks Romih

Reply to this Message

Brian Tkatch - 23 Nov 2005 14:53 GMT

I would wonder if things actually are being changed. If they are, and
you require a consistant snapshot, ISOLATION LEVEL might be what you
need. The SQL Reference Volume 1 in "Chapter 1. Concepts" has a section
on "Isolation levels" and "Appendix H. Comparison of isolation levels"
has a chart comparing the different ones.

B.

Reply to this Message

maksr - 24 Nov 2005 13:03 GMT

Thank you for your consideration.

I've come a little closer to the problem, but it seems, still further
from the solution.
The problem is not in locking or other users. That's why I changed the
thread title.

The records get lost in a simple SQL that only copies one temporary
table into another.
The table is about million records, some hundred bytes each record.

I've noticed, that every time, when it happens, the system is somehow
very loaded.
One of the SQL-s in the process that usually finishes in a second, at
that time needs 15 minutes.

But it does finish, everything does finish. Without exception.
And finishes wrong.

Is it possible to give some option or some other way of running my
stored procedures in a more sensitive way, so I would get an exception
if the system is too loaded, or simply somehow raise the "level of
sensitivity". It's hardly credible that the records can be lost so
simply.

Maks Romih.

Reply to this Message