{"id":501,"date":"2014-03-14T07:52:43","date_gmt":"2014-03-14T07:52:43","guid":{"rendered":"http:\/\/visit.markjacobsen.net\/?p=501"},"modified":"2014-03-13T02:56:00","modified_gmt":"2014-03-13T02:56:00","slug":"find-nullable-columns-in-db2-with-no-null-values","status":"publish","type":"post","link":"https:\/\/markjacobsen.net\/2014\/03\/find-nullable-columns-in-db2-with-no-null-values\/","title":{"rendered":"Find Nullable Columns in DB2 with no Null Values"},"content":{"rendered":"<p>I&#8217;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. <\/p>\n<pre><code>SELECT RTRIM(TAB.TABSCHEMA)||'.'||TAB.TABNAME AS TABLE_OBJECT_NAME \r\n, COLS.COLNAME AS COLNAME , COLS.TYPENAME AS \r\nTYPE ,RTRIM(CHAR(COLS.LENGTH))|| \r\nCASE COLS.SCALE \r\nWHEN 0 \r\nTHEN ' ' \r\nELSE ','||RTRIM(CHAR(COLS.SCALE)) \r\nEND || \r\nCASE COLS.TYPENAME \r\nWHEN 'VARCHAR' \r\nTHEN ' AVG:'||RTRIM(CHAR(COLS.AVGCOLLEN)) \r\nELSE ' ' \r\nEND AS LENGTH \r\n,TAB.CARD as TAB_CARD \r\n,COLS.COLCARD AS COL_CARD \r\n,CASE \r\nWHEN NULLS ='Y' \r\nTHEN 'NULL ('||RTRIM(CHAR(NUMNULLS))||')' \r\nWHEN NULLS = 'N' THEN 'NOT NULL' \r\nELSE '??' END as NUM_NULLS \r\n,RTRIM(COLS.TABSCHEMA)||'.'||COLS.TABNAME||'2' AS SORTCOL,COLS.COLNO AS SORTCOL2 \r\nFROM SYSCAT.COLUMNS COLS \r\nINNER JOIN SYSCAT.TABLES TAB ON (COLS.TABSCHEMA = TAB.TABSCHEMA AND COLS.TABNAME = TAB.TABNAME) \r\nWHERE RTRIM(COLS.TABSCHEMA) = 'XX' \r\nAND ( \r\n(NULLS = 'Y' AND NUMNULLS=0 AND TAB.CARD > 0) \r\n--OR (COLS.TYPENAME = 'VARCHAR' AND COLS.AVGCOLLEN >= COLS.LENGTH-2) \r\n) \r\nORDER BY SORTCOL,SORTCOL2 \r\nFOR READ ONLY WITH UR;<\/code><\/pre>\n<i>Please remember to <a href=\"https:\/\/markjacobsen.net\/newsletter\" target=\"_blank\">subscribe to the newsletter<\/a> to stay up to date!<\/i><br\/>\n<br\/>\n<i>You or someone you know looking to buy or sell?<\/i>\n<a href=\"https:\/\/freedomre.com\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" src=\"https:\/\/markjacobsen.net\/wp-content\/uploads\/2019\/09\/freedomre-glre-small.png\" border=\"0\" \/><\/a><br\/>\n<i><small>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...<\/small><\/i>","protected":false},"excerpt":{"rendered":"<p>I&#8217;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 &hellip; <a href=\"https:\/\/markjacobsen.net\/2014\/03\/find-nullable-columns-in-db2-with-no-null-values\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Find Nullable Columns in DB2 with no Null Values<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"footnotes":"","_jetpack_memberships_contains_paid_content":false,"jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[3],"tags":[22],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2UXeD-85","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/posts\/501"}],"collection":[{"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/comments?post=501"}],"version-history":[{"count":1,"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/posts\/501\/revisions"}],"predecessor-version":[{"id":502,"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/posts\/501\/revisions\/502"}],"wp:attachment":[{"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/media?parent=501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/categories?post=501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/markjacobsen.net\/wp-json\/wp\/v2\/tags?post=501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}