{"id":707,"date":"2015-11-30T15:33:46","date_gmt":"2015-11-30T10:03:46","guid":{"rendered":"http:\/\/ibm-mainframes.com\/blog\/?p=707"},"modified":"2023-01-20T13:45:54","modified_gmt":"2023-01-20T08:15:54","slug":"null-in-db2-and-cobol","status":"publish","type":"post","link":"https:\/\/zmainframes.com\/zlog\/null-in-db2-and-cobol\/","title":{"rendered":"NULL in DB2 and COBOL."},"content":{"rendered":"<h1 style=\"text-align: center;\"><strong>NULL In DB2 and COBOL<\/strong><\/h1>\n<p>SQL, unlike COBOL, supports variables that can contain null (values &#8211; the use of word &#8220;value&#8221; along with NULL is not usually encouraged as NULL itself means the absence of value and it in itself can not be termed as &#8220;value&#8221;, well). Actually, a null &#8220;value&#8221; means that no entry has been made and usually implies that the value is either unknown or undefined, at the moment. For example, a null value in a joining date column does not mean that the date is undetermined, it means that the date is not known or has not been set yet.<\/p>\n<p>For a column in a DB2 table for which null is allowed, special handling is required in a COBOL program. The program should define a null indicator variable for each column for which nulls are allowed. This null indicator variable should be defined as a binary halfword \u2013 PIC S9(4) COMP (why? &#8211; well, at the moment treat it as a rule) \u2013 and should be listed in the FETCH as an additional host variable immediately after the regular host variable. If the value of this indicator variable is less than zero then the attribute within the table was null.<\/p>\n<p>To answer your question &#8211; let&#8217;s begin with a CREATE table called MYTABLE which contains three fields: COL_A (which is NOT NULL), COL_B (nulls allowed) and COL_C (nulls allowed). We then use INSERT statements to populate the table:<\/p>\n<pre class=\"theme:cisco-router lang:default decode:true\">CREATE TABLE MYTABLE\n( COL_A CHAR(2) NOT NULL,\nCOL_B CHAR(2) ,\nCOL_C CHAR(2) ) ;<\/pre>\n<pre class=\"theme:cisco-router lang:default decode:true\">INSERT INTO MYTABLE VALUES ('A', 'B', 'C');\nINSERT INTO MYTABLE VALUES ('D', 'E', NULL);\nINSERT INTO MYTABLE VALUES ('F', NULL, G');\nINSERT INTO MYTABLE VALUES ('H', NULL, NULL);\nINSERT INTO MYTABLE VALUES ('I', 'J', 'K');<\/pre>\n<p>Now below, the indicator variables are coded within the WORKING-STORAGE SECTION as follows (no such variable is coded for COL_A as it was defined as NOT NULL):<\/p>\n<pre class=\"theme:cisco-router lang:default decode:true \">05 COL_B-NULL-INDICATOR PIC S9(4) COMP.\n05 COL_C-NULL-INDICATOR PIC S9(4) COMP.<\/pre>\n<p>You can see that the null indicator variable is listed in the FETCH as an additional host variable immediately after the regular host variable as follows:<\/p>\n<pre class=\"theme:cisco-router lang:default decode:true \">EXEC SQL\n\nFETCH MYTABLE-CURSOR INTO\n:MY-COL_A ,\n:MY-COL_B :COL_B-NULL-INDICATOR,\n:MY-COL_C :COL_C-NULL-INDICATOR\n\nEND-EXEC.<\/pre>\n<p>If the value retrieved from the table is null, the null indicator variable will contain a value less than zero, so we check the null indicator as follows:<\/p>\n<pre class=\"theme:cisco-router lang:default decode:true \">IF COL_B-NULL-INDICATOR &lt; 0\nMOVE ALL '*' TO DL-COL_B\nELSE\nMOVE MY-COL_B TO DL-COL_B\nEND-IF<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>NULL In DB2 and COBOL SQL, unlike COBOL, supports variables that can contain null (values &#8211; the use of word [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,30],"tags":[],"class_list":["post-707","post","type-post","status-publish","format-standard","hentry","category-cobol","category-ibm-db2"],"amp_enabled":true,"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"Anuj Dhawan","author_link":"https:\/\/zmainframes.com\/zlog\/author\/anuj-dhawan\/"},"rttpg_comment":843,"rttpg_category":"<a href=\"https:\/\/zmainframes.com\/zlog\/mainframes\/cobol\/\" rel=\"category tag\">Cobol<\/a> <a href=\"https:\/\/zmainframes.com\/zlog\/mainframes\/database\/ibm-db2\/\" rel=\"category tag\">IBM DB2<\/a>","rttpg_excerpt":"NULL In DB2 and COBOL SQL, unlike COBOL, supports variables that can contain null (values &#8211; the use of word [&hellip;]","_links":{"self":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts\/707","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/comments?post=707"}],"version-history":[{"count":3,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts\/707\/revisions"}],"predecessor-version":[{"id":1788,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts\/707\/revisions\/1788"}],"wp:attachment":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/media?parent=707"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/categories?post=707"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/tags?post=707"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}