RE: SYSCOLUMNS.CURRENT_ROWS oddity...

Birgitta is on the right track--a DELETE is the culprit. However, it's not
the presence of deleted rows that are causing the mismatch. It's the fact
that CURRENT_ROWS really is the *current* count of rows at the time the
query is run, whereas NUMBER_DISTINCT_VALUES is the value that the
statistics engine calculated last time it analyzed the data in the column.
Column stats are not re-analyzed every time the table is changed--this
would be an unwelcome amount of overhead--so these values are often
slightly "stale." Thus, the most likely explanation is that a handful of
rows have been DELETEd since the latest column stat refresh. If you
manually refresh the column stats, you should see these values appear more
reasonable. Keep in mind, though, that NUMBER_DISTINCT_VALUES is always an
estimate based on sampling--there is no guarantee that it will be 100%
accurate.

We'll work on improving the SYSCOLUMNSTAT documentation to make this more
clear.

Hope that helps!

Tim Clark
DB2 for IBM i / SQL Optimizer

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 01/21/2021
12:26:20 AM:

From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-
l@xxxxxxxxxxxxxxxxxx>,
Date: 01/21/2021 12:26 AM
Subject: [EXTERNAL] RE: SYSCOLUMNS.CURRENT_ROWS oddity...
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

Any deleted rows? May be the DISTINCT_VALUES also considers the values

of

the deleted rows (a row is not physically deleted before an RGZPFM is
performed)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars."

(Les

Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training

them

and keeping them!"
„Train people well enough so they can leave, treat them well enough so

they

don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

Robert

Rogerson
Sent: Mittwoch, 20. Januar 2021 20:42
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: Re: SYSCOLUMNS.CURRENT_ROWS oddity...

Charles, that returns 716 which I would expect. There is a unique key

on

cmcus#. select count(*) also returns 716.

Thanks,

Rob

On Wed, Jan 20, 2021 at 2:35 PM Charles Wilt <charles.wilt@xxxxxxxxx>

wrote:

How many actual distinct values are there?

select count(distinct cmcus#)
from qs36F.cusmasp;


Charles


On Wed, Jan 20, 2021 at 12:03 PM Robert Rogerson
<rogersonra@xxxxxxxxx>
wrote:

Hi All,

When running this query I don't understand why there are more
distinct values than rows.

SELECT column_name, current_rows, number_distinct_values
FROM syscolumnstat
WHERE table_schema = 'QS36F'
AND table_name = 'CUSMASP'
AND column_name = 'CMCUS#';

COLUMN_NAME CURRENT_ROWS NUMBER_DISTINCT_VALUES CMCUS# 716 724 I've
tried searching on Google and the IBM site but couldn't find any
explanation.

Has anyone seen this and can explain it?

Thanks,

Rob
--