DB2 tablespace Management

Create tablespace syntax tree
 
 
 
>-CREATE
-- + ----------------------- + ---------------------------->
+-LARGE --------------- +
+-REGULAR --------------- +
|.-SYSTEM-. |
'-+ -------- + -- TEMPORARY -'
'-USER ---'
 
> -- TABLESPACE -- tablespace-name ------------------------------------>
 
> -- + ----------------------------------------------------------- + -->
|.-Database partition group-. |
'-IN -- + -------------------------- + -- db-partition-group-name -'
 
> -- + -------------------------- + --------------------------------->
'-PAGESIZE -- integer -- + --- + -'
'-K -'
 
.-Managed by--automatic storage -- | size-attributes | ---------------------.
> -- + ------------------------------------------------------------------------ + -->
'-Managed by -- +-SYSTEM -- | system-containers | -------------------------- + -'
'-DATABASE -- | database-containers | -- | size-attributes | -'
 
> -- + --------------------------------- + -------------------------->
'-EXTENTSIZE -- +-number-of-pages-+ -'
'-Integer -- +-K-+ --'
'-M -'
 
> -- + ----------------------------------- + ------------------------>
'-PREFETCHSIZE -- +-AUTOMATIC ------- + -'
+-Number-of-pages-+
'-Integer -- +-K-+ --'
+-M-+
'-G -'
 
> -- + ----------------------------- + ------------------------------>
'-BUFFERPOOL -- bufferpool-name -'
 
> -- + ---------------------------------- + ------------------------->
'-OVERHEAD -- number-of-milliseconds -'
 
> -- + ------------------------ + ------------------------------------->
+-No file system caching-+
'-File system caching ----'
 
> -- + -------------------------------------- + --------------------->
'-TRANSFERRATE -- number-of-milliseconds -'
 
> -- + --------------------------------- + -------------------------> <
'-Dropped table recovery -- +-ON -- + -'
'-OFF -'
Size-attributes
 
| -- + --------------------- + -- + ----------------------------- + ----->
'-AUTORESIZE -- +-NO -- +-''-INITIALSIZE -- integer -- +-K-+ -'
'-YES-' +-M-+
'-G -'
 
> -- + ------------------------------------ + ----------------------->
'-INCREASESIZE -- integer -- +-PERCENT-+ -'
'-+-K-+ ---'
+-M-+
'-G -'
 
> -- + ----------------------------- + ------------------------------ |
'-MAXSIZE -- +-integer -- +-K-+ -'
| +-M-+ |
| '-G-' |
'-NONE -----------'
 
System-containers
.----------------------------------------------------------------------.
|.-, ------------------. |
V |
| ---- USING -- (---- 'iner iner-string'-+ --) -- + --------------------------- +-+ -- |
'-| On-db-partitions-clause | -'
 
Database-containers
.--------------------------------------------------------------.
V |
| ---- USING -- | container-clause | -- + ----------------------------- +-+ -- |
'-| On-db-partitions-clause | -'
 
Container-clause
 
.-,---------------------------------------------------.
V |
| -- (---- +-FILE --- + -- 'iner iner-string' -- +-number-of-pages-+ --) -- |
'-DEVICE-''-integer -- +-K-+ --'
+-M-+
'-G -'
 
On-db-partitions-clause
 
| -- ON -- +-DBPARTITIONNUM -- + ---------------------------------------->
'-DBPARTITIONNUMS -'
 
.-,--------------------------------------------------.
V |
> -- (---- Db-partition-number1 -- + -------------------------- +-+ --) -- |
'- TO -- db-partition-number2 -'
 
 
Parameter Details
1. LARGE, REGULAR, system temporary, or USER TEMPORARY
Specifies the type of the tablespace to be created. If not specified, the default type is determined BY the "managed by" clause.
LARGE:
Only permanent data is stored. This type can only be used in DMS tablespaces. It is also the default type for creating DMS tablespace. When a table is placed in a large tablespace:
This table can be larger than the tablespace placed in the regular type.
This table supports more than 255 rows of records per data page, which can increase the space utilization.
The index on this table requires an additional 2 bytes per line, compared with the index on the regular tablespace.
REGULAR:
Only permanent data is stored. This type can be used in DMS and SMS tablespaces. This type is also the only type that can be used in the SMS tablespace, and is also the default type of SMS.
SYSTEM TEMPORARY
A workspace that stores temporary tables and completes operations such as sorting and joining. A database has at least one temporary system tablespace, because the temporary table can only be placed in this tablespace. A temporary tablespace is automatically created when a database is created. Because temporary tablespace involves space recovery, SMS tablespace is often used.
USER TEMPORARY
Store the created temporary tables and declared temporary tables. When a database is created, no temporary tablespace exists. To allow definition of temporary tables and declaration of temporary tables, at least one user temporary tablespace is created by using USE privileges. Because temporary tablespace involves space collection, SMS tablespace is often used.
Note: When multiple temporary tablespaces exist, they are balanced in a circular manner.
2. tablespace-name
 
The name of the tablespace. This is an overall name and an SQL identifier. You cannot use the name of a tablespace that exists in the catalog or start with 'sys. The tablespace owner creates a tablespace. For this tablespace, the USE privilege with the with grant option is granted.
3. in database partition group db-partition-group-name
Specify a database partition group for the tablespace. The database partition group must already exist. When creating a temporary system tablespace, the only allowed partition group is IBMTEMPGROUP.
The keyword 'database PARTITION Group' is not mandatory and optional, and does not affect the parameter.
If you do not specify a partition group when creating a tablespace, use IBMDEFAULTGROUP for the Regular, Large, and User Temporary types; Use the IBMTEMPGROUP type for the system temporary type.
4. PAGESIZE integer [K]
Define the page size for the tablespace. If the suffix K is not specified, the valid values are 4096,8192, 16384,32768. If the suffix K is available, the valid values are, 16, and 32. If these are not valid values, an error is returned. When the page size of the tablespace is different from the page size of the specified buffer pool, an error is returned. The default value of pagesize can be found in db cfg. this parameter is set during database creation.
5. MANAGED BY AUTOMATIC STORAGE
The specified tablespace is the automatically stored tablespace. If the database does not define automatic storage, an error is returned. You can specify Automatic Storage for SMS or DMS.
| Size-attributes | statement Block
AUTORESIZE: Specifies whether the auto-resize function is enabled for the DMS tablespace. When the tablespace is full, the size is automatically increased. For other parameters, see the preceding syntax diagram.
6. MANAGED BY SYSTEM
The specified tablespace is SMS, and the system manages the tablespace. If type is not specified, the default action is to create a regular tablespace.
| System-containers | syntax block, which specifies the container of the SMS tablespace.
USING ('iner iner-string ',...)
For SMS tablespace, specify one or more containers to store data. The length of container-string exceeds 240 bytes. Each container-string can be an absolute or relative path.
Note: If the relative path is used, it is relative to the database directory and can be a soft connection. If the specified folder does not exist, the database manager automatically creates the folder. If the tablespace is deleted, all components created by the database manager are also deleted. If the specified folder already exists, the folder cannot contain any files or subdirectories.
The container-string format depends on the operating system. In windows, the absolute path starts with the drive letter and:. in Unix, the absolute path starts. The relative path format is the same.
You can also support remote resources, such as LAN-redirected drives or NFS-mounted file systems.
| On-db-partitions-clause | specify the container in a partitioned database. If not specified, create a partition in the partition group. If it is a temporary system table, it is created in each partition.
7. MANAGED BY DATABASE
The specified tablespace is DMS, and the database manages the tablespace. If the type is not specified, the default value is large.
| Database-containers | syntax block, which specifies a container for the DBS tablespace.
USING | container-clause |
(FILE | DEVICE 'iner iner-string' number-of-pages ,...)
For DMS tablespace, specify one or more containers to store data. Container Type (FILE or DEVICE) and size (No unit, default is the number of pages) need to be specified. You can also specify the unit, K, M, and G. If the unit is specified, remove the pagesize with the number of cell lines and then perform the rounded down operation to determine the number of pages. Container-stiring cannot exceed 254 bytes.
The FILE container must be an absolute or relative path. If the directory does not contain any components, it will be created by the Database Manager. If the file does not exist, it is created and initialized to the specified size. When the tablespace is deleted, all components are also deleted. If the file exists, it will be overwritten. If the file is smaller than the specified size, it will be extended. If it is larger than the specified size, it will not be truncated.
For the DEVICE container, container-string must be the DEVICE name and the DEVICE must already exist.
All containers must be unique in all databases. A container can belong to only one tablespace. The size of different containers in a tablespace can be different. However, the size of containers in a tablespace is the same, which can achieve better performance. The exact format of container-string is related to the operating system.
Remote resources are also supported, same as SMS.
| On-db-partitions-clause |, same as SMS.
8. EXTENTSIZE number-of-pages
Specify an EXTENTSIZE. The number of pages to be written to the current container before redirecting to the next container.
You can also specify the Unit K, M, and the final extent size as the number of bytes in addition to the pagesize value. The Database Manager cyclically uses containers to store data based on the extent size. According to the principle of circular writing to containers by DB2, if you choose to implement disk strip splitting and DB2 strip splitting, the extended data block size of the tablespace should be the same as that of the disk, this will optimize the performance.
The default value of EXTENTSIZE can be found in db cfg. DFT_EXTENT_SZ is set when DB is created. The valid value is 2-256.
9. PREFETCHSIZE
Retrieve the data required for the query before the query, so that you do not need to wait for I/O.
The default value is determined by dft_prefetch_sz in db cfg.
The default value of prefetch size is AUTOMATIC update, which is controlled by the Database Manager. It is automatically adjusted when the container is added.
Number-of-pages specifies the number of prefetch pages, which can also be measured in bytes.
10. BUFFERPOOL bufferpool-name
Specify the buffer pool used by the tablespace. The buffer pool must already exist. If not specified, the default buffer pool (ibmdefabp bp) will be used. The page size of the buffer pool must be the same as that of the tablespace.
11. OVERHEAD number-of-milliseconds
Specify the I/O controller overhead and disk seek and delay time. This value is used to determine the I/O overhead during query optimization. It can be any number, including floating point. If the container OVERHEAD value of the table is empty, the average value is obtained. Db2 v9 or above. The default value is 7.5. It was upgraded from v9, and the default value is 12.67.
12. file system caching or NO FILE SYSTEM CACHING
Specifies whether I/O operations are cached at the operating system level. If this parameter is not specified, the default value is as follows:
In JFS on AIX, Linux System z®, All non-VxFS file systems on Solaris, HP-UX, SMS temporary tablespace, all LOB and large data, all use file system caching by default. In all other platforms and FILE systems, no file system is used by default.
File system caching's I/O operations on the target tablespace are cached at the operating SYSTEM level.
No file system caching specifies that the I/O operation bypasses the operating SYSTEM cache.
13. TRANSFERRATE number-of-milliseconds
Specifies the time for reading a page to the memory. This value is used to determine the I/O cost during query optimization. It can be a floating point number. If the value of each container is different, the average value is obtained. The database created in versions earlier than V9. The default value is 0.06 milliseconds. After the database is upgraded from V9 to V9, the default value is 0.18 milliseconds.
14. DROPPED TABLE RECOVERY
Indicates whether the deleted table can be recovered. You can use the rollforward database... Recover dropped table ".
ON: Specifies the table to be deleted and can be recovered. This is the default value after V8.
OFF. The deleted table cannot be recovered. The default value of V7.
 
Daily tablespace management commands
In this example, most of the created tablespace pagesize is 32 KB, so
1. Create a bufferpool with a pagesize of 32 KB.
Create bufferpool bp32k size 2000 pagesize 32 k
2. View bufferpool attributes
Select * from syscat. bufferpools
3. Change the buffer pool size
Alter bufferpool bp32k size 2000
 
 
4. Create a 32 KB data block on the data page. The pre-value of KB is default, and the large DMS tablespace cached by the file system is not used.
 
 
Create large tablespace tbs_data pagesize 32 k managed by database using (file '/database/tbs_data/cont0' 100 M, file'/database/tbs_data/cont1 '100 M) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching
 
 
5. Create a temporary SMS System tablespace with a data page of 32 KB
Create temporary tablespace tbs_temp pagesize 32 k managed by system using ('/database/tbs_temp') bufferpool bp32k
 
 
 
6. Create a temporary SMS user tablespace with a data page of 32 KB
 
Create user temporary tablespace tbs_user_temp pagesize 32 k managed by system using ('/database/tbs_usertemp') bufferpool bp32k
7. Create a 32 KB Automatic Storage Management tablespace on the data page
Create tablespace tbs_index pagesize 32 k bufferpool bp32k
 
8. Create an Automatic Storage Management tablespace with an initial size of 100 MB, an incremental value of 100 MB, and a maximum size of GB
Create tablespace tbs_data2 initialsize 100 M increasesize 100 M maxsize 100G
9. View tablespace Information
List tablespaces
List tablespaces show detail
Db2pd-d testdb-tablespaces
Get snapshot for tablespaces on testdb
You can also view the sysibmadm. snaptbsp and sysibmadm. snapcontainer views.
10. View container information related to table null
List tablespace containers for 7 # tablespace id
List tablespace containers for 7 show detail
11. If there is still unallocated space in the storage of the DMS tablespace, you can use the extend, reduce, or resize option of alter tablespace to expand the size of the existing tablespace container. Extend is used to expand the container size, reduce is used to reduce the existing container size, and resize is used to reset the container size. For reduce and resize, make sure that the changed tablespace has sufficient space. Otherwise, DB2 rejects the operation.
Alter tablespace tbs_data extend (file '/database/tbs_data/cont0' 10 M)
12. If the tablespace container has no space left, you can use the add option to add the container. If necessary, you can also use the drop option to delete the container. The add and drop operations rebalance data between containers ). If the data volume is large, the rebalance may take a long time, which may have a great impact on the system.
 
Alter tablespace tbs_data add (file '/database/tbs_data/cont2' 100 m)
13. If you use the begin new strip set option, the new container is used when the existing container space is used up. This option prevents data from being rebalance between containers, but may cause data offset.
Alter tablespace tbs_data begin new stripe set (file '/database/tbs_data/cont3' 100 m)
 
14. view the tablespace path for Automatic Storage Management
Db2pd-d testdb-storagepaths
15. For automatic storage and management tablespaces, container changes cannot be made at the tablespace level, but only at the data level, because the Automatic Storage path is specified at the time of database creation. You can use the add storage on option to add a new storage path for the database. Before v9.7, the automatic storage path can only be added and cannot be deleted. The newly added storage path does not use the backup tablespace immediately. The new path is used only when the file system space in the existing storage path is full. The new path is added only to solve the capacity problem. After v9.7, the new storage path can be used immediately as long as the tablespace managed by automatic storage is rebalance.
 
Alter database testdb add storage on '/database/czm'
Alter tablespace 7 rebalance
 
 
16. Convert the DMS tablespace to the automatic storage and management tablespace.
Alter tablespace tbs_data managed by automatic storage
Alter tablespace tbs_data rebalance
 
 
 
Reference: Xu Mingwei, DB2 database best management practices
Http://www.ibm.com/developerworks/cn/data/library/techarticles/0212wieser/0212wieser.html
Http://www.bkjia.com/database/201201/116270.html

This article is original. For more information, see the source and author.
If any error occurs, please correct it.
Email: czmcj@163.com