Hi and happy end of February; today is 2/28/2024 - One more day left - happy leap year.
As you know if you read my previous "episode" - I am working on a niche area of Db2 - i.e. Db2 Stored Procedures (SPs) and, something similar but with a different name, Db2 SQL Procedures (SQLPs) - definitions to follow in subsequent BLOG episodes. This started when I presented at IDUG EMEA 2022 in Edinburgh, Scotland. We were discussing Stored Procedure Address Spaces, why they are important to know and how they evolved. In that presentation, I simply highlighted their creation and high level functionality. For example the initial SP address space was SPAS and then it used the Workload Manager ones in which SPs were executed.
Attendees asked more detail questions and my presentation was not set up to answer them - e.g. "what's the difference between Native and External SPs?". "what's an SQL Procedure, both External and Native?" Eye opening questions, to be sure.
In this blog and also in my new presentation that I am preparing for IDUG (hoping it gets accepted), I plan on explaining the differences and giving the evolution of these 2 "Procedure" types (SPs & SQLPs).
One of the initial questions posed by many Db2 developers and DBAs, is how well do SPs run compared to applications that simply do Db2 DML. The following has been published by Robert Catterall, IBM Senior Consulting Db2 for z/OS Specialist in his Tridex presentation:
Can stored procedures be over-used in a Db2 for z/OS environment?
Over-use is possible – depends on priorities
§If maximum performance is a priority, stored procedures could be over-used
§Comparing performance of SQL DML statements issued by client programs versus packaging same statements in server-side stored procedures:
- Very similar if transaction involves execution of 3-4 SQL DML statements
- If > 4 SQL DML statements/tran, stored procedures will probably provide better performance
- If only 1 or 2 SQL DML statements/tran, performance probably best with client-issued SQL
Using stored procedures, even for transactions that involve execution of only 1 or 2 SQL DML statements could be considered over-use from a performance perspective:
- That said, performance difference may not be very substantial
- If stored procedures will be used even for the simplest of transactions, use of native SQL procedures becomes more important (avoid thread task switch overhead of external stored procedures)
- Note that the performance benefit of high-performance DBATs increases as in-Db2 CPU time per transaction decreases - see DBAT definition below.
On the other hand…
§ If stored procedures are used to ensure that all ”table-touching” SQL issued for an application is static (perhaps for data-security reasons), it may be that stored procedures will never be thought of as being over-used
- May not be a viable alternative for ensuring that data access is via static SQL
- Yes, client programs could issue static SQL DML statements (for example: SQLJ in Java programs), but client-side developers often prefer to invoke stored procedures via interface such as JDBC versus using something like SQLJ
Again, the more that stored procedures are used…
- …the more important it is to make them native SQL procedures when possible (discussion to follow in the next "episode").
- …the more important it is to get high-performance DBAT functionality by binding high-use stored procedure packages with RELEASE(DEALLOCATE)
DBATS:
Distributed database access threads (sometimes called DBATs) are threads that are connected through a network to access data at a Db2 server on behalf distributed requesting systems. Database access threads are created in the following situations: When new connections are accepted from remote requesters.
So Db2 SPs were meant to be more efficient in their performance. Here is a consensus observation:
What is not a benefit of using stored procedures?
The only disadvantage of a Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server. (Oct 9, 2018).
However, we will next review performance of SPs, distinguishing between Native and External. Then we will look at the introduction of Db2 SQL Procedures (SQLPs) and give some insight into their usage and performance.
WATCH THIS SPACE.