Two ways of DB2 table space management

Below you will find a table space for DB2 (DB2 certified DB2 Training) in managed form, with examples of related issues for your reference, and if you are interested in managing the DB2 table space, you may want to help.

The DB2 table space is managed in two ways: the System Management Space (Systems Management space,sms) and the Database Management space (db Management Space,dms).

By type: Rule table space, long integer tablespace, System temp table space, User temp table space. One of the long integer table spaces can only be DMS.

A table in the rule table space that contains user data. The Default User table space is named USERSPACE1, and the index is stored in the rule table space, and the system catalog table is placed in the rule table space.

The default system directory tablespace name is syscatspace.

Temporary tablespace is divided into system temporary table space and user temporary table space. The system temporary tablespace is used to store the internal temporary data required in various data operations (sort, reorganize tables, create indexes, join tables), although you can create any number of system temporary table spaces, but it is recommended that users only create one using the page size used by most tables, with the default system temporary tablespace named TEMPSPACE1. The user temporary tablespace is used to store the described global temporary table (which indicates that the global temporary table stores the application temporary data). The user temporary tablespace is not created by default when the database is created.

SMS each container is a directory in the file space of the operating system; DMS Each container is a fixed, pre-allocated file, or physical device.

SMS management is simple, automatically managed by the operating system, the size of the space with the change in data volume system automatically adjusted.

DMS is managed by the database, and the size of the space is determined at the time of creation, when there is not enough space to manually add or delete part of the data to free up space.

In most cases, DMS performs better than SMS.

Simple syntax for creating SMS tablespace using the command line method:

CREATE tablespace; MANAGED by SYSTEM USING (';')

Simple syntax for creating DMS tablespace using the command line method:

CREATE tablespace; MANAGED by DATABASE USING (FILE '; '

;)

Example one: Create an SMS table space on OS/2 or Windows NT:

CREATE tablespace RESOURCE MANAGED by SYSTEM

USING (' d:\acc_tbsp ', ' e:\acc_tbsp ', ' f:\acc_tbsp ')

Example two: Create a DMS tablespace on OS/2, using two file containers with 5000 pages each:

CREATE tablespace RESOURCE MANAGED by DATABASE

USING (FILE ' d:\db2data\acc_tbsp ' 5000,

FILE ' e:\db2data\acc_tbsp ' 5000)

Example three: Create a DMS table space on UNIX, using 3 logical volumes with 10000 pages each:

CREATE tablespace RESOURCE MANAGED by DATABASE

USING DEVICE '/dev/rdblv6 ' 10000,

DEVICE '/dev/rdblv7 ' 10000,

DEVICE '/DEV/RDBLV8 ' 10000)

OVERHEAD 24.1

Transferrate 0.9

The UNIX devices mentioned in the above statement must already exist, and the instance owners and Sysadm groups must be able to write to them.

Example four: Create a DMS tablespace on a node group called Oddnodegroup in the UNIX partition database:

CREATE tablespace plans MANAGED by DATABASE

USING (device '/dev/hdisk0 ' 10000, device '/dev/n1hd01 ' 40000) on

NODE 1

(Device '/dev/hdisk0 ' 10000, device '/dev/n3hd03 ' 40000) On

NODE 3

(Device '/dev/hdisk0 ' 10000, device '/dev/n5hd05 ' 40000) On

NODE 5

In this example, the Oddnodegroup node group consists of a database partition numbered 1, 3, and 5 that uses a/dev/hdisk0 device with 10,000 4KB pages on all database partitions, plus, A device that contains 40,000 4KB-sized pages is also described for each database partition.

Example five: On a UNIX system, create an SMS table space with a 8KB page size:

CREATE tablespace sms8k PAGESIZE 8192

MANAGED by SYSTEM

USING (' Fsms_8k_1 ')

Bufferpool buffpool8k

The associated buffer pool must also have the same 8KB page size. In addition, the table space that is created can only be used if the referenced buffer pool is activated.

You can rename an existing tablespace without having to care about the individual objects in that table space. When you rename a tablespace, all catalog records that reference that tablespace are changed. You cannot rename a syscatspace table space; You cannot rename a tablespace that is in a forward-pending or roll-forward state. When you delete a tablespace, all data in that tablespace is also deleted, the container is freed, the catalog item is stripped, and all the objects defined in the tablespace are deleted or marked as invalid. You can reuse the containers in the tablespace by removing the tablespace, but you must commit the drop tablespace command before attempting to reuse the containers.

Two ways of DB2 table space management