dbimport reports constraint that already exists and exits...

This is a multi-part message in MIME format. ------=_NextPart_000_017D_01BF42FD.AECEA2C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi Listers, dbimport of IDS 7.30.UC3 to another server exists with the attached message. I tried removing the table's sql schema from the schema file, but the dbimport reports the same error for other tables. Any ideas. Thanks in advance Denmark W. ------=_NextPart_000_017D_01BF42FD.AECEA2C0 Content-Type: application/msword; name="import.doc" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="import.doc" =0A= *** execute sqlobj=0A= 625 - Constraint name (n218_4834) already exists.=0A= ------=_NextPart_000_017D_01BF42FD.AECEA2C0--

Denmark Weatherburn wrote: > > Hi Listers, > > dbimport of IDS 7.30.UC3 to another server exists with the attached > message. I tried removing the table's sql schema from the schema file, but > the dbimport reports the same error for other tables. > > Any ideas. > > *** execute sqlobj=0A= > 625 - Constraint name (n218_4834) already exists.=0A= I think this might be the same problem we run into with 9.x engine and dbexport. For some strange reason dbexport seems to like to name the constraints (usually "not null" constraints) per row in the sql script. When this happen, the sql script for this table seems like: create table "informix".t_xbanner ( banner_id integer not null constraint "informix".n171_706, banner_nme char(50) not null constraint "informix".n171_707, instead of: create table "informix".t_xbanner ( banner_id integer not null, banner_nme char(50) not null, what then usually happens, is that the dbimport fails like in your case because there are tables that have allready named their own constraints with same names that this t_xbanner table here would like to use. I guess that "the feature" here is, that not all the tables get their constraints named this way by dbexport, and as some of the tables let engine to name them, so this naming procedure will eventually try to use overlapping constraint names. Then the error message is (of course) usually something not_that_informative. We got: *** prepare locktable 206 - The specified table (informix.t_xbanner) is not in the database. 111 - ISAM error: no record found. You will fix this problem either by editing the sql by hand or by generating a perl -script to rip those constraint "blahblah" things off. I hope this helps. * Kimmo Sinkko * E-mail kimmo.sinkko@almamedia.fi_nospam * * Alma Media Net Ventures * (remove _nospam from the address) *

You should have a look on Art S. Kagel's utilities including an alternative to dbschema (IIUG). Hth, Chris

> > Hi Listers, > > dbimport of IDS 7.30.UC3 to another server exists with the attached > message. I tried removing the table's sql schema from the schema file, but > the dbimport reports the same error for other tables. Yes, there is a bug in dbexport related to the constraint namings in export schema file. Simply rename constraint name in schema file, then re-run dbimport. HTH. -- ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285

The problem is that dbexport makes the implicit constraint names generated for unnamed constraints like NOT NULL constraints explicit and these names were generated using the table's tabid and colno. In the database into which you are dbimporting the tables there is already a table with that tabid and colno with a NOT NULL constraint of the same name. You can either tediously edit the schema file to remove the explicit constraint names or add your own constraint names that will not clash OR you can get my utility myschema.ec which is a dbschema replacement which, unlike dbschema, can output a dbimport compatible schema script and does NOT output default constraint names. It also has many other useful and desireable features lacking in dbschema. Myschema.ec is part of the package utils2_ak available in the IIUG Software Repository. Art S. Kagel Kimmo Sinkko wrote: > > Denmark Weatherburn wrote: > > > > Hi Listers, > > > > dbimport of IDS 7.30.UC3 to another server exists with the attached > > message. I tried removing the table's sql schema from the schema file, but > > the dbimport reports the same error for other tables. > > > > Any ideas. > > > > *** execute sqlobj=0A= > > 625 - Constraint name (n218_4834) already exists.=0A= > > I think this might be the same problem we run into with 9.x engine and > dbexport. > > For some strange reason dbexport seems to like to name the constraints > (usually "not null" constraints) per row in the sql script. When this > happen, the sql script for this table seems like: > > create table "informix".t_xbanner > ( > banner_id integer not null constraint "informix".n171_706, > banner_nme char(50) not null constraint "informix".n171_707, > > instead of: > > create table "informix".t_xbanner > ( > banner_id integer not null, > banner_nme char(50) not null, > > what then usually happens, is that the dbimport fails like in your case > because there are tables that have allready named their own constraints > with same names that this t_xbanner table here would like to use. > > I guess that "the feature" here is, that not all the tables get their > constraints named this way by dbexport, and as some of the tables let > engine to name them, so this naming procedure will eventually try to use > overlapping constraint names. Then the error message is (of course) > usually something not_that_informative. We got: > > *** prepare locktable > 206 - The specified table (informix.t_xbanner) is not in the database. > > 111 - ISAM error: no record found. > > You will fix this problem either by editing the sql by hand or by > generating a perl -script to rip those constraint "blahblah" things off. > > I hope this helps. > > * Kimmo Sinkko * E-mail kimmo.sinkko@almamedia.fi_nospam * > * Alma Media Net Ventures * (remove _nospam from the address) *

Art: I think this started happening in one of those INFORMIX version > 7.0 < 7.30. In later vesions of INFORMIX > 7.30 the problem donot surface anymore. Rgds "Art S. Kagel" wrote: > The problem is that dbexport makes the implicit constraint names generated > for unnamed constraints like NOT NULL constraints explicit and these names > were generated using the table's tabid and colno. In the database into which > you are dbimporting the tables there is already a table with that tabid and > colno with a NOT NULL constraint of the same name. You can either tediously > edit the schema file to remove the explicit constraint names or add your own > constraint names that will not clash OR you can get my utility myschema.ec > which is a dbschema replacement which, unlike dbschema, can output a dbimport > compatible schema script and does NOT output default constraint names. It > also has many other useful and desireable features lacking in dbschema. > Myschema.ec is part of the package utils2_ak available in the IIUG Software > Repository. > > Art S. Kagel > > Kimmo Sinkko wrote: > > > > Denmark Weatherburn wrote: > > > > > > Hi Listers, > > > > > > dbimport of IDS 7.30.UC3 to another server exists with the attached > > > message. I tried removing the table's sql schema from the schema file, but > > > the dbimport reports the same error for other tables. > > > > > > Any ideas. > > > > > > *** execute sqlobj=0A= > > > 625 - Constraint name (n218_4834) already exists.=0A= > > > > I think this might be the same problem we run into with 9.x engine and > > dbexport. > > > > For some strange reason dbexport seems to like to name the constraints > > (usually "not null" constraints) per row in the sql script. When this > > happen, the sql script for this table seems like: > > > > create table "informix".t_xbanner > > ( > > banner_id integer not null constraint "informix".n171_706, > > banner_nme char(50) not null constraint "informix".n171_707, > > > > instead of: > > > > create table "informix".t_xbanner > > ( > > banner_id integer not null, > > banner_nme char(50) not null, > > > > what then usually happens, is that the dbimport fails like in your case > > because there are tables that have allready named their own constraints > > with same names that this t_xbanner table here would like to use. > > > > I guess that "the feature" here is, that not all the tables get their > > constraints named this way by dbexport, and as some of the tables let > > engine to name them, so this naming procedure will eventually try to use > > overlapping constraint names. Then the error message is (of course) > > usually something not_that_informative. We got: > > > > *** prepare locktable > > 206 - The specified table (informix.t_xbanner) is not in the database. > > > > 111 - ISAM error: no record found. > > > > You will fix this problem either by editing the sql by hand or by > > generating a perl -script to rip those constraint "blahblah" things off. > > > > I hope this helps. > > > > * Kimmo Sinkko * E-mail kimmo.sinkko@almamedia.fi_nospam * > > * Alma Media Net Ventures * (remove _nospam from the address) *