To define a column in a relational table:
Each column in a table must have a name that is unique for that table. Selecting column names is described in detail in Appendix A, Naming Rules.
The data type and length specify the type of data and the maximum length that are valid for the column. Data types may be chosen from those provided by the database manager or you may choose to create your own user-defined types. For information about the data types provided by DB2, and about user-defined types, refer to the SQL Reference.
Examples of data type categories are: numeric, character string, double-byte (or graphic) character string, date-time, and binary string.
Large object (LOB) data types support multi-media objects such as documents, video, image and voice. These objects are implemented using the following data types:
For a better understanding of large object support, refer to the SQL Reference.
A user-defined type (UDT), is a type that is derived from an existing type. You may need to define types that are derived from and share characteristics with existing types, but that are nevertheless considered to be separate and incompatible.
A structured type is a user-defined type whose structure is defined in the database. It contains a sequence of named attributes, each of which has a data type. A structured type may be defined as a subtype of another structured type, called its supertype. A subtype inherits all the attributes of its supertype and may have additional attributes defined. The set of structured types that are related to a common supertype is called a type hierarchy, and the supertype that does not have any supertype is called the root type of the type hierarchy.
A structured type may be used as the type of a table or a view. The names and data types of the attributes of the structured types, together with the object identifier, become the names and data types of the columns of this typed table or typed view. Rows of the typed table or typed view can be thought of as a representation of instances of the structured type.
A structured type cannot be used as the data type of a column of a table or a view. There is also no support for retrieving a whole structured type instance into a host variable in an application program.
A reference type is a companion type to the structured type. Similar to a distinct type, a reference type is a scalar type that shares a common representation with one of the built-in data types. This same representation is shared for all types in the type hierarchy. The reference type representation is defined when the root type of a type hierarchy is created. When using a reference type, a structured type is specified as a parameter of the type. This parameter is called the target type of the reference.
The target of a reference is always a row in a typed table or view. When a reference type is used, it may have a scope defined. The scope identifies a table (called the target table) or view (called the target view) that contains the target row of a reference value. The target table or view must have the same type as the target type of the reference type. An instance of a scoped reference type uniquely identifies a row in a typed table or typed view, called its target row.
A user-defined function (UDF) can be used for a number of reasons, including invoking routines that allow comparison or conversion between user-defined types. UDFs extend and add to the support provided by built-in SQL functions, and can be used wherever a built-in function can be used. There are two types of UDFs:
For example, two numeric data types are European Shoe Size and American Shoe Size. Both types represent shoe size, but they are incompatible, because the measurement base is different and cannot be compared. A user-defined function can be invoked to convert one shoe size to another.
For a better understanding of user-defined types, structured types, reference types, and user-defined functions, refer to the SQL Reference.
Some columns cannot have meaningful values in all rows because:
For example, a column containing an employee's middle initial is not applicable to an employee who has no middle initial.
For example, the MGRNO column might not contain a valid manager number because the previous manager of the department has been transferred, and a new manager has not been appointed yet.
In both situations, you can choose between allowing a NULL value (a special value indicating that the column value is unknown or not applicable), or allowing a non-NULL default value to be assigned by the database manager or by the application.
NULL values and default values are described in detail in the SQL Reference.