This section is intended for programmers who are new to developing a DB2 application. It presents a model of the logical parts of a DB2 application. If you are an experienced SQL developer, you can ignore this section.
A DB2 application program is made up of several main parts: setup, connecting to the database, one or more transactions, disconnecting from the database, and ending the program. 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:
The body of every program contains the SQL statements which access and manage data. These statements constitute transactions. Among the statements included in this section are:
The end of the application program typically contains SQL statements that:
To set up a DB2 program, you typically declare the program variables used and declare the SQL Communications area.
All host program variables that interact with the database manager must be declared in an SQL declare section. An SQL declare section is a group of host program variable declarations that are preceded by the SQL statement BEGIN DECLARE SECTION and followed by the SQL statement END DECLARE SECTION. Host program variables declared in an SQL declare section are called host variables, and can be used in host-variable references in SQL statements. (Here, 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". Each column of every table is assigned a data type when the table is created.
Host variables can be used 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 a row that meets the search criteria is found, 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, and each data type can be related to a host language data type. 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:
C/C++:
long variable_name;
COBOL:
01 variable-name PICTURE S9(9) COMPUTATIONAL-5.
FORTRAN:
INTEGER*4 variable_name
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 what SQL data type that column has. Do this by querying the system catalog, which is a set of views containing information about all tables created in the database. This catalog is described in the SQL Reference.
After you have determined the data types, you can refer to the conversion charts in the host language chapters, and code the appropriate declarations. Figure 6 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. Other host variable data types and sizes are determined at run time based on the contents of the variable.
Figure 6 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 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/ANSI SQL92 or FIPS 127-2(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. 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/ANSI SQL92 or FIPS 127-2(2) standard, do not use the above statements or the INCLUDE SQLCA statement. For the alternative to coding the above statements, refer to "SQLSTATE and SQLCODE Variables" for C or C++ applications, "SQLSTATE and SQLCODE Variables" for COBOL applications, or "SQLSTATE and SQLCODE Variables" for FORTRAN applications.
Generally, other nonexecutable SQL statements are also part of this section of the program. These are discussed later in this manual, and in the SQL Reference. 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 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 distributed unit of work (DUOW). In this case, only one server can be the current server. For information on distributed unit of work, see "Distributed Unit of Work".
Your program can establish a connection to a database server either explicitly using the CONNECT statement, or implicitly to the default database server. See the SQL Reference for a discussion about how to use this statement, and about connection states. A default database server is established when the application requester is initialized. If implicit connect is available and an application process is started, it is implicitly connected to the default database server. It is good practice for the first SQL statement executed by an application program to be the CONNECT statement, to avoid accidentally executing SQL statements against the default database.
After the connection has been established, your program can issue SQL statements that manipulate data, define and maintain database objects, and initiate control operations, such as granting user authority, or committing changes to the database. A connection lasts until a CONNECT RESET, CONNECT TO, or DISCONNECT statement is issued. In a DUOW 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 DUOW (see "Distributed Unit of Work").
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.
The system ensures the consistency of data at the transaction level, by ensuring that either all operations within a transaction are completed, or none are completed. Suppose, for example, that money is to be deducted from one account and added to another. If both these updates are placed in a single transaction, and if a system failure occurs while they are in progress, then when the system is restarted, the data is automatically restored to the state it was in before the transaction began. If a program error occurs, all changes made by the statement in error are restored. Work done in the transaction prior to execution of the statement in error is not undone, 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 distributed unit of work (DUOW). For information on these topics, see "Remote Unit of Work" and "Distributed Unit of Work".
A transaction begins implicitly with the first executable SQL statement and ends by either a COMMIT or a ROLLBACK statement, or when the program ends.
The following are examples of statements that 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 such a statement is encountered after you end a transaction, it automatically starts another.
To end a transaction, you can use either the COMMIT statement to save its changes, or the ROLLBACK statement to ensure that these changes are not saved.
This statement ends the current transaction. It makes the database changes made during the current transaction visible to other processes.
Changes should be committed 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.
Your application programs should explicitly end any transactions prior to terminating. If you do not end transactions explicitly, DB2 Universal Database automatically commits all the changes made during the program's pending transaction when the program ends successfully, except on the Windows 95 and Windows NT operating systems. DB2 Universal Database rolls back the changes under the following conditions:
On the Windows 95 and Windows NT operating systems, if you do not explicitly commit the transaction, the database manager always rolls back the changes.
See "Ending the Program" and "Diagnostic Handling and the SQLCA Structure" for more information about program termination.
| Note: | The COMMIT statement has no effect on the contents of host variables. |
This statement ends the current transaction, and restores the data to the state it was in prior to the transaction beginning.
Notes:
The ROLLBACK statement should not be issued if a severe error occurs such as the loss of communications between the client and server applications, or if the database gets corrupted. You will receive a message indicating that you cannot issue a ROLLBACK statement in the event of a severe error. The only statement that can be issued after a severe error 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 of 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 a COMMIT or ROLLBACK is issuing by DB2 depends on a number of 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. The transaction is rolled back unless you explicitly commit the transaction using the COMMIT statement.
Figure 1 summarizes the general framework for a DB2 application program in pseudocode format. This framework must, of course, be tailored to suit your own program.
Figure 1. Pseudocode Framework for Coding Programs
Start Program -�
EXEC SQL BEGIN DECLARE SECTION |
DECLARE USERID FIXED CHARACTER (8) |
DECLARE PW FIXED CHARACTER (8) |
* | Application
* | Setup
(other host variable declarations) |
* |
* |
EXEC SQL END DECLARE SECTION |
EXEC SQL INCLUDE SQLCA |
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK |
* -�
*
* -�
EXEC SQL CONNECT TO database A USER :userid USING :pw |
EXEC SQL SELECT ... |
EXEC SQL INSERT ... | First Unit
* | of Work
EXEC SQL COMMIT |
* -�
*
* -�
EXEC SQL CONNECT TO database B USER :userid USING :pw |
EXEC SQL SELECT ... |
EXEC SQL DELETE ... | Second Unit
* | of Work
EXEC SQL COMMIT |
* -�
*
* -�
EXEC SQL CONNECT TO database A |
EXEC SQL SELECT ... |
EXEC SQL DELETE ... | Third Unit
* | of Work
EXEC SQL COMMIT |
* -�
*
* -�
EXEC SQL CONNECT RESET |
ERRCHK |
* | Application
(check error information in SQLCA) | Cleanup
* |
End Program -�
|
(2) FIPS 127-2 refers to Federal Information Processing Standards Publication 127-2 for Database Language SQL. ISO/ANSI SQL92 refers to American National Standard Database Language SQL X3.135-1992 and International Standard ISO/IEC 9075:1992, Database Language SQL.