DB2 · CREATE TABLESPACE

Source: Internet
Author: User
Tags prefetch

The create tablespace statement defines the tablespace, assigns containers to the tablespace, and records the table space definitions and attributes in the catalog.
Permission: SYSCTRL or SYSADM
 
Syntax:
 
>-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 -'
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.
 
USER TEMPORARY
Store the created temporary tables and declared temporary tables. When a database is created, no temporary tablespace exists. To allow defining temporary tables and declaring temporary tables, at least one user temporary tablespace is created by using USE privileges.
 
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.
 
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. 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.
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.
AUTOMATIC
The specified prefetch size is automatically updated and is controlled by the Database Manager. It is automatically adjusted when the container is added.
Number-of-pages
Specifies the number of prefetch pages.
The unit can also be used to automatically calculate the number of pages 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
I/O operations on the target tablespace are cached at the operating system level.
NO FILE SYSTEM CACHING
The specified 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.
 
PS:
1. When there are multiple temporary tablespaces, use a circular method to balance their usage.
2. the tablespace owner creates the tablespace and is granted the USE privilege with the with grant option in this tablespace.
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.