zOs/SQL/IMSFRE7
-----------------------------------------------------------------------
-- SELECT ALLER IMS-DATENBANKEN, WELCHE NICHT GENÜGEND --
-- FREESPACE HABEN --
-- (1.TEIL: DEFAULTWERTE / 2.TEIL: DB-ABHÄNGIGE WERTE) --
-----------------------------------------------------------------------
--
SELECT JN.DB_NAME, JN.DD_NAME,
digits(int(JN.ALLOC)) AS ALLOC,
digits(int(MAX_SPC)) AS MAX_SPC,
digits(int(JN.HIGHWATER)) AS HIGHWATER,
digits(int(JN.THRESHOLD)) AS THRESHOLD,
digits(int(JN.HI_ALLOC)),
digits(int(JN.HI_ALLOC2)),
JN.SW_FREE, JN.SP_FREE, JN.SP_IFREE,
JN.DB_TYP, (JN.TRK_SEC), 'DEFAULT',
JN.HI_ALCSPC, JN.CAPACITY
FROM (SELECT SP.DB_NAME, SP.DD_NAME,
(SP.HI_ALLOC-SP.FREESPC) AS ALLOC,
CASE
WHEN SP.TRK_SEC = 0 then
real(sP.HI_ALLOC2)
WHEN SP.DB_TYP = 'HDAM'
AND SP.TRK_SEC > 0 THEN
real(8589934590)
WHEN SP.DB_TYP = 'PHDAM'
AND SP.TRK_SEC > 0 THEN
real(4294967295)
ELSE 0
END AS MAX_SPC,
CASE
WHEN SP.DB_TYP = 'HDAM' THEN
(real(8589934590)
- (real(SW.FREESPCP)* real(8589934590) / 100))
WHEN SP.DB_TYP = 'PHDAM' THEN
(real(4294967295) - (real(SW.FREESPCP)
* real(4294967295) / 100))
ELSE 0
END AS HIGHWATER,
CASE
WHEN SP.TRK_SEC = 0
AND SP.HI_ALLOC2 > 0
AND SW.FREESPCP > 0 THEN
SP.HI_ALLOC2 * SW.FREESPCP / 100
WHEN SP.DB_TYP = 'HDAM'
AND SW.FREESPCP > 0
AND SP.TRK_SEC > 0 THEN
real(8589934590) * SW.FREESPCP / 100
WHEN SP.DB_TYP = 'PHDAM'
AND SW.FREESPCP > 0
AND SP.TRK_SEC > 0 THEN
real(4294967295) * SW.FREESPCP / 100
ELSE 0
END AS THRESHOLD, SW.FREESPCP AS SW_FREE,
SP.HI_ALLOC, SP.HI_ALLOC2,
SP.FREESPCP AS SP_FREE,
SP.IFREESPCP AS SP_IFREE, SP.DB_TYP,
SP.TRK_SEC * CAPACITY AS TRK_SEC,
SP.EXTENTS, SP.HI_ALCSPC, SP.CAPACITY
FROM oa1a.TADM31A1 SP,
oa1a.TADM32A1 SW
WHERE SP.DB_ACCESS = 'OSAM'
AND SP.DB_TYP IN ('HDAM','PHDAM')
AND SP.FREESPCP < SW.FREESPCP
AND SW.DB_NAME = 'DEFAULT'
AND SW.DD_NAME = 'DEFAULT'
AND SW.END_DATUM >= CURRENT TIMESTAMP
AND SP.RUNTIME = (SELECT MAX(RUNTIME)
FROM oa1a.TADM31A1
WHERE DB_NAME = SP.DB_NAME
AND DD_NAME = SP.DD_NAME)
AND SP.RUNTIME >= CURRENT TIMESTAMP - 7 DAYS) AS JN
LEFT OUTER JOIN
oa1a.TADM32A1 SX
ON SX.DB_NAME = JN.DB_NAME
AND (SX.DD_NAME = JN.DD_NAME
OR SX.DD_NAME = 'ALL')
AND SX.END_DATUM >= CURRENT TIMESTAMP
AND (SX.FREESPCP > 0
OR SX.EXCLUDE = 'Y')
WHERE SX.DB_NAME IS NULL
AND ((JN.TRK_SEC > 0
AND (JN.HIGHWATER-JN.ALLOC) / JN.TRK_SEC < 1)
OR (JN.TRK_SEC = 0
AND JN.SP_FREE < JN.SW_FREE))
UNION
SELECT JN.DB_NAME, JN.DD_NAME,
digits(int(JN.ALLOC)) AS ALLOC,
digits(int(MAX_SPC)) AS MAX_SPC,
digits(int(JN.HIGHWATER)) AS HIGHWATER,
digits(int(JN.THRESHOLD)) AS THRESHOLD,
digits(int(JN.HI_ALLOC)),
digits(int(JN.HI_ALLOC2)),
JN.SW_FREE, JN.SP_FREE, JN.SP_IFREE,
JN.DB_TYP, (JN.TRK_SEC), 'DB-SPEZ',
JN.HI_ALCSPC, JN.CAPACITY
FROM (SELECT SP.DB_NAME, SP.DD_NAME,
(SP.HI_ALLOC-SP.FREESPC) AS ALLOC,
CASE
WHEN SP.TRK_SEC = 0 THEN
SP.HI_ALLOC2
WHEN SP.DB_TYP = 'HDAM'
AND SP.TRK_SEC > 0 THEN
real(8589934590)
WHEN SP.DB_TYP = 'PHDAM'
AND SP.TRK_SEC > 0 THEN
real(4294967295)
ELSE 0
END AS MAX_SPC,
CASE
WHEN SP.DB_TYP = 'HDAM' THEN
(real(8589934590) -
(SW.FREESPCP * real(8589934590) / 100))
WHEN SP.DB_TYP = 'PHDAM' THEN
(real(4294967295) - (SW.FREESPCP
* real(4294967295) / 100))
ELSE 0
END AS HIGHWATER,
CASE
WHEN SP.TRK_SEC = 0
AND SP.HI_ALLOC2 > 0
AND SW.FREESPCP > 0 THEN
SP.HI_ALLOC2 * SW.FREESPCP / 100
WHEN SP.DB_TYP = 'HDAM'
AND SW.FREESPCP > 0
AND SP.TRK_SEC > 0 THEN
real(8589934590) * SW.FREESPCP / 100
WHEN SP.DB_TYP = 'PHDAM'
AND SW.FREESPCP > 0
AND SP.TRK_SEC > 0 THEN
real(4294967295) * SW.FREESPCP / 100
ELSE 0
END AS THRESHOLD, SW.FREESPCP AS SW_FREE,
SP.HI_ALLOC, SP.HI_ALLOC2,
SP.FREESPCP AS SP_FREE,
SP.IFREESPCP AS SP_IFREE, SP.DB_TYP,
SP.TRK_SEC * CAPACITY AS TRK_SEC,
SP.EXTENTS, SP.HI_ALCSPC, SP.CAPACITY
FROM oa1a.TADM31A1 SP,
oa1a.TADM32A1 SW
WHERE SP.DB_ACCESS = 'OSAM'
AND SP.DB_TYP IN ('HDAM','PHDAM')
AND SP.FREESPCP < SW.FREESPCP
AND SW.DB_NAME = SP.DB_NAME
AND (SW.DD_NAME = SP.DD_NAME
OR SW.DD_NAME = 'ALL')
AND SW.EXCLUDE = 'N'
AND SW.END_DATUM >= CURRENT TIMESTAMP
AND SP.RUNTIME = (SELECT MAX(RUNTIME)
FROM oa1a.TADM31A1
WHERE DB_NAME = SP.DB_NAME
AND DD_NAME = SP.DD_NAME)
AND SP.RUNTIME >= CURRENT TIMESTAMP - 7 DAYS) AS JN
WHERE ((JN.TRK_SEC > 0
AND (JN.HIGHWATER-JN.ALLOC) / JN.TRK_SEC < 1)
OR (JN.TRK_SEC = 0
AND JN.SP_FREE < JN.SW_FREE))
WITH UR