Find Nullable Columns in DB2 with no Null Values

I’m not a fan of NULL columns. So check out the query below if you want to find DB2 fields that are defined as nullable, but which contain no null values. You can also modify the query to show you how many records contain null values. Make sure to set the COLS.TABSCHEMA in the WHERE predicate.

SELECT RTRIM(TAB.TABSCHEMA)||'.'||TAB.TABNAME AS TABLE_OBJECT_NAME 
, COLS.COLNAME AS COLNAME , COLS.TYPENAME AS 
TYPE ,RTRIM(CHAR(COLS.LENGTH))|| 
CASE COLS.SCALE 
WHEN 0 
THEN ' ' 
ELSE ','||RTRIM(CHAR(COLS.SCALE)) 
END || 
CASE COLS.TYPENAME 
WHEN 'VARCHAR' 
THEN ' AVG:'||RTRIM(CHAR(COLS.AVGCOLLEN)) 
ELSE ' ' 
END AS LENGTH 
,TAB.CARD as TAB_CARD 
,COLS.COLCARD AS COL_CARD 
,CASE 
WHEN NULLS ='Y' 
THEN 'NULL ('||RTRIM(CHAR(NUMNULLS))||')' 
WHEN NULLS = 'N' THEN 'NOT NULL' 
ELSE '??' END as NUM_NULLS 
,RTRIM(COLS.TABSCHEMA)||'.'||COLS.TABNAME||'2' AS SORTCOL,COLS.COLNO AS SORTCOL2 
FROM SYSCAT.COLUMNS COLS 
INNER JOIN SYSCAT.TABLES TAB ON (COLS.TABSCHEMA = TAB.TABSCHEMA AND COLS.TABNAME = TAB.TABNAME) 
WHERE RTRIM(COLS.TABSCHEMA) = 'XX' 
AND ( 
(NULLS = 'Y' AND NUMNULLS=0 AND TAB.CARD > 0) 
--OR (COLS.TYPENAME = 'VARCHAR' AND COLS.AVGCOLLEN >= COLS.LENGTH-2) 
) 
ORDER BY SORTCOL,SORTCOL2 
FOR READ ONLY WITH UR;
Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply