By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,965 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,965 IT Pros & Developers. It's quick & easy.

V8.2 SQL stored procedures implementation

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
<ef******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
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

Comes with a built-in C compiler.
Nov 12 '05 #2

P: n/a
Mark A wrote:
<ef******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
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


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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
> > 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


What about execution time?
Nov 12 '05 #4

P: n/a
Mark A wrote:
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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
Serge Rielau wrote:
Mark A wrote:
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.


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.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
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

Nov 12 '05 #7

P: n/a
ef******@gmail.com wrote:
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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

P: n/a
> 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.

Nov 12 '05 #9

P: n/a
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.

ef******@gmail.com wrote:
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

Nov 12 '05 #10

P: n/a
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...

Serge Rielau wrote:
ef******@gmail.com wrote:
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


Nov 12 '05 #11

P: n/a
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.

Nov 12 '05 #12

P: n/a
peteh wrote:
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? 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 :-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #13

P: n/a
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

Nov 12 '05 #14

P: n/a
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.

Nov 12 '05 #15

P: n/a
ef******@gmail.com wrote:
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).

Yes, you could do that. Would be a nice apples to apples test.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #16

P: n/a
Serge Rielau wrote:
peteh wrote:
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?


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.


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.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.