db2 tablespace error on multiple logical partition on one aix machine

[v***@yahoo.com]

Hi Team,

I have been getting error message for creating tablespace on the multiple logical partition database on single aix machine for tools catalog database. I did try with sevaral other paths but i am getting same error.

db2eee>db2 "create regular tablespace tbsp32k0000 pagesize 32768 managed by system using ('/hbc_temp/tbsp') bufferpool bp32k0000"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0294N The container is already in use. SQLSTATE=42730


Please any idea/recommendation what i am running wrong.

Thanks
Dave

[Knut Stolze]

Post by v***@yahoo.com
Hi Team,
I have been getting error message for creating tablespace on the multiple
logical partition database on single aix machine for tools catalog
database. I did try with sevaral other paths but i am getting same error.
db2eee>db2 "create regular tablespace tbsp32k0000 pagesize 32768 managed
by system using ('/hbc_temp/tbsp') bufferpool bp32k0000"
DB21034E The command was processed as an SQL statement because it was not a
SQL0294N The container is already in use. SQLSTATE=42730
Please any idea/recommendation what i am running wrong.

The thing is that DB2 will create the tablespace on each partition.
Therefore, a container will be created under /hbc_temp/tbsp/ on _each_
partition. One partition can create the container, and very other one will
find the existing container and raise the SQL0294 error message.

What you have to do is to specify a separate path for each logical
partition. So you have to change your CREATE TABLESPACE statement to
something like this:

CREATE TABLESPACE ...
MANAGED BY SYSTEM
USING ( '/hbc_temp/tbsp/part1' ) ON DBPARTITIONNUM 1
USING ( '/hbc_temp/tbsp/part2' ) ON DBPARTITIONNUM 2
USING ( '/hbc_temp/tbsp/part3' ) ON DBPARTITIONNUM 3
...

[Joachim Müller]

Hi Dave,

or you can use the '$N' for qualifing the path with the partition:

CREATE TABLESPACE SYSTOOLSPACE IN DATABASE PARTITION GROUP IBMCATGROUP
PAGESIZE 32K MANAGED BY DATABASE USING
(FILE '/db2/DWP/cont_ $N _1/SYSTOOLSPACE' 1000)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
AUTORESIZE YES
INCREASESIZE 256 K
NO FILE SYSTEM CACHING
BUFFERPOOL BP_DATA_32K;

CREATE TABLESPACE DB2INFOSPACE IN DATABASE PARTITION GROUP DIM_PG PAGESIZE
32K MANAGED BY DATABASE USING
(FILE '/db2/DWP/cont_ $N _1/DB2INFOSPACE' 1000)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
AUTORESIZE YES
INCREASESIZE 256 K
NO FILE SYSTEM CACHING
BUFFERPOOL BP_DATA_32K;

CREATE TABLESPACE USRSPACE IN DATABASE PARTITION GROUP DWH_PG
PAGESIZE 32K MANAGED BY DATABASE
USING (FILE '/db2/DWP/cont_ $N _1/USRSPACE' 1000)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
AUTORESIZE YES
INCREASESIZE 256 K
NO FILE SYSTEM CACHING
BUFFERPOOL BP_DATA_32K;

best regards,
Joachim Müller

Post by v***@yahoo.com
Hi Team,
I have been getting error message for creating tablespace on the multiple
logical partition database on single aix machine for tools catalog
database. I did try with sevaral other paths but i am getting same error.
db2eee>db2 "create regular tablespace tbsp32k0000 pagesize 32768 managed
by system using ('/hbc_temp/tbsp') bufferpool bp32k0000"
DB21034E The command was processed as an SQL statement because it was not a
SQL0294N The container is already in use. SQLSTATE=42730
Please any idea/recommendation what i am running wrong.
Thanks
Dave