Look through EVERYONEs UDO's for a dodgy SQL statement

On the hunt for a rouge SQL statement…

 We have the following clue:

 SELECT SDAN8,SDQTYT,SDPPDJ,SDUORG,SDDCT,SDFRGD,SDDELN,SDPA8,SDADTM,SDTHRP,SDSRP2,SDPRP2,SDUPRC,SDTHGD,SDOPTT,SDFRMP,SDSHPN,SDSO15,SDOORN,SDDMCT,SDRSDJ,SDSQOR,SDLITM,SDMOT,SDCOMM,SDLNTY,SDDOCO,SDDRQT,SDLTTR,SDRFRV,SDEMCU,SDSOBK,SDNXTR,SDDCTO,SDLOTN,SDPRP3,SDDSC1,SDSWMS,SDLNID,SDFUP,SDRCTO,SDFRTH,SDPEFJ,SDSRP5,SDOPDJ,SDPOE,SDPMTN,SDOKCO,SDAITM,SDOPPID,SDKITID,SDUOM2,SDRKCO,SDDRQJ,SDAAID,SDVR01,SDDGL,SDALLOC,SDRSDT,SDLOCN,SDANBY,SDADDJ,SDMCU,SDDOC,SDCNDJ,SDAEXP,SDPRP4,SDPEND,SDZON,SDDVAN,SDUOM,SDNUMB,SDSRP4,SDPMTO,SDPRIO,SDSO16,SDRLLN,SDSHAN,SDPSIG,SDRKIT,SDPDTT,SDKCO,SDPMPN,SDITM,SDCORD,SDCARS,SDIVD,SDSPATTN,SDSRP1,SDTORG,SDUNCS,SDTRDJ,SDPSN,SDOGNO,SDVR02,SDFUC,SDPRP5,SDSOCN,SDKCOO,SDPRP1,SDUOM4,SDSHCCIDLN,SDCRCD,SDOCTO,SDSRP3,SDASN,SDTPC,SDSOQS,SDRLIT,SDPDDJ,SDPNS,SDFEA,SDSFXO,SDDSC2,SDSONE,SDCRR,SDRORN FROM TWEDTA.F4211 WHERE (((((((((((((((((((((SDLITM LIKE :1  ) OR SDLITM LIKE :2  ) OR SDLITM LIKE :3  ) OR SDLITM LIKE :4  ) OR SDLITM LIKE :5  ) OR SDLITM LIKE :6  ) OR SDLITM LIKE :7 ....

 

What can I tell from this?

• Looks like a JDE statement, by the capitals & syntax used
• Counting the (‘s there are 20 different conditions applied, the assumption is that it’s looking only for LITM like – but because I do not have the complete statement – I cannot be sure.
• Seems to have been run more than once
• This is killing the performance of JDE – it’s not using any indexes

So, what can we do to find this?  Firstly, lets make sure that this is NOT someone’s special advanced query.  If someone has saved this, they could run it (automatically) when they launch P42101 (for example).

Advanced queries are stored in F952430, generally in central objects.  The acutual query is stored in a blob, but is pretty easy to read – it’s XML.

UDO tables are explained below:  This is a handy reference table.

User Defined Objects	Object Type (H92|OT)	Runtime Table
One View Reports	ONEVIEW	F952400 - User Generated Contents Detail
EnterpriseOne Pages	E1PAGE	F952400 - User Generated Contents Detail
Composite Pages (from Tools Release 9.2.0.2)	COMPOSITE	F952410 - Composite Page
Images (from Tools Release 9.2.0.2)	IMAGE	F952415 - Image
One View Watchlists	WATCHLIST	F952420 - OneCenter Watchlists Detail
Notifications (from Tools Release 9.2.2.0)	NTF	F952425 - Notification
Advanced Queries	QUERY	F952430 - Advance Query
Personal Forms (from Tools Release 9.2.1)	PERSFORM	F952435 - Dynamic Form Personalization
Form Extensions (from Tools Release 9.2.2.4)	FORMEXTNS	F952439 - Form Extensions
Grid Formats	FORMAT	F952440 - Grid Formats Detail
EnterpriseOne Search (From Tools Release 9.2.1.2)	SEARCH	F952445 - EnterpriseOne Search
Composite Application Framework (CafeOne)	CAFE1	F952450 - CafeOne Layout Table
Schedules (from Tools Release 9.2.2.0)	SCHEDULE	F952455 - Scheduler
Orchestrations	ORCH	F9524810 - Orchestrations
Cross Reference	XREF	F9524820 - Cross Reference
Rule	RULE	F9524830 - Rule
Service Request	SREQ	F9524840 – Service Request
Whitelist	WLST	F9524850 - Whitelist

 

So I developer the query below to look for a clause that has at least 15 conditions.  Here is a hint, if you suspect your user is using the “in” functionality – then it will not be using the like statement!  They need to be using the =’s operator if the user has chosen in.  I can therefore determine that my query above is using like, so there must be many separate conditions specified.  This can only be done with an advanced query (or data selection in a UBE, but I know that this is from the web).

As a template SQL below looks for any advanced query using or more than 15 times…  

Remember I know that there are at least 20 operators, so I started by look at statements that are saved with over 15.  Note that I’m also only looking at those that use an “or” operator by specifying the regex_match for LOGIC=false.   I’m actually counting the “WHERE clause” conditions with my regex.

 

select AQUSER, AQFMNM, AQSFMNM, AQWOBNM, utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,1))

from pd920.f952430

where  regexp_count(utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,1))||utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,2001)), 'SPECIAL_VALUE', 1,'c') >= 15

and regexp_count(utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,1))||utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,2001)), '\/CONDITION', 1,'c') >= 15

and regexp_count(utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,1))||utl_raw.cast_to_varchar2(dbms_lob.substr(AQOMRBLOB,2000,2001)), 'LOGIC="false"', 1,'c') = 1;

 

We can build on this to look for “like” and not like etc etc, but need the entire SQL statement if that is possible.

Anyway, you can see form the above it’s easy to query the UDO’s in bulk and find what we are looking for.

The below is a sample using & and contains… you can see how it’s easy to read.

 

'<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="3ae9c029d8e7412c9f939d99fa6fad3a" LOGIC="true">

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">100</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.20" OPERATOR="4">

      <VALUE SPECIAL_VALUE_ID="0">ee</VALUE>

   </CONDITION>

</ADVANCED_QU

Here are some sample queries and the associated XML’s from JDE UDOs.

 

REM INSERTING into EXPORT_TABLE

SET DEFINE OFF;

I<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="7fffc84b04384edab7622f5539d395c4" LOGIC="false">

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">1000</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">500</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">200</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">100</VALUE>

   </CONDITION>

</ADVANCED_QUERY>');

 

 

<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="bdee48faf48f4b9a8678540d8514dbac" LOGIC="false">

   <CONDITION LEFT="qbe0_1.48" OPERATOR="2">

      <VALUE SPECIAL_VALUE_ID="0">100</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.48" OPERATOR="2">

      <VALUE SPECIAL_VALUE_ID="0">200</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.48" OPERATOR="2">

      <VALUE SPECIAL_VALUE_ID="0">300</VALUE>

   </CONDITION>

</ADVANCED_QUERY>');

 

 

<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="5c9eaa0b95fc4bfe91ef0e5328ad45b5" LOGIC="false">

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">100</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.48" OPERATOR="4">

      <VALUE SPECIAL_VALUE_ID="0">000</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.48" OPERATOR="4">

      <VALUE SPECIAL_VALUE_ID="0">100</VALUE>

   </CONDITION>

</ADVANCED_QUERY>');

<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="3da0a6e18d6a4c4db7f9016a7334115e" LOGIC="false">

   <CONDITION LEFT="qbe0_41.109" OPERATOR="2">

      <VALUE SPECIAL_VALUE_ID="0">10000</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_41.109" OPERATOR="2">

      <VALUE SPECIAL_VALUE_ID="0">2000</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_41.109" OPERATOR="2">

      <VALUE SPECIAL_VALUE_ID="0">4000</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_41.109" OPERATOR="0">

      <VALUE SPECIAL_VALUE_ID="0">1234</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_41.109" OPERATOR="0">

      <VALUE SPECIAL_VALUE_ID="0">456789</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_41.109" OPERATOR="0">

      <VALUE SPECIAL_VALUE_ID="0">123456</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_41.109" OPERATOR="0">

      <VALUE SPECIAL_VALUE_ID="0">789456</VALUE>

   </CONDITION>

</ADVANCED_QUERY>');

 

<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="f2016ecdb4c147ec843b4f2fdf63cb7f" LOGIC="false">

   <CONDITION LEFT="qbe0_1.48" OPERATOR="12">

      <VALUE SPECIAL_VALUE_ID="0">100</VALUE>

      <VALUE SPECIAL_VALUE_ID="0">200</VALUE>

      <VALUE SPECIAL_VALUE_ID="0">3000</VALUE>

      <VALUE SPECIAL_VALUE_ID="0">4000</VALUE>

   </CONDITION>

</ADVANCED_QUERY>');

 

<?xml version = ''1.0'' encoding = ''UTF-8''?>

<ADVANCED_QUERY AUTOCLEAR="false" AUTOFIND="true" GUID="3ae9c029d8e7412c9f939d99fa6fad3a" LOGIC="true">

   <CONDITION LEFT="qbe0_1.19" OPERATOR="5">

      <VALUE DECIMALFORMAT="." SPECIAL_VALUE_ID="0">100</VALUE>

   </CONDITION>

   <CONDITION LEFT="qbe0_1.20" OPERATOR="4">

      <VALUE SPECIAL_VALUE_ID="0">ee</VALUE>

   </CONDITION>

</ADVANCED_QU

 When logic="TRUE" this seems to be AND

 

You've seen all of the examples above, and you know they query and the syntax.  If you have similar problem queries, this will allow you to troll through the UDO's and do a mail merge with users - telling them that you are going to delete their 20 non indexed OR based text range queries - and DON'T do it again!