zOs/SQL/IMSFRE2
select
( real(589934590) - (decimal(7, 10 ,2)* 589934590 / 100))
from sysibm.sysDummy1
;
select
(real(8589934590) - (real(decimal(7, 10 ,2))* 8589934590 / 100))
from sysibm.sysDummy1
;
-----------------------------------------------------------------------
-- 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,
(JN.ALLOC) AS ALLOC,
(MAX_SPC) AS MAX_SPC, (JN.HIGHWATER) AS HIGHWATER,
(JN.THRESHOLD) AS THRESHOLD,
(JN.HI_ALLOC), (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
SP.HI_ALLOC2
WHEN SP.DB_TYP = 'HDAM'
AND SP.TRK_SEC > 0 THEN
8589934590
WHEN SP.DB_TYP = 'PHDAM'
AND SP.TRK_SEC > 0 THEN
4294967295
ELSE 0
END AS MAX_SPC,
CASE
WHEN SP.DB_TYP = 'HDAM' THEN
(8589934590 - (real(SW.FREESPCP)* 8589934590 / 100))
WHEN SP.DB_TYP = 'PHDAM' THEN
(4294967295 - (real(SW.FREESPCP)* 4294967295 / 100))
ELSE 0
END AS HIGHWATER,
CASE
WHEN SP.TRK_SEC = 0
AND SP.HI_ALLOC2 > 0
AND SW.FREESPCP > 0 THEN
real(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 real(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,
(JN.ALLOC) AS ALLOC,
(MAX_SPC) AS MAX_SPC, (JN.HIGHWATER) AS HIGHWATER,
(JN.THRESHOLD) AS THRESHOLD,
(JN.HI_ALLOC), (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
8589934590
WHEN SP.DB_TYP = 'PHDAM'
AND SP.TRK_SEC > 0 THEN
4294967295
ELSE 0
END AS MAX_SPC,
CASE
WHEN SP.DB_TYP = 'HDAM' THEN
(real(8589934590)- (SW.FREESPCP * 8589934590 / 100))
WHEN SP.DB_TYP = 'PHDAM' THEN
(Real(4294967295)- (SW.FREESPCP * 4294967295 / 100))
ELSE 0
END AS HIGHWATER,
CASE
WHEN SP.TRK_SEC = 0
AND SP.HI_ALLOC2 > 0
AND SW.FREESPCP > 0 THEN
real(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 real(JN.HIGHWATER-JN.ALLOC) / JN.TRK_SEC < 1)
OR (JN.TRK_SEC = 0
AND JN.SP_FREE < JN.SW_FREE))
WITH UR