问题:
DB2数据库中所有的表空间页大小都是4k,但SQL语句却报错:SQL1585N A temporary table could not be created because there is no available system temporary table space that has a compatible page size. SQLSTATE=54048
原因分析:
这个问题容易重现,表所在表空间页大小都是4k,却需要8k页大小的临时表空间, 以下是重现的步骤。
页大小均为4k,但表t1和t1_tmp中字段实际长度(3000)超过了页大小的一半(2048),导致merge的时候产生的临时表字段长度超过4k,所以才需要页大小超过4k的系统临时表空间。
db2 list tablespaces show detail | grep 'Page size'
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
Page size (bytes) = 4096
db2 "connect to sample"
db2 "create table t1(id int, name varchar(4096))"
db2 "create table t1_tmp(id int, name varchar(4096))"
x=a
for i in `seq 1 3000`
do
x=$x'a'
done
db2 "insert into t1 values(1,'${x}'),(2,'${x}')"
db2 "insert into t1_tmp values(2,'${x}'),(3,'${x}')"
db2 "merge into t1 using t1_tmp on t1.id=t1_tmp.id when matched then update set t1.name=t1_tmp.name when not matched then insert values(t1_tmp.id, t1_tmp.name)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1585N A temporary table could not be created because there is no available
system temporary table space that has a compatible page size. SQLSTATE=54048
解决:
创建一个8k的系统临时表空间即可:
db2inst1@NODE01:~> db2 "create bufferpool buf_8k pagesize 8 k"
DB20000I The SQL command completed successfully.
db2inst1@NODE01:~> db2 "create system temporary tablespace tempspace8k pagesize 8k bufferpool buf_8k"
DB20000I The SQL command completed successfully.
db2inst1@NODE01:~> db2 "merge into t1 using t1_tmp on t1.id=t1_tmp.id when matched then update set t1.name=t1_tmp.name when not matched then insert values(t1_tmp.id, t1_tmp.name)"
DB20000I The SQL command completed successfully.
db2inst1@NODE01:~> db2 "select count(*) from t1"
1
-----------
3
1 record(s) selected.