This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy.
Popular Course in this category. Course Price View Course. Free Data Science Course. Login details for this Free course will be emailed to you. Null means no entry has been made for the column and it implies that the value is either unknown or not applicable. Because DB2 supports null you can distinguish between a deliberate entry of 0 for numerical columns or a blank for character columns and an unknown or inapplicable entry NULL for both numerical and character columns.
Null indicates that the user did not explicitly make an entry or has explicitly entered NULL for the column. For example, a null value in the Price column of the ITEM table in some database does not mean that the item is being given away for free; instead it means that the price is not known or has not yet been set.
When are nulls useful? Well, defining a column as NULL provides a placeholder for data you might not yet know. For example, when a new employee is hired and is inserted into the EMP table, what should the employee termination date column be set to? Instead, null can be used to specify that the termination date is currently unknown. Consider three potential entity occurrences: a man with black hair, a woman with unknown hair color, and a bald man. The woman with the unknown hair color and the bald man both could be assigned as null, but for different reasons.
How could you handle this without using nulls? All occurrences of a column assigned as a DATE data type are valid dates. DB2 does not differentiate between nulls that signify unknown data and those that signify inapplicable data. This distinction must be made by the program logic of each application. By properly modeling and normalizing your data structures you can usually eliminate the need to use nulls to indicate that a column is inapplicable for a specific row.
For example, consider the following table:. Well, here we have three columns that are set to null or not based on other values in the table. We can design our way out of this problem by creating a separate table for employees and contractors. If additional columns were needed for full-time employees that did not apply part-time employees we might even split the employee table into two: one for full-time and another for part-time. After doing so, there is no more need to use null for inapplicable data.
An indicator variable is defined to DB2 for each column that can accept nulls. The null indicator is used by DB2 to track whether its associated column is null or not. A positive value or a value of 0 means the column is not null and any actual value stored in the column is valid.
If a CHAR column is truncated on retrieval because the host variable is not large enough, the indicator value will contain the original length of the truncated column. A negative value indicates that the column is set to null. If the value is -2 then the column was set to null as the result of a data conversion error. 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. Meaning: -1 : the field is null 0 : the field is not null -2 : the field value is truncated. Every column defined to a DB2 table must be designated as either allowing or disallowing nulls.
Null is the default if nothing is specified after the column name. The purposes of the indicator variable are to: 1 Specify the null value.
0コメント