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.