can't drop table ISAM error -111 ?
User couldn't drop a leftover table FIRST_TMP from Informix 12.10 on Windows, getting ISAM error -111. The issue was that the table name was stored in uppercase in systables, requiring case-sensitive handling. Solution: set DELIMIDENT=1 environment variable and use double quotes around the uppercase table name in the DROP TABLE statement.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
Informix 12.10.FC5WE on windows server 2012 r2.
A developer created a temp table with 'select .... into temp FIRST_TMP' syntax
(as reported to me).
Somehow this table is hanging around despite the session that created it
having been closed and I'd like to get rid of it but this is proving to be
difficult. It appears to be a real table instead of a temp table so maybe the
temp keyword was left off initially.
The table shows in schema outputs, and has references in systables, syscolumns
and systabauth that I can find (may be others?).
If I try to drop it from dbaccess with 'drop table FIRST_TMP' then get
following error:
206: The specified table (first_tmp) is not in the database.
111: ISAM error: no record found.
So, this is our production system, is it safe to just remove its references
from the system tables to get rid of it or is there another way?
Regards,
Bryce Stenberg.
Is it possible that the table's actual ne is in upper case? Try this:
SELECT tabname
FROM systables
WHERE tabname matches 'T*';
Art
On Mar 13, 2017 19:49, "BRYCE STENBERG" <bryce@hrnz.co.nz> wrote:
Hi,
Informix 12.10.FC5WE on windows server 2012 r2.
A developer created a temp table with 'select .... into temp FIRST_TMP'
syntax
(as reported to me).
Somehow this table is hanging around despite the session that created it
having been closed and I'd like to get rid of it but this is proving to be
difficult. It appears to be a real table instead of a temp table so maybe
the
temp keyword was left off initially.
The table shows in schema outputs, and has references in systables,
syscolumns
and systabauth that I can find (may be others?).
If I try to drop it from dbaccess with 'drop table FIRST_TMP' then get
following error:
206: The specified table (first_tmp) is not in the database.
111: ISAM error: no record found.
So, this is our production system, is it safe to just remove its references
from the system tables to get rid of it or is there another way?
Regards,
Bryce Stenberg.
************************************************************
*******************
Forum Note: Use "Reply" to post a response in the discussion forum.
--001a1146fd1ce40ad9054aa5fd02
Hi, > Is it possible that the table's actual ne is in upper case? I'm not sure what an 'ne' is, this is the row in systables for the FIRST_TMP table: tabname : FIRST_TMP owner : informix partnum : 2099447 tabid : 1003 rowsize : 13 ncols : 4 nindexes : 0 nrows : 0.0 created : 10/03/2017 version : 65732609 tabtype : T locklevel : P npused : 0.0 fextsize : 32 nextsize : 32 flags : 0 site : (null) dbname : (null) type_xid : 0 am_id : 0 pagesize : 4096 ustlowts : (null) secpolicyid : 0 protgranularity : (null) statchange : 0 statlevel : A Should I just delete it and any other rows in tables that reference tabid 1003 maybe? Or might that screw with other things? Regards, Bryce.
Yup, uppercase name! Do this:
export DELIMIDENT=1
dbaccess <database> -
drop table "FIRST_TEMP";
<ctrl-d>
Art
On Mar 13, 2017 21:20, "BRYCE STENBERG" <bryce@hrnz.co.nz> wrote:
Hi,
> Is it possible that the table's actual ne is in upper case?
I'm not sure what an 'ne' is, this is the row in systables for the FIRST_TMP
table:
tabname : FIRST_TMP
owner : informix
partnum : 2099447
tabid : 1003
rowsize : 13
ncols : 4
nindexes : 0
nrows : 0.0
created : 10/03/2017
version : 65732609
tabtype : T
locklevel : P
npused : 0.0
fextsize : 32
nextsize : 32
flags : 0
site : (null)
dbname : (null)
type_xid : 0
am_id : 0
pagesize : 4096
ustlowts : (null)
secpolicyid : 0
protgranularity : (null)
statchange : 0
statlevel : A
Should I just delete it and any other rows in tables that reference tabid
1003
maybe? Or might that screw with other things?
Regards, Bryce.
************************************************************
*******************
Forum Note: Use "Reply" to post a response in the discussion forum.
--001a114241a08935d2054aa73a04
I would NOT suggest deleting from systables directly. It could be that the table was created as case-sensitive. Try running the drop with double quotes around the table name: drop table "FIRST_TMP"; If that doesn't work, set DELIMIDENT to y in the environment (e.g. export DELIMIDENT=y) and try the drop with the double quotes around the table name again. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BRYCE STENBERG Sent: Monday, March 13, 2017 7:20 PM To: ids@iiug.org Subject: Re: can't drop table ISAM error -111 ? [38744] Hi, > Is it possible that the table's actual ne is in upper case? I'm not sure what an 'ne' is, this is the row in systables for the FIRST_TMP table: tabname : FIRST_TMP owner : informix partnum : 2099447 tabid : 1003 rowsize : 13 ncols : 4 nindexes : 0 nrows : 0.0 created : 10/03/2017 version : 65732609 tabtype : T locklevel : P npused : 0.0 fextsize : 32 nextsize : 32 flags : 0 site : (null) dbname : (null) type_xid : 0 am_id : 0 pagesize : 4096 ustlowts : (null) secpolicyid : 0 protgranularity : (null) statchange : 0 statlevel : A Should I just delete it and any other rows in tables that reference tabid 1003 maybe? Or might that screw with other things? Regards, Bryce. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Art (and Mike),
>export DELIMIDENT=1
>dbaccess <database> -
>drop table "FIRST_TEMP";
><ctrl-d>
that did the trick.
Cheers, Bryce.
Related threads
- Error 206 during insert with ESQL/C
- Re: How can I extract just the last(i.e. most current) entry from