请教大侠 Informix 临时表问题 -229

slowlybright1

在运行批处理任务时，首先load大量数据到informix表中，然后运行存储过程会出现229（不能打开或创建临时文件。
），有时是242（不能打开数据库表< 表名>）的错误
但是数据库重启后，数据库里面的表不变，重新运行该存储过程又是正常的，请问是什么原因造成的？

请各位大侠多多指教。

[ 本帖最后由 slowlybright1 于 2009-2-26 10:01 编辑 ]

liaosnet

-229    Could not open or create a temporary file.

The database server cannot create a temporary disk file. The file
should be created in the directory that the DBTEMP environment variable
specifies (or /tmp by default on UNIX systems). Check the accompanying
ISAM error code and look for operating-system error messages that
might give more information. Possibly your account does not have write
permission in that directory, or the disk is full. Since Version 5.01,
the IBM Informix SE database server uses the DBTEMP environment variable,
but IBM Informix Dynamic Server and IBM Informix OnLine Dynamic Server do not.

On a UNIX system, try increasing the maximum number of open files
(NFILES parameter on some systems) to approximately 400.


-242    Could not open database table table-name.

The database server cannot begin reading a table. Check the
accompanying ISAM error code for more information. The problem might be
file permissions, a hardware error, or a corrupted system catalog.
Unless the ISAM error code or an operating-system message points to
another cause, run the bcheck or secheck utility to verify file
integrity.

这两个均需要提供ISAM错误来判断...

slowlybright1

请问isam错误信息如何获取？

liaosnet

在输出229错误的时候同时输出的....

slowlybright1

我是在dbaccess yysjk下运行的，似乎看不到这个isam错误号，在那里运行可以看到这个isam号？ AIX下echo "execute procedure proc_gd_cpwj_mx('20090210')"|dbaccess yysjk这样调看的到吗？

liaosnet

dbaccess下是可以看到的...
一般是先返回正常错误,然后是ISAM错误号...

liaosnet

这个也应该可以的...输出到当前终端..

slowlybright1

229: Could not open or create a temporary file.  
   110: ISAM error:  end or beginning of the file.  

代码执行到这部分出错
select ...
from loan  a
     ,gl_assgn  b
     ,gl_tx_bdts c
     ,cpwj d
     ,ln_gl_acct e
where b.lnpa_loan_typ=a.lnpa_loan_typ
   and a.loan_no=c.loan_no
   and c.loan_no=e.loan_no
   and e.ldgr_id = c.ldgr_cde
   --and c.prcs_bch != '00000'
   and substr(c.bch,1,2)=d.jydqdh
   and c.host_tx_no = d.gylsh
   and c.bch_gl_ac_no = d.zhdh
   and c.drcr = decode(d.jdbj, '0', 'D', 'C')
   and ( ('0' = d.bczbz and c.tx_no not in (select revse_tx_no from gl_tx_bdts where revse_tx_no is not null))
        or
         ('1' = d.bczbz and c.tx_no in (select revse_tx_no from gl_tx_bdts where revse_tx_no is not null)))
   and (d.jydm = '3750' or d.jydm = '3752' or d.jydm = '3754')     
   and d.zhdh in (select zhdh from temp_bzh)
order by  1,2,3,4  asc
into temp temp_cp_part1 with no log ;      -------temp_cp_part1表获得了FLBZ,BASE_AMT,ZHDH等字段

create index temp_cp_part1_idx on temp_cp_part1(cpzxh, cpznxh);

liaosnet

-110    ISAM error: end or beginning of the file.

Reading rows sequentially, forward or backward, the ISAM processor has
reached one end of the file (table). For C-ISAM programs, this message
is the normal signal for end of file. Use a different retrieval mode of
isread to reposition the file. For SQL products, see the SQL error
message or return code.

slowlybright1

一般什么情况会造成这种错误呢？

-110 ISAM错误: 文件的开头或结尾。

ISAM处理器向前或向后顺序地读取行时达到了文件(表）的一端。对C-ISAM程序，这个
信息是文件结束的正常信号。使用isread的其它检索方法来在文件中重新定位。对SQL
产品，参见SQL错误信息或返回代码。