© 2002 International Business Machines Corporation. All rights reserved.
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.
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 .
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.
- 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.
- 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:
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
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++
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
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
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
'Create new ADO connection object
Dim adoConnection As New ADODB.Connection
.CursorLocation = adUseClient
.ConnectionString = strConnectionString
'Open ADO Connection Object
'Return new ADO connection object
Set SAMPLE_GetConnection = adoConnection
MsgBox "Error Code: "& Err.Number & vbNewLine& _
"Description: " &Err.Description & vbNewLine & _
"Source: " & Err.Source, _
Set SAMPLE_GetConnection =Nothing
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
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) _
'create ADOCommand Object.
Dim adoCommand As ADODB.Command
Dim adoRecordSet As ADODB.Recordset
On Error GoTo PROCEDURE1_ExecuteCommand_ErrHandler
Set adoCommand = New ADODB.Command
.CommandType = adCmdStoredProc
.ActiveConnection = adoConnection
.CommandText= "ABDUL.PROCEDURE1" End With
'Add Parameters to the Command object
Dim adoParm As Parameter
Set adoParm = adoCommand.CreateParameter("var0", _
Set adoParm = adoCommand.CreateParameter("var1", _
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
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, _
MsgBox "Error code: " & Err.Number& vbNewLine & _
"Description: "& Err.Description & vbNewLine& _
"Source: " & Err.Source, _
vbOKOnly + vbCritical
End If End
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
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.
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
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.
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.
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
North Castle Drive Armonk, NY 10504-1785
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.