DB2 NULL Statement
NULL data type is used to represents that the value is absent in the column.
All data types accept the NULL value.
If the column declaration not having NULL indicator or only NULL, then the column will allows inserting NULL value.
CREATE TABLE Table name (Column1 data-type, Column2 data-type, Column3 data-type [NULL], . . Column-n data-type Primary key/foreign key column1,column2,….column-n, [LIKE reference table name] ON database-name.tablespace-name)
If the column declaration had NOT NULL, then the column will not allow inserting the NULL value in it.
CREATE TABLE Table name (Column1 data-type NOT NULL, Column2 data- type, Column3 data- type NOT NULL, . . Column-n data-type Primary key/foreign key column1,column2….column-n, [LIKE reference table name] ON database-name.tablespace-name)
How the INSERT/UPDATE NULL to the table in the Program:
Inserting/updating of NULL to the database tables through the program can be done in similar way.
Steps to update/insert the NULL to table are:
o Need to move ‘-1’ to NULL indicator field of the host variable.
o The NULL Indicator will be automatically generated while creating the DCLGEN for all the columns which are Nullable columns.
o The NULL indicator will have same name as the columns host variable by adding –NI at the end and as S9(04) COMP.
o Execute the INSERT/UPDATE like below
EXEC SQL INSERT INTO Table-name (Column-1, Column-2, … , Column-n) VALUES (:Host-variable1 :Host-variable1-NI, :Host-variable2 :Host-variable2-NI, …,: Host-variablen) END-EXEC.
EXEC SQL UPDATE Table-name SET Column-1 = :Host-variable1 :Host-variable1-NI, Column-2 = :Host-variable2 :Host-variable2-NI, … , Column-n = :Host-variablen WHERE Condition END-EXEC.
DB2 Left Outer Join DB2 Error Codes