When the syntax of embedded SQL statements is fully known at precompile time, the statements are referred to as static SQL. This is in contrast to dynamic SQL statements whose syntax is not known until run time.
| Note: | Static SQL is not supported in interpreted languages, such as REXX. |
The structure of an SQL statement must be completely specified in order 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 for any host variables referenced by the statement. However, host variable information, such as 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 may not be current when the application runs.
Programming using static SQL requires less effort 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 services API calls that the host language compiler can process.
Because the authorization of the person binding the application is used, the end user does not require direct privileges to execute the statements in the package. For example, an application could allow a user to update parts of a table without granting an update privilege on the entire table. This can be achieved by restricting the static SQL statements to allow updates only to certain columns or a range of values.
Static SQL statements are persistent, meaning that the statements last for as long as the package exists. Dynamic SQL statements are cached until they are either invalidated, freed for space management reasons, or the database is shut down. If required, the dynamic SQL statements are recompiled implicitly by the DB2 SQL compiler whenever a cached statement becomes invalid. For information on caching and the reasons for invalidation of a cached statement, refer to the SQL Reference.
The key advantage of static SQL, with respect to persistence, is that the static statements exist after a particular database is shut down, whereas dynamic SQL statements cease to exist when this occurs. In addition, static SQL does not have to be compiled by the DB2 SQL compiler at run time, while dynamic SQL must be explicitly compiled at run time (for example, by using the PREPARE statement). Because DB2 caches dynamic SQL statements, the statements do not need to be compiled often by DB2, but they must be compiled at least once when you execute the application.
There can be performance advantages to static SQL. For simple, short-running SQL programs, a static SQL statement executes faster than the same statement processed dynamically since the overhead of preparing an executable form of the statement is done at precompile time instead of at run time.
| Note: | 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 re-bound 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 distribution statistics for the table. |
This sample program shows examples of static SQL statements and database manager API calls in the following supported languages:
The REXX language does not support static SQL, so a sample is not provided.
This sample program contains a query that selects a single row. Such a query can be performed using the SELECT INTO statement.
The SELECT INTO statement selects one row of data from tables in a database, and the values in this row are assigned to host variables specified in the statement. Host variables are discussed in detail in Using Host Variables. For example, the following statement will deliver the salary of the employee with the last name of 'HAAS' into the host variable empsal:
SELECT SALARY
INTO :empsal
FROM EMPLOYEE
WHERE LASTNAME='HAAS'
A SELECT INTO statement must be specified to return only one or zero rows. Finding more than one row results in an error, SQLCODE -811 (SQLSTATE 21000). If several rows can be the result of a query, a cursor must be used to process the rows. See Selecting Multiple Rows Using a Cursor for more information.
For more details on the SELECT INTO statement, refer to the SQL Reference.
For an introductory discussion on how to write SELECT statements, see Coding SQL Statements to Retrieve and Manipulate Data.
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
EXEC SQL INCLUDE SQLCA; (1)
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION; (2)
char firstname[13];
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
#ifdef DB268K
/* Before making any API calls for 68K environment,
need to initial the Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
printf( "Sample C program: STATIC\n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd; (3)
CHECKERR ("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: static [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL SELECT FIRSTNME INTO :firstname (4)
FROM employee
WHERE LASTNAME = 'JOHNSON';
CHECKERR ("SELECT statement"); (5)
printf( "First name = %s\n", firstname );
EXEC SQL CONNECT RESET; (6)
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : STATIC.SQC */
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
class Static
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(1);
}
}
public static void main(String argv[])
{ try
{ System.out.println (" Java Static Sample");
String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname
Connection con = null;
// Set the connection (3)
if (argv.length == 0)
{ // connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 2)
{ String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else
{ throw new Exception("\nUsage: java Static [username password]\n");
}
// Set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
String firstname = null; (2)
#sql { SELECT FIRSTNME INTO :firstname
FROM employee
WHERE LASTNAME = 'JOHNSON' } ; (4)
System.out.println ("First name = " + firstname);
}
catch( Exception e ) (5)
{ System.out.println (e);
}
}
}
Identification Division.
Program-ID. "static".
Data Division.
Working-Storage Section.
copy "sql.cbl".
copy "sqlca.cbl". (1)
EXEC SQL BEGIN DECLARE SECTION END-EXEC. (2)
01 firstname pic x(12).
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: STATIC".
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd (3)
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL SELECT FIRSTNME INTO :firstname (4)
FROM EMPLOYEE
WHERE LASTNAME = 'JOHNSON' END-EXEC.
move "SELECT" to errloc.
call "checkerr" using SQLCA errloc. (5)
display "First name = ", firstname.
EXEC SQL CONNECT RESET END-EXEC. (6)
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Prog.
stop run.
program static
implicit none
include 'sqlenv.f'
EXEC SQL INCLUDE SQLCA (1)
EXEC SQL BEGIN DECLARE SECTION (2)
character*12 firstname
character*8 userid
character*18 passwd
EXEC SQL END DECLARE SECTION
character*80 errloc
print *, 'Sample Fortran Program: STATIC'
print *, 'Enter your user id (default none):'
read 100, userid
100 format (a8)
if( userid(1:1) .eq. ' ' ) then
EXEC SQL CONNECT TO sample
else
print *, 'Enter your password :'
read 100, passwd
EXEC SQL CONNECT TO sample USER :userid USING :passwd
end if (3)
errloc = 'CONNECT'
call checkerr (sqlca, errloc, *999)
EXEC SQL SELECT firstnme INTO :firstname
c FROM employee WHERE lastname = 'JOHNSON' (4)
errloc = 'SELECT'
call checkerr (sqlca, errloc, *999) (5)
print *, 'First name = ', firstname
EXEC SQL CONNECT RESET (6)
errloc = 'CONNECT RESET'
call checkerr (sqlca, errloc, *999)
999 stop
end
The database manager provides application programmers with statements for retrieving and manipulating data; the coding task consists of embedding these statements into the host language code. This section shows how to code statements that will retrieve and manipulate data for one or more rows of data in DB2 tables. (It does not go into the details of the different host languages.) For the exact rules of placement, continuation, and delimiting SQL statements, see:
One of the most common tasks of an SQL application program is to retrieve data. This is done using the select-statement, which is a form of query that searches for rows of tables in the database that meet specified search conditions. If such rows exist, the data is retrieved and put into specified variables in the host program, where it can be used for whatever it was designed to do.
After you have written a select-statement, you code the SQL statements that define how information will be passed to your application.
You can think of the result of a select-statement as being a table having rows and columns, much like a table in the database. If only one row is returned, you can deliver the results directly into host variables specified by the SELECT INTO statement.
If more than one row is returned, you must use a cursor to fetch them one at a time. A cursor is a named control structure used by an application program to point to a specific row within an ordered set of rows. For information about how to code and use cursors, see the following sections:
Host variables are variables referenced by embedded SQL statements. They transmit data between the database manager and an application program. When you use a host variable in an SQL statement, you must prefix its name with a colon, (:). When you use a host variable in a host language statement, omit the colon.
Host variables are declared in compiled host languages, and are delimited by BEGIN DECLARE SECTION and END DECLARE SECTION statements. These statements enable the precompiler to find the declarations.
| Note: | Java JDBC and SQLJ programs do not use declare sections. Host variables in Java follow the normal Java variable declaration syntax. |
Host variables are declared using a subset of the host language. For a description of the supported syntax for your host language, see:
The following rules apply to host variable declaration sections:
With respect to SQL statements, all host variables have a global scope regardless of where they are actually declared in a single source file. Therefore, host variable names must be unique within a source file.
This does not mean that the DB2 precompiler changes the scope of host variables to global so that they can be accessed outside the scope in which they are defined. Consider the following example:
foo1(){
.
.
.
BEGIN SQL DECLARE SECTION;
int x;
END SQL DECLARE SECTION;
x=10;
.
.
.
}
foo2(){
.
.
.
y=x;
.
.
.
}
Depending on the language, the above example will either fail to compile because variable x is not declared in function foo2() or the value of x would not be set to 10 in foo2(). To avoid this problem, you must either declare x as a global variable, or pass x as a parameter to function foo2() as follows:
foo1(){
.
.
.
BEGIN SQL DECLARE SECTION;
int x;
END SQL DECLARE SECTION;
x=10;
foo2(x);
.
.
.
}
foo2(int x){
.
.
.
y=x;
.
.
.
}
For further information on declaring host variables, see:
The Declaration Generator speeds application development by generating declarations for a given table in a database. It creates embedded SQL declaration source files which you can easily insert into your applications. db2dclgn supports the C/C++, Java, COBOL, and FORTRAN languages.
To generate declaration files, enter the db2dclgn command in the following format:
db2dclgn -d database-name -t table-name [options]
For example, to generate the declarations for the STAFF table in the SAMPLE database in C in the output file staff.h, issue the following command:
db2dclgn -d sample -t staff -l C
The resulting staff.h file contains:
struct
{
short id;
struct
{
short length;
char data[9];
} name;
short dept;
char job[5];
short years;
double salary;
double comm;
} staff;
For detailed information on db2dclgn, refer to the Command Reference.
Table 4. Declaring Host Variables
| Language | Example Source Code |
|---|---|
| C/C++ |
EXEC SQL BEGIN DECLARE SECTION; short dept=38, age=26; double salary; char CH; char name1[9], NAME2[9]; /* C comment */ short nul_ind; EXEC SQL END DECLARE SECTION; |
| Java |
// Note that Java host variable declarations follow // normal Java variable declaration rules, and have // no equivalent of a DECLARE SECTION short dept=38, age=26; double salary; char CH; String name1[9], NAME2[9]; /* Java comment */ short nul_ind; |
| COBOL |
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 age PIC S9(4) COMP-5 VALUE 26. 01 DEPT PIC S9(9) COMP-5 VALUE 38. 01 salary PIC S9(6)V9(3) COMP-3. 01 CH PIC X(1). 01 name1 PIC X(8). 01 NAME2 PIC X(8). * COBOL comment 01 nul-ind PIC S9(4) COMP-5. EXEC SQL END DECLARE SECTION END-EXEC. |
| FORTRAN |
EXEC SQL BEGIN DECLARE SECTION
integer*2 age /26/
integer*4 dept /38/
real*8 salary
character ch
character*8 name1,NAME2
C FORTRAN comment
integer*2 nul_ind
EXEC SQL END DECLARE SECTION
|
Table 5. Referencing Host Variables
| Language | Example Source Code |
|---|---|
| C/C++ |
EXEC SQL FETCH C1 INTO :cm; printf( "Commission = %f\n", cm ); |
| JAVA (SQLJ) |
#SQL { FETCH :c1 INTO :cm };
System.out.println("Commission = " + cm);
|
| COBOL |
EXEC SQL FETCH C1 INTO :cm END-EXEC DISPLAY 'Commission = ' cm |
| FORTRAN |
EXEC SQL FETCH C1 INTO :cm WRITE(*,*) 'Commission = ', cm |
Applications written in languages other than Java must prepare for receiving null values by associating an indicator variable with any host variable that can receive a null. Java applications compare the value of the host variable with Java null to determine whether the received value is null. An indicator variable is shared by both the database manager and the host application; therefore, the indicator variable must be declared in the application as a host variable. This host variable corresponds to the SQL data type SMALLINT.
An indicator variable is placed in an SQL statement immediately after the host variable, and is prefixed with a colon. A space can separate the indicator variable from the host variable, but is not required. However, do not put a comma between the host variable and the indicator variable. You can also specify an indicator variable by using the optional INDICATOR keyword, which you place between the host variable and its indicator.
Indicator Variables shows indicator variable usage in the supported host languages using the INDICATOR keyword.
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind; if ( cmind < 0 ) printf( "Commission is NULL\n" );
#SQL { FETCH :c1 INTO :cm };
if ( cm == null )
System.out.println( "Commission is NULL\n" );
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind END-EXEC IF cmind LESS THAN 0 DISPLAY 'Commission is NULL'
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind IF ( cmind .LT. 0 ) THEN WRITE(*,*) 'Commission is NULL' ENDIF
In the figure, cmind is examined for a negative value. If it is not negative, the application can use the returned value of cm. If it is negative, the fetched value is NULL and cm should not be used. The database manager does not change the value of the host variable in this case.
| Note: | If the database configuration parameter DFT_SQLMATHWARN is set to 'YES', the value of cmind may be -2. This indicates a NULL that was caused by evaluating an expression with an arithmetic error or by an overflow while attempting to convert the numeric result value to the host variable. |
If the data type can handle NULLs, the application must provide a NULL indicator. Otherwise, an error may occur. If a NULL indicator is not used, an SQLCODE -305 (SQLSTATE 22002) is returned.
If the SQLCA structure indicates a truncation warning, the indicator variables can be examined for truncation. If an indicator variable has a positive value, a truncation occurred.
When processing INSERT or UPDATE statements, the database manager checks the indicator variable if one exists. If the indicator variable is negative, the database manager sets the target column value to NULL if NULLs are allowed. If the indicator variable is zero or positive, the database manager uses the value of the associated host variable.
The SQLWARN1 field in the SQLCA structure may contain an 'X' or 'W' if the value of a string column is truncated when it is assigned to a host variable. It contains an 'N' if a null terminator is truncated.
A value of 'X' is returned by the database manager only if all of the following conditions are met:
The value returned in the indicator variable will be the length of the resultant character string in the application's code page.
In all other cases involving data truncation, (as opposed to NULL terminator truncation), the database manager returns a 'W'. In this case, the database manager returns a value in the indicator variable to the application that is the length of the resultant character string in the code page of the select list item (either the application code page, the data base code page, or nothing). For related information, refer to the SQL Reference.
Each column of every DB2 table is given an SQL data type when the column is created. For information about how these types are assigned to columns, refer to the CREATE TABLE statement in the SQL Reference. The database manager supports the following column data types:
The following data types are supported only in double-byte character set (DBCS) and Extended UNIX Code (EUC) character set environments:
Notes:
Supported host languages have data types that correspond to the majority of the database manager data types. Only these host language data types can be used in host variable declarations. When the precompiler finds a host variable declaration, it determines the appropriate SQL data type value. The database manager uses this value to convert the data exchanged between itself and the application.
As the application programmer, it is important for you to understand how the database manager handles comparisons and assignments between different data types. Simply put, data types must be compatible with each other during assignment and comparison operations, whether the database manager is working with two SQL column data types, two host-language data types, or one of each.
The general rule for data type compatibility is that all supported host-language numeric data types are comparable and assignable with all database manager numeric data types, and all host-language character types are compatible with all database manager character types; numeric types are incompatible with character types. However, there are also some exceptions to this general rule depending on host language idiosyncrasies and limitations imposed when working with large objects.
Within SQL statements, DB2 provides conversions between compatible data types. For example, in the following SELECT statement, SALARY and BONUS are DECIMAL columns; however, each employee's total compensation is returned as DOUBLE data:
SELECT EMPNO, DOUBLE(SALARY+BONUS) FROM EMPLOYEE
Note that the execution of the above statement includes conversion between DECIMAL and DOUBLE data types. To make the query results more readable on your screen, you could use the following SELECT statement:
SELECT EMPNO, DIGIT(SALARY+BONUS) FROM EMPLOYEE
To convert data within your application, contact your compiler vendor for additional routines, classes, built-in types, or APIs that supports this conversion.
Character data types may also be subject to character conversion. If your application code page is not the same as your database code page, see Conversion Between Different Code Pages.
For the list of supported SQL data types and the corresponding host language data types, see the following:
For more information about SQL data types, the rules of assignments and comparisons, and data conversion and conversion errors, refer to the SQL Reference.
The following code segments show the modification to the corresponding segments in the C version of the sample STATIC program, listed in C Example: STATIC.SQC. They show the implementation of indicator variables on data columns that are nullable. In this example. the STATIC program is extended to select another column, WORKDEPT. This column can have a null value. An indicator variable needs to be declared as a host variable before being used.
. . . EXEC SQL BEGIN DECLARE SECTION; char wd[3]; short wd_ind; char firstname[13]; . . . EXEC SQL END DECLARE SECTION; . . . /* CONNECT TO SAMPLE DATABASE */ . . . EXEC SQL SELECT FIRSTNME, WORKDEPT INTO :firstname, :wd:wdind FROM EMPLOYEE WHERE LASTNAME = 'JOHNSON'; . . .