7.2 Self-build application

---

7.2 Self-build application

Self-build (in-house developed) application is unique in every case. There are variety of languages used in applications and each one can have its unique way of using APIs. In this section we explain the necessary steps in converting self-build applications from Oracle to DB2 UDB, and we provide some examples in C/C++ and Java, which show you how to convert the database calls.

Please note that the examples included in this chapter are excerpts from the actual programs, and cannot be compiled and executed by themselves.

7.2.1 Converting Oracle Pro*C applications to DB2 UDB

While many aspects of DB2 UDB application development underwent changes in recent years (stored procedures from C/COBOL/Java to SQL procedure language, support for PL/SQL in user-defined functions, triggers, and in-line SQL, and an enriched set of (built-in functions, etc.), support for embedding SQL into other host languages (C/C++) practically has not changed. This has resulted in many difficulties during migrations of applications from Oracle to DB2 UDB.

This chapter explains the steps necessary during application conversion to programs with embedded DB2 SQL calls.

Connecting to database

There is a difference in how C programs connect to the database. In Oracle each instance (service name) can manage only one database. DB2 instances can be used to manage multiple databases, thus the database name should be implicitly provided by connection statement.

In order to connect to the Oracle database, you need to specify Oracle user and password for that user:

    EXEC SQL CONNECT :user_name IDENTIFIED BY :password; 

In DB2 UDB, you need to specify the database name, user ID, and password for that user ID. So, the above statement will be converted as:

    EXEC SQL CONNECT TO :dbname USERID :userid PASSWORD :password; 

Please note that dbname, userid and password need to be declared as host variables.

Host variable declaration

Host variables are C or C++ language variables that are referenced within SQL statements. They allow an application to pass input data to and receive output data from the database manager. After the application is precompiled, host variables are used by the compiler as any other C/C++ variable.

Host variables should not only be compatible with DB2 data types (accepted by DB2 precompiler), but also must be acceptable for the programming language compiler.

As the C program manipulates the values from the tables using host variables, the first step is to convert Oracle table definition to DB2 data types; see Appendix B, "Data types" on page 365 for details. Please note that this mapping is one to many as it depends on the actual usage of data. For example, Oracle DATE data can be converted to DB2 DATE, if it only stores the actual date, but it needs to be converted to DB2 TIMESTAMP if it stores DATE and TIME.

The next step is to match DB2 data types with C data types. The table in Appendix B, "Data types" shows mapping between data types.

All host variables in a C program need to be declared in a special declaration section, so that the DB2 precompiler can identify the host variables and the data types:

    EXEC SQL BEGIN    DECLARE SECTION;       char emp_name[31] = {'\0'};       sqlint32  ret_code = 0;    EXEC SQL END DECLARE SECTION; 

Within this declaration section, there are rules for host variable data types that are different from Oracle precompiler rules. Oracle precompiler permits host variables to be declared as VARCHAR. VARCHAR[n] is a pseudo-type recognized by the Pro*C precompiler. It is used to represent blank-padded, variable-length strings. Pro*C precompiler will convert it into a structure with a 2-byte length field followed by a n-byte character array. DB2 requires usage of standard C constructs. So, the declaration for the variable emp_name VARCHAR[25] needs to be converted as follows:

    struct emp_name {       short  var_len;       char   var_data[25] }; 

Or, as mentioned above, the use of a char emp_name[n] is also permitted for VARCHAR data. Variables of user-defined types (using typedef) in PRO*C need to be converted to the source data type. For example, type theUser_t has been declared to host values from Oracle object type:

    typedef struct user_s          {short int userNum;            char userName[25];            char userAddress[40];          } theUser_t; 

In Pro*C program, you can have host variables declared as theUser_t:

    EXEC SQL BEGIN DECLARE;              theUser_t *myUser;         EXEC SQL END DECLARE SECTION; 

To use this host variable for DB2, you would need to take this out of the EXEC SQL DECLARE SECTION and define the host variable MyUser as a structure.

DB2 allows for the host variable to be declared as a pointer with the following restriction:

• If a host variable is declared as a pointer, no other host variable may be declared with that same name within the same source file.

The host variable declaration char *ptr is accepted, but it does not mean a null-terminated character string of an undetermined length. Instead, it means a pointer to a fixed-length, single-character host variable. This may not be what was intended for the Oracle host variable declaration.

It is recommended that sqlint32 and sqlint64 are used for INTEGER and BIGINT host variables, respectively. By default, the use of long host variables results in the precompiler error SQL0402 on platforms where long is a 64-bit quantity such as 64 BIT UNIX. Use the PREP option LONGERROR NO to force DB2 to accept long variables as acceptable host variable types and treat them as BIGINT variables.

Oracle host tables

In Pro*C programs, you can declare host variables using arrays, then declare a cursor you want to get results from. You can then issue a fetch statement that will get all rows from the cursor into that host array.

Here is a fragment of PRO*C that demonstrates this method:

    EXEC SQL BEGIN DECLARE SECTION;    long int     dept_numb[10];    char         dept_name[10][14];    char         v_location[12];    EXEC SQL END DECLARE SECTION;    /* …… */    EXEC SQL DECLARE CUR1 CURSOR FOR         SELECT DEPTNUMB, DEPTNAME          FROM org_table         WHERE LOCATION = :v_location;    /*……. */    EXEC SQL FETCH CUR1 INTO :dept_num, :dept_name; 

The last statement will get all 10 rows from the cursor into arrays.

As DB2 does not support arrays for the host variable declaration, the above code needs to be converted as follows:

    EXEC SQL BEGIN DECLARE SECTION;    sqlint32     h_dept_numb = 0;    char         h_dept_name[14] = {'\0'};    char         v_location[12] = {'\0'};    EXEC SQL END DECLARE SECTION;    /* move array out of DECLARE section - just C variables */    long int     dept_numb[10];    char         dept_name[10][14];    short int    i = 0;    /* …… */    EXEC SQL DECLARE CUR1 CURSOR FOR         SELECT DEPTNUMB, DEPTNAME          FROM org_table         WHERE LOCATION = :v_location;    /*we need Fetch one row at the time and move to corresponding          member of array */    for (i=0;i<11;i++){        EXEC SQL FETCH CUR1 INTO :h_dept_num, :h_dept_name;        if (SQLCODE == 100) {          break;        }        dept_numb[i] = h_dept_numb;        strcpy(dept_name[i], h_dept_name);    } 

Exception handling

The mechanisms for trapping errors are quite similar between Oracle and DB2 UDB, using the same concept of separating error routines from the mainline logic. There are three different WHENEVER statements that could be used to define program behavior in case of an error in DB2:

 EXEC SQL WHENEVER SQLERROR GOTO error_routine; EXEC SQL WHENEVER SQLWARNING CONTINUE; EXEC SQL WHENEVER NOT FOUND not_found_routine; 

Although the WHENEVER statement is prefixed by EXEC SQL like other SQL statements, it is not an executable statement. Instead, a WHENEVER statement causes the precompiler to generate code in a program to check the SQLCODE attribute from the SQLCA after each SQL statement, and to perform the action specified in the WHENEVER statement. SQLERROR means that an SQL statement returns a non-positive SQLCODE indicating an error condition. SQLWARNING indicates a non-negative SQLCODE (except +100), while NOT FOUND specifies SQLCODE = +100, indicating that no data rows were found to satisfy a request.

A compilation unit can contain as many WHENEVER statements as necessary, and they can be placed anywhere in the program. The scope of one WHENEVER statement reaches from the placement of the statement in the file onward in the character stream of the file until the next suitable WHENEVER statement is found or end-of-file is reached. No functions or programming blocks are considered in that analysis. For example, you may have two different SELECT statements, one must return at least one row, and the other may not return any. You will need two different WHENEVER statements:

    EXEC SQL WHENEVER NOT FOUND GOTO no_row_error;         EXEC SQL SELECT  address                            INTO  :address                            FROM   test_table                           WHERE phone = :pnone_num;         ……..         EXEC SQL WHENEVER NOT FOUND CONTINUE;         EXEC SQL SELECT  commis_rate                           INTO :rate :rateind                            WHERE prod_id = :prodId;          if (rateind == -1) rate = 0.15;          …… 

Oracle precompiler also supports DO and STOP statements as actions in a WHENEVER statement; those are not supported by DB2 precompiler and need to be converted to GOTO.

Another alternative is to check explicitly the SQLCODE after each EXEC SQL statement because that allows more context-sensitive error handling.

Error messages and warnings

The SQL Communication Area (SQLCA) data structure in DB2 is similar to the same structure of Oracle. SQLCA provides information for diagnostic checking and event handling.

To get the full text of longer (or nested) error messages, you need the sqlglm() function:

    sqlglm(message_buffer, &buffer_size, &message_length); 

where message_buffer is the character buffer in which you want Oracle to store the error message; buffer_size specifies the size of message_buffer in bytes; Oracle stores the actual length of the error message in *message_length. The maximum length of an Oracle error message is 512 bytes.

DB2 UDB provides its user with a special run-time API function to return an error message based on SQLCODE:

    rc=sqlaintp(msg_buffer, 1024, 80, sqlca.sqlcode); 

Where 80 stands for the number of characters after which a line break will be inserted in the message. DB2 will search for work-boundaries to place such a line break. 1024 specifies the length of the message buffer, e.g. char msg_buffer[1024]. As a result of invoking this function, the allocated buffer will contain the descriptive error message, e.g.:

    SQL0433N Value "TEST VALUES" is too long. SQLSTATE=22001. 

If you need more information about a particular error, DB2 UDB provides an API function that returns an extended message associated with the specific SQLSTATE:

    rc=sqlogstt(msg_sqlstate_buffer, 1024, 80, sqlca.sqlcode); 

As a result of invoking this function, char msg_sqlstate_buffer[1024] will contain, for example, the following message:

    SQLSTATE 22001: Character data, right truncation occurred; for example, an    update or insert value is a string that is too long for the column, or    datetime value cannot be assigned to a host variable, because it is too    small. 

Passing data to a stored procedure from a C program

In Oracle, in order to invoke a remote database procedure, the following statements are used:

    EXEC SQL EXECUTE           BEGIN             Package_name.SP_name(:arg_in1,:arg_in2, :status_out);           END;    END-EXEC; 

The value transfer between the calling environment and the stored procedure may be achieved through arguments. You can choose one of three modes for each argument: IN, OUT or INOUT. For example, the above stored procedure may be declared as:

    CREATE PACKAGE package_name IS         PROCEDURE  SP_name(                 agr_in1 IN NUMBER ,                 arg_in2 IN CHAR(30),                 status_out OUT NUMBER); 

When this stored procedure is invoked, values passed from the calling program will be accepted by the stored procedure correspondingly.

DB2 client application invokes stored procedure by using the CALL statement. The CALL statement can pass parameters to the stored procedure and receive parameters returned from the stored procedure. It has the following syntax:

    CALL procedure_name (:parm1, …:parmN); 

As with all SQL statements you prepare CALL statement with parameters markers an then supply values for the markers using SQLDA:

    CALL procedure_name USING DESCRIPTOR host_var; 

The SQLDA is very helpful if you have an unknown number of host variables or very many variables - like 100 or more. Managing single variables in those cases can be very troublesome.

In order to invoke stored procedure from C client the following need to be in place

• a stored procedure need to be created and registered with database

• a host variable or parameter marker to each IN and INOUT parameter of the stored procedure should be declared and initialized

Consider an example. The program must give a raise to each employee whose current salary is less than some value. The program will pass that value to a stored procedure, perform an update and return back the status. Client code in C will look as shown in Example 7-1.

Example 7-1: Passing data to store procedure

 #include <sqlenv.h> main() {     EXEC SQL BEGIN DECLARE SECTION;          Sqlint32  salary_val=0;          Sqlint16 salind=1;          Sqlint16  status=0;          Sqlint16  statind=0;     EXEC SQL END DECLARE SECTION;     EXEC SQL INCLUDE SQLCA;     EXEC SQL CONNECT TO sample;     EXEC SQL WHENEVER SQLERROR GOTO err_routine;     salary_val = getSalaryForRaise();     statind = -1;   /* set indicator variable to -1 */                     /* for status as output-only variable */      EXEC SQL CALL raiseSal(:salary_val :salind, :status :statind);       if (status == 0){           printf  (" The raises has been successfully given \n ");           EXEC SQL COMMIT;        }        else           if (status ==1)               printf  (" NO input values has been provided.\n ");           else               if (status == 2)                   printf("Stored procedure failed.\n");     err_routine:         printf  (" SQL Error, SQLCODE = \n ", SQLCODE);         EXEC SQL ROLLBACK; } 

Note that all host variables that are used as parameters in the statement are declared and initialized in EXEC SQL DECLARE SECTION.

Building C/C++ DB2 application.

DB2 UDB provides sample build scripts for precompiling, compiling, and linking C-embedded SQL programs. These are located in the sqllib/samples/c directory, along with sample programs that can be built with these files. This directory also contains the embprep script used within the build script to precompile a *.sqc file.

Build files are provided by DB2 UDB for each language on supported platforms where the types of programs they build are available in the same directory as the sample programs for each language. These build files, unless otherwise indicated, are for supported languages on all supported platforms. The build files have the .bat (batch) extension on Windows, and have no extension on UNIX platforms. For example, bldmapp.bat is a script to build C/C++ application on Windows.

DB2 UDB also provides utilemb.sqc and utilemb.h files, containing functions for error handling. In order to use utility functions, the utility file must first be compiled, and then its object file linked in during the creation of the target program's executable. Both the makefile and build files in the samples directories do this for the programs that require error-checking utilities.

For more information on building C applications, see IBM DB2 UDB Application Development Guide: Building and Running Applications.

7.2.2 Converting Oracle Java applications to DB2 UDB

For Java programmers, DB2 UDB offers two application programming interfaces (APIs), JDBC and SQLj.

JDBC is a mandatory component of the Java programming language as defined in the Java 2, Standard Edition (J2SE) specification. To enable JDBC applications for DB2 UDB, an implementation of the various Java classes and interfaces, as defined in the standard, is required. This implementation is known as a JDBC driver. DB2 UDB offers a complete set of JDBC drivers for this purpose. The JDBC drivers are categorized as the legacy CLI drivers or the new Universal JDBC Drivers.

SQLJ is a standard development model for data access from Java applications. The SQLJ API is defined within the SQL 1999 specification. The new Universal JDBC Driver provides support for both JDBC and SQLJ APIs in a single implementation. JDBC and SQLJ can interoperate in the same application. SQLJ provides the unique ability to develop using static SQL statements and control access at the DB2 UDB package level.

The Java code conversion is rather easy. The API itself is well defined and database independent. For instance, the database connection logic is encapsulated in standard J2EE DataSource objects. The Oracle or DB2 UDB specific things like user name, database name, etc. are then configured declaratively within the application.

However, there is the need to change your Java source code regarding:

• The API driver (JDBC or SQLJ)

• The database connect string

• Oracle proprietary SQLs like CONNECT BY for recursive SQL, the usage of DECODE() or SQL syntax like the (+) operator instead of LEFT/RIGHT OUTER JOIN. MTK provide support here with the SQL Translator.

• Remove or simulate proprietary optimizer hints in SQL queries.

Java access methods to DB2

DB2 UDB has rich support for the Java programming environment. You can access DB2 data by putting the Java class into a module in one of the following ways:

• DB2 Server

  • Stored procedures (JDBC or SQLJ)

  • SQL functions or user-defined functions (JDBC or SQLJ)

• Browser

  • Applets based on JDBC (JDBC)

• J2EE Application Servers (such as WebSphere® Application Server)

  • Java ServerPages (JSPs) (JDBC)

  • Servlets (SQLJ or JDBC)

  • Enterprise JavaBeans (EJBs) (SQLJ or JDBC)

Available JDBC driver for DB2 UDB

DB2 UDB V8.1 supports the JDBC 2.1 and JDBC 3.0. Table 7-1 shows you the JDBC drivers delivered by IBM. An overview of all available JDBC drivers can be found at:

Table 7-1: JDBC driver

Type	Driver URL
type 2	COM.ibm.db2.jdbc.app.DB2Driver (only for applications)
type 3	COM.ibm.db2.jdbc.net.DB2Driver (only for applets)
type 4	com.ibm.db2.jcc.DB2Driver (for applications and applets)

http://servlet.java.sun.com/products/jdbc/drivers

The type 3 and 4 drivers require you to provide the user ID, password, host name and a port number. For the type 3 driver, the port number is the applet server port number. For the type 4 driver the port number is the DB2 UDB server port number. The type 2 driver implicitly uses the default value for user ID and password from the DB2 client catalog, unless you explicitly specify alternative values. The JDBC Type 1 driver is based on a JDBC-ODBC bridge. Therefore, an ODBC driver can be used in combination with this JDBC driver (provided by Sun). IBM does not provide a Type 1 driver, and it is not a recommended environment.

After coding your program, compile it as you would with any other Java program. You do not need to perform any special precompile or bind steps.

JDBC driver declaration

In order to connect from a Java application to an Oracle database using the OCI driver, you have to import the Oracle driver calls, register the driver manager, and connect with your user ID, the password, and the database name.

In DB2 UDB, it is not necessary to import a DB2 JDBC library. The registration and connection to DB2 UDB is similar to Example 7-2. Use the proper JDBC driver URL and connection string you need as explained in Table 7-1.

Example 7-2: Oracle JDBC connection

 import java.sql.*; import java.io.*; import oracle.jdbc.driver.*; class rsetClient {   public static void main (String args []) throws SQLException   {     // Load the driver     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());     // Connect to the database     Connection conn =       DriverManager.getConnection ("jdbc:oracle:oci8:@oracle","uid","pwd");     // ...   } } 

Example 7-3: DB2 JDBC connection

 import java.sql.*; class rsetClient {   public static void main (String args []) throws SQLException {    // Load DB2 JDBC application driver    try     {        Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");     }     catch (Exception e)     {          e.printStackTrace();     }     // Connect to the database     Connection conn =       DriverManager.getConnection("jdbc:db2:dbname","uid","pwd");     // ...   } } 

Stored procedure call

The calls of stored procedure differ in the handling of input parameters and output parameters between Oracle and DB2 UDB. The following examples explain the different kinds of procedure calls, and the usage of parameters and result sets.

Stored procedure with input parameter

We assume a stored procedure defined in Oracle as:

    CREATE OR REPLACE PROCEDURE sp_testcall_1( parm1 IN INTEGER                                              ,parm2 IN INTEGER) 

and in DB2 UDB as:

    CREATE PROCEDURE sp_testcall_1( IN parm1 INTEGER                                   ,IN parm2 INTEGER ) 

The procedures have two input parameters and no output parameter. There is no difference in the call between Oracle and DB2. In both cases you have to set the parameter values before you call and execute the procedure.

Example 7-4: Java call of Oracle or DB2 UDB procedure with input parameter

 String SP_CALL = "{call sp_testcall_1(?,?)}"; // Connect to the database Connection conn =   DriverManager.getConnection (url, userName, password); CallableStatement stmt; try {     stmt = conn.prepareCall(SP_CALL);     stmt.setInt(1,10);     stmt.setInt(2,15);     stmt.execute();     // ... } 

Stored procedure with result set

The next example shows a procedure without input parameter, but a result set as output parameter. The result set is an opened cursor defined within the procedure. The rows are fetched in the Java application with a loop.

The Oracle stored procedure is defined as:

    TYPE CursorType IS REF CURSOR;    CREATE PROCEDURE sp_testcall_3(oCursor OUT CursorType) AS    BEGIN     open oCursor for select last_name from employees;    END; 

The output parameter type is registered as CURSOR before the procedure is called.

The corresponding DB2 procedure looks like:

    CREATE PROCEDURE db2_spcall_v3    RESULT SETS 1    LANGUAGE SQL    BEGIN       DECLARE c1 CURSOR WITH RETURN FOR         SELECT last_name         FROM employees;       OPEN c1;    END! 

The result set definition in SQL PL is different than Oracle's PL/SQL. You have to specify the amount of expected result sets.

With DB2 UDB, you do not need to register the result set with the method registerOutParameter() in the Java application. To get the result set you call the method getResultSet() instead of getObject() as in Example 7-5.

Example 7-5: Java call of Oracle procedure with result set

 String SP_CALL = "{call sp_testcall_3}"; // Connect to the database Connection conn =   DriverManager.getConnection (url, userName, password); try {     CallableStatement stmt = conn.prepareCall(SP_CALL);     stmt.registerOutParameter (1, OracleTypes.CURSOR);     stmt.executeUpdate();     ResultSet rs = (ResultSet) stmt.getObject(1);     while(rs.next())     {         // ...     } } 

Example 7-6: Java call of DB2 UDB procedure with result set

 String SP_CALL = "{call db2_spcall_v3}"; // Connect to the database Connection conn =       DriverManager.getConnection (url, userName, password); try {         CallableStatement stmt = conn.prepareCall(SP_CALL);         ResultSet rs = null;         stmt.execute();         rs = stmt.getResultSet();         while(rs.next())         {             // ...         } } 

Stored procedure with input parameter and result set

Example 7-7 is a combination of Example 7-4 and Example 7-5. Note the numbering of the parameters. The first input parameter value2 is numbered with 2, the result set rs is numbered with 1.

Example 7-7: Java call of Oracle procedure with input parameter and result set

 private static final String SP_CALL = "{call sp_testcall4 (?) }"; CallableStatement stmt1 = conn.prepareCall(SP_CALL); Stmt1.registerOutParameter(1, OracleTypes.CURSOR); Stmt1.execute(); ResultSet rs = (ResultSet) stmt1.getObject(1); while(rs.next()) {     int value1 = rs.getInt(1);     stmt2.setInt(2, value2);     stmt2.execute();     ResultSet rs = (ResultSet) stmt1.getObject(1);     // ... } 

You define with DB2 UDB the input parameter and the result set as we show in Example 7-4 and Example 7-6. The numbering of the input parameter begins with 1, independent from an expected result set.

Example 7-8: Java call of DB2 UDB procedure with input parameter and result set

 String SP_CALL = "{call db2_spcall_v4(?)}"; Connect to the database     Connection conn =       DriverManager.getConnection (url, userName, password); try {         CallableStatement stmt = conn.prepareCall(SP_CALL);         stmt.setInt(1, emp_id);         ResultSet rs = null;         stmt.execute();         rs = stmt.getResultSet();         while(rs.next())         {             System.out.println (rs.getString (1));             // ...         } } 

Stored procedure converted from a function

The call of an Oracle function is similar to the call of a procedure with input parameter and a result set. The function is defined as:

    CREATE TYPE CursorType IS REF CURSOR;    CREATE FUNCTION sp_testcall_4(v_num IN INTEGER)                    RETURN CursorType 

As described in Chapter 5, "Conversion reference" on page 149, you have to convert the Oracle function to a DB2 procedure. The migrated DB2 procedure may look like the previous Example 7-9 with an input parameter and a result set as an output parameter.

Example 7-9: Java of Oracle function with input parameter and result set

 String SP_CALL = "{? = call sp_testcall_4(?)}"; // Connect to the database Connection conn =   DriverManager.getConnection (url, userName, password); try {     CallableStatement stmt = conn.prepareCall(SP_CALL);     stmt.registerOutParameter (1, OracleTypes.CURSOR);     stmt.setInt(2, 6);     stmt.execute();     ResultSet rs = (ResultSet) stmt.getObject(1);     while(rs.next())     {         // ...     } } 

7.2.3 Converting Oracle Call Interface applications

For applications using the Oracle Call Interface (OCI) you may want to consider rewriting them by using CLI or ODBC. The OCI is specific to the Oracle database and cannot be used with any other databases.

In most cases, you can replace OCI functions with the appropriate CLI/ODBC functions, followed by relevant changes to the supporting program code. The remaining non-OCI program code should require minimal modification. The examples in this chapter show a comparison of the OCI and CLI/ODBC statements required for establishing a connection to an Oracle and DB2 UDB database.

Introduction to CLI

DB2 Call Level Interface (DB2 CLI) is IBM's callable SQL interface to the DB2 family of database servers. It is a C and C++ application programming interface for relational database access that uses function calls to pass dynamic SQL statements as function arguments. It is an alternative to embedded dynamic SQL, but unlike embedded SQL, DB2 CLI does not require host variables or a precompiler.

DB2 CLI is based on the Microsoft Open Database Connectivity (ODBC) specification, and the International Standard for SQL/CLI. These specifications were chosen as the basis for the DB2 Call Level Interface in an effort to follow industry standards, and to provide a shorter learning curve for those application programmers already familiar with either of these database interfaces. In addition, some DB2 specific extensions have been added to help the application programmer specifically exploit DB2 features.

The DB2 CLI driver also acts as an ODBC driver when loaded by an ODBC driver manager. It conforms to ODBC 3.51.

Comparison of DB2 CLI and Microsoft ODBC

Figure 7-1 compares DB2 CLI and the DB2 ODBC driver. The left side shows an ODBC driver under the ODBC Driver Manager, and the right side illustrates DB2 CLI, the callable interface designed for DB2 UDB specific applications.

Figure 7-1: DB2 CLI and ODBC

In an ODBC environment, the Driver Manager provides the interface to the application. It also dynamically loads the necessary driver for the database server that the application connects to. It is the driver that implements the ODBC function set, with the exception of some extended functions implemented by the Driver Manager. In this environment, DB2 CLI conforms to ODBC 3.51.

For ODBC application development, you must obtain an ODBC Software Development Kit. For the Windows platform, the ODBC SDK is available as part of the Microsoft Data Access Components (MDAC) SDK, available for download from http://www.microsoft.com/data/ For non-Windows platforms, the ODBC SDK is provided by other vendors.

In environments without an ODBC driver manager, DB2 CLI is a self-sufficient driver, which supports a subset of the functions provided by the ODBC driver. Appendix C, "Oracle Call Interface (OCI) mapping" on page 377 summarizes the two levels of support. The CLI and ODBC function summary provides a complete list of ODBC functions and indicates if they are supported.

Setting up the CLI environment

Runtime support for DB2 CLI applications is contained in all DB2 UDB clients. Support for building and running DB2 CLI applications is contained in the DB2 Application Development (DB2 AD) Client.

The CLI/ODBC driver will auto bind on the first connection to the database, provided the user has the appropriate privilege or authorization. The administrator may want to perform the first connect or explicitly bind the required files.

Procedure

In order for a DB2 CLI application to successfully access a DB2 database:

1. Ensure the DB2 CLI/ODBC driver was installed during the DB2 client install.

2. Catalog the DB2 database and node if the database is being accessed from a remote client. On the Windows platform, you can use the CLI/ODBC settings GUI to catalog the DB2 database.

3. Optional: Explicitly bind the DB2 CLI/ODBC bind files to the database with the command:

       db2 bind ~/sqllib/bnd/@db2cli.lst blocking all messages cli.msg\               grant public 

   On the Windows platform, you can use the CLI/ODBC settings GUI to bind the DB2 CLI/ODBC bind files to the database.

4. Optional: Change the DB2 CLI/ODBC configuration keywords by editing the db2cli.ini file, located in the sqllib directory on Windows, and in the sqllib/cfg directory on UNIX platforms.

   On the Windows platform, you can use the CLI/ODBC settings GUI to set the DB2 CLI/ODBC configuration keywords.

Change of OCI database calls

All Oracle Call Interface (OCI) calls in your application need to be changed to CLI calls. The program flow is retaining, but you need to modify the definition and processing of database handles. There may not be an exact match in the conversion process. Your program code might require additional revisions to obtain similar functionality.

The following examples show you the different SQL statements in order to connect to a database. In Oracle you need to define variables for the environment handles as well as the database name, username, and password:

    ociRC = OCILogon( env_hp,                    // environment handle                      err_hp,                    // error handle                      &svc_hp,                   // service context                      user_name,                 // username                      strlen (user_name),        // length of username                      password,                  // password                      strlen (password),         // length of password                      db_name                    // database name                      strlen (db_name));         // length of database name 

In DB2 UDB you also need to specify the connection handle, database name, username, and password. So, the OCI statement will be converted as:

    cliRC = SQLConnect( *pHdbc,                  // connection handle                        db_name,                 // database name                        strlen (db_name),        // length of database name                        user_name,               // username                        strlen (user_name),      // length of username                        password,                // password                        strlen (password));      // length of password 

Appendix C, "Oracle Call Interface (OCI) mapping" on page 377 gives you a mapping of the most important Oracle8 OCI calls to the closest DB2 CLI equivalents. Refer to the Oracle8i Server Application Development guide and to the DB2 UDB Call Level Interface Guide and Reference for details on the OCI and CLI functions.

The following classes of OCI functions have no equivalents in DB2 CLI. The functionality must be implemented either in SQL or in C (or C++) directly:

• Navigational functions

   OCIObject__() OCICache__() 

• Datatype mapping and manipulation functions

   OCIColl__() OCIDate__() OCINumber__() OCIString__ 

  etc.

  However, CLI performs conversion of data between data types wherever possible.

• External procedure functions

   OCIExtProc__() 

Error handling and diagnostics

Diagnostics refers to dealing with warning or error conditions generated within an application. There are two levels of diagnostics returned when calling DB2 CLI functions:

• Return codes

• Detailed diagnostics consist of SQLSTATEs, messages, and SQLCA

Each CLI function returns the function return code for a basic diagnosis. The functions SQLGetDiagRec() and SQLGetDiagField() provide more detailed diagnostic information. If the diagnostic originates at the DBMS, the SQLGetSQLCA() function provides access to the SQLCA. This arrangement lets applications handle the basic flow control based on return codes, and uses the SQLSTATES along with the SQLCA to determine the specific causes of failure, and to perform specific error handling.

Table 7-2 lists the mapping of all possible return codes of Oracle OCI functions and DB2 CLI functions.

Table 7-2: Return code mapping from OCI to CLI functions

OCI return code	CLI return code	Explanation
OCI_SUCCESS	SQL_SUCCESS	The function completed successfully, no additional SQLSTATE information is available.
OCI_SUCCESS_WITH_INFO	SQL_SUCCESS_WITH_INFO	The function completed successfully with a warning or other information. Call SQLGetDiagRec() or SQLGetDiagField() to receive the SQLSTATE and any other informational messages or warnings. The SQLSTATE will have a class of '01'.
OCI_NO_DATA	SQL_NO_DATA_FOUND	The function returned successfully, but no relevant data was found. When this is returned after the execution of an SQL statement, additional information may be available and can be obtained by calling SQLGetDiagRec() or SQLGetDiagField().
OCI_ERROR	SQL_ERROR	The function failed. Call SQLGetDiagRec() or SQLGetDiagField() to receive the SQLSTATE and any other error information.
OCI_INVALID_HANDLE	SQL_INVALID_HANDLE	The function failed due to an invalid input handle (environment, connection or statement handle). This is a programming error. No further information is available.
OCI_NEED_DATA	SQL_NEED_DATA	The application tried to execute an SQL statement but DB2 CLI lacks parameter data that the application had indicated would be passed at execute time.
OCI_STILL_EXECUTING	SQL_STILL_EXECUTING	The function is running asynchronously and has not yet completed. The DB2 CLI driver has returned control to the application after calling the function, but the function has not yet finished executing.
OCI_CONTINUE	no equivalent

The OCI function OCIErrorGet() returns the diagnostic record according to the SQLSTATE. Within a CLI application, the functions SQLGetDiagRec() and SQLGetDiagField() return three pieces of information:

• SQLSTATE

• Native error

  If the diagnostic is detected by the data source, this is the SQLCODE; otherwise, this is set to -99999.

• Message text

  This is the message text associated with the SQLSTATE.

SQLGetSQLCA() returns the SQLCA for access to specific fields, but should only be used when SQLGetDiagRec() or SQLGetDiagField() cannot provide the desired information.

Further information

You can find more information about CLI applications and development in:

• DB2 UDB Call Level Interface Guide and Reference, volumn1, SC09-4849; volumn2, SC09-4850

• DB2 UDB Application Development Guide: Building and Running Applications, SC09-4825

• DB2 UDB Application Development Guide: Programming Client Applications, SC09-4826

• Web site http://www.ibm.com/software/data/db2/udb/ad

7.2.4 Converting ODBC applications

The Open Database Connectivity (ODBC) is similar to the CLI standard. Applications based on ODBC are able to connect to the most popular databases. Thus, the application conversion is pretty easy. You have to perform the conversion of database specific items in your application such as:

• Proprietary SQL query changes

• Possible changes in calling stored procedures and functions

• Possible logical changes

And, the proceed to the test, roll-out, and education tasks as well. Your current development environment will be the same. For a more detailed description of the necessary steps, please reference 7.1, "Application migration planning" on page 226.

---