What are the pros and cons to writing DB2 stored procedures in SQL versus Java?

Question

With newer versions of DB2 you can write stored procedures in SQL or you can create procedures in Java (or other languages) that can then be added to the database and called just like SQL procedures. I'm wondering what the the pros and cons of each method are. I'm specifically interested in comparing those two types of procedures, not debating procedures versus external code which I think has already been covered. Here is what I've come up with so far:

SQL:

• Better performance for basic SQL functionality

• Less verbose for simple logic, i.e. you can run SQL directly

• No extra compile step - just create procedure ...

Java:

• More structured and full-featured code (classes, objects, reuse, libraries)

• Better resources for help both in terms of programmers and documentation

Any other thoughts?

Answer 1

Not just Java but any procedural language: procedural, that's the key.

DB2 is a relational algebra, not a programming language. If you need to do something complex, it's better to use a procedural language than try to bend SQL into something it's not meant to be.

We actually use REXX for our DB2/z work but I've seen guys here use bash on the mainframe for quick prototyping. Some who only use SQL create all these horrible temporary tables for storing things that are best kept in structures and away from the DB.

My thoughts are that complexity is the only differentiating factor between using SQL directly in a stored procedure and a procedural approach. We have two rules of thumb when making these decisions.

1/ If a unit of work requires more than two trips to the database, we make it a stored procedure.

2/ If a stored procedures creates a temporary table, then we use a procedural language to do it.

Answer 2

You may already have found this but just in case and for any others that swing by here there's an IBM Redbook on SProcs and the changes for DB2 v9 available at DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond which discusses the available options and their relative merits.

Answer 3

The advantage with the SQL stored procedures is they are portable to other Db2 UDb with minimal or no changes. But Db2 external procedures would be a better choice as you can do more with a procedural language than sql alone. I would say cobol would be a better fit for DB2 external stored procedures than Java for the below reasons. 1). You would be able to reuse some code from existing programs. Converting a cobol sub program to a stored procedure or stored procedure to a cobol sub program is very easy to accomplish. 2). You would be able to use existing cobol development team who has functional knowledge with the system.