DB2 and Visual Studio 2008: Getting Started

Wednesday Jun 11th 2008 by Paul Zikopoulos
Share:

Paul Zikopoulos gets you started on the path to DB2 9 and Visual Studio 2008 for .NET developers including hints and tips to get you in the express lane when it comes to enabling .NET developers to build DB2 data-bound applications.

In a previous set of articles, I showed you all the great integration features between the IBM DB2 9 for Linux, UNIX, and Windows (DB2 9) data server and Microsoft Visual Studio 2005. In another series, I showed you the integration between DB2 and Visual Studio 2003. Quite simply, DB2 has been providing – in my opinion – the richest and most complete database integration for .NET developers, often setting the standard for other database vendors and defining database development productivity.

With DB2 9.5 Fix Pack (or the generally available version of DB2 9.5 with the latest IBM Database Add-ins for Visual Studio) DB2 now supports Visual Studio 2008 which became generally available earlier this year .

In this article, I want to get you started on the path to DB2 and Visual Studio 2008 for .NET developers; additionally, I’ll give you some hints and tips to get you in the express lane when it comes to enabling .NET developers to build DB2 data-bound applications.

Things to do in order to get started with Visual Studio 2008 and DB2

Before you get started using Visual Studio 2008 to build your .NET DB2 applications, you need to have the right client connectivity software to connect to the target data server and the component (referred to as an add-in) that provides the rich integration for your DB2 server.

As of DB2 9.5, if you want to connect your .NET application to a DB2 for Linux, UNIX, or Windows data server, you need to a minimum deploy the new IBM Data Server Driver for ODBC, CLI, and .NET. This new driver takes up a mere 10 MB and affords you the opportunity to use the smallest footprint possible for .NET connectivity. This driver supports the .NET 2.0, 3.0, and 3.5 frameworks, just like Visual Studio 2008. Before DB2 9.5, you had to install the DB2 Runtime Client (about 120 MB) to enable this connectivity. You can download the IBM Data Server Driver for ODBC, CLI, and .NET at: http://www-306.ibm.com/software/data/db2/windows/dotnet.html.

Once you’re able to connect to a DB2 server using .NET, you need to install the IBM Database Add-Ins for Visual Studio to get the rich integration provided between DB2 and Visual Studio 2008. As of DB2 9.5, this add-in is used for any IBM data server connections; whether you’re building a .NET application that runs on DB2 for Linux, UNIX, or Windows, DB2 for z/OS, DB2 for IBM i (formerly known as DB2 for i5/OS), or IBM Informix Dynamic Server (IBM IDS), you use the same add-in. This makes deployment for heterogeneous environments more streamlined because the IBM Database Add-Ins for Visual Studio is a mere 30 MB. In DB2 9, this add-in used to be called the IBM DB2 Add-In for Visual Studio 2005. As you can see, its name has since changed (the version information has been removed, as well as the DB2 moniker) to reflect the fact that the same add-in can now be used in not only the Visual Studio 2005 or Visual Studio 2008 integrated development environments (IDEs) but also for any IBM data server.

The only way to get this add-in in DB2 9 was to install a Windows-based DB2 Client or a server image; this meant a significant footprint (over 150 MB). Quite simply, DB2 9.5 gives you the opportunity to move the footprint required for .NET development from over 250 MB to a mere 40 MB by installing the IBM Data Server Driver for ODBC, CLI, and .NET and the IBM Database Server Add-Ins for Visual Studio. Keep in mind that if you wanted to connect this development environment to DB2 for z/OS or DB2 for i, you would need to add a DB2 Connect license into the connection flow (either directly or through a DB2 Connect gateway); however, this won’t affect the size of the footprint required for connectivity. You can download the IBM Database Add-Ins for Visual Studio at: https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=swg-vsai&S_PKG=dl&lang=en_US&cp=UTF-8.

Note: As of DB2 9, a DB2 data server release with a full version number (or a .5 version number) generally contains new functionality or significant upgrades (for example, DB2 9.5). In contrast, the client connectivity portion of DB2 (clients or drivers) is updated more frequently. Since they are not tied to the data server release schedule, maintenance upgrades (also called fix packs) are just as likely as new versions and point releases to contain new connectivity functionality. Our development laboratories work very hard to ensure backward compatibility. This allows you to gain the benefits of newer client-side functionality in a more dynamic nature. For example, Visual Studio 2008 support is part of the IBM Database Add-Ins for Visual Studio as of DB2 9.5 Fix Pack 1. IBM was able to deliver this timely support for Visual Studio 2008 because of this new client delivery architecture.

When you use this lightweight deployment option to configure your developer’s desktop, you should keep in mind that the IBM Database Add-Ins for Visual Studio and the IBM Data Server Driver for ODBC, CLI, and .NET must be at the same code level. For example, if you plan to support the Visual Studio 2008 IDE, you must install these components at the Fix Pack 1 level. If Fix Pack 2 were to bring other functional changes to either of these components, and you wanted to leverage them, you would have to ensure that both components were at Fix Pack 2.

For anything related to .NET development, this Web site is an all-inclusive starting point for pretty much anything you need to get started: www.ibm.com/software/data/db2/windows/dotnet.html. If you want a free copy of DB2 to get started with .NET development, you can find it at: www.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-db2expresscviper2. (You can even use this edition in production.)

Assuming you have configured your .NET development environment for DB2 (DB2 will take care of this automatically for you when you install the add-ins on a workstation where Visual Studio is already installed), when you start Visual Studio 2008 you should be able to see that the IBM Database Add-Ins for Visual Studio have been successfully registered:

Adding a DB2 Data Source

Before you can create a .NET application that works with a DB2 data server, or work with your DB2 data server using Visual Studio 2008 for that matter, you need to set up a database connection in the Visual Studio Server Explorer.

To add a DB2 database connection to your Server Explorer, perform the following steps:

1.      Right-click the Database Connections folder in the Server Explorer and select Add Connection:

2.      The Add Connection dialog box opens.

3.      Ensure that the Data Source field is set to IBM DB2, IDS, and U2 Servers (IBM DB2, IDS, and U2 Data Providers) like this:

If this is not the case, click Change, select IBM DB2, IDS and U2 Servers in the Data source box and the appropriate provider in the Data Provider box.

You can see there’s a data source option called IBM IDS Server. You would use this option to connect to versions of IBM IDS before Version 11.10. If you can, I strongly recommend using the IBM DB2, IDS and U2 Servers driver. Refer to the “Addendum: Two Informix .NET Drivers?” section at the end of this article for more details if you are connecting to an IBM IDS server.

4.      Enter the server name in the Select or enter server name field. You can click Refresh to return a list of DB2 servers discoverable on your subnet network.

5.      In the Enter information to log on to the server field, enter a user account that’s authorized to access that database using the User ID and Password fields:

If you select the Save my password check box, these credentials will be persisted so you don’t have to enter a password every time you connect to your data server in the Solution Explorer. For example:

6.      Enter the database alias name of the target database that you want to connect to in the Select or enter a database name field.

You can use this field’s drop-down list () to see all the databases in your local instance catalog.

7.      Expand the Specify connection options twistee and select both the Automatic refresh of connection when IDE loads and Run procedures to discover results sets options:

The Automatic refresh of connection when IDE loads check box instructs Visual Studio 2008 to refresh the schema cache every time the Visual Studio 2008 IDE starts. Part of the rich feature set in the IBM Database Add-Ins for Visual Studio is an asynchronous schema cache of your database connection. This feature enables you to work in a disconnected mode from your database and still have access to its schema objects for drag-and-drop form creation, design-time SQL assistance, and more. Normally, I recommend selecting this option because sometimes objects may have been created between Visual Studio 2008 sessions and they may not show up in the Server Explorer until the object cache is refreshed explicitly by the user or implicitly by Visual Studio itself; selecting this option automatically refreshes the schema cache when the IDE starts.

The IBM Database Add-Ins for Visual Studio 2008 also enables you to define new result sets – and views – for your stored procedures. A result set is the set of rows that a stored procedure returns for a SELECT statement. You can either discover result set definitions by specifying values for the input values, or you can manually define a result set and its columns. The Run procedures to discover results sets option instructs Visual Studio 2008 to run your stored procedures and define their results sets; if you don’t select this option, you’ll have to do this manually. For example, the following figure shows what happens when you click a stored procedure’s Results Sets node for a database connection that doesn’t have the Run procedures to discover results sets feature enabled:

In contrast, if you enable this feature, when you click on Results sets it would look like this:

8.      Optional: If your database has a large number of objects, you can filter the connection object you are creating by entering a schema name in the Schema filter field:

For example, the setting in the previous figure would have the effect of only showing those objects within the PAULZ schema in the Server Explorer. You can’t specify more than one schema to filter on in this window; if you want to filter on a different schema, you need to create a different database connection object.

You can use the Exclude system schemas check box as a higher-level schema qualifier such that only user schemas are shown in the Server Explorer. For example, you may have a number of user schemas in your database (PAULZ, TEST, CHLOE); if you left the Schema filter field empty but selected this option, it would hide objects within the SYSWS, SYSVIEWS, SYSSTAT, SYSPROC, SYSIBMADM, and SYSIBM schemas; this is a nice way to suppress seeing those administrative schemas that are part of every DB2 database but that you typically don’t interact with.

You can use the Select Table Type Filter window to select the types of tables that you want to see in the Server Explorer under a data server connection object. To filter the types of tables, click the table filter button ():

Using the Select Table Type Filter window, you can select either the default table types for a data server or the table types to show for a specific data connection.

If the check box for a given table type is selected, tables of that type are shown under a data connection for the appropriate data server. The options in the Select Table Type Filter are detailed below:

  • DB2 for Linux, UNIX, and Windows (LUW)
    • T - Table (untyped)
    • H - Hierarchy table
    • U - Typed table
    • S - Materialized Query Table (MQT)
    • N - Nickname
  • DB2 for i
    • T – Table
    • L – Logical
    • S - Physical
  • DB2 for z/OS (zOS)
    • T – Table
    • G - Created global temporary table
    • X - Auxiliary table
    • N - Nickname

I generally don’t use these options since I often connect to DB2 for Linux, UNIX, and Windows and DB2 for z/OS data servers. However, many developers who are connecting to a DB2 for i data server want to include physical tables/files, a concept that is not familiar to those of us who don’t develop on the DB2 for i platform.

9.      Select the objects you want to show in your database connection object. For this article, ensure that all the options are selected.

Note: The Object Name Filter column doesn’t do anything in this window.

You can use the following part of the Add Connection window to include or exclude objects from a database connection. This is useful if you want to set up multiple connections and even provide a layer of security over the database connections. For example, perhaps you have a set of application DBAs who are responsible to expose tables to application developers through views. While the application DBAs likely need access to facets of the underlying database exposed in the Solution Explorer, they are most likely to build routines that refer to a view security layer and shouldn’t be distracted with other objects such as tables, XML source, and so on. As you can see in the following figure, there are multiple connections with different filters in the Server Explorer:

In addition, you may want to set up multiple connections to the same database with different user accounts to represent different stages or facets of the application development life cycle. For example, perhaps you want to test a piece of code with production authorizations, or Q/A authorizations, or design, and so on:

At this point, your Modify Connection window should look similar to the following window:

10.  Click Test Connection to ensure that you’ve correctly specified the target data server and user account authorized to connect to the target data server. If the test was successful, you will receive a message confirming this fact:

At this point, you have a successful data server connection, and from here you can perform any actions supported by the Solution Explorer, start to build applications, and more.

Ready, Set, Develop...

In this article, I showed you how you can set up a database connection object to a target IBM data server. In addition, I showed you all of the unique and powerful features that are part of an IBM data server connection in Visual Studio 2008.

You should experiment and get to know the filter capabilities available for Visual Studio and IBM data servers; they are mostly unique and not offered by other competitive databases. They provide a rich and granular control framework that ultimately leads to better security and a more rapid application development experience.

In future installments of this series, I’m going to show you how to build applications using Visual Studio 2008 and DB2 9 – including a .NET 3.5 framework extension such as LINQ.

Addendum: Two Informix .NET Drivers?

When you create a data server connection in Visual Studio 2008, there are two drivers (shown at the top of the following list) that you can use to connect to an IBM IDS server:

The IBM IDS Server is shipped as part of the Informix Client SDK. The common driver for all IBM data servers is the IBM DB2, IDS and U2 Servers driver. The IBM IDS Server driver has been around for quite some time (since IDS Version 7). You can get full details about this driver at: http://publib.boulder.ibm.com/epubs/html/25124470/25124470tfrm.htm. This driver doesn’t utilize the Distributed Relational Database Architecture (DRDA) interface to communicate with an IDS server. In contrast, the IBM DB2, IDS and U2 Servers uses DRDA (just as it would for a DB2 data server connection) but this common driver can only be used for IDS Version 11.10 or later data servers. Using the common driver for IBM data servers provides you with common behavior for more efficient and predictable testing.

IBM DB2, IDS and U2 Servers is my recommendation for any new .NET applications that you may write (assuming the new driver supports all the features of Informix you need to leverage) In fact, as it is enhanced over time, it will be in synch with the DB2 .NET provider (since they have merged). Specifically, this common driver offers the following features over the earlier IBM IDS Server .NET provider:

  • 64-bit support
  • Schema metadata functions
  • Remote database enumeration
  • .NET framework 3.0 and 3.5 support

From a tooling perspective, the common driver uniquely provides the following Visual Studio 2008 integration for a more rapid application development experience:

  • Web application development
  • Internet Information Services (IIS) Web services generation
  • Designers to create tables, procedures, functions, and triggers
  • Designers to alter tables and triggers
  • The ability to view or alter data
  • Run procedures and functions

At the time of writing, the common driver doesn’t completely support all Informix server data types. For this reason, I generally recommend that any new development be done with the new common driver whereas migrated applications might still need functions provided by the IBM IDS Server .NET provider. Over the next few releases of the IBM DB2, IDS and U2 Servers driver, most of these migration issues should be addressed.

» See All Articles by Columnist Paul C. Zikopoulos

Trademarks

IBM, DB2, DB2 Connect, Distributed Relational Database Architecture, DRDA, i5/OS, Informix, iSeries, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, or service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2008.

Disclaimer

The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved