A DB2 application program consists of several parts:
A transaction is a set of database operations that must conclude successfully before being committed to the database. With embedded SQL, a transaction begins implicitly and ends when the application executes either a COMMIT or ROLLBACK statement. An example of a transaction is the entry of a customer's deposit, and the updating of the customer's balance.
Certain SQL statements must appear at the beginning and end of the program to handle the transition from the host language to the embedded SQL statements.
The beginning of every program must contain:
Note that DB2 applications written in Java throw an SQLException, which you handle in a catch block, rather than using the SQLCA.
The body of every program contains the SQL statements that access and manage data. These statements constitute transactions. Transactions must include the following statements:
The end of the application program typically contains SQL statements that:
To code a DB2 application, you must first declare:
All variables that interact with the database manager must be declared in an SQL declare section. You must code an SQL declare section with the following structure:
Host program variables declared in an SQL declare section are called host variables. You can use host variables in host-variable references in SQL statements. Host-variable is a tag used in syntax diagrams in the SQL Reference. A program may contain multiple SQL declare sections.
The attributes of each host variable depend on how the variable is used in the SQL statement. For example, variables that receive data from or store data in DB2 tables must have data type and length attributes compatible with the column being accessed. To determine the data type for each variable, you must be familiar with DB2 data types, which are explained in Data Types.
For DB2 Version 7, the names of tables, aliases, views, and correlations have a maximum length of 128 bytes. Column names have a maximum length of 30 bytes. In DB2 Version 7, schema names have a maximum length of 30 bytes. Future releases of DB2 may increase the lengths of column names and other identifiers of SQL objects up to 128 bytes. If you declare variables that represent SQL objects with less than 128 byte lengths, future increases in SQL object identifier lengths may affect the stability of your applications. For example, if you declare the variable char[9]schema_name in a C++ application to hold a schema name, your application functions properly for the allowed schema names in DB2 Version 6, which have a maximum length of 8 bytes.
char[9] schema_name; /* holds null-delimited schema name of up to 8 bytes;
works for DB2 Version 6, but may truncate schema names in future releases */
However, if you migrate the database to DB2 Version 7, which accepts schema names with a maximum length of 30 bytes, your application cannot differentiate between the schema names LONGSCHEMA1 and LONGSCHEMA2. The database manager truncates the schema names to their 8-byte limit of LONGSCHE, and any statement in your application that depends on differentiating the schema names fails. To increase the longevity of your application, declare the schema name variable with a 128-byte length as follows:
char[129] schema_name; /* holds null-delimited schema name of up to 128 bytes
good for DB2 Version 7 and beyond */
To improve the future operation of your application, consider declaring all of the variables in your applications that represent SQL object names with lengths of 128 bytes. You must weigh the advantage of improved compatibility against the increased system resources that longer variables require.
To ease the use of this coding practice and increase the clarity of your C/C++ application code, consider using C macro expansion to declare the lengths of these SQL object identifiers. Since the include file sql.h declares SQL_MAX_IDENT to be 128, you can easily declare SQL object identifiers with the SQL_MAX_IDENT macro. For example:
#include <sql.h>
char[SQL_MAX_IDENT+1] schema_name;
char[SQL_MAX_IDENT+1] table_name;
char[SQL_MAX_IDENT+1] employee_column;
char[SQL_MAX_IDENT+1] manager_column;
For more information on C macro expansion, see C Macro Expansion.
You can use host variables to receive data from the database manager or to transfer data to it from the host program. Host variables that receive data from the database manager are output host variables, while those that transfer data to it from the host program are input host variables.
Consider the following SELECT INTO statement:
SELECT HIREDATE, EDLEVEL
INTO :hdate, :lvl
FROM EMPLOYEE
WHERE EMPNO = :idno
It contains two output host variables, hdate and lvl, and one input host variable, idno. The database manager uses the data stored in the host variable idno to determine the EMPNO of the row that is retrieved from the EMPLOYEE table. If the database manager finds a row that meets the search criteria, hdate and lvl receive the data stored in the columns HIREDATE and EDLEVEL, respectively. This statement illustrates an interaction between the host program and the database manager using columns of the EMPLOYEE table.
Each column of a table is assigned a data type in the CREATE TABLE definition. You must relate this data type to the host language data type defined in the Supported SQL Data Types section of each language-specific chapter in this document. For example, the INTEGER data type is a 32-bit signed integer. This is equivalent to the following data description entries in each of the host languages, respectively:
For the list of supported SQL data types and the corresponding host language data types, see the following:
In order to determine exactly how to define the host variable for use with a column, you need to find out the SQL data type for that column. Do this by querying the system catalog, which is a set of views containing information about all tables created in the database. The SQL Reference describes this catalog.
After you have determined the data types, you can refer to the conversion charts in the host language chapters and code the appropriate declarations. The Declaration Generator utility (db2dclgn) is also available for generating the appropriate declarations for a given table in a database. For more information on db2dclgn, see Declaration Generator - db2dclgn and refer to the Command Reference.
Table 4 shows examples of declarations in the supported host languages. Note that REXX applications do not need to declare host variables except for LOB locators and file reference variables. The contents of the variable determine other host variable data types and sizes at run time.
Table 4 also shows the BEGIN and END DECLARE SECTION statements. Observe how the delimiters for SQL statements differ for each language. For the exact rules of placement, continuation, and delimiting of these statements, see the language-specific chapters of this book.
The SQL Communications Area (SQLCA) is discussed in detail later in this chapter. This section presents an overview. To declare the SQLCA, code the INCLUDE SQLCA statement in your program.
For C or C++ applications use:
EXEC SQL INCLUDE SQLCA;
For Java applications: You do not explicitly use the SQLCA in Java. Instead, use the SQLException instance methods to get the SQLSTATE and SQLCODE values. See SQLSTATE and SQLCODE Values in Java for more details.
For COBOL applications use:
EXEC SQL INCLUDE SQLCA END-EXEC.
For FORTRAN applications use:
EXEC SQL INCLUDE SQLCA
When you preprocess your program, the database manager inserts host language variable declarations in place of the INCLUDE SQLCA statement. The system communicates with your program using the variables for warning flags, error codes, and diagnostic information.
After executing each SQL statement, the system returns a return code in both SQLCODE and SQLSTATE. SQLCODE is an integer value that summarizes the execution of the statement, and SQLSTATE is a character field that provides common error codes across IBM's relational database products. SQLSTATE also conforms to the ISO/ANS SQL92 and FIPS 127-2 standard.
Note that if SQLCODE is less than 0, it means an error has occurred and the statement has not been processed. If the SQLCODE is greater than 0, it means a warning has been issued, but the statement is still processed. See the Message Reference for a listing of SQLCODE and SQLSTATE error conditions.
If you want the system to control error checking after each SQL statement, use the WHENEVER statement.
| Note: | Embedded SQL for Java (SQLJ) applications cannot use the WHENEVER statement. Use the SQLException methods described in SQLSTATE and SQLCODE Values in Java to handle errors returned by SQL statements. |
The following WHENEVER statement indicates to the system what to do when it encounters a negative SQLCODE:
WHENEVER SQLERROR GO TO errchk
That is, whenever an SQL error occurs, program control is transferred to code that follows the label, such as errchk. This code should include logic to analyze the error indicators in the SQLCA. Depending upon the ERRCHK definition, action may be taken to execute the next sequential program instruction, to perform some special functions, or as in most situations, to roll back the current transaction and terminate the program. See Coding Transactions for more information on a transaction and Diagnostic Handling and the SQLCA Structure for more information about how to control error checking in your application program.
Exercise caution when using the WHENEVER SQLERROR statement. If your application's error handling code contains SQL statements, and if these statements result in an error while processing the original error, your application may enter an infinite loop. This situation is difficult to troubleshoot. The first statement in the destination of a WHENEVER SQLERROR should be WHENEVER SQLERROR CONTINUE. This statement resets the error handler. After this statement, you can safely use SQL statements.
For a DB2 application written in C or C++, if the application is made up of multiple source files, only one of the files should include the EXEC SQL INCLUDE SQLCA statement to avoid multiple definitions of the SQLCA. The remaining source files should use the following lines:
#include "sqlca.h"
extern struct sqlca sqlca;
If your application must be compliant with the ISO/ANS SQL92 or FIPS 127-2 standard, do not use the above statements or the INCLUDE SQLCA statement. For more information on the ISO/ANS SQL92 and FIPS 127-2 standards, see Definition of FIPS 127-2 and ISO/ANS SQL92. For the alternative to coding the above statements, see the following:
Generally, other nonexecutable SQL statements are also part of this section of the program. Both the SQL Reference and subsequent chapters of this manual discuss nonexecutable statements. Examples of nonexecutable statements are:
Your program must establish a connection to the target database server before it can run any executable SQL statements. This connection identifies both the authorization ID of the user who is running the program, and the name of the database server on which the program is run. Generally, your application process can only connect to one database server at a time. This server is called the current server. However, your application can connect to multiple database servers within a multisite update environment. In this case, only one server can be the current server. For more information on multisite updates, see Multisite Update.
Your program can establish a connection to a database server either:
Refer to the SQL Reference for a discussion of connection states and how to use the CONNECT statement. Upon initialization, the application requester establishes a default database server. If implicit connects are enabled, application processes started after initialization connect implicitly to the default database server. It is good practice to use the CONNECT statement as the first SQL statement executed by an application program. This avoids accidentally executing SQL statements against the default database.
After the connection has been established, your program can issue SQL statements that:
A connection lasts until a CONNECT RESET, CONNECT TO, or DISCONNECT statement is issued. In a multisite update environment, a connection also lasts until a DB2 RELEASE then DB2 COMMIT is issued. A CONNECT TO statement does not terminate a connection when using multisite update (see Multisite Update).
A transaction is a sequence of SQL statements (possibly with intervening host language code) that the database manager treats as a whole. An alternative term that is often used for transaction is unit of work.
To ensure the consistency of data at the transaction level, the system makes sure that either all operations within a transaction are completed, or none are completed. Suppose, for example, that the program is supposed to deduct money from one account and add it to another. If you place both of these updates in a single transaction, and a system failure occurs while they are in progress, then when you restart the system, the database manager automatically restores the data to the state it was in before the transaction began. If a program error occurs, the database manager restores all changes made by the statement in error. The database manager will not undo work performed in the transaction prior to execution of the statement in error, unless you specifically roll it back.
You can code one or more transactions within a single application program, and it is possible to access more than one database from within a single transaction. A transaction that accesses more than one database is called a multisite update. For information on these topics, see Remote Unit of Work and Multisite Update.
A transaction begins implicitly with the first executable SQL statement and ends with either a COMMIT or a ROLLBACK statement, or when the program ends.
In contrast, the following six statements do not start a transaction because they are not executable statements:
BEGIN DECLARE SECTION INCLUDE SQLCA
END DECLARE SECTION INCLUDE SQLDA
DECLARE CURSOR WHENEVER
An executable SQL statement always occurs within a transaction. If a program contains an executable SQL statement after a transaction ends, it automatically starts a new transaction.
To end a transaction, you can use either:
This statement ends the current transaction. It makes the database changes performed during the current transaction visible to other processes.
You should commit changes as soon as application requirements permit. In particular, write your programs so that uncommitted changes are not held while waiting for input from a terminal, as this can result in database resources being held for a long time. Holding these resources prevents other applications that need these resources from running.
The COMMIT statement has no effect on the contents of host variables.
Your application programs should explicitly end any transactions prior to terminating. If you do not end transactions explicitly, DB2 automatically commits all the changes made during the program's pending transaction when the program ends successfully, except on Windows 32-bit operating systems. DB2 rolls back the changes under the following conditions:
On Windows 32-bit operating systems, if you do not explicitly commit the transaction, the database manager always rolls back the changes.
For more information about program termination, see Ending the Program and Diagnostic Handling and the SQLCA Structure.
This statement ends the current transaction, and restores the data to the state it was in prior to beginning the transaction.
The ROLLBACK statement has no effect on the contents of host variables.
If you use a ROLLBACK statement in a routine that was entered because of an error or warning and you use the SQL WHENEVER statement, then you should specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK. This avoids a program loop if the ROLLBACK fails with an error or warning.
In the event of a severe error, you will receive a message indicating that you cannot issue a ROLLBACK statement. Do not issue a ROLLBACK statement if a severe error occurs such as the loss of communications between the client and server applications, or if the database gets corrupted. After a severe error, the only statement you can issue is a CONNECT statement.
To properly end your program:
| Note: | If the current transaction is still active when the program terminates, DB2 implicitly ends the transaction. Since DB2's behavior when it implicitly ends a transaction is platform specific, you should explicitly end all transactions by issuing a COMMIT or a ROLLBACK statement before the program terminates. See "Implicitly Ending a Transaction" for details on how DB2 implicitly ends a transaction. |
If your program terminates without ending the current transaction, DB2 implicitly ends the current transaction (see Ending the Program for details on how to properly end your program). DB2 implicitly terminates the current transaction by issuing either a COMMIT or a ROLLBACK statement when the application ends. Whether DB2 issues a COMMIT or ROLLBACK depends on factors such as:
DB2 implicitly commits a transaction if the termination is normal, or implicitly rolls back the transaction if it is abnormal. Note that what your program considers to be an abnormal termination may not be considered abnormal by the database manager. For example, you may code exit(-16) when your application encounters an unexpected error and terminate your application abruptly. The database manager considers this to be a normal termination and commits the transaction. The database manager considers items such as an exception or a segmentation violation as abnormal terminations.
DB2 always rolls back the transaction regardless of whether your application terminates normally or abnormally, unless you explicitly commit the transaction using the COMMIT statement.
Your application can use any of the DB2 APIs to set up and pass application contexts between threads as described in Multiple Thread Database Access. If your application uses these DB2 APIs, DB2 implicitly rolls back the transaction regardless of whether your application terminates normally or abnormally. Unless you explicitly commit the transaction using the COMMIT statement, DB2 rolls back the transaction.
Pseudocode Framework for Coding Programs summarizes the general framework for a DB2 application program in pseudocode format. You must, of course, tailor this framework to suit your own program.
Start Program
EXEC SQL BEGIN DECLARE SECTION |
DECLARE USERID FIXED CHARACTER (8) |
DECLARE PW FIXED CHARACTER (8) |
| Application
(other host variable declarations) | Setup
|
EXEC SQL END DECLARE SECTION |
EXEC SQL INCLUDE SQLCA |
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK |
(program logic)
EXEC SQL CONNECT TO database A USER :userid USING :pw |
EXEC SQL SELECT ... |
EXEC SQL INSERT ... | First Unit
(more SQL statements) | of Work
EXEC SQL COMMIT |
(more program logic)
EXEC SQL CONNECT TO database B USER :userid USING :pw |
EXEC SQL SELECT ... |
EXEC SQL DELETE ... | Second Unit
(more SQL statements) | of Work
EXEC SQL COMMIT |
(more program logic)
EXEC SQL CONNECT TO database A |
EXEC SQL SELECT ... |
EXEC SQL DELETE ... | Third Unit
(more SQL statements) | of Work
EXEC SQL COMMIT |
(more program logic)
EXEC SQL CONNECT RESET |
ERRCHK |
| Application
(check error information in SQLCA) | Cleanup
|
End Program