请教：关于SQL0286N
我在创建数据库表的时候报了如下的错误DB21034EThe command was processed as an SQL statement because it was not a valid Command Line Processor command.During SQL processing it returned:
SQL0286NA default table space could not be found with a page size of at least "8192" that authorization ID "DB2INST1" is authorized to use.SQLSTATE=42727
应该是默认表空间不足吧？
我的创建数据库的语句是这样的
create database dbtest USING CODESET GBK TERRITORY CN
USER TABLESPACE MANAGED BY SYSTEM USING ('/home/db2inst1/tabspace1' )
数据库已经创建成功了。可是就是没有办法建表，我的那个表有很多字段，应该怎么做？
请教：关于SQL0286N
能不能看一下你的建表语句和你的"list tablespaces show detail"输出！请教：关于SQL0286N
建表语句太大了，不好贴，大概有300个字段的样子吧db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1609
Useable pages = 1609
Used pages = 1609
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
请教：关于SQL0286N
原帖由 "oprs"]字段太多，超出了默认页大小4k的限制，你可以新建个页大小为8k的userspace2表空间，将这个表建立在userspace2上
create table (.......) in userspace2
偶测试了一下，这个提示好像是sql0670，不过这个解决方法应该是正确的！
SQL0670N The row length of the table exceeded a limit of
"<length>;" bytes.(Table space
"<tablespace-name>;".)
Explanation:The row length of a table in the database manager
cannot exceed:
o 4005 bytes in a table space with a 4K page size
o 8101 bytes in a table space with an 8K page size
o 16293 bytes in a table space with an 16K page size
o 32677 bytes in a table space with an 32K page size
The length is calculated by adding the internal lengths of the
columns.Details of internal column lengths can be found under
CREATE TABLE in the SQL Reference.
One of the following conditions can occur:
o The row length for the table defined in the CREATE TABLE or
ALTER TABLE statement exceeds the limit for the page size of
the table space.The regular table space name
"<tablespace-name>;" identifies the table space from
which the page size was used to determine the limit on the
row length.
o The row length for the table defined in the DECLARE GLOBAL
TEMPORARY TABLE statement exceeds the limit for the page size
of the table space.The user temporary table space name
"<tablespace-name>;" identifies the table space from
which the page size was used to determine the limit on the
row length.
The statement cannot be processed.
User Response:Depending on the cause, do one of the following.
o In the case of CREATE TABLE, ALTER TABLE, or DECLARE GLOBAL
TEMPORARY TABLE, specify a table space with a larger pagesize,
if possible.
o Otherwise, reduce the row length by eliminating one or more
columns or reducing the lengths of one or more columns.
sqlcode:-670
sqlstate:54010
请教：关于SQL0286N
page size最大可以到32k，考虑一下表的数据容量，最大512GB。db2 =>list tablespaces show detail;
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 16384
Useable pages = 16380
Used pages = 10704
Free pages = 5676
High water mark (pages) = 10704
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 3168
Free pages = 4992
High water mark (pages) = 3296
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8188
Used pages = 152
Free pages = 8036
High water mark (pages) = 152
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
db2 =>
db2 => CREATE TABLE user_secret_question
(
id bigint NOT NULL,
question varchar(1000) NOT NULL,
answer varchar(10000) NOT NULL,
ownerbigint NOT NULL,
CONSTRAINT user_secret_question_pkey PRIMARY KEY (id)
);db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) =>
DB21034EThe command was processed as an SQL statement because it was not a
valid Command Line Processor command.During SQL processing it returned:
SQL0286NA default table space could not be found with a page size of at
least "16384" that authorization ID "MACAN" is authorized to use.
SQLSTATE=42727
db2 =>
我可以通过扩展来扩展USERSPACE1来达到目的么？
如何设置USERSPACE1使得它可以自动增长呢？ 你表空间的page size 全是4K的,建一个page size 32K的表空间,然后在上面建表 可以扩展当前这个USERSPACE1的page size呢？
页:
[1]