New stored procedure sp_compatmode

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:

Example 1: Execute sp_compatmode with these server options:

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)

NoteChanging 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.