足够的页面大小不存在 - DB2插入 [英] sufficient page size does not exist - DB2 insert

问题描述

我有一个DB2查询（简单的insert语句），它试图插入大约27列。在这些列中，1是 Clob ，问题在于。对于我的 Clob 列，有时该值甚至可能包含 28K 字符。在这种极端情况下，我收到以下错误，

 具有足够页面大小的系统临时表空间不存在。 。SQLCODE = -1585，SQLSTATE = 54048，DRIVER = 3.64.82 
  

当我googled并通过一些页面，有一个实体叫做系统临时表空间，这个实体将在执行查询时被数据库使用（我不确定，但是很少的页面表示它将仅用于排序和加入，但我没有在我的查询）。

经过几个建议，我创建了一个 系统临时表空间 ，页面大小为32K，使用以下查询

 创建系统临时表格STB PAGESIZE 32K管理系统使用（'C：\DB2\NODE0005'）BUFFERPOOL BP32K 
  

仍然是我的问题。什么是正确的方式来执行我的查询。我正在努力了解 系统临时表空间 ， 页面大小 等的重要性。但是任何帮助

解决方案

您可以为每个页面创建一个系统临时表空间作为SMS（系统管理）。在这种情况下，您的查询将始终找到具有适当页面大小的表空间。

  CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K 
 CREATE SYSTEM TEMPORARY TABLESPACE STB_8 PAGESIZE 8K 
 CREATE SYSTEM TEMPORARY TABLESPACE STB_16 PAGESIZE 16K 
创建系统临时表格STB_32 PAGESIZE 32K 
  

创建短信时，表空间不会预先分配磁盘空间，只能在使用时增长。

I am having a DB2 query(simple insert statement) which is trying to insert some 27 columns. Out of those columns 1 is Clob and the issue is there. For my Clob column, sometimes the value might even contain 28K characters. And in such extreme cases, I am getting the below error,

 A system temporary table space with sufficient page size does not exist .. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.64.82

As I googled and gone through some pages, there is an entity called System Temporary Table Space which will be used by the DB when executing the query.(I am not sure, but few pages says that it will be used only for sorting and joining, but i don't have either in my query).

After going through few suggestions I created a System Temporary Table Space with the page size of 32K, using the below query,

CREATE SYSTEM TEMPORARY TABLESPACE STB PAGESIZE 32K MANAGED BY SYSTEM USING ( 'C:\DB2\NODE0005') BUFFERPOOL BP32K

Still my issue continues. What would be the proper way to have my query executed. I am trying to understand the importance of System Temporary Table Space, Page Size,etc.. But any help that could fix this issue for now would be greatly appreciated.

解决方案

You can create a system temporary tablespace for each page as SMS (System Managed). In that case, your query will always find a tablespace with the appropriate page size.

CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K 
CREATE SYSTEM TEMPORARY TABLESPACE STB_8 PAGESIZE 8K 
CREATE SYSTEM TEMPORARY TABLESPACE STB_16 PAGESIZE 16K 
CREATE SYSTEM TEMPORARY TABLESPACE STB_32 PAGESIZE 32K 

When creating SMS, the tablespace will not preallocate space in the disk, and they only growth as they are used.

这篇关于足够的页面大小不存在 - DB2插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！