Amit,
thanks for your reply,
if I run your query ,it's not the result I expect:
WITH TEMP1 ( DBPGNAME, DBPARTITIONNUM, ALLNODES) AS ( SELECT DBPGNAME
,MIN(DBPARTITIONNUM) ,VARCHAR( CHAR(MIN(DBPARTITIONNUM)),50) FROM
SYSCAT.DBPARTITIONGROUPDEF A GROUP BY DBPGNAME UNION ALL SELECT A.DBPGNAME
,A.DBPARTITIONNUM, (RTRIM(B.ALLNODES) || ', ' || CHAR(A.DBPARTITIONNUM))
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 DBPARTITIONNUM = ( SELECT MAX(DBPARTITIONNUM) 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) SELECT SUBSTR(TABLESPACE_NAME,1, 18) AS
TABLESPACE_NAME, CASE WHEN TABLESPACE_TYPE = 0 THEN 'DMS' ELSE 'SMS' END AS
TBS_TYPE, INT(TOTAL_PAGES) AS TOTAL_PAGES,
INT(TOTAL_PAGES*PAGE_SIZE/1024/1024) AS TOTAL_MB, INT(USED_PAGES) AS
USED_PAGES, INT(USED_PAGES*PAGE_SIZE/1024/1024) AS USED_MB, INT(FREE_PAGES)
AS FREE_PAGES, INT(FREE_PAGES*PAGE_SIZE/1024/1024) AS FREE_MB, ((USED_PAGES
* 100) / TOTAL_PAGES) AS PERC_USED, Y.DBPGNAME, Y.PARTITIONS FROM TABLE
(SNAPSHOT_TBS_CFG(CAST(NULL AS VARCHAR(20)), -2 )) AS X LEFT OUTER JOIN
TEMP3 AS Y ON RTRIM(X.TABLESPACE_NAME)= RTRIM(Y.TBSPACE)
TABLESPACE_NAME TBS_TYPE TOTAL_PAGES TOTAL_MB USED_PAGES USED_MB
FREE_PAGES FREE_MB PERC_USED DBPGNAME
PARTITIONS
------------------ -------- ----------- ----------- ----------- -----------
----------- ----------- -------------------- -------------------------------
----------------------------------------------------------------------------
--------------------- --------------------------------------------------
SQL0347W The recursive common table expression "DB2DWP.TEMP1" may contain
an
infinite loop. SQLSTATE=01605
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 4, 5
DLP_IDX DMS 50000 390 36032 281
13952 109 72 USERGROUP
2, 3, 5
For each row I only need the Partition for the Tablespaces.
Regards,
Joachim
"Amit" <aamit@hotmail.com> schrieb im Newsbeitrag
news:85e31e0f.0401161555.422abe70@posting.google.c om...
> You can use the following query to get the partition information. I
> hope you are aware that the snapshot table functions cannot be used
> from remote clients because you cannot implicitly attach to the
> instance from a remote client. This was in documented in known
> problems and workaround docs in fp 3.
> Thanks
> Amit (singleton at canada.com)
>
> WITH TEMP1 ( DBPGNAME, DBPARTITIONNUM, ALLNODES) AS ( SELECT DBPGNAME
> ,MIN(DBPARTITIONNUM) ,VARCHAR( CHAR(MIN(DBPARTITIONNUM)),50)
> FROM SYSCAT.DBPARTITIONGROUPDEF A
> GROUP BY DBPGNAME UNION ALL
> SELECT A.DBPGNAME ,A.DBPARTITIONNUM, (RTRIM(B.ALLNODES) || ', ' ||
> CHAR(A.DBPARTITIONNUM))
> 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
> DBPARTITIONNUM = ( SELECT MAX(DBPARTITIONNUM) 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)
> SELECT SUBSTR(TABLESPACE_NAME,1, 18) AS TABLESPACE_NAME,
> CASE WHEN TABLESPACE_TYPE = 0
> THEN 'DMS'
> ELSE 'SMS'
> END AS TBS_TYPE,
> INT(TOTAL_PAGES) AS TOTAL_PAGES,
> INT(TOTAL_PAGES*PAGE_SIZE/1024/1024) AS TOTAL_MB,
> INT(USED_PAGES) AS USED_PAGES,
> INT(USED_PAGES*PAGE_SIZE/1024/1024) AS USED_MB,
> INT(FREE_PAGES) AS FREE_PAGES,
> INT(FREE_PAGES*PAGE_SIZE/1024/1024) AS FREE_MB,
> ((USED_PAGES * 100) / TOTAL_PAGES) AS PERC_USED, Y.DBPGNAME,
> Y.PARTITIONS
> FROM TABLE (SNAPSHOT_TBS_CFG(CAST(NULL AS VARCHAR(20)), -2 )) AS X
> LEFT OUTER JOIN TEMP3 AS Y ON RTRIM(X.TABLESPACE_NAME)=
> RTRIM(Y.TBSPACE)
>
>
>
>
>
>
>
> "Bill Pellett" <wpellett@prodigy.net> wrote in message
news:<XxWNb.57465$Ee2.41821@newssvr31.news.prodigy .com>...
> > I don't believe you can get the partition number from the snapshot table
> > function. Use the following SQL to see partition numbers:
> >
> > select substr(tbspace,1,18), tbspaceid, substr(t.dbpgname,1,18),
> > dbpartitionnum from syscat.tablespaces as t, syscat.dbpartitiongroupdef
as d
> > where t.dbpgname = d.dbpgname;
> >
> > You won't see any of your temp table spaces since
SYSCAT.DBPARTITIONGROUPDEF
> > doesn't contain partition numbers for temp table spaces.
> >
> >
> > "Joachim Mueller" <jom0309@[--spam--]douglas-informatik.de> wrote in
message
> > news:bu8cd9$rli$1@news.dtag.de...
> > > Have written a litte sql for showing the utilization of the
tablespace.
> > > (found it within the newsgroup from Paul Vernon).
> > > Looks fine for an one Partition System only.
> > > But how i can I see on which Partiton the Tablesapces reside.
> > >
> > > select substr(TABLESPACE_NAME,1, 18) as TABLESPACE_NAME,
> > > case when TABLESPACE_TYPE = 0
> > > then 'DMS'
> > > else 'SMS'
> > > end as TBS_TYPE,
> > > int(TOTAL_PAGES) as TOTAL_PAGES,
> > > int(TOTAL_PAGES*PAGE_SIZE/1024/1024) as TOTAL_MB,
> > > int(USED_PAGES) as USED_PAGES,
> > > int(USED_PAGES*PAGE_SIZE/1024/1024) as USED_MB,
> > > int(FREE_PAGES) as FREE_PAGES,
> > > int(FREE_PAGES*PAGE_SIZE/1024/1024) as FREE_MB,
> > > ((USED_PAGES * 100) / TOTAL_PAGES) as PERC_USED
> > > from table (snapshot_tbs_CFG(cast(null as varchar(20)), -2 )) as x
> > > order by PERC_USED desc;
> > >
> > > Output:
> > >
> > > TABLESPACE_NAME TBS_TYPE TOTAL_PAGES TOTAL_MB USED_PAGES
USED_MB
> > > FREE_PAGES FREE_MB PERC_USED
> > >
> >
> ------------------ -------- ----------- ----------- ----------- ----------
> > -
> > > ----------- ----------- --------------------
> > >
> > > TEMPSPACE4 SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE4 SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE4 SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE4 SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE4 SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE8K SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE8K SMS 1409 11 1409
> > 11
> > > 0 0 100
> > >
> > > TEMPSPACE8K SMS 1409 11 1409
> > 11
> > > 0 0 100
> > >
> > > TEMPSPACE8K SMS 1409 11 1409
> > 11
> > > 0 0 100
> > >
> > > TEMPSPACE8K SMS 1409 11 1409
> > 11
> > > 0 0 100
> > >
> > > TEMPSPACE32K SMS 1 0 1
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE32K SMS 17 0 17
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE32K SMS 17 0 17
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE32K SMS 17 0 17
> > 0
> > > 0 0 100
> > >
> > > TEMPSPACE32K SMS 17 0 17
> > 0
> > > 0 0 100
> > >
> > > INDEXSPACE DMS 2560000 20000 2109456
> > 16480
> > > 450512 3519 82
> > >
> > > USERSPACE DMS 3000000 23437 2400736
> > 18755
> > > 599200 4681 80
> > >
> > > USERSPACE DMS 3000000 23437 2412256
> > 18845
> > > 587680 4591 80
> > >
> > > USERSPACE DMS 3000000 23437 2377088
> > 18571
> > > 622848 4866 79
> > > .....
> > >
> > > --
> > >
> > > regards
> > > Joachim Müller
> > >
> > >