Skip to Content
avatar image
Former Member

SAP ASE 15.0.3 compatibility with ASE 12.5

We are in ASE 15.0.3 and if I run an "update index statistics" and if the compatibility is set to 'full' server-wide, what impact will this have on the optimizer?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jun 28, 2017 at 02:58 PM

    Note that 15.0.3 is very old at this point and is past SAP's end-of-mainstream-support date.
    I recommend upgrading to a current version, 15.7 or 16.0

    I don't believe compatibility mode has any affect on the UPDATE STATISTICS command.
    The UPDATE STATISTICS command would cause the optimizer to have the statistics it most likely needs to pick a plan based on good cost estimations.

    The compatibility mode would prevent the optimizer from using some of the new query processing methods, for example

    Compatibility mode does not support these Adaptive Server 15.0 features:
    • Partitioned tables
    • group bys with more than 31 columns
    • Scrollable and insensitive cursors
    • Commands on computed columns
    • Queries that fire “instead-of-triggers”
    • Queries executed inside “instead-of-triggers”

    https://wiki.scn.sap.com/wiki/display/SYBASE/Required+SQL+Code+changes+when+Upgrading+to+15.x


    • Queries that issue parameterized literals in the statement cache, unless the query includes an insert...values command
    • Query processing diagnostics used by showplan_in_xml
    • Queries that include hash or hashbyte functions
    • User-defined functions (SQL UDFs)

    You might find these documents helpful:

    https://wiki.scn.sap.com/wiki/display/SYBASE/15.5+Migration+Guide

    https://wiki.scn.sap.com/wiki/display/SYBASE/Required+SQL+Code+changes+when+Upgrading+to+15.x

    -bret
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      I agree with you, Bret, that going to ASE 15.7 or 16.x is the way to go but in reality (short-term of immediate option), application changes and its repercussion is an expensive exercise, not to mention the downtime to clients, if not planned properly.

      Going back to my concern, if ASE is running in compatibility mode, how does ASE determine when to switch to full compatibility or to restricted mode? I would assume the plan loaded by the optimizer has been generated and flagged with that mode at generation (compile time).

      I do the following:
      a) disable the server-wide setting
      (sp_configure 'enable compatibility mode',0)
      b) run UPDATE INDEX STATISTICS on the table/objects
      c) enable compatibility mode on each procs touching the table/objects
      (set compatibility_mode on | off}

      Will the optimiser generate a plan that is 15.x compliant? What happens if the mode is changed at session level?

      Lastly, if the mode is set at server-wide, will there be a difference in the STATISTICS generated by ASE 15.x compared with the above.

      Thank you (in advance) for your feedback.

      Regards

      Jun

      .