Hi all,
In a DB2 9.7 ASE in RHAL environment, when I try to create a tablespace using EXTENTSIZE 16, it goes well but I try it with 32, it fails(SQL0289N) .
CREATE LARGE TABLESPACE MY_32K_TS_P0 IN DATABASE PARTITION GROUP PG_0 PAGESIZE 32768 MANAGED BY DATABASE
USING ( FILE '/db2/db2inst1/testdb/NODE0000/CONT0001/DMS/MY_32K_TS_P0' 100,
FILE '/db2/db2inst1/testdb/NODE0000/CONT0002/DMS/MY_32K_TS_P0' 100 ) ON DBPARTITIONNUMS (0)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_32K
AUTORESIZE YES
MAXSIZE NONE
DROPPED TABLE RECOVERY ON
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0289N Unable to allocate new pages in table space "MY_32K_TS_ P0".
SQLSTATE=57011
Checking diag:
2015-02-13-19.39.38.077968 +480 I731972A499 LEVEL: Error
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCreatePool, probe:950
MESSAGE : ZRC=0x85020021=-2063466463=SQLB _END_OF_CONTAIN ER
"DMS Container space full"
2015-02-13-19.39.38.090391 +480 I732472A495 LEVEL: Severe
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, data management, sqldPoolCreate, probe:273
RETCODE : ZRC=0x85020021=-2063466463=SQLB _END_OF_CONTAIN ER
"DMS Container space full"
2015-02-13-19.41.50.209228 +480 I732968A502 LEVEL: Error
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSCreatePo ol, probe:800
MESSAGE : ZRC=0x85020021=-2063466463=SQLB _END_OF_CONTAIN ER
"DMS Container space full"
2015-02-13-19.41.50.211040 +480 I733471A442 LEVEL: Error
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSCreatePo ol, probe:800
DATA #1 : String, 9 bytes
Page=168
CREATE LARGE TABLESPACE MY_32K_TS_P0 IN DATABASE PARTITION GROUP PG_0 PAGESIZE 32768 MANAGED BY DATABASE
USING ( FILE '/db2/db2inst1/testdb/NODE0000/CONT0001/DMS/MY_32K_TS_P0' 100,
FILE '/db2/db2inst1/testdb/NODE0000/CONT0002/DMS/MY_32K_TS_P0' 100 ) ON DBPARTITIONNUMS (0)
EXTENTSIZE 16
PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_32K
OVERHEAD 11.700000
TRANSFERRATE 0.200000
AUTORESIZE YES
MAXSIZE NONE
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON
DB20000I The SQL command completed successfully.
$ db2 get db cfg | grep -i extent
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
There are plenty of space on those file systems.
Thanks,
~Luiz
In a DB2 9.7 ASE in RHAL environment, when I try to create a tablespace using EXTENTSIZE 16, it goes well but I try it with 32, it fails(SQL0289N) .
CREATE LARGE TABLESPACE MY_32K_TS_P0 IN DATABASE PARTITION GROUP PG_0 PAGESIZE 32768 MANAGED BY DATABASE
USING ( FILE '/db2/db2inst1/testdb/NODE0000/CONT0001/DMS/MY_32K_TS_P0' 100,
FILE '/db2/db2inst1/testdb/NODE0000/CONT0002/DMS/MY_32K_TS_P0' 100 ) ON DBPARTITIONNUMS (0)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_32K
AUTORESIZE YES
MAXSIZE NONE
DROPPED TABLE RECOVERY ON
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0289N Unable to allocate new pages in table space "MY_32K_TS_ P0".
SQLSTATE=57011
Checking diag:
2015-02-13-19.39.38.077968 +480 I731972A499 LEVEL: Error
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCreatePool, probe:950
MESSAGE : ZRC=0x85020021=-2063466463=SQLB _END_OF_CONTAIN ER
"DMS Container space full"
2015-02-13-19.39.38.090391 +480 I732472A495 LEVEL: Severe
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, data management, sqldPoolCreate, probe:273
RETCODE : ZRC=0x85020021=-2063466463=SQLB _END_OF_CONTAIN ER
"DMS Container space full"
2015-02-13-19.41.50.209228 +480 I732968A502 LEVEL: Error
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSCreatePo ol, probe:800
MESSAGE : ZRC=0x85020021=-2063466463=SQLB _END_OF_CONTAIN ER
"DMS Container space full"
2015-02-13-19.41.50.211040 +480 I733471A442 LEVEL: Error
PID : 10485942 TID : 12879 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-16117 APPID: *N0.db2inst1.15 0213111028
AUTHID : db2inst1
EDUID : 12879 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSCreatePo ol, probe:800
DATA #1 : String, 9 bytes
Page=168
CREATE LARGE TABLESPACE MY_32K_TS_P0 IN DATABASE PARTITION GROUP PG_0 PAGESIZE 32768 MANAGED BY DATABASE
USING ( FILE '/db2/db2inst1/testdb/NODE0000/CONT0001/DMS/MY_32K_TS_P0' 100,
FILE '/db2/db2inst1/testdb/NODE0000/CONT0002/DMS/MY_32K_TS_P0' 100 ) ON DBPARTITIONNUMS (0)
EXTENTSIZE 16
PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_32K
OVERHEAD 11.700000
TRANSFERRATE 0.200000
AUTORESIZE YES
MAXSIZE NONE
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON
DB20000I The SQL command completed successfully.
$ db2 get db cfg | grep -i extent
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
There are plenty of space on those file systems.
Thanks,
~Luiz