DB2 Development Add-Ins for Visual Studio 6.0 - The Next Generation AD Tooling

DB2 Version 8 promises to be an exciting release for developers. One feature designed specifically for Microsoft Visual Studio developers is the new integrated DB2 Development Add-Ins for Visual Studio 6.0, which includes tools to make your job easier.

Share:

Abdul Al-Azzawe, DB2 Application Development Support, IBM Silicon Valley Lab

Photo: Abdul H. Al-AzzaweAbdul 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 next-generation application development tooling for DB2. Prior to his current assignment, he was a member of the core DB2 engine development team at the IBM Toronto Lab. Special thanks to Kiran Potu of the Silicon Valley Lab for her extensive contributions to the Visual Studio Add-Ins development.



08 August 2002

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

For developers, one of the most exciting new features of DB2® Universal DatabaseTM Version 8 is the introduction of a new set of Add-Ins for Visual Studio 6.0. Anyone familiar with Visual Studio 6.0 knows that it contains a set of rich database application development tools. These tools include the Data View for exploring your database connections, the Query Builder for generating new SQL queries against tables in your Data View, and the Schema Designer for building new set of tables. Although most of these tools work with DB2 connections, the functionality they offer is somewhat limited for DB2.

The DB2 Development Add-Ins for Visual Studio not only address these limitations, but they also provide you with additional features above and beyond those provided for other DBMSs, making DB2 the database server of choice for application development.

The purpose of this article is to introduce you to this exciting new DB2 tooling for Visual Studio developers.


Technical highlights

The DB2 Development Add-Ins for Visual Studio 6.0 are designed to provide database developers with server-side development features similar to what is provided using the new DB2 Development Center, and client-side development features such as automatic ActiveX Data Objects (ADO) code generation for your server objects, and source control management using Visual Source Safe.

For more information about the DB2 Development Center, refer to the overview article, DB2 Development Center - The Next-Generation AD Tooling .

Key features

Some of the key features of the Development Add-Ins for Visual Studio include:

Visual Studio integration

  • Integrates tightly into Visual Basic, Visual C++, and Visual InterDev 6.0 IDEs using a DB2 Development View.
  • Support for source code control management using Visual Source Safe 6.0.
  • Client-side ADO code generation for DB2 connections, procedures, and functions.
  • Client-side Data Environment object creation for DB2 connections, procedures, and functions.

Functionality

  • Support for the entire family of DB2 servers including DB2 Universal Database for z/OS™, DB2 for iSeries™, DB2 for UNIX®, Linux for 390, and Windows®.
  • Enhanced z/OS support including specialized SQL IDs (package owner, build owner, and secondary SQL ID) and advanced build options.
  • Support for developing SQL and Java™ stored procedures, SQL scalar and table user-defined functions, MQSeries®, OLE DB, and XML table functions, and structured data types for EJB methods and properties.
  • Support for viewing live database tables, views, triggers, stored procedures, and user-defined functions.

Project management

  • Import of stored procedures and functions from databases, other projects, and other source files.
  • Deployment tool for exported DB2 Development Center projects with binary deployment support for like DB2 servers.

Testing and debugging

  • Ability to test stored procedures and user-defined functions in any supported language.
  • Saved object test settings, including parameter values, and pre-execution and post-execution SQL scripts.
  • Enhanced debugging of SQL stored procedures with variable value change support using an integrated SQL debugger.
  • Simplified debugging of Java stored procedures using the IBM VisualAge#xae distributed debugger.

Scalability and performance

  • Online (connected) and offline (disconnected) database connection support with connection pooling.
  • Advance filtering prior to database catalog data retrieval.
  • Client caching and persistence of the database catalog data to avoid re-queries.
  • Concurrent task execution using multi-threading.

Registering the DB2 Development Add-Ins

Prior to using the Add-Ins for Visual Studio, you will need to register the Add-Ins on your system. You can register the Add-Ins in one of two ways:

  • Using your task bar by clicking Start=>Programs => IBM DB2 => Set-up Tools =>Register Visual Studio Add-Ins
  • Using a DOS window, run the following command: db2vscmd register

Registering the Add-Ins add the appropriate Windows registry entries and make the Add-Ins available through Visual Studio. For proper registration, make sure to launch the various Visual Studio components at least once before registering the Add-Ins. This is to ensure that your Visual Studio user profile has already been created.

If you already had Visual Studio installed prior to installing DB2, then the registration command is run automatically.

Tip: The DB2 Development Add-In makes use of a number of the DB2 Development Center dialogs and wizards. Because of some minor interoperability limitations between Java and Visual Basic, some of these views may get obstructed by the Visual Studio IDEs. To alleviate this problem, most of the DB2 Development Center views are shown at the top left corner of your desktop; make sure to leave that space visible and do not maximize your Visual Studio IDE window.


DB2 Development Add-In for Visual Basic 6.0

The DB2 Development Add-In for Visual Basic is tightly integrated into the Visual Basic IDE. Once activated, the Add-In:

  • Inserts the appropriate DB2 menu entries into your Visual Basic View, Tools, and Help menus
  • Creates a DB2 Tools toolbar for launching the various DB2 Centers and tools
  • Shows the DB2 Development and Output views
Figure 1. DB2 Development Add-In for Visual Basic
DB2 Development Add-In for Visual Basic

The Development View is a dockable view that is implemented using the Visual Basic UI framework. All other secondary views, including the output view, wizards, and dialogs, are floating DB2 Development Center views.

One of the key features of the Add-In is its client-side Visual Basic ADO code generation and Data Environment object creation for DB2 connections, procedures, and functions.

Launching the DB2 Development Add-In for Visual Basic
To launch the DB2 Development Add-In from Visual Basic, you must first have at least one active Visual Basic project. Then choose Add-ins => IBM DB2 Development Add-In . The DB2 Development View is displayed after all the resources are loaded and initialized.


DB2 Development and Tools Add-Ins for Visual C++ 6.0

The DB2 Add-Ins for Visual C++ are tightly integrated into the Visual C++ IDE. There are two Add-ins: a DB2 Development Add-In and a DB2 Tools Add-In. When activated, the Tools Add-In creates a DB2 Tools toolbar for launching the various DB2 Centers and tools. The DB2 Development Add-In creates a DB2 Development toolbar for accessing the development, output, and editor views.

Figure 2. DB2 Development Add-In for Visual C++
DB2 Development Add-In for Visual C++

The Development View is a floating tool window that is implemented using the Visual C++ UI framework. All other secondary views, including the output view, wizards, and dialogues, are floating DB2 Development Center views. The Development View cannot be docked into the VC++ IDE; however, you can toggle the visibility of this view from the DB2 Development toolbar.

Launching the DB2 Development and Tools Add-Ins for Visual C++
To launch the DB2 Development and Tools Add-Ins from Visual C++, you must first have at least one active Visual C++ project. You can then launch the Add-Ins from Tools =>Customize , by selecting the DB2 Development and Tools Add-In entries from the Macros and Add-Ins tab.

The DB2 Development View is displayed after all the resources are loaded and initialized.


DB2 Development Add-In for Visual InterDev 6.0

The DB2 Development Add-In for Visual InterDev is tightly integrated into the Visual InterDev IDE. When activated, the Add-In:

  • Inserts the appropriate DB2 menu entries into your Visual InterDev View, Tools, and Help menus.
  • Creates a DB2 Tools toolbar for launching the various DB2 Centers and tools.
  • Shows the DB2 Development and Output views.
Figure 3. DB2 Development Add-In for Visual InterDev
DB2 Development Add-In for Visual InterDev

The Development View is a dockable view that is implemented using the Visual InterDev UI framework. All other secondary views, including the output view, wizards, and dialogs, are floating DB2 Development Center views.

One of the key features of the Add-In is its client-side VBScript ADO code generation for Active Server Pages (ASPs) for DB2 connections, procedures, and functions.

Launching the DB2 Development Add-In for Visual InterDev
To launch the DB2 Development Add-In from Visual InterDev, you must first have at least one active Visual InterDev project. Then choose Tools => IBM DB2 Development Add-In .

The DB2 Development View is displayed after all the resources are loaded and initialized.


Working with projects

You can associate a DB2 Development Add-In project with any Visual Studio project. When first launching the Add-In, a new DB2 project having the same name as your Visual Studio project is created and is associated with your Visual Studio project. By default, the project is saved in a dc subdirectory of your Visual Studio project. You can choose to keep the same name and path, or save the DB2 project under a different project name and path using Save As , or by changing the name and path in the project properties dialog. Regardless of your choice, the Visual Studio project is associated with the last saved DB2 project.

The next time you launch the Add-In for the same Visual Studio project, the associated DB2 project is opened for you automatically (or is created). You can elect to open another DB2 project.

Tip: Make sure to properly name and save your Visual Studio project prior to launching the Visual Studio Add-In for the first time. This will ensure that your DB2 project will have the proper default name and path as opposed to your having to change this manually afterward.


Working with connections

You can add one or more DB2 database connections to your DB2 Development View project. To add a connection, simply select Add Connection for the project to launch the Add Connection wizard.

Figure 4. Add DB2 Connection wizard
Add DB2 Connection wizard

When adding a connection, it is generally a good idea to specify the schema filter option to limit catalog data access to those objects you are most interested in. This filter option will be inherited by all of the connection subfolders (procedures, functions, and so on). The connection wizard also lets you specify additional connection options, including OS/390 secondary SQL IDs.

Generating ADO connections

The DB2 Development Add-In generates the client-side ADO code that is required to establish a connection to the DB2 server using the native IBM OLE DB provider, IBMDADB2. You can generate the ADO code by opening a code window, then selecting Add ADO Connection Code for the desired DB2 connection in the Development View.

Figure 5. An example of Visual Basic ADO connection code


'create and return ADO Connection Object.
Public Function SAMPLE_GetConnection( _
Optional strUserName As String = "", _
Optional strPassword As String = "") As ADODB.Connection
On Error GoTo SAMPLE_ErrHandler

Dim strConnectionString As String
strConnectionString = "Provider=IBMDADB2;DSN=SAMPLE"
If strUserName <> "" And strPassword <> "" Then
strConnectionString = strConnectionString& _
"; User ID=" & strUserName
& "; Password=" & strPassword
End If
'Create new ADO connection object
Dim adoConnection As New ADODB.Connection
With adoConnection
.CursorLocation = adUseClient
.ConnectionString = strConnectionString
End With

'Open ADO Connection Object
'Call adoConnection.Open()

'Return new ADO connection object
Set SAMPLE_GetConnection = adoConnection
Exit Function

SAMPLE_ErrHandler:
MsgBox "Error Code: "& Err.Number & vbNewLine& _
"Description: " &Err.Description & vbNewLine & _
"Source: " & Err.Source, _
vbOKOnly +vbCritical
Err.Clear
Set SAMPLE_GetConnection =Nothing
End Function

The generated code can be VB for Visual Basic or VBScript for Visual InterDev. The generated code is automatically inserted at the bottom of your code window for Visual Basic; however, for Visual InterDev the code generated will appear in the Output View. Cut and paste this code from the Output View to the desired ASP code window.

Creating data environment connections

Apart from ADO code generation, the DB2 Development Add-In for Visual Basic lets you create Connection objects for your project data environments. These objects are required when developing data-bound forms. Simply select Add ADO Connection Object for the desired DB2 connection in the Development View, then select the data environment where the new connection is to be added and the ADO connection object name.

Figure 6. Adding a data environment connection object
Adding a data environment connection object

Working with procedures

The DB2 Development View lets you specify filters prior to enumerating the DB2 server-side stored procedures. Regardless of any filter you may specify, updated and unbuilt ("dirty") stored procedures are always shown in the stored procedures folder.

You can view the properties, and test stored procedures of any language; for SQL and Java stored procedures you can also view and modify the source, and build and drop the procedures. At the time of this article, Java support is available for all platforms except for iSeries™.

You can create new SQL and Java stored procedures using the Create Stored Procedure wizard. You can debug SQL stored procedures using the integrated SQL debugger, and you can debug Java stored procedures using the IBM Visual Age distributed debugger.

For more information, see Developing stored procedures in the DB2 Development Center overview article.

Generating ADO commands for procedures

The DB2 Development Add-In generates the client-side ADO code that is required to call a DB2 stored procedure using an ADO command object. You can generate the ADO code by opening a code window, then selecting Add ADO Command Code for the desired stored procedure in the Development View.

Figure 7. An example of Visual Basic ADO Command code for a stored procedure


'Create and execute a command object. Return a RecordSet object
Public Function PROCEDURE1_ExecuteCommand(ByRef adoConnection As ADODB.Connection, _
ByVal vvar0 As Long, _ByRef vvar1 As String) _
As ADODB.Recordset
'create ADOCommand Object.
Dim adoCommand As ADODB.Command
Dim adoRecordSet As ADODB.Recordset

On Error GoTo PROCEDURE1_ExecuteCommand_ErrHandler

Set adoCommand = New ADODB.Command

With adoCommand
.CommandType = adCmdStoredProc
.ActiveConnection = adoConnection
.CommandText= "ABDUL.PROCEDURE1" End With

'Add Parameters to the Command object
Dim adoParm As Parameter
Set adoParm = adoCommand.CreateParameter("var0", _
adInteger,_
adParamInput, _
Value:=vvar0)
Call adoCommand.Parameters.Append(adoParm)
Set adoParm = adoCommand.CreateParameter("var1", _
adVarChar, _
adParamOutput, _
4000)
Call adoCommand.Parameters.Append(adoParm)
Set adoRecordSet = New ADODB.Recordset
adoRecordSet.Open adoCommand, , adOpenStatic,adLockOptimistic

'Get the output values
vvar1 = adoCommand.Parameters("var1").Value
'Return the record set object object
Set PROCEDURE1_ExecuteCommand = adoRecordSet
Set adoCommand = Nothing
Exit Function

PROCEDURE1_ExecuteCommand_ErrHandler:
If adoConnection.Errors.Count > 0 Then
MsgBox "Error code: " &adoConnection.Errors(0).Number &vbNewLine & _
"Description: " &adoConnection.Errors(0).Description &vbNewLine & _
"Source: " &adoConnection.Errors(0).Source, _
vbOKOnly +vbCritical
adoConnection.Errors.Clear
Else
MsgBox "Error code: " & Err.Number& vbNewLine & _
"Description: "& Err.Description & vbNewLine& _
"Source: " & Err.Source, _
vbOKOnly + vbCritical
Err.Clear
End If End
Function

The generated code can be VB for Visual Basic or VBScript for Visual InterDev. The generated code is automatically inserted at the bottom of your code window for Visual Basic; however, for Visual InterDev, the code generated will appear in the Output View. You will have to cut and paste this code from the Output View to the desired ASP code window.

The generated code performs the default data mapping between DB2 SQL data types and ADO data types for the procedure parameters.

Creating data environment commands for procedures

Apart from ADO code generation, the DB2 Development Add-In for Visual Basic lets you create data environment Command objects for your project's stored procedures. These objects are required when developing data-bound forms. Simply select Add ADO Command Object for the desired DB2 stored procedure in the Development View, then select the data environment and connection where the new command for invoking the selected stored procedure is to be added. You can also specify the ADO command object name.

Figure 8. Adding a data environment command object
Adding a data environment command object

Tip: Because of restrictions in the Visual Basic extensibility APIs for data environments, parameter information is not added to the newly created ADO command object for DB2 procedures that have parameters. You can add parameter information manually in the properties for the created ADO command object. You can find parameter information from the procedure's properties in the DB2 development view.


Working with functions

The DB2 Development View lets you specify filters before enumerating the DB2 server-side user-defined functions. Regardless of any filter you may specify, dirty functions are always shown in the functions folder.

You can view the properties and s test functions in any language; for SQL scalar and table functions you can view and modify the source, and build and drop the functions. At the time of this article, SQL function development support is available for UNIX, Windows, and DB2 on Linux/390 DB2 servers.

You can create new SQL scalar and table functions using a wizard. There are also wizards for creating MQSeries reader/receiver functions, XML document parser functions, and OLE DB data source user-defined table functions.

For more information, see Developing SQL functions in the DB2 Development Center overview article.

Generating ADO commands for functions

The DB2 Development Add-In generates the client-side ADO code that is required to invoke a DB2 user-defined function using an ADO command object. See Generating ADO commands for procedures for more information.

Creating Data environment commands for functions

Apart from ADO code generation, the DB2 Development Add-In for Visual Basic lets you create data environment Command objects for your project's stored procedures. See Creating data environment commands for procedures .


Importing, exporting, and deploying

The DB2 Development Add-Ins provide you with advanced project management options, including the ability to add existing objects to your project using the import wizard, and the ability to deploy exported DB2 Development Center project objects to other DB2 database servers using the deployment tool.

For more information, see Importing, exporting, and deploying in the DB2 Development Center overview article.


Building queries

The DB2 Development Add-In can graphically generate and modify SELECT, INSERT, DELETE, and UPDATE statements using the SQL Assist tool. These queries can be inserted into the body of stored procedures and UDFs from their appropriate wizard or DB2 source code editor.

You can also launch SQL Assist from your Visual Studio toolbar to graphically generate or modify SQL statements and add them to any Visual Studio code windows, such as any ADO commands that you may be coding manually.

For more information, see Enhanced SQL Assist in the DB2 Development Center overview article.


Integrating with Visual Source Safe

One of the great features of Visual Studio is the ability to check your code into the Visual Source Safe source control management system. This provides you with version control and team development. The DB2 Development Add-In for Visual Studio integrates with Visual Source Safe and lets you check in your project files into a Visual Source Safe project database.

Tip: To use the Visual Source Safe (VSS) features of the DB2 Development Add-In, you must first install Microsoft Visual Source Safe. The DB2 Development Add-In VSS menu entries are disabled if VSS is not installed.

VSS and your DB2 project

To manage your DB2 code using Visual Source Safe, you must first add your DB2 project to Visual Source Safe. Simply select the Add Visual Source Safe Project pop-up menu entry for your DB2 project to launch the Add Visual Source Safe Project dialog.

Figure 9. Adding a visual source safe project
Adding a visual source safe project

You must first specify the user name, password, and the Visual Source Safe database to log on to, then either choose the project name, or specify a new project name. Once completed, you will be able to check in and check out your DB2 project files.

VSS and your DB2 procedures and functions

To check in and check out your Java and SQL procedures and SQL functions into Visual Source Safe, you must first add their source code into your Visual Source Safe project. You can do this by selecting Visual Source Safe => Add File for the desired procedure or function.

You can then check in and check out your procedure and function files using the corresponding Visual Source Safe menu entry.

Tip: If the procedure or function that is being checked in or out was not developed or imported in this Visual Studio session, you will have to get the source from the database by clicking View Source or Edit Source before actually checking the file in or out.


Conclusion

This article is an introduction to the many new and exciting features of the DB2 Development Add-Ins for Visual Studio, a tightly integrated set of tools for building stored procedures and user-defined functions that supports the entire family of DB2 servers. Some of the Development Add-In features include:

  • Wizards that make it easy to create Java and SQL procedures, SQL UDFs, MQSeries table UDFs, OLE DB table UDFs, and XML table UDFs
  • Support for read-only access to stored procedures, UDFs, triggers, tables, and views.
  • Online and offline database connection support with server catalog data caching for offline mode.
  • Integrated SQL debugger that includes large variables support and the ability to change values at debug-time.
  • Client-side VB and VBScript ADO code generation for DB2 connections, procedures, and functions.
  • New and enhanced import and deployment features, and wizards for rapid project management.
  • Integration with Visual Source Safe for source control management and versioning.

The Development Add-Ins provide Microsoft Visual Studio developers with a rich set of AD tools specifically designed for DB2, making IBM DB2 the cross-platform database server of choice for Windows developers.


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.

This article contains sample code from a beta-level product. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Top of page

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=13568
ArticleTitle=DB2 Development Add-Ins for Visual Studio 6.0 - The Next Generation AD Tooling
publish-date=08082002