Re: informix migration

dbexport/import doesn't work? well that sounds scary - i have never done it before but the senior DBA believes that is the route to go for our migration. it seems strange that something that has been around for so long (and must have been used successfully in the past) now does not work. anywhere you can pont me to to read these documented cases by IBM and others? Thanks Tom "David E. Grove" <david_grove@correct.state.ak.us> wrote in message news:<vqg1ubbhijcf22@corp.supernews.com>... > We are currently contemplating a very similar migration (9.21 to 9.4 on a > new machine-- in our case to a new, upgraded Sun box). We are finding..., > shall we say... "opportunities for professional growth" with dbexport and > dbimport. Currently, we have a tech support case open with IBM on the > problem, but the summary is that dbexport runs successfully and produces a > .sql file that is unusable by dbimport. So, it may be that we can't use > dbexport/dbimport. Apparently this problem has existed (and been documented > by IBM and others) for several years, without being fixed. There is an > alternative to dbexport/dbimport (which we haven't tried yet) at IIUG. The > point is that IDS is an industrial strength, enterprise level product. It > (and fundamental, mature, bundled utilities such as dbimport/dbexport) > should absolutely work. IBM shouldn't have to depend on "the kindness of > strangers" to fix necessary, basic, but defective functionality which comes > bundled with their product. > > (In our opinion, of course-- we realize that IBM, or others, may find this > level of quality perfectly acceptable. They certainly have a right to their > opinions.) > > We, however, do have an Other :-) possible alternative on the horizon. > > DG

On Wed, 05 Nov 2003 10:42:32 -0500, tomL wrote: In the general case dbexport/dbimport do work. There are several specific problems of which I am aware. Most of them are 'corrected' by substituting a schema generated by myschema -l. Some are simple enough to make editing the dbexport generated schema, other are not worth the effort. There are also problems that even myschema does not solve, read on. Known problems (anyone who knows one I missed or knows one has been resolved in some release, please pipe in): o The schema contains NOT NULL constraints with hard coded constraint names based on the original tabid of the table on the source for NOT NULL columns if you created the table/column with an older NOT NULL CLAUSE or if you did not specify an explicit constraint name. If you are importing into an existing database these can clash with existing NOT NULL constraint names. Not a problem for a clean load. Fix: Myschema outputs the older NOT NULL clauses or edit the schema and fix that yourself. o EXTENT sizes in the schema reflect the current values in systables for each table at export time. If the table no longer requires such extent sizing this can allocate unneeded space to a table requiring reorganization later. Fix: Edit the file or use myschema -l with the -m option (also -M if you use fragmented tables extensively). o Other constraint naming problems similar to the NOT NULL problem also cause trouble but these are trouble even in a clean import if tables have been ALTERED after constraint creation and the alters could not be performed in-place or if independent tables were created after dependent tables and the constraints added later. A non-in-place ALTER TABLE creates a new table with a higher tabid but the constraint names still reflect the old tabid which during the import may now belong to another table with similar constraints. Likewise, independent tables will be created before their dependent tables in an import also causing constraint naming clashes. Fix: Myschema -l does not output an explicit constraint name if the name of the constraint matches the pattern used by the engine to name unnamed constraints so the imported constraint will get a new name. Edit the schema and remove the auto-generated constraint names (they are formed with a single letter, tabid, underscore, small number) or give them all better names (I use <tablename>_[PK|UK|FK<n>] where n is a number so multiple FK constraint names are unique). o Object definitions contain owner names which can cause problems migrating from one server to another as one or more of the original 'owners' may not exist on the new server. Fix: myschema -l -O or edit the schema and remove all "ownername". clauses. o Some versions do not generate 'WITH CRCOLS' clauses for ER replicated tables. Fix: Myschema or edit. o Create aggregate statements are not included in some 9.xx releases. The usual fix: Myschema or recreate the aggregates by hand. o There was a problem not in dbexport but in the engine itself where the engine added several extraneous layers of parenthesis to CREATE TRIGGER statements which dbexport would output to the schema. For more complex triggers the resulting trigger may not be valid syntax and will fail to import perhaps due to the size of the SQL syntax check buffer. Fix: Sorry myschema has the same problem and I haven't built a parser yet that can unwind the mess, you'll have to edit the schema, reformatting the trigger so you can follow the parenthesis levels and remove extraneous layers. I think this was finally fixed but those upgrading from earlier versions of the engine (especially if there are triggers created with even earlier versions) may still run into it. o If you created a table OF TYPE that was subsequently altered some versions do not output the ALTER TABLE statements needed to recreate the table in its current state. This will cause an import failure in many 9.xx versions. Fix: Myschema does this correctly also or you can parse through syscolumns to see how the current schema differs from the table type definition. Well, I think that that's it. Art S. Kagel > dbexport/import doesn't work? well that sounds scary - i have never done it > before but the senior DBA believes that is the route to go for our migration. > it seems strange that something that has been around for so long (and must > have been used successfully in the past) now does not work. anywhere you can > pont me to to read these documented cases by IBM and others? Thanks Tom > > > "David E. Grove" <david_grove@correct.state.ak.us> wrote in message > news:<vqg1ubbhijcf22@corp.supernews.com>... >> We are currently contemplating a very similar migration (9.21 to 9.4 on a new >> machine-- in our case to a new, upgraded Sun box). We are finding..., shall >> we say... "opportunities for professional growth" with dbexport and dbimport. >> Currently, we have a tech support case open with IBM on the problem, but the >> summary is that dbexport runs successfully and produces a .sql file that is >> unusable by dbimport. So, it may be that we can't use dbexport/dbimport. >> Apparently this problem has existed (and been documented by IBM and others) >> for several years, without being fixed. There is an alternative to >> dbexport/dbimport (which we haven't tried yet) at IIUG. The point is that >> IDS is an industrial strength, enterprise level product. It (and >> fundamental, mature, bundled utilities such as dbimport/dbexport) should >> absolutely work. IBM shouldn't have to depend on "the kindness of strangers" >> to fix necessary, basic, but defective functionality which comes bundled with >> their product. >> >> (In our opinion, of course-- we realize that IBM, or others, may find this >> level of quality perfectly acceptable. They certainly have a right to their >> opinions.) >> >> We, however, do have an Other :-) possible alternative on the horizon. >> >> DG

On Wed, 05 Nov 2003 13:40:05 -0500, "Art S. Kagel" <kagel@bloomberg.net> wrote: >On Wed, 05 Nov 2003 10:42:32 -0500, tomL wrote: > >In the general case dbexport/dbimport do work. There are several specific >problems of which I am aware. Most of them are 'corrected' by substituting a >schema generated by myschema -l. Some are simple enough to make editing the >dbexport generated schema, other are not worth the effort. There are also >problems that even myschema does not solve, read on. > >Known problems (anyone who knows one I missed or knows one has been resolved in >some release, please pipe in): > >o The schema contains NOT NULL constraints with hard coded constraint names >based on the original tabid of the table on the source for NOT NULL columns if >you created the table/column with an older NOT NULL CLAUSE or if you did not >specify an explicit constraint name. If you are importing into an existing >database these can clash with existing NOT NULL constraint names. Not a problem >for a clean load. Fix: Myschema outputs the older NOT NULL clauses or edit the >schema and fix that yourself. Doesn't seem to be a problem in 9.21 and 9.30 -- dbschema doesn't add not null constraint names. > >o EXTENT sizes in the schema reflect the current values in systables for each >table at export time. If the table no longer requires such extent sizing this >can allocate unneeded space to a table requiring reorganization later. Fix: >Edit the file or use myschema -l with the -m option (also -M if you use >fragmented tables extensively). > This is true for dbschema if the first extent size was overstated. Myschema will take a look at the current value in systables (if used with the correct flags) and shrink it down to what values are actually used. If it is a matter of deleted rows, neither dbschema nor myschema will modify the first extent. ...snip...

Well, it only fails sometimes. I have used it successfully, in the past, as have many (probably most) IDS DBAs. But, sometimes, dbexport can produce a .sql file that dbimport cannot use. I am only aware of my tiny little corner of this issue. Fou us, the issues involve dbexport producing .sql files with syntax errors. This seems to be associated with NON left justified statements (IBM documents this as BUG #157723), with extra CRs ('/013'), and with statements that are too long. If you do a search on "dbimport" and "error" you can find reports of some others' experiences. DG "tomL" <tomcaml@yahoo.com> wrote in message news:3158a6a2.0311050742.7973153d@posting.google.com... > dbexport/import doesn't work? well that sounds scary - i have never > done it before but the senior DBA believes that is the route to go for > our migration. it seems strange that something that has been around > for so long (and must have been used successfully in the past) now > does not work. anywhere you can pont me to to read these documented > cases by IBM and others? > Thanks > Tom > > > "David E. Grove" <david_grove@correct.state.ak.us> wrote in message news:<vqg1ubbhijcf22@corp.supernews.com>... > > We are currently contemplating a very similar migration (9.21 to 9.4 on a > > new machine-- in our case to a new, upgraded Sun box). We are finding..., > > shall we say... "opportunities for professional growth" with dbexport and > > dbimport. Currently, we have a tech support case open with IBM on the > > problem, but the summary is that dbexport runs successfully and produces a > > .sql file that is unusable by dbimport. So, it may be that we can't use > > dbexport/dbimport. Apparently this problem has existed (and been documented > > by IBM and others) for several years, without being fixed. There is an > > alternative to dbexport/dbimport (which we haven't tried yet) at IIUG. The > > point is that IDS is an industrial strength, enterprise level product. It > > (and fundamental, mature, bundled utilities such as dbimport/dbexport) > > should absolutely work. IBM shouldn't have to depend on "the kindness of > > strangers" to fix necessary, basic, but defective functionality which comes > > bundled with their product. > > > > (In our opinion, of course-- we realize that IBM, or others, may find this > > level of quality perfectly acceptable. They certainly have a right to their > > opinions.) > > > > We, however, do have an Other :-) possible alternative on the horizon. > > > > DG

On Wed, 05 Nov 2003 14:06:41 -0500, John Carlson wrote: > On Wed, 05 Nov 2003 13:40:05 -0500, "Art S. Kagel" <kagel@bloomberg.net> > wrote: > >>On Wed, 05 Nov 2003 10:42:32 -0500, tomL wrote: >> >>In the general case dbexport/dbimport do work. There are several specific >>problems of which I am aware. Most of them are 'corrected' by substituting a >>schema generated by myschema -l. Some are simple enough to make editing the >>dbexport generated schema, other are not worth the effort. There are also >>problems that even myschema does not solve, read on. >> >>Known problems (anyone who knows one I missed or knows one has been resolved >>in some release, please pipe in): >> >>o The schema contains NOT NULL constraints with hard coded constraint names >>based on the original tabid of the table on the source for NOT NULL columns if >>you created the table/column with an older NOT NULL CLAUSE or if you did not >>specify an explicit constraint name. If you are importing into an existing >>database these can clash with existing NOT NULL constraint names. Not a >>problem for a clean load. Fix: Myschema outputs the older NOT NULL clauses or >>edit the schema and fix that yourself. > > > Doesn't seem to be a problem in 9.21 and 9.30 -- dbschema doesn't add not null > constraint names. Good to know. >>o EXTENT sizes in the schema reflect the current values in systables for each >>table at export time. If the table no longer requires such extent sizing this >>can allocate unneeded space to a table requiring reorganization later. Fix: >>Edit the file or use myschema -l with the -m option (also -M if you use >>fragmented tables extensively). >> >> > This is true for dbschema if the first extent size was overstated. Myschema > will take a look at the current value in systables (if used with the correct > flags) and shrink it down to what values are actually used. If it is a matter > of deleted rows, neither dbschema nor myschema will modify the first extent. Actually if you add -a to myschema it will report in comments what the extents sizing should be to take account of the deletes and if you specify -m then it will actually use these values (queries from sysmaster:sysptnhdr) in the CREATE TABLE statements. Art S. Kagel > ...snip...