Under which isolation level does the query run?
Consider using RS or RR, and commit in the loop to free up any locks,
assuming this will not (greatly) inconvenience the other table users.
You may be seeing nonrepeatable reads (use RS) or phantoms (use RR).
Another avenue of approach: look at the explain plan: Are you getting the
rowcount from an indexscan or a tablescan?
Regards
Alex
-----Original Message-----
From: Damir Wilder
[mailto:db2udbdba-ezmlmshield-x65425972.[Email address protected]
Sent: 19 October 2005 14:18
To: LazyDBA Discussion
Subject: inconsistent rowcount
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
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
******************************************************
The information in this E-mail and in any attachment is
confidential and is intended solely for the addressee.
Access, copying, disclosure or use of such information
by anyone else is unauthorised. If you are not the
intended recipient please contact [Email address protected]
While reasonable efforts are made to ensure these files are free
of virus infection and offensive materials, if something of this
nature is inadvertently sent to you, please destroy it, accept
our apologies and contact [Email address protected] with
details of the sender. We will ensure that action is taken
immediately to prevent any recurrence. Debenhams accept no
responsibility for any views expressed by the originator of this email.
Debenhams Retail plc (reg. no. 83395) Registered in England and Wales.
Registered office: 1 Welbeck Street, London W1G 0AA.
http://www.debenhams.com
******************************************************
DB2 & UDB email list listserv db2-l LazyDBA home page