parsing dynamic sql and removing constants

[Alexandre H. Guerra]

Hello

I need to process a SQL monitoring log stored in a table to group the
statements that change just the constants in it. Ex:

select a,b,c from table where (a = 'xyz' and b = 123 and c !=
'2004-10-10') or ( a like 'def%' )
select a,b,c from table where (a = 'zyx' and b = 321 and c !=
'2004-01-01') or ( a like '%fed' )

using an udf to remove constants:

select a,b,c from table where (a = '' and b = and c != '' ) or ( a
like '' )
or
select a,b,c from table where (a = %cconstant% and b = %nconstant% and
c != %dconstant% ) or ( a like %cconstant% )

I'm building a simple udf to do this, but i found this a little more
trickier.
It seems that i could use yacc / lex to do this, but i didnt touch
anything related yet
(i wasn't willing to spend so much time)

Anybody knowns any clever algorithm/udf that i could use to do this?
Thanks

[Philip Sherman]

This is a much much more complex process than your two statements make
it appear to be. I believe that there are some purchasable performance
tools from third party vendors that do this bit I've never used any of
them and don't know how well they work.

I wrote a Java program to do this directly from a statement trace log
from UDB V7. I basically had to write an SQL statement parser then
rebuild the SQL statement. I'd be interested in "a clever algorithm" too.

Phil Sherman
Alexandre H. Guerra wrote:

Hello

I need to process a SQL monitoring log stored in a table to group the
statements that change just the constants in it. Ex:

select a,b,c from table where (a = 'xyz' and b = 123 and c !=
'2004-10-10') or ( a like 'def%' )
select a,b,c from table where (a = 'zyx' and b = 321 and c !=
'2004-01-01') or ( a like '%fed' )

using an udf to remove constants:

select a,b,c from table where (a = '' and b = and c != '' ) or ( a
like '' )
or
select a,b,c from table where (a = %cconstant% and b = %nconstant% and
c != %dconstant% ) or ( a like %cconstant% )

I'm building a simple udf to do this, but i found this a little more
trickier.
It seems that i could use yacc / lex to do this, but i didnt touch
anything related yet
(i wasn't willing to spend so much time)

Anybody knowns any clever algorithm/udf that i could use to do this?
Thanks

[Ian]

Alexandre H. Guerra wrote:

Hello

I need to process a SQL monitoring log stored in a table to group the
statements that change just the constants in it. Ex:

select a,b,c from table where (a = 'xyz' and b = 123 and c !=
'2004-10-10') or ( a like 'def%' )
select a,b,c from table where (a = 'zyx' and b = 321 and c !=
'2004-01-01') or ( a like '%fed' )

using an udf to remove constants:

select a,b,c from table where (a = '' and b = and c != '' ) or ( a
like '' )
or
select a,b,c from table where (a = %cconstant% and b = %nconstant% and
c != %dconstant% ) or ( a like %cconstant% )

I did this once using perl regular expressions, it was pretty effective.
(but I used 3 regexes to completely parameterize a statement).

There was an article on DB2DD to build a UDF that exposed the perl regex
library to DB2, so I suppose this would be possible to do within DB2.
(The script I wrote was reading SQL from a flat file).


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[Florian]

Hi,

i just wrote an UDF in C++ which uses the Perl Library to format the
constants in SQL-Statements. We are going to use this UDF in our
performance monitoring tool so i am not allowed to post it here. But
you can use the following links to implement your own UDF (1st
scenario).

1st scenario: CPerlString, Classdefinition s to integrate Perl Library
http://www.codeproject.com/string/cperlstring.asp
and integrate CPerlString in your UDF

2nd scenario: Knut Stolze (this guy knows what he does) uses PCRE
http://www-106.ibm.com/developerwork...301stolze.html

Using Lexx and yacc could be a problem (non deterministic, not context
sensitive grammar ....)

sample Perl REGEX to replace any number in a string with
'HERE_WAS_A_NUM BER':
$parse =~ s/(\W)(\d+)/$1HERE_WAS_A_NU MBER/gi;
be careful, this also make out of 123.456
HERE_WAS_A_NUMB ER.HERE_WAS_A_N UMBER
I wrote 4 regular expressions in my UDF to remove the constants and it
works for almost 90% of the sqls i tested with. Let me know if you
need some hints for the regular expressions, a sample c-code or
compiling DB2 UDFs for WIN32 (took me 2 days to set up the right
environment)

best wishes
Florian
Ian <ia*****@mobile audio.com> wrote in message news:<41******* *@127.0.0.1>...

Alexandre H. Guerra wrote:

Hello

I need to process a SQL monitoring log stored in a table to group the
statements that change just the constants in it. Ex:

select a,b,c from table where (a = 'xyz' and b = 123 and c !=
'2004-10-10') or ( a like 'def%' )
select a,b,c from table where (a = 'zyx' and b = 321 and c !=
'2004-01-01') or ( a like '%fed' )

using an udf to remove constants:

select a,b,c from table where (a = '' and b = and c != '' ) or ( a
like '' )
or
select a,b,c from table where (a = %cconstant% and b = %nconstant% and
c != %dconstant% ) or ( a like %cconstant% )

I did this once using perl regular expressions, it was pretty effective.
(but I used 3 regexes to completely parameterize a statement).

There was an article on DB2DD to build a UDF that exposed the perl regex
library to DB2, so I suppose this would be possible to do within DB2.
(The script I wrote was reading SQL from a flat file).


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---