Creating and Editing Tablespaces
Go Up to Creating and Editing Database Dependent Objects
Contents
A tablespace is the physical location where the data in a database is stored. Using tablespaces, the administrator can control disk layout to optimize database performance, by for example storing a frequently used index on a fast disk. The manner in which space is allocated to database objects directly impacts their ability to grow, which can ultimately affect database performance.
The following database platforms support tablespaces:
- IBM DB2 for LUW 5.x, 6.x, 7.x, 8.x, 9.x, 10.x
- IBM DB2 for OS/390 5.x and 6.x and for z/OS 7.x, 8.x, 9.x, 10.x, and 11.x
- Oracle 7.x, 8.x, 9i, 10g, 11g and 12c
- The Tablespace Wizard and Tablespace Editor share the same options, except for Attachment Bindings options which are present only in the editor.
Create a TableSpace
- In the Data Model Explorer, expand the Physical Main Model, right-click the Tablespaces node, and then click New Tablespace.
- Complete the Tablespace Wizard and then click Finish to create the alias.
- Once you have created the tablespace, you can edit it by right-clicking the tablespace you want to change, and then selecting Edit Tablespace.
The options available depend on the database platform selected.
- Many of the table space parameters are also used when defining table storage. For information on defining storage parameters not described here, see Defining Table Storage.
The following helps you complete the options of the Tablespace Wizard:
Name & Type page/tab
- Choose the type of data to store in the tablespace:
- Regular: Choose for all data types except for temporary tables.
- Long: Choose for long or LOB tables columns, or for structured type columns or index data. The table space must be a DMS table space.
- Temporary: Choose if you want to store temporary data. A database must have at least one temporary table space to store temporary tables and another that allows the declaration of temporary tables.
- Universal (Range-partitioned): A range-partitioned tablespace is segmented and based on partitioning ranges. It contains a single table and you can create any type of index on tables in the range-partitioned tablespace. If you specify this option, you must also specify the Segment Size and the number of partitions. You can either accept the default number of rows per page or specify a different number.
Name page/tab
- Do you want space management to be performed through the dictionary or locally in the tablespace? Specify how extents should be managed.
- Dictionary Managed: Relies on SQL dictionary tables to track space utilization. Adding an extent to a table requires the database manager to find free space, mark it as non-free and potentially causes rollback segments to extend. These operations may jeopardize database performance because they cannot be performed simultaneously. However, dictionary managed tablespaces are very flexible, different objects sharing the same tablespace can have different extent sizes.
- Locally Managed: Eliminates the problem of tablespace fragmentation, and potential bottlenecks associated with the data dictionary. The database manager maintains a bitmap of free and used blocks, or sets of blocks. Locally managed tablespaces can be used for temporary tablespaces and can often sustain high extent-allocation and deallocation activities. If a table is created with the wrong extent size, causing tablespace fragmentation, this type of tablespace management can enforce extent uniformity at the tablespace level.
- Disable logging when schema/data is modified: If selected, disables logging when schema or data is modified.
- Place tablespace offline after creation: If selected, places tablespace offline after creation.
- AutoAllocate Extents: If selected, Oracle automatically assigns and allocates appropriately sized extents depending on the objects in the tablespace.
- Uniform Extent Size: If selected, extent sizes are managed at the tablespace level, not at the object level. The extent size is specified when the tablespace is created and all the extents for objects created in the tablespace will be the same.The uniform extent size cannot be overridden when a schema object, such as a table or an index, is created. This option may eliminate fragmentation.
Datafile page/tab
- Defined Datafiles: Double-click the Size or Unit fields to edit them. Click Add only when you need to define another datafile.
- Reuse Existing File: If Use Oracle Managed Files to automatically generate Defaults is not enabled, this option is available. If selected, lets you reuse an existing datafile, which the database manager overwrites the first time the file is accessed.
Storage page/tab
- If the table space of an Oracle table space is dictionary managed, as specified on the Name tab of the editor, then define the parameters of the extents on this tab.
- Specify the containers for the tablespace below: Specify an absolute or relative directory name. The relative path is relative to the database directory. If the directory name does not exist, it will be created.
- Click Add only if you want to specify another container.
- Select a NodeGroup to assign to the tablespace: Specify either a nodegroup or a database partition number.
- Select a page size: The page size must be the same as the page size of the buffer pool that is associated with the database.
Options page/tab
- Extent Size: Specify the number of pages that will be written to a container before writing to the next container. The database manager cycles through the containers as it stores data. The default extent size is specified by the database configuration parameter DFT_EXTENT_SZ.
- Prefetch Size: Specify the number of pages that will be read when prefetching tablespace data. The default prefetch size is specified by the database configuration parameter DFT_PREFETCH_SZ.
- Overhead: Specifies the overhead, disk seek, and latency time of the I/O controller. This should be an average for all containers belonging to the tablespace. This value is used during query optimization to determine the I/O cost.
- Transfer Rate: Specifies the time required to read one page into memory. This should be an average for all containers belonging to the tablespace. This value is used during query optimization to determine the I/O cost.
- Select the recovery status: If ON is selected, dropped tables in this tablespace may be recovered using the ROLLFORWARD command with the RECOVER TABLE ON option.
Options 1 page/tab
- GBP Cache: Specifies a group buffer pool cache.
- Sizing: Specifies the settings for the different Sizing parameters:
- If you are creating a non-partitioned tablespace, enter a value between 1 and 255 in the Max rows per page field.
- If you are creating a partitioned tablespace, enter the number of partitions you want to create for the tablespace (between 1 and 254) in the Number of Partitions field, then select a size for the partitions from the Partition Size (DSSIZE) list. Enter a value between 1 and 255 in the Max rows per page field.
- If you are creating a segmented tablespace, select a value from the Segment Size list, and enter a value between 1 and 255 in the Max rows per page field.
- If you are creating an LOB tablespace, select a value from the Partition Size (DSSIZE) list.
- TRACKMOD: When set to Yes, the database manager tracks database modifications to enable the backup utility to detect which subsets of the database pages should be examined by an incremental backup and included in the backup image if necessary.
Options 2 page/tab
- Locking: To control concurrent access and prevent uncontrolled data access, you can create locks which associates a database manager resource with an application, called the lock owner, to control how other applications access the same resource. The Lock Size determines the granularity of the locks.
- Maximum Locks: Set this to a low number to avoid contention. Setting LOCKMAX to zero increases tablespace availability by preventing lock escalations which can lock the tablespace. Setting LOCKMAX to SYSTEM allows the database manager to control the maximum number of locks by a setting in the database configuration file.
- Close Rule: Determines whether the data sets can be closed when the table space is not in use, and the limit on the number of open data sets (DSMAX) is reached. If you select No, no data sets can be closed. However, if DSMAX is reached and no CLOSE YES page sets exist, then even CLOSE NO page sets will be closed.
- Partitions: For information on partition parameters, see Partitioning a Table.
Definition page/tab
Enter or edit a definition for the tablespace. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.
DDL page/tab
Displays the CREATE TABLESPACE statement needed to build the tablespace. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the tablespace.
Attachment Bindings page/ tab
Bind an external piece of information, or attachment to the tablespace. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.