You'll like this one
Renaming a column via DB-Access's interactive Alter Table (Schema Editor) menu on 11.70.FC4 (Fedora 17) returned error -201 and left the table dropped. Others had seen the same thing; it occurred on tables with primary/foreign key constraints (dropping the PK/FK avoided it). Jonathan Leffler identified it as a known DB-Access bug (CQ idsdb00248285), fixed in the 11.70 codeline in March 2013, caused by the Schema Editor mishandling an error while recreating constraints. Running ALTER TABLE directly as SQL (query editor, command line, or any API) is unaffected, and several posters simply advised avoiding the Schema Editor.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
11.70.FC4IE on Fedora 17 Dbaccess - alter table - rename a column -201 syntax error And the table has gone Cheers Paul Paul Watson Oninit www.oninit.com Advanced DataTools www.advancedatatools.com Tel: +1 913 674 0360 Cell: +1 913 387 7529 Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.
Your not alone...
Had the same happen to one of our developers . When he told me the table
vanished I was skeptical as I went to try and do a repro and it didn't
reproduce. It then happened again a week ago and here is what I do know: It
won't reproduce when using the command line (alter table rename col) but will
repro when using dbaccess IF the column being renamed was a column name that
became a reserved word (status). I suspect you need to have the table created
in a much earlier version and then have upgraded to 11.70. I also need to open
a ticket with suppport.
Mark
No reserved words involved here but the table does have a PK and FK - not
referencing the renamed column though. Remove the PK and FK , but leave the
indices that support them and it is fine
No on the upgrade - clean piece on paper migration and new tables
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK
JALKIEWICZ
Sent: Friday, March 29, 2013 9:02 AM
To: ids@iiug.org
Subject: Re: You'll like this one [29907]
Your not alone...
Had the same happen to one of our developers . When he told me the table
vanished I was skeptical as I went to try and do a repro and it didn't
reproduce. It then happened again a week ago and here is what I do know: It
won't reproduce when using the command line (alter table rename col) but
will
repro when using dbaccess IF the column being renamed was a column name that
became a reserved word (status). I suspect you need to have the table
created
in a much earlier version and then have upgraded to 11.70. I also need to
open
a ticket with suppport.
Mark
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
I don't think I would like that at all. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Paul Watson Sent: Friday, March 29, 2013 8:36 AM To: ids@iiug.org Subject: You'll like this one [29906] 11.70.FC4IE on Fedora 17 Dbaccess - alter table - rename a column -201 syntax error And the table has gone Cheers Paul Paul Watson Oninit www.oninit.com Advanced DataTools www.advancedatatools.com Tel: +1 913 674 0360 Cell: +1 913 387 7529 Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
There was a bug for this that was fixed recently (CQ idsdb00248285, checked
in 2013-03-22 for 11.70 version). It was related to constraints being
added or dropped (possibly as a side-effect of some other schema change)
and the symptoms were precisely -201 and table dropped. It is strictly a
problem in the way DB-Access processes schema changes; it is not a problem
in the server itself (it is doing as DB-Access commands it to do).
So - yes, known bug. Yes - fixed in main codelines.
The bug number will help you if/when you open a case with IBM Tech Support.
On Fri, Mar 29, 2013 at 7:06 AM, Paul Watson <paul@oninit.com> wrote:
> No reserved words involved here but the table does have a PK and FK - not
> referencing the renamed column though. Remove the PK and FK , but leave the
> indices that support them and it is fine
>
> No on the upgrade - clean piece on paper migration and new tables
>
> Cheers
> Paul
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK
> JALKIEWICZ
> Sent: Friday, March 29, 2013 9:02 AM
> To: ids@iiug.org
> Subject: Re: You'll like this one [29907]
>
> Your not alone...
>
> Had the same happen to one of our developers . When he told me the table
> vanished I was skeptical as I went to try and do a repro and it didn't
> reproduce. It then happened again a week ago and here is what I do know: It
> won't reproduce when using the command line (alter table rename col) but
> will
> repro when using dbaccess IF the column being renamed was a column name
> that
>
> became a reserved word (status). I suspect you need to have the table
> created
> in a much earlier version and then have upgraded to 11.70. I also need to
> open
> a ticket with suppport.
>
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--14dae93b57403cf82f04d9121eaf
Thanks - sounds like it - when PK and FK are removed all is good
Maybe someone's test plan need to be changed :-)
Cheers
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
On Mar 29, 2013, at 10:42, "Jonathan Leffler" <jonathan.leffler@gmail.com>
wrote:
> There was a bug for this that was fixed recently (CQ idsdb00248285, checked
> in 2013-03-22 for 11.70 version). It was related to constraints being
> added or dropped (possibly as a side-effect of some other schema change)
> and the symptoms were precisely -201 and table dropped. It is strictly a
> problem in the way DB-Access processes schema changes; it is not a problem
> in the server itself (it is doing as DB-Access commands it to do).
>
> So - yes, known bug. Yes - fixed in main codelines.
>
> The bug number will help you if/when you open a case with IBM Tech Support.
>
> On Fri, Mar 29, 2013 at 7:06 AM, Paul Watson <paul@oninit.com> wrote:
>
>> No reserved words involved here but the table does have a PK and FK - not
>> referencing the renamed column though. Remove the PK and FK , but leave the
>> indices that support them and it is fine
>>
>> No on the upgrade - clean piece on paper migration and new tables
>>
>> Cheers
>> Paul
>>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK
>> JALKIEWICZ
>> Sent: Friday, March 29, 2013 9:02 AM
>> To: ids@iiug.org
>> Subject: Re: You'll like this one [29907]
>>
>> Your not alone...
>>
>> Had the same happen to one of our developers . When he told me the table
>> vanished I was skeptical as I went to try and do a repro and it didn't
>> reproduce. It then happened again a week ago and here is what I do know: It
>> won't reproduce when using the command line (alter table rename col) but
>> will
>> repro when using dbaccess IF the column being renamed was a column name
>> that
>>
>> became a reserved word (status). I suspect you need to have the table
>> created
>> in a much earlier version and then have upgraded to 11.70. I also need to
>> open
>> a ticket with suppport.
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
>
> --14dae93b57403cf82f04d9121eaf
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Jonathan.
If I understand you correctly, this reproduce only with the commnands via the
dbaccess utility and not from the command line ?
Mark
It sounds like the problem only appears if you use the (A)lter command in
the dbaccess menu, but if you run an alter SQL via the dbaccess editor you
will be OK.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK
JALKIEWICZ
Sent: Friday, March 29, 2013 11:21 AM
To: ids@iiug.org
Subject: Re: You'll like this one [29912]
Thanks Jonathan.
If I understand you correctly, this reproduce only with the commnands via
the dbaccess utility and not from the command line ?
Mark
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Both correct.
The problem is the 'Schema Editor' code for 'Alter table' mishandling an
error condition.
If you run an ALTER TABLE statement (that you create yourself) directly
either in Query Language option in DB-Access, or in the command-line mode,
or through some other program (ESQL/C, JDBC, ODBC, I4GL, ...) there is no
issue.
On Fri, Mar 29, 2013 at 9:55 AM, Andrew Ford <andrew@informix-dba.com>wrote:
> It sounds like the problem only appears if you use the (A)lter command in
> the dbaccess menu, but if you run an alter SQL via the dbaccess editor you
> will be OK.
>
> -----Original Message-----
> From: ids-bounces@iiug.org On Behalf Of MARK JALKIEWICZ
> Sent: Friday, March 29, 2013 11:21 AM
> Subject: Re: You'll like this one [29912]
>
> Thanks Jonathan.
>
> If I understand you correctly, this reproduce only with the commnands via
> the dbaccess utility and not from the command line?
>
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--089e0160c21cd805e804d9133d8b
I would agree - and table needs RI
Cheers
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
On Mar 29, 2013, at 11:55, "Andrew Ford" <andrew@informix-dba.com> wrote:
> It sounds like the problem only appears if you use the (A)lter command in=20=
> the dbaccess menu, but if you run an alter SQL via the dbaccess editor you=
=20
> will be OK.=20
>=20
> -----Original Message-----=20
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK=
=20
> JALKIEWICZ=20
> Sent: Friday, March 29, 2013 11:21 AM=20
> To: ids@iiug.org=20
> Subject: Re: You'll like this one [29912]=20
>=20
> Thanks Jonathan.=20
>=20
> If I understand you correctly, this reproduce only with the commnands via=20=
> the dbaccess utility and not from the command line ?=20
>=20
> Mark=20
>=20
> **************************************************************************=
**=20
> ***=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
>=20
> **************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
Only happens if you modify a table's schema using the dbaccess menu system.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Mar 29, 2013 at 12:21 PM, MARK JALKIEWICZ <
mark.jalkiewicz@verizon.net> wrote:
> Thanks Jonathan.
>
> If I understand you correctly, this reproduce only with the commnands via
> the
> dbaccess utility and not from the command line ?
>
> Mark
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8fb204869da74f04d9141d13
Schema editor is scary , don't use it! I never touch it!
On Fri, Mar 29, 2013 at 2:05 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Only happens if you modify a table's schema using the dbaccess menu system.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Fri, Mar 29, 2013 at 12:21 PM, MARK JALKIEWICZ <
> mark.jalkiewicz@verizon.net> wrote:
>
> > Thanks Jonathan.
> >
> > If I understand you correctly, this reproduce only with the commnands via
> > the
> > dbaccess utility and not from the command line ?
> >
> > Mark
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8fb204869da74f04d9141d13
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3005dc54a8e3ed04d9161f3b
Good for creating play tables in development databases, should not be needed
for
anything else...
On 29 March 2013 at 20:29 FRANK <yunyaoqu@gmail.com> wrote:
> Schema editor is scary , don't use it! I never touch it!
>
> On Fri, Mar 29, 2013 at 2:05 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Only happens if you modify a table's schema using the dbaccess menu system.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated nor
> > those of the entities themselves.
> >
> > On Fri, Mar 29, 2013 at 12:21 PM, MARK JALKIEWICZ <
> > mark.jalkiewicz@verizon.net> wrote:
> >
> > > Thanks Jonathan.
> > >
> > > If I understand you correctly, this reproduce only with the commnands via
> > > the
> > > dbaccess utility and not from the command line ?
> > >
> > > Mark
> > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8fb204869da74f04d9141d13
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf3005dc54a8e3ed04d9161f3b
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>