SQL queries for Storage calculation, in DB2 v10 (NFM) on z/OS
There are multiple
ways to calculate Storage at Object-level. I coded some SQL queries which will
make the Storage calculation easy and effective.
For process automation of Storage calculation at Database-level, you can refer my earlier blog post.
1. To get the details of Tablespaces
whose Used Pages percentage crossed 80.
SELECT
SUBSTR(SPCRPT.DBNAME,1,8) AS
DBNAME
,SUBSTR(SPCRPT.NAME,1,10) AS
TSNAME
,SUBSTR(CHAR(SPCRPT.PART),1,4) AS
PART
,SUBSTR(CHAR(SPCRPT.TROW),1,10) AS
TOT_ROWS
,SUBSTR(CHAR(SPCRPT.EXTENTS),1,4) AS
EXTENTS
,SUBSTR(CHAR(SPCRPT.ALLOC_SPACE_INKB),1,10) AS
ALLOC_SPC_INKB
,SUBSTR(CHAR(SPCRPT.MAX_SPACE_INKB),1,10) AS
MAX_SPC_INKB
,SUBSTR(CHAR(SPCRPT.ALLOC_SPACE_INPAGES),1,10) AS ALLOC_SPC_INPAGES
,SUBSTR(CHAR(SPCRPT.MAX_SPACE_INKB/SPCRPT.PGSIZE),1,10)
AS
MAX_SPC_INPAGES
,SUBSTR(CHAR(ROUND((SPCRPT.ALLOC_SPACE_INPAGES*100)/
((SPCRPT.MAX_SPACE_INKB/
(SPCRPT.PGSIZE-((SPCRPT.PGSIZE*SPCRPT.PCTFREE)/100)))
-SPCRPT.FREEPAGE),2)),1,4) AS USED_PAGES_PERC
,SUBSTR(CHAR(SPCRPT.ALLOC_SPACE_INTRACKS),1,8) AS ALLOC_SPC_INTRKS
,SUBSTR(CHAR(SPCRPT.MAX_SPACE_INTRACKS),1,8) AS MAX_SPC_INTRKS
,SUBSTR(CHAR(ROUND((SPCRPT.ALLOC_SPACE_INTRACKS*100)
/MAX_SPACE_INTRACKS,2)),1,4) AS USED_TRACKS_PERC
FROM(
SELECT
STATS.DBNAME
,STATS.NAME
,CASE WHEN
STATS.PARTITION IS NULL THEN -1
ELSE STATS.PARTITION
END AS
PART
,CASE WHEN
STATS.TOTALROWS IS NULL THEN -1
ELSE STATS.TOTALROWS
END AS
TROW
,CASE WHEN
STATS.EXTENTS IS NULL THEN -1
ELSE STATS.EXTENTS
END AS
EXTENTS
,CASE WHEN
STATS.SPACE IS NULL THEN -1
ELSE STATS.SPACE
END AS
ALLOC_SPACE_INKB
,(STATS.SPACE/48)
AS
ALLOC_SPACE_INTRACKS
,CASE WHEN
STATS.NACTIVE IS NULL THEN -1
ELSE STATS.NACTIVE
END AS
ALLOC_SPACE_INPAGES
,TS.PGSIZE
,TP.FREEPAGE
,TP.PCTFREE
,(CASE
WHEN SUBSTR(TS.DSSIZE,1,1)
IN
(0,2,3,4,8)
AND
TS.TYPE=' ' THEN CEILING((64*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,2)=16 AND TS.TYPE=' '
THEN
CEILING((64*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,1)=2 THEN CEILING((2*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,1)=3 THEN CEILING((32*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,1)=4 THEN CEILING((4*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,1)=6 THEN CEILING((64*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,1)=8 THEN CEILING((8*1024*1024)/48)
WHEN SUBSTR(TS.DSSIZE,1,2)=16
THEN
CEILING((16*1024*1024)/48)
ELSE CEILING((4*1024*1024)/48)
END) AS
MAX_SPACE_INTRACKS
,(CASE TS.DSSIZE
WHEN 0
THEN
CASE WHEN
TS.TYPE = 'G' THEN 4194304
WHEN TS.TYPE =
'L'
THEN
4194304
WHEN TS.TYPE =
'O'
THEN
4194304
WHEN TS.TYPE =
'P'
THEN
4194304
WHEN TS.TYPE =
'R'
THEN
4194304
WHEN TS.TYPE =
' '
THEN
67108864
ELSE
CASE
WHEN
TS.PARTITIONS > 254 THEN
CASE
WHEN
TS.PGSIZE = 4 THEN 4194304
WHEN
TS.PGSIZE = 8 THEN 8388608
WHEN
TS.PGSIZE = 16 THEN
16777216
WHEN
TS.PGSIZE = 32 THEN
33554432
ELSE
NULL
END
WHEN
TS.PARTITIONS > 64 THEN
4194304
WHEN
TS.PARTITIONS > 32 THEN
1048576
WHEN
TS.PARTITIONS > 16 THEN
2097152
WHEN
TS.PARTITIONS > 0 THEN 4194304
ELSE
2097152
END
END
ELSE TS.DSSIZE
END) AS
MAX_SPACE_INKB
FROM
SYSIBM.SYSTABLESPACESTATS STATS
INNER JOIN
SYSIBM.SYSTABLESPACE TS
ON STATS.DBNAME =
TS.DBNAME AND
STATS.NAME =
TS.NAME
INNER JOIN
SYSIBM.SYSTABLEPART TP
ON STATS.DBNAME =
TP.DBNAME AND
STATS.NAME =
TP.TSNAME AND
STATS.PARTITION =
TP.PARTITION
WHERE STATS.DBNAME LIKE
'LP%'
OR STATS.DBNAME LIKE
'ARCH%'
OR STATS.DBNAME LIKE
'DPOODS%'
OR STATS.DBNAME LIKE
'DP1%'
OR STATS.DBNAME LIKE
'DBPROD%'
) AS
SPCRPT
WHERE
DECIMAL((SPCRPT.ALLOC_SPACE_INPAGES*100)/((SPCRPT.MAX_SPACE_INKB/(SPCRPT.PGSIZE-((SPCRPT.PGSIZE*5)/100)))-SPCRPT.FREEPAGE),4,2)
>
80
ORDER
BY
1,
2,
3
WITH UR;
2.
To
get the details of Indexes whose Used Pages percentage crossed 80.
SELECT
SPCRPT.DBNAME
,SPCRPT.NAME
,SPCRPT.PARTITION
,SPCRPT.TOTALENTRIES
,SPCRPT.EXTENTS
,SPCRPT.ALLOC_SPACE_INKB
,SPCRPT.MAX_SPACE_INKB
,SPCRPT.ALLOC_SPACE_INPAGES
,(SPCRPT.MAX_SPACE_INKB/SPCRPT.PAGE_SIZE)
AS
MAX_SPACE_INPAGES
,DECIMAL((SPCRPT.ALLOC_SPACE_INPAGES*100)/((SPCRPT.MAX_SPACE_INKB/(SPCRPT.PAGE_SIZE-((SPCRPT.PAGE_SIZE*10)/100)))-SPCRPT.FREEPAGE),6,2)
AS
USED_PAGES_PERC
,SPCRPT.ALLOC_SPACE_INTRACKS,
(SPCRPT.MAX_SPACE_INKB/48) AS MAX_SPACE_INTRACKS
FROM(
SELECT
STATS.DBNAME, STATS.NAME,
STATS.PARTITION, STATS.TOTALENTRIES, STATS.EXTENTS,
STATS.SPACE AS
ALLOC_SPACE_INKB, (STATS.SPACE/48) AS
ALLOC_SPACE_INTRACKS,
STATS.NACTIVE AS
ALLOC_SPACE_INPAGES, IX.PGSIZE, IP.FREEPAGE,
CASE
WHEN IX.PIECESIZE=0 THEN MIN(TS.DSSIZE,4294967296/(MIN(4096,4294967296/(TS.DSSIZE/TS.PGSIZE)))*IX.PGSIZE)
WHEN IX.PIECESIZE<>0
THEN
32*IX.PIECESIZE
ELSE IX.PIECESIZE
END AS
MAX_SPACE_INKB,
CASE
WHEN IX.PGSIZE=4096
THEN
4
ELSE IX.PGSIZE
END AS
PAGE_SIZE
FROM
SYSIBM.SYSINDEXSPACESTATS STATS
INNER JOIN
SYSIBM.SYSINDEXES IX ON
STATS.DBNAME =
IX.DBNAME AND
STATS.NAME =
IX.NAME
INNER JOIN
SYSIBM.SYSINDEXPART IP ON
STATS.DBNAME =
IP.IXCREATOR AND
STATS.NAME =
IP.IXNAME AND
STATS.PARTITION =
IP.PARTITION
INNER JOIN
SYSIBM.SYSTABLESPACE TS ON
STATS.DBNAME =
TS.DBNAME AND
STATS.PSID =
TS.PSID
WHERE STATS.DBNAME LIKE
'LP%'
OR STATS.DBNAME LIKE
'ARCH%'
OR STATS.DBNAME LIKE
'DPOODS%'
OR STATS.DBNAME LIKE
'DP1%'
OR STATS.DBNAME LIKE
'DBPROD%'
) AS
SPCRPT
WHERE
DECIMAL((SPCRPT.ALLOC_SPACE_INPAGES*100)/((SPCRPT.MAX_SPACE_INKB/(SPCRPT.PAGE_SIZE-((SPCRPT.PAGE_SIZE*10)/100)))-SPCRPT.FREEPAGE),6,2)
>
80
ORDER
BY
1,
2,
3
WITH UR;
3.
To
get the Storage details of all Tablespaces, grouped by Database.
SELECT
SPCRPT.DBNAME
,SUM(SPCRPT.USED_SPACE_INMB) AS
USED_SPACE_ALLTS_MEGABYTES
,SUM(SPCRPT.ALLOC_SPACE_INMB) AS
ALLOC_SPACE_ALLTS_MEGABYTES
,SUM(SPCRPT.TOTAL_AVAIL_SPACE_INGB)
AS
MAX_SPACE_ALLTS_GROW_GIGABYTES
FROM(
SELECT
STATS.DBNAME
,((STATS.DATASIZE/1024)/1024)
AS
USED_SPACE_INMB
,(STATS.SPACE/1024) AS ALLOC_SPACE_INMB
,((CASE TS.DSSIZE
WHEN 0
THEN
CASE WHEN
TS.TYPE = 'G' THEN 4194304
WHEN TS.TYPE =
'L'
THEN
4194304
WHEN TS.TYPE =
'O'
THEN
4194304
WHEN TS.TYPE =
'P'
THEN
4194304
WHEN TS.TYPE =
'R'
THEN
4194304
WHEN TS.TYPE =
' '
THEN
67108864
ELSE
CASE
WHEN
TS.PARTITIONS > 254 THEN
CASE
WHEN
TS.PGSIZE = 4 THEN 4194304
WHEN
TS.PGSIZE = 8 THEN 8388608
WHEN
TS.PGSIZE = 16 THEN
16777216
WHEN
TS.PGSIZE = 32 THEN
33554432
ELSE
NULL
END
WHEN
TS.PARTITIONS > 64 THEN
4194304
WHEN
TS.PARTITIONS > 32 THEN
1048576
WHEN
TS.PARTITIONS > 16 THEN
2097152
WHEN
TS.PARTITIONS > 0 THEN 4194304
ELSE
2097152
END
END
ELSE TS.DSSIZE
END)/1024)/1024
AS
TOTAL_AVAIL_SPACE_INGB
FROM
SYSIBM.SYSTABLESPACESTATS STATS
INNER JOIN
SYSIBM.SYSTABLESPACE TS
ON STATS.DBNAME =
TS.DBNAME AND
STATS.NAME =
TS.NAME
WHERE STATS.DBNAME LIKE
'LP%'
) AS
SPCRPT
GROUP
BY
SPCRPT.DBNAME
WITH UR;
4.
To
get the Storage details of all Indexes, grouped by Database.
SELECT
SPCRPT.DBNAME
,SUM(SPCRPT.ALLOC_SPACE_INMB) AS
ALLOC_SPACE_ALLTS_MEGABYTES
,SUM(SPCRPT.TOTAL_AVAIL_SPACE_INMB)
AS
MAX_SPACE_ALLTS_GROW_MEGABYTES
FROM(
SELECT
STATS.DBNAME
,(STATS.SPACE/1024)
AS
ALLOC_SPACE_INMB
,(CASE
WHEN IX.PIECESIZE=0 THEN MIN(TS.DSSIZE,4294967296/(MIN(4096,4294967296/(TS.DSSIZE/TS.PGSIZE)))*IX.PGSIZE)
WHEN IX.PIECESIZE<>0
THEN
32*IX.PIECESIZE
ELSE IX.PIECESIZE
END)/1024
AS
TOTAL_AVAIL_SPACE_INMB
FROM
SYSIBM.SYSINDEXSPACESTATS STATS
INNER JOIN
SYSIBM.SYSINDEXES IX ON
STATS.DBNAME =
IX.DBNAME AND
STATS.NAME =
IX.NAME
INNER JOIN
SYSIBM.SYSTABLESPACE TS ON
STATS.DBNAME =
TS.DBNAME AND
STATS.PSID =
TS.PSID
WHERE STATS.DBNAME LIKE
'LP%'
) AS
SPCRPT
GROUP
BY
SPCRPT.DBNAME
WITH UR;
No comments:
Post a Comment