DB2 .NET FAQs

DB2 .NET FAQs

Where can I find the DB2 .NET provider and VS Addins?

Support for IBM Data Servers consists of two pieces:

IBM DB2, IDS, and U2 Data Provider for .NET Framework
IBM Database Addins for Visual Studio

The provider is required in all cases, and needs to be on all workstations where applications are deployed. The addins are only required on development workstations.

The Addins currently support VS 2005 and VS 2008.

For deployment scenarios, the provider is available in all DB2 (Data Server) packages except for the cli driver. The smallest package is the ds driver (IBM Data Server Driver for ODBC, CLI and .NET). This web site has the latest downloads:

http://www.ibm.com/software/data/db2/ad/deploy.html

In addition to being the smallest download, the ds driver also has no client database directory, all connections need to fully specify the remote server and database in the connection string. If a client side database directory is required, the Data Server Runtime client can be used instead. It is available at the above deploy download link also.

For development workstations, the simplest configuratiuon is to simply download the addins on to one of the runtime configurations described above. The addins can be downloaded here:

https://www.ibm.com/services/forms/preLogin.do?lang=en_US&source=swg-vsai

There is alot of synergy between the addins and the provider, and in many cases the stand alone download of the addins prereqs the most recent provider. Ensure you review the readme for the standalone addins to ensure any prereqs are met.

Note that connectivity to iSeries(r) and zSeries(r) servers requires DB2 Connect.

Another good source of recent information is the DB2 Visual Studio Zone on developerWorks

I am having problems connecting from Visual Studio

There are many potential causes for connectivity issues, to aid in debugging, IBM has provided the testconn utility, installed with DB2 9. Testconn is a small .NET application that directly uses the DB2 .NET data provider and performs a test connection and some simple operations to validate server setup.

There are two versions testconn11 for FW 1.1 and testconn20 for FW 2.0. Both have the same syntax, but they will load the proper framework version of the DB2 .NET provider. For invocation, simply provide a .NET connection string:

testconn20 database=sample;server=myhost:50000;userid=myid;password=mypass

A complete list of connection string keywords is available in the ConnectionString property online help.

If testconn encounters any errors, it will display them along with suggested resolution steps. Here is a sample testconn20 output:

Step 1: Printing version info
        .NET Framework version: 2.0.50727.42
        DB2 .NET provider version: 9.0.0.2
        Capability bits: ALLDEFINED
        Build: 20060813
        Elapsed: 0.150591

Step 2: Connecting using "database=sample"
DB2 Server type and version: DB2/NT 09.01.0000
Elapsed: 0.9336642

Step 3: Selecting rows from SYSIBM.SYSTABLES to validate existance of packages
SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
Elapsed: 0.050197

Step 4: Calling GetSchema for tables to validate existance of schema functions
Elapsed: 0.2208668

Test passed.

What is DB2 Connect?

DB2 Connect(tm) is basically a license to connect to DB2 servers on IBM(r) iSeries(tm) and zSeries(r). This is accomplished via one of two methods:
* Direct connect - your application connects directly to the iSeres / zSeries server
* Gateway - your application connects to a DB2 Connect gateway that pools connections to iSeres or zSeries servers

The Direct Connect scenario requires a DB2 Connect license on each client machine, whereas the Gateway scenario only requires a DB2 Connect license on the gateway.

The DB2 Connect web page has more details on the DB2 Connect Editions.

The simplest way to get started with DB2 Connect is to purchase a DB2 Connect 9 Personal Editon license via Passport Advantage. If you already have the DB2 9 VS 2005 Addins (or just the DB2 9 DB2 .NET provider) installed, you do not need to download any additional code. Just follow this process here to add your DB2 Connect PE license to your existing installation.

1. retrieve the db2conpe.lic file from your Passport Advantage distribution

For the runtime client:

2. issue db2licm -a db2conpe.lic to apply the license to your existing installation

For the ds driver:

2. copy the license file to the license directory under the directory the driver was installed in

A more comphrensive guide to licensing can be found in the DB2 InfoCenter License Management

At this time, there is no trial for DB2 Connect 9 Personal Edition.

Why can I NOT see all my tables?

This question is most often asked when connecting to an iSeries server, and applies equally to VS .NET 2003 and VS 2005 / 2008 addins.

Earlier implementations only iterate tables with a table type of 'T' into the Server Explorer (or IBM Explorer in VS .NET 2003). iSeries tables of type 'P' or 'L' will thus not be visible. for zOS Servers, table of types 'T', 'G', 'X' and 'N' are iterated.

This restriction has been lifted in the current version of the VS Addins (for 2005 and 2008). Please see the first FAQ for download instructions.

Is FW 3.0 / FW 3.5 and/or Windows Vista(tm) supported?

From an ADO.NET point of view, there is no real new functionality in FW 3.0 / FW 3.5 - it is compatible to FW 2.0. Thus the DB2 .NET FW 2.0 provider relased as part of DB2 9 and DB2 Connect 9 fully supports FW 3.0 / FW 3.5. Support for LINQ as part of FW 3.5 is described here.

DB2 9 FP2 added support for Windows Vista, this is described in our Support for Microsoft Windows Vista page.

Can DB2 V8 and DB2 9 coexist?

The simple answer is yes, but ...

This question does NOT address DB2 9 clients talking to DB2 V8 LUW servers or DB2 Connect V8 gateways - that is fully supported with no known issues. This question addresses coexistence on the same box, ie. trying to install the DB2 9 client on a box with DB2 Connect V8 already installed.

The main issue to be aware of is that only one copy of db2app.dll (DB2's native code library for ODBC, OLE DB and .NET) can be loaded into a single process. With any coexistence with DB2 V8, DB2 V8 must be the default copy, and thus the DB2 V8 db2app.dll will be used for any ODBC, OLE DB or DB2 .NET FW 1.1 applications. Since DB2 V8 does not have a FW 2.0 provider, The DB2 9 FW 2.0 provider will be used for FW 2.0 apps.

The issue comes about where a single FW 2.0 application also has code that either still references the FW 1.1 DB2 .NET provider, or also uses ODBC or OLE DB to connect with DB2. These references will link back to the DB2 V8 code level, and will attempt to also bring the DB2 V8 db2app.dll into the same process with the DB2 9 db2app.dll.

Sounds complex - it is. The recomendation is to avoid DB2 V8 and DB2 9 coexistance on the same box when using .NET.

For a complete list of limitations, read the Multiple DB2 copies roadmap

How do I redistribute DB2 .NET with my application?

The DB2 .NET provider can not simply be copied, it has several supporting files and installation actions, and thus it needs to be installed. This can be accomplished by one of these methods:

1. Install the Data Server Runtime Client or the Data Server Driver for ODBC, CLI and .NET (ds driver for short) on the target machine(s)

2. Package the Data Server Client Runtime merge modules with your application. Details are in the Runtime Client Merge Module online information

What non-DB2 servers are supported?

The latest version of the VS Addins and the DB2 .NET provider support Informix(tm) Cheetah (V11.10) and U2(tm) (Universe 10.2 and Unidata 7.1) servers. Your existing applications written to use the DB2 .NET provider should be easily extended to these other members of the IBM Data Server product line.

The Informix server must have DRDA mode enabled, otherwise connections operate just like those for DB2 servers.

U2 servers need the new "servertype" keyword specified with either unidata or universe.

There are lmiitations in using these servers, online documentation will be provided shortly.

What is SQL1159 Initialization Error?

This is a startup error with the DB2 .NET (or Common IDS .NET) provider. This is typically caused by incompatabilities with the DB2 .NET provider code and the registry information, as the result of a failed installation or manual registry changes.

For the most part, these problems need to be solved by a re-install of the runtime package (eg. ds driver) that is being used. A description of the reason codes is in the online documentation SQL1159N

There is one additional reason code that has been added since that documentation was produced:

10 - version mismatch between IBM.Data.DB2.dll and db2app.dll - the first token is the db2app.dll version, second token is IBM.Data.DB2.dll version.

Is LINQ supported?

The short answer is yes. Microsoft has created the Entity Framework, which can be pictured as a "generic" ADO.NET provider. The Entiry Framework has been fully LINQ enabled by Microsoft, thus applications can use LINQ to Entities to access all servers that support the Entity Framework.

IBM has delivered support for the Entity Framework in the V9.5 FP3 version of our client drivers. The online information center has full information on servers supported and any current limitations Provider support for Entity Framework

What are packages and why / when do I need to rebind?

Packages (in DB2 terms) are containers for the necessary instructions to execute a SQL statement. They are actually stored in the database itself. This section describes only the packages necessary for the dynamic programming languages (ODBC, .NET and JDBC), additional packages exist for other utilities and are outside the scope of this article.

For dynamic languages (like .NET, ODBC and JDBC), DB2 has a set of "empty" packages stored in the database, and the client utilizes those packages as the application executes SQL statements. The packages used for these dynamic languages have been standardized with V8 of our client drivers and have the following naming prefixes:

SYSSHxyy
SYSSNxyy
SYSLHxyy
SYSLNxyy

where:

S is for small packages, L for large packages
H for with hold cursors, N for not with hold
x is the isolation level
yy is the sequence #

A full description of the packages can be found here

These packages need to exist on the database before any dynamic statements (from ODBC, .NET and JDBC) can be used. There is no automatic bind process from the client, they must be explicitly bound. For DB2 LUW servers, the packages are created at database creation time by the server.

There is one other package - SYSSTAT, this package contains misc static functions that can not be executed dynamically.

Changes in CLI / .NET and JDBC packages

The packages were standardized in V8 of the DB2 LUW and DB2 Connect clients. The following is the change history of these packages:

Dynamic packages SYSSHxyy, SYSSNxyy, SYSLHxyy, SYSLNxyy:

V8 GA - created

SYSSTAT misc static statements:

V8 GA - created
V9.1 FP2, V9.5 GA - added additional sections for Jcc z/OS type 2

Rebinds only need to happen when upgrading across one of the change points listed above. For example, upgrading fro V8 GA to V9.1 GA would not require a rebind. However, any upgrade from V9.1 FP1 would technically require a rebind because the SYSSTAT package changed. However, the change affected the Jcc z/OS type 2 driver only, thus CLI and .NET, and other servers, need not rebind as they will be unaffected by the change.

Migration Concerns

There is a large focus on binary compatibility as new release and fixpacks are delivered. However, there are times when there needs to be a breaking change. This FAQ outlines the breaking changes that have been delivered.

V9.1 GA
- stopped shipping a FW 1.0 provider, redirection policy in place to redirect FW 1.0 references to FW 1.1, requires running a FW 1.0 built app with FW 1.1
- cache data property - V9.1 added streaming capabilities, which can improve the performance of larger datatypes. However, with this in place, the provider can not allow multiple gets on the same column for the same row. The data will be streamed into the app for the first get, and not available when a second get is issued. The cache data property causes the provider to cache these streamed values, to enable multiple gets. This or course has potential memory implications, and is off by default for the FW 2.0 provider, which is new with V9.1.

9.7 GA
- stopped shipping a FW 1.1 provider, redirection policy is in place to redirect FW 1.1 references to FW 2.0, requires running a FW 1.1 built app with FW 2.0
- cli.ini - it has always been a strong best practice to not attempt to use the cli.ini file with DB2 .NET apps, even though "some" settings appeared to work. The cli.ini file has never been supported with DB2 .NET apps. In V9.7 any accidental processing of the cli.ini file has been fully removed. The db2dsdriver.cfg file is a cross-API fully supported config file.

Resources

DB2 Express-C - Free download

DB2 and .NET FAQs