onspaces -c -d: Create a dbspace

Use the onspaces -c -d command to create a dbspace or a temporary dbspace.

Figure 1: Syntax
(1)
Notes:
  • 1 Windows™ Only
Element Purpose Key considerations
-c Creates a dbspace

You can create up to 2047 storage spaces of any type.

After you create a storage space, you must back up both this storage space and the root dbspace. If you create a storage space with the same name as a deleted storage space, perform another level-0 backup to ensure that future restores do not confuse the new storage space with the old one.

For more information, see Manage dbspaces.

drive Specifies the Windows drive to allocate as unbuffered disk space

The format can be either \\.\drive, where drive is the drive letter that is assigned to a disk partition, or \\.\PhysicalDrivenumber, where PhysicalDrive is a constant value and number is the physical drive number.

For information on allocating unbuffered disk space, see Allocating raw disk space on Windows.
Examples:
\\.\F:
\\.\PhysicalDrive2

For path name syntax, see your operating-system documentation.

-d dbspace Names the dbspace to be created The dbspace name must be unique and cannot exceed 128 bytes. It must begin with a letter or underscore and must contain only letters, numbers, underscores, or the $ character.

For more information, see Manage dbspaces. The syntax must conform to the Identifier segment. For more information, see Identifier.

-ef extentsize Indicates, in KB, the size of the first extent for the tblspace tblspace The minimum, and default, size of the first extent for the tblspace tblspace of a non-root dbspace is equivalent to 50 dbspace pages, which are specified in KB. For example: 100 KB for a 2 KB page size dbspace, 200 KB for a 4 KB page size dbspace, 400 KB for an 8 KB page size dbspace.

The maximum size of a tblspace tblspace extent is 1048575 pages minus the space that is needed for any system objects. On a 2 KB page size system, the maximum size is approximately 2 GB.

For more information, see Specifying the first and next extent sizes for the tblspace tblspace.

-en extentsize Indicates, in KB, the size of the next extents in the tblspace tblspace The minimum size of the next extents for the tblspace tblspace of a non-root dbspace is equivalent to 4 dbspace pages, which are specified in KB. For example: 8 KB for a 2 KB page size dbspace, 16 KB for a 4 KB page size dbspace, 32 KB for an 8 KB page size dbspace.

The default size for a next extent is 50 dbspace pages.

The maximum size of a tblspace tblspace extent is 1048572 pages. On a 2 KB page size system, the maximum size is approximately 2 GB.

If there is not enough space for a next extent in the primary chunk, the extent is allocated from another chunk. If the specified space is not available, the closest available space is allocated.

For more information, see Specifying the first and next extent sizes for the tblspace tblspace.

-k pagesize Indicates in KB, the non-default page size for the new dbspace.
For systems with sufficient storage, performance advantages of a larger page size can include the following:
  • Reduced depth of B-tree indexes, even for smaller index keys
  • You can group on the same page long rows that currently span multiple pages of the default page size
  • Checkpoint time is typically reduced with larger pages
  • You can define a different page size for temporary tables so that they have a separate buffer pool.
The page size must be between 2 KB and 16 KB and must be a multiple of the default page size. For example, if the default page size is 2 KB, then pagesize can be 2, 4, 6, 8, 10, 12, 14, or 16. If the default page size is 4 KB (Windows), then pagesize can be 4, 8, 12, or 16.

For more information, see Creating a dbspace with a non-default page size.

-m pathname offset Specifies an optional path name and offset to the chunk that mirrors the initial chunk of the new dbspace

Also see the entries for -p pathname and -o offset in this table.

For more information, see Manage dbspaces.
-o offset Indicates, in KB, the offset into the disk partition or into the device to reach the initial chunk of the new dbspace

Unsigned integer. The starting offset must be equal to or greater than 0. The starting offset plus the chunk size cannot exceed the maximum chunk size. The offset must be a multiple of the page size. The maximum offset is 2 or 4 TB, depending on the platform.

For more information, see Allocating raw disk space on Windows.

-p pathname Indicates the disk partition or device of the initial chunk of the dbspace that you are creating The chunk must be an existing unbuffered device or buffered file. When you specify a path name, you can use either a full path name or a relative path name. However, if you use a relative path name, it must be relative to the directory that was the current directory when you initialized the database server. UNIX™ example (unbuffered device): /dev/rdsk/c0t3d0s4 UNIX example (buffered device): /ix/ids9.2/db1chunk Windows example:c:\Ifmxdata\ol_icecream\mychunk1.dat

For path name syntax, see your operating-system documentation.

-s size Indicates, in KB, the size of the initial chunk of the new dbspace Unsigned integer. The size must be equal to or greater than 1000 KB and a multiple of the page size. The starting offset plus the chunk size cannot exceed the maximum chunk size.

The maximum chunk size is 2 or 4 TB, depending on the platform.

-t Creates a temporary dbspace for storage of temporary tables You cannot mirror a temporary dbspace. You cannot specify the first and next extent sizes for the tblspace tblspace of a temporary dbspace.

For more information, see Temporary dbspaces.

-u Specifies to create an unencrypted space Use this option to create an unencrypted storage space when encryption is enabled by the DISK_ENCRYPTION configuration parameter.

The maximum size of a dbspace is equal to the maximum number of chunks multiplied by the maximum size of a chunk. (The maximum number of chunks is 32766 per instance. The maximum size of a chunk is equal to 2147483647 pages multiplied by the page size.)

This command has an equivalent SQL administration API function.

You cannot change the page size of a dbspace after you create it.

You cannot store logical or physical logs in a dbspace that is not the default platform page size.

If a dbspace is created when a buffer pool with that page size does not exist, HCL OneDB™ creates a buffer pool using the values of the fields of the default line of the BUFFERPOOL parameter.

Temporary dbspaces

When you create a temporary dbspace with onspaces, the database server uses the newly created temporary dbspace, after you add the name of the new temporary dbspace to your list of temporary dbspaces in the DBSPACETEMP configuration parameter, the DBSPACETEMP environment variable, or both and restart the server.

You cannot specify the first and next extent of a temporary dbspace. The extent size for temporary dbspaces is 100 KB for a 2 KB page system or 200 KB for a 4 KB page system.

You can specify the first and next space of the tblspace tblspace in the root dbspace if you do not want the database server to automatically manage the size. To specify the first and next extent sizes of a root tblspace tblspace, use the TBLTBLFIRST and TBLTBLNEXT configuration parameters before you create the root dbspace the first time that you start the database server.