Hi, I am trying to run alter table command on a particular able to add a column to it using the following command:

***alter table tab_name add col_name int***

And after typing the command when click Run button, the command keeps on running and doesn't stop even after a long time.And if i manually stop it nothings happens to be done.

But i am able to alter table with the same command on any other table but not on this particular table.

Any ideas where i can be doing wrong, what should i check or edit any settings. or What can be the possible reasons i am not able to run alter table command on a particular table.

regards, satyam

asked 31 Mar '13, 01:54

msatyam's gravatar image

msatyam
30112
accept rate: 0%

Comment Text Removed

See http://sqlanywhere-forum.sap.com/questions/16106/asa-7-alter-table

The comments there apply to all versions prior to 16. Either your ALTER statement is waiting for a lock because another connection has an uncommitted transaction that references the table you are trying to alter or the table is large and the server is busy rewriting all of the rows of the table.

permanent link

answered 31 Mar '13, 07:12

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

To emphasize John's reply: all it takes is a SELECT statement on some other connection to prevent ALTER TABLE from running... close that other connection or run a COMMIT to release the schema lock.

(31 Mar '13, 09:09) Breck Carter

Even after running commit command i am not able to run alter table command it is going in infinite loop, is there any other command to close all open connections.

(03 Apr '13, 02:00) msatyam

Actually the thing is my one application connects to database server but at the same time i am trying to run alter table command from my C code using unixodbc by creating new connection to the database. But i am not able to do so as it goes in infinite loop and my code doesn't go beyond alter table command that means it is having the same effect as if i try to alter table from dbisql when my application is connected to the database.

But i am able to use update or select command from my C ODBC code by creating the a new connection but alter is not working.

Is there some command i should run before running alter as i cannot close the other database connection.

(03 Apr '13, 02:46) msatyam
Replies hidden
2

If any connection is holding on to table schema locks (e.g. there is an open transaction / cursor), you will not be able to run the ALTER command. You should check sa_locks() ( http://dcx.sybase.com/index.html#sa160/en/dbreference/sa-locks-system-procedure.html ) to confirm if there are any open table schema locks on this table from your other connection.

Another point is that this is not an 'infinite loop' scenario - as John commented, you are either blocked or you are asking the server to perform a large amount of work and you are not giving it enough time to complete.


Database connections can be forcibly dropped using the DROP CONNECTION SQL command. ( http://dcx.sybase.com/index.html#sa160/en/dbreference/drop-connection-statement.html ).

(03 Apr '13, 10:11) Jeff Albion

Thankyou this will help i guess. One last thing i need to ask is there any way to use where clause as i need to select the row where LockTable='something'

(04 Apr '13, 03:38) msatyam
Replies hidden
2

select * from sa_locks() where table_name = 'YourTableName'

(04 Apr '13, 03:43) Reimer Pods
showing 4 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11
×10

question asked: 31 Mar '13, 01:54

question was seen: 728 times

last updated: 04 Apr '13, 09:14