Posted: Tue Apr 19, 2005 8:10 am Post subject: Parameter markers in dynamic SQL statements
In most of the DB2 manuals, it is recommended to use parameter markers in dynamic SQL statements, instead of coding literals each time.
If I change my COBOL applications to use parameter markers, will it boost the overall performance of the application, since now there are chances that the rebinding of the mini-plan for the statement could be avoided.
Also, should the current EDM and buffer pools size be increased to effectively use dynamic statement caching?
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Tue Apr 19, 2005 8:39 am Post subject:
jineshthoppil,
Parameter Markers can provide huge performance benefits when used with Non-SELECT dynamic SQL. The use of Parameter markers eliminate the overhead of a prepare for each sql.
Consider a program that executes SQL statements based on an input file. A loop in the program reads a key value from the input file and issues a DELETE, INSERT, or UPDATE for the specified key. Without the parameter markers you need to PREPARE each execution of each SQL statement inside the loop.
Using non-SELECT dynamic SQL, however, you can separate PREPARE and EXECUTE, isolating PREPARE outside the loop. The key value that provides the condition for the execution of the SQL statements can be substituted using a host variable and a parameter marker. If thousands of SQL statements must be executed, you can avoid having thousands of PREPAREs by using this technique. This method greatly reduces overhead and runtime and increases the efficient use of system resources.
A prepared statement can contain more than one parameter marker. Use as many as necessary to ease development.Make sure that repeated statements use parameter markers instead of literals.
I don't think you need to change the Buffer pools for dynamic statement caching.At any given point of time , Buffer pools should make use of about 75% (OLTP) or 50% (OLAP) of available memory (Buffer Pools).
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum