Order by报错问题解决

来源：互联网发布：回归分析对数据的要求编辑：程序博客网时间：2024/06/02 03:49

在本地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。

0 0