Re: Which of the SYSIBM tables/views show the row count for

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

rob@xxxxxxxxx wrote:
> However, if I add this "standard" logical
> R IIMJOER                   PFILE(IIMJOE)
>   IPROD         25A 
> K IPROD 
> Against that same physical file then you will see the same times.
> 12.199
> 13.313
> 12.277
>
> So, why not just have the UNIQUE key on the physical?
>   
My response to this would be to put the primary key on the physical file 
where it belongs.

And I'm not sure why this causes such a change.  In fact, this is a real 
problem as far as I'm concerned.  It's one of the few places where a 
DDL-defined file seriously underperforms a DDS-defined file.  Even 
creating a non-unique index using CREATE INDEX still causes the very 
nasty 7-to-1 perform decrease.

It seems that the logic that allows a physical file to be keyed is much 
faster than a separate index object (i.e. logical file), and that SQL 
always uses a separate index object.  That's a painful reality.  I 
normally recommend DDL files over DDS files because up until now there 
was no reason not to.  However, it seems that you're stuck with that 
performance hit in DDL.

Ouch.  Score a big one for DDS.

Joe