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 D. "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 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 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 ]