import db from 32 bit informix to 64 bit informix

I am tring to export a database with spatail data form spatial.8.21.HC1 IBM Informix Dynamic Server Version 9.40.HC5 HP-UX wdcuast5 B.11.11 U 9000/800 dbimport to to IBM Informix Dynamic Server Version 11.50.FC9 Linux hvgrndev66 2.6.32-279.19.1.el6.x86_64 #1 SMP Sat Nov 24 14:35:28 EST 2012 x86_64 x86_64 x86_64 GNU/Linux spatial.8.21.FC1.LINUX86 This fails because of the spatail stufff being 64 bit on Linux I tried to unload spatail blade from database on database on HPUX . But this fails. Any ideas how i can migrate this database? some of the errors Mack Release 4.10.UC5 Sep 16 2004 blade: ifxmngr err expct: expected SQL stmt: insert into informix.systraceclasses(name)| values ('blademgr'); SQL state: X23000:-239 : Could not insert new row - duplicate value in a UNIQUE INDEX column.XIX000: -100 : : ISAM error: duplicate value for a record with unique key. blade: ifxmngr err expct: expected SQL stmt: drop type SYSBldSQLText restrict; SQL state: XIX000:-9630 : Cannot drop type (sysbldsqltext): still in use. blade: ifxmngr err expct: expected SQL stmt: create opaque type SYSBldSQLText(| internallength = variable,| maxlen = 24000,| alignment = 1|); SQL state: XIX000:-9631 : Opaque type (sysbldsqltext) already exists in database. blade: ifxmngr err expct: expected SQL stmt: create function SysBldLike (SYSBldSQLText,SYSBldSQLText)|returns boolean|external name '$ INFORMIXDIR/extend/ifxmngr/ifxmngr.bld(SYSBldSQLTextLike)'|language c; SQL state: XIX000:-673 : Another routine (sysbldlike) with same signature already exists in database . blade: ifxmngr err expct: expected SQL stmt: create table sysbldobjects| (create_order serial8, bld_id CHAR(64) not null,| obj_ kind integer default 0,| obj_signature lvarchar not null,| obj_owner CHAR(64) default 'infor mix',| sequence integer default 0,| create_sql SYSBldSQLText, create_can_fail boolean defaul t 'f',| drop_sql lvarchar, drop_can_fail boolean default 'f',| alter_sql lvarchar, alter_can _fail boolean default 'f',| state char default 'U',| temp_state char default 'U',| final _state char default 'U',| metadata lvarchar,| reserved1 varchar(255),| reserved2 integer ,| reserved3 integer| ); SQL state: XS0001:-310 : Table (informix.sysbldobjects) already exists in database. blade: ifxmngr err expct: expected SQL stmt: create index sysbldobjindex on sysbldobjects (create_order); SQL state: XS0011:-316 : Index (sysbldobjindex) already exists in database. blade: ifxmngr err expct: expected SQL stmt: create table sysbldobjdepends| (bld_id CHAR(64) not null,| obj_kind integer defaul t 0,| obj_signature lvarchar not null,| obj_owner CHAR(64) default 'informix',| need_obj _kind integer default 0,| need_obj_signature lvarchar not null,| need_obj_owner CHAR(64) def ault 'informix',| state char default 'U',| temp_state char default 'U',| final_state cha r default 'U',| reserved1 varchar(255),| reserved2 integer,| reserved3 integer| ); SQL state: XS0001:-310 : Table (informix.sysbldobjdepends) already exists in database. blade: ifxmngr err expct: expected SQL stmt: create table sysbldobjkinds| (obj_kind integer not null primary key,| description varchar (32),| format varchar (64)| ); SQL state: XS0001:-310 : Table (informix.sysbldobjkinds) already exists in database. blade: ifxmngr err expct: expected SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| values (0, 'unknown', '<an y>'); SQL state: X23000:-268 : Unique constraint (informix.u103_54) violated.XIX000:-100 : : ISAM error: duplicate value for a record with unique key. blade: ifxmngr err expct: expected SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| values (1, 'error', '<code >-<locale>'); SQL state: X23000:-268 : Unique constraint (informix.u103_54) violated.XIX000:-100 : : ISAM error: duplicate value for a record with unique key. blade: ifxmngr err expct: expected SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| values (2, 'aggregate', '< name>(<type>)'); SQL state: X23000:-268 : Unique constraint (informix.u103_54) violated.XIX000:-100 : : ISAM error: duplicate value for a record with unique key.

Importing databases with datablade content into 11.70 is problematic because the export contains content from the datablade's metadata tables. However, as soon as you reference any of the datatypes supported by a datablade in 11.70 the server will trigger a stored procedure that will install the datablade causing the metadata tables to become already populated which will result in the import failing with duplicate key errors. Contact IBM for how safely perform this export and import. 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 Tue, Jan 29, 2013 at 8:41 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > I am tring to export a database with spatail data form > spatial.8.21.HC1 > IBM Informix Dynamic Server Version 9.40.HC5 > HP-UX wdcuast5 B.11.11 U 9000/800 > > dbimport to to > IBM Informix Dynamic Server Version 11.50.FC9 > Linux hvgrndev66 2.6.32-279.19.1.el6.x86_64 #1 SMP Sat Nov 24 14:35:28 EST > 2012 x86_64 x86_64 x86_64 > GNU/Linux > > spatial.8.21.FC1.LINUX86 > > This fails because of the spatail stufff being 64 bit on Linux > I tried to unload spatail blade from database on database on HPUX . > But this fails. > Any ideas how i can migrate this database? > some of the errors > Mack Release 4.10.UC5 Sep 16 2004 > > blade: ifxmngr > err expct: expected > SQL stmt: insert into informix.systraceclasses(name)| values ('blademgr'); > SQL state: X23000:-239 : Could not insert new row - duplicate value in a > UNIQUE INDEX column.XIX000: > -100 : : ISAM error: duplicate value for a record with unique key. > > blade: ifxmngr > err expct: expected > SQL stmt: drop type SYSBldSQLText restrict; > SQL state: XIX000:-9630 : Cannot drop type (sysbldsqltext): still in use. > > blade: ifxmngr > err expct: expected > SQL stmt: create opaque type SYSBldSQLText(| internallength = variable,| > maxlen = 24000,| alignment > = 1|); > SQL state: XIX000:-9631 : Opaque type (sysbldsqltext) already exists in > database. > > blade: ifxmngr > err expct: expected > SQL stmt: create function SysBldLike (SYSBldSQLText,SYSBldSQLText)|returns > boolean|external name '$ > INFORMIXDIR/extend/ifxmngr/ifxmngr.bld(SYSBldSQLTextLike)'|language c; > SQL state: XIX000:-673 : Another routine (sysbldlike) with same signature > already exists in database > .. > > blade: ifxmngr > err expct: expected > SQL stmt: create table sysbldobjects| (create_order serial8, bld_id > CHAR(64) > not null,| obj_ > kind integer default 0,| obj_signature lvarchar not null,| obj_owner > CHAR(64) > default 'infor > mix',| sequence integer default 0,| create_sql SYSBldSQLText, > create_can_fail > boolean defaul > t 'f',| drop_sql lvarchar, drop_can_fail boolean default 'f',| alter_sql > lvarchar, alter_can > _fail boolean default 'f',| state char default 'U',| temp_state char > default > 'U',| final > _state char default 'U',| metadata lvarchar,| reserved1 varchar(255),| > reserved2 integer > ,| reserved3 integer| ); > SQL state: XS0001:-310 : Table (informix.sysbldobjects) already exists in > database. > > blade: ifxmngr > err expct: expected > SQL stmt: create index sysbldobjindex on sysbldobjects (create_order); > SQL state: XS0011:-316 : Index (sysbldobjindex) already exists in database. > > blade: ifxmngr > err expct: expected > SQL stmt: create table sysbldobjdepends| (bld_id CHAR(64) not null,| > obj_kind > integer defaul > t 0,| obj_signature lvarchar not null,| obj_owner CHAR(64) default > 'informix',| need_obj > _kind integer default 0,| need_obj_signature lvarchar not null,| > need_obj_owner CHAR(64) def > ault 'informix',| state char default 'U',| temp_state char default 'U',| > final_state cha > r default 'U',| reserved1 varchar(255),| reserved2 integer,| reserved3 > integer| ); > SQL state: XS0001:-310 : Table (informix.sysbldobjdepends) already exists > in > database. > > blade: ifxmngr > err expct: expected > SQL stmt: create table sysbldobjkinds| (obj_kind integer not null primary > key,| description > varchar (32),| format varchar (64)| ); > SQL state: XS0001:-310 : Table (informix.sysbldobjkinds) already exists in > database. > > blade: ifxmngr > err expct: expected > SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| > values > (0, 'unknown', '<an > y>'); > SQL state: X23000:-268 : Unique constraint (informix.u103_54) > violated.XIX000:-100 : : ISAM error: > duplicate value for a record with unique key. > > blade: ifxmngr > err expct: expected > SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| > values > (1, 'error', '<code > >-<locale>'); > SQL state: X23000:-268 : Unique constraint (informix.u103_54) > violated.XIX000:-100 : : ISAM error: > duplicate value for a record with unique key. > > blade: ifxmngr > err expct: expected > SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| > values > (2, 'aggregate', '< > name>(<type>)'); > SQL state: X23000:-268 : Unique constraint (informix.u103_54) > violated.XIX000:-100 : : ISAM error: > duplicate value for a record with unique key. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e0cb4efe33a8e372a804d477e4cc

Got to love the way you dbexport and then the dbimport doesn't work - even funny when you are working with same OS/IDS version - happens too often IMHO Cheers Paul > Importing databases with datablade content into 11.70 is problematic > because the export contains content from the datablade's metadata tables. > However, as soon as you reference any of the datatypes supported by a > datablade in 11.70 the server will trigger a stored procedure that will > install the datablade causing the metadata tables to become already > populated which will result in the import failing with duplicate key > errors. > > Contact IBM for how safely perform this export and import. > > 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 Tue, Jan 29, 2013 at 8:41 PM, KARL OLIVER > <karl.oliver@maf.govt.nz>wrote: > >> I am tring to export a database with spatail data form >> spatial.8.21.HC1 >> IBM Informix Dynamic Server Version 9.40.HC5 >> HP-UX wdcuast5 B.11.11 U 9000/800 >> >> dbimport to to >> IBM Informix Dynamic Server Version 11.50.FC9 >> Linux hvgrndev66 2.6.32-279.19.1.el6.x86_64 #1 SMP Sat Nov 24 14:35:28 >> EST >> 2012 x86_64 x86_64 x86_64 >> GNU/Linux >> >> spatial.8.21.FC1.LINUX86 >> >> This fails because of the spatail stufff being 64 bit on Linux >> I tried to unload spatail blade from database on database on HPUX . >> But this fails. >> Any ideas how i can migrate this database? >> some of the errors >> Mack Release 4.10.UC5 Sep 16 2004 >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: insert into informix.systraceclasses(name)| values >> ('blademgr'); >> SQL state: X23000:-239 : Could not insert new row - duplicate value in a >> UNIQUE INDEX column.XIX000: >> -100 : : ISAM error: duplicate value for a record with unique key. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: drop type SYSBldSQLText restrict; >> SQL state: XIX000:-9630 : Cannot drop type (sysbldsqltext): still in >> use. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: create opaque type SYSBldSQLText(| internallength = variable,| >> maxlen = 24000,| alignment >> = 1|); >> SQL state: XIX000:-9631 : Opaque type (sysbldsqltext) already exists in >> database. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: create function SysBldLike >> (SYSBldSQLText,SYSBldSQLText)|returns >> boolean|external name '$ >> INFORMIXDIR/extend/ifxmngr/ifxmngr.bld(SYSBldSQLTextLike)'|language c; >> SQL state: XIX000:-673 : Another routine (sysbldlike) with same >> signature >> already exists in database >> .. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: create table sysbldobjects| (create_order serial8, bld_id >> CHAR(64) >> not null,| obj_ >> kind integer default 0,| obj_signature lvarchar not null,| obj_owner >> CHAR(64) >> default 'infor >> mix',| sequence integer default 0,| create_sql SYSBldSQLText, >> create_can_fail >> boolean defaul >> t 'f',| drop_sql lvarchar, drop_can_fail boolean default 'f',| alter_sql >> lvarchar, alter_can >> _fail boolean default 'f',| state char default 'U',| temp_state char >> default >> 'U',| final >> _state char default 'U',| metadata lvarchar,| reserved1 varchar(255),| >> reserved2 integer >> ,| reserved3 integer| ); >> SQL state: XS0001:-310 : Table (informix.sysbldobjects) already exists >> in >> database. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: create index sysbldobjindex on sysbldobjects (create_order); >> SQL state: XS0011:-316 : Index (sysbldobjindex) already exists in >> database. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: create table sysbldobjdepends| (bld_id CHAR(64) not null,| >> obj_kind >> integer defaul >> t 0,| obj_signature lvarchar not null,| obj_owner CHAR(64) default >> 'informix',| need_obj >> _kind integer default 0,| need_obj_signature lvarchar not null,| >> need_obj_owner CHAR(64) def >> ault 'informix',| state char default 'U',| temp_state char default 'U',| >> final_state cha >> r default 'U',| reserved1 varchar(255),| reserved2 integer,| reserved3 >> integer| ); >> SQL state: XS0001:-310 : Table (informix.sysbldobjdepends) already >> exists >> in >> database. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: create table sysbldobjkinds| (obj_kind integer not null >> primary >> key,| description >> varchar (32),| format varchar (64)| ); >> SQL state: XS0001:-310 : Table (informix.sysbldobjkinds) already exists >> in >> database. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| >> values >> (0, 'unknown', '<an >> y>'); >> SQL state: X23000:-268 : Unique constraint (informix.u103_54) >> violated.XIX000:-100 : : ISAM error: >> duplicate value for a record with unique key. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| >> values >> (1, 'error', '<code >> >-<locale>'); >> SQL state: X23000:-268 : Unique constraint (informix.u103_54) >> violated.XIX000:-100 : : ISAM error: >> duplicate value for a record with unique key. >> >> blade: ifxmngr >> err expct: expected >> SQL stmt: insert into sysbldobjkinds (obj_kind, description, format)| >> values >> (2, 'aggregate', '< >> name>(<type>)'); >> SQL state: X23000:-268 : Unique constraint (informix.u103_54) >> violated.XIX000:-100 : : ISAM error: >> duplicate value for a record with unique key. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --e0cb4efe33a8e372a804d477e4cc > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

it is the spatail blade issue dir names on 32 bit dir name spatial.8.20.HC2 on 64 bit system spatial.8.21.FC1 import looks for spatial.8.20.HC2 Anyway have logging pmr with IBM

You might do better to create a new target database and register spatial.8.21.HC1 in that. Create any tables you need to migrate, including spatial ones, in that database. You can use dbschema to save the DDL from the source database and edit the first/next extent sizes to values (appropriate to your regular as well as any of your out of row spatial data) before you run the DDL into the target database. You can then export and import from source database to the target database using files, pipes, esql, java... program. The current spatial release is 8.21.xC4 I believe, but check with IBM. Stuart McCann -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KARL OLIVER Sent: Thursday, 31 January 2013 6:51 AM To: ids@iiug.org Subject: Re: import db from 32 bit informix to 64 bit i.... [29447] it is the spatail blade issue dir names on 32 bit dir name spatial.8.20.HC2 on 64 bit system spatial.8.21.FC1 import looks for spatial.8.20.HC2 Anyway have logging pmr with IBM ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the NSW Government. This email message has been swept by MIMEsweeper for the presence of computer viruses. *************************************************************** Please consider the environment before printing this email.

Stuart must be a better way than that. I am trying to unregister datblade on souce system cant even do that ! get this meassge- anyone have ideas please Unregister module spatial.8.21.HC1 out of database test0811? [Y/n]y blademgr: ERROR: DataBlade spatial.8.21.HC1 could not be unregistered from database test0811. Use 'show last log' for details of failures in module's scripts. uast5_response>show last log Mack Release 4.10.UC5 Sep 16 2004 blade: ifxmngr err expct: expected SQL stmt: insert into informix.systraceclasses(name)| values ('blademgr'); SQL state: X23000:-239 : Could not insert new row - duplicate value in a UNIQUE INDEX column.XIX000: -100 : : ISAM error: duplicate value for a record with unique key. blade: ifxmngr err expct: expected SQL stmt: drop type SYSBldSQLText restrict; SQL state: XIX000:-9630 : Cannot drop type (sysbldsqltext): still in use. blade: ifxmngr err expct: expected SQL stmt: create opaque type SYSBldSQLText(| internallength = variable,| maxlen = 24000,| alignment = 1|); SQL state: XIX000:-9631 : Opaque type (sysbldsqltext) already exists in database. blade: ifxmngr err expct: expected SQL stmt: create function SysBldLike (SYSBldSQLText,SYSBldSQLText)|returns boolean|external name '$ INFORMIXDIR/extend/ifxmngr/ifxmngr.bld(SYSBldSQLTextLike)'|language c;

Changing the subject a bit, based on the last response... I'm having a very similar issue with unregister of web.4.13.UC3 from an AIX 6.1 system, using IDS 10.00.UC10X1 (upgrading to 11.7 as part of this project). We've used the html data type that's defined in WebBlade, so I've removed all of our homegrown tables/functions/etc.; I've run the SQL from the notes file to removeAdminTool and removeAPB2schema. Yet I still get the error about sysbldsqltext still being in use. ... blade: ifxmngr err expct: expected SQL stmt: insert into informix.systraceclasses(name)| values ('blademgr'); SQL state: X23000:-239 : Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:informix.systraceclasses) .XIX000:-100 : : ISAM error: duplicate value for a record with unique key. blade: ifxmngr err expct: expected SQL stmt: drop type SYSBldSQLText restrict; SQL state: XIX000:-9630 : Cannot drop type (sysbldsqltext): still in use. ... Does anyone have any ideas?