DB2 NULL Statement

DB2 Left Outer Join DB2 Error Codes

Advertisements


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.

Syntax:

						
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.

Syntax:

						
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

Syntax:

						
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.

Update Syntax:

						
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

Advertisements