Analyze Capture fails in Workload Analyzer

[Former Member]

Hello,

I am using free 64-bit SAP ASE-16_0 SP02 Developer Edition (with SAP COCKPIT-4 option installed) on x86_64 Fedora-23 (kernel-4.4.9, glibc-2.22). The exact ASE version string is

Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25320 SMP/P/x86_64/Enterprise Linux/ase160sp02plx/2492/64-bit/FBO/Sat Nov 21 04:05:39 2015

Everything works fine and extremely fast and SAP COCKPIT-4 is a great interface. Unfortunately there is a problem with Workload Analyzer. It works fine and captures workloads correctly, but when it comes to analyzing a capture – the analyze fails, although all the prerequisites are satisfied (16 KB server, utf-8 server charset, 16 GB sybcatdb database on two separate devices [8 GB data + 8 GB log], as well as all ASE configuration options according to the WA Users Guide). You can see the error message in attached screenshot and here:

========

Capture analysis failed.

Analyzing analysis failed for capture SYBASE_20160527_092344(5).

com.sybase.workload.exception.ASECatRepoServerException: Call stored procedure p_setupanalysisenv error.

com.sybase.jdbc4.jdbc.SybSQLException: 'procedurep_addcapreqtds_cap5s' is not a recognized CREATE option.

(ASECatRepoSvrASEController.java: Line 770)(ASECatCaptureMgr.java: Line 4639).

========

I think this is a bug in stored procedures installed by 'installsybcatdb' script into 'sybcatdb' database. The keyword 'procedure' and the name of procedure 'p_addcapreqtds_cap5s' in CREATE statement are mistakenly concatenated. Instead of having been written as

create procedure p_addcapreqtds_cap5s

it was written as

create procedurep_addcapreqtds_cap5s

Is there anybody who knows how to fix this? Without being able to analyze captured workloads there is no sense to install and use Workload Analyzer option in developer edition.

Thanks in advance.

[Former Member]

Hello Mark,

thanks for responding. Nice to see you are doing well as always. First to answer your questions.

> has the t_baseprocedures table been created?

Yes, it has been created and exists.

> does the following return anything:

> select * from t_baseprocedures where procname = 'p_addcapreqtds'

Yes, it returns 2-columns row: “p_addcapreqtds” and “create procedure p_addcapreqtds ...” varchar strings correctly.

> if the above does return a record, does the source code include

> a space between the words 'procedure' and 'p_addcapreqtds'?

Yes it includes a space, as you can see above. So far, so correct.

> can you manually create the proc in question (p_addcapreqtds_cap_5)

> by running the following: exec p_setupanalysisenv 5

No, because words 'procedure' and '<p_name>' are concatenated in @proctext variable for all procedures, not only for 'p_addcapreqtds_cap_5'. No blank space between the words.

The installation script 'installsybcatdb' is buggy, specially the procedure 'p_setupanalysisenv'. There is a strange behavior in cursor loop in the procedure.

fetch c1 into @procname, @proctext

while @@sqlstatus = 0

begin

select @newprocname = @procname + "_cap" + @logicalcapid

select @proctext = str_replace(@proctext, @procname, @newprocname)

Everything looks fine and correct, but putting the line

select @proctext

in the next line shows concatenated words. This forced me to manually put a blank space.

select @proctext = str_replace(@proctext, @procname, " " + @newprocname)

and that made the job. The same is true with cursor c2 loop dealing with table and new table names, where I also put the blank space manually.

select @proctext = str_replace(@proctext, @tablename, " " + @newtablename)

Unfortunately, this is not the only problem. There are a bunch of other errors during the execution of this procedure probably due to incorrect fetching into @proctext varchar(16384) variable in cursor c1 and c2 loops.

Regards,

Niksa

[Former Member]

Most definitely this is str_replace issue, in fact a bug in str_replace builtin function. Here is a trivial interactive example.

pubs2:1> declare @proctext varchar(100)

pubs2:2> select @proctext = "create procedure p_test as select * from publishers"

pubs2:3> select @proctext = str_replace(@proctext, "p_test", "p_test_v22")

pubs2:4> select @proctext

pubs2:5> go

(1 row affected)

(1 row affected)

----------------------------------------------------------------------------------------------------

create procedurep_test_v22t as select * from publishers                                           

(1 row affected)

pubs2:1>

[Mark_A_Parsons]

Hello Niksa, long time no see!

Looks like str_replace locates the string to replace but then offsets the start/end positions by -1; so in your test case instead of replacing 'p_test' it's replacing ' p_tes' ("Duh, Mark!" ?):

======================================

-- original

create procedure p_test as select * from publishers

-- should be

create procedure p_test_v22 as select * from publishers

-- actual

create procedurep_test_v22t as select * from publishers

======================================

I'm not finding anything in the published CR lists of PL03 and PL04 so you'll likely need to contact tech support to see if they already know about this.

------------------

I'm wondering if this issue shows up in other string functions, is different for @variables vs literals, and/or is related to utf-8, eg:

======================================

-- some sample tests to run against a utf-8 dataserver and a non/utf-8 dataserver ...

-- str_replace() with literal

select str_replace("create procedure p_test as select * from publishers", "p_test", "p_test_v22")

go

-- str_replace() with literal and multi-string search/replace;

-- all 3x correct? all 3x wrong? mix of correct/wrong?

select "create procedure p_test as select * from publishers"

union all

select str_replace("create procedure p_test as select * from publishers", " p", "XY")

go

/* correct result:

---------------------------------------------------

create procedure p_test as select * from publishers

createXYrocedureXY_test as select * fromXYublishers

*/

-- stuff() with @variable

declare @proctext varchar(100)

select @proctext = "create procedure p_test as select * from publishers"

select stuff(@proctext, 18, 6, "p_test_v22")

go

-- stuff() with literal

select stuff("create procedure p_test as select * from publishers", 18, 6, "p_test_v22")

go

-- charindex() with @variable; returns 17 (wrong) or 18 (right)?

declare @proctext varchar(100)

select @proctext = "create procedure p_test as select * from publishers"

select charindex("p_test", @proctext)

go

-- charindex() with literal; returns 17 (wrong) or 18 (right)?

select charindex("p_test", "create procedure p_test as select * from publishers")

go

-- patindex() with @variable; returns 17 (wrong) or 18 (right)?

declare @proctext varchar(100)

select @proctext = "create procedure p_test as select * from publishers"

select patindex("%p_test%", @proctext)

go

-- patindex() with literal; returns 17 (wrong) or 18 (right)?

select patindex("%p_test%", "create procedure p_test as select * from publishers")

go

-- substring() with @variable: returns "p_test" or " p_tes"?

declare @proctext varchar(100)

select @proctext = "create procedure p_test as select * from publishers"

select substring(@proctext, 18, 6)

go

-- substring() with literal; returns "p_test" or " p_tes"?

select substring("create procedure p_test as select * from publishers", 18, 6)

go

======================================

[Mark_A_Parsons]

Fired up a Solaris/x86 VM and installed ASE 16:

- ASE 16.0 SP02 PL03 (Solaris/x86)

- utf8/binary

- 2K page size

Your test case generates the correct result.

All of my test cases generate correct results.

Will have to see if I can locate a linux host to see if this is an OS issue ... and if no issue there ... will have to locate a SP02 PL02 install ...

[Former Member]

Yes, looks like str_replace offsets the start/end positions, but it happens only if a blank space (chr(32)) or more blank spaces exist in the source string in front of located pattern string. In the case of more existing blank spaces, each and every one blank space is replaced with the replacement string. Fore example

master:1> select str_replace("abc", "b", "R")

master:2> go

---

aRc

This is correct, no blank space(s).

master:1> select str_replace("a bc", "b", "R")

master:2> go

----

aRRc

Located pattern string and the blank space in front of it are replaced with the replacement string.

master:1> select str_replace("a      bc", "b", "R")

master:2> go

---------

aRRRRRRRc

Wow! Very productive!

master:1> select str_replace("a      bc", "b", "RRR")

master:2> go

---------------------------

aRRRRRRRRRRRRRRRRRRRRRc

That's it. Note however: this strange str_replace behavior doesn't exist in non/utf-8 servers, at least not in iso_1/binary server. I've just tested str_replace running against a 16K/iso_1/binary server and the function works correctly as expected. Also, Workload Analyzer in SAP-COCKPIT-4 is running perfectly without any error (Capture, Analyze and Replay).

This is really funny. 16K page size and utf-8 server charset are the key prerequisites to install Workload Analyzer repository. The 'installsybcatdb' script will refuse and abort the installation of repository for any charset other than utf-8 (if you don't edit the script). Cannot believe they never tested WA running against an utf-8 server.

Here are generated outputs of the tests you required, running against the utf-8 server.

-- some sample tests to run against a utf-8 dataserver and a non/utf-8 dataserver ...

-- str_replace() with literal

master:1> select str_replace("create procedure p_test as select * from publishers", "p_test", "p_test_v22")

master:2> go

-----------------------------------------------------------------------------------

create procedurep_test_v22t as select * from publishers

-- str_replace() with literal and multi-string search/replace;

-- all 3x correct? all 3x wrong? mix of correct/wrong?

master:1> select "create procedure p_test as select * from publishers"

master:2> union all

master:3> select str_replace("create procedure p_test as select * from publishers", " p", "XY")

master:4> go

---------------------------------------------------

create procedure p_test as select * from publishers

createXYrocedureXY_test as select * fromXYublishers

-- stuff() with @variable

master:1> declare @proctext varchar(100)

master:2> select @proctext = "create procedure p_test as select * from publishers"

master:3> select stuff(@proctext, 18, 6, "p_test_v22")

master:4> go

--------------------------------------------------------------------------------------------------------

create procedure p_test_v22 as select * from publishers

-- stuff() with literal

master:1> select stuff("create procedure p_test as select * from publishers", 18, 6, "p_test_v22")

master:2> go

-------------------------------------------------------------

create procedure p_test_v22 as select * from publishers

-- charindex() with @variable; returns 17 (wrong) or 18 (right)?

master:1> declare @proctext varchar(100)

master:2> select @proctext = "create procedure p_test as select * from publishers"

master:3> select charindex("p_test", @proctext)

master:4> go

-----------

          17

-- charindex() with literal; returns 17 (wrong) or 18 (right)?

master:1> select charindex("p_test", "create procedure p_test as select * from publishers")

master:2> go

-----------

          17

-- patindex() with @variable; returns 17 (wrong) or 18 (right)?

master:1> declare @proctext varchar(100)

master:2> select @proctext = "create procedure p_test as select * from publishers"

master:3> select patindex("%p_test%", @proctext)

master:4> go

-----------

          18

-- patindex() with literal; returns 17 (wrong) or 18 (right)?

master:1> select patindex("%p_test%", "create procedure p_test as select * from publishers")

master:2> go

-----------

          18

-- substring() with @variable: returns "p_test" or " p_tes"?

master:1> declare @proctext varchar(100)

master:2> select @proctext = "create procedure p_test as select * from publishers"

master:3> select substring(@proctext, 18, 6)

master:4> go

------------------------

p_test

-- substring() with literal; returns "p_test" or " p_tes"?

master:1> select substring("create procedure p_test as select * from publishers", 18, 6)

master:2> go

------------------------

p_test

[Mark_A_Parsons]

I've downloaded/installed ASE 16 SP02 PL02 on linux (64bit) (same exact ASE version as you); built a 16KB dataserver and configured with utf8/binary; but I'm unable to reproduce your issue.

============================

select @@version

select @@maxpagesize

select str_replace("create procedure p_test as select * from publishers", "p_test", "p_test_v22")

go

---------------------------------------------------------------------------------------------------------

Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25320 SMP/P/x86_64/Enterprise Linux/ase160sp02plx/2492/64-bit/FBO/Sat Nov 21 04:05:39 2015

-----------

       16384

-----------------------------------------------------------------------------------

create procedure p_test_v22 as select * from publishers

============================

What else do I need to mimic your env ... what charset/sort order are you using (ie, what does your sp_helpsort output look like)?

============================

sp_helpsort

go

... snip ...

Sort Order Description

------------------------------------------------------------------

Character Set = 190, utf8

     Unicode 3.1 UTF-8 Character Set

     Class 2 Character Set

Sort Order = 50, bin_utf8

     Binary sort order for the ISO 10646-1, UTF-8 multibyte encodin

     g character set (utf8).

============================

[Former Member]

Sort order is not the same. My sort order is "45, altdict".

master:1> sp_helpsort

master:2> go

... <cut> ...

Sort Order Description

------------------------------------------------------------------

Character Set = 190, utf8                                        

     Unicode 3.1 UTF-8 Character Set                              

     Class 2 Character Set                                        

Sort Order = 45, altdict                         

     Alternate (lower-case first) dictionary ordering

So please reconfigure the server and test the str_replace issue again. The "altdict" sort order was offered by the InstallAnywhere gui installer as the first item in the sort order selection window and I chose it deliberately just to try collation of Croatian characters č Č ć Ć đ Đ š Š ž Ž.

You are right. I just reconfigured the server to use sort order "50, bin_utf8" and, really, no str_replace issue. It works correctly.

Anyway, str_replace functionality should have nothing to do with charsets and sort order selections. Therefore I consider the issue being a bug, either in str_replace or in utf-8 implementation or in both.

[Mark_A_Parsons]

Now getting the same behavior with sort order = 45:

========================

Character Set = 190, utf8

Sort Order = 45, altdict

select str_replace("create procedure p_test as select * from publishers", "p_test", "p_test_v22")

go

-----------------------------------------------------------------------------------

create procedurep_test_v22t as select * from publishers

========================

[Former Member]

Thanks a lot Mark for your outstanding cooperation. Now the real question is whether the issue is limited only to utf-8/altdict combination or not (I encountered the issue accidentally having been curious to check out altdict collating sequence with regard to Croatian specific characters). What about other sort orders related to utf-8? What about many other possible charset/sortorder combinations?

BTW, it is not clear to me why Croatian language is not supported by utf-8 character set (no appropriate sort order). It is supported only by ISO 8859-2, CP 852 and CP 1250 charsets and, instead of going multilingual, I am always forced to build ISO 8859-2 server with binary order. In Oracle, beside ISO 8859-2 and MSWIN1250, Croatian language is supported even by three unicode character sets: UTF8, AL32UTF8 and AL16UTF16. Localization setup is very simple and elegant. It is accomplished by a client side variable NLS_LANG set as an environment variable on unix platforms with three components: language, territory and character set specified in the format

NLS_LANG = <language>_<territory>.<charset>

For db instance/database built against AL32UTF8 charset

NLS_LANG = CROATIAN_CROATIA.AL32UTF8

defaulting to CROATIAN sort order derived from <language>.