|
|
|
|
| SQL Getting Started | Introduces SQL concepts and provides examples for many constructs and tasks. | |
| SQL Reference | Describes SQL syntax, semantics, and the rules of the language. | |
| Embedded SQL Programming Guide | Explains how to develop applications that access DB2 databases using embedded SQL. | |
| Building Applications for UNIX Environments | Provides environment setup information and step-by-step instructions to compile, link, and run DB2 applications on a UNIX system. | |
| Building Applications for Windows and OS/2 Environments | Provides environment setup information and step-by-step instructions to compile, link, and run DB2 applications on a Windows or OS/2 system. | |
|
|
|
 |
|
|
| cursor | Demonstrates the use of a cursor using static SQL | |
| delet | Deletes items from a database using static SQL. | |
| dynamic | Demonstrates the use of a cursor using dynamic SQL. | |
| openftch | Fetches rows from the database and then updates or deletes them using static SQL. | |
| static | Retrieves information using static SQL | |
| updat | Updates a database using static SQL. | |
|
|
|
Structured Query Language (SQL) is the database interface language used to access and manipulate data in DB2 databases. You can embed SQL statements in your applications, enabling them to perform any task supported by SQL, such as retrieving or storing data. Using DB2, you can code your embedded SQL applications in the COBOL, FORTRAN, C, C++, and REXX programming languages. Other products enable you to code your applications in other languages, such as Basic and PL/I. For details about those products, refer to "About the Application Developer's Kit" and "Companion Products".
An application in which you embed SQL statements is called a host program. A programming language that you compile, and in which you embed SQL statements, is called a host language. The program and language are defined this way because they host or accommodate SQL statements.
The following table shows parts of host programs written in C and COBOL.
The SQL statements begin with EXEC SQL, distinguishing them from the host
language statements written in C and COBOL. SQL statement (1) declares
an SQLCA data structure, which your application can examine to determine the
results of an SQL statement. SQL statement (2) declares a host
variable, which you can use to pass data between your application and the
database.
| C Host Program | COBOL Host Program |
|---|---|
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlenv.h>
EXEC SQL INCLUDE SQLCA;(1)
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;(2)
char statement [81] = " ";
EXEC SQL END DECLARE SECTION;
|
DATA DIVISION. WORKING-STORAGE SECTION. COPY "sql.cbl". COPY "sqlenv.cbl". EXEC SQL INCLUDE SQLCA END-EXEC.(1) EXEC SQL BEGIN DECLARE SECTION END-EXEC.(2) 01 STATEMENT PIC X(81). EXEC SQL END DECLARE SECTION END-EXEC. |
Your application will usually contain the following parts. (Figure 2 also shows the parts in pseudocode format. Refer to the programming guides for the exact syntax of the SQL statements.)
Figure 2. A typical host program in pseudocode format
 |
(5)EXEC SQL CONNECT TO databaseA USER :USERID USING :PW
You also include one or more SQL statements to handle warnings and errors. In the example, an SQL statement causes control to pass to the statements identified by the host label ERRCHK:
(4)EXEC SQL WHENEVER SQLERROR GOTO ERRCHK
A transaction begins implicitly with the first executable SQL statement. For example, this can be the CONNECT statement (5) to connect to the database you want to access. Or it can be some other SQL statement, such as SELECT to select a row from the database, or INSERT to insert a row into the database.
A transaction ends with either a COMMIT or a ROLLBACK statement, or when the program ends. To commit means to complete the transaction. To rollback means to undo the transaction.
(6)EXEC SQL CONNECT RESET
When you embed SQL statements in your application, you must precompile and bind your application to a database, following the steps listed below and in Figure 3.
Figure 3. Building your embedded SQL application
 |
The precompiler converts the SQL statements in each source file into DB2 run-time API calls to the database manager. The precompiler also produces an access package in the database and, optionally, a bind file, if you specify that you want one created.
The access package (or package) contains access plans selected by the DB2 optimizer for the static SQL statements in your application. The access plans contain the information required by the database manager to execute the static SQL statements in the most efficient manner as determined by the optimizer. For dynamic SQL statements, the optimizer creates access plans when you run your application. Static and dynamic SQL statements are explained in "Comparing Static SQL versus Dynamic SQL".
The bind file contains the SQL statements and other data required to create an access package. You can use the bind file to rebind your application later without having to precompile it first. Rebinding creates access plans that are optimized for current database conditions. You need to rebind your applications if your application will access a different database from the one against which it was precompiled.
Static SQL statements are ones where you know, before compile time, the SQL statement type and the table and column names. The only unknowns are the specific data values the statement is searching for or updating. You can represent those values in host language variables. You prepare or build static SQL statements before you run your application.
In contrast, dynamic SQL statements are those statements that your application builds and executes at run time. An interactive application that prompts the end user for key parts of an SQL statement, such as the names of the tables and columns to be searched, is a good example of dynamic SQL. The application builds the SQL statement while it's running, and then submits the statement for processing.
You can write applications that have only static SQL statements or only dynamic SQL statements or a mix of both.
| Merits | Limitations |
|---|---|
| Ease of programming: Static SQL applications are
generally straight forward to program. You simply embed SQL statements in your
source file. For dynamic SQL applications, you need to use host variables or a
data structure to handle the parts of the SQL statement that aren't known
during development, as well as additional SQL statements such as PREPARE,
EXECUTE, and DESCRIBE.
Persistence: The access plan for static SQL statements is retained in the database in a ready-to-execute package, and can be reused. For dynamic SQL statements, the access plan must be created each time you run your application. Encapsulation and security: With static SQL applications, you can limit the access end users have to data. For example, you can grant a user permission to run an application that updates a table without giving the user general access to the table. You do this by granting authorizations to run an access package when you bind your application. This encapsulates access authority in the package, giving selected users permission only to run a package, not to access the entire table. In dynamic SQL applications, authorizations are validated at run time on a per statement basis. Therefore, in order to run a dynamic SQL statement, a user must have explicit access privileges for each database object. | Fixed access paths: The performance of static SQL
statements depends on the state of the database the last time you bound your
application. If you add an index to the database at a later time, your static
application cannot take advantage of the index unless you rebind it.
Similarly, adding a large number of rows to a table will change the statistics
that were used when the static SQL statement was originally bound to the
database. Since dynamic SQL statements are parsed and optimized at run time,
your dynamic application can take advantage of or react to any changes to the
database every time you run it.
Inflexibility: Applications that use static SQL are less flexible because you must code the transactions that the end user is going to issue against the database, and you cannot change them without recoding, recompiling, and rebinding the application to the database. Interactive applications with dynamic SQL are more flexible because the end user can specify the SQL statements at run time, either manually or using software. |
Generally, static SQL statements are well-suited for high-performance applications with predefined transactions. A reservation system is a good example of such an application.
| Merits | Limitations |
|---|---|
| Flexibility: You can develop flexible applications in
which end users specify the SQL statements at run time. This is ideal for
applications where you don't know the complete SQL statements during
development. For static SQL applications, you must know all the parts except
the specific data values the statement is searching for or updating which you
can represent with a host language variable.
Late access path binding: Dynamic SQL statements always use an optimal access plan because the statements are parsed and optimized at run time. This is ideal if the state of the database changes often, and can improve the performance of your application. Static SQL statements use the access plan created at precompile time. Object independence: Database objects that dynamic SQL statements access do not have to exist at precompile or bind time, only at run time. For static SQL statements, all the objects they reference must exist at bind time, but not necessarily at precompile time. | Start-up costs: In general, an application that uses dynamic SQL has a higher
start-up cost per SQL statement than static SQL because dynamic SQL statements
need to be compiled before your application can use them. However, each time
your application runs a dynamic SQL statement, the initial cost to compile
becomes less of a factor. If multiple users run the same dynamic application
with the same statements, only the first application to issue the statement
incurs the compilation cost.
Unpredictability: The performance behavior of dynamic SQL applications might differ during each execution because dynamic statements are prepared at run time, and the status of the database may change from one execution to the next. Static SQL applications run using an access package created at precompile time. The performance behavior of the application remains constant until you rebind it. |
Generally, dynamic SQL statements are well-suited for applications that run against a rapidly changing database where transactions need to be specified at run time. An interactive query interface is a good example of such an application.