informix 表空间

    informix执行一个大查询sql语句发生错误：

   [Error Code: -229, SQL State: IX000] Could not open or create a temporary file. 2) [Error Code: -131, SQL State: IX000] ISAM error:         no free disk space

    根据此错误提示，怀疑是临时表空间太小，于是拟增加表空间解决问题：

 1.用  $onstat -c  查询临时表空间名称

            

 2.用 $onstat -d  查询DBSPAACETEMP详细信息

           

          

           

             

3.通过以上查询知道表空间名称和其应扩展地址， 通过以下语句增加表空间

NIOS251-/opt/IBM/informix/chunks> touch tmpchunks1

NIOS251-/opt/IBM/informix/chunks> touch tmpchunks2

NIOS251-/opt/IBM/informix/chunks> touch tmpchunks3
NIOS251-/opt/IBM/informix/chunks> touch tmpchunks4
NIOS251-/opt/IBM/informix/chunks> touch tmpchunks5
NIOS251-/opt/IBM/informix/chunks> touch tmpchunks6

            

NIOS251-/opt/IBM/informix/chunks> chmod 660 tmpchunks1
NIOS251-/opt/IBM/informix/chunks> chmod 660 tmpchunks2
NIOS251-/opt/IBM/informix/chunks> chmod 660 tmpchunks3
NIOS251-/opt/IBM/informix/chunks> chmod 660 tmpchunks4
NIOS251-/opt/IBM/informix/chunks> chmod 660 tmpchunks5
NIOS251-/opt/IBM/informix/chunks> chmod 660 tmpchunks6

NIOS251-/opt/IBM/informix/chunks> onspaces -a tmpdbs1 -p /opt/IBM/informix/chunks/tmpchunks1 -o 0 -s 200000
Verifying physical disk space, please wait ...
Chunk successfully added.
NIOS251-/opt/IBM/informix/chunks> onspaces -a tmpdbs2 -p /opt/IBM/informix/chunks/tmpchunks2 -o 0 -s 200000
Verifying physical disk space, please wait ...
Chunk successfully added.
NIOS251-/opt/IBM/informix/chunks> onspaces -a tmpdbs3 -p /opt/IBM/informix/chunks/tmpchunks3 -o 0 -s 200000
Verifying physical disk space, please wait ...
Chunk successfully added.
NIOS251-/opt/IBM/informix/chunks> onspaces -a tmpdbs4 -p /opt/IBM/informix/chunks/tmpchunks4 -o 0 -s 200000
Verifying physical disk space, please wait ...
Chunk successfully added.
NIOS251-/opt/IBM/informix/chunks> onspaces -a tmpdbs5 -p /opt/IBM/informix/chunks/tmpchunks5 -o 0 -s 200000
Verifying physical disk space, please wait ...
Chunk successfully added.
NIOS251-/opt/IBM/informix/chunks> onspaces -a tmpdbs6 -p /opt/IBM/informix/chunks/tmpchunks6 -o 0 -s 200000
Verifying physical disk space, please wait ...
Chunk successfully added.

 4.再查询详细信息，发现DBSPAACETEMP表空间已经加上。

5.再在执行sql，发现错误依然存在，在网上查询，了解到，实际上执行查询语句时首先会用到rootdbs表空间，并且要求是连续的，$onstat -d 查询详细信息：

有两块空间，一块的free已经很少了，另一块还很多，但是 offset是 50，

6.分析怀疑是两个表空间，第二张表的offset=50，所以两个表空间是不连续的，执行sql可能先用到第一块表空间，于是在第一块rootdbs上增加表空间rootchunks1  参数 -o 设为0.

至此，sql语句可以顺利执行。

附，关于onspaces 命令，详细信息如下

$ onspaces --

Usage:

 onspaces { -a <spacename> -p <path> -o <offset> -s <size> [-m <path> <offset>]

               { { [-Mo <mdoffset>] [-Ms <mdsize>] } | -U }

               } |

          { -c { -d <DBspace> [-k <pagesize>] [-t]

                    -p <path> -o <offset> -s <size> [-m <path> <offset>] } |

               { -d <DBspace> [-k <pagesize>]

                    -p <path> -o <offset> -s <size> [-m <path> <offset>]

                    [-ef <first_extent_size>] [-en <next_extent_size>] } |

               { -b <BLOBspace> -g <pagesize>

                    -p <path> -o <offset> -s <size> [-m <path> <offset>] } |

               { -S <SBLOBspace> [-t]

                    -p <path> -o <offset> -s <size> [-m <path> <offset>]

                    [-Mo <mdoffset>] [-Ms <mdsize>] [-Df <default-list>] } |

               { -x <Extspace> -l <Location> } } |

          { -d <spacename> [-p <path> -o <offset>] [-f] [-y] } |

          { -f[y] off [<DBspace-list>] | on [<DBspace-list>] } |

          { -m <spacename> {-p <path> -o <offset> -m <path> <offset> [-y] |

                            -f <filename>} } |

          { -r <spacename> [-y] } |

          { -s <spacename> -p <path> -o <offset> {-O | -D} [-y] } |

          { -ch <sbspacename> -Df <default-list> } |

          { -cl <sbspacename> } |

          { -ren <spacename> -n <newname> }

    -a  - Add a chunk to a DBspace, BLOBspace or SBLOBspace

    -c  - Create a DBspace, BLOBspace, SBLOBspace or Extspace

    -d  - Drop a DBspace, BLOBspace, SBLOBspace, Extspace, or chunk

    -f  - Change dataskip default for specified DBspaces

    -m  - Add mirroring to an existing DBspace, BLOBspace or SBLOBspace

    -r  - Turn mirroring off for a DBspace, BLOBspace or SBLOBspace

    -s  - Change the status of a chunk

    -ch - Change default list for smart large object space

    -cl - garbage collect smart large objects that are not referenced

    default-list = {[LOGGING = {ON|OFF}]  [,ACCESSTIME = {ON|OFF}]

            [,AVG_LO_SIZE = {1 - 2097152}] }

    -ren - Rename a DBspace, BLOBspace, SBLOBspace or Extspace