Putting one, out of several, databases in an insta
Larry asked whether one database in a multi-database IDS 11.50 instance (Solaris 10) could be made read-only without affecting the others. The answer: there's no server-level option — the only way is to revoke INSERT/UPDATE/DELETE privileges from all users and roles (especially PUBLIC), leaving SELECT. Art Kagel suggested using myschema -g to dump the grants and a sed one-liner to turn them into a revoke script (untested). The thread then drifts into rebuilding an index without downtime: CREATE INDEX ... ONLINE avoids locking the table, but the old index must be dropped first since Informix has no CREATE OR REPLACE, so the index is unavailable during the build. A follow-up question about downsides of ONLINE index builds is left unanswered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Solaris 10 IDS 11.50.FC5 I have an instance with a few databases in it. Is it possible to put one of those databases into read only mode without affecting any of the other databases? Larry
Revoke all insert, update, and delete privileges from all tables for allusers and ROLES (especially PUBLIC) in the database. That's the only way.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> I have an instance with a few databases in it. Is it possible to put one of
> those databases into read only mode without affecting any of the other
> databases?
>
> Larry
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
That is what I was thinking, but I was hoping there might be an easier way
that I was not aware of.
Thank you.
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
<art.kagel@gmail.com>
Sent: Thursday, May 18, 2017 10:22 AM
To: ids@iiug.org
Subject: Re: Putting one, out of several, databases in .... [39243]
Revoke all insert, update, and delete privileges from all tables for allusers and ROLES (especially PUBLIC) in the database. That's the only way.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com<http://www.askdbmgt.com>;
ASK Database Management - Home<http://www.askdbmgt.com/>;
www.askdbmgt.com
Database Consulting and Support ... This is the site for Art S. Kagel's
consultancy. The soaring majesty and beauty in the image above hides the
complex ecology and ...
Blog: http://informix-myview.blogspot.com/
Informix - My view<http://informix-myview.blogspot.com/>;
informix-myview.blogspot.com
A place to share my views and ideas which may be of interest to the Informix
user Community.
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> I have an instance with a few databases in it. Is it possible to put one of
> those databases into read only mode without affecting any of the other
> databases?
>
> Larry
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You can use myschema to easily get the privileges into a script you can run
to recreate them:
myschema -d mydatabase -g privs_file.sql 1>/dev/null
And to easily create the revoke script:
sed '/INSERT|UPDATE|DELETE/s/GRANT /REVOKE /s/ TO / FROM /' privs_file.sql
>revoke_file.sql
Off the cuff, not tested.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Thu, May 18, 2017 at 11:44 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> That is what I was thinking, but I was hoping there might be an easier way
> that I was not aware of.
>
> Thank you.
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> <art.kagel@gmail.com>
> Sent: Thursday, May 18, 2017 10:22 AM
> To: ids@iiug.org
> Subject: Re: Putting one, out of several, databases in .... [39243]
>
> Revoke all insert, update, and delete privileges from all tables for all> users and ROLES (especially PUBLIC) in the database. That's the only way.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.com>;
>
> ASK Database Management - Home<http://www.askdbmgt.com/>;
> www.askdbmgt.com
> Database Consulting and Support ... This is the site for Art S. Kagel's
> consultancy. The soaring majesty and beauty in the image above hides the
> complex ecology and ...
>
> Blog: http://informix-myview.blogspot.com/
>
> Informix - My view<http://informix-myview.blogspot.com/>;
> informix-myview.blogspot.com
> A place to share my views and ideas which may be of interest to the
> Informix
> user Community.
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > I have an instance with a few databases in it. Is it possible to put one
> of
> > those databases into read only mode without affecting any of the other
> > databases?
> >
> > Larry
> >
> >
> > ************************************************************
> > *******************
> > 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.
>
>
revoke the modify (update/delete/etc) rights from all users incl. public andleave only select right.
Marcus Haarmann
Von: "LARRY SORENSEN" <LSORENSEN25@msn.com>
An: "ids" <ids@iiug.org>
Gesendet: Donnerstag, 18. Mai 2017 17:24:41
Betreff: Putting one, out of several, databases in an i.... [39240]
Solaris 10
IDS 11.50.FC5
I have an instance with a few databases in it. Is it possible to put one of
those databases into read only mode without affecting any of the other
databases?
Larry
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you.
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Marcus Haarmann
<marcus.haarmann@midoco.de>
Sent: Thursday, May 18, 2017 2:18 PM
To: ids@iiug.org
Subject: Re: Putting one, out of several, databases in .... [39246]
revoke the modify (update/delete/etc) rights from all users incl. public andleave only select right.
Marcus Haarmann
Von: "LARRY SORENSEN" <LSORENSEN25@msn.com>
An: "ids" <ids@iiug.org>
Gesendet: Donnerstag, 18. Mai 2017 17:24:41
Betreff: Putting one, out of several, databases in an i.... [39240]
Solaris 10
IDS 11.50.FC5
I have an instance with a few databases in it. Is it possible to put one of
those databases into read only mode without affecting any of the other
databases?
Larry
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you.
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
<art.kagel@gmail.com>
Sent: Thursday, May 18, 2017 10:58 AM
To: ids@iiug.org
Subject: Re: Putting one, out of several, databases in .... [39245]
You can use myschema to easily get the privileges into a script you can run
to recreate them:
myschema -d mydatabase -g privs_file.sql 1>/dev/null
And to easily create the revoke script:
sed '/INSERT|UPDATE|DELETE/s/GRANT /REVOKE /s/ TO / FROM /' privs_file.sql
>revoke_file.sql
Off the cuff, not tested.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com<http://www.askdbmgt.com>;
ASK Database Management - Home<http://www.askdbmgt.com/>;
www.askdbmgt.com
Database Consulting and Support ... This is the site for Art S. Kagel's
consultancy. The soaring majesty and beauty in the image above hides the
complex ecology and ...
Blog: http://informix-myview.blogspot.com/
Informix - My view<http://informix-myview.blogspot.com/>;
informix-myview.blogspot.com
A place to share my views and ideas which may be of interest to the Informix
user Community.
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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 Thu, May 18, 2017 at 11:44 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> That is what I was thinking, but I was hoping there might be an easier way
> that I was not aware of.
>
> Thank you.
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> <art.kagel@gmail.com>
> Sent: Thursday, May 18, 2017 10:22 AM
> To: ids@iiug.org
> Subject: Re: Putting one, out of several, databases in .... [39243]
>
> Revoke all insert, update, and delete privileges from all tables for all> users and ROLES (especially PUBLIC) in the database. That's the only way.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.com>;
>
> ASK Database Management - Home<http://www.askdbmgt.com/>;
> www.askdbmgt.com<http://www.askdbmgt.com>;
> Database Consulting and Support ... This is the site for Art S. Kagel's
> consultancy. The soaring majesty and beauty in the image above hides the
> complex ecology and ...
>
> Blog: http://informix-myview.blogspot.com/
>
> Informix - My view<http://informix-myview.blogspot.com/>;
> informix-myview.blogspot.com
> A place to share my views and ideas which may be of interest to the
> Informix
> user Community.
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > I have an instance with a few databases in it. Is it possible to put one
> of
> > those databases into read only mode without affecting any of the other
> > databases?
> >
> > Larry
> >
> >
> > ************************************************************
> > *******************
> > 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.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Solaris 10
IDS 11.50.FC5
What is the option or syntax to rebuild an index so that it builds a new index
while users are using the old index and then deletes the old index when the
new one is ready for use?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
<art.kagel@gmail.com>
Sent: Thursday, May 18, 2017 10:22 AM
To: ids@iiug.org
Subject: Re: Putting one, out of several, databases in .... [39243]
Revoke all insert, update, and delete privileges from all tables for allusers and ROLES (especially PUBLIC) in the database. That's the only way.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com<http://www.askdbmgt.com>;
ASK Database Management - Home<http://www.askdbmgt.com/>;
www.askdbmgt.com
Database Consulting and Support ... This is the site for Art S. Kagel's
consultancy. The soaring majesty and beauty in the image above hides the
complex ecology and ...
Blog: http://informix-myview.blogspot.com/
Informix - My view<http://informix-myview.blogspot.com/>;
informix-myview.blogspot.com
A place to share my views and ideas which may be of interest to the Informix
user Community.
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> I have an instance with a few databases in it. Is it possible to put one of
> those databases into read only mode without affecting any of the other
> databases?
>
> Larry
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
create index idxname on tablename( ... ) IN dbspace ONLINE;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> What is the option or syntax to rebuild an index so that it builds a new
> index
> while users are using the old index and then deletes the old index when the
> new one is ready for use?
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> <art.kagel@gmail.com>
> Sent: Thursday, May 18, 2017 10:22 AM
> To: ids@iiug.org
> Subject: Re: Putting one, out of several, databases in .... [39243]
>
> Revoke all insert, update, and delete privileges from all tables for all> users and ROLES (especially PUBLIC) in the database. That's the only way.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.com>;
>
> ASK Database Management - Home<http://www.askdbmgt.com/>;
> www.askdbmgt.com
> Database Consulting and Support ... This is the site for Art S. Kagel's
> consultancy. The soaring majesty and beauty in the image above hides the
> complex ecology and ...
>
> Blog: http://informix-myview.blogspot.com/
>
> Informix - My view<http://informix-myview.blogspot.com/>;
> informix-myview.blogspot.com
> A place to share my views and ideas which may be of interest to the
> Informix
> user Community.
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > I have an instance with a few databases in it. Is it possible to put one
> of
> > those databases into read only mode without affecting any of the other
> > databases?
> >
> > Larry
> >
> >
> > ************************************************************
> > *******************
> > 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.
>
>
Since version10 (I believe), Informix allows building index without locking
the table (ONLINE) but you can't build a new index on the same key, so you
still have to drop that existing index then rebuild it online. For that
reason, that index will NOT be available until the index build is done. Let's
go GreenThis email contains 100% recycled electrons.
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Thursday, May 18, 2017 11:49 PM
Subject: Re: Putting one, out of several, databases in .... [39250]
create index idxname on tablename( ... ) IN dbspace ONLINE;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> What is the option or syntax to rebuild an index so that it builds a new
> index
> while users are using the old index and then deletes the old index when the
> new one is ready for use?
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> <art.kagel@gmail.com>
> Sent: Thursday, May 18, 2017 10:22 AM
> To: ids@iiug.org
> Subject: Re: Putting one, out of several, databases in .... [39243]
>
> Revoke all insert, update, and delete privileges from all tables for all> users and ROLES (especially PUBLIC) in the database. That's the only way.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.com>;
>
> ASK Database Management - Home<http://www.askdbmgt.com/>;
> www.askdbmgt.com
> Database Consulting and Support ... This is the site for Art S. Kagel's
> consultancy. The soaring majesty and beauty in the image above hides the
> complex ecology and ...
>
> Blog: http://informix-myview.blogspot.com/
>
> Informix - My view<http://informix-myview.blogspot.com/>;
> informix-myview.blogspot.com
> A place to share my views and ideas which may be of interest to the
> Informix
> user Community.
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > I have an instance with a few databases in it. Is it possible to put one
> of
> > those databases into read only mode without affecting any of the other
> > databases?
> >
> > Larry
> >
> >
> > ************************************************************
> > *******************
> > 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.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Art,
this will not cover the situation, since the old index has to be dropped
before,
otherwise the server will generate an error (index for columns already exists).
The "online" keyword only does not lock the table while the index is created.
I am not sure if such a mechanism exists which keeps an old index until the
new one
is created.
Marcus
Von: "Art Kagel" <art.kagel@gmail.com>
An: "ids" <ids@iiug.org>
Gesendet: Freitag, 19. Mai 2017 05:49:05
Betreff: Re: Putting one, out of several, databases in .... [39250]
create index idxname on tablename( ... ) IN dbspace ONLINE;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> What is the option or syntax to rebuild an index so that it builds a new
> index
> while users are using the old index and then deletes the old index when the
> new one is ready for use?
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> <art.kagel@gmail.com>
> Sent: Thursday, May 18, 2017 10:22 AM
> To: ids@iiug.org
> Subject: Re: Putting one, out of several, databases in .... [39243]
>
> Revoke all insert, update, and delete privileges from all tables for all> users and ROLES (especially PUBLIC) in the database. That's the only way.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.com>;
>
> ASK Database Management - Home<http://www.askdbmgt.com/>;
> www.askdbmgt.com
> Database Consulting and Support ... This is the site for Art S. Kagel's
> consultancy. The soaring majesty and beauty in the image above hides the
> complex ecology and ...
>
> Blog: http://informix-myview.blogspot.com/
>
> Informix - My view<http://informix-myview.blogspot.com/>;
> informix-myview.blogspot.com
> A place to share my views and ideas which may be of interest to the
> Informix
> user Community.
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > I have an instance with a few databases in it. Is it possible to put one
> of
> > those databases into read only mode without affecting any of the other
> > databases?
> >
> > Larry
> >
> >
> > ************************************************************
> > *******************
> > 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.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
That is correct. Informix does not have an equivalent of the Oracle CREATE
OR REPLACE verb which would be what you would need.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, May 19, 2017 at 1:42 AM, Marcus Haarmann <marcus.haarmann@midoco.de>
wrote:
> Hi Art,
> this will not cover the situation, since the old index has to be dropped
> before,
> otherwise the server will generate an error (index for columns already
> exists).
> The "online" keyword only does not lock the table while the index is
> created.
>
> I am not sure if such a mechanism exists which keeps an old index until the
> new one
> is created.
>
> Marcus
>
> Von: "Art Kagel" <art.kagel@gmail.com>
> An: "ids" <ids@iiug.org>
> Gesendet: Freitag, 19. Mai 2017 05:49:05
> Betreff: Re: Putting one, out of several, databases in .... [39250]
>
> create index idxname on tablename( ... ) IN dbspace ONLINE;>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > What is the option or syntax to rebuild an index so that it builds a new
> > index
> > while users are using the old index and then deletes the old index when
> the
> > new one is ready for use?
> >
> > Larry
> >
> > ________________________________
> > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> > <art.kagel@gmail.com>
> > Sent: Thursday, May 18, 2017 10:22 AM
> > To: ids@iiug.org
> > Subject: Re: Putting one, out of several, databases in .... [39243]
> >
> > Revoke all insert, update, and delete privileges from all tables for all> > users and ROLES (especially PUBLIC) in the database. That's the only way.
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com<http://www.askdbmgt.com>;
> >
> > ASK Database Management - Home<http://www.askdbmgt.com/>;
> > www.askdbmgt.com
> > Database Consulting and Support ... This is the site for Art S. Kagel's
> > consultancy. The soaring majesty and beauty in the image above hides the
> > complex ecology and ...
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Informix - My view<http://informix-myview.blogspot.com/>;
> > informix-myview.blogspot.com
> > A place to share my views and ideas which may be of interest to the
> > Informix
> > user Community.
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> > wrote:
> >
> > > Solaris 10
> > >
> > > IDS 11.50.FC5
> > >
> > > I have an instance with a few databases in it. Is it possible to put
> one
> > of
> > > those databases into read only mode without affecting any of the other
> > > databases?
> > >
> > > Larry
> > >
> > >
> > > ************************************************************
> > > *******************
> > > 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.
> >
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Ok. Thank you. Is there any down side to creating an index "ONLINE"?
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
<art.kagel@gmail.com>
Sent: Friday, May 19, 2017 12:14 AM
To: ids@iiug.org
Subject: Re: Putting one, out of several, databases in .... [39253]
That is correct. Informix does not have an equivalent of the Oracle CREATE
OR REPLACE verb which would be what you would need.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com<http://www.askdbmgt.com>;
ASK Database Management - Home<http://www.askdbmgt.com/>;
www.askdbmgt.com
Database Consulting and Support ... This is the site for Art S. Kagel's
consultancy. The soaring majesty and beauty in the image above hides the
complex ecology and ...
Blog: http://informix-myview.blogspot.com/
Informix - My view<http://informix-myview.blogspot.com/>;
informix-myview.blogspot.com
A place to share my views and ideas which may be of interest to the Informix
user Community.
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, May 19, 2017 at 1:42 AM, Marcus Haarmann <marcus.haarmann@midoco.de>
wrote:
> Hi Art,
> this will not cover the situation, since the old index has to be dropped
> before,
> otherwise the server will generate an error (index for columns already
> exists).
> The "online" keyword only does not lock the table while the index is
> created.
>
> I am not sure if such a mechanism exists which keeps an old index until the
> new one
> is created.
>
> Marcus
>
> Von: "Art Kagel" <art.kagel@gmail.com>
> An: "ids" <ids@iiug.org>
> Gesendet: Freitag, 19. Mai 2017 05:49:05
> Betreff: Re: Putting one, out of several, databases in .... [39250]
>
> create index idxname on tablename( ... ) IN dbspace ONLINE;>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > What is the option or syntax to rebuild an index so that it builds a new
> > index
> > while users are using the old index and then deletes the old index when
> the
> > new one is ready for use?
> >
> > Larry
> >
> > ________________________________
> > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> > <art.kagel@gmail.com>
> > Sent: Thursday, May 18, 2017 10:22 AM
> > To: ids@iiug.org
> > Subject: Re: Putting one, out of several, databases in .... [39243]
> >
> > Revoke all insert, update, and delete privileges from all tables for all> > users and ROLES (especially PUBLIC) in the database. That's the only way.
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com<http://www.askdbmgt.com>;
> >
> > ASK Database Management - Home<http://www.askdbmgt.com/>;
> > www.askdbmgt.com<http://www.askdbmgt.com>;
> > Database Consulting and Support ... This is the site for Art S. Kagel's
> > consultancy. The soaring majesty and beauty in the image above hides the
> > complex ecology and ...
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Informix - My view<http://informix-myview.blogspot.com/>;
> > informix-myview.blogspot.com
> > A place to share my views and ideas which may be of interest to the
> > Informix
> > user Community.
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> > wrote:
> >
> > > Solaris 10
> > >
> > > IDS 11.50.FC5
> > >
> > > I have an instance with a few databases in it. Is it possible to put
> one
> > of
> > > those databases into read only mode without affecting any of the other
> > > databases?
> > >
> > > Larry
> > >
> > >
> > > ************************************************************
> > > *******************
> > > 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.
> >
> >
>
>
> ************************************************************
> *******************
> 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.
The only way I can see to do it with minimal user impact would require
building a duplicate table and creating the new index there and switching the
names around to activate it. Of course, this assumes a static table and enough
space for the operation. If the table changes, then you would have to either
keep them in sync or fix the changes after the new table is active.
--EEM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Friday, May 19, 2017 1:14 AM
To: ids@iiug.org
Subject: Re: Putting one, out of several, databases in .... [39253]
That is correct. Informix does not have an equivalent of the Oracle CREATE OR
REPLACE verb which would be what you would need.
Art
Art S. Kagel, President and Principal Consultant ASK Database Management
www.askdbmgt.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 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, May 19, 2017 at 1:42 AM, Marcus Haarmann <marcus.haarmann@midoco.de>
wrote:
> Hi Art,
> this will not cover the situation, since the old index has to be
> dropped before, otherwise the server will generate an error (index for
> columns already exists).
> The "online" keyword only does not lock the table while the index is
> created.
>
> I am not sure if such a mechanism exists which keeps an old index
> until the new one is created.
>
> Marcus
>
> Von: "Art Kagel" <art.kagel@gmail.com>
> An: "ids" <ids@iiug.org>
> Gesendet: Freitag, 19. Mai 2017 05:49:05
> Betreff: Re: Putting one, out of several, databases in .... [39250]
>
> create index idxname on tablename( ... ) IN dbspace ONLINE;>
> Art
>
> Art S. Kagel, President and Principal Consultant ASK Database
> Management www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > What is the option or syntax to rebuild an index so that it builds a
> > new index while users are using the old index and then deletes the
> > old index when
> the
> > new one is ready for use?
> >
> > Larry
> >
> > ________________________________
> > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art
> > Kagel <art.kagel@gmail.com>
> > Sent: Thursday, May 18, 2017 10:22 AM
> > To: ids@iiug.org
> > Subject: Re: Putting one, out of several, databases in .... [39243]
> >
> > Revoke all insert, update, and delete privileges from all tables for> > all users and ROLES (especially PUBLIC) in the database. That's the only
way.
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant ASK Database
> > Management www.askdbmgt.com<http://www.askdbmgt.com>;
> >
> > ASK Database Management - Home<http://www.askdbmgt.com/>;
> > www.askdbmgt.com Database Consulting and Support ... This is the
> > site for Art S. Kagel's consultancy. The soaring majesty and beauty
> > in the image above hides the complex ecology and ...
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Informix - My view<http://informix-myview.blogspot.com/>;
> > informix-myview.blogspot.com
> > A place to share my views and ideas which may be of interest to the
> > Informix user Community.
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN
> > <LSORENSEN25@msn.com>
> > wrote:
> >
> > > Solaris 10
> > >
> > > IDS 11.50.FC5
> > >
> > > I have an instance with a few databases in it. Is it possible to
> > > put
> one
> > of
> > > those databases into read only mode without affecting any of the
> > > other databases?
> > >
> > > Larry
> > >
> > >
> > > ************************************************************
> > > *******************
> > > 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.
> >
> >
>
>
> ************************************************************
> *******************
> 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.
Only that it takes a bit longer than an offline build and it has to lock
the table briefly to sync the last few changes to the table during the
build. Doesn't normally cause any problems for apps running in WAIT mode.
FYI there is an RFE (Request For Enhancement) for CREATE OR REPLACE support
to be added that you might want to vote for.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, May 19, 2017 at 8:27 AM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> Ok. Thank you. Is there any down side to creating an index "ONLINE"?
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel
> <art.kagel@gmail.com>
> Sent: Friday, May 19, 2017 12:14 AM
> To: ids@iiug.org
> Subject: Re: Putting one, out of several, databases in .... [39253]
>
> That is correct. Informix does not have an equivalent of the Oracle CREATE
> OR REPLACE verb which would be what you would need.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com<http://www.askdbmgt.com>;
> ASK Database Management - Home<http://www.askdbmgt.com/>;
> www.askdbmgt.com
> Database Consulting and Support ... This is the site for Art S. Kagel's
> consultancy. The soaring majesty and beauty in the image above hides the
> complex ecology and ...
>
> Blog: http://informix-myview.blogspot.com/
> Informix - My view<http://informix-myview.blogspot.com/>;
> informix-myview.blogspot.com
> A place to share my views and ideas which may be of interest to the
> Informix
> user Community.
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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, May 19, 2017 at 1:42 AM, Marcus Haarmann <
> marcus.haarmann@midoco.de>
> wrote:
>
> > Hi Art,
> > this will not cover the situation, since the old index has to be dropped
> > before,
> > otherwise the server will generate an error (index for columns already
> > exists).
> > The "online" keyword only does not lock the table while the index is
> > created.
> >
> > I am not sure if such a mechanism exists which keeps an old index until
> the
> > new one
> > is created.
> >
> > Marcus
> >
> > Von: "Art Kagel" <art.kagel@gmail.com>
> > An: "ids" <ids@iiug.org>
> > Gesendet: Freitag, 19. Mai 2017 05:49:05
> > Betreff: Re: Putting one, out of several, databases in .... [39250]
> >
> > create index idxname on tablename( ... ) IN dbspace ONLINE;> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com<http://www.askdbmgt.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 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 Thu, May 18, 2017 at 11:41 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
> > wrote:
> >
> > > Solaris 10
> > >
> > > IDS 11.50.FC5
> > >
> > > What is the option or syntax to rebuild an index so that it builds a
> new
> > > index
> > > while users are using the old index and then deletes the old index when
> > the
> > > new one is ready for use?
> > >
> > > Larry
> > >
> > > ________________________________
> > > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art
> Kagel
> > > <art.kagel@gmail.com>
> > > Sent: Thursday, May 18, 2017 10:22 AM
> > > To: ids@iiug.org
> > > Subject: Re: Putting one, out of several, databases in .... [39243]
> > >
> > > Revoke all insert, update, and delete privileges from all tables for> all
> > > users and ROLES (especially PUBLIC) in the database. That's the only
> way.
> > >
> > > Art
> > >
> > > Art S. Kagel, President and Principal Consultant
> > > ASK Database Management
> > > www.askdbmgt.com<http://www.askdbmgt.com>;
> > >
> > > ASK Database Management - Home<http://www.askdbmgt.com/>;
> > > www.askdbmgt.com<http://www.askdbmgt.com>;
> > > Database Consulting and Support ... This is the site for Art S. Kagel's
> > > consultancy. The soaring majesty and beauty in the image above hides
> the
> > > complex ecology and ...
> > >
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Informix - My view<http://informix-myview.blogspot.com/>;
> > > informix-myview.blogspot.com
> > > A place to share my views and ideas which may be of interest to the
> > > Informix
> > > user Community.
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and do not reflect on 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 Thu, May 18, 2017 at 10:24 AM, LARRY SORENSEN <LSORENSEN25@msn.com>
> > > wrote:
> > >
> > > > Solaris 10
> > > >
> > > > IDS 11.50.FC5
> > > >
> > > > I have an instance with a few databases in it. Is it possible to put
> > one
> > > of
> > > > those databases into read only mode without affecting any of the
> other
> > > > databases?
> > > >
> > > > Larry
> > > >
> > > >
> > > > ************************************************************
> > > > *******************
> > > > 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.
> > >
> > >
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ******