IBM DB2 Development Add-Ins for Visual Studio .Net

Document options Document options requiring JavaScript are not displayed New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

Level: Introductory

Abdul Al-Azzawe, Senior Software Engineer, IBM Silicon Valley Lab

01 Mar 2003

Attention all you .NET developers! It's easier than ever to develop DB2 applications because you never have to leave your Visual Studio .NET environment to take advantage of all the great development and administration tools offered by DB2.

Note from the editor: The IBM DB2 Development Add-Ins for Visual Studio .NET are available as part of DB2 V8.1.2 and later.

Related articles:

• DB2 Database Project for Visual Studio .Net
• Creating and Linking LOBs in a Data Application for Microsoft .Net using DB2 Universal Database

Introduction

With the IBM DB2 Development Add-In for Visual Studio .NET, you can:

• Add DB2 database projects to your solution that use the DB2 managed provider.
• Create DB2 scripts for creating and managing routines (stored procedures and user-defined functions), tables, views, indexes, triggers, and any other supported database elements.
• Use predefined code templates or script generation wizards.
• Explore the catalog information of your DB2 family of servers and view properties, source code, and data of tables, views, and routines.
• Drag and drop your server objects onto your Visual Basic and C# windows forms to automatically generate the required DB2 managed provider ADO.Net code.
• Configure your DB2DataAdapter, DB2Connection, and DB2Command managed provider toolbox controls.
• Launch various DB2 development and administration centers.

This article gives an overview of the key features. Future articles and tutorials on the DB2 Developer Domain will give more detailed information about specific features.

Back to top

Product availability and supported platforms

The IBM DB2 Development Add-in for Visual Studio .NET is a component of the IBM DB2 Application Development Client . The following client/server combinations are supported by the IBM DB2 Development Add-Ins as I write this article:

Table 1. Supported client-server platforms for the DB2 Development add-ins

When developing DB2 for z/OS and 390 SQL stored procedures, you will need to setup a proper WLM Environment and install the correct level of the build utility. The DB2 Development Add-Ins support a DSNTPSMP build utility level of 1.15. If you have a lower version build utility installed, you must upgrade your system.

For more information on developing stored procedures on the DB2 for z/OS and 390 platform, consult the following setup cookbook by Peggy Rader:

Getting Started with the DB2 Development Center to Create SQL and Java Stored Procedures on DB2 V7 on z/OS

Back to top

Registering and un-registering the add-ins

The IBM DB2 Development Add-In is installed on the client as part of DB2 Application Development Client of the DB2 Universal Database for Windows, Version 8.1.2.

Automatic registration of the add-ins and help

If you installed Visual Studio .NET before you installed DB2, the DB2 Development Add-In is registered automatically for you. If you installed Visual Studio .NET after you installed DB2, or if you modified your installation, you must manually register the add-ins.

Registering the add-ins and help

To register the IBM DB2 Development Add-In tools and help with Visual Studio .NET:

1. Exit Visual Studio .NET.
2. From any command window, run db2vsrgx.bat.
3. Start Visual Studio .NET.

Un-registering the add-ins and help

To un-register the IBM DB2 Development Add-In tools and help from Visual Studio .NET:

1. Exit Visual Studio .NET.
2. From any command window, run db2vsurgx.bat
3. Start Visual Studio .NET.

Un-registering the help only

To un-register the IBM DB2 Development Add-In help only from Visual Studio .NET:

1. Exit Visual Studio .NET.
2. From any command window, run db2vsreg -unregister doc
3. Start Visual Studio .NET.

Back to top

Overview of the integrated development features

The key integrated development features of the IBM DB2 Development Add-Ins for Visual Studio.Net include:

• Solution explorer - To enable database developers to build DB2 server-side objects, including routines, triggers, tables, and views, using script files.
• Server explorer - To give application developers access to DB2 server-side objects from any tier of their application, including Win/Web forms in the presentation layer, and class assemblies / Web services in the business and data access layers.
• SQL editor - To enable application developers to use the Visual Studio .NET editor to edit and view DB2 scripts.
• Dynamic help - To provide application developers with fast access to various DB2 Development Add-Ins help topics.
• Output views - To provide a medium for showing the results of compiling or testing the various DB2 script files and server objects.
• Managed provider - To enable application developers to code to the DB2 server side objects using ADO.Net programming model.

In addition, database developers can launch development and and administration tools from a toolbar.

Solution explorer - IBM projects

The Visual Studio .Net solution explorer provides you with an organized view of your projects and their files as well as ready access to the commands that pertain to them.

You can add a variety of project types to each Visual Studio .NET solution including C#, Visual Basic, and database projects, to name a few. Each project can contain one or more folders and subfolders for grouping project items. For a typical database project, these project items include queries, procedures, functions, tables, and views.

The IBM DB2 Development Add-In extends the Visual Studio .NET projects templates to include an IBM Projects folder with a specific DB2 Database Project that may be created and managed in the Solution Explorer.

DB2 database project
The DB2 database project lets you use script-based development of server-side objects. Each script file can contain DB2 data definition language (DDL) and data manipulation language (DML) SQL statements. Using these scripts, you can create SQL stored procedures, SQL user-defined functions, tables, views, indexes, triggers, types, and so on.

With a DB2 database project, you can:

• Add new or existing SQL stored procedure scripts.
• Add new or existing SQL user-defined function (UDF) scripts.
• Add new or existing scripts based on generic templates that contain supported DB2 data definition language (DDL) and data manipulation language (DML).
• Specify project dependencies and project build order in the solution.
• Specify build configuration options including script files build order.
• Check your script files into any configured source control management system such as Microsoft Visual Source Safe.

Script template files
The DB2 database project provides you with an initial set of predefined script templates. See Table 2 for a complete list. These script files serve as an initial starting point for developing DB2 scripts.

With Visual Studio .NET customization options, you can also add your own set of DB2 script templates that may apply to your project requirements.

Table 2. Script templates for a DB2 database project

Item	Description
	Script to drop and then create a sample SQL stored procedure. You can modify the script to include additional supporting SQL such as granting access for the procedure, altering the procedure, and so on.
	Script to drop and then create a sample SQL scalar user-defined function. You can modify the script to create SQL table functions or to include additional SQL for managing or testing the table function.
	Script to drop and then create a sample table. You can expand the script to include advanced table properties, such as table spaces, indexes, constraints, and referential integrity rules.
	Script to drop and then create a sample table view.
	Script to drop and then create a sample trigger. Multiple triggers may be defined in the same script file.
	Script to query data from database tables. Multiple queries may be defined, including other SQL statements like INSERT, UPDATE, and DELETE.
	Blank generic script. Any valid DB2 SQL may be defined in this script. One great way to use this type of script would be to write test scripts that are not part of the project build, but which can be executed as required to test some server-side objects.

Script wizards
Apart from the pre-defined script templates described in Table 2, the DB2 database project also provides you with a set of script generation wizards that allow you to customize the generated script files.

Table 3. Script wizards for a DB2 database project

Item	Description
	The DB2 SQL Stored Procedure wizard guides you through the steps required to customize an SQL procedure script, including adding one or more SQL statements, specifying parameters, and inserting code and script fragments from flat text files.
	The DB2 SQL User-Defined Function wizard guides you through the steps required to customize an SQL function script similar to the procedure script customization with the additional support for generating scalar or table functions.

Expect to see additional enhancements in this area, because we are planning on adding functionally rich wizards.

Server explorer - IBM Explorer

The IBM DB2 Development Add-In extends the Visual Studio .NET environment by adding a new tool window called IBM Explorer. The IBM Explorer provides Visual Studio .NET users with access to IBM database connections using the Data Connections folder. The Data Connections folder in the IBM Explorer is similar to the Data Connections folder in the Server Explorer, but is specifically designed for DB2 managed provider connections.

The Data Connections folder in the IBM Explorer enables you to:

• Work with multiple named DB2 connections supporting connect on demand technology.
• Specify database catalog filters and local caching for higher performance and scalability.
• View properties of server objects including tables, views, and routines.
• Retrieve data from tables and views.
• Execute test runs for routines.
• View source code for SQL stored procedures and user-defined functions.
• Generate ADO .NET code using drag and drop.

Working with connections
Using the Add Connection menu entry off the data connections folder, you can add any number of DB2 managed provider connections. These connections will have a default name of DatabaseAlias.UserName; however, you can rename the connection as required.

While adding a new connection, you can specify a default schema (objects owner) filter, which will be propagated to the connection subfolders for tables, views, and routines.

After a data connection is added, all of the subfolders are created, and populated on demand. This allows for specifying a more detailed subfolder filter before retrieving the catalog information for that folder.

You can delete DB2 connections from the data connections folder at any time. Deleting a data connection simply removes the entry from the folder and not from the database server. All cached information for that connection will be lost.

Working with tables and views
Using the tables and views folder of a DB2 connection in the data connections folder of the IBM Explorer you can:

• Specify a sub-folder filter based on a combination of schema name and table or view names.
• Refresh folder's list of tables and views from the server.
• Retrieve data from the table or view and display it in a read-only data grid.
• Display table or view properties as well as column properties in the standard Visual Studio .NET properties sheet.
• Refresh an individual table or view including its columns information from the database server.
• Delete the table or view causing that entry to be deleted from the database server.
• Drag and drop the table or view onto a form causing ADO.NET objects to be initialized and added to the component tray of the form where proper code is generated.

Generating ADO.NET code for tables or views
When you drag and drop a table or view onto a form, the entry catalog information is inspected to generate proper DB2 ADO.NET managed provider objects:

• A new DB2Connection object is created and initialized if one does not exist already in the form's components tray.
• A new DB2DataAdapter object are created and initialized.
• Four new DB2Command objects are created and initialized to perform an SQL SELECT, UPDATE, INSERT, and DELETE for the selected entry columns.
• Multiple DB2Parameter objects based on the primary and foreign key information for the table or view are generated to uniquely identify a specific data row.

Only the DB2Connection and DB2DataAdapter objects are visible in the component tray. The four DB2Command objects and their parameters are invisible.

See Managed provider tools for information on how to modify and optimize these ADO.Net objects.

Working with procedures and functions
Using the procedures and functions folder of a DB2 connection in the data connections folder of the IBM Explorer you can:

• Specify a subfolder filter based on a combination of schema name and procedure/function names and language.
• Refresh folder's list of procedures and functions from the server.
• Execute a test run of a stored procedure or function (any language), specifying the values for any input parameters and displaying the output data set including any output parameters in a read-only data grid.
• Display procedure or function properties as well as parameter properties in the standard Visual Studio .NET properties sheet.
• Refresh an individual procedure or function including its parameter information from the database server.
• Display the SQL source code for a procedure or function.
• Delete the procedure or function causing that entry to be deleted from the database server.
• Drag and drop the procedure or function onto a form causing ADO.NET objects to be initialized and added to the component tray of the form where proper code is generated.

Generating ADO.NET code for procedures and functions
After you drag and drop a procedure or function onto a form, the catalog information is inspected to generate proper DB2 ADO.NET managed provider objects:

• A new DB2Connection object will be created and initialized if one does not exist already in the form's components tray.
• A new DB2Command object will be created to invoke the selected entry.
• Multiple DB2Parameter objects based on the parameters information for the procedure/function.

The DB2Connection and corresponding DB2Command objects will be visible in the component tray. The command parameters will be invisible.

See the Managed provider tools section below for information on how to reconfigure and optimize these ADO.Net objects.

SQL editor

The IBM DB2 Development Add-In extends the native Visual Studio .NET editor with a specialized DB2 SQL editor. With the editor you can view and modify your DB2 SQL code and script files as shown in Figure 4.

The DB2 SQL editor includes the following features:

• Colorized SQL text for increased readability based on DB2 SQL syntax.
• Integration with the Microsoft Visual Studio .NET IntelliSense feature, which allows for intelligent auto-completion while typing DB2 scripts. This includes object names for a given schema, column names for tables or views, and parameters for procedures and functions.
• An editor menu that lets you insert frequently used SQL code fragments.

Dynamic help

The IBM DB2 Development Add-In help integrates tightly with the Visual Studio .Net help facility. You can access the content-based help for a specific topic, or you can view the dynamic help based on the development context you are in.

Output views

The DB2 Development Add-In for Visual Studio .NET makes use of the standard views to show results of various actions.

Displaying data sets
A read-only DB2 output data grid is shown whenever you execute a development task that results in a data set. This includes:

• Issuing a test run on any language stored procedure or user-defined function.
• Retrieving data from tables or views.
• Executing DB2 scripts that calls stored procedures or user-defined functions.
• Executing DB2 scripts that insert, deletes, updates, or selects data from DB2 tables or views.

Displaying messages
The standard output pane is used to display compile results for project script items. More detailed information pertaining to executing development tasks against DB2 servers is displayed in the IBM DB2 Message Pane. This includes messages resulting from executing DB2 scripts, or catalog access queries.

Back to top

Customizing the DB2 development tools

You can customize the IBM DB2 Development Add-In for Visual Studio .NET by setting various tools options using the standard Visual Studio .NET Options dialog. The Options dialog can be launched using the Tools -> Options... menu entry. Select the IBM DB2 Tools Options folder.

At the time this article was written, not all of the options were included in the Options dialog. You can manually modify all of the supported options shown in Table 4 by accessing the userOptions.xml file under your %APPDATA%\IBM\vsnet folder. Make sure to exit Visual Studio .NET before modifying this file. To revert back to the default settings, simply delete this file.

Table 4. Options you can change

Edit the userOptions.xml file for a complete list of options with their descriptions.

Native managed provider

IBM DB2 Universal Database V8.1 FP2 ships with a .NET managed provider, IBM.Data.DB2, which is specifically designed to work with DB2 servers. This native managed provider can give you significant performance improvement over the OLE DB and ODBC bridges, and it also provides the ability to exploit the specific features of DB2 servers.

DB2 managed provider ADO.Net objects
The DB2 managed provider classes are similar to Microsoft SQL Server (System.Data.SqlClient) classes. They include:

• IBM.Data.DB2.DB2Connection - This object establishes a connection to a DB2 data source.
• IBM.Data.DB2.DB2Command - This object executes an SQL statement or stored procedure call against a DB2 data source.
• IBM.Data.DB2.DB2CommandBuilder - This object automatically generates commands to reconcile changes made to a DataSet with the associated DB2 data source.
• IBM.Data.DB2.DB2DataAdapter - This object defines a set of data commands and database connection that are used to fill a DataSet and update the DB2 data source.

DB2 objects in the Visual Studio .NET Data toolbox
When the DB2 managed provider is installed, a set of DB2 ADO.NET objects are added to the Data tab of the Visual Studio .NET toolbox. These objects include the DB2DataAdapter, DB2Connection, and DB2Command.

You can drag and drop these controls and drop them on a form, which adds the appropriate DB2 managed provider objects to your form's design-time component tray. Appropriate ADO.NET code is generated to initialize these objects. See the Managed provider tools section for information on how to re-configure and optimize these ADO.Net objects.

Sample DB2 ADO.Net code
Listing 1 is a sample DB2 ADO.Net code that creates a data adapter and binds it to a data grid.

				// Create a connection string and make the connection to a database
string connectionString = "Database=Sample";
DB2Connection myDB2Connection = new DB2Connection();
myDB2Connection.ConnectionString = connectionString;

// Now create a DB2Command to present a SELECT statement
DB2Command db2SelectCommand = new DB2Command();
db2SelectCommand.CommandText = "Select * from Customers";
db2SelectCommand.Connection = myDB2Connection;

// Create a DataAdapter for executing the Select statement
DB2DataAdapter da = new DB2DataAdapter();
DB2DataAdapter.SelectCommand = db2SelectCommand;

// Create DataSet object and fill it with data
DataSet ds = new DataSet();
da.Fill(ds, "Customers");

// Attach this dataset to a data-bound visual control (DataGrid).
DataGrid dataGrid1 = new DataGrid();
dataGrid1.DataSource = ds.DefaultViewManager;

Managed provider tools

The IBM DB2 Development Add-Ins for Visual Studio .NET provides integrated user interfaces to surface some of the features of the DB2 managed provider.

DB2 Data Connection editor
The Data Connection editor lets you choose a DB2 managed provider data connection from the list of connections in the IBM Explorer. You also have the option of adding a new connection. When a connection is chosen, the DB2 managed provider connection string is automatically generated for the DB2 ADO .NET connection object.

The Data Connection editor dialog is shown when clicking the ellipses (...) for the connection string property of the DB2 ADO.Net connection object shown in the design-time component tray of a form.

DB2 Data Adapter configuration wizard
Use the DB2 Data Adapter configuration wizard to set or modify the various properties of a DB2 ADO .NET Data Adapter object. These properties include the data connection string and the SELECT, INSERT, UPDATE, and DELETE statements and parameters.

The Data Adapter configuration wizard is automatically launched when you drop a data adapter control from the toolbox onto a form. You can launch it any time to reconfigure an existing data adapter in the form's design-time component tray.

The DB2 Data Adapter Configuration wizard takes you through the following steps:

1. Select a data connection from the drop-down list. This list includes the data connections that are currently in the IBM Explorer. If you want to add a new connection, click New Connection. The DB2 Data Adapter will use the specified database connection information to load and update data.
2. Specify statement options. If your DB2 data adapter is used to update the data source, the wizard can generate the INSERT, UPDATE, and DELETE statements. The wizard will also step you through the generated statements for further modification and optimization. The DB2 Data Adapter will use the SQL INSERT, UPDATE, and DELETE statements to update the data source.
3. Specify the SQL SELECT statement and its parameters. The DB2 Data Adapter will use the specified SELECT statement to load the data into the data set and to generate the INSERT, UPDATE, and DELETE statements.
4. Specify the optional SQL INSERT statement and its parameters. The INSERT statement is used to add new data to the data source. You can either generate the statement or type the statement.
5. Specify the optional SQL UPDATE statement and its parameters. The UPDATE statement is used to update the data source. You can either generate the statement or type the statement.
6. Specify the optional SQL DELETE statement and its parameters. The DELETE statement is used to delete data from the data source. You can either generate the statement or type the statement.

DB2 Command text editor
The DB2 Command text custom editor allows for entering the appropriate SQL SELECT, INSERT, UPDATE, or DELETE statements as well as the list of command parameters for a DB2 ADO.NET command object.

The DB2 command text editor appears when you click the ellipses (...) for the command text property of the DB2 ADO.Net command object shown in the design-time component tray of a form. It can also be launched when clicking the ellipses (...) for the command text property of the DB2 ADO.Net command object that is part of a DB2 Data Adapter object.

The SQL tab of the command text editor lets you specify the actual SQL statement SQL; the Parameters tab lets you define and configure the list of command parameters. Any parameter markers specified using the question mark (?) or a colon (:) followed by the parameter name (:ParameterName) are automatically added as parameters to the command in the Parameters tab.

Back to top

Launching DB2 development and administration tools

You can launch DB2 development and administration tools from the Visual Studio.Net IDE, including:

• Development Center
• Command Center
• Control Center
• Task Center
• Health Center
• Journal Center
• Replication Center
• Information Center

You can launch these tools either from the the DB2 Tools toolbar (Figure 12) or by Tools -> IBM DB2 Tools ().

DB2 Development Center

Use the Development Center to develop routines such as SQL and Java (JDBC" or SQLJ) routines.

Using the Development Center, you can:

• Create routines and structured types.
• Build routines and structured types on local and remote DB2 servers.
• Modify and rebuild existing routines.
• Run routines for testing and debugging.
• Deploy routines from a development project or database to a production server.

The Development Center provides a single development environment that supports the entire DB2 family ranging from the workstation to z/OS.

DB2 Command Center

Use the Command Center to execute DB2 commands and SQL statements, to execute z/OS or OS/390 host system console commands, to work with command scripts, and to view a graphical representation of the access plan for explained SQL statements.

DB2 Control Center

Use the Control Center to manage systems, DB2 Universal Database instances, DB2 Universal Database for OS/390 and z/OS subsystems, databases, and database objects such as tables and views. In the Control Center, you can display all of your systems, databases, and database objects and perform administration tasks for those objects.

DB2 Task Center

Use the Task Center to organize task flow, schedule tasks, and distribute notifications about the status of completed tasks. You can create tasks using a script that contains DB2, operating system, or MVS JCL commands.

DB2 Health Center

The Health Center is the tool that identifies key performance and resource allocation problems within DB2, through notifications such alarms or warnings, and provides recommended actions that can help to resolve the problems.

DB2 Journal

The Journal diplays historical information about tasks, database actions and operations, Control Center actions, messages, and alerts.

DB2 Replication Center

Use the Replication Center to administer relational data between DB2 servers or databases. From the Replication Center, you can define replication environments, copy designated changes from one location to another, and synchronize the data in both locations.

DB2 Information Center

Use the Information Center that is accessed from the DB2 tools to update the HTML documentation that is installed from the DB2 HTML Documentation CD and to find instructions, answers, and other information about DB2 Universal Database.

Back to top

Conclusion

The IBM DB2 Development Add-In for Microsoft Visual Studio .NET provides you with a set of seamlessly integrated application development tools for developing applications specifically designed for IBM DB2 Universal Database servers.

With the IBM DB2 Development Add-In, you can now use your Visual Studio .NET IDE to develop applications that leverages the power and rich functionality of the IBM DB2 Universal Database for z/OS, Windows, Linux, and UNIX servers.

Some of the advantages of DB2 tooling support over other databases include:

• Support for DB2 managed provider data connections from the IBM Explorer.
• Multiple default script items templates with advanced scripting options.
• Full featured set of scripting wizards for generating DB2 specific DDL and DML scripts.
• Support for multiple configuration options allowing for possibly different data connection and script build options.
• Define script items build order within a project.
• Content and dynamic help support.
• Local client caching of catalogue data including procedure and function SQL code.
• Early filtering of catalogue data.
• Multiple concurrent and asynchronous database task execution.
• Specialized output pane for detailed tracking of database tasks.

Stay tuned for more exciting features that will be added in future releases.

Back to top

Acknowledgments

Being the IBM DB2 Development Add-Ins product architect was a very rewarding experience for me. I had the opportunity to work with extremely talented IBMers who lived up to the challenge of delivering high-quality, functionally rich products in a very short period of time:

• Sonali Surange for the DB2 Tools toolbar, the Data Connections folder of the IBM Explorer, and the data access layer;
• Ardeshir Jamshidi for the scripting wizards, managed provider tools, and ADO.Net code generation;
• Kevin Taylor for the specialized DB2 Database project and integration with SCM (Visual Source Safe);
• Eric Johnson for the SQL editor and script parser;
• Ajay Aggrawal and Veera Maddipati, the quality assurance team;
• Seth Packham and Tamara Khaleel for the online documentation;
• Kathleen Nojima for the artwork; and finally,
• Kreg Holgerson and Connie Nelin for their great leadership.

Special thanks to the Toronto team, Brent Gross (manager), Kelvin Ho, Nancy Taiyab, Samuel Oosterhuis, and Alvin Fung for delivering the native DB2 .NET Managed Provider that made the integration with Visual Studio .NET possible.

Back to top

Notices

IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing
IBM Corporation
North Castle Drive Armonk, NY 10504-1785
U.S.A.

All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only.

IBM, DB2, and DB2 Universal Database are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

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

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

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

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

IBM copyright and trademark information

Resources

• Building Web Services with C# and DB2 tutorial
  

About the author

		Abdul H. Al-Azzawe is a senior software engineer at the IBM Silicon Valley Lab in San Jose. An IBM veteran, he joined the company in 1990. Abdul is the lead architect for the SQL debugger and the application development tools for DB2 on Windows. Prior to his current assignment, he was a member of the core DB2 engine development team at the IBM Toronto Lab.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top