Embedded SQL
Applications can be written with SQL statements embedded within a host language. The SQL statements provide the database interface, while the host language provides the remaining support needed for the application to execute. The supported host languages are:
C/C++
Fortran
Cobol
Java (SQLJ)
SQL statements placed in an application are not specific to the host language. The database manager provides a way to convert the SQL syntax for processing by the host language.
For C, C++, COBOL, or FORTRAN, this conversion is handled by the DB2 precompiler. The DB2 precompiler is invoked using the PREP command. The precompiler converts embedded SQL statements directly into DB2 run-time services API calls.
For Java, the SQLj translator converts SQLj clauses into JDBC statements. The SQLj translator is invoked with the SQLJ command.
When the precompiler processes a source file, it specifically looks for SQL statements and avoids the non-SQL host language. It can find SQL statements because they are surrounded by special delimiters. These concepts are described in detail in Chapter 9, Embedded SQL Programming.
Static Embedded SQL
The structure of an SQL statement must be completely specified for a statement to be considered static. For example, the names for the columns and tables referenced in a statement must be fully known at precompile time. The only information that can be specified at run time are values within an SQL WHERE clause using host variables referenced by the statement. However, host variable information, such as their data types, must still be precompiled.
When a static SQL statement is prepared, an executable form of the statement is created and stored in the package in the database. The executable form can be constructed either at precompile time, or at a later bind time. In either case, preparation occurs before run time. The authorization of the person binding the application is used, and optimization is based upon database statistics and configuration parameters that might not be current when the application runs.
Dynamic Embedded SQL
The dynamic SQL support statements accept a character-string host variable and a statement name as arguments. The host variable contains the SQL statement to be processed dynamically in text form. The statement text is not processed when an application is precompiled. In fact, the statement text does not have to exist at the time the application is precompiled. Instead, the SQL statement is treated as a host variable for precompilation purposes and the variable is referenced during application execution.
These SQL statements are referred to as dynamic SQL. Dynamic SQL support statements are required to transform the host variable containing SQL text into an executable form and operate on it by referencing the statement name.
NOTE
The content of dynamic SQL statements follows the same syntax as static SQL statements, but with the following exceptions:
Comments are not allowed.
The statement cannot begin with EXEC SQL.
The statement cannot end with the statement terminator. An exception to this is the CREATE TRIGGER statement which can contain a semicolon (;).
You might want to use dynamic SQL when:
You need all or part of the SQL statement to be generated during application execution.
The objects referenced by the SQL statement do not exist at precompile time.
You want the statement to always use the most optimal access path, based on current database statistics.
Authorization of the SQL statement should be determined at run time rather than bind time.