Using database partition expressions
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.
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.
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:
The following containers are created:CREATE TABLESPACE TS1 MANAGED BY DATABASE USING (device '/dev/rcont $N' 20000)
/dev/rcont0 - on database partition 0 /dev/rcont1 - on database partition 1
- On a system with three database partitions:
The following paths are added:ALTER STOGROUP IBMSTOGROUP ADD '/DB2/path $N'
/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:
The following containers are created:CREATE TABLESPACE TS2 MANAGED BY DATABASE USING (file '/DB2/containers/TS2/container $N+100' 10000)
/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:
The following containers are created:CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING ('/TS3/cont $N%2','/TS3/cont $N%2+2')
/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:
The containers are created as:'/dbdir/node $N /cont1' '/ $N+1000 /file1' ' $N%10 /container' '/dir/ $N2000 /dmscont'
'/dbdir/node5/cont1' '/1005/file1' '5/container' '/dir/2000/dmscont'