DB2 10.5 for Linux, UNIX, and Windows

Using database partition expressions

In most cases, you must use the same storage paths for each partition in a partitioned database environment, and all of the storage paths must exist before you issue a statement. One exception is when you use database partition expressions within the storage path. Doing this allows the database partition number to be reflected in the storage path such that the resulting path name is different on each partition.

You can specify a database partition expression for container string syntax when creating either SMS or DMS containers. You typically specify the database partition expression when using multiple logical database partitions in a partitioned database system. The expression ensures that container names are unique across database partition servers. If you specify an expression, the database partition number is part of the container name or, if you specify additional arguments, the result of the argument is part of the container name.

Important: The SMS table space type has been deprecated in Version 10.1 for user-defined permanent table spaces and might be removed in a future release. The SMS table space type is not deprecated for catalog and temporary table spaces. For more information, see SMS permanent table spaces have been deprecated.

Important: Starting with Version 10.1 Fix Pack 1, the DMS table space type is deprecated for user-defined permanent table spaces and might be removed in a future release. The DMS table space type is not deprecated for catalog and temporary table spaces. For more information, see DMS permanent table spaces have been deprecated.

Use the argument " $N" ([blank]$N) to indicate a database partition expression. You can use a database partition expression anywhere in the storage path name, and you can specify multiple database partition expressions. Terminate the database partition expression with a space character; whatever follows the space is appended to the storage path name after the database partition expression is evaluated. If there is no space character in the storage path name after the database partition expression, it is assumed that the rest of the string is part of the expression. If you specify a number before the N argument, ($[number]N), the partition number is formatted with leading zeros.

You must specify the argument by using one of the forms in the following table. Operators are evaluated from left to right. A percent sign (%) represents the modulus operator. The database partition number in the following examples is assumed to be 10.

Table 1. Database partition expressions
Syntax	Example	Value
[blank]$N	"$N"	10
[blank]$[number]N	"$4N"	0010
[blank]$N+[number]	"$N+100"	110
[blank]$N%[number]	"$N%5"	0
[blank]$N+[number]%[number]	"$N+1%5"	1
[blank]$N%[number]+[number]	"$N%4+2"	4

If you specified a storage path by using a database partition expression, you must use the same storage path string, including the database partition expression, to drop the path. This path string is in the DB_STORAGE_PATH_WITH_DPE field of the ADMIN_GET_STORAGE_PATHS table function. This element is not shown if you did not include a database partition expression in the original path.

Examples

On a system with two database partitions:

CREATE TABLESPACE TS1 MANAGED BY DATABASE USING
     (device '/dev/rcont $N' 20000)

The following containers are created:

     /dev/rcont0 - on database partition 0
     /dev/rcont1 - on database partition 1

On a system with three database partitions:

ALTER STOGROUP IBMSTOGROUP ADD '/DB2/path $N'

The following paths are added:

/DB2/path0 - on database partition 0
/DB2/path1 - on database partition 1
/DB2/path2 - on database partition 2

On a system with four database partitions:

   CREATE TABLESPACE TS2 MANAGED BY DATABASE USING
     (file '/DB2/containers/TS2/container $N+100' 10000)

The following containers are created:

     /DB2/containers/TS2/container100 - on database partition 0
     /DB2/containers/TS2/container101 - on database partition 1
     /DB2/containers/TS2/container102 - on database partition 2
     /DB2/containers/TS2/container103 - on database partition 3

On a system with two database partitions:

   CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING
     ('/TS3/cont $N%2','/TS3/cont $N%2+2')

The following containers are created:

     /TS3/cont0 - On database partition 0
     /TS3/cont2 - On database partition 0
     /TS3/cont1 - On database partition 1
     /TS3/cont3 - On database partition 1

If there are 10 database partitions, the containers use the following syntax:

     '/dbdir/node $N /cont1'
     '/ $N+1000 /file1'
     ' $N%10 /container'
     '/dir/ $N2000 /dmscont'

The containers are created as:

     '/dbdir/node5/cont1'
     '/1005/file1'
     '5/container'
     '/dir/2000/dmscont'