Use sp_compatmode on Adaptive Server 15.0.3 ESD #1 and later, to verify if full compatibility mode can be used effectively. sp_compatmode generates warnings if it detects configuration options that conflict with compatibility mode, such as:
One of abstract plan dump, abstract plan load or abstract plan replace is set
statement cache and literal autoparam are set
The histogram tuning factor is set to a value other than 1
Example 1: Execute sp_compatmode with these server options:
compatibility mode is set
dump/load/replace is “on”
statement cache is “on”
literal autoparam is “on”
histogram tuning factor is set to 20
1> sp_compatmodeCompatibility mode is enabled. WARNING: Compatibility mode will not be used when 'abstract plan dump/load/replace' is on. WARNING: Compatibility mode may not be used when statement cache and literal autoparam are enabled. WARNING: The configuration option 'histogram tuning factor' is configured with value '20', which is not the default value in ASE 12.5. This may lead to different accuracy of statistics and different query plans. (return status = 0)
Example 2: Execute sp_compatmode when compatibility mode is not set:
1> sp_compatmode Compatibility mode is not enabled. (return status = 0)
Changing the configuration of the histogram tuning factor
from the default in Adaptive Server 15.0 (20) to the default in
Adaptive Server 12.5 (1), creates plans that are more consistent
with Adaptive Server 12.5.
