You will either need to run the Tablespace Snaphot Query separately
(maybe use a sql proc) for each partition (you can pass in the
partition number to the table function) or use recursive sql (using a
common table expression) to call the table function repeatedly with
different partition numbers and show the results with the partition
number as the last column.

"Joachim Mueller" <jom0309@[--spam--]douglas-informatik.de> wrote in message news:<buis2q$93m$1@news.dtag.de>...
> Amit,
>
> thanks once more, but (a big BUT) sometimes the PERC_USED column is equal on
> all
> partitions, but most the the differ from partition to partition.
> What I want is to see which partition have reached the threshold for 85% so
> I can resize the container
> lying on this partition.
>
> Here is a better sample:
>
> TABLESPACE_NAME TBS_TYPE TOTAL_PAGES TOTAL_MB USED_PAGES USED_MB
> FREE_PAGES FREE_MB PERC_USED
>
> ------------------ -------- ----------- ----------- ----------- -----------
> ----------- ----------- --------------------
>
>
> USERSPACE DMS 3000000 23437 2382240 18611
> 617696 4825 79
>
> USERSPACE DMS 3000000 23437 2410496 18832
> 589440 4605 80
>
> USERSPACE DMS 3000000 23437 2406784 18803
> 593152 4634 80
>
> USERSPACE DMS 3000000 23437 2421152 18915
> 578784 4521 80
>
> INDEXSPACE DMS 2560000 20000 2022640 15801
> 537328 4197 79
>
> INDEXSPACE DMS 2560000 20000 2024432 15815
> 535536 4183 79
>
> INDEXSPACE DMS 2560000 20000 2014752 15740
> 545216 4259 78
>
> INDEXSPACE DMS 2560000 20000 2096592 16379
> 463376 3620 81
>
> You see that USERSPACE also INDEXSPACE have the same TOTAL_PAGES each on one
> of the four partitions.
> But USED_PAGES differs on the four partitions.
>
> Sorry for my bad english, it's hard to explain something thats not my native
> language;-)
>
> regards,
>
> Joachim
>
> "Amit" <aamit@hotmail.com> schrieb im Newsbeitrag
> news:85e31e0f.0401191548.680ee44@posting.google.co m...
> > oops - realised my query had some problems - here's the one that
> > calculates the aggregates and returns the total for each tablespace
> > If you want a query that returns the partition number for each row
> > instead of aggregate, you can also achive that similarly using sql
> > recursion (pass in the next node number as the secomd argument in
> > SNAPSHOT_TBS_CFG ). I think partition number should be in the result
> > set (and monitor elements) for tablespaces but was proabably left out
> > due to an oversight.
> >
> >
> > WITH TEMP1 ( DBPGNAME, DBPARTITIONNUM, ALLNODES, LEVEL) AS ( SELECT
> > DBPGNAME
> > ,MIN(DBPARTITIONNUM) ,VARCHAR( CHAR(MIN(DBPARTITIONNUM)),50), 0
> > FROM SYSCAT.DBPARTITIONGROUPDEF A
> > GROUP BY DBPGNAME UNION ALL
> > SELECT A.DBPGNAME ,A.DBPARTITIONNUM, (RTRIM(B.ALLNODES) || ', ' ||
> > CHAR(A.DBPARTITIONNUM)), B.LEVEL+1
> > FROM SYSCAT.DBPARTITIONGROUPDEF A , TEMP1 B
> > WHERE A.DBPGNAME = B.DBPGNAME
> > AND A.DBPARTITIONNUM > B.DBPARTITIONNUM
> > ),
> > TEMP2 (TBSPACE, DBPGNAME, ALLNODES) AS
> > ( SELECT TBSPACE, F.DBPGNAME, F.ALLNODES FROM SYSCAT.TABLESPACES D
> > LEFT OUTER JOIN ( SELECT C.DBPGNAME, C.ALLNODES FROM TEMP1 C WHERE
> > C.LEVEL = ( SELECT MAX(LEVEL) FROM TEMP1 E WHERE
> > C.DBPGNAME = E.DBPGNAME)) F
> > ON D.DBPGNAME=F.DBPGNAME ),
> > TEMP3 (TBSPACE, DBPGNAME, PARTITIONS ) AS
> > (SELECT TBSPACE, COALESCE( DBPGNAME, 'IBMTEMPGROUP') , COALESCE(
> > ALLNODES, CHAR((SELECT DBPARTITIONNUM FROM SYSCAT.DBPARTITIONGROUPDEF
> > WHERE DBPGNAME='IBMCATGROUP' FETCH FIRST ROW ONLY )) ) FROM TEMP2),
> > TEMP4 AS
> > (SELECT TABLESPACE_NAME , CASE WHEN TABLESPACE_TYPE = 0 THEN 'DMS'
> > ELSE 'SMS' END AS
> > TBS_TYPE, SUM(TOTAL_PAGES) AS TOTAL_PAGES,
> > SUM(TOTAL_PAGES*PAGE_SIZE)/1024/1024 AS TOTAL_MB, SUM(USED_PAGES) AS
> > USED_PAGES, SUM(USED_PAGES*PAGE_SIZE)/1024/1024 AS USED_MB,
> > SUM(FREE_PAGES)
> > AS FREE_PAGES, SUM(FREE_PAGES*PAGE_SIZE)/1024/1024 AS FREE_MB, CASE
> > WHEN SUM (TOTAL_PAGES) >0 THEN (SUM(USED_PAGES)
> > * 100 / SUM (TOTAL_PAGES) ) ELSE NULL END AS PERC_USED FROM TABLE
> > (SNAPSHOT_TBS_CFG(CAST(NULL AS VARCHAR(20)), -2 )) AS A GROUP BY
> > TABLESPACE_NAME, TABLESPACE_TYPE )
> > SELECT A.*, B.DBPGNAME, B.PARTITIONS FROM TEMP4 A INNER JOIN TEMP3 B
> > ON RTRIM(A.TABLESPACE_NAME) = RTRIM(B.TBSPACE)
> >
> >
> >
> >
> > aamit@hotmail.com (Amit) wrote in message
> news:<85e31e0f.0401190901.5a442a90@posting.google. com>...
> > > Not sure if I understand correctly. The last column of the query I
> > > posted showns the partitions that the tablespace exists on. The
> > > tablespace will exist on all partitions of the nodegroup (database
> > > partition group) that its defined on - not just one partition.
> > >
> > > Thanks
> > > Amit