使用db2move迁移数据库到本地，执行ddl建表语句的时候，报错如下

点击(此处)折叠或打开

CREATE TABLE "BLUEDB "."T_SCHEMA_VERSION" ( "SCHEMA_VERSION_ID" DECIMAL(15,5) NOT NULL , "TITLE" VARCHAR(400) , "DESCRIPTION" VARCHAR(4000) , "CREATED_DATE" TIMESTAMP NOT NULL ) IN "USERSPACE1"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0670N The statement failed because the row size of the resulting table
would have exceeded the row size limit. Limit: "4005". Table space:
"USERSPACE1". SQLSTATE=54010

从错误提示看：
exceeded the row size limit. Limit: "4005"
表的行大小超过了行大小限制：4005

关于行大小：

The row size of a table is the sum of the byte counts of the columns,

解决：
原来是因为原库的pagesize是32k,而我的本地测试库默认是4k
点击(此处)折叠或打开

Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x0A000200170E0560 0 DMS Regular 16384 4 Yes 4 1 1 Def 1 0 3 SYSCATSPACE
0x0A000200170E8D80 1 SMS SysTmp 16384 32 Yes 32 1 1 On 1 0 4294967295 TEMPSPACE1
0x0A000200170F15A0 2 DMS Large 16384 32 Yes 32 1 1 Def 1 0 31 USERSPACE1
0x0A000200170FA480 3 DMS Large 16384 4 Yes 4 1 1 Def 1 0 3 SYSTOOLSPACE
0x0A000200171033C0 4 SMS UsrTmp 16384 32 Yes 32 1 1 On 1 0 4294967295 TEMPSPACE
0x0A0002001710E2E0 5 SMS UsrTmp 16384 4 Yes 4 1 1 On 1 0 4294967295 SYSTOOLSTMPSPACE

将本地的表空间"USERSPACE1"使用32k的pagesize

解释：
在db2中，对于变长字符串的实际占用存储空间量有一个限制：数据必须适合放在单个表空间页面上。

表的行长度不能超过：
* 在具有 4K 页大小的表空间中，是 4005 字节
* 在具有 8K 页大小的表空间中，是 8101 字节。
* 在具有 16K 页大小的表空间中，是 16293 字节。
* 在具有 32K 页大小的表空间中，是 32677 字节

点击(此处)折叠或打开

Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x0A000200170E0560 0 DMS Regular 16384 4 Yes 4 1 1 Def 1 0 3 SYSCATSPACE
0x0A000200170E8D80 1 SMS SysTmp 16384 32 Yes 32 1 1 On 1 0 4294967295 TEMPSPACE1
0x0A000200170F15A0 2 DMS Large 16384 32 Yes 32 1 1 Def 1 0 31 USERSPACE1
0x0A000200170FA480 3 DMS Large 16384 4 Yes 4 1 1 Def 1 0 3 SYSTOOLSPACE
0x0A000200171033C0 4 SMS UsrTmp 16384 32 Yes 32 1 1 On 1 0 4294967295 TEMPSPACE
0x0A0002001710E2E0 5 SMS UsrTmp 16384 4 Yes 4 1 1 On 1 0 4294967295 SYSTOOLSTMPSPACE

[db2inst3@kvm101 ~]$ db2 ? SQL0670N

SQL0670N The statement failed because the row size of the resulting
table would have exceeded the row size limit. Limit: "".
Table space: "".

Explanation:

The row size of a table is the sum of the byte counts of the columns,
including the inline lengths of all structured or XML type columns.
Therefore, the row size is affected by the total number of columns, the
total number of LOB columns, and the data type of the columns.

The row size limit is the maximum number of bytes of a given table. This
limit consist of a defined value in some cases or a variable value that
depends on the table space page size.

The message is returned when an attempt is made to create or modify a
table by using one of the following statements:

* CREATE TABLE
* ALTER TABLE statement
* DECLARE GLOBAL TEMPORARY TABLE
* CREATE GLOBAL TEMPORARY TABLE

User response:

To resolve the issue reported by this message, perform the following
steps:

1. Calculate the row size of the table that you are trying to create or
modify.
2. Determine the limit or restriction exceeded by the row size.
* For row-oriented tables: the row size limit is determined by the
table space page size.
* For column-oriented tables: the limit is the column-oriented table
maximum row size.

3. Perform one of the following actions:
* Reduce the row size by reducing the lengths of one or more
columns, specifying a different data type, or eliminating one or
more columns.
* For row-oriented tables only: Specify in the statement a table
space with a page size that fits the row size that you calculated.

sqlcode: -670

sqlstate: 54010

Related information:
DECLARE GLOBAL TEMPORARY TABLE statement
CREATE GLOBAL TEMPORARY TABLE statement
CREATE TABLE statement
ALTER TABLE statement
SQL and XML limits

ITPub博客

db2-SQL0670N-exceeded the row size limit Limit: "4005"

The row size of a table is the sum of the byte counts of the columns,

最新文章