在本地DB2的数据库了执行了下面的语句,抛出异常:(注:tbl_orgunit所在的表空间的pagesize=8k,系统临时表空间的pagesize=8k,均自动存储,MANAGED BY AUTOMATIC STORAGE)
select * from tbl_orgunit order by orgunitname desc (orgunitname varchar(50))
A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=4.18.60
select * from tbl_orgunit order by orgunitid desc (orgunitid int)却没有问题。
解决方法:增加新的pagesize=32k的系统临时表空间,当然,也得新增32k的缓冲池。
CREATE BUFFERPOOL "BufferPool32K" IMMEDIATE ALL DBPARTITIONNUMS SIZE 5000 AUTOMATIC PAGESIZE 32768;
CREATE SYSTEM TEMPORARY TABLESPACE "TEMPSPACE2"
IN DATABASE PARTITION GROUP "IBMTEMPGROUP"
PAGESIZE 32 K
MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 8
BUFFERPOOL "BufferPool32K"
OVERHEAD INHERIT
TRANSFERRATE INHERIT
USING STOGROUP "IBMSTOGROUP"
FILE SYSTEM CACHING;
上述语句执行完毕后,order by操作报错问题解决。
顺便感慨IBM的软件的难用程度,简单的order by操作也要做trouble shooting。