Question Re: backward compatiblity in Technology Q&A

backward compatiblity

Former Member — Thu, 23 Apr 2015 07:32:24 GMT

we are planning to upgrade our legacy systems with versions 12.5.4 to latest 16

I want to explore the option of backward compatibility. Is this automatically enabled or there is an option that needs to be installed or configured?

If there are resources for testing the compatibility, please point me to those as well.

Regards

Re: backward compatiblity

Former Member — Thu, 23 Apr 2015 13:13:32 GMT

I suggest to do tests with the compatibility mode and without the compatibility mode in ASE16.

When we moved from ASE12.5 to ASE15.5, some of our stored procedures were slower in ASE15.5 so we configured these stored procedures to use the compatibility mode so that they run with the ASE12.5 optimizer. Then later we re-wrote the stored procedure to be faster in ASE15.5 to disable the compatibility mode.

I think a similar scenario can be applied to go from ASE12.5.4 to ASE16.

Session level – use set compatibility_mode on to enable compatibility mode at the current level or inside the stored procedure

Server-wide – use sp_configure 'enable compatibility mode', 1 to enable thee compatibility mode for the server.

Re: backward compatiblity

ChrisBaker — Thu, 23 Apr 2015 14:36:52 GMT

I would be very careful about using 'compatibility mode' to solve everything. It will not support many of the new ASE features (e.g. partitions) and can mask other issues.

Many years ago, there was a Sybase site dedicated to ASE migrations to ASE 15, but that is no longer available, and, besides, the documentation would be stale, considering the plethora of new features available in the 15.x and 16.x codelines. (e.g. DirectIO, optimizer goals, etc).

I would suggest first starting with the current migration guide at:

Migration Technology Guide

Along with the guide, some of the strongest suggestions would be to:

Ensure that you delete all statistics for all tables (if restoring or upgrading an existing 12.5.x database).
Never use an old ASE .cfg file.
Run 'update index statistics' on all tables. (This can help the most in any poorly performing queries).
Add necessary indexes.
Set the 'optimizer level' to 'ASE_CURRENT'
Use MDA and showplan/plancost to help identify poorly running queries and then rewrite them if necessary.
Keep statistics up-to-date

The ASE 15+ optimizer is capable of generating Cartesian products from poorly written SQL that earlier ASEs did not consider - often this can be the reason for needing 'compatibility mode.

Also consider bringing in professional services to help in any migration plan/performing the migration.

Chris

Re: backward compatiblity

Former Member — Thu, 23 Apr 2015 15:10:37 GMT

In my opinion, many suggestions in the Migration Technology Guide are important as a base/prerequisite but don't fully reflect the reality of the client site.

For example, for many slow SPs, I've deleted and recreated all index statistics (and even statistics of non-indexed columns) and it did not make any difference. It was still slow. I tried to understand by looking at the query plans, the code and the indexes.

If you have thousands of SPs, tables, it is very difficult to have the time and and the resources to review the code of all and the query plans especially because the query plan format is different between ASE12 and ASE15/16 and not everyone can easily understand the query plan to figure out for example why the optimizer does not use the index you want to use.

I think it is really important to understand that the optimizer has been re-written a lot so it should not be a surprise that some SPs might need to be completely rewritten and retested to be as fast as before (using User-Defined Functions, removing temporary tables, testing subqueries, using while loop instead of cursors....). It was we did and it worked without changing the statistics strategy.