By placing a table space in a multiple-partition database partition group, all of the tables within the table space are divided or partitioned across each
partition in the database partition group. The table space is created into a database partition group. Once in a database partition group, the table space must remain there; it cannot be changed to another database partition group.
The CREATE TABLESPACE statement is used to associate a table space with a database partition group.
Related reference:
v “CREATE TABLESPACE statement” in the SQL Reference, Volume 2
Specifying raw I/O
When working with containers to store data, DB2 Universal Database supports direct disk access (raw I/O). This type of support allows you to attach a direct disk access (raw) device to any DB2 Universal Database system. (The only exception is the Windows 9x operating system.) Prerequisites:
You must know the device or file names of the containers you are going to reference when creating your table spaces. You must know the amount of space associated with each device or file name that is to be allocated to the table space.
You will need the correct permissions to read and write to the container.
Procedure:
The following list demonstrates the physical and logical methods for identifying a direct disk access type of device:
v On Windows, to specify a physical hard drive, use the following syntax:
\\.\PhysicalDriveN
where N represents one of the physical drives in the system. In this case, N could be replaced by 0, 1, 2, or any other positive integer:
\\.\PhysicalDrive5
v On Windows, to specify a logical drive (that is, an unformatted partition) use the following syntax:
\\.\N:
where N: represents a logical drive letter in the system. For example, N:
could be replaced by E: or any other drive letter. To overcome the limitation imposed by using a letter to identify the drive, you can use a globally unique identifier (GUID) with the logical drive.
v Note: You must have Windows NT Version 4.0 with Service Pack 3 or later installed to be able to write to a device.
v On UNIX-based platforms, use the character serial device name; for example,/dev/rhd0
For Windows 2000 and above, there is a new method for specifying DMS raw table space containers. Volumes (that is, basic disk partitions or dynamic volumes) are assigned a globally unique identifier (GUID) when they are created. The GUID can be used as a device identifier when specifying the containers in a table space definiton. The GUIDs are unique across systems which means that in a multiple partitioned database configuration, GUIDs are different for each partition even if the disk partition defintions are the same.
A tool called db2listvolumes.exe is available (only on Windows operating systems) to make it easy to display the GUIDs for all the disk volumes defined on a Windows system. This tool creates two files in the current directory where the tool is run. One file, calledvolumes.xml, contains
information about each disk volume encoded in XML for easy viewing on any XML-enabled browser. The second file, calledtablespace.ddl, contains the required syntax for specifying table space containers. This file must be updated to fill in the remaining information needed for a table space definition. The db2listvolumes tool does not require any command line arguments.
Related tasks:
v “Setting up raw I/O on Linux” on page 90
Setting up raw I/O on Linux
When working with containers to store data, DB2 Universal Database supports direct disk access (raw I/O). This type of support allows you to attach a direct disk access (raw) device to any DB2 Universal Database system. There is specific information while working in a Linux environment.
Prerequisites:
You must know the device or file names of the containers you are going to reference when creating your table spaces. You must know the space associated with each device or file name that is to be allocated to the table space.
Before you set up raw I/O on Linux, you require the following:
v One or more free IDE or SCSI disk partitions
v A raw device controller named /dev/rawctl or /dev/raw. If not, create a symbolic link:
# ln -s /dev/your_raw_dev_ctrl /dev/rawctl
v The raw utility, which is usually provided with the Linux distribution Note: Of the distributions currently supporting raw I/O, the naming of raw
device nodes is different:
Table 3. Linux distributions supporting raw I/O.
Distribution Raw device nodes Raw device controller
RedHat or TurboLinux /dev/raw/raw1 to 255 /dev/rawctl
SuSE /dev/raw1 to 63 /dev/raw
DB2 supports either of the above raw device controllers, and most other names for raw device nodes. Raw devices are not supported by DB2 on Linux/390.
Procedure:
Linux has a pool of raw device nodes that must be bound to a block device before raw I/O can be performed on it. There is a raw device controller that acts as the central repository of raw to block device binding information.
Binding is performed using a utility namedraw, which is normally supplied by the Linux distributor.
To configure raw I/O on Linux:
In this example, the raw partition to be used is/dev/sda5. It should not contain any valuable data.
Step 1. Calculate the number of 4 096-byte pages in this partition, rounding down if necessary. For example:
# fdisk /dev/sda Command (m for help): p
Disk /dev/sda: 255 heads, 63 sectors, 1106 cylinders Units = cylinders of 16065 * 512 bytes
Table 4. Linux raw I/O calculations.
Device boot Start End Blocks Id System
/dev/sda1 1 523 4200997 83 Linux
/dev/sda2 524 1106 4682947+ 5 Extended
/dev/sda5 524 1106 4682947 83 Linux
Command (m for help): q
#
The number of pages in /dev/sda5 is
num_pages = floor( ((1106-524+1)*16065*512)/4096 ) num_pages = 11170736
Step 2. Bind an unused raw device node to this partition. This needs to be done every time the machine is rebooted, and requires root access.
Use raw -a to see which raw device nodes are already in use:
# raw /dev/raw/raw1 /dev/sda5
/dev/raw/raw1: bound to major 8, minor 5
Step 3. Set appropriate read permissions on the raw device controller and the disk partition. Set appropriate read and write permissions on the raw device.
Step 4. Create the table space in DB2, specifying the raw device, not the disk partition. For example:
CREATE TABLESPACE dms1 MANAGED BY DATABASE
USING (DEVICE ’/dev/raw/raw1’ 11170736)
Table spaces on raw devices are also supported for all other page sizes supported by DB2.
Related tasks:
v “Specifying raw I/O” on page 89