DB2 UDB for Visual Studio 2005 developers, Part 1: Overview of IBM Database Add-ins for Visual Studio 2005

IBM® has announced and introduced DB2® tools for Visual Studio 2005 to allow rapid application development, database schema development, and debugging, along with numerous other new features. The functionality allows you to build applications for the IBM® DB2 Universal Database™ (DB2 UDB) family of databases without writing a single line of code, to create and alter complex database objects using designers, and to debug SQL procedures for a range of DB2 platforms. IBM tooling is available on all of the Visual Studio 2005 editions or Visual Studio 2005 team editions. This article provides an overview of the new functionality provided by IBM for Visual Studio 2005.

Share:

Sonali Surange (ssurange@us.ibm.com), Developer, IBM

Sonali Surange photoSonali Surange is an Advisory Software Engineer working at the IBM San Francisco lab, providing tools for seamless integration with IBM Servers on Visual Studio .NET.



08 December 2005

Key new feature additions in IBM tooling for Visual Studio 2005

New feature additions in IBM tooling for Visual Studio 2005 will enable developers to reduce development time and develop .NET applications for the entire DB2 server family using Microsoft® Visual Studio® as their integrated development environment. These additions include:

  • Seamless integration with Microsoft Server Explorer where DB2 connections can now be added into Microsoft Server Explorer. The IBM DB2 .NET managed provider for .NET framework 2.0 is used for this seamless integration. IBM Explorer has been deprecated.
  • Introduction of IBM designers to script and create DB2 database objects. Also newly added in this release is the capability to alter and clone database objects and to define and alter roles and privileges for each object, using IBM designers.
  • IBM Database add-ins for Visual Studio 2005 are now available as a separate install.

Highlights

Here are some of the highlights of the new tooling:

  • You can build Windows applications and Web sites for DB2 without writing any code:
    • All flavors of DB2 are supported (DB2 UDB for Linux®, UNIX®, and Windows®, DB2 UDB for iSeries™, and DB2 UDB for z/OS®).
    • Federated database and nicknames are supported for application development.
    • Filtering of database objects is supported for optimal performance on iSeries and zSeries® servers.
    • Caching of schema information for objects in DB2 connections in Server Explorer provides better application development performance at design time.
  • A new feature in IBM tooling is the ability to hide or show specific folders for a DB2 connection in Server Explorer.
  • The tooling continues to support displaying detailed messages of DB2 activity on the IBM Message Pane.
  • The new tooling introduces a new set of IBM designers to create, alter, and clone database objects:
    • All IBM designers continue to use the smart multi-line editors that provide syntax colorization and statement completion.
    • You have the ability to create new tables, views, and procedures using IBM designers.
    • There is new functionality to alter existing tables, views, and procedures, using IBM designers.
    • There is new functionality to create and alter roles and assign privileges to database objects.
    • The IBM designers give you the ability to clone tables and procedures.
  • The new tools give you a new way to seamlessly debug SQL procedures on Linux®, UNIX®, and Windows, or zSeries servers, from Server Explorer. Debugging support now uses the new IBM Designer for Procedures that allows a seamless debugging experience.
  • There is a new designer to view or create script for all objects. The IBM Script Designer provides:
    • The ability to change and execute scripts
    • The ability to run single or multiple DDL/DML statements and view results in single or multiple grids
    • The ability to alter objects using scripts
  • You can show data from tables and views with the following new enhancements:
    • You can filter columns while retrieving data.
    • You can save data as XML to import or export, allowing easy table or view data migration.
  • Execute Procedures and Functions has the following new enhancements:
    • You have the ability to run pre- and post-scripts.
    • You can save input or in-out parameter values across Visual Studio sessions.
    • You can commit or rollback transactions.
  • There is a new user interface to view result sets in DB2 connections on the Server Explorer. It gives you:
    • The ability to view single or multiple result sets for a procedure in Server Explorer
    • The ability to discover automatically (when possible) or to manually define or customize result set definitions for a procedure
    • The ability to set the preference to always discover or always manually define the result set definition in Add or Modify connection
  • You have continued support for DB2 projects and IBM Scripting wizards to create DB2 scripts.
    • The debugging support has been discontinued from DB2 Projects. Instead, it is supported seamlessly from DB2 connections on Server Explorer.

Download

IBM Visual Studio Add-ins are now available as a separate install.


Server Explorer integration

DB2 connections can be added using the Add Connection option from Server Explorer. Follow these simple steps to set up a DB2 connection:

  1. If Server Explorer is not already open, Click View > Server Explorer.
  2. From Data Connections node, right-click and select Add Connection.
  3. Select Data source, and select IBM DB2. (You can optionally check the box for Always use this selection, to avoid this step every time a DB2 connection is added.) Click OK.
  4. Add the server name, database name, user ID, and password. (Optionally select to Save password to persist password across Visual Studio 2005 sessions. Optionally click Test Connection.) Click OK. A connection to your database will be added in Server Explorer.
Figure 1. Add connection
Add connection

Several features are available in the Server Explorer Add connection integration that are unique to IBM tools and not available for other databases in Server Explorer:

  • After adding a new connection, all schema information is asynchronously pre-fetched and cached. This allows rapid access to this information when designing applications or using IBM designers and its smart editors for intellisense. Messages indicating this pre-fetch can be seen in the IBM Message Pane of the Output Window.
  • Several new customizations are available at the time of adding a connection:
    • Customers have previously requested the ability to automatically refresh a connection when the Visual Studio instance is re-opened. You can make that selection on the Add Connection or Modify Connection dialog.
    • Customers do not wish to always run procedures to discover result sets. You can choose your preference to always manually specify the result set or automatically discover by running the procedure on the Add or Modify Connection dialog.
    • IBM databases on iSeries and zSeries servers can contain large amounts of data. Application developers may not always be interested in all the default database objects, such as tables, views, procedures, and functions, but simply a few of them. Add Connection and Modify Connection dialogs allow you to select the folders you are interested in. Only the selected folders participate in the pre-fetch operation and schema caching. This selection provides an improved development experience if the developer is using that connection.
    • Filtering for schema and folders has been simplified to use a LIKE search. Filters for schema apply to all folders. In addition, folder level filters are now supported in the Add Connection or Modify Connection dialogs. Filter values are case sensitive. For example, adding a schema filter value of "IBM" will get all objects starting with schema "IBM."

There are a few changes to node in comparison with the earlier Visual Studio 2003 support:

  • You will always require the server name, port, user ID, password, and database name to create connections. Typically a default DB2 installation on Windows has a port number of 50000.
  • Only one connection to a server and database can be added for the same user. If you want to use a connection to the same database and same user, but with different filtering or advanced options, simply use Modify Connection to change the filtering or any of the custom options in the Add / Modify Connection dialog.

Rapid application development

Applications can be seamlessly developed for DB2 using the new paradigm introduced in Visual Studio 2005 for building Windows applications and Web sites. Simply follow the following process to build a Windows application:

  1. Add a connection to a DB2 database.
  2. Create a Windows application in C# or VB.NET.
  3. From the top level "Data" menu, select Add New Data source.
  4. The wizard allows selecting a table, view, or procedure to create a Data source.
  5. The selected Data source can be drag dropped on the Windows form.
  6. Run the application.
Figure 2. Application development
Application Development

A later tutorial in this series will describe in detail the process of building Windows applications and Web sites using IBM tools.

Key highlights:

  • For a connection, the filtered list of objects is available from the cache. This provides an optimal design time experience on remote servers, like iSeries and zSeries.
  • By specifying additional schema and object level filters, the list of objects listed to create a data source can be made specific to the developer's requirements.
  • When creating a data source for a procedure, the pre-defined result set is automatically picked up. If the result set was not already defined, it is discovered or can be manually set, based on user’s preference.

IBM designers

Unique to IBM tools is the support for IBM designers that allows creating database objects. Tables, views, and procedures can be created using the IBM designer dedicated for each task. Each designer allows creating indexes, constraints, keys, and triggers (where applicable).

In this release, altering existing database objects and the ability to define roles and privileges for each role for each object has been newly added. Privileges can be set on each designer. In addition, cloning of objects to easily create similar objects is also provided using IBM designers.

Key highlights of IBM designers are:

  • Each designer contains views that allow completing a task associated with that designer. Common views are available on designers to provide a similar look and feel and ease of use. You can switch between views using the IBM toolbars or top level menus for the designer you are using.
  • Each designer contains properties sections where extended properties can be set or viewed. Commonly used properties are also displayed in the main section for each designer. Typically, the default values are pre-filled for ease of use and can be changed.
  • Each designer uses the smart multi-line editor that allows statement completion and intellisense using cached connection information from DB2 connections from Server Explorer.
  • Common views to display, create, or alter scripts for a designer.
  • For each designer, the standard File > Save icon or menu is available to save changes in IBM designers. When creating a new database object or altering an existing object, this action runs the script on the database and creates or alters the object. If there are any errors, a message box indicating failure is displayed, and detailed errors are listed in the IBM Message pane of the Output Window.

IBM Table Designer

Creating a new table

You can create a new table using the IBM Table Designer either using the context menu option for tables node to "Add New Table," or using the top level menu entry under "Database" for "Add New Table." Switching between views is achieved by clicking on the toolbars or by using the entries under the top level menu for "IBM Table Designer". The following menus are provided:

  • Columns view – Allows you to define columns and their data types.
    • Column data types are pre-filled based on the DB2 platform for the procedure for easy selection.
    • You have the ability to set extended properties in the Keys Properties. Such properties include setting primary keys, identities, and default values on each column.
Figure 3. Columns view
Columns view
  • Keys view – Allows defining primary keys, unique keys, and foreign keys.
    • For primary and unique key creation, columns must satisfy certain criteria to be eligible, such as being marked as "not null". The tool makes creating these keys easy by automatically providing a lookup on the valid candidate keys defined in the columns view.

      For example, if you are creating a Customer table and the CustomerID is marked as not null, then when you define the primary key, CustomerID will show as a candidate column.

    • For foreign key creation, another table with a primary key with a compatible data type is required. Table and column lookup is provided to make this process easy.
    • You have the ability to set extended properties using the Keys Properties.
Figure 4. Keys view
Keys view
  • Indexes view – Allows creating indexes for the columns defined in the columns view.
    • Columns defined in the columns view are pre-selected to allow further index definition.
    • You have the ability to set extended properties using the Index Properties user interface.
  • Check constraints view – Allows adding check constraints for a table and its columns.
    • You have the ability to set extended properties using the Check Constraints properties user interface.
  • Triggers view – Allows adding triggers to a table.
    • You can define the trigger text in the multi-line smart editor.
    • You have the ability to set extended properties such as event trigger information in Trigger Properties.
Figure 5. Triggers view
Triggers view
  • Privileges view – Allows adding roles and privileges to the users.
    • You can look up and select from existing users in the database.
    • You can grant or revoke privileges from each user or group.
    • The list of privileges that can be granted or revoked is automatically pre-filled for the DB2 server you are working on, so you can select one of the valid values.
Figure 6. Privilege view
Privilege view
  • Show script view – View the create script for the table.
Figure 7. Show script view
Show script view

Altering an existing table

The context menu option for "Open Definition" from an existing table in a DB2 connection in Server Explorer or the top level menu entry under "Database" for "Open Definition" allows you to alter a table using the Table Designer. Alternately, you can continue using the IBM Designer opened for creating a table after clicking Save. The designer now automatically goes in alter mode and allows you to alter the object just created.

IBM Table Designer for altering a table provides the same views as those when creating a table. These views are described in the section above. Some key differences are as follows:

  • The features of a table that can be altered differ for each DB2 platform. Based on the platform for the connection this table belongs to, the features in the various views are automatically enabled or disabled.
  • In cases where the platform allows altering the column data type, the list of data types available is pre-filled with the valid compatible data types for that platform, making the type selection easy.
  • Alter script can be viewed in the Show script view.

Cloning an existing table

The context menu option for "Clone Definition" from an existing table in a DB2 connection in Server Explorer allows you to clone a table using the Table Designer. Alternately, you can use the top level menu entry under "Database" for "Clone Definition."

IBM Table Designer for cloning a table is the same designer as a create table designer. Some key differences are:

  • All of the views are pre-filled with the definitions of the original table.
  • The table name is made unique by appending a numeric value.

Show data for a table - IBM DataView Designer

"Show Data" is available as a context menu option from an existing table in a DB2 connection in Server Explorer or the top level menu entry under "Database" for "Show Data." Key features of the DataView Designer are:

  • It gives you the ability to insert, update, delete rows of a table. Note that insert, update and delete are only allowed on tables that have a primary key.
  • You have the ability to select columns to display, sort order, and specify an SQL statement for filter criteria, using the Filter Options section.
  • A key feature is the ability to import or export data into the table as XML. This permits easy table migration from one database to another.
Figure 8. IBM DataView Designer
IBM DataView Designer

Show script for a table - IBM Script Designer

"Show Script" is available as a context menu option from an existing table in a DB2 connection in Server Explorer or the top level menu entry under "Database" for "Show Script."

Script used to generate the table is displayed in the script designer. The script includes commented drop statements that users can uncomment to drop the table. This is provided to avoid unintentional dropping of the table. Changes can be made to the script, if desired. Changes can be executed using the execute toolbar or the Execute menu entry on the top level "IBM DataView Designer" menu.

Key features of the IBM Script designer are:

  • Ability to execute scripts. Scripts execution is always committed.
  • Ability to view results in grids.
  • Ability to alter a table by modifying the script.
Figure 9. IBM Script Designer
IBM DataView Designer

IBM View Designer

Creating a new view

The context menu option for Tables node to "Add New View" or the top level menu entry under Database for "Add New View" allows you to create a new table using the IBM View Designer. Switching between views is achieved by clicking on the toolbars or by using the entries under the top level menu for "IBM View Designer." The following views are provided:

  • View view – Allows defining the view.
    This provides the ability to simply check the syntax or test execute the SQL statement provided in the view definition. In the case of test execution, the result can be viewed in a grid. Note that the test execute action is always rolled back.
Figure 10. IBM View Designer
IBM View Designer
  • Triggers view – Allows adding triggers to a view. This gives you:
    • The ability to define the trigger text in the multi-line smart editor.
    • The ability to set extended properties and event trigger information in Trigger properties.
  • Privileges view – Allows adding roles and privileges to users. This gives you:
    • The ability to look up and select from existing users in the database.
    • The ability to grant or revoke privileges from each user or group. The list of privileges that can be granted is automatically pre-filled for the DB2 server you are working on, so you can select one of the valid values.
  • Show script view – View the create script for the view.

Altering an existing view

The context menu option for "Open Definition" from an existing view in a DB2 connection in Server Explorer or the top level menu entry under "Database" for "Open Definition" allows you to alter a view using the View Designer. Alternately, you can continue to use the IBM Designer opened for creating a view after clicking Save. The designer now automatically goes in alter mode and allows you to alter the object just created.

IBM Table Designer for altering a view provides the same views as those when creating a view. These views are described in the section above. Some key differences are as follows:

  • Features of a view that can be altered differ for each DB2 platform. Based on the platform for the connection this table belongs to, the features in the various views are automatically enabled or disabled.
  • Alter script can be viewed in the Show script view.

Functionality for Show data and Show script is similar to that of the table.

IBM Procedure Designer

Creating a new procedure

The context menu option for procedures node to "Add New Procedure" or the top level menu entry under Database for "Add New Procedure" allows you to create a new table using the IBM Procedure Designer. Switching between views is achieved by clicking on the toolbars or by using the entries under the top level menu for "IBM Procedure Designer." The following views are provided:

  • Procedure view – Allows defining procedure parameters and procedure body.
    • Parameter data types are pre-filled based on the DB2 platform for the procedure for easy selection.
    • Ability to set extended properties for a parameter in the Procedure properties. Such properties include parameter direction, length, scale, and precision.
    • A default procedure body returning one result set is provided. Note that if the body is changed to return more than one result set, the number of result sets should be specified manually. This can be done using the "Number of Result Sets" field provided in the Procedure Identification section.
Figure 11. IBM Procedure Designer
IBM Procedure Designer
  • Privileges view – Allows adding roles and privileges to the users or groups.
    • Ability to look up and select from existing users in the database.
    • Ability to grant or revoke privileges from each user or group. The list of privileges that can be granted is automatically pre-filled for the DB2 server you are working on, so you can select on of the valid values.

Altering an existing procedure

The context menu option for "Open Definition" from an existing table in a DB2 connection in Server Explorer or the top level menu entry under "Database" for "Open Definition" allows you to alter a procedure using the Procedure Designer. Alternately, you can continue to use the IBM Designer opened for creating a procedure after clicking Save. The designer now automatically goes in alter mode and allows you to alter the object just created.

IBM Table Designer for altering a table provides two additional views:

  • Features of a table that can be altered differ for each DB2 platform. Based on the platform for the connection this table belongs to, the features in the various views are automatically enabled or disabled.
  • Execute view – Allows executing the procedure.
  • Step into view – Allows debugging the procedure.

Debugging SQL procedures

A new enhancement to the debugging procedure is the ability to debug using an IBM Procedure designer. In this release, debugging support is also extended to zSeries servers. A procedure can be debugged using the following simple steps:

  1. Create the procedure for debugging by selecting the debug mode option to ALLOW. If the procedure was originally created without the debugging option, it can be altered to be created in debug mode. For a procedure, an alter action is equivalent to a drop and re-create.
  2. Add a breakpoint in the SQL body.
  3. To start the debugging, use the toolbar for Step Into or the menu entry for Step Into from the top level menu for IBM Procedure Designer. Alternately, you can use the context menu from the procedure under the Server Explorer connection.
Figure 12. Debug SQL procedure
Debug SQL procedure

Key highlights for the debugging support:

  • Ability to step into embedded SQL procedures for debugging
  • Ability to watch variable values
  • Ability to change variable values

Cloning an existing procedure

The context menu option for "Clone Definition" from an existing table in a DB2 connection in Server Explorer allows you to clone a procedure using the Procedure Designer. Alternately, you can use the top level menu entry under "Database" for "Clone Definition."

IBM Procedure Designer for cloning a table is the same designer as a Create Procedure Designer. Some key differences are:

  • All of the views are pre-filled with the values from the original procedure.
  • The procedure name is made unique by appending with a numeric value.

Execute procedure - IBM DataView Designer

"Execute" is available as a context menu option from an existing table in a DB2 connection in Server Explorer, or the top level menu entry under "Database" for "Execute." The IBM DataView Designer displays the results.

New features for procedure execution are:

  • Ability to select commit or rollback.
  • Ability to define pre and post scripts. Note that the pre and post scripts together with the procedure execution are executed in one transaction.

Show script for a procedure - IBM Script Designer

"Show Script" is available as a context menu option from an existing table in a DB2 connection in Server Explorer, or the top level menu entry under "Database" for "Show Script." Script used to generate the table is displayed in the script designer. The script includes commented drop statements, which users can uncomment to drop the procedure.

Result set definition

Unique to IBM tools is the ability to view single or multiple result sets for a procedure under the procedure node in Server Explorer. Result sets are used in application development for the procedures. Result sets can be defined manually or discovered automatically, when possible. Typically, result sets for procedures with input or in-out parameters cannot be automatically discovered. The result set can be defined by using the discover option in the result set definition user interface where values for input parameters can be provided. Alternately, the result set can be defined manually.

Figure 13. Result set definition
Result set definition

Functions

For an existing function, ability to execute, view script, and delete is provided. In this release, functions can be created using the function wizard from DB2 projects only.


Conclusion

IBM has provided a broad range of new support in the Visual Studio 2005 tooling. The new features include seamless integration with Microsoft Server Explorer, introduction of complete new set of IBM designers, seamless debugging using Server Explorer and IBM designers, and introduction of key functionality, like working with privileges.

For more information and availability, please visit DB2 UDB for .Net.

Acknowledgements

This release of Visual Studio 2005 Add-ins has been developed by IBM teams in San Francisco, Silicon Valley Lab and Toronto Lab.

Resources

Learn

Get products and technologies

Discuss

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, IBM i
ArticleID=100280
ArticleTitle=DB2 UDB for Visual Studio 2005 developers, Part 1: Overview of IBM Database Add-ins for Visual Studio 2005
publish-date=12082005