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, Classdefinitions 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_NUMBER':
$parse =~ s/(\W)(\d+)/$1HERE_WAS_A_NUMBER/gi;
be careful, this also make out of 123.456
HERE_WAS_A_NUMBER.HERE_WAS_A_NUMBER
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*****@mobileaudio.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 =---