Table space is stored in the database and the database logic layer between the tables. Table space created in the database, table space created in the table.
Use the table space is a clear advantage to a reasonable distribution of the data stored in different disk or stored in different locations on the disk to help improve data access efficiency.
DB2 table space by way of management is divided into two types: system management space (System Management Space, SMS) and database management space (Database Management Space, DMS).
Divided by type: rules table space, a large object table space, system temporary table space, the user temporary table space.
Rules contained in user data table space table. The default user table space named USERSPACE1, the index is also stored in the rule table space, additional system catalog table is also on the rules table space. Default system catalog table space named SYSCATSPACE.
Temporary table space is divided into system temporary table space and user temporary table space.
System temporary table space used to store various data manipulation (sorting, re-table, create index, connection tables) in the internal temporary data required, although you can create any number of system temporary table space, but recommends that users only use most of the table used to create a page size, the default system temporary table space named TEMPSPACE1.
User temporary table space used to store the global temporary table shows (note the global temporary table has been stored in the application temporary data). User temporary table space is not created by default when the database was created.
SMS is the operating system for each container file space in a directory; DMS each container is a fixed, pre-allocated file, or a physical device.
SMS management is relatively simple, automated management by the operating system, the size of space changes with the amount of data automatically adjusted.
DMS is managed by the database, the space created when the OK, space is not enough time to manually add or delete some data to free space.
In most cases, DMS performance better than SMS.
Using the command line to create a simple SMS table space syntax:
CREATE TABLESPACE; MANAGED BY SYSTEM USING ('';'')
Example One: In Windows, create a SMS table space:
CREATE TABLESPACE RESOURCE MANAGED BY SYSTEM USING (''d: acc_tbsp'','' e: acc_tbsp'','' f: acc_tbsp'')
Complete results of the D, E, F three disk name acc_tbsp create three folders, each folder has a name SQLTAG.NAM the following file.
Example Two: Remove case of an SMS table space is created:
DROP TABLESPACE RESOURCE
End run this command, the corresponding table space will be removed from the DB2 registry, but the three folders on the disk still needs to manually remove.
Using the command line to create DMS table space, a simple syntax:
CREATE TABLESPACE; MANAGED BY DATABASE USING (FILE'';'' or CREATE TABLESPACE; MANAGED BY DATABASE USING (DEVICE'';''
Example Three: In Windows, to create a DMS table space, using their two 5,000-page file container:
CREATE TABLESPACE RESOURCE MANAGED BY DATABASE USING (FILE''d: db2dataacc_tbsp''5000, FILE''e: db2dataacc_tbsp''5000)
Operation results are as follows: in D, E db2data disk folder The following creates a file named acc_tbsp, large and small, to 20000K (default page size is 4K).
Example Four: In the case of three to create the table space to add a container:
ALTER TABLESPACE RESOURCE ADD (FILE''f: db2dataacc_tbsp''5000)
Results of the operation in the RESOURCE table space consists of three containers (files): d: db2dataacc_tbsp, e: db2dataacc_tbsp, f: db2dataacc_tbsp.
Example Five: The RESIZE clause to change the case of three to create the table space container size:
ALTER TABLESPACE RESOURCE RESIZE (file''d: db2dataacc_tbsp''8000, file''e: db2dataacc_tbsp''8000, file f: db2dataacc_tbsp''8000) for each container (file) size into 8000. The bigger the capacity of the container will not be wrong, but if the container has been filled with data, then reduce the capacity of the container will cause an error.
Cases 6: The case of 3 EXTEND clause to change the table space to create the size of the container:
ALTER TABLESPACE RESOURCE EXTEND (file''d: db2dataacc_tbsp''1000, file''e: db2dataacc_tbsp''1000, file''f: db2dataacc_tbsp''1000)
Operating results of the order based on the original capacity, each container to increase 1000.
Cases 7: Delete the case of three created in DMS table space:
DROP TABLESPACE RESOURCE
The result of running in the DB2 registry to remove the RESOURCE table space, while the disk to the corresponding folders and files are automatically deleted together.
Eight cases: in UNIX to create a DMS table space, use of each of the three logical volumes 10000:
CREATE TABLESPACE RESOURCE MANAGED BY DATABASE USING (DEVICE''/ dev/rdblv6''10000, DEVICE''/ dev/rdblv7''10000, DEVICE''/ dev/rdblv8''10000)
The above mentioned statement UNIX device must already exist, and the instance owner and SYSADM group must be able to write them.
Comparison of SMS and DMS
Characteristics of SMS DMS
Table space can be dynamically increased the number of containers you N Y be able to index data stored in the table in different table spaces you N Y be able to store large object data to a different table space table, you N Y table can be distributed to multiple storage a table space, you N Y only need to allocate space when you Y N table space can be placed on different disks you Y N created, you can change the block size N N
Cases 9: Creating a system temporary table space:
CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp MANAGED BY SYSTEM USING ('d: tmp_tbsp', 'e: tmp_tbsp')
System temporary table can only be stored in the system temporary table space, so the database must always have at least one system temporary table space.
Case of 10: create a user temporary table space:
CREATE USER TEMPORARY TABLESPACE usr_tbsp MANAGED BY DATABASE USING (FILE 'd: db2datauser_tbsp' 5000, FILE 'e: db2datauser_tbsp' 5000)
User temporary table space to store the description of the temporary table (DECLARE GLOBAL TEMPORARY TABLE statement using the definition)
Case of 11: Using RENAME statement to rename table space:
RENAME TABLESPACE RESOURCE TO RES1
With the statement to rename table space, will automatically change all references to the table space, catalog records, so no need to care about the table space in the individual objects.
Case of 12: In the RESOURCE table space to create a table named T1:
CREATE TABLE T1 (ABC INT) IN RESOURCE
Recommand Link:
Christmas-Idea PSP AOL Video EncodeHelp Tools StorageMp4 to aviAlltoDVD DVD to DivXBlackberry FormatFree Download Converter Mp4 To 3gpHow-to DVD To DivXALLTODVD VOB to MPEGavi to 3GPEvaluate Business Databases And ToolsBliss CD Sound MP2 to OGG RippingCD-R WAVE To MidiBluesea Xvid MPG MPEG EncodeLasVegas Blackberry ConverterExplosion Blackberry ConverterPRINTER ReportSimple Food And Drink