版次注意事項


|7.3 Chapter 3. Creating a Database

|7.3.1 Creating a Table Space

|7.3.1.1 Using Raw I/O on Linux

|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 named raw, which is normally |supplied by the Linux distributor.

|Before you set up raw I/O on Linux, you require the following: |

|

|Note:
|Of the distributions currently supporting raw |I/O, the naming of raw device nodes is different:
|Distribution  Raw device nodes      Raw device controller 
|------------  --------------------  --------------------- 
|RedHat 6.2    /dev/raw/raw1 to 255  /dev/rawctl 
|SuSE 7.0      /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. |

|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
 
     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 global read permissions on the raw device controller and the disk partition. Set global read and write permissions on the raw device:

# chmod a+r /dev/rawctl
# chmod a+r /dev/sdb1
# chmod a+rw /dev/raw/raw1

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.

|7.3.2 Creating a Sequence

|

|Following the section titled "Defining an Identity Column on a New Table," add the following section, "Creating a Sequence":

|A sequence is a database object that allows the |automatic generation of values. Sequences are ideally suited to the task of |generating unique key values. Applications can use sequences to avoid possible |concurrency and performance problems resulting from the generation of a unique |counter outside the database.

|Unlike an identity column attribute, a sequence is not tied to a particular |table column nor is it bound to a unique table column and only accessible |through that table column.

|A sequence can be created, or altered, so that it generates values in one |of these ways: |

|The following is an example of creating a sequence object:

|   CREATE SEQUENCE order_seq
|      START WITH 1
|      INCREMENT BY 1
|      NOMAXVALUE
|      NOCYCLE
|      CACHE 24

|In this example, the sequence is called order_seq. It will |start at 1 and increase by 1 with no upper limit. There is no reason to cycle |back to the beginning and restart from 1 because there is no assigned upper |limit. The number associated with the CACHE parameter specifies |the maximum number of sequence values that the database manager preallocates |and keeps in memory.

|The sequence numbers generated have the following properties: |

|If a database that contains one or more sequences is recovered to a prior |point in time, then this could cause the generation of duplicate values for |some sequences. To avoid possible duplicate values, a database with sequences |should not be recovered to a prior point in time.

|Sequences are only supported in a single node database.

|There are two expressions used with a sequence. | | | |

|The PREVVAL expression returns the most recently generated value for the |specified sequence for a previous statement within the current session.

|The NEXTVAL expression returns the next value for the specified sequence. |A new sequence number is generated when a NEXTVAL expression specifies the |name of the sequence. However, if there are multiple instances of a NEXTVAL |expression specifying the same sequence name within a query, the counter for |the sequence is incremented only once for each row of the result.

|The same sequence number can be used as a unique key value in two separate |tables by referencing the sequence number with a NEXTVAL expression for the |first table, and a PREVVAL expression for any additional tables.

|For example:

|   INSERT INTO order (orderno, custno)
|      VALUES (NEXTVAL FOR order_seq, 123456);
|   INSERT INTO line_item (orderno, partno, quantity)
|      VALUES (PREVVAL FOR order_seq, 987654, 1)

|The NEXTVAL or PREVVAL expressions can be used in the following locations: |

|7.3.3 Comparing IDENTITY Columns and Sequences

|Following the new section titled "Creating a Sequence", add the following |section: | |

|While there are similarities between IDENTITY columns and sequences, there |are also differences. The characteristics of each can be used when designing |your database and applications.

|An identity column has the following characteristics: |

|A sequence object has the following characteristics: |

|While these are not all of the characteristics of these two items, these |characteristics will assist you in determining which to use depending on |your database design and the applications using the database.

|7.3.4 Creating an Index, Index Extension, or an Index Specification

|Within the section titled "Creating an Index, Index Extension, or an |Index Specification", add the following note in the paragraph beginning |with the sentence: "Any column that is part of an index key is limited |to 255 bytes." |

|Note:
The DB2_INDEX_2BYTEVARLEN registry variable can be used to |allow columns with a length greater than 255 bytes to be specified as part |of an index key. |


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]