Tables

Tables are logical structures maintained by the database manager. Tables are made up of columns and rows. There is no inherent order of the rows within a table. At the intersection of every column and row is a specific data item called a value. A column is a set of values of the same type. A row is a sequence of values such that the nth value is a value of the nth column of the table.

There are three types of tables:

• A base table is created with the CREATE TABLE statement and is used to hold persistent user data. For more information see CREATE TABLE.

  A base table has a name and may have a different system name. The system name is the name used by the IBM® i operating system. Either name is acceptable wherever a table-name is specified in SQL statements.

  A column of a base table has a name and may have a different system column name. The system column name is the name used by the IBM i operating system. Either name is acceptable wherever column-name is specified in SQL statements. For more information see CREATE TABLE.

  A materialized query table is a base table created with the CREATE TABLE statement and used to contain data that is derived (materialized) from a select-statement. A source table is a base table, view, table expression, or user-defined table function. The select-statement specifies the query that is used to refresh the data in the materialized query table.

  Materialized query tables can be used to improve the performance of SQL queries. If the database manager determines that a portion of a query could be resolved by using the data in a materialized query table, the query may be rewritten by the database manager to use the materialized query table. For more information about creating materialized query tables, see CREATE TABLE.

  A partitioned table is a table whose data is contained in one or more local partitions (members). There are two mechanisms that can be specified to determine into which partition a specific row will be inserted. Range partitioning allows a user to specify different ranges of values for each partition. When a row is inserted, the values specified in the row are compared to the specified ranges to determine which partition is appropriate. Hash partitioning allows a user to specify a partitioning key on which a hash algorithm is used to determine which partition is appropriate. The partitioning key is a set of one or more columns in a partitioned table that are used to determine in which partition a row belongs.

  A distributed table is a table whose data is partitioned across a nodegroup. A nodegroup is an object that provides a logical grouping of a set of two or more systems. The partitioning key is a set of one or more columns in a distributed table that are used to determine on which system a row belongs. For more information about distributed tables, see the DB2® Multisystem book.

• A result table is a set of rows that the database manager selects or generates from a query. For information on queries, see Queries.
• A declared temporary table is created with a DECLARE GLOBAL TEMPORARY TABLE statement and is used to hold temporary data on behalf of a single application. This table is dropped implicitly when the application disconnects from the database.