解决联合主键过长无法创建问题

该问题的原因是因为表空间的Pagesize默认为2k而导致无法创建较长的联合主键

解决方法为：增加一个Pagesize大小为4k的表空间，转为alarm_all_rank_statistic表使用；

 

1、默认创建数据库页面空间为2K（Pagesize (k) ）
[informix@tnms213 ceshi]$ onspaces -c -d tnms_his11  -p /zznode1/datadbs1/ceshi/tnms_his11 -s 10000 -o 0
Verifying physical disk space, please wait ...
Space successfully added.
** WARNING **  A level 0 archive of Root DBSpace will need to be done.

2、创建一定长度的联合主键报错
create table "informix".alarm_all_rank_statistic
  (
    sdate varchar(10),
    rank decimal(4,0),
    management_domain varchar(255),
    ems_name varchar(64),
    me_name varchar(64),
    title varchar(64),
    native_object_name varchar(255),
    alarm_num decimal(16,0),
    statistic_time datetime year to second,
    primary key (sdate,title,native_object_name,me_name)
  ) in tnms_his11;

  550: Total length of columns in constraint is too long.
Error in line 13
Near character position 15
---------解决方法：

3、删除已创建的tnms_his11表空间
[informix@tnms213 ceshi]$ onspaces -d tnms_his11
WARNING: Dropping a DBspace.
Do you really want to continue? (y/n)y
Space successfully dropped.
** WARNING **  A level 0 archive will need to be done before any chunks from
DBspace tnms_his11 can be reused (see Dynamic Server Administrator's manual).

4、创建页面空间为4k的表空间（Pagesize (k)     4 ）
[informix@tnms213 ceshi]$ onspaces -c -d tnms_his11 -k 4 -p /zznode1/datadbs1/ceshi/tnms_his11 -s 10000 -o 0
Verifying physical disk space, please wait ...
Space successfully added.

5、重新创建一定长度联合主键的表
[informix@tnms213 ceshi]$ dbaccess tnmsdb2_it -

Database selected.

> create table "informix".alarm_all_rank_statistic
>   (
>     sdate varchar(10),
>     rank decimal(4,0),
>     management_domain varchar(255),
>     ems_name varchar(64),
>     me_name varchar(64),
>     title varchar(64),
>     native_object_name varchar(255),
>     alarm_num decimal(16,0),
>     statistic_time datetime year to second,
>     primary key (sdate,title,native_object_name,me_name)
>   ) in tnms_his11;

Table created.

6、查看已创建表信息
[informix@tnms213 ceshi]$ oncheck -pt tnmsdb2_it:alarm_all_rank_statistic

TBLspace Report for tnmsdb2_it:informix.alarm_all_rank_statistic

    Physical Address               42:10
    Creation date                  06/05/2012 16:31:30
    TBLspace Flags                 901        Page Locking
                                              TBLspace contains VARCHARS
                                              TBLspace use 4 bit bit-maps
    Maximum row size               738      
    Number of special columns      6        
    Number of keys                 0        
    Number of extents              1        
    Current serial value           1        
    Current SERIAL8 value          1        
    Current REFID value            1        
    Pagesize (k)                   4        
    First extent size              4        
    Next extent size               4        
    Number of pages allocated      4        
    Number of pages used           1        
    Number of data pages           0        
    Number of rows                 0        
    Partition partnum              20971522 
    Partition lockid               20971522