fhg
Volume 6, Number 39 -- October 25, 2006

Using Parameter Markers with SQL Functions

Published: October 25, 2006

by Michael Sansoterra

SQL is a superb data access tool in terms of ease of use and versatility. Data extraction routines that used to take hours to write in RPG or COBOL often takes minutes to write in SQL. The one draw back to dynamic SQL (as compared to data access routines written in a high level language), is that the DB2 query optimizer often takes precious time to figure out how to best execute an SQL request. To take the edge off this performance hit, dynamic SQL statements can be made "reusable" by using parameter markers so that the query optimizer can reuse the previous execution's access plan, and thus boost performance.

For clarification, host variables should not be confused with parameter markers (although they have many similarities). Host variables are used in pre-compiled SQL statements within high level language programs such as RPG, C, or COBOL. Parameter markers (as indicated by question marks within a statement) are used in dynamically prepared SQL statements in environments such as ODBC, CLI, JDBC, .NET, etc.

One issue I've encountered with parameter markers, though, is that they can be a problem when used within SQL functions. Consider this common scenario, a reusable INSERT statement with parameter markers. The following SQL inserts a social security number (DEC(9,0)), employee number (INTEGER) and resume link (DATALINK data type) into a table called EMPRESUME:

Insert Into i3/EmpResume(SSN,EmpNo,ResumeLink) 
Values(?,?,DLValue(?,'URL',?))

Because column ResumeLink is a DataLink data type, the built-in DLValue function is used to convert character data to a DataLink. (In case you're wondering, DLValue's three parameters are: data location, link type and comment.) Executing this statement from a dynamically prepared SQL environment will generate error: "SQL0418 Use of parameter marker not valid."

The problem is that SQL needs help interpreting the data type of the parameter markers that are used as a function parameter. Since many SQL functions accept multiple data types, SQL can't reliably infer a data type. Therefore, when passing parameters to a function, the developer needs to give DB2 a little more information on each parameter's type. This can be done by using the CAST function:

Insert Into i3/EmpResume(SSN,EmpNo,ResumeLink) 
Values(?,?,DLValue(Cast(? As VarChar(1024)),'URL',Cast(? As VarChar(240))))

Now this statement with a parameterized function can be dynamically prepared and subsequently re-used. Here is sample VB.NET 1.1 code to execute this SQL using the IBM DB2 for iSeries .NET managed provider:

Dim connDB2 As iDB2Connection = New iDB2Connection( _
"DataSource=AS400;UserId=x;Password=y;Naming=System")
connDB2.Open()
Dim cmdDB2 As iDB2Command = New iDB2Command("TABLE INSERT EXAMPLE")
cmdDB2.Connection = connDB2
cmdDB2.CommandText = "Insert Into i3/EmpResume(SSN,EmpNo,ResumeLink) 
   Values(?,?,DLValue(Cast(? As VarChar(1024)),
   'URL',Cast(? As VarChar(250))))"

cmdDB2.CommandType = CommandType.Text
cmdDB2.Parameters.Add("SSN", iDB2DbType.iDB2Decimal)
cmdDB2.Parameters.Add("EmpNo", iDB2DbType.iDB2Integer)
cmdDB2.Parameters.Add("ResumeLink", iDB2DbType.iDB2VarChar)
cmdDB2.Parameters.Add("LinkComment", iDB2DbType.iDB2VarChar)

cmdDB2.Parameters("SSN").Value = 324004210
cmdDB2.Parameters("EmpNo").Value = 145
cmdDB2.Parameters("ResumeLink").Value = _
"http://localhost/resumes/Resume145.doc"
cmdDB2.Parameters("LinkComment").Value = "Resume for Bart Quincy"

cmdDB2.ExecuteNonQuery()
cmdDB2.Dispose()
connDB2.Close()
connDB2.Dispose()

In addition to scalar functions, table functions often require the use of parameter markers as well. For example, say table function MajorCities accepts a state code (VARCHAR), minimum population requirement (INTEGER) and returns a list of cities matching the input parameters. A Select statement using this function with parameter markers could be coded as:

Select * 
  From Table(MajorCities(Cast(? As VarChar(256)),
                         Cast(? As Integer))) As CityList

This statement is now ready to be executed within a dynamic SQL environment.

This technique has been tested at V5R2 and will work on that release or any that follow.

Don't let the requirement for parameterized SQL functions stop you from using parameterized statements. With a little help from the CAST function to define the parameter type, parameterized SQL functions are allowed and should be used to make re-usable statements execute quicker.


Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.



Sponsored By
ADVANCED SYSTEMS CONCEPTS

SEQUEL can be used for virtually ALL
data access functions on the System i, including:

                                                · Executive Dashboards
                                                · Graphical Query & Reporting
                                                · Drill-Down Data Analysis
                                                · Multi-Platform Database Support
                                                · E-Mail Report and File Distribution
                                                · Secure Web Access

SEQUEL is the ONE solution for all your data access needs.

www.ASC-iSeries.com



Senior Technical Editor: Ted Holt
Technical Editors: Howard Arner, Joe Hertvik, Shannon O'Donnell, Kevin Vandever
Contributing Technical Editors: Joel Cochran, Wayne O. Evans, Raymond Everhart,
Bruce Guetzkow, Brian Kelly, Marc Logemann, David Morris
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

nuBridges:  Leading provider of secure FTP on the iSeries
Magic Software Enterprises:  Design IT, Control IT, Monitor IT with iBOLT
COMMON:  Join us at the Spring 2007 conference, April 29 May 3, in Anaheim, California

 


 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement