DDL with HDR

We have an Informix 10 system with an HDR mirror, and we are having trouble with DDL statements getting "non-exclusive access" errors. We have a piece of software that is used by many organisations, and each organisation has a seperate database on the server. When we have a new version of the software you run it, and connect to a database. It checks the database structure and applies structural changes as necessary. Different organisations can be on different versions of the software. Thall worked fine before we set up HDR, and now the database upgrades are very slow and fail with "non-exclusive access" errors. One thing we noticed is that dbimport works fine (its seems to do its thing on the primary server, and then once its done, then update the secondary server). Is there any way that we can get our software to do the same thing as dbimport, to avoid these problems? Specifically isolation level, lock mode and transaction options? Thanks Stacey

I'm going to guess that the upgrade is performing quite a few create in= dex statements. If that is the case, then you need to set your lock timeou= t wait high enough to allow the index to be transfered from the primary t= o the secondary. N.B. --- In IDS11, Index Page Logging can be used to avoid this problem= . M.P. = "STACEY VERNER" = <stacey@cjntech.c = o.nz> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect DDL with HDR [9384] = 06/17/2007 04:40 = PM = = = Please respond to = ids@iiug.org = = = We have an Informix 10 system with an HDR mirror, and we are having tro= uble with DDL statements getting "non-exclusive access" errors. We have a piece of software that is used by many organisations, and eac= h organisation has a seperate database on the server. When we have a new version of the software you run it, and connect to a database. It checks the database structure and applies structural changes as necessary. Different organisations can be on different versions of the software. Thall worked fine before we set up HDR, and now the database upgrades a= re very slow and fail with "non-exclusive access" errors. One thing we noticed is that dbimport works fine (its seems to do its t= hing on the primary server, and then once its done, then update the secondary server). Is there any way that we can get our software to do the same thing as dbimport, to avoid these problems? Specifically isolation level, lock m= ode and transaction options? Thanks Stacey ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Yes. It fails on: - create index - alter table add constraint If we increase the timeout then it will probably work, but it will take a long time to run. Without HDR an upgrade takes 5-10 minutes (depending on the size of the database). With HDR it is getting about 1/3 of the way through and taking nearly an hour to get there, which would this very time consuming. DBImport does nothave this problem. It seems to delay the mirroring process until after the whole import completes. We'll be testing our software on Informix 11 fairly soon so we'll see if it has been sorted. Thanks Stacey

In IDS11, we have introduced the RSS node (Remote Standalone Secondary)= which is much like the HDR secondary, except it is totally running in a= n ASYNC mode and uses a fully duplexed TCP interface. This should speed = up the process. As anyone working with networking will tell you - you can push more dat= a through a fully-duplexed connection than a half-duplexed because of all= of the ACKs which are required before the source can send the next message= . One thing which might be possible would be to convert the HDR connectio= n into an RSS connection while the upgrade is underway and then reconvert= the RSS secondary into an HDR secondary once the upgrade is complete. To perform the conversion is a couple of onmode commands and it is doable online (takes just a few seconds.) = "STACEY VERNER" = <stacey@cjntech.c = o.nz> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: DDL with HDR [9386] = 06/17/2007 05:59 = PM = = = Please respond to = ids@iiug.org = = = Yes. It fails on: - create index - alter table add constraint If we increase the timeout then it will probably work, but it will take= a long time to run. Without HDR an upgrade takes 5-10 minutes (depending on th= e size of the database). With HDR it is getting about 1/3 of the way through a= nd taking nearly an hour to get there, which would this very time consumin= g. DBImport does nothave this problem. It seems to delay the mirroring pro= cess until after the whole import completes. We'll be testing our software on Informix 11 fairly soon so we'll see i= f it has been sorted. Thanks Stacey ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Hi, I would suggest to stop HDR (shutdown secondary server) while doing the structural changes. This should force the primary server to react without these locks. After restart of the secondary the instance will apply the changes from the logs. I remember we had these problems once and I think this was the solution we used to prevent the errors. E.G. a alter table and creation of a foreign key afterwards did fail because the secondary server has not created the table successfully. Hope this helps. Marcus -----Original Message----- From: Madison Pruet [mailto:mpruet@us.ibm.com] Sent: Monday, June 18, 2007 1:28 AM To: ids@iiug.org Subject: Re: DDL with HDR [9387] In IDS11, we have introduced the RSS node (Remote Standalone Secondary)= which is much like the HDR secondary, except it is totally running in a= n ASYNC mode and uses a fully duplexed TCP interface. This should speed = up the process. As anyone working with networking will tell you - you can push more dat= a through a fully-duplexed connection than a half-duplexed because of all= of the ACKs which are required before the source can send the next message= .. One thing which might be possible would be to convert the HDR connectio= n into an RSS connection while the upgrade is underway and then reconvert= the RSS secondary into an HDR secondary once the upgrade is complete. To perform the conversion is a couple of onmode commands and it is doable online (takes just a few seconds.) = "STACEY VERNER" = <stacey@cjntech.c = o.nz> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: DDL with HDR [9386] = 06/17/2007 05:59 = PM = = = Please respond to = ids@iiug.org = = = Yes. It fails on: - create index - alter table add constraint If we increase the timeout then it will probably work, but it will take= a long time to run. Without HDR an upgrade takes 5-10 minutes (depending on th= e size of the database). With HDR it is getting about 1/3 of the way through a= nd taking nearly an hour to get there, which would this very time consumin= g. DBImport does nothave this problem. It seems to delay the mirroring pro= cess until after the whole import completes. We'll be testing our software on Informix 11 fairly soon so we'll see i= f it has been sorted. Thanks Stacey ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. = ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

As long as the changes don't cause the log file to wrap. ;-) Seriously though -- the time that it takes to convert the HDR secondary= to an RSS node and vice versa is just about the same time that it takes t= o bring down and back up the HDR secondary. And that approch would have = the advantage that when the secondary is reconverted back to the HDR second= ary, then it would be caught up with the primary. By bouncing the HDR secondary, you would still have to spend some time getting caught back = up. M.P. = "Marcus Haarmann" = <marcus.haarmann@ = midoco.de> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect RE: Re: DDL with HDR [9388] = 06/18/2007 03:04 = AM = = = Please respond to = ids@iiug.org = = = Hi, I would suggest to stop HDR (shutdown secondary server) while doing the= structural changes. This should force the primary server to react without these locks. After restart of the secondary the instance will apply the changes from= the logs. I remember we had these problems once and I think this was the solution= we used to prevent the errors. E.G. a alter table and creation of a foreign key afterwards did fail because the secondary server has not created the table successfully. Hope this helps. Marcus -----Original Message----- From: Madison Pruet [mailto:mpruet@us.ibm.com] Sent: Monday, June 18, 2007 1:28 AM To: ids@iiug.org Subject: Re: DDL with HDR [9387] In IDS11, we have introduced the RSS node (Remote Standalone Secondary)= =3D which is much like the HDR secondary, except it is totally running in a= =3D n ASYNC mode and uses a fully duplexed TCP interface. This should speed= =3D up the process. As anyone working with networking will tell you - you can push more dat= =3D a through a fully-duplexed connection than a half-duplexed because of all=3D of the ACKs which are required before the source can send the ne= xt message=3D .. One thing which might be possible would be to convert the HDR connectio= =3D n into an RSS connection while the upgrade is underway and then reconvert=3D the RSS secondary into an HDR secondary once the upgrade i= s complete. To perform the conversion is a couple of onmode commands and it is doable online (takes just a few seconds.) =3D "STACEY VERNER" =3D <stacey@cjntech.c =3D o.nz> =3D To Sent by: ids@iiug.org =3D ids-bounces@iiug. =3D cc org =3D Subj=3D ect Re: DDL with HDR [9386] =3D 06/17/2007 05:59 =3D PM =3D =3D =3D Please respond to =3D ids@iiug.org =3D =3D =3D Yes. It fails on: - create index - alter table add constraint If we increase the timeout then it will probably work, but it will take= =3D a long time to run. Without HDR an upgrade takes 5-10 minutes (dependin= g on th=3D e size of the database). With HDR it is getting about 1/3 of t= he way through a=3D nd taking nearly an hour to get there, which would thi= s very time consumin=3D g. DBImport does nothave this problem. It seems to delay the mirroring pro= =3D cess until after the whole import completes. We'll be testing our software on Informix 11 fairly soon so we'll see i= =3D f it has been sorted. Thanks Stacey ***********************************************************************= =3D ******** Forum Note: Use "Reply" to post a response in the discussion forum. =3D ***********************************************************************= * ******* Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Sure, but this works with IDS 10. Marcus -----Original Message----- From: Madison Pruet [mailto:mpruet@us.ibm.com] Sent: Monday, June 18, 2007 2:43 PM To: ids@iiug.org Subject: RE: Re: DDL with HDR [9389] As long as the changes don't cause the log file to wrap. ;-) Seriously though -- the time that it takes to convert the HDR secondary= to an RSS node and vice versa is just about the same time that it takes t= o bring down and back up the HDR secondary. And that approch would have = the advantage that when the secondary is reconverted back to the HDR second= ary, then it would be caught up with the primary. By bouncing the HDR secondary, you would still have to spend some time getting caught back = up. M.P. = "Marcus Haarmann" = <marcus.haarmann@ = midoco.de> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect RE: Re: DDL with HDR [9388] = 06/18/2007 03:04 = AM = = = Please respond to = ids@iiug.org = = = Hi, I would suggest to stop HDR (shutdown secondary server) while doing the= structural changes. This should force the primary server to react without these locks. After restart of the secondary the instance will apply the changes from= the logs. I remember we had these problems once and I think this was the solution= we used to prevent the errors. E.G. a alter table and creation of a foreign key afterwards did fail because the secondary server has not created the table successfully. Hope this helps. Marcus -----Original Message----- From: Madison Pruet [mailto:mpruet@us.ibm.com] Sent: Monday, June 18, 2007 1:28 AM To: ids@iiug.org Subject: Re: DDL with HDR [9387] In IDS11, we have introduced the RSS node (Remote Standalone Secondary)= =3D which is much like the HDR secondary, except it is totally running in a= =3D n ASYNC mode and uses a fully duplexed TCP interface. This should speed= =3D up the process. As anyone working with networking will tell you - you can push more dat= =3D a through a fully-duplexed connection than a half-duplexed because of all=3D of the ACKs which are required before the source can send the ne= xt message=3D .. One thing which might be possible would be to convert the HDR connectio= =3D n into an RSS connection while the upgrade is underway and then reconvert=3D the RSS secondary into an HDR secondary once the upgrade i= s complete. To perform the conversion is a couple of onmode commands and it is doable online (takes just a few seconds.) =3D "STACEY VERNER" =3D <stacey@cjntech.c =3D o.nz> =3D To Sent by: ids@iiug.org =3D ids-bounces@iiug. =3D cc org =3D Subj=3D ect Re: DDL with HDR [9386] =3D 06/17/2007 05:59 =3D PM =3D =3D =3D Please respond to =3D ids@iiug.org =3D =3D =3D Yes. It fails on: - create index - alter table add constraint If we increase the timeout then it will probably work, but it will take= =3D a long time to run. Without HDR an upgrade takes 5-10 minutes (dependin= g on th=3D e size of the database). With HDR it is getting about 1/3 of t= he way through a=3D nd taking nearly an hour to get there, which would thi= s very time consumin=3D g. DBImport does nothave this problem. It seems to delay the mirroring pro= =3D cess until after the whole import completes. We'll be testing our software on Informix 11 fairly soon so we'll see i= =3D f it has been sorted. Thanks Stacey ***********************************************************************= =3D ******** Forum Note: Use "Reply" to post a response in the discussion forum. =3D ***********************************************************************= * ******* Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. = ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.