Static SQL usage
Static SQL is generally used in embedded SQL database applications. Static SQL statements are hard-coded in an embedded SQL application program when the source code is written. The source code must be processed by the DB2 database manager using a SQL precompiler before it can be compiled and executed. During this process, the SQL precompiler evaluates references to tables, columns, and declared data types of all host variables and determines which data conversion methods need to be used when data is moved to and from the database. Additionally, the SQL precompiler performs error checking on each coded SQL statement and ensures that appropriate host variable data types are used for their respective table column values. Static SQL is called static because the SQL statements in the program do not change each time the program is run. Static SQL works well in many situations. In fact, it can be used in any application for which the data access can be determined at program design time. For example, an order entry program always uses the same statement to insert a new order, and an airline reservation system always uses the same statement to change the status of a seat from 'available' to 'reserved'. Each of these statements would be generalized through the use of host variables. Different values can be inserted in a sales order and different seats can be reserved. Because such statements can be hard-coded in the program, such programs have the advantage that statements need to be parsed, validated, and optimized only once, at compile time. This results in relatively fast code at runtime.
Although static SQL statements are easy to use, they are limited because their format must be known in advance by the precompiler and because they can only work with host variables. There are many situations in which the content of an SQL statement is not known by the programmer in advance. For example, suppose a spreadsheet allows a user to enter a query, which the spreadsheet then sends to the database management system to retrieve data. The contents of this query obviously cannot be known to the programmer when the spreadsheet program is written.
Static SQL can be executed from the following interfaces:
Embedded SQL applications
Embedded SQL routines
In general programming using static SQL is easier than using embedded dynamic SQL. Static SQL statements are simply embedded into the host language source file, and the precompiler handles the necessary conversion to database manager run-time service API calls that the host language compiler can process. Within embedded SQL programs, there are two main implementations of static SQL:
Static SQL containing no host variables
This type of implementation is generally rare, and used mainly for initialization code and in simple SQL examples. Static SQL without host variables performs very well, because with no host variables to resolve, there is no run-time performance overhead, and the DB2 optimizer's capabilities can be fully realized.
Static SQL containing host variables
This is the traditional legacy style of static SQL. Although static SQL with host variables performs better at run time than dynamic SQL which requires statement preparation and the establishment of catalog locks during statement compilation, the full power of the optimizer cannot be utilized because the optimizer does not know the entire SQL statement. This can be problematic when the data a SQL statement operates on has a highly non-uniform data distribution. For this reason static SQL without host variables can perform better. Of course host variables provide support for binding variable values to a statement.
Static SQL Performance
In general executing a query as static SQL will ensure good query performance. Both forms of static SQL statements described above can be used to achieve better query performance than is possible when dynamic SQL is used. For simple, short-running SQL programs, a static SQL statement executes faster than the same statement processed dynamically because the overhead of preparing an executable form of the statement is done at precompile time instead of at run time.
The performance of static SQL depends on the statistics of the database the last time the application was bound. However, if these statistics change, the performance of equivalent dynamic SQL can be very different. If, for example, an index is added to a database at a later time, an application using static SQL cannot take advantage of the index unless it is rebound to the database. In addition, if you are using host variables in a static SQL statement, the optimizer will not be able to take advantage of any information about data distribution.
Static SQL Authorization
The authorization of the person that binds an application package is used at runtime to validate the privileges required to execute the static SQL statements used in the application. This means that the end user running the application does not directly require the privileges to execute the individual statements in the package. For example, consider an application that contains SQL statements that update a table storing inventory data. Once the package is bound to the database by someone with the necessary privileges to update the table, other users will be able to run the application and thereby make updates to the inventory table.
The combination of static SQL features make it an appropriate choice for applications where SQL statement syntax is known at application programming time and in cases where SQL statement performance is critical to application performance. It can also make application programming code easier to maintain and simply the management of authorizations required to execute SQL within applications.
In static SQL, the authorizations to access objects (such as a table, view) are associated with a package and are validated at package binding time. This means that database administrators need only to grant EXECUTE on a particular package to a set of users (thus encapsulating their privileges in the package) without having to grant them explicit access to each database object. In dynamic SQL, the authorizations are validated at run time on a per statement basis; therefore, users MUST be granted explicit access to each database object. This permits these users access to parts of the object that they do not have a need to access.
Dynamic SQL usage
Dynamic SQL is generally used for sending SQL statements to the database manager from interactive query building graphical user interfaces and SQL command line processors as well as from applications where the complete structure of queries is not known at application compilation time and the programming API supports dynamic SQL.
There are many situations in which the content of an SQL statement is not known by a user or programmer in advance. For example, suppose a spreadsheet allows a user to enter a query, which the spreadsheet then sends to the database management system to retrieve data. The contents of this query obviously cannot be known to the programmer when the spreadsheet program is written. To solve this problem, the spreadsheet uses a form of embedded SQL called dynamic SQL. Unlike static SQL statements, which are hard-coded in the program, Dynamic SQL statements can be built at run time and placed in a string host variable. They are then sent to the database management system for processing. Because the database management system must generate an access plan at run time for dynamic SQL statements, dynamic SQL is generally slower than static SQL. When a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not stripped from the program, as in static SQL. Instead, they are replaced by a function call that passes the statement to the DBMS.
Since the actual creation of dynamic SQL statements is based upon the flow of programming logic at application run time, they are more powerful than Static SQL statements. However, because the DBMS must go through each of the multiple steps of processing a SQL statement for each dynamic request, Dynamic SQL tends to be slower than Static SQL. DB2® database manager provides support internally for a dynamic statement cache which automatically assists in improving the performance of dynamic SQL queries.
Dynamic SQL can be used from the following interfaces:
Applications and external routines that employ APIs that support dynamic SQL including: Embedded SQL, JDBC, CLI, ADO.NET
Interactive DB2 GUI interfaces including: DB2 Command Editor in the DB2 Control Center
DB2 Command Line Processor
DB2 Command Windows