Performance of Static DB2 Stored Procedure versus Dynamic

Products

• Aion Business Rules Expert

Issue/Introduction

Are there performance difference between DB2 Stored Procedures versus Dynamic ones? This summarizes the reasons for choosing one or the other.

 

Environment

Release: 11.0

Resolution

When designing AION client/server applications (PC applications as the client) consideration should be taken that the AION BRE product is using ODBC 3 or DB2 ODBC which is a callable interface to DB2 to get information from DB2 subsystem into the application. ODBC, as any other callable interface, does not support static SQL.

That means from DB2 point of view, all AION applications are using dynamic SQL according to definition of Static SQL given by IBM (see below):

'An SQL statement or SQL statement string is considered static if it is "hard-coded" in the program. It does not matter if the statement text is stored in program variable or coded on a DECLARE CURSOR statement; if you can see the statement in the program, it is considered static from the programming point of view. The result is that you have to go through the program deployment cycle (compile and link-edit) every time any of those "hard-coded" SQL statements change.'

From AION BRE point of view the Static DB2 Stored Procedure (further Static Procedure) means the procedure was introduced to AION application deployed on mainframe via AION SQL Editor - Query Properties - by checking Use Static SQL that stores all DB2 Stored Procedure's meta data in an AION application such as procedure name, input/output parameters, parameter data types etc. In contrast, PC applications use dynamic SQL no matter what Static flag is set to.

Therefore, Static Procedures has some advantages:

1. The Static Procedure's meta data information allows the application to save a lot of time during the procedure call.
2. The developer does not have to worry about binding the procedure output to the application.
3. There is no need to use special functions to link parameter markers to application variables.

Choosing between Static and Dynamic Procedures:

The final decision can be made by estimating application characteristics such as flexibility, complexity, and performance.

Dynamic Procedures must be prepared before they are executed. The resources consumed for this step have the potential to make Dynamic Procedures perform worse than Static ones depending on external conditions. Using Static Procedures may save you from 5% to 20% of the CPU time.