SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-18
Creating Tables on a System That Uses SMF
Creating Tables on a System That Uses SMF
If you are running SQL/MP on a system using the SMF product, you can specify a
virtual volume for a table. The virtual volume is associated with a storage pool; SMF
places the table on a physical volume in that storage pool. SMF chooses a physical
location based on its size estimate of the file and on the available space in the pool.
In exceptional cases, you might want a file to reside on a particular physical volume. To
accomplish this, you can specify a direct volume that is not in any storage pool, or you
can specify a virtual volume for the table and use the PHYSVOL option to select a
particular physical volume in the pool.
If you do need to specify a physical volume for a file, it can be advantageous to use the
PHYSVOL option rather than specifying a direct volume outside the control of SMF. If
you use the PHYSVOL option, you can move the file to a different physical location in
the future without having to recompile query execution plans or change other SQL
objects (such as indexes, views, and catalog tables) that refer to the file. You can move
the file even if a referencing object—an index, for example—is unavailable.
However, if the file is on a direct volume and you move the file with the ALTER TABLE.
. . MOVE statement, SQL changes the external references to the file. In this situation,
the referencing objects must be available.
This example creates a table on a virtual volume, $VIR1, associated with a SMF pool.
The example specifies that the file reside on the physical volume, $PVOL3:
>> CREATE TABLE $VIR1.MFG.ORDERS
+> (ORDERNUM DECIMAL (6) UNSIGNED NO DEFAULT NOT NULL,
+> |
+> PRIMARY KEY ORDERNUM)
+> PHYSVOL $PVOL3
+> |
+> ORGANIZATION KEY SEQUENCED;
--- SQL operation complete.
In the preceding example, $VIR1 must be associated with the storage pool that
contains $PVOL3. When you create a table name on a virtual volume such as $VIR1,
you use the same syntax you would use for volumes not managed by SMF. However,
with the PHYSVOL option, you specify only the volume name (such as $PVOL3). SQL
returns an error if you specify a full volume.subvolume.file name with the
PHYSVOL option. If you omit the PHYSVOL option, SMF determines the physical
volume on which the ORDERS table resides.
For more information about using this feature, see the SQL/MP Reference Manual and
the Storage Management Foundation User’s Guide.