RE: SYSCOLUMNS.CURRENT_ROWS oddity...

["Timothy P Clark" <timclark@xxxxxxxxxx>]

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
> > > --