Inserting NULL

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
jaydeeppal
Member
Posts: 4
Joined: Thu Feb 16, 2006 12:24 pm

Inserting NULL

Post by jaydeeppal » Fri Apr 21, 2006 10:39 am

Hi all!
Can anybody tell me the various methods of inserting a NULL value to a particular col of a table.
Also clarify, if I move '-1' to the Null indicator of the host variable corresponding to that col, and then insert it, will it work?

Jaydeep.

srinivas
Member
Posts: 9
Joined: Wed Feb 08, 2006 2:58 pm
Location: Bangalore

NULL value

Post by srinivas » Tue May 30, 2006 3:07 pm

Hi,

Yes u r right, by moving -1 value to null-indicator we can insert null values.

ovreddy
Member
Posts: 14
Joined: Sat Oct 07, 2006 4:13 pm
Location: Chennai
Contact:

Post by ovreddy » Mon Oct 16, 2006 4:46 pm

Hi Srinivas,

In SPUFI like tool we can use directly NULL to insert null values..

Ex: INSERT INTO TABLE1(COL1,COL2) VALUES (102,NULL);

This will insert a null value into second column of the table.

Also we can use same type of syntax for UPDATE.

Thanks,
Reddy

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Oct 16, 2006 8:09 pm

careful, you speak of NULL as a value. It is not. Null is a state.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

ovreddy
Member
Posts: 14
Joined: Sat Oct 07, 2006 4:13 pm
Location: Chennai
Contact:

Post by ovreddy » Tue Oct 17, 2006 12:28 pm

Hi,

I have executed the following statement and I didn't see any error in execution... If you are talking about wording then I will agree with you...

CREATE GLOBAL TEMPORARY TABLE TEST(EMPNO INTEGER,ENAME CHAR(10));
---------+---------+---------+---------+---------+---------+-----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
INSERT INTO TEST VALUES(10,'RAJU');
---------+---------+---------+---------+---------+---------+-----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
INSERT INTO TEST VALUES(20,NULL);
---------+---------+---------+---------+---------+---------+-----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
SELECT * FROM TEST WHERE ENAME IS NULL;
---------+---------+---------+---------+---------+---------+-----
EMPNO ENAME
---------+---------+---------+---------+---------+---------+-----
20 ----------
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Thanks,
Reddy

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Tue Oct 17, 2006 6:41 pm

Reddy, you are correct: it is the wording. NULL is not inserted; the NULL Indicator
(an additional column added to the table when a data column has been declared as NULLABLE in the CREATE Table SQL)

for a data column of the DB2 table is SET.

When the NULL indicator for a column is SET to -1 (NULL) then nothing is associated with the data column; DB2 does not return anything for the data column nor does DB2 modify the data column. As you know, you cannot modify the data column unless the NULL Indicator for that column is set to NOT NULL (0 or <> -1).
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others