Skip to Content
avatar image
Former Member

optdiag in ASE 16.0 SP03 PL02/EBF 27415 on Linux appears to be broken

I have just installed the Express Edition of ASE 16.0 SP03 PL02/EBF 27415 in a virtualised RHEL 7.4 environment hosted by Widows 7. I did it to check that the appropriate version of sp__optdiag I have available at works with SP03 as well as SP02. I didn't expect optdiag to give an error, which it did as follows for a char (5) column in a table with every datatype

Internal error: data length mismatch in ct_fetch().
CTLIB Message: - L4/O2/S6/N36/6/0:
ct_send(): protocol specific layer: internal Client Library error: There is a tds state machine error. An illegal tds token sequence was received.

sp__optdiag ran against the same database using isql with no problems.

The database was created as per t1.txt and the tables were created as per the other two text files.

The same database in SP02 directly running on Windows 7 gives complete optdiag output with no errors, and it matches the sp__optdiag output.

I do not know if this is a problem with this version of optdiag or with how ASE has been installed.

Does anyone have a test copy of SP03 that is running directly on Linux (or maybe any OS) that they can test optdiag against, using a database for the tables created in the two files?

t1.txt (364 B)
all-types.txt (12.5 kB)
dol-types.txt (12.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Feb 09, 2018 at 03:21 PM

    Hi Raymond,

    I meant to say I did have a quick shufty at this the other day. It does reproduce but only when LANG is set to en_US.UTF-8, i.e. the bash default, set it to C or blank and you should find it works. The TDS traces didn't throw out anything obvious just a load of rows coming back as LONGCHAR rather than CHAR (expected with UTF8) and then it just goes splat, It probably needs debugging by a friendly engineer.

    I suspect (hope!) it might be quite esoteric so not worth worrying too much about.



    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Simon,

      I trust that you and the rest of the chaps are fine.

      Thanks for the pointer about it being a LANG issue. I'd had problems with the LANG variable when working on a BCP with pipe Korn shell script in 64 bit Cygwin on a virtualised Windows 10 Enterprise installation. I had to unset it to get the ASE connections to work properly.

      I'll endeavour to remember to try again with LANG unset if I encounter any future problems.



  • Dec 20, 2017 at 09:26 AM

    Hi Raymond,

    As long as you are using the correcet isql for your version of ASE then you should not see TDS state errors. Use isql -v and check the version string.

    If the version is at least 16.0 or later then it looks like there may be an issue with SP03 and I would encourage you to log a support incident with your simple repro and we can look at getting a fix.

    However, can I recommend you use sp_showoptstats instead? This is a fully supported SAP procedure which will be updated and maintained for all versions in the future so hopefully you won't run into problems again.


    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 20, 2017 at 11:05 AM

    Hi Andy,

    Both the isql and optdiag versions I used are for ASE 16.0 SP03, as that is all the virtualised environment has access to.

    I do not have any ability that I am aware of to be able to raise a case with SAP support as I'm using ASE as a hobbyist. Can one be raised as an internal support case?

    On the sp_showoptstats front, SP03 is the first version that it appears to work "properly" in. Even as recently as ASE 16.0 SP02 PL02, the procedure had the same bugs that were in the original ASE 15.0 version of sp__optdiag that Kevin Sherlock produced way back when, which my versions of sp__optdiag for ASEs 15.5, 15.7 and 16.0 fix. I see that sp_showoptstats has undergone a significant re-write for SP03. But... It still only outputs in XML, despite there being a check for @option requesting text output. If I want to look at statistics, I don't want to have to find an XML parser to put the results through to be able to make sense of them. And the biggest annoyance is that it still doesn't detect the length of the text string and set textsize before trying to select the XML - needing to manually do "set textsize 9999999" and then (usually) re-execute the procedure.

    sp_showoptstats can't be a replacement for optdiag, as optdiag is needed for manually manipulating statistics if needed.



    Add comment
    10|10000 characters needed characters exceeded

  • Jan 18, 2018 at 06:04 PM

    Is there an XSL file to convert the sp_showoptstats XML output to HTML? And/or, is there a utility to parse the sp_showoptstats XML output and flag problem tables?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member


      I've avoided sp_showoptstats as its output is so unfriendly. sp__optdiag will produce human readable output but you'll still need to analyse it to identify any problems.



  • Jan 18, 2018 at 05:37 AM

    Hello Raymond,

    I really appreciate all the work you have contributed to sp__optdiag recently! Really cool stuff. It was always a goal of mine to maintain only one version of the proc that would run on all versions of 15.x, 16.x etc, but that turned out probably a pipe dream.

    I've gotten emails, messages, and even spoke to several people directly who have taken copies of sp__optdiag and further enhanced it to do all kinds of useful things as you have done (not to mention to fix bugs I left behind:(). Makes me wish it were in a Github project or some other such collaborative repository.

    I do have ASE 16.0 SP03 PL02 downloaded on Linux x86. If you haven't had anyone test out optdiag with your sample tables yet, I'd be glad to help out.

    Add comment
    10|10000 characters needed characters exceeded

    • FWIW, I am able to reproduce the error you see, but also noticed that you were led to the LANG env variable solution, which also tests out for me (unset LANG, and all is well).

      Copy stats is a great application of the code, and again, I admire and appreciate your efforts here.

      I have/had some other ideas around applications for statistics manipulations including:

      1. Statistics "versions". ie: be able to store (either in a separate table, or within systabstats/sysstatistics via other formatid's, statid's etc which are unused in those tables) different "versions" of statistics which accomodate "set statistics simulate" scenarios where having to use "optdiag statistics simulate" is cumbersome (you can only have one "set" of simulated statistics stored in catalog at one time). Along with this, an ability to "switch" statistics sets between "simulate" status and "active", or move a current "simulate set" to a separate "copy" or "version" in the catalog, then import another simulate set, move them to a separate version, etc. So, one could do:

      sp__create_stats_version <tablename>, <"active|simulate">, <statsversionname> [ [,<indexname>] , [colname] ]

      sp__switch_stats_version <tablename> , <"active|simulate">, <source_statsversionname> ,<dest_statsversionname> [ [,<indexname>] , [colname] ] [printonly]

      2. Generating all the primitive "update statistics" commands for one or more tables that could be executed either separately, or concurrently to help optimize the throughput of statistics maintenance.

      ie: instead of "update index statistics mytable" - translate this to the individual update stats commands that operate "under the covers". ie: if mytable had indexes:

      index_name_1 (col1, col2)

      index_name_2 (col2,col3, col4)

      index_name_3 (col4,col5)

      The result of proc would be to print out SQL language for several separate (but equivalent statements that could be executed in parallel to user defined degree:

      update statistics mytable index_name_1

      update statistics mytable index_name_2

      update statistics mytable index_name_3

      update statistics mytable (col3)

      update statistics mytable (col5)

      (simplified example, not taking into considerations of partitioning,global, local stats etc).