DB2 Development Center -- The Next Generation AD Tooling for DB2

Document options Document options requiring JavaScript are not displayed My developerWorks needs you! Connect to your technical community Rate this page Help us improve this content

Level: Introductory

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

23 Jul 2002

DB2 Version 8 promises to be an exciting release for developers. One feature designed specifically for developers is the new DB2 Development Center, which is a rapid iterative development environment for building stored procedures, user-defined functions, structured data types, and more.

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

One of the most exciting new features of DB2® Universal Database^TM Version 8 is a new development center specifically designed for developers and for database administrators who support development projects. What is the Development Center? How does it differ from its predecessor, the Stored Procedure Builder? Is this a database developer or a database administrator tool? This overview article attempts to address all these questions and a few more. The Development Center is a great new tool with many great new features. Describing all of its capabilities is beyond the scope of this one short article, so expect to see more information on DB2 Developer Domain over time.

DB2 Development Center is no longer available. This article remains for those who already have the DB2 Development Center with DB2 V8 or earlier. If you have DB2 9 or later, please download IBM Data Studio.

This article describes some of the technical highlights of the new Development Center and describes some of the major tasks you can do with the center, such as developing stored procedures, user-defined functions, and specialized table functions. If you are particularly interested in using Microsoft® tools for application development, see also the companion article on Development Center Add-ins for Visual Studio.

Back to top

Technical highlights

In a nutshell, the Development Center is an evolution of the Stored Procedure Builder. It is a rapid iterative development environment for building stored procedures, user-defined functions, structured data types and much more. This tool was rewritten from the ground up to allow for concurrent task execution, flexible docking, enhanced scalability, and higher productivity.

Some of the Development Center features include:

Functionality

• Support for the entire family of DB2 servers including DB2 Universal Database for z/OS^TM, DB2 for iSeries^TM, DB2 for UNIX®, Linux for 390, and Windows®
• Enhanced z/OS^TM support including specialized SQL IDs (package owner, build owner, and secondary SQL ID) and advanced build options
• Support for developing SQL and Java^TM 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.

Usability

• Quick start launchpad for guiding novice users through the initial set of development tasks
• Customizable workbench using flexible docking
• Separation of project-specific server objects that are presented in the project view, from the live database objects shown in the server view

Project Management

• Multiple concurrent project development
• Import of stored procedures and functions from databases, other projects, and other source files
• Export of project files to a deployment project or script
• Deployment wizard, tool, and command line with binary deployment support for like DB2 servers

Testing and Debugging

• Test any language stored procedure and user defined function
• 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. This support is currently available only for DB2 on Linux, UNIX, and Windows and is planned to be available on DB2 for z/OS Version 8.
• Simplified debugging of Java stored procedures using the IBM VisualAge® distributed debugger. This support is available only for the Linux, UNIX, and Windows platforms.

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 database catalog data to avoid re-queries
• Concurrent task execution using multi-threading

Back to top

Developing stored procedures

You can use the Development Center to build Java and SQL stored procedures. At the time of this article's publication, Java support is available for all platforms except for iSeries.

Use either the editor or the Stored Procedure Wizard to create a new stored procedure.

See the Run settings section for information on testing both SQL and Java stored procedures.

Creating and debugging SQL stored procedures

With the wizard, as shown in Figure 2, you can create and generate code for new SQL stored procedures. One of the new features of the wizard is the ability to insert code fragments into the generated code.

Including SQL code fragments

Simply put, code fragments are user-defined sections of source code or comments that you can insert at pre-defined location in the generated source code. They are text files that you create and which you and your team members can reuse. This is especially useful when a standard set of error handling functions, headers, variable declarations, and so on are to be included in all stored procedures for a given project.

Debugging SQL

The Development Center's implementation of the SQL debugger includes numerous enhancements including greater stability, support for variable value change while debugging, and viewing sections of large variables, such as large objects (LOBs).

The SQL debugger is tightly integrated into the Development Center editor as shown in Figure 3.

Creating and debugging Java stored procedures

Both JDBC and SQLJ stored procedures may be created using the DB2 Development Center. The wizard to create new Java stored procedures is similar to the SQL procedure wizard. You can insert multiple code fragments and now you can specify additional helper jars that can be installed on the DB2 server with your stored procedure jar. These helper jars typically include some utility classes that are required by your Java stored procedures.

Debugging Java stored procedures

Using the Stored Procedure Builder, debugging Java stored procedures required several manual and non-intuitive steps. With the Development Center, all you have to do is to select the Java stored procedure and click Debug. This will automatically insert the required DB2 debug record and launch the Visual Age distributed debugger on your client machine.

Back to top

Developing SQL functions

The Development Center is the first DB2 GUI tool that supports the development of user-defined functions (UDFs). Although DB2 supports several languages for UDFs, the Development Center currently supports SQL scalar and table functions, and currently this functionality is supported on UNIX, Linux, Linux for 390, and Windows.

Create a new UDF using the editor or by using the Create user-defined function Wizard as shown in Figure 4.

Refer to the Run Settings section for information on testing of SQL UDFs.

Including SQL code fragments

As with SQL stored procedures, the wizard lets you include code fragments into the generated UDF code.

Debugging SQL

Debugging SQL UDFs is not supported at this time; however, you can issue a test run of your functions, similar to the test run of stored procedures.

Back to top

Developing specialized table functions

Apart from SQL scalar and table functions, the Development Center supports the development of the following specialized table functions:

• MQSeries® table function
• OLE DB data source table function
• XML document parser and data extractor table function with optional XSL transformation

MQSeries Table Function wizard

With the Development Center, you can create MQSeries messages table UDFs, as shown in Figure 5, that make use of the MQSeries (WebSphere® MQ) integration UDFs that were introduced in DB2 V7.2. An earlier version of this wizard was introduced in the Stored Procedure Builder.

Using the wizard, you can create a table UDF that receives (destructively reads) or simply reads (peeks at) MQ messages, plus the creation of an optional table view for easier access. Messages are decomposed into SQL data columns using either delimited column markers or fixed-length columns with a specific starting position and length. This integration with MQ provides the capability of doing such things as populating an operational data store in real-time. For more information about the MQSeries integration UDFs, see Using MQSeries from DB2 Applications.

OLE DB Table Function wizard

Using the built-in DB2 extensions for OLE, you can use the Development Center to create OLE DB table UDFs (see Figure 6) that allow for accessing of OLE DB data sources from within DB2. An earlier version of this wizard was introduced in the Stored Procedure Builder.

These read-only table UDFs can either access a set of columns from a specific OLE DB table, or it can access a query result that spans multiple tables at the OLE DB data source. Apart from the option to create an associated table view, you can also elect to create a DB2 table in which the OLE DB data source data is automatically retrieved and inserted into the newly created table.

A helper wizard is also provided that facilitates building the OLE DB connection string for the supported OLE DB data sources.

XML Parser Table Function wizard

Using new built-in DB2 custom UDFs for parsing XML documents, the Development Center enables you to create XML table UDFs that extract SQL tabular data from XML documents. This is a new table UDF that is being introduced with the Development Center.

Using the wizard, you can create a table UDF that parses out XML nodes and converts them into SQL data columns. The wizard supports an optional invocation of an XSL transformation prior to data extraction using a built-in DB2 XSL UDF. This optional feature allows for transforming those XML documents having tabular data that may not conform to the style supported by this wizard.

Back to top

Developing structured types

You can use the Development Center to map Enterprise Java Beans (EJB) entity beans with container-managed persistence (CMP) to DB2 structured types. This means you can reuse logic from your EJBs in your SQL statements. A side effect of this is that the execution of the logic is pushed down from the application server to the database server meaning that there's less data transfer between the two.

For each CMP field of the EJB there is a structured type attribute that is generated to hold the data of that field. You create an SQL structured type for each EJB method, so that if a SQL method is invoked the EJB code gets executed. The tools suggest a default mapping that you can modify.

The Development Center generates the code that enables DB2 to call the EJB methods and deploys it to the database server with the required structured type definitions. When the structured types are created in the database, you can create either typed tables or create object views on existing relational tables. For more information about structured types and object views, see DB2's Object-Relational Highlights.

Back to top

Importing, exporting, and deploying

Apart from working with server-side objects, you can use the Development Center to add existing objects into your project, you can export the project objects for later deployment, and you can deploy these objects to other, possibly remote, DB2 database servers.

Importing stored procedures and functions

You can include existing objects from other sources into your project using the Import Wizard (Figure 8), which enables you to import the object as a procedure or a UDF. You can import these objects from another database connection, from an existing project on your file system, or from a file that contains the source of the server object.

Importing SQL procedures and functions
You can import SQL procedures and functions from a database, a project, or a file. When importing from a file, it is assumed that only one such object exists in the file. If multiple objects are found, only the first object is imported.

Importing Java procedures
You can import Java procedures from a database, a project, or a file. When importing from a file, the Development Center parser presents you with the list of candidate methods having the proper stored procedure signatures, and you make the selection as to which method represents the desired procedure.

Exporting stored procedures and functions

After you are satisfied with the objects in your project, you can select to export these objects for later re-deployment to other database servers. You can optionally request to have binary files included in your export list. This option allows for non-compile redeployment to other platform compatible database servers.

The Development Center supports two types of export mechanisms: exporting to a deployment project, or exporting to a deployment script.

Exporting to a deployment project
You can export your project objects to a deployment project. This type of export allows for later re-deployment using the Development Center's deployment tool or command. This is the easiest way to export and deploy, because it requires no user modification to the export files. The exported project and files are automatically compressed into a zip file. You can then move them to another, possibly remote, DB2 machine that has the required deployment tool or command installed.

Exporting to a deployment script
You can export your project objects to a deployment script. This is similar to the way export works in the Stored Procedure Builder, in that a deployment .bat file and shell script are created for you, allowing for later redeployment to other database servers. In most cases, you will have to manually modify the generated deployment script to specify the proper database, user name, and password to use during deployment. The exported script and required source files are automatically compressed into a zip file. You can then move them to another, possibly remote, DB2 machine, and then execute the deployment script manually.

Deploying stored procedures and functions

When you are satisfied with the objects in your project, you can select to deploy these objects to other database servers, as shown in Figure 10. There are multiple deployment mechanisms, depending on your particular requirement:

• Deploying the project objects directly from the Development Center using the deployment wizard
• Exporting the project and then deploying it later using the deployment tool
• Exporting the project and then deploying it later using the deployment command
• Exporting the project as a script and then later executing the deployment script

Deploying projects using the Deployment Wizard
From within the Development Center, you can elect to deploy some of your project objects directly to another DB2 server using the Deployment Wizard, which is accessible through the pop-up menu on the connection, procedures, and functions folders. This type of deployment allows for rapid movement of your project objects from your project DB2 server to another server that is accessible through your development machine. If the target server is not directly accessible, then you should export your project for later redeployment.

Using the Deployment Wizard, you can customize the deployment options, including build options for each of your project objects, based on the target DB2 server.

Deploying exported projects using the deployment tool
After you export a deployment project, you can deploy the exported project objects to other DB2 servers using the deployment graphical tool, which is similar to the Deployment Wizard, with the exception of a new starting step required to specify the source exported project zip file name and path. Use the command executable, db2dcdpl, to launch the Development Center deployment tool.

Also, as with the Deployment Wizard, you can customize the deployment options, including build options for each of your project objects, based on the target DB2 server. You can also elect to deploy a subset of the exported project objects.

Deploying exported projects using the deployment command
After you export a deployment project, you can deploy the exported project objects to other DB2 servers by entering the deployment command, db2dcdpl. This is the same as the deployment tool executable, with the exception that you will need to specify the cmd option, to avoid executing the GUI. You will also need to specify the source exported project zip file name and path, the target database name, and the connection user name and password, as follows:

 
db2dcdpl cmd [options] source-file target-database [user-id password] 

The list of options include:

The db2dcdpl command will bring up the help screen if the command options are not valid.

The deployment command is very useful in those scenarios where it can be used as part of a server setup script, or it may be scheduled to run at a specific time using the DB2 scheduler or any other scheduling tool.

Unlike the deployment wizard and tool, you cannot customize the deployment options, such as the build options for each of your project objects. The original project build options will be used. You can however modify these project options using the Development Center before you export the project.

Deploying an exported script to other DB2 servers
After you export a deployment script, you can deploy the exported project objects to other DB2 servers using the exported script command. You will first have to unzip the script command, plus any associated helper files, such as .sar and .jar files. You must also modify the deployment script to specify the target database name and the user name and password used to connect to the database.

The deployment script is very useful when you need to specify additional deployment options or to modify the order of deployment. You can also schedule to run the script at a specific time using the DB2 scheduler or any other scheduling tool.

Back to top

Specifying run settings for stored procedures and UDFs

With the Development Center, you can test all of your project procedures and functions and any existing server procedures and functions. You can specify pre- and post-execution scripts, parameter values, and other test options, such as rollback or commit after test execution (Figure 11).

Run settings

You can at anytime specify the test settings for these procedures and functions using Run Settings. The settings you specify are persisted as part of your project settings, for later reuse. With this feature, you do not have to retype your test parameters and options every time you want to test the procedure or function.

Pre- and post-execution scripts
You can specify data definition language (DDL) or data manipulation language (DML) SQL statements in the pre- and post- execution scripts. You can use the pre-execution script to set up the test environment, perhaps by creating some temporary tables or inserting some data records required for the test. You can use the post-execution script to clean up after the procedure or function executes.

Parameter values
You can specify the values of any input or inout procedure and function parameters. These values can be reused on subsequent test runs. The parameter values may be specified using the Run Settings dialogue or on the Specify parameter values dialogue. The Test run dialogue is shown when you choose Run instead of Run Settings.

Back to top

Server view -- Control Center for developers

A key new feature of the Development Center is the separation of the project objects shown in the Project View from the live server objects that are shown in the Server View, as shown in Figure 12.

You can think of the Server View as being a miniature Control Center that is specifically designed for developers because it includes objects that developers work with, such as stored procedures, triggers, tables, and views, among others. This view can be docked anywhere on the Development Center.

Server View features

Some of the Server View features include:

• The ability to specify individual filters for each of the object folders. This is especially useful when you have large databases, in which you may want to filter out how many objects are returned.
• The data presented in the Server View is cached on the local machine and is reused on subsequent invocation. This enhances performance because you get to control when this data should be refreshed.

Server View objects

The Server View folders include stored procedures, UDFs, triggers, tables, and views. Some of the actions that can be performed on the folder objects include:

• Stored procedures - Add to a project, show properties, view source, test run, and drop.
• User defined functions - Add to a project, show properties, view source, test run, and drop.
• Triggers - Show properties (including the source).
• Tables - Show properties and sample content.
• Views - Show properties and sample content.

Back to top

Building queries

A new version of SQL Assist (Figure 13) is included with the Development Center.

The enhanced version of SQL Assist supports many new features, including a redesigned condition builder, join assistance, improved scalability, accessibility, and "round-trip" query editing. Improved integration with Development Center using round-trip query editing means that you can highlight a SQL statement from anywhere in the Development Center (including wizards and scripts) and then launch SQL Assist to do further editing of the SQL statement.

Back to top

Working with environment settings

There are numerous configuration options available to you through the Development Center. You can modify these options using the Project->Environment Settings, as shown in Figure 14.

Manually editing the settings file

There are many more settings available than what is shown in the Environment Settings dialogue shown in Figure 14. You can modify these additional setting by editing the settings file yourself. The settings file is located on your client machine under your user path. The location of the user path is platform-specific. For Windows, it is the %USERPROFILE% environment variable, and for UNIX it is the $HOME environment variable.

There are two settings files, DB2DC.settings and YourName.settings, under the IBM\DB2DC folder located under the user path. Do not modify DB2DC.settings; rather, you should copy the sections of interest from DB2DC.settings file into YourName.settings, and then modify the settings values. Important: Be sure to exit the Development Center before you start editing the settings file to avoid losing your changes.

For example, to modify your work directory, copy the section, PROCESS, from DB2DC.settings into YourName.settings, and then modify the value of the WORK_DIRECTORY XML node as follows:

 
	<PROCESS description="Process"> 
		<WORK_DIRECTORY description="Temporary build directory" 
		value="C:\Documents and Settings\Abdul\IBM\DB2DC\Projects\" /> 
	</PROCESS> 

As you can see, each entry in the settings file has a name, description, and value. Modify only the values.

Restoring the settings files

If you accidentally corrupt either or both settings files, simply delete the corrupted file and it will be generated for you automatically the next time you launch the Development Center.

Back to top

Conclusion

This article is an introduction to the many new and exciting features of the DB2 Development Center, a rapid iterative development tool for building stored procedures, user defined functions, and structured data types, supporting the entire family of DB2 servers. Some of the Development Center new features include:

• Extended objects support to include creation of SQL UDFs, MQSeries table UDFs, OLE DB table UDFs, XML table UDFs, and EJB structured types via easy-to-use wizards.
• New and enhanced import, export, and deployment features and wizards for rapid project management.
• New server view for read-only access to stored procedures, UDFs, triggers, tables, and views, separating your project objects shown in the Project View from your live server objects show in the Server View.
• Online and offline database connection support with server catalog data caching for offline mode.
• Enhanced integrated SQL debugger to include large variable support and debug-time value change capability.

The Development Center is designed specifically for database developers and database administrators responsible for deploying and administrating database applications built using this tool. The Development Center represents an evolution in DB2 AD tooling, providing you with ease of use, scalability, and a rich set of functionality.

Back to top

Notice

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.

Top of page

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 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.

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