alter table column in sybase ase

[Rottdpogs]

please advice. im altering the size length of a field, how will i do that? the field is also part of the index. the thanks in advance.

 

[JMCraig]

I've created a FAQ in the forum on this but apparently it takes a while for that to become usable. Here's the simplest option (I list some more in the FAQ):

Use ALTER TABLE to add the new version of the column.

Populate it via an UPDATE statement.

Drop and recreate your indexes.

Now use ALTER TABLE to drop the old version of the column.

This will not recover the space occuppied by the old version of the column, but it's probably the simplest way to deal with it.

Good luck!

J M Craig
nsjmcraig@netscape.net

 

[Rottdpogs]

Thanks.I'll try

 

[JMCraig]

It turns out that in at least Sybase 12.5 (I haven't checked the docs for earlier versions), you can use the ALTER TABLE's MODIFY function to change a column's datatype or default.

Basically, it'll still apparently do a copy of the table under the covers, so to speak. You have to enable the SELECT INTO/BULK COPY DB option to do it. (Be aware that this option allows non-logged operations and after one of these, your transaction log cannot be backed up--you'll need to do a full DB dump before your transaction log dumps will start to be useful again. And turn off the DB option while you're at it!) It doesn't say so in the portion of the docs I was reading, but it appears to me that ALTER TABLE with the MODIFY command is likely to do a non-logged operation (i.e. invalidate your transaction log).

I apologize for not taking time to run this back to see what version first had this capability. You can check out the on-line docs at Sybase.com if you like.

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[Rottdpogs]

Thanks again.