alter table command not working on a particular table

[Former Member]

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

[johnsmirnios]

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.

[Breck_Carter]

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.

[Former Member]

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.

[Former Member]

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.

[jeff_albion]

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 ).

[Former Member]

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'

[reimer_pods]

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