DB2 LUW 9.7 - Determine how many rows in a table's partition

Filippo Conti

unread,

Jul 10, 2012, 4:20:19 AM7/10/12

to

Hello,
How can i get rows amount per partition ?
It's to check correct data distribution inside table's partitions.
Can I do something like "select(*) count from table(partition0)" ?

Thanks in advance

Filippo

Frederik Engelen

unread,

Jul 10, 2012, 5:44:12 AM7/10/12

to

Just do a runstats and execute:

select card from syscat.datapartitions where tabname = 'XXX'

--
Frederik Engelen

diabolik_giant

unread,

Jul 10, 2012, 7:02:48 AM7/10/12

to

Il giorno martedì 10 luglio 2012 11:44:12 UTC+2, Frederik Engelen ha scritto:
> On Jul 10, 10:20 am, Filippo Conti <filippo.conti...@gmail.com> wrote:
> > Hello,
> > How can i get rows amount per partition ?
> > It's to check correct data distribution inside table's partitions.
> > Can I do something like "select(*) count from table(partition0)" ?
> >
> > Thanks in advance
> >
> > Filippo

>
> Just do a runstats and execute:
>

> select card from syscat.datapartitions where tabname = 'XXX'
>
> --
> Frederik Engelen

I saw but I need it at runtime (in a SP with "select into") and my SP can't execute statistics, any idea ?

Frederik Engelen

unread,

Jul 10, 2012, 10:34:51 AM7/10/12

to

> I saw but I need it at runtime (in a SP with "select into") and my SP can't execute statistics, any idea ?- Hide quoted text -
>
> - Show quoted text -

I would try creating the select count(*)-statement by joining
syscat.datapartitions on syscat.datapartitionexpression using - based
on your partition key - a combination of CASE statements, LISTAGG, row
value constructors , ... and execute it using EXECUTE IMMEDIATE. What
you can use depends on your DB2 version (RVC is V10 only). How
complicated that becomes depends mainly on your partition key, I can't
estimate that from here nor do I know for sure if it would be
possible :-)

--
Frederik Engelen

Lennart Jonsson

unread,

Jul 10, 2012, 11:50:12 AM7/10/12

to

On 2012-07-10 10:20, Filippo Conti wrote:
> Hello,
> How can i get rows amount per partition ?
> It's to check correct data distribution inside table's partitions.
> Can I do something like "select(*) count from table(partition0)" ?
>

There is a DATAPARTITIONNUM scalar function, example:

SELECT DATAPARTITIONNUM(<col>) as Partition
, COUNT(*) as Total_Rows
FROM <tbl>
group by DATAPARTITIONNUM(<col>)
order by DATAPARTITIONNUM(<col>);

See:

http://www-01.ibm.com/support/docview.wss?uid=swg21426352

/Lennart

Frederik Engelen

unread,

Jul 10, 2012, 3:20:42 PM7/10/12

to

On Jul 10, 5:50 pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:

Nice one...

--
Frederik Engelen

Mark A

unread,

Jul 10, 2012, 4:32:18 PM7/10/12

to

On Tue, 10 Jul 2012 17:50:12 +0200, Lennart Jonsson wrote:

> There is a DATAPARTITIONNUM scalar function, example:
>
> SELECT DATAPARTITIONNUM(<col>) as Partition
> , COUNT(*) as Total_Rows
> FROM <tbl>
> group by DATAPARTITIONNUM(<col>)
> order by DATAPARTITIONNUM(<col>);

Isn't that for DPF partitions only? I assume he is talking about Table Partitions, but not sure.

Fin

unread,

Jul 10, 2012, 7:34:00 PM7/10/12

to

Why can't you execute a runstats ?

Try using calling the admin_cmd procedure from within your SP
ie: CALL ADMIN_CMD('RUNSTATS ON TABLE ABC AND INDEXES ALL');

Frederik Engelen

unread,

Jul 11, 2012, 4:22:57 AM7/11/12

to

It's for range (table) partitioning, it looks like it should solve
Filippo's problem.

--
Frederik Engelen

Lennart Jonsson

unread,

Jul 11, 2012, 6:25:55 AM7/11/12

to

The FAQ starts with:

If a table is partitioned by range, how to find the number of rows
contained in each data partition?

So I assume it is not DPF only (can't try, on vacation so express-c is
all I got at hand).

/Lennart

Ian

unread,

Jul 11, 2012, 9:38:21 AM7/11/12

to

You're thinking of the DBPARTITIONNUM() function (aka NODENUMBER()).

--- Posted via news://freenews.netfront.net/ - Complaints to ne...@netfront.net ---