Dynamic Embedded SQL

This section examines how to dynamically prepare SQL statements in a program module. Coding embedded dynamic SQL statements can be complex because a special data structure, known as an SQLDA, must be used to interpret the SQL statement.

If, in the development phase of an application, every SQL statement is known, then static embedded SQL statements could be used for the entire application. When a fully defined SQL statement is embedded in an application, it is said to be statically prepared. The meaning behind static preparation will become clear when we discuss how to precompile and bind applications in the latter part of this chapter. For now, just note that during the static preparation phase, the QEP of the SQL statement is stored inside a DB2 component known as a package, thereby avoiding this cost at application run time.

If, there are unknown elements when the statement is prepared, the statement requires dynamic preparation. Table 9.3 provides a checklist of conditions when a statement requires dynamic preparation.

For example, consider an ad-hoc reporting application that allows the end-user to select specific column names, specify conditions, and vary how the results are ordered in the report. You can generalize the SQL statement for this report using the following template:

SELECT <column-list>
FROM <table-list>
WHERE <condition-list>
ORDER BY <column-list>

There are a number of ways to solve this problem. If you were limited to using static SQL, you would need to create a static query for every possible variation the end-user could run. Although this might be feasible if the report were simple, this is much more difficult to maintain.

Another way to solve this problem is to “build” the query within your application code from its subcomponents. For example, you can include columns as selected by the user, as well as the appropriate tables, specify the appropriate conditions, and choose the ordering of the result set. Of course, this can only be done at run time, so while your application code may have parts of the query inside it, you will actually have to put it together dynamically.

This is the type of scenario in which dynamic SQL is necessary. In dynamic embedded SQL, input host variables are treated as placeholders for portions of the SQL statements. Output host variables are placeholders for return arguments for the results of the SQL statements SELECT INTO, VALUES INTO, or FETCH INTO.

You do not have to dynamically prepare INSERT statements, assuming that the target of the INSERT and the source of the data are well defined. INSERT statements operate on all columns of a row; therefore, the column names/types and the number of columns are predefined within the table definition. The exception is if the table or view name is not known during application development.

In addition, using dynamic SQL statements provides the flexibility of controlling the compilation environment for each SQL statement with special registers, such as CURRENT QUERY OPTIMIZATION.

Generally speaking, coding a dynamic SQL application requires more complex programming techniques because special data structures must be manipulated. You might have to incorporate dynamic SQL statements to provide end-users with the flexible interface they require. Another excellent example of a dynamic embedded SQL interface is the DB2 CLP utility. This utility dynamically prepares each SQL statement.

Dynamic SQL Phases

Using dynamically prepared SQL statements requires a number of steps, starting with the PREPARE statement.

Prepare

If an SQL statement is dynamically prepared, the syntax of the statement must be checked during program execution. The access plan also must be generated during program execution. These two tasks are performed using the PREPARE SQL statement. The PREPARE statement transforms the string representation of an SQL statement into a form that can be executed by an application. The PREPARE statement creates a temporary executable module, which is not stored in the database system catalog tables as is the case for static SQL.

The PREPARE statement can optionally populate a structure known as the SQLDA with information about the SQL statement.

Describe

The DESCRIBE statement can be used to provide the application developer with information about an SQL statement that has been previously prepared. For example, a DESCRIBE statement of a previously prepared SELECT statement allows the application developer to determine the amount of storage required to store the query results.

Execute

The EXECUTE statement executes a previously prepared SQL statement. Therefore, it is possible for the developer to prepare and describe the statement and never actually execute the statement. This technique can be useful to determine information about the SQL statement without actually executing the statement. Once prepared, a statement can be executed repeatedly within an application.

Fetch

The FETCH statement retrieves a single row of data from a result set. It is very similar to the FETCH statement used in embedded static SQL statements. The main difference is that this type of FETCH statement uses a cursor that represents a dynamically prepared SQL statement. The cursor must have been opened prior to the FETCH statement. Data can be retrieved into host variables or a properly allocated SQLDA structure.

Execute Immediate (Optional)

The EXECUTE IMMEDIATE SQL statement is a shortcut to issuing a dynamic SQL statement. An SQL statement that does not require any host variables or parameter markers, and does not return a multirow result set, can be executed using the EXECUTE IMMEDIATE statement. The statement is dynamically prepared and executed in a single embedded SQL statement.

Types of Dynamic SQL Statements

There are two major types of dynamic SQL statements:

• Dynamic statements not requiring parameter markers.

• Dynamic statements requiring parameter markers.

Parameter Markers

A dynamic SQL statement cannot contain host variables except for a character string host variable containing the SQL statement to be processed dynamically. (This is because host variable information such as data type and length is only available during application precompilation. At execution time the host variable information is not present.) Any missing information for a dynamic SQL statement should contain a parameter marker. A parameter marker is represented in an SQL statement using the question mark (?) character. In static SQL statements, host variables can only be used as placeholders for certain parts of the statement. Host variables are usually used in the WHERE clause for static SQL statements. Parameter markers are more flexible than host variables. For example, a parameter marker can be used in the FROM clause or the SELECT list, as well as any place where static host variables can be used.

A parameter marker is defined in the SQL statement before the statement is prepared. The missing information is supplied in the EXECUTE or OPEN statements using a host variable or an SQLDA data structure.

Dynamic Statements without Parameter Markers

If the entire SQL statement is known at run time, then the statement can be dynamically prepared and parameter markers are not required. These statements are fully optimized during the dynamic prepare because there is no missing information.

Dynamic Statements with Parameter Markers

Parameter markers are used in dynamic SQL statement processing in a manner similar to the way host variables are used in static SQL processing. Each dynamic SQL statement contains a question mark (?) for each missing element. When the statement is processed, the missing data is substituted using a host variable or an SQLDA data structure.

SQLDA Data Structure

The SQLDA data structure is a two-way communications data structure. The SQLDA is used to transfer data back and forth between the application and DB2 UDB. This structure actually contains a number of other structures named SQLVAR. Each SQLVAR data structure corresponds to a single column of data.

The SQLDA can be used as input to SQL statements and output from SQL statements. The SQLDA data structure is defined for C, REXX, FORTRAN, and COBOL.

Before continuing on the subject of embedded dynamic SQL statement processing, we need to examine the SQLDA data structure. This data structure is used as output from DB2 UDB in the following cases:

• During the PREPARE INTO or DESCRIBE statement because it will contain the required number of SQLVAR elements for the query, or it will populate the SQLDA with the column data type information.

• During the FETCH INTO statements because the SQLDA can be used as the location for the retrieved rows of data.

• During the EXECUTE statement because the SQLDA can be used to provide data for the parameter markers of the prepared SQL statement.

In OPEN, FETCH, EXECUTE, and CALL statements, the SQLDA is used to contain input and output host variables. In DESCRIBE and PREPARE INTO statements, the SQLDA is used to describe a column of a result table, including its length and data type.

SQLVAR Elements

There are two types of SQLVAR structures in the SQLDA. Base SQLVARs contain information regarding the data type, name, length, host variable, and indicator variable for each column in the result set. The base SQLVARs are always present. The secondary SQLVARs are only present if the SQLDA has been doubled in size. As noted previously, an SQLDA is doubled in size if the select list includes columns defined as a LOB.

The SQLDA header information in Table 9.4 contains information regarding the size of the SQLDA, the number of SQLVARs allocated (sqln), and the number of SQLVARs required for the statement (sqld). Note that some of the elements of the SQLDA header are initialized by DB2 UDB and others are initialized by the application. The sqln element is set by the application and the sqld element is set by DB2 UDB.

The value of sqln needs to be greater than or equal to the value of sqld before the SQLDA can be used in a FETCH , EXECUTE , or CALL statement. The DESCRIBE will not be successful until the application developer has allocated the proper number (sqld) of SQLVAR entries. If there are LOB data types, then twice as many (2*sqld) SQLVAR entries must be allocated.

Output SQLDA

If the number of columns defined in an SQL statement is unknown by the application developer, then the application must be ready to accept any number of columns. The maximum number of columns in a SELECT statement is 500 or 1,012 depending on whether the page size is 4K or larger. Not only application developers need to know the number of columns, they must also know the data type of each column.

In Table 9.5, the SQLVAR structure is shown. The first element, sqltype, is used to indicate the data type. If the SQLDA is being used as input using the FETCH, EXECUTE, or CALL statements, the sqltype and a compatible host variable must be initialized. The possible values for sqltype are defined in the DB2 UDB v8 SQL Reference.

This user may supply the entire SQL statement or a portion of the SQL statement. Often, only a portion of the SQL statement is known. After the entire SQL statement has been determined, the developer must dynamically prepare the statement for execution using the PREPARE statement.

The first example does not contain any parameter markers because the entire statement text is known.

 szSQLText='SELECT customer_id, customer_name
            FROM customer';
PREPARE cust1 INTO :minsqlda FROM :szSQLText';

Let's examine this example starting from the end of the PREPARE statement. A host variable called szSQLText is referenced. This host variable must contain a valid SQL statement. The host variable should be a string data type and contain the entire SQL statement (there are no parameter markers in this example).

If the SQL statement has any missing parts, they should be represented in the statement text using the (?) character and supplied as host variables during statement execution. The SQL statement is checked for syntax and information is provided about the number and data types of the columns referenced.

The PREPARE statement in the previous example contains two other user-supplied components. A statement identifier of cust1 is used to reference the prepared statement during query execution. The cust1 identifier is not a host variable. It will be used during query execution and row fetching. Another host variable called minsqlda is used to contain information about the SQL statement. The SQL statement contains two columns of type variable length (VARCHAR).

The SQLDA structure, called minsqlda, contains information about the query being prepared. Like all other host variables, the SQLDA data structure must be initialized prior to its use in an SQL statement. An SQLDA contains a number of SQLVAR data structures. Each SQLVAR data structure corresponds to a single column for each row being processed. Because the nature of the query is unknown by the application developer, the required size of the SQLDA is also unknown.

There are two methods of initializing the SQLDA data structure.

• Allocate an SQLDA with the maximum number of SQLVAR elements.

• Allocate an SQLDA with the minimum number of SQLVAR elements and reinitialize the SQLDA with the proper number of SQLVAR elements following a DESCRIBE or PREPARE INTO statement.

The statement being prepared in the example contains a two-column select list. Therefore, an SQLDA structure with at least two SQLVAR elements is required. If the SQLDA does not contain enough SQLVAR elements for the query, the statement cannot be executed. If you decided to declare the maximum SQLDA size, you would waste memory, but avoid having to reallocate the SQLDA to a larger size. Remember, if any of the columns are defined as LOBs, the number of SQLVAR elements needs to be doubled.

If the SQLDA is not large enough, additional SQLVAR elements must be allocated and the statement must be prepared again, or the DESCRIBE statement can be used to acquire the column description. (In the latter case, DB2 UDB returns the number of items detected in the select list to the application in the first PREPARE.)

In the following example, the SQL statement defined in the string szSQLText2 is being dynamically prepared. This will not populate an SQLDA structure. It is not mandatory that every dynamically prepared SQL statement be described using the PREPARE INTO or the DESCRIBE statements. Remember that the proper SQLDA structure needs to be provided when the statement is executed.

PREPARE cust2 FROM :szSQLText2

After the statement (SELECT * FROM tabname) has been prepared, it is executed and fetched into an SQLDA data structure. The DESCRIBE statement is used to provide detailed information regarding the column name, length, null constraints, and data type.

Deferred Prepare

Deferred prepare improves network traffic performance by combining the SQL PREPARE statement flow with the associated OPEN, DESCRIBE, or EXECUTE statement flow. DB2 UDB defers sending the SQL PREPARE statement until the associated OPEN, DESCRIBE, or EXECUTE statement is issued by the application. For example, a PREPARE statement that does not have the INTO <SQLDA> clause can be deferred until an OPEN is issued. Deferred prepare can improve the performance of dynamic SQL and ODBC applications that perform queries, especially when the answer set is small. Deferred prepare can be specified as a precompiler option (DEFERRED_PREPARE) or in the Set Client API (SQL_DEFERRED_PREPARE setting). The CLP uses deferred prepare.