SFI's Blog | Ask the Mainframe Software Expert
  Please post your questions here. We will reply promptly.
Most recent questions…
 
 
 
 
 
 
  CMS 3.0 - Service Management for Large Enterprises
  StreamFoundry Services - How we deliver value to Mainframe environments
  StreamFoundry Software Support Options - Support that your Mainframe environment demands
  StreamFoundry Resource Matrix - IBM Mainframe Software Expertise on-demand
  Check out SFI's tapeless VM/Encrypt solution
 
 
 
Home » Blogs » Questions
  User ID:     Password:    
New User? Click Here to Register.  
 
    Search Blogs/Questions:    
Click Here to Post Your Questions.  
Questions under category : DB2 » SQL
 
 
Q : Does the way I code dynamic SQL calls within an application matter to DB2?
Posted : Donna on   February 16, 2009 at 11:49 am
 
 
Replies to the question.
 
  More / Hide
 

Yes it does and it is important that you use parameter makers.  Dynamic SQL Cache became available in DB2 V7.1.  Unlike static SQL which is externalized to a DBRM library, dynamic SQL must be prepared by what is known as a mini-bind prior to execution.  The mini-bind process may adds overhead to the SQL call with DB2 parsing, validating, and selecting the optimal path for the call.  In order to take full advantage of this feature it is important not to flood DB2 with new SQL calls as a result of coding literals within the SQL call.  Using parameter markers add an additional level of complexity to programming, but the performance benefits significantly outweigh the effort.

class=MsoNormal> 

class=MsoNormal>An example:

class=MsoNormal> 

class=MsoNormal>SELECT LAST_NAME, FIRST_NAME

  FROM DSN910.EMPLOYEE

WHERE EMPLOYEE_ID = ?

 

After statement preparation and before execution, parameter #1 would be set to the appropriate look-up value using the appropriate method and syntax for the programming language.

 
 
Posted : Donna on  February 16, 2009 at 11:55 am

Yes it does and it is important that you use parameter makers.  Dynamic SQL Cache became available in DB2 V7.1.  Unlike static SQL which is externalized to a DBRM library, dynamic SQL must be prepared by what is known as a mini-bind prior to execution.  The mini-bind process may adds overhead to the SQL call with DB2 parsing, validating, and selecting the optimal path for the call.  In order to take full advantage of this feature it is important not to flood DB2 with new SQL calls as a result of coding literals within the SQL call.  Using parameter markers add an additional level of complexity to programming, but the performance benefits significantly outweigh the effort.

class=MsoNormal> 

class=MsoNormal>An example:

class=MsoNormal> 

class=MsoNormal>SELECT LAST_NAME, FIRST_NAME

  FROM DSN910.EMPLOYEE

WHERE EMPLOYEE_ID = ?

 

After statement preparation and before execution, parameter #1 would be set to the appropriate look-up value using the appropriate method and syntax for the programming language.

 
 
 
Post a Reply.
Comments :
Bold Italics Clear  
    Note: Please use shift+enter to insert new line.
 
 
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
The System z Health Check
 
Subscribe to the SFI Newsletter
Enter Your E-Mail:
 
 
 
  © 2005-15 StreamFoundry Inc. | E-Mail : inquiries@streamfoundry.com | Call us: 617.877.4728