V8.2 SQL stored procedures implementation

[efiryago]

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

[Mark 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.

[Serge Rielau]

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

[Mark 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?

[Serge Rielau]

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

[Knut Stolze]

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

[efiryago]

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

[Serge Rielau]

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

[Josh Tiefenbach]

> 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.

[amurchis]

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

[Sean McKeough]

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

[peteh]

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.

[Serge Rielau]

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

[efiryago]

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

[peteh]

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.

[Serge Rielau]

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

[Knut Stolze]

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