By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

inconsistent rowcount

P: n/a
Hi,
can someone please help me out with the following problem:

I run a simple Korn shell script:
----
db2 connect to $EZERSQLDB user $FCWDBUSER using $FCWDBPASSWORD 1>$-
while [[ 1 = 1 ]];
do
db2 -x "SELECT count(*), current timestamp FROM DB2ADMIN.STANJE_R"
sleep 10
done
----
that returns the number of rows present in the table (10858), but
occasionally the number returned is one less than the expected result
(10857), or even worse - one more (10859):
...
10858 2005-10-19-13.24.08.769326
10858 2005-10-19-13.24.18.823832
10857 2005-10-19-13.24.28.884950
10858 2005-10-19-13.24.50.091621
10858 2005-10-19-13.25.00.221457
...
10858 2005-10-19-13.43.27.441652
10858 2005-10-19-13.43.37.633690
10857 2005-10-19-13.43.47.686231
10858 2005-10-19-13.43.57.839650
10858 2005-10-19-13.44.07.894608
...
10858 2005-10-19-13.14.24.921809
10859 2005-10-19-13.14.34.973751
10858 2005-10-19-13.14.45.248090
10858 2005-10-19-13.14.55.413005
...
The table itself contains a fairly constant number of rows (currently
10858), with up to several new inserts daily.
No row can ever be (and is not) deleted from the table, but present rows can
be (and are, at least once a day) updated frequently.

How is this possible??

OS is AIX 4.3.3, and "db2level" shows:
DB21085I Instance "db2" uses DB2 code release "SQL07025" with level
identifier
"03060105" and informational tokens "DB2 v7.1.0.68", "s020616" and
"U484480".

Not the newest platform level(s), I agree, but still the results of the
query are beyond me :-(
Regards,
Damir Wilder
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Try going to the online information center
(http://publib.boulder.ibm.com/infoce...8luw/index.jsp)
and doing a search on:
"isolation level"

You probably want to read the section: "Isolation levels" (50%) first.
Phil Sherman



proba wrote:
Hi,
can someone please help me out with the following problem:

I run a simple Korn shell script:
----
db2 connect to $EZERSQLDB user $FCWDBUSER using $FCWDBPASSWORD 1>$-
while [[ 1 = 1 ]];
do
db2 -x "SELECT count(*), current timestamp FROM DB2ADMIN.STANJE_R"
sleep 10
done
----
that returns the number of rows present in the table (10858), but
occasionally the number returned is one less than the expected result
(10857), or even worse - one more (10859):
...
10858 2005-10-19-13.24.08.769326
10858 2005-10-19-13.24.18.823832
10857 2005-10-19-13.24.28.884950
10858 2005-10-19-13.24.50.091621
10858 2005-10-19-13.25.00.221457
...
10858 2005-10-19-13.43.27.441652
10858 2005-10-19-13.43.37.633690
10857 2005-10-19-13.43.47.686231
10858 2005-10-19-13.43.57.839650
10858 2005-10-19-13.44.07.894608
...
10858 2005-10-19-13.14.24.921809
10859 2005-10-19-13.14.34.973751
10858 2005-10-19-13.14.45.248090
10858 2005-10-19-13.14.55.413005
...
The table itself contains a fairly constant number of rows (currently
10858), with up to several new inserts daily.
No row can ever be (and is not) deleted from the table, but present rows can
be (and are, at least once a day) updated frequently.

How is this possible??

OS is AIX 4.3.3, and "db2level" shows:
DB21085I Instance "db2" uses DB2 code release "SQL07025" with level
identifier
"03060105" and informational tokens "DB2 v7.1.0.68", "s020616" and
"U484480".

Not the newest platform level(s), I agree, but still the results of the
query are beyond me :-(
Regards,
Damir Wilder

Nov 12 '05 #2

P: n/a
Phil,

actually interesting question: Do I need to be able to lock a row for
counting? Especially when having an index.

Second: I still don't understand how to get an extra row ...

Damir - you have an index on that table ? And what isolation level are
you using ?

Juliane

Nov 12 '05 #3

P: n/a
Juliane,
there are two indices, one of them the primary key, the other ordinary,
duplicates-allowed.
The isolation level is CS, but that cannot explain the fact that from time
to time I see extra rows (that can also hardly explain the missing rows...).
There were no inserts into the table (whether commited or not) at the time,
so the number of _actual_ rows in the table didn't change at all.
There were some updates, that is certain.

In case of a lock-issue, I would expect to receive a deadlock or
lock-timeout SQL error message, and not a completely wrong answer :-(

Again, the DB2 level is not the newest: V7.2.5, neither is the fixpak the
latest.
Could this have something to do with it?

--

Pozdrav,
Damir Wilder
ETNA d.o.o.
tel: 4611-111
---

"juliane26" <ju*******@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Phil,

actually interesting question: Do I need to be able to lock a row for
counting? Especially when having an index.

Second: I still don't understand how to get an extra row ...

Damir - you have an index on that table ? And what isolation level are
you using ?

Juliane

Nov 12 '05 #4

P: n/a
proba wrote:
Juliane,
there are two indices, one of them the primary key, the other ordinary,
duplicates-allowed.
The isolation level is CS, but that cannot explain the fact that from time
to time I see extra rows (that can also hardly explain the missing
rows...). There were no inserts into the table (whether commited or not)
at the time, so the number of _actual_ rows in the table didn't change at
all. There were some updates, that is certain.

In case of a lock-issue, I would expect to receive a deadlock or
lock-timeout SQL error message, and not a completely wrong answer :-(

Again, the DB2 level is not the newest: V7.2.5, neither is the fixpak the
latest.
Could this have something to do with it?


It could. I dimly remember that there was an APAR due to incorrect results
of COUNT(*). But that was quite a while in the past (might have been V7)
and I don't remember any specifics. :-(

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #5

P: n/a
> It could. I dimly remember that there was an APAR due to incorrect
results
of COUNT(*). But that was quite a while in the past (might have been V7)
and I don't remember any specifics. :-(


Perhaps this APAR?

JR18178 ROWCOUNT IS WRONG AFTER APPLICATION RUNS OUT OF APP_CTL_HEAP

It is certainly worth the effort to fix the DB to the latest level.

Thanks,
Damir
Nov 12 '05 #6

P: n/a
Yes, this APAR fits perfectly

Thanks, Damir and Knut

Nov 12 '05 #7

P: n/a
"proba" <pr***@proba.com> wrote in message
news:dj**********@ss405.t-com.hr...
Again, the DB2 level is not the newest: V7.2.5, neither is the fixpak the
latest.
Could this have something to do with it?

Damir Wilder
ETNA d.o.o.
tel: 4611-111
---

You can upgrade to FP14 (or whatever) for free. There is no excuse for being
that far behind in a FP level.
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.