Ibm Db2

Db2 Application Development Overview/Application Development With Db2

Application development with DB2

Application programmers face constant challenges to efficiently utilize database resources to their full potential. This is especially the case with the growth of e-business and the increased use of the Internet. This e-book provides various application development options for DB2 using free distribution of DB2 Express-C.

This e-book is meant for you to use as a hands-on resource for developing DB2 UDB applications using XML, PHP, C/C++, Java, and .NET. We also cover the new Developer Workbench tool, which provides a comprehensive development environment for building data driven applications with DB2 9.

DB2 provides various flexible programming options. The DB2 product family supports all major standardized programming interfaces for data access. The IBM DB2 9 release features native support for storing, managing, and querying XML data, which is explored throughout this book.

You should keep in mind that there are key advantages to each interface, which need to be taken into consideration when choosing the programming interface and language. Various client side database application solutions using examples in PHP, C/C++, Java, and C# with DB2 9 Express-C are presented in later.

DB2 supported development environments The DB2 application development environment is composed of several elements:

Operating systemAIXHP-UXLinuxSolarisWindows There are various conditions you should be aware of when developing application solutions on these environments. These include OS level and for Linux, kernel level as well as glibc version.DB2 installation DB2 V8 Application Development client or DB2 9 Client installation is required for DB2 application development.Database application programming interface The interfaces include CLI/ODBC, embedded SQL, JDBC/SQLJ, OLE DB, Perl DBI, PHP, and ADO.NET. We discuss these in further detail in the next section.Programming language Supported programming languages for DB2 include C, C++, COBOL, FORTRAN, Java, Perl, PHP, REXX, and .NET.Transaction manager and development tools Keep in mind that using the XA (distributed transaction processing) interface requires transaction manager.There are a number of development tools available for DB2 database application development. We discuss IBM DB2 Development Add-In for Visua Studio (Visual Studio 2005 Add-In) and the Developer Workbench in this e-book.

DB2 supported interfaces IBM DB2 provides various programming interfaces for data access. These interfaces provide functions and methods that you can use to perform various interactions with the database.

Administrative API The DB2 Administrative API is primarily used for issuing DB2 administrative commands. It allows you to develop applications that you can use to administer and monitor DB2 instances and databases.

All the administrative API functions are derived from DB2 Command Line Processor (CLP) commands. You can find more information regarding DB2 API functions in the Administrative API reference, SC10-4231.

Embedded SQL The SQL statement can be embedded within a host language where SQL statements provide the database interface while the host programming language provides all remaining functionality.

The nature of embedded SQL applications requires a vendor specific precompiler first preprocesses the application code, then the resulting host language code is compiled and linked directly with the vendor’s library. DB2 supports C/C++, FORTRAN, COBOL, and Java (SQLJ) programming languages for embedded SQL.

SQL statements in embedded applications are independent of the host programming language used. Precompilers or SQLJ translators are required for embedded code (to generate the necessary packages or the serialized file for SQLJ) prior to generating the binaries through the native compiler. Precompilers for C, C++, COBOL, and FORTRAN as well as the SQLJ translator are provided with DB2 application development components. A precompiler processes the source file to separate SQL statements from non-SQL host languages, which are surrounded by special delimiters to generate native host language code and a package.

Embedded SQL applications can be categorized into two separate categories:

Static embedded SQL

In embedded SQL, you have to specify complete SQL statement structure. This means that all database objects (including columns and table) must be fully known at the precompile time with the exception of objects referenced in the SQL WHERE clause. However, all host variable data types must be still known at the precompiler time.

Dynamic embedded SQL

When not all of the database objects in the SQL statement are known at precompile time, use dynamic embedded SQL. The dynamic embedded SQL statement accepts a character string host variable and a statement name as argument. These character string host variables serve as placeholders for future SQL statements to be executed.

There are some differences in syntax between static and dynamic SQL statements. In dynamic SQL statements, the following conditions exist:

Comments are not allowed.The statement is not prefixed by EXEC SQL.The statement cannot end with the statement terminator except in the CREATE TRIGGER statement, which can contain a semicolon.

The dynamic embedded SQL is the ideal option for creating embedded SQL applications when the user does not have complete information about all underlying database objects at precompile time, wants to always use the most optimized access path based on current database statistics, or authorization of the SQL statement needs to be determined at runtime. Below it   demonstrates a general overview of embedded SQL creation

Embedded SQL creation overview

Driver support More common application solutions are developed using drivers. When accessing a database using various available drivers, driver manager is usually involved. The driver manager provides a set of industry standard interfaces (APIs) to access a data source using data source specific drivers. Applications utilizing drivers are compiled and linked with the driver manager’s libraries to invoke standardized APIs. DB2 currently provides support for a large number of drivers, including CLI/ODBC, ADO and OLEDB, JDBC, SQLJ, PERL DBI, and .NET data provider.

CLI/ODBC As part of the X/Open standard, Call Level Interface (CLI) and Open DataBase Connectivity (ODBC) standards had the same origin. The ODBC standards provide a set of interfaces for accessing the database. DB2 CLI driver can be used on its own to access a DB2 database or as an ODBC driver. The DB2 CLI driver is an ODBC 3 compliant driver and contains further functionality that is not specified in ODBC standards. In order to utilize additional functionality in CLI driver, the application program needs to be linked directly to CLI driver without the use of the ODBC driver manager.

Perl Database Interface (DBI) Perl is part of Open Source Standard and is one of the popular choices for use with Web services through the Common Gateway Interface (CGI). IBM DB2 provides support for Perl Scripts using Database Interface (DBI). DBI provides a set of standard class methods to access data sources using drivers calledDatabase Drivers (DBD). In order to develop Perl application solutions, you need to obtain Perl, the DBI module, and the DBD:DB2 driver from the Comprehensive Perl Archive network:

Building and installing the DBD:DB2 module requires the following:

For Linux and UNIX:

To build and install the DBD::DB2 module, you must have:

Perl 5.005_03 or later.DB2 V8 Application Development Client or DB2 9 client.A supported C compiler as documented under “Supported operating systems” on the Application development Web page.Set the DB2_HOME environment variable to the location of your DB2 instance. For example:
 bash# export DB2_HOME=/home/db2inst1/sqllib
Install the DBI module: bash# perl -MCPAN -e 'install DBI'Install the DBD::DB2 module: bash# perl -MCPAN -e 'install DBD::DB2'For Windows:

If you are using the ActiveState Perl distribution on Windows, you can install a binary version of the DBI and DBD::DB2 modules.

Prerequisites:ActivePerl 5.8 or laterDB2 client, Version 8.1 or later

OLE DB OLE DB is a data access service that was introduced with Microsoft®’s ActiveX® Data Objects (ADO). ActiveX Data Objects are a set of classes by which applications can access data from multiple sources that utilize a given layer. It provides consistent access to data sources exposed through OLE DB, XML, or third-party .NET data provider.

IBM’s OLE DB driver is called IBMDADB2. If IBMDADB2 is not explicitly specified, Microsoft’s OLE DB driver (MSDASQL) will be utilized by default. MSDASQL allows clients utilizing OLE DB to access third party (non-Microsoft SQL server) data sources using ODBC driver but does not guarantee full functionality of OLE DB driver.

JDBC driver Java is one of the most popular choices for application solutions. Write once, run everywhere is the theme of JAVA, which reduces development time and has made this technology an ideal choice for many.

The data source access is achieved through the JDBC programming interface in Java. JDBC driver specification defines four types of driver architectures:

Type 1

Drivers that implement the JDBC API as a mapping to another data access API, such as Open Database Connectivity (ODBC). Drivers of this type are generally dependent on a native library, which limits their portability. DB2 does not provide type 1 driver.

Type 2

Drivers that are written partly in the Java programming language and partly in native code. The drivers use a native client library specific to the data source to which they connect. Because of the native code, their portability is limited.

Type 3

Drivers that use a pure Java client and communicate with a server using a database-independent protocol. The server then communicates the client's requests to the data source. DB2 no longer includes a type 3 driver.

Type 4

Drivers that are pure Java and implement the network protocol for a specific data source.

The client connects directly to the data source. DB2 V8 provides type 2 (APP driver and JCC type 2), type 3 (NET driver), and type 4 (JCC type 4) drivers with APP and NET drivers being deprecated from V8 GA but still shipped (for V8).

DB2 9 provides type 2 (APP driver and JCC type 2) and type 4 (JCC type 4) drivers. The JDBC type 3 driver (NET) has been discontinued and is not shipped with V9. The DB2 JDBC type 2 (APP) driver was deprecated in DB2 V8 and will remain deprecated in V9. Support for DB2 JDBC type 2 (APP) will be removed in a future release.

Note that there have been no functional enhancements on deprecated DB2 JDBC type 2 (APP) and DB2 JDBC type 3 (NET) drivers since DB2 V7. All future Java application development on DB2 UDB should be done using DB2 JCC type 2 or DB2 JCC type 4 drivers. Below it summarizes the DB2 JDBC driver support on V8 and V9.

DB2 JDBC driver

SQLJ Along with host language embedded SQL type applications, there are also embedded Java applications, better known as SQLJ programs. SQLJ is a method for accessing DB2 from a Java application that supports static execution. Again, the benefits of a static execution are reduced resource consumption, improved diagnostics, improved security, and greater repeatability of SQL performance due to static access paths and plans. Everything you need to get from the data is already in the package bound at bind time.

SQLJ provides performance benefits of static query execution by embedding SQL queries into Java applications. SQLJ still utilizes the JDBC driver to access data source and is the layer above JDBC. SQLJ translator is used to process SQLJ source files with the extension .sqlj. It translates .sqlj source files into .java files and an SQLJ serialized profile into a form of .ser file. The serialized file contains all the SQL statements in original SQLJ source file. The translated resulting .java file will contain calls to SQLJ run-time libraries in place of SQL statements. In order to bind the application statically to a DB2 database, you use the DB2 profile customizer tool called db2sqljcustomize. The db2sqljcustomize connects and binds a package on the target database using the serialized profile. The package bound in the target database using db2sqljcustomize will contain sections which correspond to each SQL query in the serialized profile.

Commands associated with SQLJ:


sqlj is the translator that takes an embedded SQLJ program and creates a .ser file used for binding and a .java file that will also be compiled into byte code, as typical Java programs are compiled.


This command will take the .ser file from the sqlj step, connect to the database against which the application will be run, and bind four bind files for this application, all with different isolation levels.


This command can be used to rebind this application against other databases; for example, it can be used for moving the application from the test to the production database.

The following packages need to be imported for SQLJ:

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

PHPPHP: Hypertext Preprocessor (PHP) is an interpretive programming language intended for Web application development. IBM supports access to DB2 Database from PHP applications through two extensions:


The ibm_db2 extension offers a procedural application programming interface to create, read, update, and write database operations in addition to extensive access to the database metadata. It can be compiled with either PHP 4 or PHP 5.


The PDO_ODBC is a driver for the PHP Data Objects (PDO) extension that offers access to DB2 database through the standard object-oriented database interface introduced in PHP 5.1. It can be compiled directly against DB2 libraries.

.NET data provider The .NET developers have choices of incorporating ODBC .NET Data provider, OLE DB .NET Data provider, or DB2 .NET Data provider (native provider). We recommend that you first consider DB2 .NET Data provider when it comes to .NET Application development. There are a number of enhancements made in DB2 .NET Data provider in V9 for native XML support:

The ODBC .NET Data provider makes ODBC calls to DB2 data source using DB2 CLI driver. It has same keyword support and restrictions as that of DB2 CLI driver and can be used only with .NET Framework Version 1.1 or Version 2.0. This utilizes IBM DB2 ODBC (thus CLI) driver.The OLE DB .NET Data provider uses IBM DB2 OLE DB Driver (IBMDADB2). It has same keyword support and restrictions as that of DB2 OLE DB driver and can be used only with .NET Framework Version 1.1 or Version 2.0. This utilizes IBM DB2 OLE DB (IBMDADB2) driver.The DB2 .NET Data provider extends DB2 support for the ADO.NET interface. The DB2 managed provider implements the same set of standard ADO.NET classes and methods and it is defined under IBM.DATA.DB2 namespace.