Introduction

Some of the main benefits to this new release of Data Studio Developer include:

• It gives you more information to focus your SQL tuning efforts.
  
• It brings improved quality of service for OpenJPA and for .NET applications.
  
• You can gain enhanced insights into your Java applications that use SQL.
  
• It gives you the ability to improve productivity and collaboration across developers and DBAs.
  
• You'll be able to reduce or eliminate SQL injection risk for Java database applications.
  

Pre-requisites to run the hands-on scenarios

You'll need the following installed in order to try the scenarios below:

• Data Studio Developer 2.1
• DB2® for Linux®, UNIX®, and Windows® -– DB2 Express-C works fine
• DB2 sample database that comes with the product

Give developers more information to focus SQL tuning efforts

Identify the SQL statements and the frequency of their execution

Existing profiling tools let you identify which Java™ methods in the application take the longest time to run. With Data Studio Developer 2.1, you can drill down even further by seeing performance metrics for Java methods that contain calls to the database. Whether you are using pureQuery, JDBC, or another framework, you can identify how much time was spent per SQL in your application, how many times it was run, and the minimum, maximum and average time it took to run.

Try these simple steps in Data Studio:

1. In the views, go to Data Management > Data Source Explorer (formerly Database Explorer). Establish a connection to the sample database and call the connection SAMPLE.
2. Create a Java project and name it myDSDPrj.
3. Download and copy the files TutMod.java and Util.java to db2jcc.example package. This file represents a simple existing JDBC application that you will use to run the various scenarios.
4. To add pureQuery support to the project:
   1. Right click on the project and select pureQuery -> Add pureQuery support.
   2. Select SAMPLE and add the appropriate schema to qualify your STAFF table.
   3. In the image below, the schema is SSURANGE.
   4. Check the box for Enable SQL capturing and binding for JDBC applications
   
   
   
   Figure 1. Add pureQuery support
   
   
   

   Notice that you now have a much-improved ability to specify a default schema to qualify tables and views. Use the new Default path widget to qualify your unqualified routines.

5. To collect the performance metrics, run the application using the pureQuery configuration:
   1. Set the schema to qualify the STAFF table before running the application. To do so, Open TutMod.Java, go to line #66 and change the value SSURANGE to your schema and save the file.
      stmt.execute("set current schema SSURANGE");
   2. Run the application using the pureQuery configuration by opening TutMod.java, then right clicking on the file and selecting Run -> Run Configurations.
   3. Select the pureQuery and provide arguments for the database name, server, port, userID and password (Figure 2) and click Run.
   4. The SQL executed along with the performance information is collected.
   
   
   
   Figure 2. Run application to gather SQL metrics
   
   
   
6. To view the performance information:
   1. Right click on myDSDPrj and select Show pureQuery outline
   2. In the pureQuery outline, click on the Toggle Profile button.
   3. Expand the myDSDPrj project, and you can see performance information for each SQL statement.
      For example, the image below shows the SELECT statement was run 2 times with a total time of 270ms.
      
      
      Figure 3. SQL execution metrics
      
      
      

The SELECT statement is run more often and the application might benefit by optimizing this SQL.

Tune and optimize your application without changing code

Once you identify the problem SQL, you need to optimize it. For production applications or packaged applications, you may not have the ability to change the application’s source code, but you may want to have the ability to change the resulting SQL. For example, you may want to change the SQL to take advantage of a newly added index, to limit the number of rows returned, or to add a sorely needed ORDER BY clause.

Data Studio Developer 2.1 provides a controlled editor to let you replace the existing SQL statement that is generated from the application with one that is equivalent and, hopefully, better. The editor automatically validates statement equivalence and disallows non-equivalent statements.

Try these simple steps in your Data Studio Developer install:

1. In myDSDPrj, expand the pureQueryFolder. Double click on capture.pdqxml
2. Right click on the SELECT statement ( SELECT id, name, dept, job, years, salary, comm FROM staff WHERE id = ?)and select Edit. This is the SELECT statement you saw above in Figure 3. Select the default schema to qualify your table.
   
   
   
   Figure 4. Edit existing SQL to reduce number of rows returned
   
   
   
   
3. Now you can edit the statement with the following SQL, which is equivalent but which returns fewer rows:
   SELECT id, name, dept, job, years, salary, comm FROM staff WHERE id = ? fetch first 2 rows only
   
4. Click Save.
   
   
   
   Figure 5. Replace with optimal SQL
   
   
   

Now that you have changed the SQL to return fewer rows, run it again and see if it made any difference in the performance metrics.

1. To run the application again:
   1. Open pdq.properties and set captureMode to OFFand add enableDynamicSQLReplacement as true
      
      pdq.captureMode=OFF pdq.executionMode=DYNAMIC pdq.enableDynamicSQLReplacement=TRUE .
   2. Run the application using the pureQuery configuration.
3. To view the performance metrics again, right click on myDSDPrj and select Show pureQuery outline
4. The image below shows that SELECT statement ran two times (as before) but with a total time of 126ms. Notice that the SQL statement took less time than before.
   
   
   Figure 6. Better SQL performance after replacing with optimal SQL
   
   
   

Improve quality of service for OpenJPA and for .NET applications

Problem determination and quality of service is easy with JPA

Now you can gain knowledge about any SQL and JPAQL issued by your existing or new JPA applications that access DB2 data. You can also seamlessly use static SQL without changing any of the application code. If your application is using DB2, use the static generator (wsdb2gen) in WebSphere® Application Version 7 to collect the SQL. Most benefits are available without running the applications. For the remaining cases, combine with client optimization. Data Studio Developer 2.1 includes tooling support to make the static generation process easier. WebSphere 7 fixpack 3 is a pre-requisite.

Try these simple steps in your Data Studio Developer install:

1. Add pureQuery support to your JPA project.
2. Add com.ibm.ws.jpa.jar from your WebSphere installation to the build path.
3. Right click on your persistence.xml and select pureQuery > Generate pureQueryXml file.
4. Right click on project and select pureQuery->Show pureQuery outline.
5. View all possible CRUDs generated from your entities, namedQueries, and namedNativeQueries from entities as well as mapping files.
6. Double click on any SQL to drill into the JPA source that triggered the SQL query.
7. As an optional step, you can use client optimization to capture SQL that is only available at runtime (for example, dynamic JPAQL statements). Run your application with the pdq.Properties captureMode ON and collect any remaining JPAQL and SQL from your application.

Whether your application is using DB2 or not, optimize existing non-DB2 JPA applications without changing a line of code. To replace existing SQL with optimal SQL, refer to the Tune and optimize your application without changing code section. Stay tuned for a more in-depth, hands-on tutorial on Data Studio Developer 2.1 support for JPA.

Improve quality of service for .Net applications using static SQL

Data Studio pureQuery Runtime 2.1 provides support to improve the stability, security, and manageability of .Net applications, including those written in any .Net based language such as C# and VB.Net. Upgrade existing .Net applications with this SQL outline.

1. Set capture mode to ON in .Net connection string
   captureMode = on;pureQueryXML=path/filename:collection=collName;rootpackage=pkgName
2. Execute all tests
3. Use DB2cap utility to bind and create static packages from the captured SQL
4. Grant execute permissions
5. Set capture mode to OFF and executionMode to STATIC in connection string
   executionMode=STATIC:captureMode=OFF
6. Execute all tests to verify correct results.

Your existing C# or VB.Net program now runs with static SQL!

Gain enhanced insights into your Java applications that use SQL

See what SQL will be issued without running your application

To use Data Studio Developer 1.2 for existing applications in pureQuery outline, you had to run the application to see the SQL that was issued and to see where it is in the source. With Data Studio Developer 2.1, you can automatically get a peek into the SQL in your application without running the application. You’ll be able to see any hardcoded SQL in the application. You can run the application to collect any remaining SQL.

Try these simple steps in your Data Studio Developer install:

1. Right click on myDSDPrj and select Show pureQuery outline.
2. In the pureQuery outline, click on the Toggle Profile button.
3. Select the Database tab. Notice that the UPDATE and DELETE statements that haven’t yet executed are shown in the outline. Double click on the DELETE statement to go to the location in the Java file. In the image below, you can see that the issuing Java source was an executeUpdate statement.
   
   
   Figure 7. Automatic peek into the SQL - Database tab
   
   
   
4. Click on the Java tab. Expand myDSDPrj and package name db2jcc.example. Notice that the DELETE and UPDATE statements that have not yet executed are shown in the outline. This time, double click on the update statement to go to the location in the Java file (in this case, to the JDBC prepared statement). Notice that the location of execution for the prepared statement is also shown at the JDBC executeUpdate.
   
   
   Figure 8. Automatic peek into the SQL - Java tab
   
   
   

If you actually run the application, the accuracy of the SQL location is improved. In addition, it includes any dynamically constructed SQL.
Try these simple steps in Data Studio Developer:

1. Open TutMod.java and uncomment the basicUpdate call in the main method.
2. To run the application:
   1. Open pdq.properties and set captureMode ON and add enableDynamicSQLReplacement as false
      pdq.captureMode=ON pdq.executionMode=DYNAMIC pdq.enableDynamicSQLReplacement=false
   2. Run the application
3. Right click on the myDSDPrj and select Show pureQuery outline.
4. Click on the Java tab. Expand myDSDPrj and package named db2jcc.example. Notice that the executeUpdate hint shown earlier is now replaced with the actual SQL update statement.
   
   
   Figure 9. Run application for better accuracy - Java Tab
   
   
   

Determine what SQL will be issued and where it will be issued from

Previously, the pureQuery outline was limited to individual projects, so you could only see information about the SQL issued from your application on a per-project basis. For applications that span projects, you couldn’t get the complete picture easily.

Close all projects in your workspace that don't belong to your application. Data Studio Developer 2.1 gives you all the information you need about the SQL your application will issue to the database.

Before you continue, let’s add some more projects to your application. Data Studio Developer now allows you to generate pureQuery code in bulk for many tables.

Bulk bottom-up code generation

In Data Studio Developer 1.2, you could only generate code for a single table. In Version 2.1, you can get your bottom-up code generation done faster for many tables.

1. Create a Java project called pdqBulkBottomUp.
2. Select DEPARTMENT and EMPLOYEE tables, right click, and select Generate pureQuery code.
3. Provide package names and select Generate annotated style code. Click Next.
   
   
   Figure 10. pureQuery code generation from two tables
   
   
   
4. For the DEPARTMENT table, select the SQL Statements tab. Select Select all rows, Create row by parameters, and Create row by object as shown below. Click Finish.
   
   
   Figure 11. pureQuery code generation - SQL statement selection
   
   
   
5. Now data access can be generated for a large number of tables in no time! Note that SQL CRUD statements are generated separately for each table; that is, relationships between tables are not considered.
6. Right click on the project and select Show pureQuery outline to drill down into the database and drill up into the application for each SQL. Get answers to questions such as: Which SQL uses the DEPARTMENT table and where in the Java code is it used?
   
   
   Figure 12. Which SQL uses a certain schema or table and where in the Java code is it used - Database tab
   
   
   
7. Click on the Java tab; as you might expect, all projects are seen in the pureQuery outline. You can drill down for the Java class to SQL to database correlation for your application. Get answers to questions such as: What SQL is issued by my Java class DepartmentData and where?
   
   
   Figure 13. What SQL is issued by the Java class and where - Java tab
   
   
   

Discover which SELECT statements are used by your application or which SQL statements use the absolute value (abs) function

Previously, you were able to get information about which SQL statements use a database column or table using the filter in pureQuery outline. Whether you are using pureQuery APIs or have existing applications using JDBC or frameworks, Data Studio Developer 2.1 lets you focus on the SQL you are interested in by allowing enhanced filtering. Use this capability to find out which SQL statements use a particular column and which of those are SELECT statements. Also, learn which SQL statements use a certain function.
Try these simple steps:

1. Click on the Filter button in the pureQuery outline as shown below.
2. For SQL text filter pattern, provide the filter information (.*SELECT.*). Then select OK. (.*SELECT.*)
   
3. Now, you can see all SELECT statements from your application in the filtered pureQuery outline view.
   
   
   Figure 14. Discover which SELECT statement your application issues
   
   
   
4. Now, to add the filter by column name, enter the filter value <SCHEMA>.STAFF.ID. You can see all SELECT statements that use the ID column in the STAFF table. You can see how this can really help with impact analysis. For database object name, provide the value <schema>.STAFF.ID. View all SELECT statements using the ID column from the STAFF table.
   
   
   Figure 15. Discover which SELECT statements use column ID
   
   
   

Find out what SQL is in your application and which one is sent to the database

View the pureQuery outline to correlate the SQL in the application with the SQL actually sent to the database. Let's look at the SQL you replaced earlier.
Try these simple steps in Data Studio:

1. In the Database tab in the pureQuery outline, expand the SELECT statement. Notice the SQL sent to the database is the one you replaced in the above step.
   
   
   Figure 16. View SQL in the application, and the one sent to the database
   
   
   

Find where the SQL is defined and executed

Data Studio Developer 1.2 showed approximate positions for SQL statements in source code based on a stack trace. Data Studio Developer 2.1 now provides greater accuracy for the location of SQL in your application. Wondering where the SQL is defined and executed? What are all the code paths that call the same SQL?
Try these simple steps in your Data Studio Developer install

1. Click on the Java tab in the pureQuery outline, and expand the SELECT statement. Notice that you can see the location where the SQL is defined in (the preparedStatement ) and where it is executed ( the executeQuery).
2. Notice that the delete statement points to the specific line where the SQL is prepared and executed.
   
   
   Figure 17. View where SQL is defined, and where it is executed
   
   
   

See how the values of the result set are flowing through the Java application

Gain more information about how the SQL is consumed by your Java application. Where is the retrieved column value used? Is it sent back to the database? Is it printed out? If you have the source code, you can track how the values are flowing through your application, once they are returned in a result set.

1. Open DepartmentData.java, right click on DEPTNO and select Source Code Analysis, Analyze column value Dependency.
2. pureQuery analysis view shows how the value flows through the application. Notice the database icons highlight where the value is retrieved from the database and updated to the database.
   
   
   Figure 18. View how DEPTNO column value is flowing through the Java application
   
   
   

Improve productivity and collaboration across developers and DBAs

Create and validate deployable bindProps

Previously, bind properties used for command line deployment had to be manually created. There was no way to indicate if entries were invalid and you had to wait until errors were reported at deployment. Developers were not able to easily test deployment using jar, ear, or war files.

Ready to deploy? Data Studio Developer 2.1 significantly speeds up deployment. Create deployable bindProps that can be centrally maintained yet shared with the organization. Data Studio Developer 2.1 allows developers to test drive these steps to ensure the application is deployment ready.

1. Right click on the Default.bindProps and select Add remove entries. Select com.pdqdemo.EmployeeData and com.pdqdemo.DepartmentData. The figure below shows an example of how entries can be easily managed.
   
   Figure 19. Easily manage static SQL properties
   
   
   
2. Next, to test bind, right click on Default.bindProps and select bind.

Now that you have verified that bindProps are ready, jar the application for deployment.

Bind deployable jars

Once the application is developed and tested, it is jarred up for deployment. Jar is then bound on the command line. Again, Data Studio Developer 2.1 allows developers to test drive these steps to ensure the application can be deployed. You can now benefit from intelligent where bind will be performed only when needed. Avoid unnecessary binds when redeploying a jar when only a subset of contained applications has changed.

1. Jar the files in the src folder and the bindprops in the pureQuery folder from the project pdqBulkBottomUp.
2. To bind the Jar file, right click on the jar file and select pureQuery->Bind. The entries listed in the bindProps will be bound.

More information on bind errors provided

In Data Studio 1.x, bind failures showed limited information when bind failed. You were not able to identify which packages caused bind failures. Lack of this knowledge made it harder to fix bind issues. The problem became even harder because numerous packages containing many SQL statements were typically bound at deployment.

Data Studio Developer 2.1 now significantly improves the information provided when bind errors occur. Version 2.1 allows you to get more feedback about which packages failed in the bind and to determine the cause of failure from Data Studio Developer tools and pureQuery runtime command line tools.

Now, unnecessary binds are avoided when redeploying a jar when only a subset of contained applications has changed

Even more integration between SQL and Java tools

If you are a DBA using tools such as the SQL builder to create SQL statements or simply using the SQL editor, you can now easily generate pureQuery code. From the SQL builder, save the SQL to a .sql file. Then, simply right click on the .sql file or in an open SQL editor and select Generate pureQuery code. The wizard allows you to productively generate pureQuery data access layer for all the SQL statements in your file.

If you are a Java developer and have .sql files from your DBA, then you can easily generate pureQuery code for all the SQL in the file within your pureQUery enabled project. You can also use this to easily migrate from existing JDBC applications or applications using frameworks to the pureQuery API. Simply export the SQL from the pureQuery outline to a .sql file in your project or open it in SQL editor. Then, simply right click on the .sql file or in an open SQL editor and select Generate pureQuery code.

Select the location of the pureQuery runtime to use in Data Studio Developer

Before Data Studio Developer 1.2, you could not easily use pureQuery runtime installed without many manual steps. Point Data Studio Developer 2.1 to your pureQuery runtime install location using the steps below.

1. From the top level Window->preferences select the Data Management -> SQL Development -> pureQuery category.
2. Provide a location where the pureQuery runtime jars are located under the pureQuery Runtime Location section.
   

Share database connection information across tools

Before Data Studio Developer 1.2, you had to create database connections separately in all your Data Studio Eclipse-based tools. Now, you can define your database definitions in one tool and share them with the others. Import shared connections and benefit from re-use!

Easily migrate pureQuery projects from Data Studio Developer 1.x or Data Studio Developer 2.1

Your existing connections from older Data Studio workspaces will be automatically migrated when the workspace is opened in Data Studio Developer 2.1. Simply select Window->Reset perspective to reset to the Data Studio Developer 2.1 perspectives. To migrate the pureQuery runtime from your existing projects, use the Upgrade project menu on your project. Refer to the technote <note pointer %gt; for more details on this process

Reduce or eliminate SQL injection risk for Java database applications

Act pro-actively to reduce SQL injection

Limit the SQL issued to the database to the known approved SQL from the captured pdqxml.

1. Executing your application using the following properties in pdq.properties. All the SQL that was run will be captured in capture.pdqxml inside the pureQueryFolder captureMode=ON executeMode=DYNAMIC pureQueryXML=pureQueryFolder/capture.pdqxml capturedOnly=false
2. When you have completed collecting all the SQL from various executions of your application, you are ready to restrict the SQL.
   Open pdq.properties and set the following properties captureMode=OFF capturedOnly=true
3. Now, only the captured SQL can be executed, either dynamically or statically.

Summary

Significantly improve developer productivity to build enterprise-ready applications. Empower the developer to create better applications and collaborate with the DBA to reduce the likelihood of critical performance issues in production. Use static SQL to reduce CPU/transaction on DB2 servers and get additional growth from existing hardware and software investments.

Table 1 summarizes the Version 2.1 feature support per database platform:

Back to top

Downloads

Information about download methods

Resources

Learn

• In the Data Studio area on developerWorks, get the resources you need to advance your skills on Data Studio.
  
  
• What's new in Data Studio 2.1 video series: Get more details about the latest features of Data Studio.
  
  
• "What’s new and exciting in IBM Data Studio Developer 1.2" (developerWorks, August 2008): Explore the features in this release that can get your job done faster and easier, including unique problem isolation and impact analysis capabilities, ability to leverage static SQL for any existing JDBC application, enhancements to static SQL development and administration, Web services, and much more
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• Download IBM Data Studio Developer 2.1 to try the scenarios described in this article.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from IBM Information Management, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Data Studio Community Space on developerWorks.
  
  
• Data Studio Team Blog on developerWorks.
  
  

About the authors

Sonali Surange is a Senior Software Engineer at the IBM Silicon Valley Lab, where she is the Team Lead for Data Studio Developer, pureQuery Tools. Previously at IBM, Sonali led the development of several key components of the Visual Studio .Net tools for IBM databases.

Zeus Courtois is an engineer in the Data Studio Development team working on the pureQuery tools at IBM's Silicon Valley Lab in San Jose, CA. Prior to joining this team, Zeus was an intern in the Data Studio Enablement team. Zeus holds an Masters degree in Information Systems from Texas A&M International University.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Give developers more information to focus SQL tuning efforts
• Improve quality of service for OpenJPA and for .Net applications
• Gain enhanced insights into your Java applications that use SQL
• Improve productivity and collaboration across developers and DBAs
• Reduce or eliminate SQL injection risk for Java database applications
• Summary
• Downloads
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Data Studio
• Demo: Use Ajax with DB2
• Briefing: Virtual tech briefings on integrated data management
• Community: DB2
• Buy: Data Studio

Dig deeper into Information Management on developerWorks

Discover knowledge paths

Skill-building guides for Information Management, Linux, open source, cloud, Java, business analytics, and more.

Special offers