Data in DB2 is contained in tables. A table consists of one or more columns of various data types. The data is structured into rows, each with the structure specified for the table.
Tables are defined using the
DB2 supports the following types of tables.
- The system catalog tables.
- Base tables, which contain persistent data and are directly queryable.
- Temporal tables:
- System period temporal tables: to keep a history of all values of all rows.
- Business period temporal tables: to allow application-defined periods of effectivity for each row.
- Bi-temporal tables: to incorporate both system period and business period features.
- Typed tables: (Note: Linux, UNIX, and Windows only) contain data as defined by a user-defined structured data type.
- Materialized query tables: to contain data derived from massive data, directly queryable.
- Temporary tables:
- Created: the definition is cataloged and shared with all users, but each session can easily obtain its own instance, with no data shared with other sessions.
- Declared: the definition is not shared nor is the data.
- Clone tables: (Note: z/OS only) to allow maintenance of high availability, high performance tables, not directly queryable.
- Columnar data tables: (Note: z/OS only), not directly queryable.
- Auxiliary tables: for various LOB column data.
- XML tables: for XML column data.
Each DB2 server (referred to on Linux, UNIX, and Windows as a "database", and on zOS as a "subsystem") has a set of tables, called the system catalog tables, which hold information about the objects within the server.
Note: The schema for these catalog tables is SYSCAT on Linux, UNIX, and Windows, and SYSIBM on z/OS where the tables are also prefixed by 'SYS', such as SYSIBM.SYSTABLES.
You can examine the catalog using
statements, just like any other table. The catalog tables are
automatically maintained as a result of data definition statements
(DDL), such as
and via other operations, such as
CREATE TABLE SQL statement to define
a table. The statement shown in Listing 1 creates a simple table named
BOOKS that contains three columns:
Listing 1. Simple table with three columns
CREATE TABLE BOOKS ( BOOKID INTEGER, BOOKNAME VARCHAR(100), ISBN CHAR(10) );
You can also use the
CREATE TABLE SQL
statement to create a table that is like another table or view:
Listing 2. Using CREATE TABLE
CREATE TABLE MYBOOKS LIKE BOOKS;
This statement creates a table with the same columns as the original table or view. The columns of the new table have the same names, data types, and nullability attributes as the columns in the old one. You can also specify clauses that copy other attributes, like column defaults and identify attributes.
There are many options available for the
CREATE TABLE statement (they are presented
in the following sections as new concepts are introduced). The details
CREATE TABLE SQL statement can be
found in the SQL Reference (see the Resources
Once you've created a table, there are several ways to populate it with
INSERT statement lets you insert
a row or several rows of data into the table. The LOAD utility,
intended for loading large volumes of data, places rows directly onto
data pages, maintains indexes in bulk rather than row-by-row, and has
the capability to avoid logging. Note: Linux, UNIX, and Windows
provides an IMPORT utility to insert rows from a file of data. It uses
INSERT statements, and is designed for
loading small amounts of data.
Tables are stored in tablespaces. Tablespaces have physical space allocated to them.
When you create a table, you can let DB2 place the table in a default tablespace, or specify the tablespace in which you'd like the table to reside, as shown in Listing 3.
Listing 3. Default or specify tablespace
CREATE TABLE BOOKS ( BOOKID INTEGER, BOOKNAME VARCHAR(100), ISBN CHAR(10) ) IN BOOKINFO;
Although tablespaces are not covered in detail here, it is important to understand that the appropriate definition of tablespaces can have significant effects upon the performance and maintainability of the database. For more information on tablespaces, check out the second tutorial in this series.
ALTER TABLE SQL statement to change
characteristics of a table. For instance, you can add or drop:
- A column.
- A primary key.
- One or more unique or referential constraints.
- One or more check constraints.
The statement shown in Listing 4 adds a column called BOOKTYPE to the BOOKS table.
Listing 4. Add BOOKTYPE column
ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1)
You can also change the following characteristics of specific columns in a table.
- The identity attributes of a column.
- The length of a string column.
- The data type of a column.
- The null ability of a column.
- The constraint of a column.
There are the following restrictions on altering columns.
- When altering the data type of a column, the new data type must be compatible with the existing data type. For example, you can convert CHAR columns to VARCHAR columns, but cannot convert them to GRAPHIC or numeric columns. Numeric columns can be converted to any other numeric data type as long as the new data type is large enough to hold the values. For example, convert an INTEGER column to BIGINT, but a DECIMAL(10,2) column cannot be converted to SMALLINT.
- When altering a string column, you cannot decrease the length.
The statement shown in Listing 5 changes the data type of column BOOKNAME from VARCHAR(100) to VARCHAR(200), and also changes the null ability of the ISBN column to NOT NULL WITH DEFAULT 'UNKNOWN':
Listing 5. Change data type of column
ALTER TABLE BOOKS ALTER BOOKNAME SET DATA TYPE VARCHAR(200) ALTER ISBN SET DEFAULT 'UNKNOWN';
Certain characteristics of a table cannot be changed. For example, you cannot change the tablespace in which the table resides, the order of columns, or change the data type of a column to an incompatible data type (see the section on Data types). To change characteristics such as these, save the table data, drop the table, and then recreate it.
DROP TABLE statement removes a table
from the database, deleting the data and the table definition. If
there are indexes or constraints defined on the table, they are
dropped as well.
DROP TABLE statement, as shown in
Listing 6, deletes the BOOKS table and its data:
Listing 6. Delete table and data
DROP TABLE BOOKS;
The columns of a table are specified in the
statement by a column name and data type. The columns can have
additional clauses specified that restrict the data in the column.
By default, a column allows null values. If you do not want to allow null values, then specify the NOT NULL clause for the column. Specify a default value using the WITH DEFAULT clause and a default value. Note that if a column allows nulls, that column will require one extra byte to hold the null indicator. Any null value is not equal to, greater than, or less than to any actual value, nor is it equal to any other null value other than the special value “NULL” which can be used in SQL.
CREATE TABLE statement, as shown in
Listing 7, creates a table called BOOKS, where the BOOKID column does
not allow null values and the default value for BOOKNAME is the string
with value TBD.
Listing 7. Using CREATE TABLE statement
CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL, BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', ISBN CHAR(10) );
In the BOOKS table, the BOOKID is a unique number assigned to each book. Rather than have the application generate the identifier, you can specify that DB2 is to generate a BOOKID using the GENERATED ALWAYS AS IDENTITY clause, as shown in Listing 8.
Listing 8. Using the GENERATED ALWAYS AS IDENTITY clause
CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', ISBN CHAR(10) );
GENERATED ALWAYS AS IDENTITY causes a BOOKID to be generated for each record. The first value generated is 1, and succeeding values are generated by incrementing the previous value by 1.
Note: In DB2 for Linux, UNIX, and Windows, it is also possible to use the GENERATED ALWAYS option to have DB2 calculate the value of a column derived from other columns of the same row automatically.
DB2 provides technology to enable tracking and querying of historical, current, and planned conditions in a straightforward and efficient manner via direct support for temporal tables.
There are two types of temporal tables: system period and business period.
In a system period temporal table, images of rows are retained in an associated history table. The timestamps are maintained by the system and reflect the times during which each row contained the values recorded. This allows a complete historical perspective of the contents of a table. One can query to obtain the results that would have been produced at any specific time in the past.
In a business period temporal table, the meaning of the timestamp is determined by the business, and its timestamps are under application control. Most commonly, these will reflect the timestamp when the business intends (or intended) certain conditions to be effective, such as when a given interest rate will take effect, or when a certain insurance policy was in force. This type of table allows one to query past, present or future business conditions.
These two types of temporal tables are not exclusive. DB2 allows you to define a table with both types of temporal attributes. These are referred to as bi-temporal tables.
Creating a table with PERIOD SYSTEM_TIME is a three step process.
- Create the base table for current data only, but include three
TIMESTAMP columns: two for start/end points of the system time,
and one for the transaction start time (used internally), and
specify PERIOD SYSTEM_TIME. These may be added to an existing
table by an
ALTER. Listing 9 shows a typical
CREATE TABLEstatement containing these fields.
Listing 9. Creating base table for current data
CREATE TABLE POLICY ( POLICYID INTEGER NOT NULL PRIMARY KEY, VIN VARCHAR(10) NOT NULL, COVERAGE INTEGER NOT NULL, SYS_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, SYS_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, TRAN_START TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME (SYS_START, SYS_END) );
- Create an identically structured history table, easily done using
CREATE TABLE … LIKEsyntax, as shown in Listing 10.
Listing 10. Use CREATE TABLE ... LIKE syntax
CREATE TABLE POLICY_HISTORY LIKE POLICY;
- Alter the base table to activate the history table, as shown in
Listing 11. Alter base table
ALTER TABLE POLICY ADD VERSIONING USE HISTORY TABLE POLICY_HISTORY;
Insert, update, and delete data to the base table as normal, but do not provide values for the three timestamps (as these are always generated), as shown in Listing 12.
Listing 12. Insert, update, and delete data
INSERT INTO POLICY(POLICYID, VIN, COVERAGE) VALUES(1001,'11235811',100000); COMMIT; UPDATE POLICY SET COVERAGE=200000 WHERE POLICYID=1001; COMMIT; DELETE FROM POLICY WHERE POLICYID=1001; COMMIT;
The old versions of the rows are copied into POLICY_HISTORY automatically as part of the changes to the values in the POLICY table, with the appropriate system timestamps. At the conclusion of the previous three steps (if they were the only actions performed and each was committed) there would be no rows in the POLICY table, but two separate rows in the POLICY_HISTORY table showing the two sets of values and the timestamps indicating when they had these values.
The query shown in Listing 13 can be used to find out what policy was in effect at 6pm on December 1, 2011. Note that this "time-travelling" query refers only to the POLICY table, not explicitly to the POLICY_HISTORY. For more on queries and "time-travelling" queries, see Section 4 - Working with DB2 data using SQL.
Listing 13. Find policy
SELECT COVERAGE FROM POLICY FOR SYSTEM_TIME AS OF '2011-12-01-18.00.00' WHERE POLICYID = 1001;
Creating a table with PERIOD BUSINESS_TIME is just a one-step process, since no separate history table is involved. Just include the two start/end timestamps (or dates) to be maintained by the application, and the PERIOD BUSINESS_TIME clause, as shown in Listing 14.
Listing 14. Creating table with PERIOD BUSINESS_TIME
CREATE TABLE POLICY ( POLICYID INTEGER NOT NULL, VIN VARCHAR(10) NOT NULL, COVERAGE INTEGER NOT NULL, POLICY_START DATE NOT NULL, POLICY_END DATE NOT NULL, PERIOD BUSINESS_TIME (POLICY_START, POLICY_END), PRIMARY KEY(POLICYID, BUSINESS_TIME WITHOUT OVERLAPS) );
With the WITHOUT OVERLAPS clause, DB2 will ensure that the rows for a given POLICYID will not overlap periods.
Materialized query tables contain data that is derived from one or more source tables, according to its defining SQL query, much like a view. Unlike a view though, the data is physically stored. The data can be refreshed according to a system schedule (system-maintained) or user direction (user-maintained).
Materialized query tables are useful for complex queries that run on large amounts of data. DB2 can pre-compute all or part of such queries and use the pre-computed or materialized results to answer the queries more efficiently. Materialized query tables are commonly used in data warehousing and business intelligence applications.
Only user-maintained materialized query tables can also be updated with
the LOAD utility and the
DELETE SQL statements.
Materialized query tables can be used to improve the performance of dynamic SQL queries. If DB2 determines that a portion of a query could be resolved using a materialized query table, the query might be rewritten by DB2 to use the materialized query table.
A materialized query table is created with a
statement using a SQL full select and some other parameters to control
its refresh and availability for query optimization.
The DDL in Listing 15 creates a materialized query table to contain a summary of the current inventory of BOOKS to be refreshed when directed by the user.
Listing 15. Creating a materialized query table refreshed by the user
CREATE TABLE INVENTORY(BOOKTYPE,AUTHORS,TITLES,BOOKS) AS (SELECT BOOKTYPE,COUNT(DISTINCT AUTHORID), COUNT(DISTINCT ISBN), COUNT(DISTINCT BOOKID) FROM BOOKS GROUP BY BOOKTYPE) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER;
The data within the table is replaced at any time by issuing (either interactively or from an application program) the SQL statement in Listing 16.
Listing 16. Refresh the data in a materialized query table
REFRESH TABLE INVENTORY;
CREATE GLOBAL TEMPORARY TABLE statement
creates a description of a temporary table. Each session that selects
from a created temporary table retrieves only rows that the same
session has inserted. When the session terminates, the rows of the
table associated with the session are deleted. This allows the session
to act upon its data with SQL without incurring significant
The rest of the specification of a created global temporary table is much like the specification of a base table, as is illustrated in Listing 17.
Listing 17. Create a created global temporary table
SET CURRENT SQLID='USER1'; CREATE GLOBAL TEMPORARY TABLE LIBRARY.TOBECATALOGED ( BOOKNAME VARCHAR(100), AUTHORNAME VARCHAR(100) ); GRANT ALL ON LIBRARY.TOBECATALOGED TO USER2, USER3, USER4;
Only USER2, USER3 and USER4 are able to use a temporary instance of LIBRARY.TOBECATALOGED. When USER2 mentions LIBRARY.TOBECATALOGED, that instance comes into existence and is accessible only to USER2 from within USER2's current session. That instance and its data only persists until USER2 issues COMMIT (which is the default). Note too that the GRANT by USER1 must be GRANT ALL. USER2 does not have to create the table instance explicitly, just use it . An example is shown in Listing 18.
Listing 18. Implicit creation of an instance of a created global temporary table
SET CURRENT SQLID='USER2'; INSERT INTO LIBRARY.TOBECATALOGED VALUES('This Book Title', 'Ann Author'); INSERT INTO LIBRARY.TOBECATALOGED VALUES('Remember This Also', 'Another Author'); SELECT COUNT(*) FROM LIBRARY.TOBECATALOGED; COMMIT; SELECT COUNT(*) FROM LIBRARY.TOBECATALOGED;
All instances of LIBRARY.TOBECATALOGED will have the same structure which is defined in the system catalogs. The first SELECT in Listing 18 will return the value 2. The second SELECT will return the value 0, since the definition of LIBRARY.TOBECATALOGED accepted the default that the data will persist only until COMMIT.
The second form of a global temporary table is a declared global temporary table. The table is available only for the session (as was the case with the created global temporary table), and the data put into it is only available within the single session until a COMMIT is issued.
The major difference between declared and created global temporary tables is that the structure of a declared global temporary table is not predefined but rather defined within the same session. Two different sessions can each have their own version (with differing structures) of a table with exactly the same name as the other. Listing 19 illustrates the creation and use of a declared global temporary table.
Listing 19. Creating and using a declared global temporary table
SET CURRENT SQLID='USER2'; DECLARE GLOBAL TEMPORARY TABLE TOBECATALOGED ( BOOKNAME VARCHAR(100), AUTHORNAME VARCHAR(100) ); INSERT INTO SESSION.TOBECATALOGED VALUES('A New Book', 'A Different Author'); SELECT COUNT(*) FROM SESSION.TOBECATALOGED; COMMIT; SELECT COUNT(*) FROM SESSION.TOBECATALOGED;
The first SELECT in Listing 19 will retrieve the value 1. The second SELECT will retrieve the value 0, since the default for both types of global temporary table is that the data is deleted when a commit is issued.
Note that the schema of any and all declared global temporary tables will be 'SESSION'. The structure of a declared global temporary table is not registered in the system catalogs, so the same table name can be used within several different sessions, each with a different structure.
Note also that all subsequent references to the declared global temporary table should include the schema 'SESSION', since if omitted, DB2 will use the value of the special register CURRENT SQLID to resolve the full name of the table.