DB2 学习笔记 - AIView.com

Get an error as compiling a view.

SQL1585N  不存在具有足够页大小的系统临时表空间。  SQLSTATE=54048 

SQL1585N不存在具有足够页大小的系统临时表空间。

解释:

可能发生了下列其中一种情况：

1.    系统临时表的行长度超过了数据库中最大系统临时表空间中可接受的限
    制。

2.    系统临时表中所需的列数超过了数据库中最大系统临时表空间中可接受
    的限制。

系统临时表空间限制取决于其页大小。这些值是：


  最大         最大   临时
  记录         列数   表空间的
  长度                页大小
  -----------  ----  ------------
  1957  字节   244   2K
  4005  字节   500   4K
  8101  字节   1012  8K
  16293 字节   1012  16K
  32677 字节   1012  32K

The view being compiled is based on other view which contain a function that has the return parameter VARCHAR(32672).

SQLLIB\BIN> db2 list tablespaces show detail

表空间标识                        = 1
名称                              = TEMPSPACE1
类型                              = 系统管理空间
内容                              = 系统临时数据
......
页大小（以字节计）                = 4096
......

I tried to reduce the return parameter length down to 4005 in the function, but the error continues to happen as compiling the view. Finally it works for VARCHAR(3600) as maximum.

Please see following test case.

The function.

CREATE FUNCTION zytst.fun1
RETURNS VARCHAR(32672)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
RETURN 'abc'
@

The inner view.

CREATE VIEW zytst.v1 AS
VALUES zytst.fun1() AS c1
@ 

The view occurring error.

CREATE VIEW zytst.v2 AS
SELECT c1 AS c1 FROM zytst.v1
@ 

The function fun1 and the view v1 could be compiled successfully in the DB whose system temporary tablespace only has 4KB pagesize in bufferpool. But the v2 could Not be compiled in the same DB.