No, no, there is nothing special about DB2 (except the details, of course). I did my vote +0, Andrea can go on.
For providing the parameters I cite Andrea "it's there where you'd expect it to be, in the Query" and that is my preference. Quoting Jody Garnett <[email protected]>: > So to bring things back on track; Christian how are you doing with > the idea being put forth - is it actually going to cause trouble for > DB2? Or is it something that DB2 needs to handle differently. > > Jody > > On 23/06/2010, at 2:26 PM, [email protected] wrote: > >> Some clarifications. DB2 makes also makes an access plan, but for one >> statement, you have different access plans for different distribution >> statistics. A simple example having a table with some indexes. If the >> table is populated with 50 rows, no index will be used, if it is >> populated with 10000 rows, the optimizer will try to use indexes. >> After large modifications for a table, you should update the statics >> (There is command called "runstats"). Afterwards, you may have >> different access plans for your statements. In the meantime, there is >> a feature called "auto runstats", so you don't need to trigger the >> command manually. And of course, there are statements performing >> better as NON PS. >> >> Spatial indexes are special in DB2. You create up to three grid sizes >> for spatial indexing. There are no statics for this type of index. >> Since I have the possibility to store geometry "neighbors" clustered, >> I can avoid many jumps from one physical block to another. This setup >> is tricky, but it performs well. >> >> Another reason is that I am always using pregeneralized features, so I >> can keep access time and data at a low level. >> >> According your BBOX example I have no ideas about DB2 performance, >> since I always use a more complicated/tuned db setup. >> >> The link is cool :-) >> http://stackoverflow.com/questions/366202/regex-for-splitting-a-string-using-space-when-not-surrounded-by-single-or-double >> >> >> >> >> >> >> >> Quoting Andrea Aime <[email protected]>: >> >>> [email protected] ha scritto: >>>> Puuh, a long mail, I will try to keep it short. >>>> >>>> 1) Each db engine has its own optimizer, DB2 as an example has a >>>> cost based optimizer (not rule based) using indices based on >>>> distribution statistics. Creating an and index for a char(1) field >>>> having values "Y" and "N" will have no sense, DB2 will never use >>>> this index. If the statistics is up to date, prepared statements >>>> outperform non ps statements clearly in most situations. But I >>>> think PostGIS and Oracle are mainstream, it is ok to focus on these >>>> systems. >>> >>> So you're saying that the DB2 optimizer does not make a plan for a >>> prepared statement, but actually recomputes it every time it receives >>> the actual values? Because it's the only way it can recognize the >>> actual bbox filter is so large that using the index is counter >>> productive. If so, that's quite cool! >>> >>>> 2) A superb solution is to parametrize the use of a VT, either to >>>> use the select statement as a prepared statement or replace the >>>> parameters markers with literals. I did this for a DB2 >>>> implementation some times ago , but for all our supported db >>>> engines this would be a heavy job. I only mention this as an idea. >>> >>> Eh yeah, indeed a heavy one. >>> >>>> 3) Using named parameters raises some problems. (For ps or non ps >>>> ). At the end of the day, you will need to parse the sql >>>> statement, because there could be a parameter marker within a >>>> string constant. During the Oracle Georaster stuff, I need to >>>> create queries which where composed of a lot of statements >>>> delimited by ";" (more like a procedure). A simple search and >>>> replace will work in 99.9 %, but not in 100%. >>> >>> I trust the user not to do too fancy things there. In GeoServer I will >>> also design the GUI so that a regexp based matcher will fill the >>> parameters table by pure inspection of the query, so if there is >>> some odd situation the user should recognize it by the code not >>> guessing properly the param names. >>> Ok, this is a bit of a long shot, but just today I found out a very cool >>> usage of regular expressions >>> that allowed me to tokenize a string so that the quoted terms >>> were recognized as a single block despite having spaces inside, see: >>> http://stackoverflow.com/questions/366202/regex-for-splitting-a-string-using-space-when-not-surrounded-by-single-or-double >>>> To bring it to an end, if we offer regexp patterns for the user and >>>> warn them about sql injection attacks , I am +0 here. (I cant >>>> give a +1 here, my stomach has not the best feeling, I fear most >>>> of the users wont even know what sql injection is, but I may be >>>> wrong here). >>> >>> No, I fear you're right, most people do not know what sql inject is >>> at all. So we have to warn people with a long red angry explanation >>> that either: >>> - they are working in a walled garden where no one will perform >>> attacks (does such heaven exist?) or >>> - if they don't think out fully the protection regexp, well, they >>> can kiss goodbye their data (if they are so smart to use a >>> account that can also write) or at least their privacy >>> (though desinign an attack that will return extra data in >>> Geotools is very hard, we just return the data we know is >>> in the feature type) >>> >>> Cheers >>> Andrea >>> >>> >>> >>> -- >>> Andrea Aime >>> OpenGeo - http://opengeo.org >>> Expert service straight from the developers. >> >> >> >> ---------------------------------------------------------------- >> This message was sent using IMP, the Internet Messaging Program. >> >> >> >> ------------------------------------------------------------------------------ >> ThinkGeek and WIRED's GeekDad team up for the Ultimate >> GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the >> lucky parental unit. See the prize list and enter to win: >> http://p.sf.net/sfu/thinkgeek-promo >> _______________________________________________ >> Geotools-devel mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/geotools-devel > > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program. ------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ Geotools-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel
