I am trying to change the datatype of an existing column in sybase IQ.
seems sybase IQ dont support alter table <table name> modify <col> <datatype> format to change the column datatype.
After some investigation I could find two ways to achive this. Please let me know if there are any other better ways to do an 'inplace' datatype change on a column. In some other DBs(MySQL i suppose) there is a way to specify after or before a specific column so that a new column will be created relative to that column. Is something similar available in sybase IQ.
1.Create a new column, copy data from old column, drop old column and rename new column to old column. Pros: all the indexes , constraints, permissions etc on table will remain in tact and we need not create them Cons: The new column will be added to the end of the table
2.Take a copy of entire table, drop the entire table and recreate with required modification, copy data back.
Pros: we will get the column name at the place we want Cons: we have to recreate indices, constraints, permissions, keys etc on the table (we need to make sure we back up these too before we drop main table)