 |
 | 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 2005V8.2 SQL stored procedures implementation
Since V8.2 does not require a C compiler to build SQL stored procedures, I am just wonderring how they are now implemented internaly as opposed to C embedded SQL before V8.2, so, basicaly, what happens by CREATE PROCEDURE LANGUAGE SQL statement (I noticed no C files get generated in $INSTHOME/sqllib/function/routine/sqlproc/database/schema directory as it was before)? Thanks, -Eugene > Since V8.2 does not require a C compiler to build SQL stored > procedures, I am just wonderring how they are now implemented internaly [quoted text clipped - 5 lines] > Thanks, > -Eugene Comes with a built-in C compiler. >>Since V8.2 does not require a C compiler to build SQL stored >>procedures, I am just wonderring how they are now implemented internaly [quoted text clipped - 7 lines] > > Comes with a built-in C compiler. We thought about it, but having to support a C-compiler on non IBM, non Linux platforms did not sound like a good idea. When you execute your first SQL Procedure after db2start you will find a new process called: db2pvm This is the "PSM Virtual Machine". When a CREATE PROCEDURE gets parsed the procedural logic gets separated from the core SQL Statements. The procedural part gets turned into bytecode while the SQL statements get bound into a package and stored in SYSCAT.PACKAGES. The bytecode gets stored in the internal description in SYSCAT.ROUTINES. The result is fairly close to what you would get with SQLJ: a VM driving statically compiled SQL. There are two advantages to this: * No more worries about OS, compiler specific PMRs * CREATE PROCEDURE takes a fraction of the time is used to in V8. Cheers Serge  SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > > Comes with a built-in C compiler. > We thought about it, but having to support a C-compiler on non IBM, non [quoted text clipped - 15 lines] > Cheers > Serge What about execution time? >>The result is fairly close to what you would get with SQLJ: a VM driving >>statically compiled SQL. >>There are two advantages to this: >>* No more worries about OS, compiler specific PMRs >>* CREATE PROCEDURE takes a fraction of the time is used to in V8. > What about execution time? I knew you'd ask that. :-) The goal of Stinger was to get rid of the C-Compiler and not regress performance. It's hard to give hard numbers, but anecdotal evidence points to a low double digit improvement on average. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab >>>The result is fairly close to what you would get with SQLJ: a VM driving >>>statically compiled SQL. [quoted text clipped - 7 lines] > It's hard to give hard numbers, but anecdotal evidence points to a low > double digit improvement on average. Here is some other evidence: One of our procedures went from an execution time of 36 hours down to 15 hours just by switching from V8.1 to V8.2. ;-) Of course, you can't simply take those numbers and apply it to everything else. SignatureKnut Stolze Information Integration IBM Germany / University of Jena BTW, Oracle9i introduced so called "Native PL/SQL" where you have an option to compile your PL/SQL stored procs, which are an interpreted code AFAIK, into server side C libraries to improve performance. Would we expect the same kind of DB2 SQL sroted procs compilation option available in new DB2 releases going forward? Another "real-life" issue: what's happens to the existing V8.1 SQL stored procs during the instance upgrade to V8.2, are they automaticaly converted to the internal modules from C? Thanks, -Eugene > BTW, Oracle9i introduced so called "Native PL/SQL" where you have an > option to compile your PL/SQL stored procs, which are an interpreted > code AFAIK, into server side C libraries to improve performance. Would > we expect the same kind of DB2 SQL sroted procs compilation option > available in new DB2 releases going forward? DB2 has gone the exact opposite direction. Ironically we call the intrepreted version "native" ;-) FWIW the code has been removed.. there is no secret switch to cross compile to C. I'm sure Oracle had its reason to go one way, IBM had it's to go the other. > Another "real-life" issue: what's happens to the existing V8.1 SQL > stored procs during the instance upgrade to V8.2, are they automaticaly > converted to the internal modules from C? No. To DB2 a V8.1 SQL PRocedure is an unfenced C procedure. Even a rebind will not change that. When you drop and recreate the proc it will become "native" > Thanks, > -Eugene SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab Similarly, a v7 sql stored procedure is still run as a fenced c stored procedure (we didn't have engine recursion, or threadsafe psm code in v7) so if you have a lot of sps that were created in that timeline you probably want to consider dropping and recreating them... >> BTW, Oracle9i introduced so called "Native PL/SQL" where you have an >> option to compile your PL/SQL stored procs, which are an interpreted [quoted text clipped - 18 lines] >> Thanks, >> -Eugene This thread started me thinking (rarely a good idea): Does this new "internal compilation" model imply in any way that C stored procs are undesirable for the long haul? We're starting to build a library of them - mostly related to utility API interfaces like LOAD, REORG/RUNSTATS, etc that have no other interfaces. Will these become obsolete or unsupported over time? I've noticed that IBM is beginning to surface things in table functions (like SNAPSHOT, etc) that used to be available only through C APIs. > This thread started me thinking (rarely a good idea): > [quoted text clipped - 3 lines] > REORG/RUNSTATS, etc that have no other interfaces. Will these become > obsolete or unsupported over time? Absolutely not. Note that some other vendors are just getting into external procedures. There is nothing short of assembler to beat the performance of C code. Also it's a standard language to write all sorts of wrappers for APIs (not only DB2 APIs). It is probably fair to say that your standard SMB customer does not have the skills to write C Procedures. So you may, over time, see Java or SQL Procedures for the app side and C Procedures for the interfaces. > I've noticed that IBM is beginning to surface things in table functions > (like SNAPSHOT, etc) that used to be available only through C APIs. Yes, thought is that customers shouldn't keep repeating the same work. The advantage of SQL interfaces is that any client interface talking to DB2 knows SQL. Cheers Serge PS: Don't let this discourage you from publishing your C procs :-) SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab Oh cool!... as far as the upgrade from V8.1 to V8.2 does not touch the old/existing V8.1 SQL stored, i.e. implemented as external C ones, procs in the database being upgraded, it seems possible to create the same stored procs, i.e.of exactly the same SQL code, but just with different names in V8.2 right after the upgrade complete and then compare the performance from the same code run as C vs. the internal implementation. If someone has done that it would be very interesting to hear the results of that testing (I would love to do that on my own but unfortunately dont have a relevant environment). Regards, -Eugene > Oh cool!... as far as the upgrade from V8.1 to V8.2 does not touch the > old/existing V8.1 SQL stored, i.e. implemented as external C ones, [quoted text clipped - 5 lines] > to hear the results of that testing (I would love to do that on my own > but unfortunately dont have a relevant environment). Yes, you could do that. Would be a nice apples to apples test. SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab Thanks Serge; As usual, I appreciate your insights. peteh p.s. Knut has been a huge help in supplementing the sample library in this arena, so I owe him a "thank you" as well. >> This thread started me thinking (rarely a good idea): >> [quoted text clipped - 7 lines] > external procedures. There is nothing short of assembler to beat the > performance of C code. Right. Whether to choose SQL/PL or C code really depends on what you want to do in the procedure (and how important performance is). If you have lots and lots of communication with the database engine, then SQL/PL might be a better choice that embedded SQL or CLI in C code. If you do a bunch of stuff that doesn't really touch the database so much, C is probably a better choice. SignatureKnut Stolze Information Integration IBM Germany / University of Jena Serge can probably answer this better than I can, but: 1) We actually went the other way to get improved performance. Prior to V82, SQL procedures WERE compiled into libraries on the server. As Knut's previous update states, though, he's getting better performance with the NEW method than with the old. 2) They'll still remain in library format, and will still run just fine. They are flagged slightly differently internally, so an SQL SP compiled prior to V82 will go through a slightly different codepath than a V82+ SQL procedure. > BTW, Oracle9i introduced so called "Native PL/SQL" where you have an > option to compile your PL/SQL stored procs, which are an interpreted [quoted text clipped - 8 lines] > Thanks, > -Eugene > When you execute your first SQL Procedure after db2start you will find a > new process called: db2pvm Not true. No new processes are created for native SQL procedures. All the user will see will be the typical db2agent EDU associated with the database. |
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
|
©2008 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.