Create a Table
The CREATE TABLE statement automatically creates a table with X100 storage structure.
The new table is owned by the user who creates it. Only the owner (or a user with the security privilege impersonating the owner) can alter or drop a table. When you create a table, it is placed in the default location designated for the database’s data files.
To create a table with X100 storage
Enter your CREATE TABLE statement at the terminal monitor prompt. For example:
CREATE TABLE lineitem (
l_orderkey INTEGER NOT NULL,
l_partkey INTEGER NOT NULL,
l_suppkey INTEGER NOT NULL,
l_linenumber INTEGER NOT NULL,
l_quantity DECIMAL(2,0) NOT NULL,
l_extendedprice DECIMAL(8,2) NOT NULL,
l_discount DECIMAL(2,2) NOT NULL,
l_tax DECIMAL(2,2) NOT NULL,
l_returnflag CHAR(1) NOT NULL,
l_linestatus CHAR(1) NOT NULL,
l_shipdate ANSIDATE NOT NULL,
l_commitdate ANSIDATE NOT NULL,
l_receiptdate ANSIDATE NOT NULL,
l_shipinstruct CHAR(25) NOT NULL,
l_shipmode CHAR(10) NOT NULL,
l_comment VARCHAR(44) NOT NULL
);\g
Note: If a column always contains a value then define the column as NOT NULL.
If your table has a large number of columns and/or relatively few rows, we recommend the X100_ROW storage type, which saves disk space and increases performance.
To create a table with X100_ROW storage
Either type the SET RESULT_STRUCTURE X100_ROW statement before the CREATE TABLE statement or use the WITH STRUCTURE=X100_ROW on the CREATE TABLE statement:
CREATE TABLE ...
WITH STRUCTURE = X100_ROW
Last modified date: 06/28/2024