blogger

    blogger
  • hot log

  • comment

    twitter

    • 12.10.xC6出来时候碰到"Could not determine encryption mode from page zero",竟果然是BUG!http://www-01.ibm.com/support/docview.wss?rs=630&uid=swg21973408

      2015-12-21 20:15

    • 空间已经转移到新加坡,希望会好一些。

      2015-11-16 14:34

    • 增加新域名liaosnet.cc,与liaosnet.com同指向。

      2015-10-09 15:25

    • 空间迁移到香港。或许速度会快一点。。

      2014-02-13 20:10

    • 解决个问题:NBU配置Storage时的目录在系统分区上,导致备份失败,最终原因是目录在系统分区上时,需要在 This directory can exist on the root file system or system disk. 这个选项前打上勾。

      2012-09-26 22:21

    • More»

    blog sort

    links

    record

    banner

    IDS11.5新功能--通过sysadmin删除无数据的表区段

    暗夜星空's Memory from Jan 30 , 2011 at 23:15 PM , under Category:INFORMIX

    Informix 11.50在xC4版本后增加的新功能:
    1,将表中的数据移动到表前区未用的空间上(如,表记录删除后的留下的空间);
    2,将表末尾没有数据的空间从表中删除;
    通过以上2个功能,可以实现回收表未用空间给其它表使用.

    以下通过示例详述:
    1,建立一个表t1导入一定的数据,并通过建立t2,t3,t4,t5表将表t1的区段分离.

     informix@suse10:/opt/informix/tmp/tmp> dbaccess testdb -

    > create table t1(id char(10),name char(20));

    Table created.

    > create table t2(id char(1));

    Table created.

    > load from t1.unl delimiter " " insert into t1;

    10000 row(s) loaded.

    > create table t3(id char(1));

    Table created.

    > load from t1.unl delimiter " " insert into t1;

    10000 row(s) loaded.

    > create table t4(id char(1));

    Table created.

    > load from t1.unl delimiter " " insert into t1;

    10000 row(s) loaded.

    > create table t5(id char(1));

    Table created.


    2,检查下表t1的区段情况.可以发现,由于t2,t3,t4表的原因,t1生成了4个区段.

     informix@suse10:/opt/informix/tmp/tmp> oncheck -pt testdb:t1



    TBLspace Report for testdb:informix.t1

        Physical Address               3:513
        Creation date                  03/03/2010 17:20:14
        TBLspace Flags                 801        Page Locking
                                                  TBLspace use 4 bit bit-maps
        Maximum row size               30        
        Number of special columns      0         
        Number of keys                 0         
        Number of extents              4         
        Current serial value           1         
        Current SERIAL8 value          1         
        Current BIGSERIAL value        1         
        Current REFID value            1         
        Pagesize (k)                   2         
        First extent size              8         
        Next extent size               8         
        Number of pages allocated      512       
        Number of pages used           510       
        Number of data pages           509       
        Number of rows                 30000      
        Partition partnum              3145790   
        Partition lockid               3145790   

        Extents                       
             Logical Page     Physical Page        Size Physical Pages
                        0            3:1223           8          8
                        8            3:1239         168        168
                      176            3:1415         168        168
                      344            3:1591         168        168


    3,然后删除t1表中的一些数据.那样表中的将有些数据页将变成不可用了.

     informix@suse10:/opt/informix/tmp/tmp> dbaccess testdb -

    Database selected.

    > delete from t1 where id < '8000';

    23343 row(s) deleted.

    > select count(*) from t1;


          (count(*)) 

                6657

    1 row(s) retrieved.

    informix@suse10:/opt/informix/tmp/tmp> oncheck -pt testdb:t1

    TBLspace Report for testdb:informix.t1

        Physical Address               3:513
        Creation date                  03/03/2010 17:20:14
        TBLspace Flags                 801        Page Locking
                                                  TBLspace use 4 bit bit-maps
        Maximum row size               30        
        Number of special columns      0         
        Number of keys                 0         
        Number of extents              4          
        Current serial value           1         
        Current SERIAL8 value          1         
        Current BIGSERIAL value        1         
        Current REFID value            1         
        Pagesize (k)                   2         
        First extent size              8         
        Next extent size               8         
        Number of pages allocated      512       
        Number of pages used           510       
        Number of data pages           122       
        Number of rows                 6657       
        Partition partnum              3145790   
        Partition lockid               3145790   

        Extents                       
             Logical Page     Physical Page        Size Physical Pages
                        0            3:1223           8          8
                        8            3:1239         168        168
                      176            3:1415         168        168
                      344            3:1591         168        168


    4,通过sysadmin库的task将表中数据前移
    命令格式为: execute function task("table repack","tabname","dbname");

     informix@suse10:/opt/informix/tmp/tmp> dbaccess sysadmin -

    Database selected.

    > execute function task("table repack","t1","testdb");

    (expression)  Succeeded: table repack  testdb:informix.t1 

    1 row(s) retrieved.


    5,通过sysadmin库的task删除没有数据的数据空间
    命令格式为: execute function task("table shrink","tabname","dbname");

     informix@suse10:/opt/informix/tmp/tmp> dbaccess sysadmin -

    Database selected.

    > execute function task("table shrink","t1","testdb");

    (expression)  Succeeded: table shrink  testdb:informix.t1 

    1 row(s) retrieved.


    6,查看当前表t1的区段情况,没有数据的表区段已经删除

     informix@suse10:/opt/informix/tmp/tmp> oncheck -pt testdb:t1



    TBLspace Report for testdb:informix.t1

        Physical Address               3:513
        Creation date                  03/03/2010 17:20:14
        TBLspace Flags                 801        Page Locking
                                                  TBLspace use 4 bit bit-maps
        Maximum row size               30        
        Number of special columns      0         
        Number of keys                 0         
        Number of extents              2          
        Current serial value           1         
        Current SERIAL8 value          1         
        Current BIGSERIAL value        1         
        Current REFID value            1         
        Pagesize (k)                   2         
        First extent size              8         
        Next extent size               8         
        Number of pages allocated      114       
        Number of pages used           114       
        Number of data pages           113       
        Number of rows                 6657       
        Partition partnum              3145790   
        Partition lockid               3145790   

        Extents                       
             Logical Page     Physical Page        Size Physical Pages
                        0            3:1223           8          8
                        8            3:1239         106        106


    通过oncheck -pe查看数据空间上的表分布

     testdb:'informix'.t1            1223        8
     testdb:'informix'.t2            1231        8
     testdb:'informix'.t1            1239      106
     FREE                            1345       62    --原区段168,回收未用的62
     testdb:'informix'.t3            1407        8
     FREE                            1415      168    --全回收
     testdb:'informix'.t4            1583        8
     FREE                            1591      168
     testdb:'informix'.t5            1759        8
     FREE                            1767    98233


    至此,就完成了表未用空间的回收.

    Page comment lists:

    likingzi 2012-01-17 17:15

    好文,学习了,谢谢分享