Executing SQL
This section provides a brief overview of the different ways to execute SQL from an application and shows you how to run your SQL statements interactively.
Methods of executing SQL
The method of preparing an SQL statement for execution and the persistence of its operational form distinguish static SQL from dynamic SQL.
Static SQL
The source form of a static SQL statement is embedded within an application program that is written in a host programming language, such as C. The statement is prepared before the program is executed, and the operational form of the statement persists beyond the execution of the program. You can use static SQL when you know before run time what SQL statements your application needs to run. 
Dynamic SQL
Unlike static SQL, dynamic SQL statements are constructed and prepared at run time. You can use dynamic SQL when you do not know the content of an SQL statement when you write a program or before you run it. 
DB2 ODBC
DB2 ODBC (Open Database Connectivity) is an application programming interface (API) that enables C and C++ application programs to access relational databases. This interface offers an alternative to using embedded static SQL and a different way of performing dynamic SQL. Through the interface, an application invokes a C function at execution time to connect to a data source, to dynamically issue SQL statements, and to retrieve data and status information. 
DB2 access for Java: SQLJ and JDBC
SQLJ and JDBC are two methods for accessing DB2 data from the Java programming language. In general, Java applications use SQLJ for static SQL, and they use JDBC for dynamic SQL.
You can read more about each of these options in “Using Java to execute static and dynamic SQL” on page 198.
Interactive SQL
Interactive SQL refers to SQL statements that you submit to DB2 by using a query tool, such as DB2 QMF for Windows. “DB2 QMF (Query Management Facility) for Windows” on page 115 has information about using this tool.
Executing SQL from your workstation
The easiest and most efficient way to run SQL is to use a query tool. DB2 QMF for Windows is a popular query tool with which you can enter and run your SQL statements easily. This section provides an overview of how to use DB2 QMF for Windows. 
Although this section focuses on DB2 QMF for Windows, other options are available. You can use DB2 QMF for WebSphere to enter and run SQL statements from your Web browser or use DB2 QMF for TSO/CICS to enter and run SQL statements from TSO or CICS. In addition, you can enter and run SQL statements at a TSO terminal by using the SPUFI (SQL processor using file input) facility. SPUFI prepares and executes these statements dynamically. All of these tools prepare and dynamically execute the SQL statements. 
DB2 QMF (Query Management Facility) for Windows
This section acquaints you with using DB2 QMF for Windows to create and run SQL statements. DB2 QMF for Windows simplifies access to DB2 from a workstation. In fact, QMF for Windows was built for DB2.
With the query-related features of DB2 QMF for Windows, you can perform the following tasks:
Build powerful queries without knowing SQL
Analyze query results online, including OLAP analysis
Edit query results to update DB2 data
Format traditional text-based reports, as well as reports with rich formatting
Display charts and other complex visuals
Send query results to an application of your choice
Develop applications using robust API commands
As you read through this chapter and learn more about how to write SQL statements, the uses of this tool will become clearer.
Entering and processing SQL statements
There are several ways to create your SQL statements with DB2 QMF for Windows:
Use the Database Explorer window to easily find and run saved queries (also known as a canned query) that everyone at the same database server can share.
If you know SQL, type the SQL statement directly in the window.
If you don't know SQL, use the prompted or diagram interface to build the SQL statement.
As Figure 5.1 shows, the Database Explorer presents the objects saved on a server in a tree structure. By expanding and collapsing branches you can easily locate and use saved queries. You can open the selected query and see the SQL statements or run the query.
Figure 5.1. Database Explorer
If you need to build a new query, you can enter the SQL statements directly in the query window or you can create the SQL statements by using diagrams or prompts. Figure 5.2 shows how to build a query and create your SQL statements visually using diagrams.
Figure 5.3 illustrates another way to create SQL statements by using a series of dialogs that prompt you for the various elements of your query.
As you build a query using diagrams or prompts, you can always see the SQL that is being created, as shown in Figure 5.4.
Working with query results
When you finish building the query, you can simply click the 'Run Query' button to execute the SQL statements. After you run the query, DB2 QMF for Windows returns the query results in an interactive window. Figure 5.5 shows an example of query results.
The query results shown in Figure 5.5 were formatted by the comprehensive formatting options of DB2 QMF for Windows. A robust expression language lets you conditionally format query results by retrieved column values. You can add calculated columns to the query results and group data columns on both axes with or without summaries. You can also use extensive drag-and-drop capabilities to easily restructure the appearance of the query results.
In addition to formatting the query results, you can perform the following actions:
Create traditional text-based reports or state-of-the-art reports with rich formatting
Display query results using charts and other complex visuals
Share reports by storing them on the database server
Send query results to various applications such as Microsoft Excel or Lotus® 1-2-3