Question Re: alter table command not working on a particular table in Technology Q&A

alter table command not working on a particular table

Former Member — Sun, 31 Mar 2013 01:54:51 GMT

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

Re: alter table command not working on a particular table

johnsmirnios — Sun, 31 Mar 2013 07:12:31 GMT

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.

Re: alter table command not working on a particular table

Breck_Carter — Sun, 31 Mar 2013 09:10:00 GMT

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.

Re: alter table command not working on a particular table

Former Member — Wed, 03 Apr 2013 02:00:59 GMT

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.

Re: alter table command not working on a particular table

Former Member — Wed, 03 Apr 2013 02:46:31 GMT

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.

Re: alter table command not working on a particular table

jeff_albion — Wed, 03 Apr 2013 10:11:16 GMT

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

Re: alter table command not working on a particular table

Former Member — Thu, 04 Apr 2013 03:38:38 GMT

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'

Re: alter table command not working on a particular table

reimer_pods — Thu, 04 Apr 2013 03:43:22 GMT

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