当你遇到以下db2错误:
SQL1585N A system temporary table space with sufficient page size does not exist. SQLSTATE=54048
是因为在view里面加入了太多的列,超过了当前系统临时表空间的page长度。
来看一下 SQL1585N的官方建议解决方案:
db2 => ? SQL1585N
SQL1585N A system temporary table space with sufficient page size does
not exist.
Explanation:
One of the following conditions could have occurred:
1. The row length of the system temporary table exceeded the limit that
can be accommodated in the largest system temporary table space in
the database.
2. The number of columns required in a system temporary table exceeded
the limit that can be accommodated in the largest system temporary
table space in the database.
3. A tablespace might be offline.
The system temporary table space limits depend on its page size. These
values are:
Max Max Page size of
Record Cols temporary
Length table space
----------- ---- ------------
4005 bytes 500 4K
8101 bytes 1012 8K
16293 bytes 1012 16K
32677 bytes 1012 32K
User response:
Create a system temporary table space of a larger page size supported,
if one does not already exist. If such a table space already exists,
eliminate one or more columns from the system temporary table. Create
separate tables or views, as required, to hold additional information
beyond the limit.
sqlcode: -1585
sqlstate: 54048
Related information:
ALTER TABLESPACE statement
官方建议 :
1建一个大一点的系统临时表空间,
2删除一些字段。
由于项目需要,一般只能选择方案1. 但是没有例子,只能自己写。
系统的默认临时表空间是USERSPACE1, 这是page 为4k的表空间。 既然默认的表空间不够,那就新建一个32k的吧。
先建立bufferpool:
db2 "create bufferpool temp_pool32k size 100 pagesize 32k"
然后检查这个bufferpool:
db2 "select * from syscat.bufferpools"
有了bufferpool,这时候才能建立自己的临时表空间:
CREATE TEMPORARY TABLESPACE "TEMP_TBS_32"
IN DATABASE PARTITION GROUP "IBMTEMPGROUP" --> 1
PAGESIZE 32K
MANAGED BY SYSTEM
USING
('/home/db2inst9/tablespaces16k/Temp_001' --->2
)
EXTENTSIZE 32
PREFETCHSIZE 16
BUFFERPOOL "TEMP_POOL32K" --->3
OVERHEAD 24.10
TRANSFERRATE 0.90
DROPPED TABLE RECOVERY OFF;
注意:
1是默认表空间的group,一定要这个值才能让view找到。
2 是本地存储文件路径,根据自己的硬盘修改。
3 是bufferpool的名字,记得一定都要改成大写字母。
4 文件/home/db2inst9/tablespaces16k/Temp_001是创建tablespaces之后才有的,之前需要自己命名。
去掉注释,创建完成后,检查创建结果:
db2 get snapshot for tablespaces on netdb | grep -A 30 TEMP_TBS_32
有返回,就表明表空间创建成功了。
在访问view,就有返回结果了。原因是db2会默认找到数据库下面最大的表空间来使用。
最后,祝好运!