The following Datacom SQL request resulted in this error:
SQLCODE = -4, SQLSTATE=22003
MESSAGE = OVERFLOW: TYPE<DECIMAL(31,3)> ACTION<DIVIDE>
SELECT
AREA_NAME,
DIGITS(DBID),
DATASET_NAME,
MAX_USED_BLOCKS,
TOTAL_BLOCKS,
TOTAL_TRACKS,
DECIMAL(MAX_USED_BLOCKS,31,2) / DECIMAL(TOTAL_BLOCKS,15,0)
AS PCT_FULL,
DECIMAL((
(DECIMAL(MAX_USED_BLOCKS,31,0) * 1) / DECIMAL(TOTAL_BLOCKS,15,0)
* (TOTAL_TRACKS * 1.1) + 1),15,0) AS MIN_TRKS_NEEDED
FROM SYSADM.DIR_DATASET
;
z/OS
Datacom SQL
After detailed analysis of the data, it was determined that some records had TOTAL_BLOCKS value of 0 which were the cause of the DIVIDE BY ZERO overflow.
Use the CASE expression to first evaluate the values in order to avoid potential divide by zero:
SELECT
CASE
WHEN DBID > 999 THEN
AREA_NAME || SUBSTR(DIGITS(DBID),2,4)
ELSE
AREA_NAME || SUBSTR(DIGITS(DBID),3,3)
END AS AREA,
DATASET_NAME,
MAX_USED_BLOCKS,
TOTAL_BLOCKS,
CASE
WHEN TOTAL_BLOCKS > 0 THEN
DECIMAL(DECIMAL(MAX_USED_BLOCKS,13,2) /
DECIMAL(TOTAL_BLOCKS,13,2) * 100
,4,1)
ELSE 0
END AS "PCT_FULL",
CASE
WHEN TOTAL_BLOCKS > 0 THEN
DECIMAL(((DECIMAL(MAX_USED_BLOCKS,13,2) /
DECIMAL(TOTAL_BLOCKS,13,2)) *
DECIMAL(TOTAL_TRACKS * 1.1,13,2)) + 1
,4,1)
ELSE 0
END AS "MIN_TRKS_NEEDED"
FROM SYSADM.DIR_DATASET
;
For documentation, see CASE Expressions .