Apparently there is an APAR that fits very well into this problem:

JR18178 ROWCOUNT IS WRONG AFTER APPLICATION RUNS OUT OF APP_CTL_HEAP

Could this be the solution to the problem?
Present DB2 level is 7.2.5 (fixpak 7a), whereas this APAR is included/solved
in fixpak 10.

Regards,
Damir Wilder


----- Original Message -----
From: "Levy Alex " <db2udbdba-ezmlmshield-x20596522.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, October 19, 2005 3:38 PM
Subject: RE: inconsistent rowcount


> 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