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

[Former Member]

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 www.lumphanan.com/ase 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?

[simon_ogden · Accepted solution]

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.

Cheers,

Simon

[Former Member · Accepted solution]

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.

Thanks

Raymond

[sladebe]

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?

[Former Member]

Ben,

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.

Cheers

Raymond

[kevin_sherlock]

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.

[Former Member]

Kevin,

Apologies for taking so long to reply. Could you please try optdiag on a database created using the files above in your ASE 16.0 SP03 PL02? And let me know if it was just me or if it is broken?

It's been fun working on sp__optdiag. I was basically redoing changes I'd made at JPMC before they made me redundant, but this time making the results as close to optdiag as possible. The derived statistic for Large I/O Efficiency gave me a bit of a headache, though.

I keep checking the trial versions of ASE on SAP.com to see if a newer one has been made available, but nothing so far so I don't know if it's broken in higher versions.

I saw a comment from you recently about wring some code to do a simple copy of statistics between tables. Using that as a start point might have gotten me to the end a bit quicker for the procedure I've written to do just that, which is available at www.lumphanan.com/ase

Cheers

Raymond

[kevin_sherlock]

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

[Former Member]

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.

Cheers

Raymond

[former_member466386]

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.

Cheers,
Andy