Introduction

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: Using the sample application, you learn to build the pureQuery data access on Oracle and tour the various user interface consistency and productivity benefits for pureQuery applications and existing JDBC applications.
  
  
• Enforcing data privacy requirements from modeling through test: Throughout the database and application development, you learn how sensitive customer information can be safeguarded by masking it when creating development or test databases and gaining insight into use of sensitive information when building or modifying applications.
  
  
• Developing development or test databases from heterogeneous databases quickly: Learn to use Optim Development Studio to deploy part of the existing Oracle application on DB2, and take a tour of how development and test databases can be created between Oracle and DB2.
  
  
  • Optimize use of the application with DB2 by optionally tuning queries using Optim Query Tuner as described in Integrating with Optim Query Tuner to help developers code efficient SQL
    
    
  • Optimize use of the application with DB2 by using static SQL (now available even for statements that use literals), as described in Increasing ability to use static SQL to improve performance for DB2 applications
    
    
  • Performing enhanced impact analysis: Once the sample is in production, you can see how to reduce the impact and associated cost of future changes to the application and deployed database packages due to changes in database schemas.
    
    
  • Understanding additional pureQuery runtime enhancements offers a short summary of additional Optim pureQuery runtime features in Version 2.2.
    
    
• Comparing database features: Look at a table that offers a comparison of which database features are available for each database.

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.

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.

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.

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.

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.

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.

		// Get information on function and versions 
		// of pureQuery and Java runtime environments	
	public static void 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.

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

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.

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.

Back to top

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 . Applications that use SQL containing columns with privacy information show the SQL with a 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.

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.

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.

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.

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.

Back to top

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.

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

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.

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

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

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.

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.

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

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.

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:

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.

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.

Back to top

Comparing database features

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

Back to top

Conclusion

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.

Resources

Learn

• Read more about replacement capability.
  
  
• Learn more about the common connections feature in Data Studio Developer.
  
  
• Get caught up by reading earlier articles including:
  • "What’s new and exciting in Data Studio Developer 2.1"
    
    
  • "What’s new and exciting in Data Studio Developer 1.2"
  
  
  
• Learn about SQL capture and client optimization with "Optimize your existing JDBC applications with pureQuery.”
  
  
• See client optimization in action in the second part of this two-part demo.
  
  
• See Optim Data Privacy solution and Test Data Management solution in action in this "Day in the life of a DBA" video.
  
  
• Learn about the pureQuery APIs and best practices in the article series "Write high performance Java data access applications.”
  
  
• What is pureQuery, and where can you get it? Learn more at the pureQuery platform Web page.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download Optim Development Studio 2.2.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Find links to blogs, forums, articles, and downloads at the Integrated Data Management community space.
  
  
• Explore the Optim Development Studio and pureQuery Runtime discussion forum.
  
  
• Read the Integrated Data Management experts blog.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

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

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Experiencing consistent usability and productivity for both Oracle and IBM databases
• Enforcing data privacy requirements from modeling through test
• Developing development or test databases from heterogeneous databases quickly
• Comparing database features
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers