|
Home > Archive > SQL Anywhere database > July 2005 > Adding a column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hi All,
I have a request from a client who is using ASA v8.0.3 and
they want to be able to add column(s) to a table without
forcing everyone to log out of the database first.
Currently when they run the script to add the column to some
table, the script blocks until all other users are
disconnected.
So, the question is: is there a way in the script to have it
add the column regardless of of an user connections? The
client does not want to force the users off if they do not
have to.
Thanks,
Brandon
| |
| Jason Hinsperger \(iAnywhere\) 2005-07-22, 1:23 pm |
| No, there is no way to force a schema change when users are using the object
that are being changed.
Making schema changes requires exclusive access to the objects involved.
--
Jason Hinsperger
Product Manager
iAnywhere Solutions
********************
********************
********************
For the latest downloads technotes, whitepapers, webcasts and other
developer
resources, go to: http://www.ianywhere.com/developer/
********************
********************
********************
<xyz> wrote in message news:42e132d0.5cb4.1681692777@sybase.com...
> Hi All,
>
> I have a request from a client who is using ASA v8.0.3 and
> they want to be able to add column(s) to a table without
> forcing everyone to log out of the database first.
>
> Currently when they run the script to add the column to some
> table, the script blocks until all other users are
> disconnected.
>
> So, the question is: is there a way in the script to have it
> add the column regardless of of an user connections? The
> client does not want to force the users off if they do not
> have to.
>
> Thanks,
>
> Brandon
| |
| Breck Carter [TeamSybase] 2005-07-22, 8:23 pm |
| Any connection that has a shared schema lock on the table must do a
commit or rollback. You might be able to query sa_conn_info and
sa_locks to determine which connections fall into that category... it
might not be all.
Breck
On 22 Jul 2005 10:54:24 -0700, xyz wrote:
>Hi All,
>
>I have a request from a client who is using ASA v8.0.3 and
>they want to be able to add column(s) to a table without
>forcing everyone to log out of the database first.
>
>Currently when they run the script to add the column to some
>table, the script blocks until all other users are
>disconnected.
>
>So, the question is: is there a way in the script to have it
>add the column regardless of of an user connections? The
>client does not want to force the users off if they do not
>have to.
>
>Thanks,
>
>Brandon
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
|
| There are no locks of any kind on the table in question.
| |
| Breck Carter [TeamSybase] 2005-07-25, 1:26 pm |
| On 25 Jul 2005 06:52:16 -0700, xyz wrote:
>There are no locks of any kind on the table in question.
In that case is should be possible to alter the table without kicking
anyone off the system.
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Jayvee Vibar 2005-07-27, 3:23 am |
| Ok. it isn't possible.
How about scheduling the change? When there's no one using the table, a
script would be executed automatically. Is this possible? Does anybody know
a simple setup that does this way? Thanks.
<xyz> wrote in message news:42e132d0.5cb4.1681692777@sybase.com...
> Hi All,
>
> I have a request from a client who is using ASA v8.0.3 and
> they want to be able to add column(s) to a table without
> forcing everyone to log out of the database first.
>
> Currently when they run the script to add the column to some
> table, the script blocks until all other users are
> disconnected.
>
> So, the question is: is there a way in the script to have it
> add the column regardless of of an user connections? The
> client does not want to force the users off if they do not
> have to.
>
> Thanks,
>
> Brandon
| |
| Breck Carter [TeamSybase] 2005-07-27, 7:24 am |
| What folks sometimes do is just run the ALTER TABLE, let it wait until
it can run. Sometimes this takes hours, but it will run as soon as the
last blocking lock is gone.
Breck
On 27 Jul 2005 00:21:46 -0700, "Jayvee Vibar" <jcnvibar@hotmail.com>
wrote:
>Ok. it isn't possible.
>How about scheduling the change? When there's no one using the table, a
>script would be executed automatically. Is this possible? Does anybody know
>a simple setup that does this way? Thanks.
>
><xyz> wrote in message news:42e132d0.5cb4.1681692777@sybase.com...
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Erik Anderson 2005-07-27, 11:24 am |
| I've usually been a bit scared to do something like that; often times after
a few minutes I find that other connections block on my connection that is
blocking to do the update; I then have to back out my command or find a
lengthening line forming behind me...
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:qjqee11q0vpurki
58kra4umauup86vtc2i@
4ax.com...
> What folks sometimes do is just run the ALTER TABLE, let it wait until
> it can run. Sometimes this takes hours, but it will run as soon as the
> last blocking lock is gone.
>
> Breck
>
> On 27 Jul 2005 00:21:46 -0700, "Jayvee Vibar" <jcnvibar@hotmail.com>
> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Breck Carter [TeamSybase] 2005-07-27, 1:24 pm |
| Don't see how that would happen here. The connection doing the ALTER
TABLE has no locks at all while it is blocked, so it can't be blocking
anything else. FWIW it is a technique used quite often, in production,
at my largest client... it works well in practice and is not
dangerous, and tends to avoid the need for "system maintenance
windows" which are practically impossible these days (yes, the changes
are tested beforehand :)
The only downside is that the ALTER TABLE connection shows up in the
"block sniffer" display in bright red since it is very long lasting
(block sniffer is a custom app that watches for Very Bad Things like
blocks and connections using multi-Gbyte chunks of temp file space).
Breck
On 27 Jul 2005 09:51:08 -0700, "Erik Anderson"
< erikba@teamworkgroup
.com> wrote:
>I've usually been a bit scared to do something like that; often times after
>a few minutes I find that other connections block on my connection that is
>blocking to do the update; I then have to back out my command or find a
>lengthening line forming behind me...
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:qjqee11q0vpurki
58kra4umauup86vtc2i@
4ax.com...
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Greg Fenton 2005-07-28, 11:26 am |
| Breck Carter [TeamSybase] wrote:
> (block sniffer is a custom app that watches for Very Bad Things like
> blocks and connections using multi-Gbyte chunks of temp file space).
When you say "custom app", do you mean "unavailable to the general public"?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
|
|
|
|
|