SEO

> -----Original Message-----
> From: Mark Denham [mailto:mkdenham@comcast.net]
>
> 1) An 'admin' mode for a database whereby user activity is suspended but
> the administrator still has the ability to manipulate the database;
> alternatively a means of taking an individual database offline [kicks
> off all users, stops user reconnects, allows admin access] without
> having to take down the entire server.

Totally agree.
Sometimes, it is necessary to disconnect all transactional users
for a very short time (20-30 seconds) to make necessary database
update: reload stored procedure, alter table, create index, etc.
Current workaround is to restart Informix with specific network
interface down, but it takes more then 20 seconds to restart IDS twice.

I think that it should be a mode when only 'informix' user
can connect to the database - something between 'online' and 'quiescent'

------------------------------------------
Alexey Sonkin
Senior Database Administrator
sending to informix-list


sending to informix-list

Alexey Sonkin wrote:
> Current workaround is to restart Informix with specific network
> interface down, but it takes more then 20 seconds to restart IDS
> twice.

Also, at 3am in the morning after a painful rebuild, it's easy to forget to
restore all the little tweaks and pokes you've done during the recovery, and
makes it harder to leave very simple instructions to the site's admin when
you are desperate to go home and leave them to hang around for the remaining
two hours.

Alexey Sonkin wrote:
>
> I think that it should be a mode when only 'informix' user
> can connect to the database - something between 'online' and
> 'quiescent'

yeah - either that, or a state where only DBA's can connect to a database.
Sites who grant DBA to public can pay the price :-)

"Andrew Hamm" <ahamm@mail.com> writes:
> Alexey Sonkin wrote:
>>
>> I think that it should be a mode when only 'informix' user
>> can connect to the database - something between 'online' and
>> 'quiescent'
>
> yeah - either that, or a state where only DBA's can connect to a database.
> Sites who grant DBA to public can pay the price :-)

A "single user" mode... and I vote for the "informix" user instead of
DBA.

--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

Ronald Cole wrote:
>
> A "single user" mode... and I vote for the "informix" user instead of
> DBA.

Ummmmm, I don't.

I try to convince all our teams/sites to use a specific
application-administration account so that they do not use "informix" or
(horror) "root" to administer the database. The principle is:

1) "root" administers the machine. It should not be used to configure the
engine, the application, or do ordinary user work because it puts the
machine in greater risk of accidental damage by sleepy brains.

2) "informix" administers the engine - space, configuration state. It
should not be used to configure the engine, the application, or do ordinary
user work because it puts the machine in greater risk of accidental damage
by sleepy brains.

3) the application account administers the database - eg schema, mass data
transformations, etc. It should not be used to do ordinary user work
because it puts the machine in greater risk of accidental damage by sleepy
brains.

Sure, "informix" should be on the list of legal administrators, but perhaps
we need a list of accounts that can connect whilst the engine is in the
hypothetical administration mode.

Andrew Hamm wrote:

> Ronald Cole wrote:
>
>>A "single user" mode... and I vote for the "informix" user instead of
>>DBA.
>
> Ummmmm, I don't.
>
> I try to convince all our teams/sites to use a specific
> application-administration account so that they do not use "informix" or
> (horror) "root" to administer the database. The principle is:
>
> 1) "root" administers the machine. It should not be used to configure the
> engine, the application, or do ordinary user work because it puts the
> machine in greater risk of accidental damage by sleepy brains.
>
> 2) "informix" administers the engine - space, configuration state. It
> should not be used to configure the engine, the application, or do ordinary
> user work because it puts the machine in greater risk of accidental damage
> by sleepy brains.

I think the sentences of (2) contradict each other. Presumably, you
meant "It should not be used to configure the database, the
application, ..."?

This user is the DBSA - database system administrator - a completely
separate role from the DBA or database administrator who manages a
single database within an instance.

Note that it is possible to have users other than 'informix' who are
the DBSA -- it's called role separation and is a major source of
headaches (for me). If you ignore role separation and keep user
informix as the only DBSA, it is much simpler everyone. You ignore
the DBSA role separation by ensuring that user 'informix' is the only
member of group 'informix' and by ensuring that $INFORMIXDIR/etc is
owned by group 'informix'.

> 3) the application account administers the database - eg schema, mass data
> transformations, etc. It should not be used to do ordinary user work
> because it puts the machine in greater risk of accidental damage by sleepy
> brains.
>
> Sure, "informix" should be on the list of legal administrators, but perhaps
> we need a list of accounts that can connect whilst the engine is in the
> hypothetical administration mode.

So, in this single-user mode, you want certain users - the separate
DBA users - to be able to connect to the server and administer their
respective databases? Obviously, DATABASE mine EXCLUSIVE partially
achieves this, but it isn't as effective as a server-level maintenance
mode.

Andrew has exactly the right idea - different users have different
jobs to do and the different roles should not be blindly conflated
(but often are). In particular, the distinction between DBA and DBSA
is usually overlooked, and a depressingly large number of databases
seem to use 'informix' as both the DBSA and DBA.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote:
> Andrew Hamm wrote:
>>
>> 2) "informix" administers the engine - space, configuration state.
>> It should not be used to configure the engine, the application, or
>> do ordinary user work because it puts the machine in greater risk of
>> accidental damage by sleepy brains.
>
> I think the sentences of (2) contradict each other. Presumably, you
> meant "It should not be used to configure the database, the
> application, ..."?

ahhh - yes. The dangers of cut-n-paste - and a chronically sleepy brain :-/

> This user is the DBSA - database system administrator - a completely
> separate role from the DBA or database administrator who manages a
> single database within an instance.
>
> Note that it is possible to have users other than 'informix' who are
> the DBSA -- it's called role separation and is a major source of
> headaches (for me). If you ignore role separation and keep user
> informix as the only DBSA, it is much simpler everyone. You ignore
> the DBSA role separation by ensuring that user 'informix' is the only
> member of group 'informix' and by ensuring that $INFORMIXDIR/etc is
> owned by group 'informix'.

I've seen it that people in group informix start the engine and then
mysteriously other people cannot connect. Perhaps that's platform-specific,
but I tend to scream and shout until all users except informix are removed
from the informix group :-)

> So, in this single-user mode, you want certain users - the separate
> DBA users - to be able to connect to the server and administer their
> respective databases? Obviously, DATABASE mine EXCLUSIVE partially
> achieves this, but it isn't as effective as a server-level maintenance
> mode.

Yes - that would be ideal for me, and i think a decent design. As you
mention in your wrap-up, the people using "informix" for DBA - well, i guess
they'll be happy anyway. The only sites that will be less able to use this
admin mode are those who grant DBA to unsuitable accounts, especially if
it's to "public". Which I've seen on sites where objects are owned by every
man and his dog, then views based on tables owned by multiple people don't
work unless everyone is given DBA, and general permissions are left in a
forgotten heap behind the fridge. In other words, shambolic sites. There's
not much you can do to help places that are in a mess.

"Andrew Hamm" <ahamm@mail.com> writes:
> Ronald Cole wrote:
>> A "single user" mode... and I vote for the "informix" user instead of
>> DBA.
>
> Ummmmm, I don't.

Well, it was a "wish" list request. I can't rightly reason out how
such a feature could be safely implemented. Transactions in progress
could hold resources that the DBA would need so simply suspending them
isn't enough and waiting for all current transactions to end could
leave the DBA waiting for quite a while more than he would like. And
if you're going to start aborting transactions, then you should
probably just warn your users and shutdown the engine to perform the
desired maintenance.

Perhaps the original requester is looking for an interface that allows
for easy "Son of Overlord"-style control over a running instance.

--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

Ronald Cole wrote:
> "Andrew Hamm" <ahamm@mail.com> writes:
>> Ronald Cole wrote:
>>> A "single user" mode... and I vote for the "informix" user instead
>>> of DBA.
>>
>> Ummmmm, I don't.
>
> Well, it was a "wish" list request.

don't sweat it ... i was speaking for myself, not knocking u down. Informix
is quite a reasonable choice, but there is scope for other specific users to
be nominated instead/as well.

> I can't rightly reason out how
> such a feature could be safely implemented. Transactions in progress
> could hold resources that the DBA would need so simply suspending them
> isn't enough and waiting for all current transactions to end could
> leave the DBA waiting for quite a while more than he would like. And
> if you're going to start aborting transactions, then you should
> probably just warn your users and shutdown the engine to perform the
> desired maintenance.

Currently the engine has a Graceful switch to quiescent mode, and also an
immedate switch to quiescent mode.

When graceful, all existing users are allowed to continue, but no new
sessions are accepted. In immediate mode, all existing sessions are closed,
and therefore their transactions are rolled back.

This could just as easily apply for a switch to admin mode - graceful or
immediate.

"Andrew Hamm" <ahamm@mail.com> writes:
> Currently the engine has a Graceful switch to quiescent mode, and also an
> immedate switch to quiescent mode.
> When graceful, all existing users are allowed to continue, but no new
> sessions are accepted. In immediate mode, all existing sessions are closed,
> and therefore their transactions are rolled back.
> This could just as easily apply for a switch to admin mode - graceful or
> immediate.

I understand the desire to not have to tell hundreds or thousands of
users that they have to exit and then restart their apps for some
quick administration. Still, it would probably be unsettling to
hundreds or thousands of users to suddenly have their apps all
"freeze" for a non-trivial length of time, without being duly warned
first, while the engine transitions into a temporary "admin mode" and
the admin's voice mail starts filling up.

--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B