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.

On Jun 17, 8:51*am, apple <jbapplewh... (AT) aep (DOT) com> wrote:
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,

On 17.6.2010 17:31, Ian wrote:
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.
*/

On Jun 17, 3:25*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
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.

Hi Ian,

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

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.
*/

I'm on v9.1, Range partition table is what I'm talking about. Looking
for "card" by partition hidden in the catalogs somewhere and is
updated with stats because I want a query to pull "card" for all
tables in a database and the tables that are partitioned, I want
"card" by partition.

This doesn't work ...

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

.... there is no "card" column in syscat.datapartitions. I wished there
was. It would solve my problem.

This won't work for me either ...

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

.... because it's on an individual table. And querying each and every
table would take way too long, especially numerous partitioned ones
with numerous partitions containing hundreds of millions of rows.

Am I out of luck?

On Jun 18, 4:04*pm, apple <jbapplewh... (AT) aep (DOT) com> wrote:
I'm afraid so, unless you can upgrade to v9.7, where the "card" column
was added to syscat.datapartitions (together with a lot of other new
columns).

--
Jeroen