PL/SQL in DB2

[Mladen Gogala]

I am a former Oracle DBA and I am very comfortable with PL/SQL. I wonder
whether there would be any improvement if I used the DB2 native language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

[Mladen Gogala]

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I wonder
whether there would be any improvement if I used the DB2 native language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

BTW, I enabled Oracle compatibility using this:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

[l***@invalid.lan]

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I wonder
whether there would be any improvement if I used the DB2 native language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

Oracle, or DB2, are no programming languages.

quoot from: https://en.wikipedia.org/wiki/PL/SQL
PL/SQL (Procedural Language/Structured Query Language) is Oracle
Corporation's procedural extension for SQL and the Oracle relational
database. PL/SQL is available in Oracle Database (since version 6 -
stored pl/sql procedures/functions/packages/triggers since version 7),
TimesTen in-memory database (since version 11.2.1),
*and IBM DB2 (since version 9.7).*

[Mladen Gogala]

Post by l***@invalid.lan

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I
wonder whether there would be any improvement if I used the DB2 native
language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

Oracle, or DB2, are no programming languages.
quoot from: https://en.wikipedia.org/wiki/PL/SQL PL/SQL (Procedural
Language/Structured Query Language) is Oracle Corporation's procedural
extension for SQL and the Oracle relational database. PL/SQL is
available in Oracle Database (since version 6 - stored pl/sql
procedures/functions/packages/triggers since version 7),
TimesTen in-memory database (since version 11.2.1),
*and IBM DB2 (since version 9.7).*

Your reply is nitpicking about the notion of a programming language, but
it doesn't respond to my question: is there a performance price to pay
for programming functions, triggers and procedures in PL/SQL instead of
the native DB2 "procedural extensions". And yes, I know that DB2 supports
Oracle dialect. I have even written how to activate that compatibility in
my post. That is not the question. The question is whether I'm paying a
price for using the compatibility or not.

[Jerry Stuckle]

Post by Mladen Gogala

Post by l***@invalid.lan

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I
wonder whether there would be any improvement if I used the DB2 native
language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

Oracle, or DB2, are no programming languages.
quoot from: https://en.wikipedia.org/wiki/PL/SQL PL/SQL (Procedural
Language/Structured Query Language) is Oracle Corporation's procedural
extension for SQL and the Oracle relational database. PL/SQL is
available in Oracle Database (since version 6 - stored pl/sql
procedures/functions/packages/triggers since version 7),
TimesTen in-memory database (since version 11.2.1),
*and IBM DB2 (since version 9.7).*

Your reply is nitpicking about the notion of a programming language, but
it doesn't respond to my question: is there a performance price to pay
for programming functions, triggers and procedures in PL/SQL instead of
the native DB2 "procedural extensions". And yes, I know that DB2 supports
Oracle dialect. I have even written how to activate that compatibility in
my post. That is not the question. The question is whether I'm paying a
price for using the compatibility or not.

Do you have a performance problem with PL/SQL? Or are you prematurely
optimizing?

There are many other things which affect performance besides the
language you are using. Try it and find out. If you have a performance
problem, find the cause and fix it. Chances are it will not be in your
choice of PL/SQL vs. DB2's SQL PL.

[l***@invalid.lan]

Post by Mladen Gogala

Post by l***@invalid.lan

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I
wonder whether there would be any improvement if I used the DB2 native
language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

Oracle, or DB2, are no programming languages.
quoot from: https://en.wikipedia.org/wiki/PL/SQL PL/SQL (Procedural
Language/Structured Query Language) is Oracle Corporation's procedural
extension for SQL and the Oracle relational database. PL/SQL is
available in Oracle Database (since version 6 - stored pl/sql
procedures/functions/packages/triggers since version 7),
TimesTen in-memory database (since version 11.2.1),
*and IBM DB2 (since version 9.7).*

Your reply is nitpicking about the notion of a programming language, but
it doesn't respond to my question: is there a performance price to pay
for programming functions, triggers and procedures in PL/SQL instead of
the native DB2 "procedural extensions". And yes, I know that DB2 supports
Oracle dialect. I have even written how to activate that compatibility in
my post. That is not the question. The question is whether I'm paying a
price for using the compatibility or not.

There's always `a` price someone has to pay for compatibility.

But in this case, because IBM/DB2 doens not have a product whuch is
comming close to Oracle/PLSQL (to my knowledge), this price should be
minimal.

Always keep in mind that someting might work on DB2, and will not work
on Oracle, or the other way around ...

If you are lucky, and compatibility is GOOD, you will never hit that point.

I do not have enough exprience to claim that you will see (or not) any
incompatabilities ...

[j***@calculo-sa.es]

Post by l***@invalid.lan

Post by Mladen Gogala

Post by l***@invalid.lan

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I
wonder whether there would be any improvement if I used the DB2 native
language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.

Oracle, or DB2, are no programming languages.
quoot from: https://en.wikipedia.org/wiki/PL/SQL PL/SQL (Procedural
Language/Structured Query Language) is Oracle Corporation's procedural
extension for SQL and the Oracle relational database. PL/SQL is
available in Oracle Database (since version 6 - stored pl/sql
procedures/functions/packages/triggers since version 7),
TimesTen in-memory database (since version 11.2.1),
*and IBM DB2 (since version 9.7).*

Your reply is nitpicking about the notion of a programming language, but
it doesn't respond to my question: is there a performance price to pay
for programming functions, triggers and procedures in PL/SQL instead of
the native DB2 "procedural extensions". And yes, I know that DB2 supports
Oracle dialect. I have even written how to activate that compatibility in
my post. That is not the question. The question is whether I'm paying a
price for using the compatibility or not.

There's always `a` price someone has to pay for compatibility.
But in this case, because IBM/DB2 doens not have a product whuch is
comming close to Oracle/PLSQL (to my knowledge), this price should be
minimal.
Always keep in mind that someting might work on DB2, and will not work
on Oracle, or the other way around ...
If you are lucky, and compatibility is GOOD, you will never hit that point.
I do not have enough exprience to claim that you will see (or not) any
incompatabilities ...

Yes, there are quite some differences in compatibility between oracle and DB2. Most of them are workaroundable and others aren't and require a code reprogramming.

on the other hand, concerning to performance in PL/SQL versus SQL/PL, once we did a test for a complex recursive query using both type of "languages" and the result was the same in terms of time, so we kept the PL/SQL code.

That was DB2 9.7 FP 4-5 and on. No wonder DB2 is now much more close to oracle's behavior and many of the issues we found are solved now. I don't have big hopes as to perf improvements.

Hope this helps. Best regards.

[l***@gmail.com]

Post by Mladen Gogala
I am a former Oracle DBA and I am very comfortable with PL/SQL. I wonder
whether there would be any improvement if I used the DB2 native language?
The two seem to be very similar, but I wonder what would I achieve by
learning another programming language? I was unable to find anything
definitive on the Interner.
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

DB2 SQL/PL is based on a procedural SQL standard, ISO SQL/PSM (and that could be thought of as an advantage) ... however, I don't believe this standard ever really "took off". From what I was aware of when selling DB2 for IBM, the PL/SQL within DB2 is NOT an emulator, but is native code within DB2 ... and the performance difference is quite small. And IBM has made many changes in the past 5 years or so to increase the compatibility with Oracle even moreso. So I'm not sure I can think of a distinct advantage either way.

Larry E.

[j***@calculo-sa.es]

Yes, I agree.
Performance makes no big difference between PL/SQL and SQL/PL.
On the other hand the issues we found from 2009 to 2012 must have been corrected by now, at least most of them.
I should test it some day if find the time.

[v***@gmail.com]

Post by j***@calculo-sa.es
Yes, I agree.
Performance makes no big difference between PL/SQL and SQL/PL.
On the other hand the issues we found from 2009 to 2012 must have been corrected by now, at least most of them.
I should test it some day if find the time.

I recommend you to rewrite PL/SQL functions to SQL/PE equivalent with BEGIN ATOMIC ... END. DB2 can inline that functions with BEGIN ATOMIC ... END and functions without BEGIN ... END into another expression.

As far as I know:

Something like
"CREATE OR REPLACE FUNCTION ...
RETURNS ...
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN ..."
the best.

Something like
"CREATE OR REPLACE FUNCTION ...
RETURNS ...
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
RETURN ...
END"
the good.

Something like
"CREATE OR REPLACE FUNCTION ...
RETURNS ...
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN
RETURN ...
END"
the worst.

The difference between these options is great. But DB2 PL/SQL has only third option.

After that, you can consider rewriting PL/SQL procedures into SQL/PL function equivalent. And don't forget about triggers. DB2 can inline SQL/PL triggers too... if conditions are right (BEGIN ATOMIC...EMD or without BEGIN...END).