Road Map to DB2 Programming
|
|
|
| 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.
|
| CLI Guide and Reference
| Explains how to develop applications that access DB2 databases using the
DB2 Call Level Interface.
|
| 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.
|
|
|
|
|
|
| basiccon.c
| Shows a basic connection.
|
| colpriv.c
| Lists column privileges.
|
| columns.c
| Lists all columns for table search string.
|
| compnd.c
| Demonstrates compound SQL.
|
| fetch.c
| Demonstrates a simple fetch sequence.
|
| getattrs.c
| Lists some common environment, connection, and statement
options/attributes.
|
| samputil.c
| Demonstrates utility functions used by most DB2 CLI samples.
|
| samputil.h
| Is the header file for samputil.c, included by most samples.
|
| setcolat.c
| Sets column attributes using SQLSetColAttributes().
|
| tables.c
| Lists all tables.
|
| xfetch.c
| Shows extended fetch, multiple rows per fetch.
|
|
|
DB2 CLI is a programming interface that your C or C++ applications
can use to access DB2 databases. DB2 CLI is based on the Microsoft Open Database Connectivity (ODBC)
specification, and the ISO CLI standard. Basing DB2 CLI on industry standards
might result in a shorter learning curve for application programmers who are
already familiar with these database interfaces.
When you use DB2 CLI, your application passes dynamic SQL statements as
function arguments to the database manager for processing. As such, DB2 CLI is
an alternative to embedded dynamic SQL, providing another way to access DB2
databases using dynamic SQL.
Your application will usually contain the following parts:
- Initialization
- Initialization allocates environment and connection handles, and connects
your application to the database. A handle is a variable that refers to a data
object controlled by the DB2 CLI. There are environment, connection,
statement, and descriptor handles.
Using handles frees your application from having to allocate and manage
global variables or data structures such as the SQLCA used in embedded SQL
applications.
Figure 4 shows the function call sequences for initialization.
Figure 4. DB2 CLI function calls for initialization and termination
- Transaction Processing
- Transaction processing consists of the five steps shown below and in Figure 5.
Figure 5. DB2 CLI function calls for transaction processing
|
- (1)
- Allocate statement handles before any SQL statements can be executed. A
statement handle refers to the data object that contains information about an
SQL statement managed by DB2 CLI. This includes information such as dynamic
parameters, cursor information, result values, and status information.
- (2)
- Prepare and execute SQL statements using one of two methods. The
transaction starts implicitly with the first access to the database.
- Prepare then execute, which splits the preparation of the statement from
the execution. You would use this method if the statement will be executed
repeatedly, usually with different parameter values.
- Execute directly, which combines the prepare and execute steps into one.
You would use this method if the statement will be executed only once. This
avoids having to call two functions to execute the same statement.
- (3)
- Process the results of the SQL statement. The method you use depends on
the type of SQL statement:
- Receive Query Results: If the statement is a query, you usually need
to perform the following steps to retrieve each row of the result set:
- Establish (describe) the structure of the result set, number of columns,
column types and lengths, using SQLNumResultCols() and
SQLDescribeCol() or SQLColAttribute().
- Optionally bind application variables to columns in order to receive the
data, using SQLBindCol().
- Repeatedly fetch the next row of data, and receive it into the bound
application variables, using SQLFetch().
- Optionally retrieve columns that were not previously bound, using
SQLGetData() after each successful fetch.
- Update Data: If the statement is modifying data, your application
needs only to check for diagnostic messages. Your application can use
SQLRowCount() to get the number of rows affected by the SQL
statement.
- Other: If the statement neither queries nor modifies the data, your
application needs only to check for diagnostic messages.
- (4)
- Free the statement handle ending processing for that statement.
- (5)
- Commit (complete) or roll back (undo) the transaction. The transaction
ends at this point.
- Termination
- Termination disconnects your application from the database, and frees the
handles.
Figure 4 shows the function call sequences for termination.
You do not need to precompile or bind DB2 CLI applications because they use
common access packages provided with DB2. You simply compile and link your
application.
However, before your DB2 CLI or ODBC applications can access DB2
databases, the DB2 CLI bind files must be bound on each DB2 database that will
be accessed. This occurs automatically on the first connection to the
database, but we recommend that the database administrator bind the bind files
from one client on each platform that will access a DB2 database.
For example, suppose you have OS/2, AIX, and Windows 95 clients that
each access two DB2 databases. The administrator must bind the bind files from
one OS/2 client on each database that will be accessed. Next, the
administrator must bind the bind files from one AIX client on each database
that will be accessed. Finally, the administrator must do the same on one
Windows 95 client.
You can develop dynamic applications using either embedded dynamic SQL
statements or DB2 CLI. In both cases, SQL statements are prepared and
processed at run time. Each method has unique advantages listed below.
- Portability
- DB2 CLI applications use a standard set of functions to pass SQL
statements to the database. All you need to do is compile and link DB2 CLI
applications before you can run them. In contrast, you must precompile
embedded SQL applications, compile them, and then bind them to the database
before you can run them. This process effectively ties your application to a
particular database.
- No binding
- You do not need to bind individual DB2 CLI applications to each database
they access. You only need to bind the bind files that are shipped with DB2
CLI once for all your DB2 CLI applications. This can significantly reduce the
amount of time you spend managing your applications.
- Extended fetching and input
- DB2 CLI functions enable you to retrieve multiple rows in the database
into an array with a single call. They also let you execute an SQL statement
many times using an array of input variables.
- Consistent interface to catalog
- Database systems often contain catalog tables that have information about
the database and its users. The form of these catalogs can vary among systems.
DB2 CLI provides a consistent interface to query catalog information about
tables, columns, foreign and primary keys, and user privileges. This shields
your application from catalog changes across releases of database servers, and
from differences among database servers. You don't have to write catalog
queries that are specific to a particular server or product version.
- Extended data conversion
- DB2 CLI automatically converts data between SQL and C data types. For
example, fetching any SQL data type into a C char data type
converts it into a character-string representation. This makes DB2 CLI
well-suited for interactive query applications
- No global data areas
- DB2 CLI eliminates the need for application controlled, often complex
global data areas, such as SQLDA and SQLCA, typically associated with embedded
SQL applications. Instead, DB2 CLI automatically allocates and controls the
necessary data structures, and provides a handle for your application to
reference them.
- Retrieve result sets from stored procedures
- DB2 CLI applications can retrieve multiple rows and result sets generated
from a stored procedure residing on the server.
- Scrollable cursors
- DB2 CLI supports server-side scrollable cursors that can be used in
conjunction with array output. This is useful in GUI applications that display
database information in scroll boxes that make use of the Page Up, Page Down,
Home and End keys. You can declare a read-only cursor as scrollable and then
move forwards or backwards through the result set by one or more rows. You can
also fetch rows by specifying an offset from the current row, the beginning or
end of a result set, or a specific row you bookmarked previously.
- Supports many programming languages
- Embedded SQL supports more that just C and C++. This might be an advantage
if you prefer to code your applications in another language.
- Consistent with static SQL
- Dynamic SQL is generally more consistent with static SQL. If you already
know how to program static SQL, moving to dynamic SQL might not be as
difficult as moving to DB2 CLI. You might prefer dynamic SQL if you also need
to use static SQL statements in your dynamic SQL applications, instead of
mixing static SQL and DB2 CLI.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
[ DB2 List of Books |
Search the DB2 Books ]
