Table space storage structure is important to establish the mapping between the logical structure of the database and operating system physical structure of the database system, it acts as an intermediate layer and a database to store the actual data between the container for indicating the physical location of data in the database. Create any database must be explicitly or implicitly assigned table space and database of all the data is in the table space.
Users can hardware environment and cost requirements, based on a different table space containers by specifying the freedom to choose the physical storage location data. And because backup and recovery can be performed at the table space level, the user can perform more granular backup and recovery control.
Former Understanding table space to understand the container.
Container is a physical storage device, can be identified by a directory name, device name or file name. In fact, this is precisely the three kinds of container types. Equipment container (such as a tape, etc.) and file containers are treated equally, usually directly be understood as a data file (internal disk storage structure presentations, the data file contains a number of Extent.). System management table space container can use the directory, database management table space can only use the device and the container file container.
Container is assigned to a table space, a single table space can be used more than one container, but the container can only belong to one table space. The container is usually located on a local disk, remote network devices or some files can also be used as a container table space, but because of network latency and reliability, remote container impact on database security and performance, it is not recommended to use a remote container .
DB2 database system table space, there are three types of management:
System management table space (SMS, System-Managed Space)
SMS table space is allocated and managed by the operating system's file system manager. In this table space, the data storage space entirely by the operating system, the only vessel able to use SMS table space directory container, SMS table spaces can define a plurality of containers, container directory can be increased according to need, so SMS table space size can be dynamically increased. But once the SMS table space is created, you can not add or remove container for the table space. SMS table space usually contains multiple files that represent the table object is stored in the file system space, such as table data, table indexes, tables, large objects are occupied by a single or several files. Once you specify the SMS table space for the table, then the data in the table is not allowed to be stored separately from that of conventional data tables, indexes, large object data can not be located in a different table space. Prior to the DB2 V9 version of the database, create a database (if not specified when creating a database table space will be created by default three table space) or the default type of table space is a SMS table space. In addition, only DB2 database management systems allow table space, table space Oracle database is a database management system management table space does not exist.
Here is a simple example of SMS table space: create a directory for the container containers1 SMS table space MYSPACE. Then create a table TEST, its general data, indexes, large objects are located MYSPACE table space (if you specify three different table space, the table creation will fail, in fact, specify the table space data for conventional SMS table space after the index table space, and large objects will tacitly). After the table is created, the data on the physical disk is this:
Wherein SQL00002.DAT data files table in the regular data; SQL00002.DTR data files and temporary data due to the reorganization, table joins and other produce. SQL00002.INX data files are stored in the index table. There LF suffix store LONG VARCHAR or LONG VARGRAPHIC data, LB suffix store BLOB, CLOB and DBLOB data storage LB assigned LBA suffix suffix files and free space information. Also by the way the figure SQLTAG.NAM file. In fact each container has a TAG data structure used to identify containers which belong to a table space which a database of which one example. For containers DMS table space, TAG is located within the first Extent, containers for SMS table space is in the form of SQLTAG.NAM file exists.
Use SMS table space, each table will be in the container table space (that is, directory) corresponding to a series of SQL * .DAT, SQL * .INX other documents. Information table and the corresponding files stored in the system catalog tables SYSIBM.SYSTABLES, each table corresponds to a FID (table where the file group number) and TID (table where the table space number), you can use the query query :
Select fid, tid, name from sysibm.systables where name = 'TEST';
Database management table space (DMS, Database-Managed Space)
DMS table space by the database management system (DBMS) to manage control, in essence, this type of table space to the greatest extent to meet the needs of the database manager and is designed for a particular purpose file system implementation. DMS table space is limited by the number of containers consisting of container DMS table spaces can be used are file container and container equipment, space containers are pre-assigned and is not allowed to modify the size, but with different SMS table space Shi, DMS table space allows you to add the container. In other words, SMS table spaces and DMS table spaces, respectively, by expanding the size and increasing the number of container vessels ways to increase table space size. You need to manually specify one or more containers DMS table spaces created. File-container table space is after you've created a separate file. Using DMS table spaces Table data can be stored separately, that is, regular data, indexes, and large object data to specify a different DMS table space.
Automatic storage DMS table space (Automatic Storage DMS)
Independent type of table space for automatic storage table space is not in the true sense. It is stored in another DMS treatment. DMS requires a lot of maintenance, and automatic memory is used as a means of simplifying space management, table space can be managed automatically maintained, it is a concept introduced in DB2 V8.8.2, to replace the SMS becomes the default type of table space .
Comparison of three kinds of table space
Distinguish three tables in all aspects of space is very large, the following table shows some key differences:
In addition to using SMS table space can simplify management, the most significant difference between SMS and DMS storage model is the maximum size of the table space. When using SMS, DBA can put up to 64GB of data in the table space. The page size to 32K, this limit can be expanded to 512GB, but at the expense of free space on each page will be less likely. To DMS table space limit model will be expanded to 2TB (4K page size of the case). If the page size to 32K, the available space can grow to 16TB. Although there are other ways to make a breakthrough 64GB table size limit, but the easiest way might be to start out with DMS table space. (Why these restrictions described later)
DMS and automatic storage DMS
So better DMS and automatic storage Which way? Automatic storage path to the database allows the DBA to store all the settings when you create a table space container can be used. DBA does not have to explicitly define the location and size of the table space, the system will automatically allocate space. In DB2 9, when creating a database will enable automatic storage unless the DBA explicitly override this setting.
Enable automatic storage database has one or more storage paths associated. Table space can be defined as "automatic storage is managed," its container is allocated by DB2 based on those storage paths. Automatically stored in the database can only be enabled when created. For the original database is not enabled for automatic storage, you can not enable this feature in the future. Similarly, for the first automatic storage enabled databases can not disable this feature in the future.
The following table summarizes some differences between managing non-automatic storage and automatic storage.
The main purpose of introducing automatic storage management model is a simplified DMS table space, while maintaining its performance characteristics. Sometimes the DBA must define all features using the table space, but will simplify the management of many applications benefit from automatic storage provided.
The above is in accordance with the management of different types of database table space has been divided, in fact, according to different purposes, the table space can be divided into five types:
System table space (SYSCATSPACE)
Table space is also called the system catalog table space, DB2 system catalog tables is to save all DB2 database DB2 object metadata places in the Oracle database, known as the data dictionary. The system table is stored in the system catalog table space. System table space is created automatically when a database is created, and each must have one and only one database system table space, the system is forced to table space named SYSCATSPACE. System default table space is an SMS table space, you can also explicitly specify a DMS table space.
System Tools table space (SYSTOOLSPACE)
System Tools table space is available for DB2 management tools and SQL administrative routines use a particular table space, system tools table space can not be explicitly created, only the first time you use any of the following when a tool or process will be created automatically: ADMIN_COPY_SCHEMA process, ADMIN_DROP_SCHEMA process management task scheduler, changing table notebook, ALTOBJ process, automatic reorganization (including db.tb_reorg_req health indicators), automatically collect statistical information (including db.tb_runstats_req health indicators), configure automatic maintenance Wizard, db2look command design advisor, GET_DBSIZE_INFO process, storage management tools, SYSINSTALLOBJECTS process. Including management task scheduler, ADMIN_COPY_SCHEMA and ADMIN_DROP_SCHEMA process for the first time does not create a system tool table space, but they are using a system tool table space.
User table space (USERSPACE)
Automatically created when a user table space is to create a database, table space name USERSPACE1, database user table stored in the default table space, the user table space is optional, a database can have more than one user table space. Must have at least one user table space (no user table space, then the database does not store user data). User table space can also be a SMS table space or a DMS table space, usually using DMS table space.
Temporary table space (TEMPSPACE)
Automatically created when a temporary database table space is created, the database manager uses the temporary table space to store temporary data when performing SQL operations such as sorting, table reorganization, index creation, and an intermediate link table and other operations generated by the temporary table space table storage, databases must have at least a temporary table space, you can also have more than one. When you create a database created by default temporary table space name is TEMPSPACE1, and is SMS table space. But the name of the table space may be arbitrary, when additional temporary table space is created, the default temporary table space can be deleted. (But we must ensure that there is a temporary database table space). Temporary table space can also be a DMS table space. In addition, DB2 support system temporary table space and user space are two types of temporary tables, system temporary table space must exist, a user temporary table space can have zero or more, to declare a temporary table.
In addition to the management and use of partition table space type, capacity will also be table space into a regular table space and table space as large. But here is a regular table space and table space is large for DMS table spaces, SMS table space size limit is less than regular table space.
Consider first a question: logic, data in the form of rows (tuples) stored in the table in the database, but physically, according to the database disk storage knowledge (database-depth study notes ---- internal disk storage structure), the data must be exact to say that is stored on a data file in the data block (page) storage. Then, the database is based on how the rows in a table addressed to the data on the physical disk data page of it?
Oracle and DB2 solution is to use a new data structure: the row pointer (or row indicator), Oracle database called ROWID, DB2 called RID. In the actual database tables, each table will attach a special hidden column, the column of pointers that line, that is, each line has a line of data pointer attribute, which points to the rows of data in the specific location of the physical disk. In fact, whether Oracle or DB2, the row pointer can all be involved in SQL queries (after all, is a valid line of ROWID attribute .Oracle property directly as query, DB2 will need to use rid function queries RID)
Regular table space
Before DB2 V9, RID has a 4-byte (32-bit) length, of which 3 bytes for data page addressing, the last one byte is used in the data page slot number address ( "Database-depth study notes - --- the internal structure of the disk storage, "introduced, each row is a record stored in the data storage space data pages, each record corresponds to a groove in the directory slot number).
By the RID structure we can calculate the number of records that can be accommodated data page (rows) of the table space and capacity:
Because a RID only one byte (8 bits) slot, so that the maximum number of records stored in a data page is 255 (8 -12 th power, why should minus 1? This is because the disk storage that articles have said, there is some data page is a free-space control record (FSCR), so need to set aside). (Note: 8 addressing potential energy range is 0-255, slot number can only be 0-255, if the record more, it can not be numbered slots directory, can not be addressed, the data stored is white deposit .)
Similarly, according to the three-byte page number, draw a table space can accommodate up to 16777216 (16M, 2 to 24 times) data pages. So, if a data page is 4KB, then the table space size is 16M * 4KB = 64GB up. If a data page 32KB, then that is 16M * 32KB = 512GB. (DB2 table spaces supported page sizes have 4KB, 8KB, 16KB and 32KB of four, a table space can only use one data page size)
Imagine, if a database table line length (one line storage space) is too small, since a data page can theoretically store up to 255 lines (in fact, the number of records of each page allows storage is generally less than 255 ), it will inevitably lead to waste of space data pages, such as 4KB pages, the length of the row is full page 12B only occupy 12B * 255 = 3060B space remaining 1036KB much space can be wasted.
Once the table space to meet the maximum page limit, there are three options available:
1. In view of these tables are connected (multiple table space table together in the view);
2. Using the DB2 database partitioning feature (DPF, Database Partitioning Feature), the data across multiple partitions to combine;
3. The use of the partition table.
Either program requires some migrate data and applications may be modified, this is very complicated.
Large table space
In order to make the data page to accommodate more rows (records) and table space for more data pages, DB2 V9 introduces a new row pointer (RID, line indicator) format. Data page stamped by three bytes to four bytes from the slot 1 byte to 2 bytes, the 6-byte RID format will ultimately limit the size of the table space to expand to 32 times the original. That table space 4KB pages maximum capacity of 2TB, 32KB page table space maximum capacity of 16TB. At the same time, the number of rows per data page can theoretically accommodate also expanded to more than 65,000 lines, but because of the specified minimum length of each row is about 12B, so, in fact 4KB pages to accommodate the maximum number of rows to 4KB / 12KB = about 341 rows, 32KB page is about 2300 lines.
This RID format good solution to the table space is limited and the data page of wasted space (no longer subject to the slot, but the line length) problems. But also brought a management challenges, such as backup and recovery. In fact, when the table began to grow TB's when you should consider using some techniques such as table partitioning, database partitioning, to manage large amounts of data.
Using the old format of 4 bytes RID table space is a regular table space, use the new six-byte format RID table space is a large table space. DB2 V9 in a large table space is the default type DMS table space, of course, you can explicitly create a regular table space (table space specified REGULAR). Obviously, SMS table space is not possible to support large table space.
RID 6-byte format supported table space comprising: a system temporary table space, the user temporary table space, the user regular table space. That is, only the system table space can not be created as large table space. Catalog table because the current is insufficient to achieve a timely manner to ensure the state a larger table space size.
Large table space before DB2 V9 called long table space to store long or LOB table columns to make up for lack of regular table space in dealing with long or LOB data.
Migration regular table space to a large table space
DB2 V9 regular table space will not be automatically upgraded to a large table space, but you can manually upgrade: ALTER TABLESPACE tablespacename CONVERT TO LARGE.
ALTER command does not change the physical structure of the table space, except for changing the way the catalog to indicate the table space can support 6-byte RID format. It should be noted that:
1. Executive LARGE upgrade requires immediate COMMIT WORK, transaction commit, otherwise it will hold an exclusive lock on the table space, and other work in the table space on the table did not proceed until the lock is lifted.
2. Once the table space is modified to LARGE, in order to better take advantage of the 6-byte RID, but also need to continue restructuring the data and index reorganization. Without index reorganization, the pre-existing table and each page will continue to limit the number of 3-byte line 255, because the index is still using the old RID, unable to go beyond the original scope of the index page. Effect of different data restructuring (table reorganization) and index reorganization, the number of index table space affected by the reorganization of pages, table reorganization affected the number of lines that a page is stored.