Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Db2 Query on Null value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
maverick05

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Wed May 28, 2008 1:35 pm    Post subject: Db2 Query on Null value
Reply with quote

Hi,

Can someone please clarify the followin queries?

1. How do i manually insert Null in a nullable column in DB2 table?
A Nullable comun doesn't have a Hex value.I can't put spaces since it takes 4 0 as Hex value.For remaining Nullable columns it displays as '-' with no hex values.But again - has got 6 0 as it hex value

2.I have defined a column as not nullable with default value as 'Y'.
In my cobol Db2 pgm,if i dont either insert a value in thsi column or never use this column in SQL insert statement,wont the table will be inserted with the value 'Y' automatically ?
Does it insert Space instead?
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Wed May 28, 2008 2:14 pm    Post subject:
Reply with quote

1. Please omit the values for nullable column while inserting or give NULL as the value. Nulls are displayed as ----- while selecting. Why are you inserting spaces to nullable columns when those columns don't need any values.

2.Default value would be inserted automatically.
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed May 28, 2008 4:30 pm    Post subject:
Reply with quote

DB2 shows and not stores the NULLs as "-----" in the tables.
DB2 uses extra memory to identify a field to be NULL or NOT NULL. It uses hexadecimal values to identify it.

HEX'00' means NOT NULL
HEX'FF' means NULL
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed May 28, 2008 5:04 pm    Post subject:
Reply with quote

Quote:

HEX'00' means NOT NULL
HEX'FF' means NULL


Is there a representation of null in DB2 ? my understanding was the null indicator should be used to check whether the col is containing null or not ?
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Wed May 28, 2008 5:21 pm    Post subject: Reply to: Db2 Query on Null value
Reply with quote

OP,

What do you mean by 'manually' here?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed May 28, 2008 7:41 pm    Post subject:
Reply with quote

Both of the following would work to manually insert NULL into a column. If a column is defined as nullable, then the default value is assumed to be NULL. NULL is also a keyword that can be used in an insert statement. If you are doing it in COBOL then you need a NULL indicator set to -1 (verify this) and you need to specify the NULL indicator field in your COBOL SQL statement.

INSERT INTO TABLE(COLA,COLB) VALUES('KEYVALUE',NULL);
INSERT INTO TABLE(COLA) VALUES('KEYVALUE');
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Thu May 29, 2008 9:33 am    Post subject:
Reply with quote

Quote:
ashimer wrote : Is there a representation of null in DB2 ? my understanding was the null indicator should be used to check whether the col is containing null or not ?

As far as NULL INDICATORS are concerned, they belong to the programming language COBOL and not to DB2. The -1 value that we use to recognize the NULLs in COBOL is a way to identify the NULL data in COBOL. As COBOL doesn't support it.
The HEX values that i wrote were regarding to the way that DB2 uses to recognize the NULL values.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 29, 2008 2:46 pm    Post subject:
Reply with quote

the_gautam wrote:
[wrote :
As far as NULL INDICATORS are concerned, they belong to the programming language COBOL and not to DB2. The -1 value that we use to recognize the NULLs in COBOL is a way to identify the NULL data in COBOL.



I disagree... null indicators are hidden cols within Db2 ...
pls read this article ...

http://www.db2portal.com/2006/04/using-nulls-in-db2.html
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Thu May 29, 2008 4:09 pm    Post subject:
Reply with quote

From the link that you sent me :
Quote:
Every nullable column requires one additional byte of storage for the null indicator. So, a CHAR(10) column that is nullable will require 11 bytes of storage per row : 10 for the data and 1 for the null indicator. This is the case regardless of whether the column is set to null or not.


I was talking about this extra 1 byte.
After going through your link, i came to know that the Null Indicators are part of DB2 tables. Thanks for the update...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 29, 2008 4:26 pm    Post subject:
Reply with quote

you are welcome icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us