|
View previous topic :: View next topic
|
| Author |
Message |
maverick05
New User
.jpg)
Joined: 14 Apr 2005
Posts: 54
Location: Earth
|
|
|
|
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 |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| 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 |
|
|
rag swain
New User
Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA
|
|
|
|
OP,
What do you mean by 'manually' here? |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
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 |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
| 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| 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 |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
you are welcome  |
|
| Back to top |
|
|
|
|
