On the heels of Vijay's virtual tech briefing on Optim Development Studio 101 (you can register for the replay here, if you missed it!), I'm going to be taking you on a deep dive in one aspect of using the product for SQL Stored Procedure development (with a focus on z/OS). Marichu Scanlon from our continuous engineering team will be on board to help answer questions.
There has been a lots of interest out there in this topic because many people use stored procedures to encapsulate business logic, improve performance, and, with DB2 for z/OS v9 native SQL procedures, they can use them for reduced cost because they are zIIP-eligible.
The first of the two sessions of this briefing will be on September 24 It will last about one hour and will cover topics of creating, deploying, running and working with existing stored procedures. Within this session I will also be answering a lot of the commonly asked questions I have seen both on the forums and customer interactions, and of course I will also be taking your questions during the event itself. Keep in mind, during the September 24 session I'll only touch briefly on how debugging plays in the stored procedure development life cycle since we will be having a follow up deep dive session on October 22 that focuses on how to enable debugging in a z/OS environment. The event is free, and you can register for it here to get the details on accessing the event.
Look forward to seeing you at the tech briefing!
Managing the data lifecycle
Matching: stored_procedures X
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