MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Parameter markers in dynamic SQL statements

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
jineshthoppil
Beginner


Joined: 30 Dec 2002
Posts: 2
Topics: 2

PostPosted: Tue Apr 19, 2005 8:10 am    Post subject: Parameter markers in dynamic SQL statements Reply with quote

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?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

PostPosted: Tue Apr 19, 2005 8:39 am    Post subject: Reply with quote

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).

Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group