Get started using IBM Data Studio Developer with Informix Dynamic Server

Create, test, and deploy IDS applications

Get an introduction to IBM® Data Studio and the IBM Data Studio Developer offering, and see how IBM Data Studio Developer 2.1 can be used to develop applications with Informix® Dynamic Server (IDS).

Share:

Pamela Siebert (psiebert@us.ibm.com), Staff Software Engineer, IBM

Author photo: Pamela SiebertPamela Siebert is a quality assurance software tester and has been working with various Informix and DB2 database server products over five years. Currently, she works with Java tools and connectivity, with a focus on drivers for IBM data servers.



Venkatesh Gopal, Senior Software Engineer, IBM

Author photo: Venkatesh GopalVenkatesh Gopal has 15 years of experience in software development and has worked in the client and server areas on both the Informix and the DB2 teams. He is currently a part of the Data Studio Development team.



09 April 2009 (First published 15 May 2008)

Also available in

Before you start

About this tutorial

In this tutorial, walk through setting up Data Studio Developer 2.1 to work with IDS. Learn how to expose database operations as Web services, see how to work with basic database objects, SQL statements, and stored procedures, and get an introduction to pureQuery, an innovative approach to building high-quality, better-performing Java database applications. This tutorial also describes the IBM Data Studio offering in some detail.

Objectives

When you've completed this tutorial, you'll:

  • Be familiar with Data Studio Developer 2.1
  • Know how to set up Data Studio Developer 2.1 to connect to IDS
  • Be able to create a Data Web Service with IDS
  • Know how to create a pureQuery application with IDS, and use some of the pureQuery features such as SQL performance statistics and heterogeneous batching

Prerequisites

Downloading the required products

The products mentioned here are available in trial versions (see Resources). You can also download Informix Dynamic Server Express Edition for free.

This tutorial assumes that you have:

  • Data Studio Developer 2.1 FP1 and WebSphere Community Edition 2.x installed. (You can get the WebSphere CE code as part of the Data Studio Developer 2.1 installation, or you can download it.)
  • At least a basic working knowledge of Eclipse-based IDEs.
  • Informix Dynamic Server 11 installed and a basic working knowledge of IDS.
  • Downloaded the sample database used in this tutorial, the GSDB database (see Download).

System requirements

To run the examples in this tutorial, you need a Windows XP system with IBM Data Studio Developer 2.1 installed, with IBM Data Server Driver for JDBC and SQLJ, and an Informix server (local or remote). Optionally, an Informix JDBC driver (Version 3.00.JC3 or later) can be used instead of IBM Data Server Driver for JDBC and SQLJ. This tutorial uses the GSDB database, which is available in the Download section.

You must use the sample database named GSDB. Follow these steps to create the database:

  • Get the zip file from the Download section of this article, and extract the files from it. You will see three files (createGSDBv1.2.bat, createGSDBv1.2.sh, and GSDBv1.2.sql) and an xml folder.
  • Bring down the IDS server if it is already up.
  • Set the DBDATE variable to Y4MD.
  • Set the SBSPACENAME value to sbspace in the onconfig file. Alternatively, you can either edit createGSDBv1.2.bat or createGSDBv1.2.sh if you want to use a different SBSPACENAME value.
  • Bring up the IDS server
  • Run createGSDBv1.2.sh or createGSDBv1.2.bat, respectively.

An introduction to IBM Data Studio for IDS

IBM Data Studio Developer 2.1 is an integrated database development environment that speeds application design, development, and deployment while increasing data access performance and manageability. It has the following capabilities for use with IDS:

  • Increases developer productivity to deliver applications faster
    • Generates a data access layer and unit test applications using Java objects, JSON, or, XML
    • Reduces code to write and maintain to lower complexity and cost over using straight JDBC, yet provides visibility to the SQL to enable tuning
    • Enables drag and drop creation of Web services for any SQL statement or stored procedure
    • Provides a seamless SQL/Java experience including SQL assistance, validation, execution, and analysis
  • Provides expert-equivalent performance for Java data access
    • Facilitates use of JDBC and SQL data access best practices, including such capabilities as heterogeneous batch, which can reduce network operations

More details can be found at:

"Base" enhancements in Data Studio Developer 2.1 for Informix

With the release of Data Studio Developer 2.1, there are many enhancements that Informix DBAs can benefit from that are not related to pureQuery or application development, including:

  • Support for UPDATE STATISTICS
    • Run update statistics on database, table, and column
    • View statistics on tables, columns, and indexes
  • Improved support for triggers
    • Create and alter triggers using Data Source Explorer
    • Enhanced General and Details tabs in the Data Object Explorer
  • Support for table fragmentation
    • Perform table partitioning in the Data Source Explorer view
    • Choose "Round-Robin" or "Expression" distribution scheme in the Data Object Editor
    • Add, delete, or edit a partition in the Data Object Editor
    • Preview DDL on Table Partitioning and execute
  • New features for Tables, Indexes, and Constraints
    • Table Management: raw table, first extent size, next extent size, lock level, DB Space
    • Index Management: DB Space for an index to be created in
    • Constraint Management: A new Mode option to specify "Enabled", "Filtering with error", "Filtering without error", or "Disable" to manage a Informix constraint (Unique, Check, or Foreign Key)
  • Informix Model Migration for InfoSphere Data Architect 7.5.1 (IDA)
    • Automatically migrate model when older physical models are imported in IDA 7.5.1

For more information about and screenshots of some of these Informix-specific features, see Guy Bowerman's blog entry.

IBM Integrated Data Management vision

The IBM Data Studio product family and the IBM Optim family are both part of a larger overall vision to create an integrated data management environment that supports the entire data lifecycle, from requirements to retirement. By focusing across the lifecycle and enabling different roles to collaborate, the goal is to increase organizational productivity and effectiveness, while improving the quality of service, cost of ownership, and governance of diverse data, databases, and data-driven applications.

Many key tools support IDS, including Data Studio Developer, and more support is planned to roll out over time. For more information about Data Studio and Integrated Data Management, see the article "IBM Data Studio software: The big picture" (developerWorks, July 2008).


Getting started with Data Studio

Launch Data Studio

Go to Start > All programs > IBM Data Studio > IBM Data Studio Developer, and click Data Studio Developer. This prompts you to pick a location for your workspace directory. Enter an appropriate location.

Connect to IDS

  1. Launch the Data Studio integrated development environment (IDE) by using the short-cut mentioned above.
  2. The IDE should be launched with the default "Data" perspective. If not, you will have to open the perspective by navigating to Window > Open Perspective > Other > Data.

    See the upper right corner where the "Data" perspective is shown in the following figure.
    Figure 1. Location of "Data" perspective
    Location of Data perspective
    (Click here to see a larger image of Figure 1.)
  3. In the bottom left-hand corner of the above figure, you should see the Data Source Explorer view. If not, the view can be enabled by navigating to Window > Show View > Data Source Explorer.

    (Note: Starting with the Data Studio Developer 2.1 release, this view is now called Data Source Explorer. For previous releases, this was called Database Explorer).
  4. Right-click on Connections to add a new connection.
    Figure 2. Creating a new connection
    Creating a new connection
  5. Set the connection parameters. Note that there are two drivers that can be used for IDS:
    • The common IBM Data Server Driver for JDBC and SQLJ that works with DB2 and IDS (in order to use this, the Informix server has to be setup to use a DRDA port)
    • the Informix JDBC driver (for Informix only, using the SQLI protocol)

    The example here uses the common IBM Data Server Driver for JDBC and SQLJ so that you can use some key features such as heterogeneous batch. For the common driver, you just need to provide the database or host for a logged database and port information, and pick the appropriate jar file. For the IDS JDBC driver, the parameters are the database name, host name, port or service name, and server name (INFORMIXSERVER).

    Figure 3. Setting up connection attributes
    Setting up connection attributes
    (Click here to see a larger image of Figure 3.)
  6. Click Finish to create the new connection.

Explore your database

Now that a connection has been added, you can navigate through your connection in the Data Source Explorer and look at your database objects by navigating through GS_DB > Schemas > schema_name> Tables.

Use the inventory_levels table in this example. The table is located in the gosales schema.

  • To look at the sample data, right-click on inventory_levels > Data > Sample Contents. The contents are shown in the bottom, right-hand corner of the screen.
  • To look at the sample DDL, right-click on the inventory_levels > Generate DDL and use defaults for the other steps.
  • To alter the table, right-click on the table, and click on Alter. This opens up the Data Object Editor, where you can add new columns, preview the SQL, and run it from the IDE.
    Figure 4. Data Object editor
    Data Object editor
  • To see a data diagram, right-click on inventory_levels > Add to Overview Diagram, and check the box next to the product table, which is under the gosales schema, and select Infer implicit relationships. The relationship will be shown between the two tables in a generated diagram (see Figures 5 and 6):
    Figure 5. Overview diagram selection
    Overview diagram selection
    Figure 6. Overview diagram
    Overview diagram

Creating a new table

Tip for writing stored procedures

The default statement terminator in the SQL editor is a semicolon (;). In other words, each line in a stored procedure that ends in a semicolon is treated as a single SQL statement. To change this behavior, you can change the default statement terminator by selecting the top menu bar option Window > Preferences > SQL Development > SQL and XQuery Editor.

You can also explore your other database objects using the Data Object Editor, such as views and procedures, and you can create new objects on the server using this mechanism.

Right-click Tables > Create > Table in Data Source Explorer to launch a wizard, and you can use the default steps. In the table creation, Preview DDL shows the generated DDL, then you can run the DDL from within the workbench.

Figure 7 shows the table created using Data Object Editor through generated DDL. Notice the generated DDL in the Preview screen.

Figure 7. New table
New table

Create Web services with IDS

One of the features of Data Studio is the ability to create Data Web Services. You can expose your database operations (invocation of a stored procedure, select, insert, or update statements) easily as Web services with a few clicks of a button, and then Data Studio takes care of creating the Web application for you. It can create both SOAP and 'REST'ful services for you. You can then deploy the Web application (that includes the Web service classes and the WSDL file) on a Web server, and you are done.

You need to create a new project first. Perform the following the steps:

  1. Go to File > New > Project > Data Development Project.
  2. Name the project ifmxproject, and click Next.
  3. Use the connection name that you created for your Informix database.
  4. Answer Yes to Open Associated Perspective.

Now you are ready to create Web services. Perform the following steps:

  1. In the Data Project Explorer, select the project that you have just created.
  2. Right-click on Web Services, and select New Web Service.
  3. Name the Web service ifmxservice, and click Finish.
  4. Right-click on the Web Service, and click New Operation.
  5. Type in an SQL statement to select three fields from the cust table, as shown in Figure 8:
    Figure 8. Creating an operation
    Creating an operation
  6. Select Validate to confirm that the statement is valid. Leave the name as "Operation1".
  7. Pick the Web service ifmxservice from the previous step, and then click Build and Deploy.
  8. This tutorial uses WebSphere Application Server Community Edition 2.1. The steps in the next section list the configuration steps for WebSphere Application Server Community Edition 2.1. This example also uses the REST scenario, instead of SOAP.
  9. Click on Finish. This creates all the necessary components for you.
    Figure 9. Deploying a Web service
    Deploying a Web service

Configure WebSphere Application Server Community Edition

A video on configuring WebSphere Application Server Community Edition

Refer to this video to see how to set up WebSphere Application Server Community Edition in one step.

This section includes a set of steps for how to configure WebSphere Application Server Community Edition 2.1 to make it work with Informix and then get the Web service running.

  1. The installer comes with Data Studio Developer 2.1 when the option is enabled. It is found in the <install dir>/dsdev/bin directory. Run the installer.
  2. After installing the server, bring up the server by selecting IBM WebSphere > Application Server Community Edition > Start the server.
  3. Open a web browser and type in the default url: http://127.0.0.1:8080/console/portal
  4. The default username/password to log in: system/manager.
    Figure 10. WebSphere Application Server Community Edition Administration Console
    WebSphere Application Server Community Edition Administration Console
    Follow the steps below if you do not want the workbench to register the database connection with the Web server.
    1. Since IBM Data Server Driver for JDBC and SQLJ is already registered, you have an option to add Informix JDBC Driver if you desire to use that driver instead. Add the ifxjdbc.jar file to the WebSphere Application Server Community Edition repository. In order to do this in the Administrative Console, select Console Navigation > Services > Repository.
    2. In the Repository Viewer, fill in the fields, and click Install. Use the following values for the fields:
      1. Pick up ifxjdbc.jar from the appropriate location.
      2. In the Group field, specify com.informix.jdbc.
      3. In the Artifact field, specify ifxjdbc.
      4. In the Version field, specify 11.0.
      5. In the Type field, specify jar.
        Figure 11. Driver JAR file
        Driver JAR file
    3. Create an IDS database pool by using the Geronimo database pool wizard. Each Web service that you deploy on the server must use a unique database pool.
    4. Name the database pool and select Informix as the database type.
    5. Select the following driver JAR file: com.informix.jdbc/ifxjdbc/11.0/jar.
    6. The next step is to set up the database pool connection. Select Database Pools under Services folder on the left screen.
    7. Create a new database pool by clicking on Using the Geronimo database pool wizard.
    8. Specify the name of the database pool and database type, then click on Next.
    9. Fill in the following fields (as illustrated in Figure 12):
      • JDBC Driver Class:com.informix.jdbc.IfxDriver
      • Driver Jar:com.informix.jdbc/ifxjdbc/11/jar
      • DB User Name: the username used to connect to the database
      • DB Password: <DB password>
      • Confirm Password: <confirm password>
      • Port: <The server port>
      • dbservername: <The database server name>
      • Database:gsdb
      • Host: <The host name>
      Figure 12. Parameters
      Parameters
      (Click here to see a larger image of Figure 12.)
    10. After pressing Next, verify that the JDBC Connect URL is valid.
    11. Optionally, you can specify values for parameters of the connection pool.
  5. Record the value of the groupID and artifactID parameters for the database connection pool. You must specify these values when you deploy the Web service that uses this database connection pool. The values of the groupID and artifactID parameters are case-sensitive. In this example, the groupID is console.dbpool and the artifactID is gs_db.
  6. Click Finish and deploy, and make sure you can connect to your database.

Ensure the geronimo-web.xml file generated by the Data Studio has the matching information in the highlighted portion.

Listing 1.
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-2.0.1" 
xmlns:naming="http://geronimo.apache.org/xml/ns/naming-1.2" 
xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.2">
  <dep:environment>
    <dep:dependencies>
      <dep:dependency>
        <dep:groupId>console.dbpool</dep:groupId><dep:artifactId>GSDB</dep:artifactId>
        <dep:version>1.0</dep:version>
        <dep:type>rar</dep:type>
      </dep:dependency>
    </dep:dependencies>
  </dep:environment>
  <context-root>ifmxprojectifmxservice</context-root>
  <naming:resource-ref>
    <naming:ref-name>jdbc/ifmxservice</naming:ref-name>
    <naming:pattern>
      <naming:groupId>console.dbpool</naming:groupId><naming:artifactId>GSDB</naming:artifactId>
      <naming:name>GSDB</naming:name>
    </naming:pattern>
  </naming:resource-ref>
</web-app>

Create the Web archive file (WAR file) to be deployed to the Web server. To do this, complete the following steps:

  1. Switch to the Java perspective by going to Window > Open Perspective > Java.
  2. For your Web service ifmxservice and your project ifmxproject, there is a Web project called "ifmxprojectifmxserviceWeb" that has been created.
  3. Generate the WAR File by clicking on the project, then right-click and select Export.
  4. Select WAR file, and then click Next.
    Figure 13. Export into WAR file
    Export into WAR file
  5. Create the file in an appropriate location to be used later in the WebSphere Application Server Community Edition deployment panel.
    Figure 14. Export to a local system
    Export to a local system

Now, switch back to the Administration Console and deploy the WAR file by performing the following steps:

  1. Log back in to the Administration Console.
  2. Go to Applications > Deploy New, and pick the WAR file created earlier.
    Figure 15. Deploy WAR file
    Deploy WAR file

If you have used all of the defaults as mentioned in the tutorial, use the following URL in a browser window to look at the service in action: http://localhost:8080/ifmxprojectifmxservice/rest/ifmxservice/Operation1/. If your Web applications port is not 8080, you may have to change the port to match your environment.


pureQuery with IDS

pureQuery provides a high-performance data access platform that makes it easier to develop, optimize, secure, and manage data access. pureQuery consists of:

  • Java application programming interfaces (APIs) built for ease of use and for simplifying the use of best practices for enhanced database performance when using Java
  • A runtime that provides optimized and secure database access
  • An Eclipse-based integrated database development environment, Data Studio Developer, for enhancing development productivity

pureQuery offers many advantages, including:

  • Bridging the gap between data and Java technology by harnessing the power of SQL within an easy-to-use Java data access platform
  • Improving problem isolation and resolution capabilities by correlating Java data access with problem application code
  • Deploying applications to query in-memory collections and databases using a single API
  • Reducing or preventing SQL injection risk for Java database applications
  • Mapping relational data to Java objects using pureQuery beans
  • Facilitating and encouraging use of Java development best practices

For more information on pureQuery, see Resources.

Creating a pureQuery application with IDS

In this section, learn how to generate a pureQuery data access layer and test code from existing tables. This section also provides a sample java application so you can try it out.

  1. Switch to the Java perspective and create a new Java project titled "pureQueryTutorial".
  2. Go to the Data Perspective's Data Source Explorer view. Optionally, you can add the Data Source Explorer View in the Java perspective, instead of switching between the Data and Java perspectives.
  3. Pick up your IDS connection, navigate to the table product, right-click, and select Generate pureQuery code.
    Figure 16. Data Source Explorer view
    Data Source Explorer view
  4. Browse to select an existing source folder under pureQueryTutorial project.
  5. Enter pureQueryCode for package name.

    Notice that in Figure 16, "Generate annotated-method interface for table" is selected. pureQuery offers two different method styles: inline-style and annotated method style.

    pureQuery inline-style

    • Includes a complete set of Java methods for executing queries and update operations
    • The methods take an SQL statement and its parameters as input and return results

    pureQuery annotated method style

    • Provides data accessor and update methods
    • The methods are declared in a user-created Java interface using annotations that express the specific query or update operations in standard SQL
    • Automatically creates implementation of specified methods using Java annotated class definitions
    • Separate data access declaration and associated SQL from application's business logic

    For more information about the different styles, see the Write high performance Java data access applications article series.

    This tutorial uses the annotated method style. Select Next.

    Figure 17. "Generate pureQuery code from a table" wizard
    'Generate pureQuery code from a table' wizard
  6. The next screen (Figure 18) allows you to generate test classes. Select both annotated method and inline-style test classes so you can view both to see the differences. Optionally, select Include connection information in test, then select Next.
    Figure 18. "Generate pureQuery code from a table" wizard, part 2
    'Generate pureQuery code from a table' wizard part 2
  7. The next screen allows you to map the columns to the bean field. Use the drop-down menu option to select Timestamp as the Field Type for both introduction_date and discontinued_date columns, and click on Next.
    Figure 19. "Generate pureQuery code from a table" wizard, part 3
    'Generate pureQuery code from a table' wizard part 3
  8. Select Generate all SQL statements, then select Finish.
    Figure 20. "Generate pureQuery code from a table" wizard, part 4
    'Generate pureQuery code from a table' wizard part 4

    As the wizard completes, several classes are created:

    • Product.java: A java file containing a one to one mapping from the data in the Product table to the Java object.
    • ProductData.java: An interface containing the abstraction of the data access layer for the querying of data or data manipulation.
    • ProductDataImpl.java: An implementation class of the interface created above
    • ProductDataTest.java: A test class demonstrating annotated-method style
    • ProductInlineSample.java: A test class demonstrating inline-style
    Figure 21. Classes created for Product
    Classes created for Product

This creates two classes for you in the package "querypackage" called Product.java and ProductData.java. You do not need to modify any of the generated files. The code should run as it is.

To read information from the Product table, you can run ProductDataTest.java or ProductInlineSample.java.

You can also write a sample application similar to the following (using your own connection URL where "xxx" is the username you are connecting as and "yyy" is the password). The file is named GetProduct.java (code details shown in Listing 2).

To run GetProduct.java, right-click the file, and click on Run as a Java Application.

Listing 2. Retrieving data
package pureQuery.example;

import java.sql.*;
import java.util.Iterator;
import pureQueryCode.*;
import com.ibm.pdq.runtime.Data;
import com.ibm.pdq.runtime.factory.DataFactory;

public class GetProduct {
	public static void main(String[] args)
	{
	try
	{
	Class.forName ("com.ibm.db2.jcc.DB2Driver");
	Connection con = DriverManager.getConnection("jdbc:ids://" +
	"localhost:9089/gsdb:" +
	"DELIMIDENT=y;","xxx","yyy");
        ProductData prodData = 
	     DataFactory.getData( ProductData.class, con ); 
        
	 Product p;

	// Uses the default SQL - select * from product

	 Iterator < Product > prodList = prodData.getProducts();
	 while (prodList.hasNext()){                          
	    p = prodList.next();
	    System.out.println("The product number is " 
	    		+ p.getBase_product_number() +
	    		" and its image is titled " + p.getProduct_image() + 
	    " and size code of: " + p.getProduct_size_code());
	 }

	System.out.println("New List");

	//Customizing the query executed
	Data db = DataFactory.getData(con);
	prodList = db.queryIterator("select * from gosales.product " +
			"where product_number <4000",Product.class);


	 	while (prodList.hasNext()){                          
		    p = prodList.next();
		    System.out.println("Product num " + p.getProduct_number()
		    + "'s base product number is:"
		    + p.getBase_product_number() 
		    + "is introduced on " + p.getIntroduction_date());
		 }
	 
	}
	
	catch(Exception e)
	{
		System.out.println(e.getMessage());
	}
	
	}
        
}

The above example shows how easy it is for Java developers to create a simple database application without having to write any SQL. You can also see that the query can be customized by giving an explicit query with the queryIterator method.

Heterogeneous batch updates with IDS

In the release of Data Studio Developer 2.1, IBM Data Studio pureQuery Runtime, Version 2.1 is included. pureQuery introduces Informix support for heterogeneous batch updates, where a batch operation can include several API calls and even span across multiple data access objects. With heterogeneous batch operation, several tables can be updated in a single network call. Heterogeneous batching is only supported by IBM Data Server Driver for JDBC and SQLJ.

Copy and paste the code from Listing 3 to take advantage of heterogeneous batch updates. The file is named HeteroBatch.java. Again, you need to edit "xxx" for username and "yyy" for password.

Listing 3. Heterogeneous batch code
package pureQuery.example;

import java.util.*;
import java.sql.*;
import com.ibm.pdq.runtime.*;
import com.ibm.pdq.runtime.factory.DataFactory;

public class HeteroBatch {

	public static void main(String[] args) {
		try
		{
			String url = "jdbc:ids://localhost:9089/gsdb:" + 
			"traceFile=C:\\temp\\myjcctrace.txt;traceLevel=-1;";
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
			Connection con = DriverManager.getConnection(url,"xxx","yyy");

			Data db = DataFactory.getData(con);


			String strSQL1 = "UPDATE GOSALESCT.CUST_ORD_DETL"
				+ "  SET PROD_QTY = PROD_QTY + 1"
				+ "  WHERE ORD_DETL_CODE = 1089";

			String strSQL2 = "UPDATE GOSALESCT.CUST_ORD"
				+ "  SET ORD_NBR_OF_ITEMS  = ORD_NBR_OF_ITEMS + 1"
				+ "  WHERE ORD_NBR = 100012";

			Map<String, Object> quantity = db.queryFirst(
					  "  SELECT ORD_NBR_OF_ITEMS"
					+ "  FROM GOSALESCT.CUST_ORD"
					+ "  WHERE ORD_NBR = ?", 100012);
			System.out
			.println("QUANTITY OF ITEMS in CUST_ORD BEFORE UPDATE : "
					+ quantity.get("ord_nbr_of_items"));

			quantity = db.queryFirst("SELECT PROD_QTY"
					+ "  FROM GOSALESCT.CUST_ORD_DETL"
					+ "  WHERE ORD_DETL_CODE = ?", 1089);
			System.out
			.println("QUANTITY OF PRODUCT in CUST_ORD_DETL BEFORE UPDATE : "
					+ quantity.get("prod_qty"));

			((Data) db).startBatch(HeterogeneousBatchKind.
							heterogeneousModify__);
			Object[] empty = new Object[0];
			db.update(strSQL1, empty);
			db.update(strSQL2, empty);
			((Data) db).endBatch();
			db.commit();

			quantity = db.queryFirst("SELECT ORD_NBR_OF_ITEMS"
					+ "  FROM GOSALESCT.CUST_ORD"
					+ "  WHERE ORD_NBR = ?", 100012);
			System.out
			.println("QUANTITY OF PRODUCT in CUST_ORD AFTER UPDATE : "
					+ quantity.get("ord_nbr_of_items"));

			quantity = db.queryFirst("SELECT PROD_QTY"
					+ "  FROM GOSALESCT.CUST_ORD_DETL"
					+ "  WHERE ORD_DETL_CODE = ?", 1089);
			System.out
			.println("QUANTITY OF ITEMS in CUST_ORD_DETL AFTER UPDATE : "
					+ quantity.get("prod_qty"));
		}
		catch (SQLException e)
		{
			System.out.println("SQL Error in the application: " +
					e.getErrorCode() + " " +
					e.getMessage());
		}
		catch (Exception e)
		{
			System.out.println("Error in the application: " +
					e.getMessage());
		}
	}
}

In the above code, both tables CUST_ORD and CUST_ORD_DETL are updated in a heterogeneous batch.

Besides using UPDATE statements in heterogeneous batch, you can use INSERT and DELETE statements. Parameters are also supported. For more information on using parameters in a heterogeneous batch, go to the "Batch heterogeneous updates with parameter" section of the Integrated Data Management Information Center (see Resources).

Also, notice that the queryFirst method is used. It is used to return the first row from the resultset. This method should be used only if you wish to retrieve one row from the resultset.

SQL Capture with pureQuery

While developing your data access application, you can also view the performance of the queries executed. In order to do this, you need to run the application with pureQuery and enable SQL capturing.

If the SQL capturing is not enabled, right-click on the Java project and select Properties.

Figure 22. Editing pureQuery support
Editing pureQuery support

Select pureQuery on the left side. Check the box next to "Enable SQL capturing and binding for JDBC applications", and click on OK.

Figure 23. "Add pureQuery support" wizard
Add pureQuery support wizard

(Click here to see a larger image of Figure 23.)

Now you need to run HeteroBatch in pureQuery. Right-click on HeteroBatch.java, and select Run Configurations... under the "Run As" option.

Figure 24. Run configuration
Run configuration

In the "Run Configurations" wizard, you need to expand pureQuery on the left column, click on HeteroBatch, and run.

Figure 25. "Run Configurations" wizard
'Run Configurations' wizard

(Click here to see a larger image of Figure 25.)

Open pureQuery Outline and click on the Toggle Profile icon on the upper right corner.

Expand the tables to see the performance results.

Figure 26. pureQuery performance results
pureQuery performance results

(Click here to see a larger image of Figure 26.)

You will be able to see results in metrics, such as number of times run, total time, maximum time, average time, and minimum time.

You can also view and edit the captured SQL statements. In the Package Explorer, locate and open capture.pdqxml.

Figure 27. capture.pdqxml location
capture.pdqxml location

Select an SQL statement and right-click on it to edit the statement.

Figure 28. Editing an SQL statement in capture.pdqxml
Editing an SQL statement in capture.pdqxml

A window pops up asking you to edit the schema and path. Select a schema and click on OK.

Figure 29. Editing the schema and path
Editing the schema and path

The new SQL statement will appear below the original one. Edit UPDATE GOSALESCT.CUST_ORD_DETL SET PROD_QTY = PROD_QTY + 1 WHERE ORD_DETL_CODE = 1089 by replacing 1 with 3.

Figure 30. Editing an SQL statement
Editing an SQL statement

Click on Save.

You now need to turn off the capture mode and set the enableDynamicSQLReplacement property to true in pdq.properties, which is located under pureQueryTutorial project. (See Figure 31.)

Figure 31. Setting enableDynamicSQLReplacement property to true and the updated result
Setting enableDynamicSQLReplacement property to true and the updated result

(Click here to see a larger image of Figure 31.)

Click on Save again and rerun the application. Notice that the PROD_QTY column is incremented by 3, instead of by 1.

This feature is useful if you want to edit an SQL statement to improve performance, but cannot change the application code. This is typical in the case of third-party applications or for applications where you do not have the source code. You can view the performance metrics again in order to determine if there is any performance change. For more information on adding alternate SQL statements to pureQueryXML files, see the Integrated Data Management Information Center (see Resources).

pureQuery documentation can also be found in the Integrated Data Management Information Center (see Resources).

You can also check out a four-part video series on the new features in Data Studio Developer 2.1, which talks about identifying SQL statements that take the longest, changing SQL statements without touching the code, improving productivity and collaboration among developers and DBAs, enhanced pureQuery Outline, and eliminating SQL injection risks (see Resources).


Conclusion

This tutorial has discussed just a few aspects of the Data Studio database development capabilities as they pertain to IDS, including Web services creation and pureQuery for Java data access.


Download

DescriptionNameSize
Code sampleGSDBv1.2_ids.zip915KB

Resources

Learn

Get products and technologies

Discuss

Comments

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


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=381189
ArticleTitle=Get started using IBM Data Studio Developer with Informix Dynamic Server
publish-date=04092009