Help with using database partition expressions

[db2dbdba]

Dear DBAs

I'm working on a DPF set up in DB2 LUW on AIX and trying to create a tablespace in 16 partition database (logical) using partition expression (http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.partition .doc%2Fdoc%2Fc0059241.html).
Verison is DB2 v9r7.

CREATE USER TEMPORARY TABLESPACE "TBSPNAME" IN DATABASE PARTITION GROUP PG1_ALL PAGESIZE 16384 MANAGED BY SYSTEM
       USING ('/db2/idbname/dbname/db2 $N /CONT0001/TBSPNAME',
              '/db2/idbname/dbname/db2 $N /CONT0002/TBSPNAME',
              '/db2/idbname/dbname/db2 $N /CONT0003/TBSPNAME',
              '/db2/idbname/dbname/db2 $N /CONT0004/TBSPNAME',
              '/db2/idbname/dbname/db2 $N /CONT0005/TBSPNAME')
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL BP_16K_TMP
         OVERHEAD 7.500000
         TRANSFERRATE 0.060000
         NO FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY OFF;

This is working fine and creating the tablespace with container paths as below. Example for NODE5

'/db2/idbname/dbname/db25/CONT0001/TBSPNAME'         
'/db2/idbname/dbname/db25/CONT0002/TBSPNAME'
'/db2/idbname/dbname/db25/CONT0003/TBSPNAME'
'/db2/idbname/dbname/db25/CONT0004/TBSPNAME'
'/db2/idbname/dbname/db25/CONT0005/TBSPNAME'

However, when I try to create the same using the feature [blank]$[number]N (mentioned below) and the command below. Command is failing with below error
"SQL0294N The container is already in use. SQLSTATE=42730"

The database partition number in the following examples is assumed to be 10.
[blank]$[number]N (Syntax) --> "$4N" (Example) --> 0010 (Value)

 CommandCREATE USER TEMPORARY TABLESPACE "TBSPNAME" IN DATABASE PARTITION GROUP PG1_ALL PAGESIZE 16384 MANAGED BY SYSTEM
       USING ('/db2/idbname/dbname/db2 $4N /CONT0001/TBSPNAME',
              '/db2/idbname/dbname/db2 $4N /CONT0002/TBSPNAME',
              '/db2/idbname/dbname/db2 $4N /CONT0003/TBSPNAME',
              '/db2/idbname/dbname/db2 $4N /CONT0004/TBSPNAME',
              '/db2/idbname/dbname/db2 $4N /CONT0005/TBSPNAME')
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL BP_16K_TMP
         OVERHEAD 7.500000
         TRANSFERRATE 0.060000
         NO FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY OFF;

Moreover the expected paths for this command is below

'/db2/idbname/dbname/db20005/CONT0001/TBSPNAME'         
'/db2/idbname/dbname/db20005/CONT0002/TBSPNAME'
'/db2/idbname/dbname/db20005/CONT0003/TBSPNAME'
'/db2/idbname/dbname/db20005/CONT0004/TBSPNAME'
'/db2/idbname/dbname/db20005/CONT0005/TBSPNAME'

Please help me in identifying the issue and fixing this.

[forrest maxu]

your comand is not right
for example

create regular tablespace TBS_BIGTBL_4K in database partition group pgr01 
   pagesize 4096 managed by database 
   using (DEVICE ' /dbvols/tbs_bigtbl_4k/p0cont1' 38G) on dbpartitionnum (0) 
   using (DEVICE ' /dbvols/tbs_bigtbl_4k/p1cont1' 38G) on dbpartitionnum (1) 
   extentsize 16 prefetchsize 32 bufferpool BP4K
 

[db2dbdba]

hi forrest

Thank you for the reply.
I've used the command with minor modification $N in place of $4N and it worked fine. I believe the command is right and I'm not sure of how to use the partition expression in my requirement and seeking the help here.

[forrest maxu]

Do you want to add the partition?

[db2dbdba]

Hi Forest

I'm trying to create a tablespace in db2 database with dpf enabled.

so my expectation is the as per the IBM link specified, it shall create the containers as per the partition numbers with a maximum of 4 digits ([blank]$[number]N)

[forrest maxu]

[blank]$N " $N" 5
[blank]$N+[number] " $N+1011" 1016
[blank]$N%[number] " $N%3" （% 是模块） 2
[blank]$N+[number]%[number] " $N+12%13" 4
[blank]$N%[number]+[number] " $N%3+20" 22





'/dbdir/node $N /cont1' '/dbdir/node5/cont1'
'/ $N+1000 /file1' '/1005/file1'
' $N%10 /container' '5/container'
'/dir/ $N%5+2000 /dmscont' '/dir/2000/dmscont'

[db2dbdba]

hi Forrest

I'm looking for case where in i shall be able to use only [blank]$[number]N. This would be something like below $4N which means for a partition 5 it would be something like 0005 which is not working as specified in the link i mentioned at the beginning of my post.

Thanks,

[forrest maxu]

$4N is equal your parartition number

you is connectting the db parttion is 5

there is no blank after the $4N