Database maintenance
The poster needed a reliable way to get exclusive access for schema maintenance (table/procedure changes), since users keep connecting and DATABASE EXCLUSIVE only works when nobody else is attached; he wanted something portable across Linux/Solaris/Windows and IDS 11.50-12.10, drivable from SQL. Suggestions: stop the SQLI/TCP listener to block new connections (11.50.FC3+, reliable from later fixpacks), use single-user mode (onmode -j), check who is blocking a table, and vote for an RFE on this. Fernando Nunes' final answer was to invoke single-user mode via the SQL Admin API: EXECUTE FUNCTION sysadmin:task('onmode','-j'). Also referenced his blog post 'When exclusive is not really exclusive'; the poster reported the posted links were dead.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have some problems to do usual updates for maintenance on my databases, because there are always connected users or someone connects when the others are exiting. There is any procedure or something like that I can use for it? I have tried the exclusive mode but only works when there are no active connections... Informix have some feature that I can use for that or the exclusive mode have more options? Thanks for any help. SP
What sort of maintenance are you talking about? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SERGIO PERES Sent: Tuesday, March 29, 2016 2:24 PM To: ids@iiug.org Subject: Database maintenance [36864] Hi, I have some problems to do usual updates for maintenance on my databases, because there are always connected users or someone connects when the others are exiting. There is any procedure or something like that I can use for it? I have tried the exclusive mode but only works when there are no active connections... Informix have some feature that I can use for that or the exclusive mode have more options? Thanks for any help. SP **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
There are usual changes on tables or procedures.
Check this post by Fernando Nunes: http://informix-technology.blogspot.pt/2006/10/when-exclusive-is-not-really-excl usive.html?m=1 On Tue, 29 Mar 2016 21:36 SERGIO PERES, <sergio.peres@airc.pt> wrote: > There are usual changes on tables or procedures. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11401b0ea5d79d052f361829
By the way, check also this RFE: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=62567 Mine, was declined : http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=68383 On Tue, 29 Mar 2016 21:45 Ricardo Henriques, < ricardoaireshenriques@gmail.com> wrote: > Check this post by Fernando Nunes: > > > http://informix-technology.blogspot.pt/2006/10/when-exclusive-is-not-really-excl usive.html?m=1 > > On Tue, 29 Mar 2016 21:36 SERGIO PERES, <sergio.peres@airc.pt> wrote: > > > There are usual changes on tables or procedures. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11401b0ea5d79d052f361829 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113d10bae7fdf8052f364091
If people are all on tcp then just stop the listener ? Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SERGIO PERES Sent: Tuesday, March 29, 2016 3:36 PM To: ids@iiug.org Subject: Re: RE: Database maintenance [36866] There are usual changes on tables or procedures. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks, yes that can help me to avoid the new connections, but I also need to stop all the active connections also and as I have several customers with different kinds and versions of OS (Linux, Solaris and Windows) and also informix versions (11.50 to 12.10), I am looking for some process more or less standard that I can apply to all this cases.
Hello Sérgio,
It's not clear to me what is the need/request, or better saying what you're
willing to do...
You could put the engine in single user mode (onmode -j) assuming the
applications don't use the informix user, or another user you plan to use
to make the necessary schema changes.
But this would stop all the applications. Would this be acceptable? Or if
you stopthe "service" listener you coudl easily kill all the user sessions.
If you don't want anything so drastically then you can try to check who's
preventing a table change (I have a script that should be able to tell you
that) and combine that with the method I wrote about in the post Ricardo
mentioned.
And please vote on the feature Ricardo mentioned... I suppose it may be a
bit hard to implement, but it's surely one of the most needed features.
Stopping the listener was introduced in 11.50.FC3, but suffereded from some
issues until FC5 or FC6. Later 11.50 fixpacks should be ok.
Regards.
On Tue, Mar 29, 2016 at 11:38 PM, SERGIO PERES <sergio.peres@airc.pt> wrote:
> Thanks, yes that can help me to avoid the new connections, but I also need
> to
> stop all the active connections also and as I have several customers with
> different kinds and versions of OS (Linux, Solaris and Windows) and also
> informix versions (11.50 to 12.10), I am looking for some process more or
> less
> standard that I can apply to all this cases.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--94eb2c0810ac8d548e052f38b3af
Hi Ricardo, both links are dead for me, please can you confirm it?
Hi Fernando, What I'll pretend is something that I can use from my apps (in sql) to kill all attached sessions except one, the one that is processing the changes to database. SP
You can "run" onmode -j through the SQL Admin API:
EXECUTE FUNCTION sysadmin:task('onmode', '-j');
Regards.
On Thu, Mar 31, 2016 at 2:52 AM, SERGIO PERES <sergio.peres@airc.pt> wrote:
> Hi Fernando,
>
> What I'll pretend is something that I can use from my apps (in sql) to kill
> all attached sessions except one, the one that is processing the changes to
> database.
>
> SP
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--089e01294614b9b2b7052f54da96