| A database is an organized collection of related objects. SQL is used throughout the database industry as a common method of issuing database queries. SQL is considered a language, composed of statements, functions, and data types. A SQL statement is used to access database objects using relational operations. Before we examine the SQL language, we need to understand some DB2 terminology. We will be referring to the basic data structures or objects that are defined for each DB2 database. These objects include -
Tables -
- Data types -
- Aliases -
- Synonyms -
Views -
Indexes -
- Keys -
Tablespaces -
Index spaces -
Databases -
Storage Groups Figure 2-1 shows the relationship between the DB2 objects. Figure 2-1. A hierarchy of DB2 structures.  Tables A table is an unordered set of data records. It consists of columns and rows. Each column is based on a data type. Tables, once created and populated with data, are referenced in the FROM clause of the SQL statements. There are three types of tables: We discuss only tables created with a DDL statement in this chapter. These tables are created using the CREATE TABLE, CREATE AUXILIARY TABLE, or CREATE GLOBAL TEMPORARY TABLE statement, and each is a logical representation of the way the data is physically stored on disk. We discuss the declared temporary tables in Chapter 7, "Application Program Features." Data Types Data types are used to define the type of data that will occupy each column in a DB2 table. It will also provide the length of column. Data that does not match the data type defined to the column will not be allowed in that column. There are different types of data, such as string, numeric, date, time, ROWIDs, LOB (large object), and user-defined distinct data types. Depending on the data type, the length can be fixed or varying. You can categorize these data types into two categories: built-in and user -defined. Data types that are defined by DB2 are referred to as built-in. The other type, user-defined, allows you define your own type of data to be stored in the column. These user-defined data types are based upon existing built-in data types. Aliases An alias is basically a pointer to another table and is a substitute for the three-part name of a table or view. An alias can be qualified by an owner ID, and the table referenced can be on the local site or on a remote site. Aliases are often used in place of any three-part named table in order to make the references portable, since DB2 on OS/390 is the only server that supports three-part name syntax. Aliases are not dropped if the table they point to is dropped. Synonyms Synonyms are used to refer to a table using a different name or to refer to another owner's table as if you were the owner. A synonym is basically a private pointer to a table. It can be referenced only by its owner and is not allowed to be qualified. Unlike an alias, a synonym can be used to refer only to a table in the same subsystem in which it was defined. If the table is dropped, so is the synonym. Views Views are alternate ways of viewing data in one or more base tables or other views. A view is not a physical object and does not store actual data. It is an entry in the DB2 catalog that, when accessed, executes a SQL statement that retrieves data from other tables or views. Views can be used to limit access to certain kinds of data, create customized views of data for users, or allow for alterations of tables without affecting application programs. Views can be read only if necessary, or they can be updateable, insertable, or deleteable. There are some restrictions to using views. Indexes Indexes are ordered sets of pointers associated with a table. Indexes can be created on permanent tables or on declared temporary tables. The index is based on the values of one or more columns in the table. The index values can be either ascending or descending. Each table can have one or more indexes, and indexes can also be used to enforce uniqueness in the data values and clustering sequence. Indexes are used to improve performance when accessing data. If an index is used, large scans of data can be avoided. Indexes are physical objects that are created in index spaces, which are data sets like those that support the tablespaces. The index space is automatically created in the same database as the table on which it is defined. There are two types of indexes: partitioned and nonpartitioned. A partitioned index is created on a partitioned tablespace and is used to define the allowable values for each partition. A nonpartitioned index is used on tables defined in segmented, simple tablespaces or as an alternate index on a partitioned tablespace. NOTE  | The maintenance overhead of indexes will negatively impact the performance of INSERT, UPDATE, and DELETE statements. |
Keys Many keys can be defined on a table. A key is a named ordered set of columns on one table. A composite key is a key made of several columns. Each key will play a particular role for a given table. Tablespaces Tablespaces are objects that consist of one or more datasets that are used to stored DB2 data. Tables are created in tablespaces, and you can have one or many tables in a tablespace, depending on the type of tablespace defined. There are four types of tablespaces: simple, segmented, partitioned, and LOB. Both simple and segmented tablespaces allow for multiple tables, but only segmented should be used due to the many performance issues. -
Simple tablespaces: These tablespaces can contain one or more tables; however, the rows of the different tables are not kept on separate pages, which can cause concurrency and space usage issues. -
Segmented tablespaces: Segmented tablespaces can contain one or more tables, and the space is divided into same- size segments. Each segment will contain rows from only one table. -
Partitioned tablespaces: A partitioned tablespace divides the data into several separate data sets (partitions). There can be only one table in a partitioned tablespace. You can have up to 254 partitions of up to 64 GB each. Each partition can have different characteristics, such as volume placement and free space. -
LOB tablespaces: LOB tablespaces are required to hold large object data. LOB tablespaces are associated with the tablespace that holds the logical LOB column. Index Spaces When an index is created, an index space is also created. Index spaces are a set of virtual storage access method (VSAM) linear datasets that hold index data, and they are implicitly associated with the database that contains the table the index is defined on. Databases Databases in DB2 are collections of tablespaces and index spaces. The database on the OS/390 platform does not have any physical storage characteristics, but acts more like an umbrella over all of its dependent objects. Storage Groups Storage groups are used to define a list of volumes to DB2 for its use when creating physical objects such as tablespaces and index spaces. The storage is allocated when the tables are loaded with data. Storage groups are created using DDL. | 
