Migrating from 7.31 to 11.70 with ER?

I'm looking into performing a migration from 7.31 to 11.70 with ER. I'm not even sure this is possible, although from what I've read it seems to be feasible. I'm now reading through this IBM doc, http://www-01.ibm.com/support/docview.wss?uid=swg21153338 and I'm reading about how to create the sbspace to spool the transaction queue. This is set with the parameter: CDR_QDATA_SBSPACE but neither sbspaces nor this parameter exist in 7.31. So two questions: 1. Is this type of migration even possible? 2. If it is, how would I resolve CDR_QDATA_SBSPACE?

Luke, In my humble opinion ( and experience ) don´t bother trying to migrate it. Kill replication and recreate it again when the instances are in 11.70. It makes life a lot easier. As you will have to stop the instances to upgrade them anyway it seem a simple way forward. Good luck. > To: ids@iiug.org > From: luke.simmons@vgregion.se > Subject: Migrating from 7.31 to 11.70 with ER? [26762] > Date: Fri, 20 Apr 2012 09:06:55 -0400 > > I'm looking into performing a migration from 7.31 to 11.70 with ER. I'm not > even sure this is possible, although from what I've read it seems to be > feasible. > > I'm now reading through this IBM doc, > > http://www-01.ibm.com/support/docview.wss?uid=swg21153338 > > and I'm reading about how to create the sbspace to spool the transaction > queue. This is set with the parameter: > > CDR_QDATA_SBSPACE> > but neither sbspaces nor this parameter exist in 7.31. > > So two questions: > > 1. Is this type of migration even possible? > 2. If it is, how would I resolve CDR_QDATA_SBSPACE? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

It's possible. We've had some rather large customers do it. Are you currently using ER or are you wanting to use ER to prevent any = down time? The ER migration is a manual process for the reason that you indicated.= In post-9.30 versions, ER uses a smartblob space to hold the queue on disk= . So you must perform the migration by doing 1) bring down ER with nothing in the queues.... 2) convert the server to 11.70 3) create the sblob space (CDR_QDATA_SBSPACE) 4) convert ER by running the conversion script in $INFORMIXDIR/etc If you do not have ER in place and are trying to prevent down time, the= n you have a bit more of a challenge. I'm not sure that I would do it because of the learning curve. The 7.31 server does not support cdr check/sync and without that it's a bit difficult to get your data back = in sync. = = From: "LUKE SIMMONS" <luke.simmons@vgregion.se> = = = = To: ids@iiug.org = = = = Date: 04/20/2012 08:08 AM = = = = Subject: Migrating from 7.31 to 11.70 with ER? [26762] = = = = Sent by: ids-bounces@iiug.org = = = = I'm looking into performing a migration from 7.31 to 11.70 with ER. I'm= not even sure this is possible, although from what I've read it seems to be= feasible. I'm now reading through this IBM doc, http://www-01.ibm.com/support/docview.wss?uid=3Dswg21153338 and I'm reading about how to create the sbspace to spool the transactio= n queue. This is set with the parameter: CDR_QDATA_SBSPACE but neither sbspaces nor this parameter exist in 7.31. So two questions: 1. Is this type of migration even possible? 2. If it is, how would I resolve CDR_QDATA_SBSPACE? ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

According to the official documentation here: http://www-01.ibm.com/support/docview.wss?uid=swg21259394 You could migrate it directly. It´s even hard to find documentation regarding ER on 7.31 nowadays. If you want to minimize downtime, using replication, and you are migrating to a much better machine, I would suggest you to: 1) do an in place migration on 7.31 engine (the running time will mostly depend on your database size, because of the data page convertions. But I´ve already did several in-place upgrades of around 10Gig databases easily, around 10 or 15 minutes, on very simple boxes. 2) then you could easily start an ER environment between the old and new ones, and let the sync finish Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: agrantha@hotmail.com > Subject: RE: Migrating from 7.31 to 11.70 with ER? [26763] > Date: Fri, 20 Apr 2012 09:22:20 -0400 > > Luke, > > In my humble opinion ( and experience ) don´t bother trying to migrate it. > Kill replication and recreate it again when the instances are in 11.70. It > makes life a lot easier. As you will have to stop the instances to upgrade > them anyway it seem a simple way forward. > > Good luck. > > > To: ids@iiug.org > > From: luke.simmons@vgregion.se > > Subject: Migrating from 7.31 to 11.70 with ER? [26762] > > Date: Fri, 20 Apr 2012 09:06:55 -0400 > > > > I'm looking into performing a migration from 7.31 to 11.70 with ER. I'm not > > even sure this is possible, although from what I've read it seems to be > > feasible. > > > > I'm now reading through this IBM doc, > > > > http://www-01.ibm.com/support/docview.wss?uid=swg21153338 > > > > and I'm reading about how to create the sbspace to spool the transaction > > queue. This is set with the parameter: > > > > CDR_QDATA_SBSPACE> > > > but neither sbspaces nor this parameter exist in 7.31. > > > > So two questions: > > > > 1. Is this type of migration even possible? > > 2. If it is, how would I resolve CDR_QDATA_SBSPACE? > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

We're trying to limit the amount of downtime, so there's no ER in place. We'll set up HDR after the migration. We're moving between versions and operating systems, but the amount of data that will change during the migration should be minimal. I suppose the easiest option would be to upgrade and then migrate, but I would even prefer limiting the amount of downtime to even more than the time that it would take to do an in-place upgrade. Even if the learning curve might be a bit a steep, it would be nice to have that information and I can't really seem to find anything out there with the exception of the link in the original post, especially migrating from 7.31. And that link seems to be pretty straight forward with the exeception of the inability of creating an sbspace to spool transactions. Paul mentioned that the 11.70 server needs to be the master. I'm not really sure what that means. I certainly don't want to replicate data over to 7.31! Maybe though there's something I'm missing. We're just doing a 1 to 1 replication. We simply want to clone our production environment.

It´s not the master from the architecture meaning, really. When doing ER from different versions, you should always type the commands, create all ER structures on the newest release. I think it´s that what he mean. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: luke.simmons@vgregion.se > Subject: Re: Migrating from 7.31 to 11.70 with ER? [26769] > Date: Fri, 20 Apr 2012 10:33:42 -0400 > > We're trying to limit the amount of downtime, so there's no ER in place. We'll > set up HDR after the migration. > > We're moving between versions and operating systems, but the amount of data > that will change during the migration should be minimal. > > I suppose the easiest option would be to upgrade and then migrate, but I would > even prefer limiting the amount of downtime to even more than the time that it > would take to do an in-place upgrade. > > Even if the learning curve might be a bit a steep, it would be nice to have > that information and I can't really seem to find anything out there with the > exception of the link in the original post, especially migrating from 7.31. > > And that link seems to be pretty straight forward with the exeception of the > inability of creating an sbspace to spool transactions. > > Paul mentioned that the 11.70 server needs to be the master. I'm not really > sure what that means. I certainly don't want to replicate data over to 7.31! > Maybe though there's something I'm missing. We're just doing a 1 to 1 > replication. We simply want to clone our production environment. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

This is ABSOLUTELY doable. I have three major clients working on porting from 7.31, 9.40, and 10.00 to 11.50 or 11.70 using ER. The 7.31-11.70 client just completed testing the ER replication between the versions and it was working like a charm. 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, Apr 20, 2012 at 9:06 AM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > I'm looking into performing a migration from 7.31 to 11.70 with ER. I'm not > even sure this is possible, although from what I've read it seems to be > feasible. > > I'm now reading through this IBM doc, > > http://www-01.ibm.com/support/docview.wss?uid=swg21153338 > > and I'm reading about how to create the sbspace to spool the transaction > queue. This is set with the parameter: > > CDR_QDATA_SBSPACE> > but neither sbspaces nor this parameter exist in 7.31. > > So two questions: > > 1. Is this type of migration even possible? > 2. If it is, how would I resolve CDR_QDATA_SBSPACE? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d5901cd9604be20ff2f

It would be wise, however, to engage a consultant that has worked with = ER if you have not used it in the past. = = From: "Art Kagel" <art.kagel@gmail.com> = = = = To: ids@iiug.org = = = = Date: 04/20/2012 02:08 PM = = = = Subject: Re: Migrating from 7.31 to 11.70 with ER? [26780] = = = = Sent by: ids-bounces@iiug.org = = = = This is ABSOLUTELY doable. I have three major clients working on portin= g from 7.31, 9.40, and 10.00 to 11.50 or 11.70 using ER. The 7.31-11.70 client just completed testing the ER replication between the versions a= nd it was working like a charm. 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 opinion= s and do not reflect on my employer, Advanced DataTools, the IIUG, nor an= y 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, Apr 20, 2012 at 9:06 AM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > I'm looking into performing a migration from 7.31 to 11.70 with ER. I= 'm not > even sure this is possible, although from what I've read it seems to = be > feasible. > > I'm now reading through this IBM doc, > > http://www-01.ibm.com/support/docview.wss?uid=3Dswg21153338 > > and I'm reading about how to create the sbspace to spool the transact= ion > queue. This is set with the parameter: > > CDR_QDATA_SBSPACE> > but neither sbspaces nor this parameter exist in 7.31. > > So two questions: > > 1. Is this type of migration even possible? > 2. If it is, how would I resolve CDR_QDATA_SBSPACE? > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d5901cd9604be20ff2f ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Doing the migration with ER will allow you to have an almost instant minimal downtime turnover to the new version and minimal downtime fallback to 7.31 if there are some unexpected problems after turnover that didn't show up during testing. 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, Apr 20, 2012 at 10:33 AM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > We're trying to limit the amount of downtime, so there's no ER in place. > We'll > set up HDR after the migration. > > We're moving between versions and operating systems, but the amount of data > that will change during the migration should be minimal. > > I suppose the easiest option would be to upgrade and then migrate, but I > would > even prefer limiting the amount of downtime to even more than the time > that it > would take to do an in-place upgrade. > > Even if the learning curve might be a bit a steep, it would be nice to have > that information and I can't really seem to find anything out there with > the > exception of the link in the original post, especially migrating from 7.31. > > And that link seems to be pretty straight forward with the exeception of > the > inability of creating an sbspace to spool transactions. > > Paul mentioned that the 11.70 server needs to be the master. I'm not really > sure what that means. I certainly don't want to replicate data over to > 7.31! > Maybe though there's something I'm missing. We're just doing a 1 to 1 > replication. We simply want to clone our production environment. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba75f53a6e804be21217e

Being one of those consultants, I have to agree. 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, Apr 20, 2012 at 3:16 PM, Madison Pruet <mpruet@us.ibm.com> wrote: > It would be wise, however, to engage a consultant that has worked with = > ER > if you have not used it in the past. > > = > > = > > From: "Art Kagel" <art.kagel@gmail.com> = > > = > > = > > = > > To: ids@iiug.org = > > = > > = > > = > > Date: 04/20/2012 02:08 PM = > > = > > = > > = > > Subject: Re: Migrating from 7.31 to 11.70 with ER? [26780] = > > = > > = > > = > > Sent by: ids-bounces@iiug.org = > > = > > = > > = > > This is ABSOLUTELY doable. I have three major clients working on portin= > g > from 7.31, 9.40, and 10.00 to 11.50 or 11.70 using ER. The 7.31-11.70 > client just completed testing the ER replication between the versions a= > nd > it was working like a charm. > > 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 opinion= > s > and do not reflect on my employer, Advanced DataTools, the IIUG, nor an= > y > 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, Apr 20, 2012 at 9:06 AM, LUKE SIMMONS > <luke.simmons@vgregion.se>wrote: > > > I'm looking into performing a migration from 7.31 to 11.70 with ER. I= > 'm > not > > even sure this is possible, although from what I've read it seems to = > be > > feasible. > > > > I'm now reading through this IBM doc, > > > > http://www-01.ibm.com/support/docview.wss?uid=3Dswg21153338 > > > > and I'm reading about how to create the sbspace to spool the transact= > ion > > queue. This is set with the parameter: > > > > CDR_QDATA_SBSPACE> > > > but neither sbspaces nor this parameter exist in 7.31. > > > > So two questions: > > > > 1. Is this type of migration even possible? > > 2. If it is, how would I resolve CDR_QDATA_SBSPACE? > > > > > > > > > ***********************************************************************= > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340d5901cd9604be20ff2f > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d591cd6e604be212911

Thanks for the replies. Like I said from the beginning, we're just starting out, learning our options and preparing to make a trial run-through and as you mentioned, a very minimal amount of downtime would be highly appreciated with this upgrade as it has to do with a very large healthcare application. I'm working with this IBM doc: http://www-01.ibm.com/support/docview.wss?uid=swg21153338 And as I said before, I ran across the sbspace issue and wasn't quite sure how to tackle it. Any ideas? I think it's a bit much to ask to write up a doc on the issue, but I think it would be much appreciated in the community for maybe just a list of shortfalls and how a 7.31 ER upgrade would differ from the standard ER docs on IBM. Because this kind of info, as far as I know, and as much as I've looked, is no where to be found.

Now that I have been playing with this a bit and starting to feel comfortable with what's going on, I now understand Madison's comment about check/sync. The 11.70 version is able to snoop the logical logs without a problem, but without a "sync" function, how would I get things in, well... sync? I took the advice of Paul and am running everything from the 11.70 server, including the sync, but there's some syscdr tables that don't exist like "partdef_tab": informix@test:~$ cdr syn repl -m g_socpak1 -r r1 g_soctest Error returned from checkActiveReplicate: prepare replStateQry_13 from select {+INDEX (partdef_tab pdef_ridsid)} partstate, start_time, cdrcmd(26) from syscdr:partdef_tab where servid = ? and repid = ? SQLCODE -206 ISAM -111 Error Details: 206: The specified table (partdef_tab) is not in the database. 111: ISAM error: no record found. Current Connection:syscdrConn_2, Database:syscdr@g_socpak1

That´s why IBM always recommend to create the ER structures (servers, replicates) always on the newest version, remotely on the previous... Had you tried to rebuild your ER structure using this guide??? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: luke.simmons@vgregion.se > Subject: Re: Migrating from 7.31 to 11.70 with ER? [26840] > Date: Thu, 26 Apr 2012 05:00:21 -0400 > > Now that I have been playing with this a bit and starting to feel comfortable > with what's going on, I now understand Madison's comment about check/sync. > > The 11.70 version is able to snoop the logical logs without a problem, but > without a "sync" function, how would I get things in, well... sync? > > I took the advice of Paul and am running everything from the 11.70 server, > including the sync, but there's some syscdr tables that don't exist like > "partdef_tab": > > informix@test:~$ cdr syn repl -m g_socpak1 -r r1 g_soctest > Error returned from checkActiveReplicate: prepare replStateQry_13 from select > {+INDEX (partdef_tab pdef_ridsid)} partstate, start_time, cdrcmd(26) from > syscdr:partdef_tab where servid = ? and repid = ? > SQLCODE -206 ISAM -111 > > Error Details: > > 206: The specified table (partdef_tab) is not in the database.> > 111: ISAM error: no record found.> > Current Connection:syscdrConn_2, Database:syscdr@g_socpak1 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I think I've done exactly that. The global catalog is on 11.70. The replication is being run from 11.70. I assume that's what you are referring to. Unfortunately, one can't run cdr sync... from 11.70 when replicating over a 7.31 server due to incompatible syscdr tables. Or at least that's what it looks like to me (see my post above). As a workaround, I'm considering just creating a dummy column on each table, performing an insert on that column to sync everything across. With the exception of performing a crap load of transactions, in some preliminary tests, this has worked pretty good.

Ok now I see your trouble. Maybe you´re trying to "re-invent the wheels" I think. Did you look at this article made by our friend Madison Pruet in 2006? http://www.ibm.com/developerworks/data/library/techarticle/dm-0604pruet/ I think your recipe is just there, check it out.... ;) Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: luke.simmons@vgregion.se > Subject: Re: RE: Migrating from 7.31 to 11.70 with ER? [26843] > Date: Thu, 26 Apr 2012 08:40:26 -0400 > > I think I've done exactly that. The global catalog is on 11.70. The > replication is being run from 11.70. I assume that's what you are referring > to. > > Unfortunately, one can't run cdr sync... from 11.70 when replicating over a > 7.31 server due to incompatible syscdr tables. Or at least that's what it > looks like to me (see my post above). > > As a workaround, I'm considering just creating a dummy column on each table, > performing an insert on that column to sync everything across. With the > exception of performing a crap load of transactions, in some preliminary > tests, this has worked pretty good. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

'cdr sync' and 'cdr check' only works with 10.00x and later versions only. For 7.31 server, you can scan all rows in the table , group them into smaller transactions and perform dummy update on these rows (some thing like update tab set col1=col1) to synchronize the data. Make sure to define "timestamp" conflict rule to allow converting update operation to insert operation if row is missing at target server. Also you will have to synchronize parent table first before synchronizing child table if replicated tables have foreign key constraints. I remember Madison has an esqlc program which does this. This tool does output sql statements after scanning the tables. Not sure if he still has this tool or not. I couldn't find it in IIUG member area or in my local hard disk. Thanks & Regards, Nagaraju From: "LUKE SIMMONS" <luke.simmons@vgregion.se> To: ids@iiug.org, Date: 04/26/2012 07:41 AM Subject: Re: RE: Migrating from 7.31 to 11.70 with ER? [26843] Sent by: ids-bounces@iiug.org I think I've done exactly that. The global catalog is on 11.70. The replication is being run from 11.70. I assume that's what you are referring to. Unfortunately, one can't run cdr sync... from 11.70 when replicating over a 7.31 server due to incompatible syscdr tables. Or at least that's what it looks like to me (see my post above). As a workaround, I'm considering just creating a dummy column on each table, performing an insert on that column to sync everything across. With the exception of performing a crap load of transactions, in some preliminary tests, this has worked pretty good. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.