Tables are the main repository of data within databases. Creating the tables and entering data to fill the tables will occur when you are creating an new database.
Prerequisites:
You must take the time to design and organize the tables that will hold your data.
Procedure:
After you determine how to organize your data into tables, the next step is to create those tables, by using the CREATE TABLE statement. The table
descriptions are stored in the system catalog of the database to which you are connected.
The CREATE TABLE statement gives the table a name, which is a qualified or unqualified identifier, and a definition for each of its columns. You can store each table in a separate table space, so that a table space contains only one table. If a table will be dropped and created often, it is more efficient to store it in a separate table space and then drop the table space instead of the table.
You can also store many tables within a single table space. In a partitioned
database environment, the table space chosen also defines the database partition group and the database partitions on which table data is stored.
The table does not contain any data at first. To add rows of data to it, use one of the following:
v The INSERT statement
v The LOAD or IMPORT commands
v The autoloader utility if working in a partitioned database environment Adding data to a table can be done without logging the change. The NOT LOGGED INITIALLY clause on the CREATE TABLE statement prevents logging the change to the table. Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE
operation in the same unit of work in which the table is created are not logged. Logging begins in subsequent units of work.
A table consists of one or more column definitions. A maximum of 500 columns can be defined for a table. Columns represent the attributes of an entity. The values in any column are all the same type of information.
Note: The maximum of 500 columns is true when using a 4 KB page size. The maximum is 1012 columns when using an 8 KB, 16 KB, or 32 KB page size.
A column definition includes a column name, data type, and any necessary null attribute, or default value (optionally chosen by the user).
The column name describes the information contained in the column and should be something that will be easily recognizable. It must be unique within the table; however, the same name can be used in other tables.
The data type of a column indicates the length of the values in it and the kind of data that is valid for it. The database manager uses character string,
numeric, date, time and large object data types. Graphic string data types are only available for database environments using multi-byte character sets. In addition, columns can be defined with user-defined distinct types.
The default attribute specification indicates what value is to be used if no value is provided. The default value can be specified, or a system-defined default value used. Default values may be specified for columns with, and without, the null attribute specification.
The null attribute specification indicates whether or not a column can contain null values.
To create a table using the Control Center:
1. Expand the object tree until you see the Tables folder.
2. Right-click the Tables folder, and select Create —> Tables Using Wizard from the pop-up menu.
3. Follow the steps in the wizard to complete your tasks.
To create a table using the command line, enter:
CREATE TABLE <NAME>
(<column_name> <data_type> <null_attribute>) IN <TABLE_SPACE_NAME)
The following is an example of a CREATE TABLE statement that creates the EMPLOYEE table in the RESOURCE table space. This table is defined in the sample database:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY, FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL WITH DEFAULT, LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3), PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL) IN RESOURCE
When creating a table, you can choose to have the columns of the table based on the attributes of a structured type. Such a table is called a “typed table”.
A typed table can be defined to inherit some of its columns from another typed table. Such a table is called a “subtable”, and the table from which it inherits is called its “supertable”. The combination of a typed table and all its subtables is called a “table hierarchy”. The topmost table in the table
hierarchy (the one with no supertable) is called the “root table” of the hierarchy.
To declare a global temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.
You can also create a table that is defined based on the result of a query. This type of table is called a materialized query table.
Related concepts:
v “Import Overview” in the Data Movement Utilities Guide and Reference v “Load Overview” in the Data Movement Utilities Guide and Reference
v “Moving Data Across Platforms - File Format Considerations” in the Data Movement Utilities Guide and Reference
v “User-defined type (UDT)” on page 130 Related tasks:
v “Creating a materialized query table” on page 137 Related reference:
v “CREATE TABLE statement” in the SQL Reference, Volume 2 v “INSERT statement” in the SQL Reference, Volume 2
v “DECLARE GLOBAL TEMPORARY TABLE statement” in the SQL Reference, Volume 2
v “IMPORT” in the Command Reference v “LOAD” in the Command Reference