Administration Guide

Provide Column Definitions for All Tables

To define a column in a relational table:

1. Choose a name for the column

   Each column in a table must have a name that is unique within the table. Selecting column names is described in detail in Appendix E. "Naming Rules".

2. State what kind of data is valid for the column

   The data type and length specify maximum length and the type of data that is 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, see the SQL Reference manual.

   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 large objects are implemented using the following data types:

   • A binary large object (BLOB) string. Examples of BLOBs are photographs of employees, voice, and video.

   • A character large object (CLOB) string, where the sequence of characters can be either single- or multi-byte characters, or a combination of both. An example of a CLOB is a resume of an employee.

   • A double-byte character for large object (DBCLOB) string, where the sequence of characters are double-byte characters. An example of a DBCLOB is a Japanese resume.

   For a better understanding of large object support, refer to the SQL Reference manual.

   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 existing types that share similar characteristics, but are considered to be separate and incompatible types.

   A structured type is a user-defined type that has a structure that 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) may 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 functions of SQL and can be used wherever a built-in function can be used. There are two types of UDFs:

   • An external function, which is written in a programming language
   • A sourced function, which will be used to invoke other UDFs

   For example, two numeric data types are European Shoe Size and American Shoe Size. Both types share the same representations of shoe size, but they are incompatible because the measurement base is different and cannot be compared. When this occurs, a user-defined function can be invoked to convert from one shoe size to another.

   During your design, you may have to consider functions for your UDTs. For a better understanding of user-defined types, structured types, reference types, and user-defined functions, refer to the SQL Reference manual.

3. State which columns might need default values

   Some columns cannot have meaningful values in all rows because:

   • A value of the column is not applicable to the row.

     For example, a column containing an employee's middle initial is not applicable to an employee who has no middle initial.

   • A value is applicable, but the value is not known at this time.

     As an 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 inapplicable) 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 manual.

[ DB2 List of Books | Search the DB2 Books ]