 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / March 2005Tips and tricks for fast SQL procedures
Shameless self promotion ;-) http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0501rielau/ I've now reached the end of my "mini-series" around SQL Procedures. If anyone has ideas on SQL related topics that need illuination I'm all ears. MERGE has come up a couple times so far.... Anything else? Cheers Serge  SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > Shameless self promotion ;-) http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0501rielau/ > I've now reached the end of my "mini-series" around SQL Procedures. > If anyone has ideas on SQL related topics that need illuination I'm all [quoted text clipped - 4 lines] > Cheers > Serge Hi Serge, GROUP BY ... HAVING ? SQL92 clauses SQL99 clauses what is the best to do in DB2 to treat Transact SQL like (Sybase ...) select type, title_id, avg(price), avg(advance) from titles group by type select type, title_id from titles group by type having date=max(date) Regards, JM >>Shameless self promotion ;-) > [quoted text clipped - 15 lines] > SQL92 clauses > SQL99 clauses With respect to GROUP BY/HAVING? or general. There are whole books about SQL-92 and SQL-99 ;-) > what is the best to do in DB2 to treat Transact SQL like (Sybase ...) Not clear what you mean? > select type, title_id, avg(price), avg(advance) from titles group by type > select type, title_id from titles group by type having date=max(date) Neither here.... other than that you will get an error on title_id... Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > >>Shameless self promotion ;-) http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0501rielau/ > >>I've now reached the end of my "mini-series" around SQL Procedures. > >>If anyone has ideas on SQL related topics that need illuination I'm all [quoted text clipped - 13 lines] > With respect to GROUP BY/HAVING? or general. > There are whole books about SQL-92 and SQL-99 ;-) ==> With respect to GROUP BY/HAVING ;-) > > what is the best to do in DB2 to treat Transact SQL like (Sybase ...) > Not clear what you mean? ==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is the best way to translate into DB2 ? > > select type, title_id, avg(price), avg(advance) from titles group by type > > select type, title_id from titles group by type having date=max(date) > Neither here.... other than that you will get an error on title_id... ==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and says title_id is missing. > Cheers > Serge Thanks, Jean-Marc > > >>Shameless self promotion ;-) http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0501rielau/ > > >>I've now reached the end of my "mini-series" around SQL Procedures. > > >>If anyone has ideas on SQL related topics that need illuination I'm all [quoted text clipped - 29 lines] > > Cheers > > Serge >>>GROUP BY ... HAVING ? >>> [quoted text clipped - 23 lines] > ==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and says > title_id is missing. OK, I'm hopelessly lost. Can you explain what you expect title_id to produce in this case? DB2 supports GROUP BY and HAVING so I'm simply drawing a complete blank here. The only request I ever came across is to allow column numbers in GROUP BY (as supported in ORDER BY), that's an IDS thing. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > >>>GROUP BY ... HAVING ? > >>> [quoted text clipped - 31 lines] > Cheers > Serge Hi Serge, Apparently, Sybase is the only one to tolerate extra columns in the select list, that do not participate in the GROUP BY clause (Transac SQL extension). In my sample, I would retrieve each row that have the max(date) by type, and extract other information from those rows such as title_id. That would translate into DB2 something like - 1 possibility: select type, title_id from titles where (type, date) in (select type, max(date) from titles group by type). Regards, Jean-Marc >>>>>GROUP BY ... HAVING ? >>>>> [quoted text clipped - 54 lines] > > Jean-Marc Ah... the SQL Standard provides a set of OLAP functions which can do that. Your example makes a lot more sense with MAX than with AVG though. Not sure what TYPE_ID should be chosen.... In DB2, if you just want one row back you can use ORDER BY with FETCH FIRST 1 ROW for MAX()/MIN(). For more general stuff OLAP does the job: SELECT x, y, z FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY X ORDER BY Y) AS rn, x, y, z FROM T) AS S WHERE rn = 1 The standard also provides functions for LAST and FIRST. Oracle has added a lot of that support AFAIK. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > >>>>>GROUP BY ... HAVING ? > >>>>> [quoted text clipped - 72 lines] > Cheers > Serge Thanks Serge, JM very interesting, thank you Serge. Just wanted to add one comment on "The cache also remembers previously executed statements, so that after the first compilation of the SQL statement. Subsequent invocations will simply keep executing the same execution plan." I strongly believe that executing one and the same execution plan is not always efficient. For data skew situations, range queries and very complex queries, we sometimes are better off with dynamic SQL (but only for large tables). What do you think? > very interesting, thank you Serge. > [quoted text clipped - 7 lines] > complex queries, we sometimes are better off with dynamic SQL (but only > for large tables). What do you think? Absolutely. For that purpose DB2 V8.2 supports REOPT(ALWAYS). It will, recompile a statement every time. The next "crank at the handle" so to speak is REOPT(AUTOMATIC). In this case the optimizer knows for which value ranges the plan is good and will kick of recompilation if the safe-zone is left. This capability is not yet in the product :-) REOPT(ONCE), which is supported, waits for the first set of values, assuming that it is representative. E.g. when you have a query to find executives above a certain salary REOPT(ONCE) is quite sufficient for the optimizer to figure out that salary is going to be highly filtering. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > > very interesting, thank you Serge. > > [quoted text clipped - 25 lines] > DB2 SQL Compiler Development > IBM Toronto Lab as usual, the download from *ibm*.com won't finish (why is that, anyway??) question: i've read through most of the Yip, et al book, but i don't find any specification of the scope of the dynamic SQL Cache: user, connection, schema, instance?? thanks, robert > as usual, the download from *ibm*.com won't finish (why is that, > anyway??) I've never had that problem. I used to have trouble with download accelerators downloading from the IBM site (corrupt files on unzip). > i've read through most of the Yip, et al book, but i don't find any > specification of the scope of the dynamic SQL Cache: user, connection, > schema, instance?? The cache is DB wide. Statements are matched based on their text, usage of certain special registers (like PATH, CURRENT SCHEMA) etc. So it is common for many connections to share the same cached statement. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab i'm home now. tried to read your article. came up immediately. must be something with the CubeLand net. found the 2nd edition at Borders. grabbed it: covers os/360 <G> version. that's a big help. some guy named Serge is prominent in the preface as being helpful in bringing the book out. back to the question: by DB wide, on mainframe, that means instance i suppose. thanks, robert > i'm home now. tried to read your article. came up immediately. must > be something with the CubeLand net. [quoted text clipped - 4 lines] > some guy named Serge is prominent in the preface as being helpful in > bringing the book out. Another way of saying I'm loud ;-) > back to the question: by DB wide, on mainframe, that means instance i > suppose. Uh, oh... I dare not comment on internal optimizations in DB2 zOS land. It is reasonable to expect that Db2 for zOS behaves the same. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.