| View previous topic :: View next topic |
| Author |
Message |
computer
Beginner
Joined: 12 Jun 2007
Posts: 64
Topics: 17
Location: Hyderabad
|
 Posted: Wed Sep 15, 2010 2:25 pm Post subject: Few Queries on COUNT and Functions |
 |
|
I need to understand the following queries output
Table Name: EMPID
| Code: |
EMPID SALARY
------- ----------
11111 10000
22222
33333 10000
44444 20000
55555
|
| Code: |
1. SELECT COUNT(*) FROM EMPID
2. SELECT COUNT(SALARY) FROM EMPID
3. SELECT AVG(SALARY) FROM EMPID
|
Thanks In Advance,
Computer |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Sep 15, 2010 4:15 pm Post subject: Re: Few Queries on COUNT and Functions |
|
|
Assuming Salary table is defined with NULL , the answer IMO are (untested though). Null values is NOT zero.
| Quote: |
1. SELECT COUNT(*) FROM EMPID |
5
| Quote: |
2. SELECT COUNT(SALARY) FROM EMPID
|
3
| Quote: |
3. SELECT AVG(SALARY) FROM EMPID |
(10000+10000+20000)/3 = 13333.33
Read this for a better understanding
http://www.mvsforums.com/helpboards/viewtopic.php?t=10624&highlight=null
Kolusu |
|
| Back to top |
|
|
computer
Beginner
Joined: 12 Jun 2007
Posts: 64
Topics: 17
Location: Hyderabad
|
| Posted: Thu Sep 16, 2010 3:13 pm Post subject: |
|
|
After reading about importance of NULL indicator, I was wondering in a db2 table what value is stored physically in a null field for a column.
And how does an application program knows by the use of a simple null-indicator. (Mean to ask whether low-values/high-values is stored in this field, so when a application program retrieves a value and indentifes as null value)
Thanks in advance,
Computer |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Sep 16, 2010 3:28 pm Post subject: |
|
|
| computer wrote: | After reading about importance of NULL indicator, I was wondering in a db2 table what value is stored physically in a null field for a column.
And how does an application program knows by the use of a simple null-indicator. (Mean to ask whether low-values/high-values is stored in this field, so when a application program retrieves a value and indentifes as null value)
Thanks in advance,
Computer |
Based on your interpretation, what you have learned is incorrect.
suggest you go back and read about what NULL actually is. in db2 there is no such thing as a NULL value. NULL and value are complete opposites.
if there is a value in a column, the colum is not NULL.
if the column IS NULL, there is no value associated with the column.
also, if a column is defined in the create table ddl as NOT NULL, it will
always have a value and can never be NULL.
the same reasoning is applied in SET theory. - look it up!
you can only have a NULL indicator for a NULLABLE Column.
the values returned to the application program are
-1 COLUMN IS NULL
+0 COLUMN IS NOT NULL and there is a value contained in the column variable
there are others -2 means truncation.
all these are identified in the manual.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Fri Sep 17, 2010 7:14 am Post subject: |
|
|
A null is not the same as 0 or blank or SPACES, LOW-Values, High-values (COBOL). Null means - no entry has been made for the column and it implies that the value is either unknown or not applicable.
Well, having read about NULL makes us to think - why in the heaven NULL is needed and when NULLs can be useful? Well, defining a column as NULL provides a place holder for data you might not yet know. E.g.: Let's assume there is a EMP DB2-Table, which keeps the track of new-employees. A new employee is hired and the details about him are inserted into the EMP table, BUT what should the employee termination date column be set to? If I wouldn
_________________
Regards,
Anuj
Last edited by Anuj Dhawan on Sun Sep 19, 2010 3:22 am; edited 1 time in total |
|
| Back to top |
|
|
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
| Posted: Fri Sep 17, 2010 10:02 am Post subject: |
|
|
Anuj,
| Quote: | | why in the heaven NULL is needed and when NULLs can be useful? Well, defining a column as NULL provides a place holder for data you might not yet know. |
Isn't it true that the need for NULL indicator was primarily a result of a need for better compression algorithm than the need for application programming?
Basically, In continuation to your example of EMP table, if a termination date is not known it could simply be defined with default and a default date value is inserted each time termination date is not provided. Later application program can read the default date and make decisions accordingly.
However, it is my understanding and I could be wrong that, Since there is no value present for nullable columns(NULL=Absence of Value), db2 compression algorithms can use null indicator and save space.
Thanks, |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Fri Sep 17, 2010 2:48 pm Post subject: |
|
|
| Quote: | Isn't it true that the need for NULL indicator was primarily a result of a need for better compression algorithm than the need for application programming?
|
Not that i'm aware of.
NULL was intended to provide for a "does not exist" condition.
NULL was a concept that was/is much more popular in theory than actual practice. . .
_________________
All the best,
di |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Sep 17, 2010 3:49 pm Post subject: |
|
|
| Sqlcode wrote: | | Isn't it true that the need for NULL indicator was primarily a result of a need for better compression algorithm than the need for application programming? |
Do have a source for that statement?
Kolusu |
|
| Back to top |
|
|
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
| Posted: Fri Sep 17, 2010 4:22 pm Post subject: |
|
|
kolusu,
Here are the links which provides some information on compression method(s).
http://www.ibm.com/developerworks/data/library/techarticle/dm-0605ahuja/index.html#null
| Quote: | NULL and default value compression
Available in DB2 for Linux, UNIX, and Windows since Version 8, with this type of compression no-disk storage is consumed for NULL values, zero length data in variable length columns and system default values. |
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007307.htm
| Quote: | | When VALUE COMPRESSION is used, NULLs and zero-length data that has been assigned to defined variable-length data types (VARCHAR, VARGRAPHICS, LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, and DBCLOB) will not be stored on disk. Only overhead values associated with these data types will take up disk space. |
It doesn't really give too much details though about the actual methods and that's why I asked this as a question rather than a statement.
My Assumption :- DB2 has to be smart enough to read just 1 byte or bit of null indicator to make a decision on whether to reserve disk space for entire field size or reserve no space at all and If I am not mistaken, it should do the same for VARCHAR Columns as well.
Thanks, |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
computer
Beginner
Joined: 12 Jun 2007
Posts: 64
Topics: 17
Location: Hyderabad
|
| Posted: Sat Sep 18, 2010 10:17 am Post subject: |
|
|
Sorry, if I am asking the same question once again. What I was asking, if a field has spaces then it stores X'40', for Low-Values X'00' and for High-Values X'FF'.
So, similarly if we say a column has got Null Value, then what value is stored physically.
Thanks,
Computer |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sat Sep 18, 2010 11:47 am Post subject: |
|
|
computer,
There is NO value stored for Null valued columns. DB2 just stores an indicator about if it has a value or not.
Kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Sat Sep 18, 2010 5:20 pm Post subject: |
|
|
| Quote: | | similarly if we say a column has got Null Value |
that is you problem. you/we/nobody that understands the meaning of NULL,
says Null Value. you simply say, the column is NULL.. period.
forget value.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Sun Sep 19, 2010 3:30 am Post subject: |
|
|
computer,
If you read through the entire thread again, you'll find this question | Quote: | | if a field has spaces then it stores X'40', for Low-Values X'00' and for High-Values X'FF' |
is already been answered in different ways:
| Dick Sir wrote: | you can only have a NULL indicator for a NULLABLE Column.
the values returned to the application program are
-1 COLUMN IS NULL
+0 COLUMN IS NOT NULL and there is a value contained in the column variable
there are others -2 means truncation.
all these are identified in the manual. |
| Quote: | | A null is not the same as 0 or blank or SPACES, LOW-Values, High-values (COBOL). Null means - no entry has been made for the column and it implies that the value is either unknown or not applicable. |
_________________
Regards,
Anuj |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
