DB2 9 Application Development exam 733 prep, Part 6: .NET programming

DB2 interfaces seamlessly with the .NET framework

Learn about writing .NET applications that interface with IBM® DB2®. Get an introduction to the ADO.NET object model and see how to execute SQL statements with ADO.NET. This is the sixth in a series of nine tutorials that you can use to help prepare for the IBM DB2 9 Family Application Development certification exam (Exam 733).

Peter He (peterzh@ca.ibm.com), DB2 Consultant, IBM

Peter HePeter He is a DB2 consultant with the DB2 Business Partner Enablement Team at the IBM Toronto Lab. Peter has worked extensively with DB2 and WebSphere technologies. He provides DB2 application design and implementation consulting to ISVs. He holds a Ph.D. from York University and is a DB2 Certified Solutions Expert.



01 March 2007

Also available in Vietnamese

Before you start

About this series

The IBM Certified Application Developer certification confirms to others that you are an intermediate- or advanced-level IBM DB2 for Linux™, UNIX®, and Windows® application developer and shows that you have strong skills in all common programming tasks as well as embedded SQL programming, ODBC/CLI programming, .NET programming, or Java™ programming.

This series of nine free tutorials is designed to help you prepare for the DB2 9 Application Development for Linux, UNIX, and Windows certification exam (Exam 733). Each tutorial includes a link to a free DB2 9 for Linux, UNIX, and Windows trial download. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.

What is this tutorial about?

In this tutorial, you will learn about writing .NET applications that interface with IBM DB2. You will see how to:

  • Connect to a DB2 database from a .NET application
  • Use ADO.NET to read and update data in a DB2 database
  • Use ADO.NET to work with results
  • Build an ADO.NET application
  • Troubleshoot a DB2 .NET application

The material in this tutorial covers the objectives in Section 6 of the DB2 9 Application Developer Certification exam (Exam 733), entitled ".NET Programming."

Who should take this tutorial?

To take the DB2 9 Family Application Development exam, you must have already passed the DB2 9 Family Fundamentals exam (Exam 730). You can use the DB2 9 Family Fundamentals tutorial series to prepare for that test. It is a very popular tutorial series that has helped many people understand the fundamentals of the DB2 family of products.

In addition to this DB2 background, you should also have basic knowledge of SQL and the .NET platform before you begin this tutorial.

This tutorial is one of the tools that can help you prepare for Exam 733. You should also review the Resources section at the end of this tutorial for more information.

System requirements

You do not need a copy of DB2 Universal Database to complete this tutorial. However, you can download a free trial version of DB2 9 Enterprise Edition if you wish.

If you plan to develop .NET applications for DB2, or you plan to try out this tutorial's sample code yourself, you need to complete the following tasks:

  • Install DB2 9 for Linux, UNIX, and Windows
  • Create the sample database by running db2sampl from the DB2 CLP. The sample code in this article has been written to work with this database.
  • Install Microsoft® Visual Studio .NET 2003 or later

Introduction to .NET programming for DB2

The .NET framework

The .NET framework has been promoted by Microsoft as a new software development platform for Windows to replace Component Object Model (COM) technology. The key features of this framework are as follows:

  • You can code .NET applications in over forty different programming languages. The most popular languages for .NET development are C# and Visual Basic .NET.
  • The .NET framework class library provides the building blocks with which you build .NET applications. This class library is language agnostic and provides interfaces to operating system and application services.
  • No matter what language your .NET application is written in, it compiles into Intermediate Language (IL), a type of bytecode.
  • The Common Language Runtime (CLR) is the heart of the .NET framework, compiling the IL code on the fly and then running it. In running the compiled IL code, the CLR activates objects, verifies their security clearance, allocates their memory, executes them, and cleans up their memory once execution is finished.

The .NET framework provides extensive data access support through ADO.NET. ADO.NET supports both connected and disconnected access. The key component of disconnected data access in ADO.NET is the DataSet class, instances of which act as a database cache that resides in your application's memory. Connected access in ADO.NET requires no additional classes.

For both connected and disconnected access, .NET applications access databases through what's known as a .NET data provider. Various database products include their own .NET data providers, including DB2 for Windows. A .NET data provider implements the following base classes:

  • Connection: Establishes and manages a database connection.
  • Command: Executes an SQL statement against a database.
  • DataReader: Reads and returns result set data from a database.
  • DataAdapter: Links a DataSet instance to a database. Through a DataAdapter instance, the DataSet can read and write database table data.

The DB2 .NET Data Provider

The DB2 .NET Data Provider extends DB2 support for the ADO.NET interface and delivers high-performing, secure access to DB2 data. DB2 for Windows includes three .NET data providers:

  • DB2 .NET Data Provider: A high-performance, managed ADO.NET data provider. This is the recommended .NET data provider for use with the DB2 family of databases. ADO.NET database access using the DB2 .NET Data Provider has fewer restrictions and provides better performance than the OLE DB and ODBC .NET bridge providers.
  • OLE DB .NET Data Provider: A bridge provider that feeds ADO.NET requests to the IBM OLE DB Provider (by way of the COM interop module). This .NET data provider is not recommended for access to DB2 family databases. The DB2 .NET Data Provider is faster and more feature rich.
  • ODBC .NET Data Provider: A bridge provider that feeds ADO.NET requests to the IBM ODBC Driver. This .NET data provider is not recommended for access to DB2 family databases. The DB2 .NET Data Provider is faster and more feature rich.

To develop and run applications that use the DB2 .NET Data Provider, you need Version 1.1 or 2.0 of the .NET Framework. .NET Framework, Version 1.0 and Visual Studio .NET 2002 are not supported for use with the DB2 9 DB2 .NET Data Provider.


Connecting to a database

In this section, you'll learn how to connect to a DB2 database from a .NET application.

Making a connection using DB2 .NET Data Provider

When you're using the DB2 .NET Data Provider, you can establish a database connection through the DB2Connection class. First, you must create a string that stores the connection parameters. Here's an example of a possible connection string:

 String connectString = "Database=SAMPLE";

When the string is used, it attempts to connect to the SAMPLE database:

Listing 1. Connection string
 String cs = "Server=draco:50000;Database=SAMPLE;UID=db2admin;PWD=1a2b3c4d;
Connect Timeout=30";
 // When used, attempts to connect to the SAMPLE database on the server
 // 'draco' through port 50000 using 'db2admin' and '1a2b3c4d' as the user id
 // and password respectively. If the connection attempt takes more than thirty
 // seconds, the attempt will be terminated and an error will be generated.

To create the database connection, pass the connectString to the DB2Connection constructor. Then use the DB2Connection object's Open() method to formally connect to the database identified in connectString.

Here's how you'd connect to a database in C#:

Listing 2. Connect to a database in C#
 String connectString = "Database=SAMPLE"; 
 DB2Connection conn = new DB2Connection(connectString); 
 conn.Open(); 
 return conn;

Making a connection with ADO.NET common base classes

Version 2.0 of the .NET framework features a namespace called System.Data.Common, which features a set of base classes that can be shared by any .NET data provider. This facilitates a generic ADO.NET database application development approach, featuring a constant programming interface. The main classes in the DB2 .NET Data Provider for .NET Framework 2.0 are inherited from the System.Data.Common base classes. As a result, generic ADO.NET applications work with DB2 databases through the DB2 .NET Data Provider. The following C# code demonstrates a generic approach to establishing a database connection:

Listing 3. Establishing a database connection
 DbProviderFactory factory = DbProviderFactories.GetFactory("IBM.Data.DB2"); 
 DbConnection conn = factory.CreateConnection(); 
 DbConnectionStringBuilder sb = factory.CreateConnectionStringBuilder(); 
 if( sb.ContainsKey( "Database" ) ) { sb.Remove( "database" ); 
 sb.Add( "database", "SAMPLE" ); } 
 conn.ConnectionString = sb.ConnectionString; 
 conn.Open();

The DbProviderFactory object is the point at which any generic ADO.NET application begins. This object creates generic instances of .NET data provider objects, such as connections, data adapters, commands, and data readers, which work with a specific database product. In the case of the example in Listing 3, the IBM.Data.DB2 string passed into the GetFactory() method uniquely identifies the DB2 .NET Data Provider and results in the initialization of a DbProviderFactory instance that creates database provider object instances specific to the DB2 .NET Data Provider. The DbConnection object can connect to DB2 family databases, just as a DB2Connection object, which is actually inherited from DbConnection, can.

Using the DbConnectionStringBuilder class, you can determine the connection string keywords for a data provider and generate a custom connection string. The code in the Listing 3 checks to see if a keyword named database exists in the DB2 .NET Data Provider; if one does, the code generates a connection string to connect to the SAMPLE database.

Connection pooling

When a connection is first opened against a DB2 database, a connection pool is created. As connections are closed, they enter the pool, ready to be used by other applications needing connections. The DB2 .NET Data Provider enables connection pooling by default. You can turn connection pooling off using the Pooling=false connection string keyword/value pair.

You can control the behavior of the connection pool by setting connection string keywords for the following:

  • The minimum and maximum pool size (min pool size, max pool size)
  • The length of time a connection can be idle before it is returned to the pool (connection lifetime)
  • Whether or not the current connection will be put in the connection pool when it is closed (connection reset)

Database connection: Sample code

Listing 4 demonstrates how to connect to DB2 from an ADO.NET application:

Listing 4. Connecting to DB2 from an ADO.NET application
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class ConnDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to the database
      conn.Open(); 
      
      Console.WriteLine("Connected to the " + dbName + " database");          
    }
    catch (Exception e )
    {
      Console.WriteLine(e.Message);     
    }
    finally
    {
    	//Close the connection
    	if (conn != null)
    	{
           conn.Close();
    	}  	
    }
  }
     
}

Before you can compile the program, you must ensure that the INCLUDE environment variable contains %DB2PATH%\INCLUDE as the first directory. To do this, update the environment setup file for Microsoft Visual C++ .NET, which you can find at C:\Program Files\Microsoft Visual Studio .NET\Common7\Tools\vsvars32.bat. This file contains the following commands:

 @set INCLUDE=%MSVCDir%\ATLMFC\INCLUDE;...; %FrameworkSDKDir%\include;%INCLUDE%

Move %INCLUDE%, which sets the %DB2PATH%\INCLUDE path, from the end of the list to the beginning, as follows:

 @set INCLUDE=%INCLUDE%;%MSVCDir%\ATLMFC\INCLUDE;...; %FrameworkSDKDir%\include

To compile the code, place the program in %DB2PATH%\samples\.NET\cs, which is the directory that contains the script bldapp.bat. Then issue the following command:

 bldapp ConnDb

To run the program, issue the following command:

 ConnDb

Reading and updating data with ADO.NET

In this section, you'll learn how to use the ADO.NET DB2Command object to execute SQL statements in a .NET application. After completing this section, you will have learned:

  • What a DB2Command object is
  • How to use the ExecuteReader() method to read data from DB2
  • How to use the ExecuteNonQuery() method to insert and delete data
  • How to use the ExecuteScalar() method to return a single value

The DB2Command object

A DB2Command object allows you to specify how you want to interact with DB2. For example, you can perform SELECT, INSERT, MODIFY, and DELETE commands on the data in DB2.

When using the DB2 .NET Data Provider, you execute SQL statements through a DB2Command class using its ExecuteReader() and ExecuteNonQuery() methods, and its CommandText, CommandType, and Transaction properties. For SQL statements that produce output, you should use the ExecuteReader() method; its results can be retrieved from a DB2DataReader object. For all other SQL statements, you should use the ExecuteNonQuery() method. The Transaction property of the DB2Command object should be initialized to a DB2Transaction. A DB2Transaction object is responsible for rolling back and committing database transactions.

DB2Command objects are created using the DB2Connection.CreateCommand() method. Here's an example:

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

To execute an INSERT, UPDATE, or DELETE SQL statement from a DB2Command object, set the CommandContext to the SQL statement and call the cmd.ExecuteNonQuery() method. For example:

Listing 5. Execute non-query SQL statement
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

To execute a query from a DB2Command object, set the CommandContext to the query and call the cmd.ExecuteReader() method:

Listing 6. Execute query
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

Once your application has performed a database transaction, you must either roll it back or commit it. You can do this using the Commit() and Rollback() methods of a DB2Transaction object. Here's how you roll back or commit a transaction in C#:

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

To parse the result sets, use a DB2DataReader object. Use Read() to advance to the next row of the result set; then, after each fetch, retrieve the column values using the methods applicable to the data type. The methods GetString(), GetInt32(), GetDecimal(), and so on (there are similar methods for other the available data types) are used to extract data from the individual columns of output. The DB2DataReader.Close() method is used to close the DB2DataReader; you should always do this when you're finished reading output. Listing 7 shows how to read a result set in C#:

Listing 7. Reading a result set in C#
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

Adding parameters to a DB2Command object

For dynamic SQL, you need to add parameters to the DB2Command object and set the values to those parameters, like so:

Listing 8. Adding parameters to DB2Command object and setting values to the parameters
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

Sample code

Listing 9 illustrates all the concepts discussed in this section, which include:

  • Creating a DB2Command object
  • Executing an INSERT, UPDATE, or DELETE statement
  • Executing an SQL query
  • Parsing a result set object
Listing 9. DB2Command: Sample application
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

To compile the code, place the program in %DB2PATH%\samples\.NET\cs, which is the directory that contains the script bldapp.bat. Then issue the following command:

 bldapp AccessSamplDb

To run the application, issue the following command:

 AccessSamplDb

Here's what the output of the application should look like:

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

Troubleshooting

In this section, you will learn how to use the troubleshooting tools available for ADO.NET applications. You'll examine basic error handling and reporting practices using Exception objects and the DB2 .NET trace and DB2 trace facilities.

Error handling

If you follow proper error handling practice in your ADO.NET programs, you'll wrap the logic in your ADO.NET applications in try/catch blocks. When errors occur in ADO.NET methods, they throw Exception objects. Therefore, for every try block that contains a database operation, the corresponding catch block should contain logic to handle an Exception object.

The example in Listing 10 features a SELECT statement with type-incompatible operands in the WHERE clause: the JOB column of the STAFF table is of data type VARCHAR, not INTEGER.

Listing 10. Try catch block
try
{
  ...
  cmd.Transaction = trans; 
  cmd.CommandText = "SELECT ID, NAME FROM STAFF WHERE JOB = 99";
  reader = cmd.ExecuteReader();    
  ...
}
catch (Exception ex)
{
  Console.WriteLine(ex.Message);
}

When an exception occurs in the try block, the catch block will catch the exception.

ex.Message contains the content of the exception. The SELECT statement with the incompatible operands generates the following exceptions:

ERROR [42818] [IBM][DB2/NT] SQL0401N  The data types of the operands for the 
operation "=" are not compatible.  SQLSTATE=42818

DB2 ADO.NET trace

To debug your DB2 ADO.NET applications, you can use the DB2 ADO.NET trace facility. To activate the ADO.NET trace facility, set the following environment variable:

SET DB2NMPTRACE=1

Once you have activated the ADO.NET trace facility, every time you run an DB2 ADO.NET application, a detailed trace of the ADO.NET call is generated. Listing 11 shows the trace data generated from the AccessSamplDb application you saw developed above:

Listing 11. .NET trace sample
| DB2Trace.TraceVersion fnc data 10: Version: 9.0.0.1
| DB2Trace.TraceVersion fnc data 11: Framework: 1.1.4322.2300
| DB2Interop.Init fnc entry
| | DB2Interop.Init fnc data 10: file:///c:/windows/assembly/gac/ibm.data.db2/9.0.0.1
__7c307b91aa13d208/ibm.data.db2.dll
| | DB2Interop.Init fnc data 19: 9.1.0.DEF.1
| | DB2Interop.Init fnc data 20: DB2COPY1
| | DB2Interop.Init fnc data 30: D:\IBM\SQLLIB\
| | DB2Interop.Init fnc data 50: D:\IBM\SQLLIB\\bin\db2app.dll
| | DB2Interop.Init fnc data 55: a
| | DB2Interop.Init fnc data 60: 15
| | DB2Interop.Init fnc data 97: SOFTWARE\IBM\DB2\InstalledCopies
| | DB2Interop.Init fnc data 98: DB2COPY1
| | DB2Interop.Init fnc data 99: 0
| DB2Interop.Init fnc exit2, rc = 0 - False, 15
db2trace False
| DB2ConnPool.GetDB2ConnPool fnc data 5: 0
| DB2ConnPool.GetDB2ConnPool fnc data 10: 1
| DB2ConnPool.GetDB2ConnPool fnc data 11: 55706604
| DB2ConnPool.GetDB2ConnPool fnc data 254: 1 1
DB2ConnPool.GetDB2ConnPool fnc exit, rc = 0
DB2ConnPool.DB2ConnPool fnc exit, rc = 0
DB2Connection.DB2Connection1 fnc entry
| DBCWrapper.DBCWrapper fnc entry
| DBCWrapper.DBCWrapper fnc exit, rc = 0
| DB2Connection.ConnectionString.set fnc entry1 - Database=SAMPLE
| | DB2Connection.State.get fnc entry
| | | DB2Connection.State.get api data 10: Closed
| | DB2Connection.State.get fnc exit, rc = 0
| | DB2ConnPool.ReplaceConnectionStringParms fnc entry
| | | DB2ConnPool.ReplaceConnectionStringParms fnc data 10: Database=SAMPLE
| | | DB2ConnPool.ReplaceConnectionStringParms fnc data 15: database, SAMPLE
| | | DB2ConnPool.ReplaceConnectionStringParms fnc data 20: dsn=SAMPLE;
| | DB2ConnPool.ReplaceConnectionStringParms fnc exit, rc = 0
| DB2Connection.ConnectionString.set fnc exit, rc = 0
DB2Connection.DB2Connection1 fnc exit, rc = 0
DB2Connection.Open fnc entry
| DB2Connection.State.get fnc entry
| | DB2Connection.State.get api data 10: Closed
| DB2Connection.State.get fnc exit, rc = 0
| DB2ConnPool.GetDB2ConnPool fnc entry
| | DB2ConnPool.GetDB2ConnPool fnc data 254: 1 2
| DB2ConnPool.GetDB2ConnPool fnc exit, rc = 0

When the program ran with the SELECT statement with the incompatible operands, .NET trace captured the error:

Listing 12. Error captured by .NET trace
DB2Command.ExecuteReader2 fnc exit, rc = 0
DB2ConnPool.CheckClose fnc entry
DB2Exception.Message.get fnc entry
| DB2ConnPool.CheckClose fnc data 10: 2/8/2007 4:14:39 PM
| | DB2Resource.DB2Resource fnc entry2 - db2nmp.xml, null
| | DB2ConnPoolWrapper.LockPool fnc entry
| | | DB2Resource.GetMsgPath fnc entry
| | | | DB2ConnPoolWrapper.LockPool fnc data 10: dsn=SAMPLE;
| | | | | DB2Resource.GetMsgPath fnc data 10: D:\IBM\SQLLIB\
| | | | DB2ConnPoolWrapper.LockPool fnc exit, rc = 0
| | | DB2Resource.GetMsgPath fnc exit1, rc = 0 - D:\IBM\SQLLIB\msg\en_US
| | | DB2ConnPool.CheckClose fnc data 20:    dsn=SAMPLE;, xa: False, conns: 1, free:
0
| | DB2Resource.DB2Resource fnc exit, rc = 0
| | DB2ConnPool.CheckClose fnc data 25: POOL [dsn=SAMPLE;] 1->NULL    pPool.pLastConn
 = 1
| | DB2Exception.Message.get fnc data 20: ERROR [42818] [IBM][DB2/NT] SQL0401N  The d
ata types of the operands for the operation "=" are not compatible.  SQLSTATE=42818

| | DB2ConnPool.CheckClose fnc data 30:       dbc: 1, xa: False, inuse: True, free on
 close: False, lifetime: -1, 0
| DB2Exception.Message.get fnc exit, rc = 0
| DB2ConnPoolWrapper.UnlockPool fnc entry
ERROR [42818] [IBM][DB2/NT] SQL0401N  The data types of the operands for the operatio
n "=" are not compatible.  SQLSTATE=42818

The DB2 .NET Provider uses some components of the DB2 CLI. It is useful sometimes for problem determination with DB2 ADO.NET applications to take CLI and DB2 trace information.


IBM Database Add-ins for Visual Studio

Overview

In addition to the DB2 .NET Data Provider, IBM also provides the IBM Database Add-ins for Visual Studio. These enable you to quickly and easily develop .NET applications for DB2 databases in Visual Studio 2005. You can also use the Add-ins to create database objects, such as indexes and tables, and develop server-side objects, such as stored procedures and user-defined functions, in DB2. The IBM Database Add-ins for Visual Studio 2005 and DB2 .NET provider support for .NET framework 2.0 have been made generally available in DB2 9.1.

Key new features in the IBM Database Add-ins for Visual Studio

Some of the highlights of IBM Database Add-ins for Visual Studio are as follows:

  • You can build Windows applications and Web sites for DB2 without writing any code:
    • All flavors of DB2 are supported (DB2 for Linux, UNIX, and Windows, DB2 for iSeries, and DB2 for z/OS®)
    • Federated database and nicknames are supported for application development
    • Filtering of database objects is supported for optimal performance on iSeries and zSeries® servers
    • Caching of schema information for objects in DB2 connections in Server Explorer provides better application development performance at design time
  • You have the ability to hide or show specific folders for a DB2 connection in Server Explorer
  • The tooling continues to support the display of detailed messages about DB2 activity on the IBM Message Pane
  • The new tooling introduces a new set of IBM designers to create, alter, and clone database objects:
    • All IBM designers continue to use the smart multiline editors that provide syntax coloring and statement completion
    • You have the ability to create new tables, views, and procedures using IBM designers
    • There is new functionality to alter existing tables, views, and procedures using IBM designers
    • There is new functionality to create and alter roles and assign privileges to database objects
    • The IBM designers give you the ability to clone tables and procedures
  • You now have a way to seamlessly debug SQL procedures on Linux, UNIX, and Windows, or zSeries servers, from Server Explorer. Debugging support now uses the new IBM Designer for Procedures, which provides a seamless debugging experience.
  • There is a new designer for viewing or creating scripts for all objects. The IBM Script Designer provides:
    • The ability to change and execute scripts
    • The ability to run single or multiple DDL/DML statements and view results in single or multiple grids
    • The ability to alter objects using scripts
  • You can show data from tables and views with the following new enhancements:
    • You can filter columns while retrieving data
    • You can save data as XML to import or export, allowing easy table or view data migration
  • The following new enhancements are available in execute procedures and functions:
    • You have the ability to run pre- and post-scripts
    • You can save input or in-out parameter values across Visual Studio sessions
    • You can commit or roll back transactions
  • There is a new user interface to view result sets in DB2 connections on the Server Explorer. It gives you:
    • The ability to view single or multiple result sets for a procedure in Server Explorer
    • The ability to discover automatically (when possible) or to manually define or customize result set definitions for a procedure
    • The ability to set the preference to always discover or always manually define the result set definition in Add or Modify connection
  • You have continued support for DB2 projects and IBM scripting wizards to create DB2 scripts. The debugging support has been discontinued from DB2 projects. Instead, it is supported seamlessly from DB2 connections on Server Explorer.

Summary

You've received a working introduction to the DB2 ADO.NET programming concepts on which you will be tested in the DB2 9 Family Application Development Certification exam (Exam 733), such as connecting to a DB2 database from a .NET application, using ADO.NET to read and update data in a DB2 database and to work with results sets, and troubleshooting DB2 .NET applications. To reinforce the ideas presented in each of the tutorial sections, do more than simply compile and run the sample code. Make your own modifications and enhancements!

Resources

Learn

  • DB2 9 Application Development exam 733 prep tutorial series: Check out the other parts of this tutorial series designed to help you prepare for the DB2 9 Family Application Development Certification exam (Exam 733). The complete list of all tutorials in this series includes:
    • Database objects and programming methods
    • Data manipulation
    • XML data manipulation
    • Embedded SQL programming
    • ODBC/CLI programming
    • .NET programming
    • Java programming
    • Advanced programming
    • User-defined routines
  • developerWorks resource page for DB2 for Linux, UNIX, and Windows: Find articles and tutorials and connect to other resources to expand your DB2 skills.
  • DB2 Information Center: Find information you need in order to use the DB2 family of products and features as well as related WebSphere® Information Integration products and features.
  • For more information on the DB2 9 for Linux, UNIX, and Windows Application Development Certification (Exam 733), check out these links:
  • DB2 9 Fundamentals certification prep tutorial series: Before you take the DB2 9 Application Development certification exam (Exam 733), you should have already taken and passed the DB2 9 Fundamentals certification exam (Exam 730). Use this series of seven tutorials to prepare for that exam; topics include:
    • DB2 planning
    • DB2 security
    • Accessing DB2 data
    • Working with DB2 data
    • Working with DB2 objects
    • Data concurrency
    • Introducing Xquery
  • DB2 9 Database administration certification prep series: Prepare for the DB2 9 Database Administration for Linux, UNIX, and Windows certification exam (Exam 731). A set of seven tutorials covers the following topics:
    • Server management
    • Data placement
    • Database access
    • Monitoring DB2 activity
    • DB2 utilities
    • High availability: Backup and recovery
    • High availability: Split mirroring and HADR
  • DB2 for .NET product page: Learn more about the IBM DB2 Add-Ins for .NET.
  • developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  • DB2 Express-C: Learn about the no-charge version of DB2 Express Edition for the community.
  • Stay current with developerWorks technical events and Webcasts.

Get products and technologies

  • DB2 Enterprise 9: Download a free trial version..
  • DB2 Express-C: Now you can use DB2 for free. Download this no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=199336
ArticleTitle=DB2 9 Application Development exam 733 prep, Part 6: .NET programming
publish-date=03012007