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.
Please remember to subscribe to the newsletter to stay up to date!
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;
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...