Use IBM Cloudscape with WebSphere Application Server Community Edition

最新推荐文章于 2025-06-07 19:24:53 发布

nomad2

最新推荐文章于 2025-06-07 19:24:53 发布

阅读量2.7k

点赞数

分类专栏： J2EE 文章标签： websphere application ibm server database derby

J2EE 专栏收录该内容

6 篇文章

订阅专栏

Level: Introductory

Nell Gawor (mailto:ngawor@us.ibm.com?subject=Use IBM Cloudscape with WebSphere Application Server Community Edition&cc=ngawor@us.ibm.com), Advisory Software Engineer, IBM
Lin Sun (mailto:linsun@us.ibm.com?subject=Use IBM Cloudscape with WebSphere Application Server Community Edition), Staff Software Engineer, IBM

09 Feb 2006

Learn how to configure IBM Cloudscape™, use ij to work with the built-in database, select a resource adapter, configure and deploy a database connection pool, and configure a remote IBM Cloudscape database. This article demonstrates how to use IBM Cloudscape in WebSphere® Application Server Community Edition.

Introduction

IBM WebSphere Application Server Community Edition (Community Edition) includes Derby for its own use (see the sidebar for more information about the difference between Derby and IBM Cloudscape). Your Java 2 Platform, Enterprise Edition (J2EE)™ applications can also leverage this Derby, which we will refer to as the built-in Derby from now on. Rather than installing and configuring an external database, you can store your application data within the built-in Derby.

This article will show you how to configure the built-in Derby and how to create your own databases and populate them with data. You will learn how to configure datasources and database connection pools to connect your application with the data and how to choose the right resource adapter for the connection. Lastly, this article covers how to connect your J2EE applications running within Community Edition with remote Derby Network Servers if the built-in database engine does not meet your needs.

The difference between Derby and IBM Cloudscape

There is no difference between Derby and IBM Cloudscape in terms of functionality. They are, in fact, the same database engine. The difference is that IBM Cloudscape adds additional features, including support, documentation, installers, and samples. This article uses the term Derby except when referring specifically to IBM's software bundle.

Configure Derby in Community Edition

Directories and files related to Derby

There are two GBeans related to Derby in Community Edition, one is the DerbySystemGBean that represents an instance of an Apache Derby system (a system being a collection of different databases). The other is the DerbyNetworkGBean that manages remote network access to the built-in Derby server. Besides the DerbySystemGBean and DerbyNetworkGBean, the following are other JAR files related to Derby.

Derby-related JAR files: wasce_home/repository/org.apache.derby/jars contains all the Derby JAR files, where wasce_home is the Community Edition installation directory.
var/derby directory: This is the Derby System directory (derby.system.home). It contains derby.log and the SystemDatabase directory by default. If you create a new database, a directory with the name of the new database will be created.
system-database-plan.xml: Contains the configuration of the org/apache/geronimo/SystemDatabase module, which includes the configuration of the DerbySystemGBean and DerbyNetworkGBean, as well as the SystemDatabase resource adapter configuration.

Use config.xml to change the Derby Network Server listening port

By default, the Derby Network Server listens on port 1527 when Community Edition starts. You will want to change the default port if there is a port conflict. Change the SystemDatabase configuration in config.xml. The following example demonstrates how to change the port to 1528.

Listing 1: Configure SysteDatabase in config.xml


			
...
<configuration name="org/apache/geronimo/SystemDatabase">
    <gbean name="DerbyNetwork">
        <attribute name="host">0.0.0.0</attribute>
        <attribute name="port">1528</attribute>
    </gbean>
</configuration>
...

After changing config.xml, you will need to restart the server for your changes to take effect. If you want to use the ij scripts provided by Community Edition, you will also need to change the IJ_PORT to the port you specified above in the wasce_home/bin/ij.bat and ij.sh files.

Use derby.properties

For further control of the Derby database engine operation, place a derby.properties file in the wasce_home/var/derby directory where wasce_home is the Community Edition installation directory. The following is a sample of a derby.properties file that will turn on statement logging to the derby.log file. When this property is set to true, Derby writes the text and parameter values of all executed statements to the information log. It also writes information about commits and rollbacks, including the time and thread number. This property is useful for debugging.

Listing 2: Derby.properties sample


		
derby.language.logStatementText=true

For more information on how to configure derby.properties, check out the Derby Properties page hosted by Apache Derby.

Back to top

Use ij to work with the built-in database

To create, view, and manipulate data within the Derby database, you can use the ij tool, Derby's interactive SQL scripting tool. This tool can be used with any Derby database, including the one that is built-in to Community Edition. Using ij, you can access a number of JDBC™ features, including the ability to view existing databases and tables, create and delete these resources, and populate them with data. Because the administrative console is a "Technical Preview", ij is the only supported way to work with the built-in Derby.

As a convenience, Community Edition provides scripts to set up the ij environment correctly in both Windows® and Linux® environments before starting the tool. Among other things, these scripts make sure that the classpath contains all the necessary Derby JAR files. Because these scripts connect to the Derby system using the Network Server, Community Edition must be running in order to access the built-in Derby. To use these scripts to run ij, run the following command:

ij.[bat|sh]

Once you've started ij, you will see a prompt where you can enter commands. You can also provide a file containing commands to execute as an argument if you do not want to execute ij in an interactive mode.

Populate data

You can use ij to create databases and tables and populate them with data. Any commands you execute are run against the database you are connected to, so nothing can happen until you are connected to a database. If you are creating a new database, you can create it and connect to it at the same time by executing the following command:

CONNECT 'name_of_new_db;create=true';

Once connected to a new or existing database you can create tables using standard SQL syntax. For example, the employee demo creates a new table to contain employee information with the following command:

CREATE TABLE EMP
  (EMPNO NUMERIC(4) PRIMARY KEY NOT NULL,
   ENAME VARCHAR(10), JOB VARCHAR(9),
   MGR NUMERIC(4), 
   SAL NUMERIC(7, 2), COMM NUMERIC(7, 2),
   DEPTNO NUMERIC(2));

Rather than entering each command separately, you can also execute a .ddl file or other file containing commands that ij understands. The empDemo sample, for example, contains a .ddl file that creates the tables and indexes that it needs. To execute it, you would issue the following command:

RUN 'samples_path/samples/emdemo/src/sql/emp_table_and_record_create.ddl';

This has the advantage of being easy to replicate each time the application is deployed to a new server against a new database.

Verify data

You can also use ij to examine existing data. To list all the user tables within a database, execute the following command:


SELECT tablename from sys.systables where tabletype = 'T';

To view the data within a table, you can use SELECT commands as well. For instance,


SELECT * FROM EMP WHERE ENAME='Smith';

To disconnect from the database you are working with, use the DISCONNECT command. To shut down ij completely, use EXIT.

This is just a brief sample of the SQL commands you can use with ij. For a more detailed listing, see the IBM Cloudscape InfoCenter (See Resources).

Back to top

Build the database configuration plan and deploy the database connection pool

Select the right Derby resource adapter for your application

Community Edition provides the following four Derby resource adapters:

tranql-connector-derby-embed-local-1.0.rar: TranQL Embedded Local Resource Adapter for Derby. The resource adaptor provides access to an embedded Derby database with local transaction support.
tranql-connector-derby-embed-xa-1.0.rar: TranQL Embedded XA Resource Adapter for Derby. The resource adaptor provides access to an embedded Derby database with XA transaction support.
tranql-connector-derby-client-local-1.0.rar: TranQL Client Local Transaction Resource Adapter for Derby. The resource adapter provides access to a remote Derby database with local transaction support.
tranql-connector-derby-client-xa-1.0.rar: TranQL Client XA Transaction Resource Adapter for Derby. The resource adapter provides access to a remote Derby database with XA transaction support.

If you want to use the built-in Derby database that is shipped with Community Edition, you can use tranql-connector-derby-embed-xa-1.0.rar for XA transaction support or tranql-connector-derby-embed-local-1.0.rar for local transaction support. If you want to use a remote Derby database that is set up on a different machine, you can use tranql-connector-derby-client-xa-1.0.rar for XA transaction support or tranql-connector-derby-client-local-1.0.rar for local transaction support. For details on how to use the latter two resource adapters, see the section on configuring a remote Derby database below.

Build the database configuration plan

Select the database connection pool type. There are three database connection pool types available: Server-wide, Application Scoped, Module Scoped. If you want the database connection pool available to all the applications running on the server, choose Server-wide. If you want the database connection pool available to one or more modules of the sample application, choose Application Scoped. If you want the database connection pool available to one specific module in the application, choose Module Scoped.

Build a database connection pool deployment plan. No matter what database configuration pool type you decide to use, you will need to build a database connection pool deployment plan. In the database connection pool deployment plan, you can specify the datasource configuration ID, its parent, dependencies, and resource adapter configuration information such as the datasource name, and the pool minimum and maximum size. By using the database connection pool deployment plan provided by the EMPDemo sample, you will understand how to modify this plan to build your own database deployment plan.

Define the datasource configuration ID and parent ID. the configId (Listing 3), has to be unique to the Community Edition server instance. If the parent (value of the parentId) is not running when the datasource is deployed, the deployer will start the parent first to make sure it is available.
Define the dependency elements. The dependency elements define all the datasource dependencies, and their relative paths to the Community Edition repository (Listing 3). If you want to connect to a remote Derby Network Server, you will also need to include the derbyclient-10.1.ibm.jar as a dependency.
Configure the resource adapter. The resource adapter configuration for Derby is very straightforward and you don't have to enter any JDBC driver information. If you set the CreateDatabase property to true, the database will be created for you when you deploy the database pool. If you look at the example below, you just need to modify the name, UserName, Password, DatabaseName, CreateDatabase elements and configure your database connection pool in order to build your own resource adapter configuration for Derby (Listing 3). The name element defines the name that other modules will use to refer to this connection pool. It has to be unique within the database connection pool scope you define. For example, jdbc/EmployeeDatasource has to be unique on a server-wide scope since the configuration is for a server-wide database connection pool type.

The max-size element defines the maximum number of concurrent connections that will be allowed; the min-size element defines the minimum number of concurrent connections that will be allowed; blocking-timeout-milliseconds defines how many milliseconds the call will wait when getting a connection from the pool before it gives up; the idle-timeout-minutes element defines how many minutes the connection can be idle before it is closed and removed from the pool.

You may also be wondering why the global-jndi-name element is commented out. This is because it is used when trying to connect to the datasource from a J2EE application client. There are no J2EE application clients in the EMPDemo sample. For more details regarding the Derby resource adapters, check out the ra.xml files in the tranql-connector-derby-client-local-1.0.rar, tranql-connector-derby-client-xa-1.0.rar, tranql-connector-derby-embed-local-1.0.rar, and tranql-connector-derby-embed-xa-1.0.rar files.

The following is the EMPDemo Derby database connection pool deployment plan provided by Community Edition as a sample. This is a server-wide database connection pool.

Listing 3: EMPDemo Derby database connection pool deployment plan



<?xml version="1.0"?>
<connector xmlns="http://geronimo.apache.org/xml/ns/j2ee/connector-1.0"
  configId="EmployeeDatasource"      
  parentId="org/apache/geronimo/Server">
    
  <dependency>
      <uri>org.apache.derby/jars/derby-10.1.ibm.jar</uri>
  </dependency>
  <dependency>
       <uri>org.apache.derby/jars/derbynet-10.1.ibm.jar</uri>
  </dependency>
    
  <resourceadapter>
      <outbound-resourceadapter>
          <connection-definition>
              <connectionfactory-interface>javax.sql.DataSource
              </connectionfactory-interface>
              <connectiondefinition-instance>
                  <name>jdbc/EmployeeDatasource</name>
                  <config-property-setting name="UserName">
                  </config-property-setting>
                  <config-property-setting name="Password">
                  </config-property-setting>
                  <config-property-setting name="DatabaseName">Employee
                  </config-property-setting>
                  <config-property-setting name="CreateDatabase">true
                  </config-property-setting>
                  <connectionmanager>
                      <xa-transaction>
                          <transaction-caching/>
                      </xa-transaction>
                      <single-pool>
                          <max-size>5</max-size>
                          <min-size>0</min-size>
                          <blocking-timeout-milliseconds>5000
                          </blocking-timeout-milliseconds>
                          <idle-timeout-minutes>15</idle-timeout-minutes>
                          <match-one/>
                      </single-pool>
                  </connectionmanager>
              <!--global-jndi-name>EmployeeDatasource</global-jndi-name-->
              </connectiondefinition-instance>
          </connection-definition>
      </outbound-resourceadapter>
  </resourceadapter>
</connector>

If you want to create the Module Scoped database connection pool type, you will need specify the above database connection pool deployment plan within an ext-module element like below:

Listing 4: Modify EMPDemo Derby database connection pool deployment plan to Module Scoped Datasource


			
		
<ext-module>
    <connector>EmployeeDatasource</connector>
    <external-path>tranql/rars/tranql-connector-derby-local-xa-1.0.rar</external-path>
	<!--Listing 3 connector element goes here-->
</ext-module>

Deploy the database connection pool

You have learned how to build the database deployment plan and what resource adapters you should use. Let's walk through how to deploy a Derby database connection pool in Community Edition server using the deployer tool from the bin directory (Listing 5).

Listing 5: Deploy the EMPDemo Derby database connection pool


	            
deploy.[bat|sh] --user system --password manager deploy /
	../repository/tranql/rars/tranql-connector-derby-embed-xa-1.0.rar /
	Cloudscape-db-plan.xml

In the command window that runs the deployment command you will see output indicating if the database connection pool has been deployed successfully. Replace the administrator user name and password as needed. If you want to use other Derby resource adapters, just replace the RAR file provided above with your choice. If you want to deploy a Module Scoped database connection pool type, the datasource will be deployed when you deploy the application; therefore, you don't have to issue the above command to deploy the datasource separately.

Modify the database connection pool

The deployment tool doesn't provide a modify command. In order to modify a database connection pool, you will need to undeploy the database connection pool (Listing 6) and deploy it

Listing 6: Undeploy the EMPDemo Derby database connection pool


	            
deploy.[bat|sh] --user system --password manager undeploy EmployeeDatasource

In the command window that runs the deployment command you will see output indicating if the database connection pool has been undeployed successfully. You can then deploy the database connection pool with the modified database deployment plan (Listing 5).

Back to top

Work with a remote Derby database

In most cases the Derby database built in to Community Edition will be sufficient for your needs. However, if you are already storing the data your application needs within a Derby database on a remote machine, you can use that instead. Community Edition will continue to use the built-in Derby for its own SystemDatabase. You can even easily have some applications using the built-in Derby and others leveraging remote databases.

Assuming the remote database you want to use is already set up and an instance of the Derby Network Server is running on the machine hosting the database, you will need to deploy a datasource that uses one of the TranQL client resource adapters (as opposed to the embedded resource adapters). You can use either tranql-connector-derby-client-local-1.0.rar or tranql-connector-derby-client-xa-1.0.rar, depending on the type of transaction support your application requires.

To illustrate how to use a remote Derby database, you will modify the EMPDemo sample from the previous section to use a remote Derby Network Server rather than the Derby built-in to Community Edition. These changes are all to the deployment plan shown above in Listing 3.

The first thing that needs to change is the dependencies. In addition to derby-10.1.ibm.jar and derbynet-10.1.ibm.jar, a resource adapter for an external Derby database must also depend on derbyclient-10.1.ibm.jar. This new JAR file contains the client-side libraries needed to communicate with the Derby Network Server running on a remote machine. Listing 7 shows the new dependencies.

Listing 7: Dependencies for accessing a remote database


        
<dependency>
  <uri>org.apache.derby/jars/derby-10.1.ibm.jar</uri> 
</dependency>
<dependency>
  <uri>org.apache.derby/jars/derbynet-10.1.ibm.jar</uri> 
</dependency>
<dependency>
  <uri>org.apache.derby/jars/derbyclient-10.1.ibm.jar</uri> 
</dependency>

After adding these new dependencies, the next thing to do is to add the additional configuration properties that specify the connection information, including the host and port of the Derby Network Server, as well as the username and password (Listing 8). By default, this server is running on port 1527.

Listing 8: Location of the Derby Network Server


        
<config-property-setting name="ServerName">hostname.ibm.com</config-property-setting> 
<config-property-setting name="PortNumber">1527</config-property-setting>  
<config-property-setting name="UserName">APP</config-property-setting> 
<config-property-setting name="Password">APP</config-property-setting>

When deploying the datasource server-wide, specify one of client resource adapters (Listing 9).

Listing 9: Deploying a server-wide datasource


	
deploy.[bat|sh] --user system --password manager deploy /
../repository/tranql/rars/tranql-connector-derby-client-xa-1.0.rar Cloudscape-db-plan.xml

Or, if you are deploying this datasource as part of an application or module you will need to reference one of the client resource adapters in the ext-module element (Listing 10).

Listing 10: ext-module element referencing a client resource adapter


	
<ext-module>
    <connector>EmployeeDatasource</connector>
    <external-path>tranql/rars/tranql-connector-derby-client-xa-1.0.rar</external-path>
	<!--Listing 3 connector element goes here-->
</ext-module>

Back to top

Conclusion

Go ahead and use IBM Cloudscape in WebSphere Application Server Community Edition now -- and have fun!

Back to top

Resources

Learn

Get products and technologies

Discuss

Participate in the discussion forum.
developerWorks blogs: Get involved in the developerWorks community.

Back to top

About the authors


		Nell Gawor is an advisory software engineer for IBM in Research Triangle Park, North Carolina, in the Software Group System House Advanced Technology Group. She received a Masters Degree in Computer Science from the University of Illinois at Urbana-Champaign. She can be reached at ngawor@us.ibm.com


		Lin Sun is a staff software engineer for IBM in Research Triangle Park, North Carolina, where she is on the Software Group WebSphere Application Server Community Edition Development Team. Lin received a Masters Degree in Information Science from University of North Carolina at Chapel Hill. She can be reached at linsun@us.ibm.com.