Decisions, decisions.. pureQuery, SQLJ, or stored procedures for static access
IBM_Optim 27000269HS Visits (3932)
A question that keeps coming up again and again.... and again is:
"Should we use stored procedures, or SQLJ or pureQuery for z/OS database access? "
The answer, as usual, is - "It depends."
Let me try to give some perspective on the question about stored procedures, SQLJ, and pureQuery.
Stored procedures are primarily meant to reduce network traffic and encapsulate a series of SQL operations with logic, but have also been used heavily to lock down the SQL issued from applications - similar to static SQL.
One thing to consider is the additional overhead one would incur for invoking stored procedures, This cost can be pretty high from what I understand. If there are a bunch of statements grouped together, along with business logic, or if results from one statement need to be used as input to another, then stored procedures would be more appropriate. If you only have one or very few SQL statements, where there is no logical need to group them together, then the overhead of the stored procedure call becomes more of a burden on the pathlength. There are more efficient ways to lock down SQL statements.
pureQuery and SQLJ provide options to lock down statements (using static SQL) without having to incur the additional stored procedure overhead.
The other consideration is cost. With pureQuery and SQLJ, the workload is eligible to run on zIIP or zAAP processors. Stored procedures will run on the general purpose CPU, unless they are native SQL procedures, which became available in V9 of DB2 for z/OS.
So, in cases where you have considered and ruled out stored procedures, you now have to think about SQLJ vs. pureQuery. Ok, so pureQuery is not free. However, from a total cost of ownership point of view, there are advantages to pureQuery. For example, Data Studio Developer has some slick tooling for pureQuery. The code generation, content assist etc., which are popular with developers, are designed around the pureQuery API. Also, if you have existing JDBC applications that you want to optimize and/or provide additional security for, you can get to static SQL without having to change any code using the pureQuery Runtime and Data Studio Developer tools.
Deployment is also significantly simplified with pureQuery. With SQLJ, emergency changes to the application and redeployment were challenging because both developers and DBAs need to get involved in altering the SQL, rebinding, and redeploying the application. With pureQuery there is no back and forth between development and DBAs. The DBA can edit the package directly and the tooling ensures that the modified SQL provides equivalent results.
My colleague Holly Hayes came up with this summary, which I like.
Achieving static SQL execution to increase z/OS capacity, lock down access path, enhance security:
Characteristics of COBOL stored procedures
Characteristics of SQLJ
Characteristics of pureQuery
Added license costs, but...
-- Vijay Bommireddipalli