Sy stem Instance(s) Database(s) Database partition group(s) tables Index(es long data Figure 3. Relationships Among Some Database Objects Schemas A schema is an identifier, such as a user ID, that helps group tables and other database objects. A schema can be owned by an individual, and the owner can control access to the data and the objects within it A schema is also an object in the database. It may be created automatically when the first object in a schema is created. Such an object can be anything that can be qualified by a schema name, such as a table, index, view, package, 6 Administration Guide: Planning
Schemas: A schema is an identifier, such as a user ID, that helps group tables and other database objects. A schema can be owned by an individual, and the owner can control access to the data and the objects within it. A schema is also an object in the database. It may be created automatically when the first object in a schema is created. Such an object can be anything that can be qualified by a schema name, such as a table, index, view, package, System Instance(s) Database(s) Database partition group(s) tables Table space index(es) long data Figure 3. Relationships Among Some Database Objects 6 Administration Guide: Planning
distinct type, function, or trigger. You must have IMPLICIT_SCHEMA authority if the schema is to be created automatically, or you can create the A schema name is used as the first part of a two-part object name. When an schema, it is assigned to the default schema, which is usually the user Dof object is created, you can assign it to a specific schema. If you do not specify the person who created the object. The second part of the name is the name of the object. For example, a user named Smith might have a table named SMITH. PAYROLL. System catalog tables: Each database includes a set of system catalog tables, which describe the logical and physical structure of the data. DB2 creates and maintains an extensive set of system catalog tables for each database. These tables contain information about the definitions of database objects such as user tables, views, and ndexes, as well as security information about the authority that users have on these objects. They are created when the database is created, and are updated during the course of normal operation. You cannot explicitly create or drop them, but you can query and view their contents using the catalog views Table spaces a database is organized into parts called table spaces. A table to store tables. When creating a table, you can decide to have P a place objects such as indexes and large object(LOB) data kept separately from the rest of the table data. A table space can also be spread over one or more physical storage devices. The following diagram shows some of the flexibility you have in spreading data over table spaces Chapter 1.Basic relational database concepts 7
distinct type, function, or trigger. You must have IMPLICIT_SCHEMA authority if the schema is to be created automatically, or you can create the schema explicitly. A schema name is used as the first part of a two-part object name. When an object is created, you can assign it to a specific schema. If you do not specify a schema, it is assigned to the default schema, which is usually the user ID of the person who created the object. The second part of the name is the name of the object. For example, a user named Smith might have a table named SMITH.PAYROLL. System catalog tables: Each database includes a set of system catalog tables, which describe the logical and physical structure of the data. DB2 creates and maintains an extensive set of system catalog tables for each database. These tables contain information about the definitions of database objects such as user tables, views, and indexes, as well as security information about the authority that users have on these objects. They are created when the database is created, and are updated during the course of normal operation. You cannot explicitly create or drop them, but you can query and view their contents using the catalog views. Table spaces: A database is organized into parts called table spaces. A table space is a place to store tables. When creating a table, you can decide to have certain objects such as indexes and large object (LOB) data kept separately from the rest of the table data. A table space can also be spread over one or more physical storage devices. The following diagram shows some of the flexibility you have in spreading data over table spaces: Chapter 1. Basic relational database concepts 7
Table space 1 Table space 2 Table 1 space 3 Table space 4 Table 2 Table space 5 Table space 6 LOB data for Table Space for temporary tables. ces Table spaces reside in database partition groups. Table space definitions and attributes are recorded in the database system catalog Containers are assigned to table spaces. A container is an allocation of physical storage(such as a file or a device A table space can be either system managed space(SMS), or database managed space(DMS). For an SMS table space, each container is a directory in the file space of the operating system, and the operating systems file manager controls the storage space. For a DMS table space, each container is either a fixed size pre-allocated file, or a physical device such as a disk, and the database manager controls the storage space 8 Administration Guide: Planning
Table spaces reside in database partition groups. Table space definitions and attributes are recorded in the database system catalog. Containers are assigned to table spaces. A container is an allocation of physical storage (such as a file or a device). A table space can be either system managed space (SMS), or database managed space (DMS). For an SMS table space, each container is a directory in the file space of the operating system, and the operating system’s file manager controls the storage space. For a DMS table space, each container is either a fixed size pre-allocated file, or a physical device such as a disk, and the database manager controls the storage space. Table space 1 Table space 2 Table 1 Table 1 index Table space 3 Table 2 Table 3 Table space 4 Table 2 index Table 3 index LOB data for Table 2 LOB LOB Table space 5 Table space 6 Space for temporary tables. System catalog tables for definitions of views, packages, functions, datatypes, triggers, etc. Figure 4. Table Spaces 8 Administration Guide: Planning
Figure 5 illustrates the relationship between tables, table spaces, and the two types of space. It also shows that tables, indexes, and long data are stored in table spaces Database Object/Concept Equivalent Physical Object System Instance(s) Table spaces are where tables are stored: Table space SMS or DMs ainer Each container is a 事 fixed, pre-allocated space file or a physical device such as a disk 孕 Figure 5. Table Spaces and Tables Figure 6 on page 10 shows the three table space types: regular, temporary, and Tables containing user data exist in regular table spaces. The default user table space is called USERSPACEl. The system catalog tables exist in a regular table space. The default system catalog table space is called SYSCATSPACE Tables containing long field data or large object data, such as multimedia objects, exist in large table spaces or in regular table spaces. The base column data for these columns is stored in a regular table space, while the long field or large object data can be stored in the same regular table space or in a Chapter 1. Basic relational database concepts 9
Figure 5 illustrates the relationship between tables, table spaces, and the two types of space. It also shows that tables, indexes, and long data are stored in table spaces. Figure 6 on page 10 shows the three table space types: regular, temporary, and large. Tables containing user data exist in regular table spaces. The default user table space is called USERSPACE1. The system catalog tables exist in a regular table space. The default system catalog table space is called SYSCATSPACE. Tables containing long field data or large object data, such as multimedia objects, exist in large table spaces or in regular table spaces. The base column data for these columns is stored in a regular table space, while the long field or large object data can be stored in the same regular table space or in a specified large table space. Database Object/Concept Equivalent Physical Object Each container is a directory in the file space of the operating system. Each container is a fixed, pre-allocated file or a physical device such as a disk. Table spaces are where tables are stored: SMS or DMS System Instance(s) Database(s) tables Table space index(es) long data Figure 5. Table Spaces and Tables Chapter 1. Basic relational database concepts 9
Indexes can be stored in regular table spaces or large table spaces Temporary table spaces are classified as either system or user System temporary table spaces are used to store internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and joining tables. Although you can create any number of system temporary table spaces, it is recommended that you create only one, using the page size that the majority of your tables use. The default system temporary table space is called TEMPSPACEl User temporary table spaces are used to store declared global temporary tables that store application temporary data. User temporary table spaces are not created by default at database creation time Database Type of table space(s): IPORARY User data is Type of table space(s) LARGE Figure 6. Three Table Space Types ontainers: A container is a physical storage device. It can be identified by a directory name, a device name or a file name 10 Administration Guide, planning
Indexes can be stored in regular table spaces or large table spaces. Temporary table spaces are classified as either system or user. System temporary table spaces are used to store internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and joining tables. Although you can create any number of system temporary table spaces, it is recommended that you create only one, using the page size that the majority of your tables use. The default system temporary table space is called TEMPSPACE1. User temporary table spaces are used to store declared global temporary tables that store application temporary data. User temporary table spaces are not created by default at database creation time. Containers: A container is a physical storage device. It can be identified by a directory name, a device name, or a file name. Database Type of table space(s): REGULAR Table(s): User data is stored here. Type of table space(s): TEMPORARY Type of table space(s): (optional) LARGE Figure 6. Three Table Space Types 10 Administration Guide: Planning