What's new and cool in Optim Development Studio, Part 1: Fast track your application development on heterogeneous databases

Optim™ Development Studio, previously known as Data Studio Developer, takes new strides towards realizing IBM's Integrated Data Management vision. This article explains how developers, architects, and database administrators (DBAs) can collaborate in new and productive ways in heterogeneous database environments with Oracle and IBM databases. Learn how you can fast track the performance of your applications even more using Optim Development Studio and pureQuery.


Sonali Surange (ssurange@us.ibm.com), Architect, Optim Development Studio pureQuery tools, IBM

Sonali Surange photoSonali Surange is the Architect for Optim Development Studio pureQuery tools at the IBM Silicon Valley Lab. Previously Sonali led the development of several key components of the Visual Studio .Net tools for IBM databases.

developerWorks Professional author

18 June 2009

Also available in Chinese Portuguese


In June 2009, IBM announced the update and renaming of several products that were formerly called Data Studio to Optim. Optim solutions have the goal of delivering on the vision of Integrated Data Management. Integrated Data Management is defined as an integrated, modular environment to manage enterprise application data, and optimize data-driven applications, from requirements to retirement across heterogeneous environments. Key new capabilities in heterogeneous data development and data access are delivered in the products formerly known as Data Studio Developer and Data Studio pureQuery Runtime, which are now called Optim Development Studio and Optim pureQuery Runtime, respectively.

Development environments rarely comprise databases from a single vendor, and applications in an organization rarely use a single database access methodology. Similarly, team members in a development organization are rarely limited to doing one task. The reality is that developer, DBA, and architect tasks frequently overlap and, depending on the size of the organization, may even be done by the same person.

The ultimate goal of Optim Development Studio is to provide an integrated development environment to help make it easier to transfer skills across projects, enable collaboration among roles such as data architects, DBAs and developers, increase the speed of development, and improve application performance across diverse database environments. In addition, improved collaboration can certainly improve overall alignment with business objectives. For example, it can help ensure that important requirements around data privacy do not fall through the cracks as an application and data move through development and deployment.

Optim Development Studio improves your data access and provides tools to speed iterative testing on Oracle and IBM databases (the DB2® family and Informix® Dynamic Server). Along with the capability to identify performance hotspots during development, developers can easily tune SQL early in the development phase when corrections are much easier and less costly to resolve rather than after the application moves to test or production.

Optim Development Studio, Version 2.2, provides powerful integration that connects the dots between the tasks and roles you need to perform. It empowers team collaboration, provides a significant productivity boost, encourages and enables iterative testing, and improves data access performance.

Sample application used in this article

This article uses a simple Oracle credit card application partly written using the pureQuery annotated-method style (see Resources for information about the pureQuery API). Some parts of the application are already in production and were written in JDBC. The database table that holds credit card information contains sensitive information, such as credit card number and expiration date. This is important to note because some of they key enhancements in the release address how private data is handled in development.

The article covers the basic lifecycle of our application at a high level as a framework for illustrating the new capabilities in Optim Development Studio, including the following topics:

Experiencing consistent usability and productivity for both Oracle and IBM databases

If your organization requires heterogeneous database and database application development on IBM and Oracle databases, Optim Development Studio 2.2 reduces the learning curve to adoption with consistent user interfaces for Oracle and IBM databases.

You can expect productivity boosts because most of the benefits of Optim Development Studio 2.2 and earlier releases of Data Studio Developer are now available for Oracle databases. Refer to the Features per database table for specific details.

Build Java applications for Oracle with pureQuery data access methods

Figures 1, 2, and 3 show how easy it is to create a pureQuery data access layer for the credit card table on Oracle.

Figure 1. Generate pureQuery code from Oracle database object
Screen cap: Data Source Explorer showing CUST_CRDTCRD right-clicked, and Generate pureQuery Code selected from its dropdown. Callout text says

You can easily camel-case your Java bean and field names if your database objects contain spaces or underscores. Figure 2 shows how you can generate code using camel-casing preferences.

Figure 2. Generate annotated-method style from table using camel-casing
2 screen caps: In first screen cap, Preferences window shows pureQuery selected on the left and Use Camel Case When Generating Defaults for Java Names selected on the right. Callout text reads

When generating code from single SQL statements or from files containing SQL statements, you can specify parameter types. Figure 3 shows how you can optionally change the parameter types when generating beans from one or more SQL statements.

Figure 3. When generating code from SQL statements, change parameter types as needed, or keep the defaults
2 screen caps: In first screen cap, SELECT CRDTCRD_ID, CUST_CODE...is right-clicked and pureQuery > Generate purQuery Code is selected. In second screen cap, Generate pureQuery Code from an SQL Statement window shows Parameters Name: ?1, Parameter Type: NUMERIC, Java name: param1, and Java type: BigDecimal.

Improving the productivity of building Java applications (Oracle and IBM databases)

SQL content assist provides validation, highlights, and other SQL editing benefits with JDBC, native SQL in JPA, Hibernate code, or pureQuery code in order to help developers catch errors sooner. Figure 4 shows an example of how Ctrl-space (or your preferred default keystroke for content assistance) provides appropriate table names for the FROM clause within JDBC.

Figure 4. SQL content assistance and highlighting
Screen cap: SELECT CRDTCRD_ID, CUST_CODE...is right-clicked, and CRDTCRD_EXP_DATE - DATE is selected from the dropdown.

A note on syntax checking for Oracle

SQL 99 syntax is supported for Oracle. In this release, you can choose either to switch off SQL validation support or to show errors as warnings for your Oracle applications that use Oracle-specific syntax.

Gaining insight into the SQL-issued and related performance metrics

For any new or existing Java application that accesses an Oracle database, even those created using a framework such as Hibernate, you can see the SQL statements that are issued and the related performance metrics, such as frequency and elapsed time. To use pureQuery capabilities with existing Java code, you need to capture the SQL while the application is running. This capability is called client optimization, and it is described in the tutorial in Resources entitled “Optimize existing JDBC applications with pureQuery.”

Performance metrics for Oracle and IBM databases

Previously, performance metrics were only available for inline style applications and for those for which SQL was captured using client optimization. In Optim Version 2.2, this capability is also available for pureQuery annotated-method style applications.

Figure 5 below shows the performance metrics for the credit card application using the pureQuery annotated-method style, which can help identify areas that might require more tuning.

Figure 5. Performance statistics for annotated-method style
3 screen caps: In the first screen cap, CustCrdcrdDataTest.java is right-clicked, Run As > Run Configurations are selected, and CustCrdtcrdDataTest is highlighted in the resulting Run Configurations window. Callout text reads

New helper methods to determine JRE and pureQuery runtime (for Oracle and IBM databases)

For both Oracle and IBM pureQuery applications, you can find out which JRE your application is using and also which JDBC driver and pureQuery runtime versions are used. This can help ensure that the application is using the correct prerequisite levels.

An example of this is provided in the SampleUtil class that is generated with your pureQuery annotated or inline style application, as shown in Listing 1. You can execute the main helper method of this class to see features and runtime versions that your application uses.

Listing 1. SampleUtil class
		// Get information on function and versions 
		// of pureQuery and Java runtime environments	
	publicstaticvoid main(String[] args)
		System.out.println( com.ibm.pdq.tools.DataVersion.getVersion());
		System.out.println( com.ibm.pdq.tools.DataVersion.getConfiguration());
		System.out.println( com.ibm.pdq.tools.DataVersion.getFeatures());
		System.out.println( com.ibm.pdq.tools.DataVersion.validate()); 


Figure 6 shows how this information is returned.

Figure 6. Gain information about JRE and pureQuery runtime and versions and about supported features
2 screen caps: In the first screen cap, SampleUtil.java is right-clicked, and Run As > Java Application are selected. Callout text reads

Replace SQL in existing Oracle applications--without changing code

As was previously available for IBM databases, you can capture the SQL of running applications and then modify the resulting capture file to replace any poorly performing SQL with one that has been tuned, without requiring access to the application source code. This can be useful in those critical situations where you need to at least temporarily fix an urgent SQL problem but cannot wait for a fix from a vendor or developer. SQL replacement capability has now been extended to Oracle with Optim Development Studio (see Resources for more information).

Oracle-specific features

Look for more content about the following Oracle-specific features:

  • Edit, run, debug, and deploy PL/SQL procedures on Oracle and DB2
  • Run commands using new CLPPlus

Optim 2.2 offers for both Oracle and IBM databases the ability to compare two SQL statements as text. You can do this whether you are editing the capture file using the pureQuery XML editor or viewing the SQL outline. Not only can this can greatly improve readability when working with large SQL statements, you can also easily compare and make changes using the Eclipse text compare features.

Figure 7 shows how a SELECT statement captured from an Oracle application can be replaced with a better one. The two SQL statements are compared as text for easy viewing and modification.

Figure 7. Compare two SQL statements as text
3 screen caps: In the first screen cap, pdq.properties is selected, and pdq.enableDynamicSQLReplacement=true is circled. Callout text reads

Explain information available in SQL outline (IBM and Oracle)

For both Oracle (Version 10.1 and higher) and IBM databases, you can gain insight into the health of your SQL and determine which SQL should be further tuned. You can get information on the predicted cost, on whether indexes are used, on how many joins are used, and so forth. Figure 8 shows how this information is visualized from the SQL outline.

Figure 8. Explain information in SQL outline
3 screen caps: In the first screen cap, pureQuery Background EXPLAIN is selected, and enable Background EXPLAIN is checked. Callout text reads

Enforcing data privacy requirements from modeling through test

Production databases often contain sensitive information, such as credit card numbers or social security numbers. When data architects use InfoSphere™ Data Architect to create data models for such databases, they can identify which attributes or columns contain sensitive information, and architects can specify appropriate privacy policies to be used with them.

Then, by associating this model with an architect's database, developers can easily see which columns are identified as containing sensitive information. This can help them maintain compliance in how they handle that data in their applications. Going even further, they can see within their applications where those private columns are being used in context to ensure that they are not doing something inappropriate, such as printing out data in those columns.

Hint: If you play a dual role, you can shell share with InfoSphere Data Architect to access data architect capabilities from your development desktop.

You can now associate a physical data model from InfoSphere Data Architect with a connection definition in the common connection repository. (For more about common connections, see the Common Connections article in Resources.) Team members creating connections from the connection repository can now have access to the same model using their connection, ensuring a consistent development and architect handshake. Any connection associated with a model containing privacy information can now show that association in the Data Source Explorer with a privacy icon Data Explorer privacy icon. Applications that use SQL containing columns with privacy information show the SQL with a privacy icon SQL privacy icon.

In the Figure 9, the credit card physical model is associated with the database connection. As a result, the credit card column is shown as private in the developer environment.

Figure 9. Associate model with connection and see private columns
3 screen caps: In the first screen cap, Data Project Explorer shows CREDIT CARD NUMBER right-clicked and resulting Properties window shows Data Privacy and Privacy Policy Type: Credit Card Number (CCN) highlighted. Callout text reads

As mentioned, developers can use the SQL outline to see which SQL statements contain sensitive information and where these SQL statements appear in the application. Figure 10 shows that an update statement is manipulating a column that contains private data.

Figure 10. Show use of “private” columns in context
Screen cap: SQL Outline tab shows UPDATE CUST_CRDTCRD SET... and CRDTCRD_PRIM_ACCT_NBR highlighted.

To see only the SQL statements using private columns, use the filter as shown in Figure 11. In addition, you can get details about the privacy policies applied for this column in the physical data model and easily navigate to the associated physical data model itself.

Figure 11. Find only those statements that use private columns
3 screen caps: In the first screen cap, the Filter window shows SQL containing columns marked as private selected. In the second screen cap, the SQL Outline tab shows Schemas. Under crdtCrdapp, CRDTCRD_PRIM_ACCT_NBR is right clicked to show Find in Physical Data Model. In the third screen cap, the Properties tab highlights Classification and Credit Card Number (CCN).

More on filters in SQL outline

Additional SQL outline filtering enhancements now enable you to filter on select, insert, update, and delete type statements.

When using pureQuery applications, you can determine whether the sensitive value is being printed on the console. Figure 12 shows how the value of a credit card number flows through the application. In addition, a filter is applied to see if and where the value is printed to the console.

Figure 12. Identify all locations in your application that might print sensitive information onto the console
3 screen caps: In the first screen cap, SELECT CRDTCRD_ID, CUST_CODE, CRDTCRD PRIM_ACCT_NBR, CRDTCRD_SERV CQ is right-clicked, and pureQuery > Source Code Analysis > Analyze Column Value Dependency is highlighted. Callout text reads

So far, you have gained insight into how sensitive information is used in a pureQuery application that will run against Oracle. Note that although this example focused on pureQuery data access, the features are also available on existing JDBC applications, such as Hibernate or JPA, when the SQL statements have been captured using client optimization.

Developing development or test databases from heterogeneous databases quickly

Continuing the example, assume that you are deploying part of the JDBC-based Oracle application onto DB2. To do that, create a DB2 development database that reflects the characteristics of the Oracle database, while safeguarding private data.

Supported servers for copy-and-paste capabilities

  • If copying between Oracle and DB2, use DB2 for LUW 9.7 in Oracle compatibility mode and Oracle Version 10 or Version 11.
  • If copying from Oracle to Oracle, Oracle must be at Version 10 or Version 11.
  • If copying from DB2 to DB2 on LUW, use Versions 9.5 and 9.7.

Optim Development Studio offers an easy way to copy and paste objects and data subsets between databases that are needed for a certain function in the development or test environment. In addition, if the test data is sensitive and must be de-identified, Optim Development Studio can generate the appropriate input for the Optim Test Data Management solution to extract and mask the data for migration.

You can move or copy objects between homogeneous databases (Oracle to Oracle, or between DB2 for Linux®, UNIX®, or Windows® [LUW] databases) or between heterogeneous databases (Oracle to DB2 for LUW, and DB2 for LUW for Oracle). You can copy the whole database, related objects, or a single object at a time.

Figures 13 and 14 show the sequence of copying the credit card table from Oracle and pasting it into DB2 with the optional specification of data privacy masking. Note that the customer credit card table has the privacy icon for the primary account number.

Figure 13. Copy from Oracle production database
Screen cap: Data Source Explorer tab shows CUST_CRDTCRD right-clicked, and Copy is selected in the dropdown.

Figure 14 shows the paste operation into DB2. If you specify that you want data privacy, Optim Development Studio does the following:

  • Copies the specified database objects
  • Generates the appropriate Optim export file, which can be consumed by Optim Test Data Management Solution to copy the data (or a referentially intact subset) into the newly created object with appropriate masking. See Resources for a video showing Optim Test Data Management with the Data Privacy Option in action.
Figure 14. Paste into DB2 and specify data privacy masking
3 screen caps: In the first screen cap, the Data Source Explorer tab shows GS_DB > GOSALESCT right-clicked, and Paste selected in the dropdown. In the second screen cap, Paste Database Objects window shows Paste with a Test Data Subset and Optional Privacy Masking selected. In the third screen cap, the Paste Database Objects window shows Export location: optimExportFile.txt highlighted.

Recommendation: The copy-and-paste feature in Optim Development Studio improves productivity while creating small development or test environments. It is not intended for migrating large numbers of objects, or for large amounts of data. For large migrations or the creation of production-like environments, use other tools such as DB2 High Performance Unload for fast migration of large amounts of data or Optim Test Data Management for more flexibility regarding copy complete business objects.

Integrating with Optim Query Tuner to help developers code efficient SQL

Now that you created the DB2 development database and moved the JDBC application there, you can now optimize the application by tuning its SQL as needed for access to DB2.

Usually developers do not focus on tuning SQL. Nevertheless, if this task could be made easier, developers could learn SQL tuning skills, as well as improve application performance proactively during development, to avoid expensive changes in the post-development or production stages. This is the goal of Optim Query Tuner, which can provide expert advice to help developers write more efficient SQL, or at least have enough information in hand to ask for new indexes or statistics from the DBA.

Although Optim Query Tuner is available as a separate product, it integrates seamlessly with Optim Development Studio when installed in a shared shell to provide natural launch points for query tuning, such as from within the Java® editor or from the SQL outline view and pureQuery XML editor.

Figure 15 shows the SQL outline view of the example credit card application on DB2, for which predicted cost information is also listed. You can select the costliest query, and you can launch Optim Query Tuner directly from that view.

Figure 15. Select the most expensive query and tune it
3 screen caps: In the first screen cap, the Optim Query Tuner tab shows Choose Tuning Activities highlighted. In the second screen cap, the Choose Tuning Activities window shows all the activities selected and OK highlighted. In the third screen cap, the SQL Outline tab shows the crdtCrdAppDB2 line right-clicked with Tune SQL selected from the dropdown. Callout text for Cost: 153.25 reads

Now view the advice provided, and make changes accordingly. In the example, Optim Query Tuner recommends that you update statistics, which you can run directly from the Query Tuner tool. (Depending on whether a developer is managing his own development database or not, he might need to ask the DBA to run statistics.)

Figure 16. Advice from Optim Query Tuner
2 screen caps: In the first screen cap, crdtCrdappDB2_Query tab highlights Formatted Query and shows the Advisor Recommendation Overview recommending that the Statistics Advisor is a High Priority item. In the second screen cap, The Statistics Recommendation Detail window shows the RUNSTATS ON TABLE 'GOSALESCT' control statement right-clicked, and Run is highlighted.

You can now use the SQL outline again to see that the cost estimate is reduced just by doing this simple task.

Figure 17. Retrieve Explain information for new SQL to see improved cost
Screen cap: The SQL Outline tab shows the SELECT CRDTCRD_ID, CUST_CODE... line right-clicked with Retrieve EXPLAIN Data highlighted in the dropdown. Cost: 145.35 is also highlighted. Callout text reads

Increasing ability to use static SQL to improve performance for DB2 applications

You can further optimize the application on DB2 using static SQL. This leads you into the following enhancements:

  • Literal replacement with parameters during the capture process
  • Bind flexibility
  • Capability to save and compare performance metrics. Note that the performance metrics comparison feature is available on Oracle and IBM databases

Literal replacement with parameters during capture process

In prior releases of Optim Development Studio and Optim pureQuery Runtime, SQL statements that used literals could not benefit from static SQL execution because the capture process treated each statement as different (as the value in the literal changed).

Optim pureQuery Runtime 2.2 can now replace such SQL statements with parameterized ones so that many unique SQL statements can be consolidated into one, thereby improving the cache hit ratio for dynamically executed SQL, or making it easier to convert to static SQL, as shown in Figure 18.

Figure 18. Literals replaced with parameters during the pureQuery capture process
2 screen caps: In the first screen cap, pdq.properties and pdq.sqlLiteralSubstitution=ENABLE are highlighted. Callout text reads

Bind flexibility

Next, bind SQL statements into a DB2 package and execute the application using static SQL, as shown in Figure 19. Note also the use of new capabilities in 2.2, including the ability to bind a single package from a captured pdqxml from the SQL outline database tab. You can also choose to run the bind process in the background to avoid disrupting other work you might be doing.

Figure 19. Bind single package
2 screen caps: In the first screen cap, the capture.pdq tab shows INSERT INTO CUST_CRDTCRD... highlighted, and the SQL Outline tab shows CustCrdcrdData right-clicked and Bind selected from the dropdown. Callout text reads

Capability to save and compare performance metrics

You can save the performance data set for each execution and compare performance results between executions of your application. Using the performance compare features, you can now compare the performance of your application between dynamic execution and static execution with literal replacement.

Figure 20 shows a significant performance boost in the example system. (Note that your results might vary significantly based on your specific execution environment and data.)

Figure 20. Compare performance of execution between dynamic execution and static SQL execution with literal replacement
Screen cap: The SQL Outline tab shows Compare selected and Database Packages information in columns labelled Number, Total Time, Max Time. Notably, for SELECT CRDTCRD_ID, CUST_CODE, the total execution time using static SQL with literal replacement is 198.05. The total execution time using dynamic SQL with no literal replacement is 301.08. Callout text reads

In the last few sections, you tuned the query and improved its predicted cost. You then turned SQL statements using literals into parameterized SQL so the code could be executed using static execution mode. You then confirmed the execution time performance improved by performing this step.

The next section shows how Optim Development Studio reduces the cost associated with applications in production when it comes to making changes to database schemas.

Performing enhanced impact analysis

Production applications undergo changes periodically. There might be changes in business requirements or feature functionality that require changes to the database schema as well. Database changes often require changes in the associated Java code used to query and handle results. If you miss a required change in a production application, you can potentially cause an application outage. If your project is in development, late changes to the database schema could trigger significant code changes that jeopardize the timely delivery and quality of the deliverable, thus increasing cost.

In addition, if you are using static SQL on DB2, changing the SQL can trigger changes in the existing deployed packages that would need to be rebound. To minimize disruption caused by package rebinds, it is important to ensure that existing packages that don’t need to be changed can remain untouched.

Change management is a critical task that should be managed with as much information as possible to help the staff make good decisions. Gauging the impact of an upcoming database schema change on your application in production or development is now made even easier in Optim Development Studio.

The example shows how a DBA and development staff can assess the impact of dropping a column CRDTCRD_EXP_DATE in a credit card application on DB2. A similar approach can be taken for columns being renamed and other database schema changes.

Typically, when a DBA proposes changes, he provides scripts with data definition language (DDL), such as ALTER statements, to execute that change. To create DDL, do the following:

  1. Right-click on the ALTER script, and select Find reference in SQL outline.
  2. Use the SQL Outline to view which SQL would get impacted with this change. From each SQL, you can navigate to the location in the source where it needs to be changed.
  3. For existing applications where the SQL has been captured, you might also need to manage the captured SQL to remove any SQL that might be invalidated. If you need to keep the SQL statements, you can move them to your own packages. Later, you can re-capture the new SQL into a new package or capture file.

To read more about capturing SQL from existing applications and using static SQL, see the tutorial entitled "Optimize existing JDBC applications with pureQuery" in Resources. Optim Development Studio makes the process simple, as shown in Figure 21.

Figure 21. Finding SQL impacted by a change and then deleting from the capture file
2 screen caps: In the first screen cap, the Script3.sql tab shows Alter Table GOALESCT.CUST_CRDTCRD Drop Column CRDTCRD_EXP_DATE right-clicked, and Find Reference in SQL Outline is selected. The callout text reads

At this point, if the impact on the application and database packages is acceptable, you or the DBA can execute the ALTER statement. Follow these steps to finish the changes needed to your application and database packages:

Summary of capture file editor improvements

Use the improved editor to gain more control over your captured file and how DB2 packages are constructed. You can:

  • Create new packages
  • Move statements from one package to another
  • Delete existing SQL statements or replace them with compatible ones
  • Compare two SQL statements using text compare features in Eclipse
  • Selectively bind packages or statements within packages
  • Name your packages, or have the tool name them for you
  • Easily restore an SQL statement back to the original captured state, or restore the captured file back to the last good version
  • Filter SQL statements by database objects or by SQL types (or any other criteria for that matter), which makes it easier to find what you’re looking for in very large capture files.
  1. Use the SQL validation features to identify the SQL that needs to change in the code and use content assist to replace them with valid ones. The validation features are available for JDBC applications, for native SQL in JPA applications, for Hibernate applications, and for pureQuery applications.
  2. Rerun the application, and capture its SQL.

    With Version 2.2, you can capture logically related parts of your application into separate capture files. This can help isolate the effects of DDL changes to only those applications that use parts of that changed schema.

    Also in Version 2.2, packages that have already been configured and bound can be marked in the capture file as don't touch or FINAL. Additional statements from an incremental capture are then captured into a new package.

  3. You can verify whether packages already exist for artifacts from your projects by specifying the following in the bindprops and then running bind: defaultOptions = –verifyPackage DETAIL. The console prints information about the packages that exist on the database already, if any. You can easily identify which pureQuery interfaces or captured files need to be re-bound after changing the code.
  4. Use the new feature to bind single packages from the SQL outline to bind only the changed packages. Or, you can specify -differenceOnly TRUE in your default.bindprops file, which binds only the packages that changed. Note that dropping a column that is referenced by one or more statements in bound packages invalidates the packages on the database.

To clean up any existing packages that are invalid as a result of this change, you can use the new function to find invalid packages on a database or schema level. You can then multi-select and drop them, as shown in Figure 22.

Figure 22. Find and drop invalid packages from Data Source Explorer
2 screen caps: In the first screen cap, the Data Source Explorer tab shows Schemas right-clicked and Find Invalid Packages selected from the dropdown. The Find Invalid or Inoperative Packages window shows the invalid packages. In the second screen cap, the GS_DB > Packages are right-clicked, and Drop is selected from the dropdown. Callout text reads

Understanding additional pureQuery runtime enhancements

  • For pureQuery applications, you can now specify application level properties that enable you set application properties without restarting the Web server.
  • Using the new support for SQL, positioned update is faster than searched update or delete.
  • For optimizing existing applications using client optimization, you can generate multiple capture files to lock captured information during incremental capture. You can then merge captured files using the command line or Optim Development Studio tools.

Comparing database features

This table shows which features are available for each of the listed databases.

Table 1. Feature support per database platform
FeatureDB2 for LUWIDSDB2 for z/OSDB2 for iOracle
pureQuery code generation Yes Yes Yes Yes Yes
Client optimization Yes Yes Yes Yes Yes
SQL editing features such as content assist, validation, highlighting
pureQuery, JDBC, JPA, Hibernate
Yes Yes Yes Yes Yes
All SQL outline benefits Yes Yes Yes Yes Yes
Performance metrics and comparison in SQL outline Yes Yes Yes Yes Yes
SQL Explain data in SQL outline Yes Yes Yes Yes 10.1 or higher
Helper methods for runtime and JRE Yes Yes Yes Yes Yes
Data privacy from modeling to test Yes Yes Yes Yes Yes
Quick and easy test and development database development Homogeneous
(9.5 and 9.7)
Heterogeneous (9.7)
No No No 10 and 11
Integration with Optim Query Tuner Yes No Coming soon Yes No
Literal replacement with literals during Client Optimization capture Yes Yes Yes Yes Yes
Application level properties Yes Yes Yes Yes Yes
Multiple capture files and merge Yes Yes Yes Yes Yes
Static SQL benefits Yes No Yes Yes No
Heterogeneous batching Yes Yes Yes Yes No
Enhanced impact analysis Yes Yes Yes Yes Yes


This article briefly addressed the new features that make Optim Development Studio a must have for any shop that wants to create high-performing Java data access code and take advantage of productivity and impact analysis features across Oracle, DB2, and Informix Dynamic Server databases.



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=What's new and cool in Optim Development Studio, Part 1: Fast track your application development on heterogeneous databases