When in ASA10 database a char column is changed (with alter table), then the field max_inline in table sys.systabcol will be filled. When upgrading this database to ASA12 (22.214.171.12489 or 126.96.36.19919) the size of the char field cannot be reduced anymore: this gives the error Illegal column definition.
/*Create a database in ASA10 and create a table:*/ create table tSample( tField char(100) ); /*Fill the field with some data*/ insert into tSample(tField) values('A'); /*alter size of the field in ASA10, resulting in sys.systabcol.max_inline=75*/ alter table tSample alter tField char(75); Now convert to ASA12!!! /*After conversion trying to reduce the field size will fail:*/ alter table tSample alter tField char(70);
When the size of the field was NOT cahnged in ASA10, the field sys.systabcol.max_inline is still empty on conversion. After the conversion, it is only one time possible to change the field size. After that sys.systabcol.max_inline is filled and any consecutive reduction of the char field size results in the same error Illegal column definition.
Please advice, since we are on the verge of bringing out our new software, only waiting to resolve this problem.
When I tried the repro from your post on the newsgroup, I reloaded to v12 prior to the first alter (as per your instructions there) and could not repro the issue. When I did the reload to v12 after the first alter as described in this forum, I repro'd the issue and I can confirm that my suggested workaround works. Just execute the following two statements to get the alter to work:
alter table tSamplealter tField char( 75 ) inline use default prefix use default;
alter table tSample alter tField char( 70 );
In particular, notice that the first statement does not attempt to change the column width.
This simple workaround should allow you to script your upgrades without waiting for an EBF.
answered 24 Jan '12, 09:52
Arco had posted the identical question in the NNTP forum sybase.public.sqlanywhere.general, where John Smirnios had indicated that this is a known issue and will be fixed in a forthcoming EBF.
Edit: To cite John's response here:
i've upgraded many db's from ASA 8 to 11 and several to ASA 12.
answered 21 Jan '12, 14:51