IBM Optim Development Studio: Routine development simplified

The latest IBM Optim™ Development Studio includes many new features. Among these is a completely redesigned way to create and manage your routines and the adoption of routine templates. Through the use of templates, you can easily create your own best-practices routines and roll these out to your entire user base with a single click. Numerous improvements to the routine editor, such as improved syntax validation, make it the most powerful editor yet. These changes provide greater flexibility, customization, and ease-of-use during all phases of your routine development.


Michael L. Pauser (, Developer, IBM

Michael is a lead developer for IBM Optim Development Studio. He has extensive experience in user interface development for client-side applications and for web-based applications.

07 October 2010

Also available in Chinese Portuguese


Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See Introduction to IBM Optim Development Studio and pureQuery Runtime

This article provides a step-by-step guide to create and manage your database routines using IBM Optim Development Studio. In this article, you will learn how to do the following:

This article assumes that you have the following products installed:

  • IBM Optim Development Studio V2.1.1.0 and any of its prerequisites.
  • IBM DB2® V9.7 for Linux®, UNIX®, and Windows®. However, you can use any database IBM Optim Development Studio supports. In addition, you must have the DB2 SAMPLE database installed. If you do not have the SAMPLE database installed, refer to your DB2 Information Center for instructions on installing this database.

Create a database connection

Before you create a routine, you first need to create a connection to your database by completing the following steps:

  1. Select Database Connections from the Data Source Explorer view.
  2. If your database is already listed in the Data Source Explorer, select it, right-click, and click Connect to launch the properties editor. If you database is not already listed, right-click and select New to launch the New Connection wizard, as shown in Figure 1.
Figure 1. New Connection wizard
Screen cap: Connection parameters with DB2 for Linux, UNIX, and Windows highlighted and database properties entered
  1. Complete the fields by providing the required information. In most cases, you need to modify only the user name and password values. Hint: select Save password to avoid reentering the password upon future reconnections.
  2. Test your connection by clicking Test Connection, making any necessary corrections, and testing again until successful.
  3. Click Finish. Your newly created SAMPLE database connection appears in the Data Source Explorer, as shown in Figure 2.
Figure 2. New SAMPLE database connection
Explorer view with SAMPLE database highlighted in the Database Connections list

Create a data development project

After creating a connection to your database, you need to create a data development project. Data development projects are used for database application development. This type of project is associated with a single connection in the Data Source Explorer. You can use data development projects to do the following:

  • Develop and test SQL queries
  • Develop, deploy, and test SQL, Java®, and PL/SQL stored procedures, user-defined functions, and packages
  • Develop and deploy Web services that access data by using SQL scripts or stored procedures

Complete the following steps to create a data development project:

  1. From the Data Project Explorer, click Click to create a new data development project. Or, from the Eclipse menu, click File > New > Data Development Project. This launches the New Data Development Project wizard.
  2. Specify a name for your project, such as Demo, as shown in Figure 3.
Figure 3. Data development project
Screen cap: Data Development Project with Demo in input field for Project Name
  1. Click Next.
  2. Select the database connection that you created in Create a database connection, such as the SAMPLE connection, as shown in Figure 4.
Figure 4. Select connection
Screen cap: SAMPLE database selected with properties displayed in lower pane
  1. Click Finish. A new data development project named Demo is shown in the Data Project Explorer, as shown in Figure 5.
Figure 5. Demo data development project
Demo data development project displayed in Data Project Explorer

Create a stored procedure

The most significant change to routine tooling for Optim Development Studio V2.2.1.0 is the adoption of routine templates. While the template concept is nothing new to an experienced Eclipse user, it does represent a significant shift in the way you develop your routines.

A routine template is simply a predefined text skeleton in which you can implement your company's best practices. Using a standard prolog, you can provide any amount of code in each template as a starting point for your developers. For example, if your company has a standard prolog, you can include that in your routine templates so that when a developer develops a new routine, your prolog is displayed in the routine editor and ready for use.

In the past, a developer was presented with a multi-page wizard that would launch windows within windows. Upon clicking Finish, a routine was generated and displayed in an editor. Developers typically deleted the generated routine, pasted their own routine skeletons as a starting point, and then began the actual development of the routine.

In order to simplify routine creation and provide value to the end user, Optim Development Studio includes many routine templates for you to use, modify, or replace. The old routine wizard has been replaced with a simple, one-page wizard, as shown in Figure 6.

Figure 6. New Stored Procedure wizard
New Stored Procedure wizard, includes fields for name, language, template details, and preview details

For each database that a data development project uses, a number of languages might be available. For each database and language, you can define routine templates. Optim Development Studio ships a number of templates for each database and supported programming language. These templates provide a more meaningful starting point for your routine development. Again, if your company has best practices for routine development, you can create your own templates and roll them out to your development team.

Complete the following steps to create a simple SQL stored procedure:

  1. From the Data Project Explorer, select the Demo project, and expand it. A Stored Procedures node appears in the project tree.
  2. Right-click Stored Procedures > New > Stored Procedure. The New Stored Procedure wizard is displayed.
  3. Type your procedure name in the Name field, such as MyStoredProcedure.
  4. For illustrative purposes, click the Language dropdown, and select a different language, such as Java. The list of templates changes based on which language you selected.
  5. Select the language SQL. The list of templates shows you the defined templates for the SQL database and language. The list shows the template name and a brief description. The preview section enables you to quickly see the template details, as well as view the template by selecting any of the template tabs.
  6. Select the Deploy & Run: IN/OUT parameters template from the list of available templates for the selected database and language.
  7. Click Finish. The SQL stored procedure is generated using the selected template and appears under the Stored Procedures node in the Data Project Explorer, as shown in Figure 7. The routine also opens in the routine editor.
Figure 7. Workspace after routine creation
Workspace shows Data Project Explorer pane on the left and pane with stored procedure code on the right
  1. To create a PL/SQL package, right-click PL/SQL Packages > New > PL/SQL Package. The New PL/SQL Package wizard is displayed.
  2. To create a user-defined function, right-click User-Defined Functions > New > User-Defined Function. The New User-Defined Function wizard is displayed.
  3. Complete the wizard for each routine type the same way. Only the list of templates is different, depending on what type of routine you want to create and depending on the database or language you select.

Manage routine templates

The routine editor has a new look and feel. For example, the editor uses a notebook and tabs. In the example in this article, there is a single DDL tab, because an SQL stored procedure has only a DDL. If you generate a Java stored procedure, it has a DDL tab and a Java tab, as shown in Figure 8. This enables you to easily switch between the two within a single routine editor session.

Figure 8. Routine editor instance with multiple tabs
Shows tabs for DDL and Java

On the right side of each routine editor, there are several button icons that enable you to quickly perform commonly used tasks, including deploy, run, debug, and display the Routine Template Preferences window, as shown in Figure 9.

Figure 9. Routine editor buttons for deploy, run, debug, and preferences
Shows 4 icons, which represent each of these actions

Routine templates are managed under the Eclipse preferences using the Eclipse standard templates window. If you have worked with Ant templates, Java templates, HTML templates, or any other Eclipse-based templates, the preference window is familiar. Complete the following steps to manage routine templates:

  1. From the routine editor, click the Open Routine Template Preferences icon. Or, from the Eclipse menu, select Window > Preferences > Data Management > SQL Development > Routines > Templates. The Routine Template Preferences window appears, as shown in Figure 10.
Figure 10. Routine Template Preferences window
Preference page shows Explorer pane on left and Templates page on the right with a field for entering the master template and a list of templates

Because the routine templates window uses the standard Eclipse templates window, this article does not discuss all the features of the templates window. Instead, this article focuses on the information you need to create and manage your own templates.

Creating a routine template

Creating a routine template enables you to implement your own skeletons and reuse them during routine development. To create a template, complete the following steps:

  1. Click New from the Routine Template Preferences window. The New Template window appears.
  2. Type a template name, such as My Template, in the Name field.
  3. Select db2luw_sp_java in the Context: field. The available context is made up of three parts: {database}_{routine type}_{language}. For example, if you want to create a DB2 z/OS SQL user-defined function template, you would select db2zos_udf_sql.
  4. Leave the Automatically Insert checkbox unchecked, because it does not apply.
  5. Enter a brief description of the template in the Description field, such as This is my first routine template.
  6. Enter your template text In the Pattern text area. This is where you can paste your company prologs, implement your best practices, and more. Optionally, you can click Insert Variable to display a list of known variables that can be used in your template. These variables are replaced with the actual value when the routine is generated. Enter the code shown Listing 1 for this article.
Listing 1. Sample template
-- this is routine ${name} created by ${user} on ${date} ${time}	

The completed New Template window is shown in Figure 11.

Figure 11. Completed New Template window
Shows name, context, description, and the text of the procedure pattern in Listing 1
  1. Click OK when you are finished. The new template is created and appears in the routine templates list.
  2. Click OK to close the Preferences window.

After closing the Preferences window, you can use your template by following the steps in Create a Stored Procedure and selecting your new template, My Template. Because ${variables} were used, the routine will differ based on the ${name}, ${user}, ${date}, and ${time} the routine was generated. Listing 2 shows an example of a routine generated using the template in this article.

Listing 2. Sample generated routing using My Template
-- this is routine PROCEDURE1 created by MPAUSER ON 2010/09/10 13:07:38	

Use master templates to deliver templates to a team

A unique feature of routine templates is the master template file. This file contains those templates that you want to make available to your developers and should be placed in a shared location for your developers to access.

The first step to using master templates is exporting those templates that you want to share with other developers. To export templates, complete the following steps:

  1. Select the template that you want to export from the Routine Template Preferences window, and click Export. The Export Templates window appears.
  2. Specify a file name on the Export Templates window, such as z:\shared\templates.xml, and click Save. The selected routine templates are saved to the shared location as templates.xml.

Once the master templates are saved in a file in a shared location, your development team can load these templates into their development environments. To load these templates, each developer completes the following steps:

  1. Specify the master templates file on the Routine Template Preferences window, such as z:\shared\templates.xml. Click Browse if you need to display the file browser.
  2. Click Reload to reload or refresh the templates into the development environment. This replaces any existing template with the same name with the contents from the master template file.
  3. Check Load at Startup to automatically reload the templates when Optim Development Studio is started, as shown in Figure 12. This replaces any existing template with the same name with the contents from the master template file.
Figure 12. Sample master template section
shows input field for the file to be used as master template with Load at Startup checked


With Optim Development Studio, creating and editing a routine has never been easier. Through the use of routine templates, you can now implement company standards or best practices. Using master templates, you can ensure that these templates and any updates to these templates are delivered to your development team.



Get products and technologies



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

Zone=Information Management
ArticleTitle=IBM Optim Development Studio: Routine development simplified