Prev: avgrowcompressionration in syscat.tables not updated
Next: Sybase to DB2 = ANTS Software
From: apple on 17 Jun 2010 11:51
DB2 LUW v9.1

Is there any IBM supplied view, function, etc. that gives cardinality
per partition on a partitioned table? I can't find anything.
From: Ian on 17 Jun 2010 17:31
On Jun 17, 8:51 am, apple <jbapplewh...(a)aep.com> wrote:
> DB2  LUW v9.1
>
> Is there any IBM supplied view, function, etc. that gives cardinality
> per partition on a partitioned table? I can't find anything.

Are you looking for a query for range partitioning or database
partitioning?

For range partitioned tables, see the DATAPARTITIONNUM() scalar
function.
For DPF environments, see the DBPARTITIONNUM() scalar function.

As in,

select dbpartitionnum(col1), count(*)
from your_table group by dbpartitionnum(col1)


Thanks,

From: Helmut Tessarek on 17 Jun 2010 18:25
On 17.6.2010 17:31, Ian wrote:
> select dbpartitionnum(col1), count(*)
> from your_table group by dbpartitionnum(col1)

Unfortunately this won't work because of an invalid use of the function.

For a ranged partinioned table you can use the following statement:

select seqno, count(col1) from your_table, syscat.datapartitions
where tabname='your_table' and datapartitionnum(col1) = seqno
group by seqno

If your stats are accurate, you could also query the catalog:

select seqno, card from syscat.datapartitions
where tabname = 'your_table'

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/
From: Ian on 17 Jun 2010 20:45
On Jun 17, 3:25 pm, Helmut Tessarek <tessa...(a)evermeet.cx> wrote:
> On 17.6.2010 17:31, Ian wrote:
>
> > select dbpartitionnum(col1), count(*)
> >    from your_table group by dbpartitionnum(col1)
>
> Unfortunately this won't work because of an invalid use of the function.

What do you mean? For DPF that query will work just fine. (Assuming
you replace 'col1' with the name of any column in your table, and
'your_table'
with the name of an actual table).

Replace dbpartitionnum with datapartitionnum and it will also work for
range-
partitioned tables, too, giving you the seqno of the data partition
and the
row count. Caveat: You'll only get counts for data partitions that
actually
have data.



From: Helmut Tessarek on 17 Jun 2010 22:49
Hi Ian,

>>> select dbpartitionnum(col1), count(*)
>>> from your_table group by dbpartitionnum(col1)
>>
>> Unfortunately this won't work because of an invalid use of the function.
>
> What do you mean?

Sorry, my bad. I was using a broken DB2 version.

> Caveat: You'll only get counts for data partitions that
> actually have data.

Yes, but you can get the number of partitions with:

select max(seqno)+1 from syscat.datapartitions
where tabname='your_table' and tabschema='your_schema'

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/
 |  Next  |  Last
Pages: 1 2
Prev: avgrowcompressionration in syscat.tables not updated
Next: Sybase to DB2 = ANTS Software