Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Lock down ASA for night processing

12 posts in General Discussion Last posting was on 2004-03-30 18:10:14.0Z
Phil. Posted on 2004-03-26 15:09:29.0Z
Sender: 3ba3.40644554.1804289383@sybase.com
From: Phil.
Newsgroups: ianywhere.public.general
Subject: Lock down ASA for night processing
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406447a5.3bdd.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 26 Mar 2004 07:09:25 -0800, 10.22.241.42
Lines: 13
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Mar 2004 07:09:27 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Mar 2004 07:09:29 -0800
X-Trace: forums-1-dub 1080313769 10.22.108.75 (26 Mar 2004 07:09:29 -0800)
X-Original-Trace: 26 Mar 2004 07:09:29 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2853
Article PK: 6322

I am using ASA 8.0.2.4285

I have a database that is being accessed by a web
application. On some daily interval I have a need to kick
all of the web users off, and prevent any new connections
for a short time period, so I can run some nightly batch
processing.

How do I automatically kill all of the current connections
and prevent new connections for this purpose?

Thanks,
Phil Lachmann


Tim McConnell Posted on 2004-03-26 16:24:16.0Z
From: Tim McConnell <tim.mcconnellNOSPAM@ianywhere.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Lock down ASA for night processing
References: <406447a5.3bdd.846930886@sybase.com>
In-Reply-To: <406447a5.3bdd.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: tmcconne-xp.sybase.com
X-Original-NNTP-Posting-Host: tmcconne-xp.sybase.com
Message-ID: <40645930$1@forums-1-dub>
Date: 26 Mar 2004 08:24:16 -0800
X-Trace: forums-1-dub 1080318256 10.25.100.215 (26 Mar 2004 08:24:16 -0800)
X-Original-Trace: 26 Mar 2004 08:24:16 -0800, tmcconne-xp.sybase.com
Lines: 56
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2854
Article PK: 6325


Phil. wrote:
> I am using ASA 8.0.2.4285
>
> I have a database that is being accessed by a web
> application. On some daily interval I have a need to kick
> all of the web users off, and prevent any new connections
> for a short time period, so I can run some nightly batch
> processing.
>
> How do I automatically kill all of the current connections
> and prevent new connections for this purpose?
>
> Thanks,
> Phil Lachmann

Hello Phil,

Such an event would contain the following (and must be executed with DBA
privileges):

// drops all users
DISCONNECT ALL;
// disables all other connections
call sa_server_option( 'Disable_connections', 'ON' );
:
// add work here
:
// allow new connections
call sa_server_option( 'Disable_connections', 'OFF' );

Information on the DISCONNECT statement and the sa_server_option system
procedure can easily be found in the online help.
Note that when DISCONNECT ALL is called, an implicit rollback is made on
all current transactions.

HTH,

--
Tim McConnell
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
<<Please only post to the newsgroup>>

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList


"Bruce Hay" Posted on 2004-03-26 16:44:03.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub>
Subject: Re: Lock down ASA for night processing
Lines: 70
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: hay-xp.sybase.com
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <40645dd3$1@forums-1-dub>
Date: 26 Mar 2004 08:44:03 -0800
X-Trace: forums-1-dub 1080319443 10.25.99.106 (26 Mar 2004 08:44:03 -0800)
X-Original-Trace: 26 Mar 2004 08:44:03 -0800, hay-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2856
Article PK: 6326

I think you'll need to use
DROP CONNECTION connection_id
in a loop to drop all connections. DISCONNECT ALL is only usable within
DBISQL and only applies to connections made by that instance of DBISQL. Use
the next_connection() function to obtain connection ids to drop; see
sa_conn_info for an example of using this function.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com> wrote in message
news:40645930$1@forums-1-dub...
> Phil. wrote:
> > I am using ASA 8.0.2.4285
> >
> > I have a database that is being accessed by a web
> > application. On some daily interval I have a need to kick
> > all of the web users off, and prevent any new connections
> > for a short time period, so I can run some nightly batch
> > processing.
> >
> > How do I automatically kill all of the current connections
> > and prevent new connections for this purpose?
> >
> > Thanks,
> > Phil Lachmann
>
> Hello Phil,
>
> Such an event would contain the following (and must be executed with DBA
> privileges):
>
> // drops all users
> DISCONNECT ALL;
> // disables all other connections
> call sa_server_option( 'Disable_connections', 'ON' );
> :
> // add work here
> :
> // allow new connections
> call sa_server_option( 'Disable_connections', 'OFF' );
>
> Information on the DISCONNECT statement and the sa_server_option system
> procedure can easily be found in the online help.
> Note that when DISCONNECT ALL is called, an implicit rollback is made on
> all current transactions.
>
> HTH,
>
> --
> Tim McConnell
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> <<Please only post to the newsgroup>>
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>


Phil. Posted on 2004-03-29 13:59:21.0Z
Sender: 6997.40682ae1.1804289383@sybase.com
From: Phil.
Newsgroups: ianywhere.public.general
Subject: Re: Lock down ASA for night processing
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40682bb5.699a.846930886@sybase.com>
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 29 Mar 2004 05:59:17 -0800, 10.22.241.42
Lines: 82
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Mar 2004 05:59:18 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Mar 2004 05:59:21 -0800
X-Trace: forums-1-dub 1080568761 10.22.108.75 (29 Mar 2004 05:59:21 -0800)
X-Original-Trace: 29 Mar 2004 05:59:21 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2868
Article PK: 6339

When looping through a list of connections, how do I
identify the current connection, so I don't disconnect the
connection I'm working in? Is there a property I can check?

Thanks,
Phil.

> I think you'll need to use
> DROP CONNECTION connection_id
> in a loop to drop all connections. DISCONNECT ALL is only
> usable within DBISQL and only applies to connections made
> by that instance of DBISQL. Use the next_connection()
> function to obtain connection ids to drop; see
> sa_conn_info for an example of using this function.
>
> Whitepapers, TechDocs, bug fixes are all available through
> the iAnywhere Developer Community at
> http://www.ianywhere.com/developer
>
> "Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com> wrote
> in message news:40645930$1@forums-1-dub...
> > Phil. wrote:
> > > I am using ASA 8.0.2.4285
> > >
> > > I have a database that is being accessed by a web
> > > application. On some daily interval I have a need to
> > > kick all of the web users off, and prevent any new
> > > connections for a short time period, so I can run some
> > > nightly batch processing.
> > >
> > > How do I automatically kill all of the current
> > > connections and prevent new connections for this
> > purpose? >
> > > Thanks,
> > > Phil Lachmann
> >
> > Hello Phil,
> >
> > Such an event would contain the following (and must be
> > executed with DBA privileges):
> >
> > // drops all users
> > DISCONNECT ALL;
> > // disables all other connections
> > call sa_server_option( 'Disable_connections', 'ON' );
> > :
> > // add work here
> > :
> > // allow new connections
> > call sa_server_option( 'Disable_connections', 'OFF' );
> >
> > Information on the DISCONNECT statement and the
> > sa_server_option system procedure can easily be found in
> > the online help. Note that when DISCONNECT ALL is called
> > , an implicit rollback is made on all current
> transactions. >
> > HTH,
> >
> > --
> > Tim McConnell
> > Certified ASA Developer Version 8
> > iAnywhere Solutions - Sybase
> > Professional Services
> > <<Please only post to the newsgroup>>
> >
> > EBFs and Maintenance Releases
> > http://downloads.sybase.com/swx/sdmain.stm
> >
> > Developer Community / Whitepapers
> > http://www.ianywhere.com/developer
> >
> > CaseXpress - to report bugs
> > http://casexpress.sybase.com
> >
> > CodeXchange - Free samples
> >
>
http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> >
>
>


"Bruce Hay" Posted on 2004-03-29 14:50:30.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com>
Subject: Re: Lock down ASA for night processing
Lines: 91
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <406837b3@forums-2-dub>
X-Original-Trace: 29 Mar 2004 06:50:27 -0800, hay-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Mar 2004 06:50:27 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Mar 2004 06:50:30 -0800
X-Trace: forums-1-dub 1080571830 10.22.108.75 (29 Mar 2004 06:50:30 -0800)
X-Original-Trace: 29 Mar 2004 06:50:30 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2870
Article PK: 6340

connection_property('Number') will return the connection id for the current
connection. That's the one you'll want to exclude.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

<Phil.> wrote in message news:40682bb5.699a.846930886@sybase.com...
> When looping through a list of connections, how do I
> identify the current connection, so I don't disconnect the
> connection I'm working in? Is there a property I can check?
>
> Thanks,
> Phil.
>
>
> > I think you'll need to use
> > DROP CONNECTION connection_id
> > in a loop to drop all connections. DISCONNECT ALL is only
> > usable within DBISQL and only applies to connections made
> > by that instance of DBISQL. Use the next_connection()
> > function to obtain connection ids to drop; see
> > sa_conn_info for an example of using this function.
> >
> > Whitepapers, TechDocs, bug fixes are all available through
> > the iAnywhere Developer Community at
> > http://www.ianywhere.com/developer
> >
> > "Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com> wrote
> > in message news:40645930$1@forums-1-dub...
> > > Phil. wrote:
> > > > I am using ASA 8.0.2.4285
> > > >
> > > > I have a database that is being accessed by a web
> > > > application. On some daily interval I have a need to
> > > > kick all of the web users off, and prevent any new
> > > > connections for a short time period, so I can run some
> > > > nightly batch processing.
> > > >
> > > > How do I automatically kill all of the current
> > > > connections and prevent new connections for this
> > > purpose? >
> > > > Thanks,
> > > > Phil Lachmann
> > >
> > > Hello Phil,
> > >
> > > Such an event would contain the following (and must be
> > > executed with DBA privileges):
> > >
> > > // drops all users
> > > DISCONNECT ALL;
> > > // disables all other connections
> > > call sa_server_option( 'Disable_connections', 'ON' );
> > > :
> > > // add work here
> > > :
> > > // allow new connections
> > > call sa_server_option( 'Disable_connections', 'OFF' );
> > >
> > > Information on the DISCONNECT statement and the
> > > sa_server_option system procedure can easily be found in
> > > the online help. Note that when DISCONNECT ALL is called
> > > , an implicit rollback is made on all current
> > transactions. >
> > > HTH,
> > >
> > > --
> > > Tim McConnell
> > > Certified ASA Developer Version 8
> > > iAnywhere Solutions - Sybase
> > > Professional Services
> > > <<Please only post to the newsgroup>>
> > >
> > > EBFs and Maintenance Releases
> > > http://downloads.sybase.com/swx/sdmain.stm
> > >
> > > Developer Community / Whitepapers
> > > http://www.ianywhere.com/developer
> > >
> > > CaseXpress - to report bugs
> > > http://casexpress.sybase.com
> > >
> > > CodeXchange - Free samples
> > >
> >
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > >
> >
> >


Phil. Posted on 2004-03-29 15:17:06.0Z
Sender: 6aa5.40683d20.1804289383@sybase.com
From: Phil.
Newsgroups: ianywhere.public.general
Subject: Re: Lock down ASA for night processing
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40683dec.6abb.846930886@sybase.com>
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com><406837b3@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 29 Mar 2004 07:17:00 -0800, 10.22.241.42
Lines: 109
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Mar 2004 07:17:05 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Mar 2004 07:17:06 -0800
X-Trace: forums-1-dub 1080573426 10.22.108.75 (29 Mar 2004 07:17:06 -0800)
X-Original-Trace: 29 Mar 2004 07:17:06 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2871
Article PK: 6345

Thanks Bruce. I think I am close to a solution. I am writing
this in a stored procedure that is looping through all of
the connections. My stored procedure does not like the DROP
CONNECTION line that I have. Any idea what is wrong with my
syntax below?

if (con_id != current_con) then
message 'dropping con_id ==',con_id;
DROP CONNECTION con_id;
end if;

Thanks,
Phil.

> connection_property('Number') will return the connection
> id for the current connection. That's the one you'll want
> to exclude.
>
> Whitepapers, TechDocs, bug fixes are all available through
> the iAnywhere Developer Community at
> http://www.ianywhere.com/developer
>
> <Phil.> wrote in message
> > news:40682bb5.699a.846930886@sybase.com... When looping
> > through a list of connections, how do I identify the
> > current connection, so I don't disconnect the connection
> I'm working in? Is there a property I can check? >
> > Thanks,
> > Phil.
> >
> >
> > > I think you'll need to use
> > > DROP CONNECTION connection_id
> > > in a loop to drop all connections. DISCONNECT ALL is
> > > only usable within DBISQL and only applies to
> > > connections made by that instance of DBISQL. Use the
> > > next_connection() function to obtain connection ids to
> > > drop; see sa_conn_info for an example of using this
> > function. >
> > > Whitepapers, TechDocs, bug fixes are all available
> > > through the iAnywhere Developer Community at
> > > http://www.ianywhere.com/developer
> > >
> > > "Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com>
> > > wrote in message news:40645930$1@forums-1-dub...
> > > > Phil. wrote:
> > > > > I am using ASA 8.0.2.4285
> > > > >
> > > > > I have a database that is being accessed by a web
> > > > > application. On some daily interval I have a need
> > > > > to kick all of the web users off, and prevent any
> > > > > new connections for a short time period, so I can
> > > > > run some nightly batch processing.
> > > > >
> > > > > How do I automatically kill all of the current
> > > > > connections and prevent new connections for this
> > > > purpose? >
> > > > > Thanks,
> > > > > Phil Lachmann
> > > >
> > > > Hello Phil,
> > > >
> > > > Such an event would contain the following (and must
> > > > be executed with DBA privileges):
> > > >
> > > > // drops all users
> > > > DISCONNECT ALL;
> > > > // disables all other connections
> > > > call sa_server_option( 'Disable_connections', 'ON' )
> > > > ; :
> > > > // add work here
> > > > :
> > > > // allow new connections
> > > > call sa_server_option( 'Disable_connections', 'OFF'
> > > ); >
> > > > Information on the DISCONNECT statement and the
> > > > sa_server_option system procedure can easily be
> > > > found in the online help. Note that when DISCONNECT
> > > > ALL is called , an implicit rollback is made on all
> > > current transactions. >
> > > > HTH,
> > > >
> > > > --
> > > > Tim McConnell
> > > > Certified ASA Developer Version 8
> > > > iAnywhere Solutions - Sybase
> > > > Professional Services
> > > > <<Please only post to the newsgroup>>
> > > >
> > > > EBFs and Maintenance Releases
> > > > http://downloads.sybase.com/swx/sdmain.stm
> > > >
> > > > Developer Community / Whitepapers
> > > > http://www.ianywhere.com/developer
> > > >
> > > > CaseXpress - to report bugs
> > > > http://casexpress.sybase.com
> > > >
> > > > CodeXchange - Free samples
> > > >
> > >
> >
>
http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > > >
> > >
> > >
>
>


Reg Domaratzki Posted on 2004-03-29 15:43:28.0Z
From: "Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com><406837b3@forums-2-dub> <40683dec.6abb.846930886@sybase.com>
Subject: Re: Lock down ASA for night processing
Lines: 130
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <40684420@forums-1-dub>
Date: 29 Mar 2004 07:43:28 -0800
X-Trace: forums-1-dub 1080575008 10.25.107.174 (29 Mar 2004 07:43:28 -0800)
X-Original-Trace: 29 Mar 2004 07:43:28 -0800, rdomarat-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2874
Article PK: 6343

You may not be able to use a variable for connection ID. Try

if (con_id != current_con) then
message 'dropping con_id ==',con_id;
execute immediate 'DROP CONNECTION ' || con_id;
end if;


--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Phil.> wrote in message news:40683dec.6abb.846930886@sybase.com...
> Thanks Bruce. I think I am close to a solution. I am writing
> this in a stored procedure that is looping through all of
> the connections. My stored procedure does not like the DROP
> CONNECTION line that I have. Any idea what is wrong with my
> syntax below?
>
> if (con_id != current_con) then
> message 'dropping con_id ==',con_id;
> DROP CONNECTION con_id;
> end if;
>
> Thanks,
> Phil.
>
> > connection_property('Number') will return the connection
> > id for the current connection. That's the one you'll want
> > to exclude.
> >
> > Whitepapers, TechDocs, bug fixes are all available through
> > the iAnywhere Developer Community at
> > http://www.ianywhere.com/developer
> >
> > <Phil.> wrote in message
> > > news:40682bb5.699a.846930886@sybase.com... When looping
> > > through a list of connections, how do I identify the
> > > current connection, so I don't disconnect the connection
> > I'm working in? Is there a property I can check? >
> > > Thanks,
> > > Phil.
> > >
> > >
> > > > I think you'll need to use
> > > > DROP CONNECTION connection_id
> > > > in a loop to drop all connections. DISCONNECT ALL is
> > > > only usable within DBISQL and only applies to
> > > > connections made by that instance of DBISQL. Use the
> > > > next_connection() function to obtain connection ids to
> > > > drop; see sa_conn_info for an example of using this
> > > function. >
> > > > Whitepapers, TechDocs, bug fixes are all available
> > > > through the iAnywhere Developer Community at
> > > > http://www.ianywhere.com/developer
> > > >
> > > > "Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com>
> > > > wrote in message news:40645930$1@forums-1-dub...
> > > > > Phil. wrote:
> > > > > > I am using ASA 8.0.2.4285
> > > > > >
> > > > > > I have a database that is being accessed by a web
> > > > > > application. On some daily interval I have a need
> > > > > > to kick all of the web users off, and prevent any
> > > > > > new connections for a short time period, so I can
> > > > > > run some nightly batch processing.
> > > > > >
> > > > > > How do I automatically kill all of the current
> > > > > > connections and prevent new connections for this
> > > > > purpose? >
> > > > > > Thanks,
> > > > > > Phil Lachmann
> > > > >
> > > > > Hello Phil,
> > > > >
> > > > > Such an event would contain the following (and must
> > > > > be executed with DBA privileges):
> > > > >
> > > > > // drops all users
> > > > > DISCONNECT ALL;
> > > > > // disables all other connections
> > > > > call sa_server_option( 'Disable_connections', 'ON' )
> > > > > ; :
> > > > > // add work here
> > > > > :
> > > > > // allow new connections
> > > > > call sa_server_option( 'Disable_connections', 'OFF'
> > > > ); >
> > > > > Information on the DISCONNECT statement and the
> > > > > sa_server_option system procedure can easily be
> > > > > found in the online help. Note that when DISCONNECT
> > > > > ALL is called , an implicit rollback is made on all
> > > > current transactions. >
> > > > > HTH,
> > > > >
> > > > > --
> > > > > Tim McConnell
> > > > > Certified ASA Developer Version 8
> > > > > iAnywhere Solutions - Sybase
> > > > > Professional Services
> > > > > <<Please only post to the newsgroup>>
> > > > >
> > > > > EBFs and Maintenance Releases
> > > > > http://downloads.sybase.com/swx/sdmain.stm
> > > > >
> > > > > Developer Community / Whitepapers
> > > > > http://www.ianywhere.com/developer
> > > > >
> > > > > CaseXpress - to report bugs
> > > > > http://casexpress.sybase.com
> > > > >
> > > > > CodeXchange - Free samples
> > > > >
> > > >
> > >
> >
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > > > >
> > > >
> > > >
> >
> >


"Bruce Hay" Posted on 2004-03-29 15:41:19.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com><406837b3@forums-2-dub> <40683dec.6abb.846930886@sybase.com>
Subject: Re: Lock down ASA for night processing
Lines: 119
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <4068439b@forums-2-dub>
X-Original-Trace: 29 Mar 2004 07:41:15 -0800, hay-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Mar 2004 07:41:16 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Mar 2004 07:41:19 -0800
X-Trace: forums-1-dub 1080574879 10.22.108.75 (29 Mar 2004 07:41:19 -0800)
X-Original-Trace: 29 Mar 2004 07:41:19 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2873
Article PK: 6347

Change to:
if (con_id != current_con) then
message 'dropping con_id ==',con_id;
execute immediate 'DROP CONNECTION ' || con_id;
end if;

<Phil.> wrote in message news:40683dec.6abb.846930886@sybase.com...
> Thanks Bruce. I think I am close to a solution. I am writing
> this in a stored procedure that is looping through all of
> the connections. My stored procedure does not like the DROP
> CONNECTION line that I have. Any idea what is wrong with my
> syntax below?
>
> if (con_id != current_con) then
> message 'dropping con_id ==',con_id;
> DROP CONNECTION con_id;
> end if;
>
> Thanks,
> Phil.
>
> > connection_property('Number') will return the connection
> > id for the current connection. That's the one you'll want
> > to exclude.
> >
> > Whitepapers, TechDocs, bug fixes are all available through
> > the iAnywhere Developer Community at
> > http://www.ianywhere.com/developer
> >
> > <Phil.> wrote in message
> > > news:40682bb5.699a.846930886@sybase.com... When looping
> > > through a list of connections, how do I identify the
> > > current connection, so I don't disconnect the connection
> > I'm working in? Is there a property I can check? >
> > > Thanks,
> > > Phil.
> > >
> > >
> > > > I think you'll need to use
> > > > DROP CONNECTION connection_id
> > > > in a loop to drop all connections. DISCONNECT ALL is
> > > > only usable within DBISQL and only applies to
> > > > connections made by that instance of DBISQL. Use the
> > > > next_connection() function to obtain connection ids to
> > > > drop; see sa_conn_info for an example of using this
> > > function. >
> > > > Whitepapers, TechDocs, bug fixes are all available
> > > > through the iAnywhere Developer Community at
> > > > http://www.ianywhere.com/developer
> > > >
> > > > "Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com>
> > > > wrote in message news:40645930$1@forums-1-dub...
> > > > > Phil. wrote:
> > > > > > I am using ASA 8.0.2.4285
> > > > > >
> > > > > > I have a database that is being accessed by a web
> > > > > > application. On some daily interval I have a need
> > > > > > to kick all of the web users off, and prevent any
> > > > > > new connections for a short time period, so I can
> > > > > > run some nightly batch processing.
> > > > > >
> > > > > > How do I automatically kill all of the current
> > > > > > connections and prevent new connections for this
> > > > > purpose? >
> > > > > > Thanks,
> > > > > > Phil Lachmann
> > > > >
> > > > > Hello Phil,
> > > > >
> > > > > Such an event would contain the following (and must
> > > > > be executed with DBA privileges):
> > > > >
> > > > > // drops all users
> > > > > DISCONNECT ALL;
> > > > > // disables all other connections
> > > > > call sa_server_option( 'Disable_connections', 'ON' )
> > > > > ; :
> > > > > // add work here
> > > > > :
> > > > > // allow new connections
> > > > > call sa_server_option( 'Disable_connections', 'OFF'
> > > > ); >
> > > > > Information on the DISCONNECT statement and the
> > > > > sa_server_option system procedure can easily be
> > > > > found in the online help. Note that when DISCONNECT
> > > > > ALL is called , an implicit rollback is made on all
> > > > current transactions. >
> > > > > HTH,
> > > > >
> > > > > --
> > > > > Tim McConnell
> > > > > Certified ASA Developer Version 8
> > > > > iAnywhere Solutions - Sybase
> > > > > Professional Services
> > > > > <<Please only post to the newsgroup>>
> > > > >
> > > > > EBFs and Maintenance Releases
> > > > > http://downloads.sybase.com/swx/sdmain.stm
> > > > >
> > > > > Developer Community / Whitepapers
> > > > > http://www.ianywhere.com/developer
> > > > >
> > > > > CaseXpress - to report bugs
> > > > > http://casexpress.sybase.com
> > > > >
> > > > > CodeXchange - Free samples
> > > > >
> > > >
> > >
> >
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > > > >
> > > >
> > > >
> >
> >


"Bruce Hay" Posted on 2004-03-29 16:46:39.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com><406837b3@forums-2-dub> <40683dec.6abb.846930886@sybase.com>
Subject: Re: Lock down ASA for night processing
Lines: 168
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: hay-xp.sybase.com
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <406852ef$1@forums-1-dub>
Date: 29 Mar 2004 08:46:39 -0800
X-Trace: forums-1-dub 1080578799 10.25.99.106 (29 Mar 2004 08:46:39 -0800)
X-Original-Trace: 29 Mar 2004 08:46:39 -0800, hay-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2878
Article PK: 6348

Now that you've done all the work to create this procedure :-(, here is an
equivalent one posted by Breck Carter Nov 18,2002 (with minor changes). I
will suggest that this be added to the iAnywhere CodeXchange site for future
use.

-- How to get full control over an ASA server...
-- call sa_server_option ( 'disable_connections', 'ON' );
-- call sa_drop_other_connections();
-- ...do your thing
-- call sa_server_option ( 'disable_connections', 'OFF' )

-- Attempts to connect will get "ASA Error -99:
-- Connections to database have been disabled".

call sa_make_object( 'procedure', 'sa_drop_other_connections', 'dbo' );

ALTER PROCEDURE dbo.sa_drop_other_connections ()

BEGIN

DECLARE @this_connection_id INTEGER;
DECLARE @other_connection_id INTEGER;
DECLARE @conn_ident VARCHAR(200);

SET @this_connection_id = connection_property ( 'number' );
SET @other_connection_id = NEXT_CONNECTION ( NULL );

WHILE @other_connection_id IS NOT NULL LOOP

SET @conn_ident = STRING(
connection_property ( 'Userid', @other_connection_id ), '-',
IF COALESCE ( connection_property ( 'NodeAddress',
@other_connection_id ), '' ) <> ''
THEN STRING ( COALESCE ( connection_property ( 'NodeAddress',
@other_connection_id ), '' ), ' - ' )
ELSE ''
ENDIF,
@other_connection_id );

IF @other_connection_id = @this_connection_id THEN
MESSAGE 'Not dropping this connection: ' || @conn_ident TO CLIENT;
ELSE
MESSAGE 'Dropping other connection: ' || @conn_ident TO CLIENT;
EXECUTE IMMEDIATE 'DROP CONNECTION ' || @other_connection_id;
END IF;

SET @other_connection_id = NEXT_CONNECTION ( @other_connection_id );

END LOOP;

END;


Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

<Phil.> wrote in message news:40683dec.6abb.846930886@sybase.com...
> Thanks Bruce. I think I am close to a solution. I am writing
> this in a stored procedure that is looping through all of
> the connections. My stored procedure does not like the DROP
> CONNECTION line that I have. Any idea what is wrong with my
> syntax below?
>
> if (con_id != current_con) then
> message 'dropping con_id ==',con_id;
> DROP CONNECTION con_id;
> end if;
>
> Thanks,
> Phil.
>
> > connection_property('Number') will return the connection
> > id for the current connection. That's the one you'll want
> > to exclude.
> >
> > Whitepapers, TechDocs, bug fixes are all available through
> > the iAnywhere Developer Community at
> > http://www.ianywhere.com/developer
> >
> > <Phil.> wrote in message
> > > news:40682bb5.699a.846930886@sybase.com... When looping
> > > through a list of connections, how do I identify the
> > > current connection, so I don't disconnect the connection
> > I'm working in? Is there a property I can check? >
> > > Thanks,
> > > Phil.
> > >
> > >
> > > > I think you'll need to use
> > > > DROP CONNECTION connection_id
> > > > in a loop to drop all connections. DISCONNECT ALL is
> > > > only usable within DBISQL and only applies to
> > > > connections made by that instance of DBISQL. Use the
> > > > next_connection() function to obtain connection ids to
> > > > drop; see sa_conn_info for an example of using this
> > > function. >
> > > > Whitepapers, TechDocs, bug fixes are all available
> > > > through the iAnywhere Developer Community at
> > > > http://www.ianywhere.com/developer
> > > >
> > > > "Tim McConnell" <tim.mcconnellNOSPAM@ianywhere.com>
> > > > wrote in message news:40645930$1@forums-1-dub...
> > > > > Phil. wrote:
> > > > > > I am using ASA 8.0.2.4285
> > > > > >
> > > > > > I have a database that is being accessed by a web
> > > > > > application. On some daily interval I have a need
> > > > > > to kick all of the web users off, and prevent any
> > > > > > new connections for a short time period, so I can
> > > > > > run some nightly batch processing.
> > > > > >
> > > > > > How do I automatically kill all of the current
> > > > > > connections and prevent new connections for this
> > > > > purpose? >
> > > > > > Thanks,
> > > > > > Phil Lachmann
> > > > >
> > > > > Hello Phil,
> > > > >
> > > > > Such an event would contain the following (and must
> > > > > be executed with DBA privileges):
> > > > >
> > > > > // drops all users
> > > > > DISCONNECT ALL;
> > > > > // disables all other connections
> > > > > call sa_server_option( 'Disable_connections', 'ON' )
> > > > > ; :
> > > > > // add work here
> > > > > :
> > > > > // allow new connections
> > > > > call sa_server_option( 'Disable_connections', 'OFF'
> > > > ); >
> > > > > Information on the DISCONNECT statement and the
> > > > > sa_server_option system procedure can easily be
> > > > > found in the online help. Note that when DISCONNECT
> > > > > ALL is called , an implicit rollback is made on all
> > > > current transactions. >
> > > > > HTH,
> > > > >
> > > > > --
> > > > > Tim McConnell
> > > > > Certified ASA Developer Version 8
> > > > > iAnywhere Solutions - Sybase
> > > > > Professional Services
> > > > > <<Please only post to the newsgroup>>
> > > > >
> > > > > EBFs and Maintenance Releases
> > > > > http://downloads.sybase.com/swx/sdmain.stm
> > > > >
> > > > > Developer Community / Whitepapers
> > > > > http://www.ianywhere.com/developer
> > > > >
> > > > > CaseXpress - to report bugs
> > > > > http://casexpress.sybase.com
> > > > >
> > > > > CodeXchange - Free samples
> > > > >
> > > >
> > >
> >
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > > > >
> > > >
> > > >
> >
> >


Phil. Posted on 2004-03-30 17:50:35.0Z
Sender: 30ed.4069b262.1804289383@sybase.com
From: Phil.
Newsgroups: ianywhere.public.general
Subject: Re: Lock down ASA for night processing
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4069b36b.30fd.846930886@sybase.com>
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com><406837b3@forums-2-dub> <40683dec.6abb.846930886@sybase.com><406852ef$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Mar 2004 09:50:35 -0800
X-Trace: forums-1-dub 1080669035 10.22.241.41 (30 Mar 2004 09:50:35 -0800)
X-Original-Trace: 30 Mar 2004 09:50:35 -0800, 10.22.241.41
Lines: 196
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2892
Article PK: 6360

Now you tell me! :)

One more question. If I set disable_connections to ON and
something bad happens in the middle of processing that
causes an abnormal termination, and disable_connections is
never set to OFF, what kind of problems would that cause?

I would obviously not want to create a situation where I
cannot reconnect to my database.

Any advice, watchouts, etc.?

Thanks,
Phil.

> Now that you've done all the work to create this procedure
> :-(, here is an equivalent one posted by Breck Carter Nov
> 18,2002 (with minor changes). I will suggest that this be
> added to the iAnywhere CodeXchange site for future use.
>
> -- How to get full control over an ASA server...
> -- call sa_server_option ( 'disable_connections', 'ON'
> ); -- call sa_drop_other_connections();
> -- ...do your thing
> -- call sa_server_option ( 'disable_connections', 'OFF'
> )
>
> -- Attempts to connect will get "ASA Error -99:
> -- Connections to database have been disabled".
>
> call sa_make_object( 'procedure',
> 'sa_drop_other_connections', 'dbo' );
>
> ALTER PROCEDURE dbo.sa_drop_other_connections ()
>
> BEGIN
>
> DECLARE @this_connection_id INTEGER;
> DECLARE @other_connection_id INTEGER;
> DECLARE @conn_ident VARCHAR(200);
>
> SET @this_connection_id = connection_property ( 'number'
> ); SET @other_connection_id = NEXT_CONNECTION ( NULL );
>
> WHILE @other_connection_id IS NOT NULL LOOP
>
> SET @conn_ident = STRING(
> connection_property ( 'Userid',
> @other_connection_id ), '-',
> IF COALESCE ( connection_property ( 'NodeAddress'
> , @other_connection_id ), '' ) <> ''
> THEN STRING ( COALESCE ( connection_property (
> 'NodeAddress', @other_connection_id ), '' ), ' - ' )
> ELSE ''
> ENDIF,
> @other_connection_id );
>
> IF @other_connection_id = @this_connection_id THEN
> MESSAGE 'Not dropping this connection: ' ||
> @conn_ident TO CLIENT;
> ELSE
> MESSAGE 'Dropping other connection: ' || @conn_ident
> TO CLIENT;
> EXECUTE IMMEDIATE 'DROP CONNECTION ' ||
> @other_connection_id;
> END IF;
>
> SET @other_connection_id = NEXT_CONNECTION (
> @other_connection_id );
>
> END LOOP;
>
> END;
>
>
> Whitepapers, TechDocs, bug fixes are all available through
> the iAnywhere Developer Community at
> http://www.ianywhere.com/developer
>
> <Phil.> wrote in message
> > news:40683dec.6abb.846930886@sybase.com... Thanks Bruce.
> > I think I am close to a solution. I am writing this in a
> > stored procedure that is looping through all of the
> > connections. My stored procedure does not like the DROP
> CONNECTION line that I have. Any idea what is wrong with
> > my syntax below?
> >
> > if (con_id != current_con) then
> > message 'dropping con_id ==',con_id;
> > DROP CONNECTION con_id;
> > end if;
> >
> > Thanks,
> > Phil.
> >
> > > connection_property('Number') will return the
> > > connection id for the current connection. That's the
> > > one you'll want to exclude.
> > >
> > > Whitepapers, TechDocs, bug fixes are all available
> > > through the iAnywhere Developer Community at
> > > http://www.ianywhere.com/developer
> > >
> > > <Phil.> wrote in message
> > > > news:40682bb5.699a.846930886@sybase.com... When
> > > > looping through a list of connections, how do I
> > > > identify the current connection, so I don't
> > > disconnect the connection I'm working in? Is there a
> > > > property I can check? > Thanks,
> > > > Phil.
> > > >
> > > >
> > > > > I think you'll need to use
> > > > > DROP CONNECTION connection_id
> > > > > in a loop to drop all connections. DISCONNECT ALL
> > > > > is only usable within DBISQL and only applies to
> > > > > connections made by that instance of DBISQL. Use
> > > > > the next_connection() function to obtain
> > > > > connection ids to drop; see sa_conn_info for an
> > > > example of using this function. >
> > > > > Whitepapers, TechDocs, bug fixes are all available
> > > > > through the iAnywhere Developer Community at
> > > > > http://www.ianywhere.com/developer
> > > > >
> > > > > "Tim McConnell"
> > > > > <tim.mcconnellNOSPAM@ianywhere.com> wrote in
> > > > > > message news:40645930$1@forums-1-dub... Phil.
> > > > > > > wrote: I am using ASA 8.0.2.4285
> > > > > > >
> > > > > > > I have a database that is being accessed by a
> > > > > > > web application. On some daily interval I have
> > > > > > > a need to kick all of the web users off, and
> > > > > > > prevent any new connections for a short time
> > > > > > > period, so I can run some nightly batch
> > > > > > processing. >
> > > > > > > How do I automatically kill all of the current
> > > > > > > connections and prevent new connections for
> > > > > > this purpose? >
> > > > > > > Thanks,
> > > > > > > Phil Lachmann
> > > > > >
> > > > > > Hello Phil,
> > > > > >
> > > > > > Such an event would contain the following (and
> > > > > > must be executed with DBA privileges):
> > > > > >
> > > > > > // drops all users
> > > > > > DISCONNECT ALL;
> > > > > > // disables all other connections
> > > > > > call sa_server_option( 'Disable_connections',
> > > > > > 'ON' ) ; :
> > > > > > // add work here
> > > > > > :
> > > > > > // allow new connections
> > > > > > call sa_server_option( 'Disable_connections',
> > > > > 'OFF' ); >
> > > > > > Information on the DISCONNECT statement and the
> > > > > > sa_server_option system procedure can easily be
> > > > > > found in the online help. Note that when
> > > > > > DISCONNECT ALL is called , an implicit rollback
> > > > > is made on all current transactions. >
> > > > > > HTH,
> > > > > >
> > > > > > --
> > > > > > Tim McConnell
> > > > > > Certified ASA Developer Version 8
> > > > > > iAnywhere Solutions - Sybase
> > > > > > Professional Services
> > > > > > <<Please only post to the newsgroup>>
> > > > > >
> > > > > > EBFs and Maintenance Releases
> > > > > > http://downloads.sybase.com/swx/sdmain.stm
> > > > > >
> > > > > > Developer Community / Whitepapers
> > > > > > http://www.ianywhere.com/developer
> > > > > >
> > > > > > CaseXpress - to report bugs
> > > > > > http://casexpress.sybase.com
> > > > > >
> > > > > > CodeXchange - Free samples
> > > > > >
> > > > >
> > > >
> > >
> >
>
http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > > > > >
> > > > >
> > > > >
> > >
> > >
>
>


Chris Keating (iAnywhere Solutions) Posted on 2004-03-30 18:10:14.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com> <40645930$1@forums-1-dub><40645dd3$1@forums-1-dub> <40682bb5.699a.846930886@sybase.com><406837b3@forums-2-dub> <40683dec.6abb.846930886@sybase.com><406852ef$1@forums-1-dub> <4069b36b.30fd.846930886@sybase.com>
Subject: Re: Lock down ASA for night processing
Lines: 228
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: keating-xp.sybase.com
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <4069b806@forums-1-dub>
Date: 30 Mar 2004 10:10:14 -0800
X-Trace: forums-1-dub 1080670214 10.25.100.248 (30 Mar 2004 10:10:14 -0800)
X-Original-Trace: 30 Mar 2004 10:10:14 -0800, keating-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2893
Article PK: 6363

Once the database is restarted, connections will be allowed.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

<Phil.> wrote in message news:4069b36b.30fd.846930886@sybase.com...
> Now you tell me! :)
>
> One more question. If I set disable_connections to ON and
> something bad happens in the middle of processing that
> causes an abnormal termination, and disable_connections is
> never set to OFF, what kind of problems would that cause?
>
> I would obviously not want to create a situation where I
> cannot reconnect to my database.
>
> Any advice, watchouts, etc.?
>
> Thanks,
> Phil.
>
>
>
> > Now that you've done all the work to create this procedure
> > :-(, here is an equivalent one posted by Breck Carter Nov
> > 18,2002 (with minor changes). I will suggest that this be
> > added to the iAnywhere CodeXchange site for future use.
> >
> > -- How to get full control over an ASA server...
> > -- call sa_server_option ( 'disable_connections', 'ON'
> > ); -- call sa_drop_other_connections();
> > -- ...do your thing
> > -- call sa_server_option ( 'disable_connections', 'OFF'
> > )
> >
> > -- Attempts to connect will get "ASA Error -99:
> > -- Connections to database have been disabled".
> >
> > call sa_make_object( 'procedure',
> > 'sa_drop_other_connections', 'dbo' );
> >
> > ALTER PROCEDURE dbo.sa_drop_other_connections ()
> >
> > BEGIN
> >
> > DECLARE @this_connection_id INTEGER;
> > DECLARE @other_connection_id INTEGER;
> > DECLARE @conn_ident VARCHAR(200);
> >
> > SET @this_connection_id = connection_property ( 'number'
> > ); SET @other_connection_id = NEXT_CONNECTION ( NULL );
> >
> > WHILE @other_connection_id IS NOT NULL LOOP
> >
> > SET @conn_ident = STRING(
> > connection_property ( 'Userid',
> > @other_connection_id ), '-',
> > IF COALESCE ( connection_property ( 'NodeAddress'
> > , @other_connection_id ), '' ) <> ''
> > THEN STRING ( COALESCE ( connection_property (
> > 'NodeAddress', @other_connection_id ), '' ), ' - ' )
> > ELSE ''
> > ENDIF,
> > @other_connection_id );
> >
> > IF @other_connection_id = @this_connection_id THEN
> > MESSAGE 'Not dropping this connection: ' ||
> > @conn_ident TO CLIENT;
> > ELSE
> > MESSAGE 'Dropping other connection: ' || @conn_ident
> > TO CLIENT;
> > EXECUTE IMMEDIATE 'DROP CONNECTION ' ||
> > @other_connection_id;
> > END IF;
> >
> > SET @other_connection_id = NEXT_CONNECTION (
> > @other_connection_id );
> >
> > END LOOP;
> >
> > END;
> >
> >
> > Whitepapers, TechDocs, bug fixes are all available through
> > the iAnywhere Developer Community at
> > http://www.ianywhere.com/developer
> >
> > <Phil.> wrote in message
> > > news:40683dec.6abb.846930886@sybase.com... Thanks Bruce.
> > > I think I am close to a solution. I am writing this in a
> > > stored procedure that is looping through all of the
> > > connections. My stored procedure does not like the DROP
> > CONNECTION line that I have. Any idea what is wrong with
> > > my syntax below?
> > >
> > > if (con_id != current_con) then
> > > message 'dropping con_id ==',con_id;
> > > DROP CONNECTION con_id;
> > > end if;
> > >
> > > Thanks,
> > > Phil.
> > >
> > > > connection_property('Number') will return the
> > > > connection id for the current connection. That's the
> > > > one you'll want to exclude.
> > > >
> > > > Whitepapers, TechDocs, bug fixes are all available
> > > > through the iAnywhere Developer Community at
> > > > http://www.ianywhere.com/developer
> > > >
> > > > <Phil.> wrote in message
> > > > > news:40682bb5.699a.846930886@sybase.com... When
> > > > > looping through a list of connections, how do I
> > > > > identify the current connection, so I don't
> > > > disconnect the connection I'm working in? Is there a
> > > > > property I can check? > Thanks,
> > > > > Phil.
> > > > >
> > > > >
> > > > > > I think you'll need to use
> > > > > > DROP CONNECTION connection_id
> > > > > > in a loop to drop all connections. DISCONNECT ALL
> > > > > > is only usable within DBISQL and only applies to
> > > > > > connections made by that instance of DBISQL. Use
> > > > > > the next_connection() function to obtain
> > > > > > connection ids to drop; see sa_conn_info for an
> > > > > example of using this function. >
> > > > > > Whitepapers, TechDocs, bug fixes are all available
> > > > > > through the iAnywhere Developer Community at
> > > > > > http://www.ianywhere.com/developer
> > > > > >
> > > > > > "Tim McConnell"
> > > > > > <tim.mcconnellNOSPAM@ianywhere.com> wrote in
> > > > > > > message news:40645930$1@forums-1-dub... Phil.
> > > > > > > > wrote: I am using ASA 8.0.2.4285
> > > > > > > >
> > > > > > > > I have a database that is being accessed by a
> > > > > > > > web application. On some daily interval I have
> > > > > > > > a need to kick all of the web users off, and
> > > > > > > > prevent any new connections for a short time
> > > > > > > > period, so I can run some nightly batch
> > > > > > > processing. >
> > > > > > > > How do I automatically kill all of the current
> > > > > > > > connections and prevent new connections for
> > > > > > > this purpose? >
> > > > > > > > Thanks,
> > > > > > > > Phil Lachmann
> > > > > > >
> > > > > > > Hello Phil,
> > > > > > >
> > > > > > > Such an event would contain the following (and
> > > > > > > must be executed with DBA privileges):
> > > > > > >
> > > > > > > // drops all users
> > > > > > > DISCONNECT ALL;
> > > > > > > // disables all other connections
> > > > > > > call sa_server_option( 'Disable_connections',
> > > > > > > 'ON' ) ; :
> > > > > > > // add work here
> > > > > > > :
> > > > > > > // allow new connections
> > > > > > > call sa_server_option( 'Disable_connections',
> > > > > > 'OFF' ); >
> > > > > > > Information on the DISCONNECT statement and the
> > > > > > > sa_server_option system procedure can easily be
> > > > > > > found in the online help. Note that when
> > > > > > > DISCONNECT ALL is called , an implicit rollback
> > > > > > is made on all current transactions. >
> > > > > > > HTH,
> > > > > > >
> > > > > > > --
> > > > > > > Tim McConnell
> > > > > > > Certified ASA Developer Version 8
> > > > > > > iAnywhere Solutions - Sybase
> > > > > > > Professional Services
> > > > > > > <<Please only post to the newsgroup>>
> > > > > > >
> > > > > > > EBFs and Maintenance Releases
> > > > > > > http://downloads.sybase.com/swx/sdmain.stm
> > > > > > >
> > > > > > > Developer Community / Whitepapers
> > > > > > > http://www.ianywhere.com/developer
> > > > > > >
> > > > > > > CaseXpress - to report bugs
> > > > > > > http://casexpress.sybase.com
> > > > > > >
> > > > > > > CodeXchange - Free samples
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
> > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >


Chris Keating (iAnywhere Solutions) Posted on 2004-03-26 16:42:00.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <406447a5.3bdd.846930886@sybase.com>
Subject: Re: Lock down ASA for night processing
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: vpn-concord-044.sybase.com
Message-ID: <40645d55$1@forums-2-dub>
X-Original-Trace: 26 Mar 2004 08:41:57 -0800, vpn-concord-044.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Mar 2004 08:41:58 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Mar 2004 08:42:00 -0800
X-Trace: forums-1-dub 1080319320 10.22.108.75 (26 Mar 2004 08:42:00 -0800)
X-Original-Trace: 26 Mar 2004 08:42:00 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2855
Article PK: 6327

To disable connections, execute sa_server_option ( 'Disable_connections' ,
'ON'). TO enable connections, use the same command with the value 'OFF'.

Use DISCONNECT ... -or- DROP CONNECTION to drop existing connections.


--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

<Phil.> wrote in message news:406447a5.3bdd.846930886@sybase.com...
> I am using ASA 8.0.2.4285
>
> I have a database that is being accessed by a web
> application. On some daily interval I have a need to kick
> all of the web users off, and prevent any new connections
> for a short time period, so I can run some nightly batch
> processing.
>
> How do I automatically kill all of the current connections
> and prevent new connections for this purpose?
>
> Thanks,
> Phil Lachmann