4 Database Connection Pools, Data Resources, Deleting Tables, and Configuring JMS

This chapter provides procedures on how to define database connection pools and data resources, which each master person index application requires, and deleting database tables that are no longer needed.

This chapter includes the following sections:

Defining the Database Connection Pools and Data Resources
Configuring JMS Resource
Dropping Oracle Healthcare Master Person Index Database Tables

Defining the Database Connection Pools and Data Resources

Each master person index application requires two database connection pools; one for the master controller and one for the sequence manager. To set up the connection pools, you need to create the connection pools and then define a JDBC resource for each. This section provides general instructions for setting up the connection pools. For more information about the procedures in this section, see the online help provided with your server's Admin Console.

Note:

In the past if you used a MySQL or Oracle database, you needed to manually install or copy the database driver to your application server environment. This is no longer the case as the drivers are now included with the OHMPI installation.

To Create JDBC Connection Pools and Data Resources

The JDBC connection pools provide connections to the master person index database. You need to create two connection pools that are configured in the same way.

A JDBC resource (also known as a data source) gives the master person index application the ability to connect to the database. Two JDBC resources are required.

Before proceeding, make sure you have the relevant information about the master person index database (such as the database name, URL, and administrator login credentials).

Perform the following steps to define database connectivity through the GlassFish or WebLogic application servers:

Creating JDBC Connection Pools and Setting Data Resources (GlassFish)
Creating JDBC Connection Pools and Setting Data Resources (WebLogic)

Creating JDBC Connection Pools and Setting Data Resources (GlassFish)

Log in to the GlassFish Admin Console.
1. In the NetBeans IDE, select the Services tab.
2. Right-click GlassFish v2.x, and select View Admin Console.
  
  The Oracle GlassFish Enterprise Server Admin Console opens.
Use the Sun GlassFish Enterprise Server Admin Console to define the following:
1. Create two Connection Pools
  
  - In the left pane choose Resources->JDBC->Connection Pools.
  
  - In the Connection Pools pane, click New.
  
  - In New JDBC Connection Pool (Step 1 of 2), set the name for your connection pool using your database name. For example, cpmysqlDS with cp representing connection pool, mysql representing the database name and DS representing the data source.
  
  - In the Resource Type field you must select one of the selections from the drop-down list.
  Note:
  All three of the Resource Type options are valid.
  - javax.sql.DataSource - Use this class if the master person index application is using local transactions only.
  - javax.sql.XADataSource - Use this class if the master person index application transactions are distributed, either within the application or across applications.
  - javax.sql.ConnectionPoolDataSource - Use this class if the master person index application is using local transactions only. This class provides possible performance improvements.
  - In the Database Vendor field select a vendor from the drop-down list.
  
  - Click Next.
  
  - In New JDBC Connection Pool (Step 2 of 2), you need to select and define four properties.
  
  For MySQL:
  
  * Database Name: The name you gave the database.
  
  * Password: The password for the database.
  
  * URL (do not select "url" in lower case): This must be jdbc:database://host name:port number/database name where database is your database (for example, Oracle or MySQL), host name is the name of your host (for example, localhost), port number is generated, and database name is the name you gave your database when you created it.
  
  * User: The name you assigned to the User.
  
  For Oracle:
  
  * URL - The URL that points to the database. The syntax of the URL is jdbc:oracle:thin:@host:port:database_name. If you are using a third-party JDBC driver, refer to the documentation for that driver for information about the URL.
  
  * user - The login ID for the user you created in Step 2: Create a Master Person Index Database and User.
  
  * password - The password for the above user.
  
  * ImplicitCachingEnabled - An indicator of whether implicit statement caching is enabled. Set this property to true.
  
  * MaxStatements - The maximum number of statements in the cache. Set this property to 1000.
  
  For Microsoft SQL Server:
  
  * URL - jdbc:microsoft:sqlserver://host:port;databasename=name
  
  * databaseName - The name of the database.
  
  * portNumber - The port number for the database.
  
  * serverName - The name of the server that is hosting the database.
  
  * user - The login ID for the user you created in Step 2: Create a Master Person Index Database and User.
  
  * password - The password for the above user.
  
  * SendStringParametersAsUnicode - An indicator of whether string parameters are sent to the database in Unicode or in the default character encoding of the database. Set this property to false.
  
  * MaxPooledStatements - The maximum number of prepared statements in the cache. Set this property to 1000.
  
  - When finished defining the MySQL or Oracle properties, click Finish.
2. Repeat all of step a, only this time set the Sequence Data Source using your application name. For example, cpPatientSDS with cp representing connection pool, Patient representing the application name and SDS representing the sequence data source.
3. Create two JDBC Resources
  
  - In the left pane choose Resources->JDBC Resources.
  
  - In the JDBC Resources pane, click New.
  
  - In New JDBC Resource, set the JNDI Name as jdbc/object nameDataSource (for example, jdbc/PatientDataSource).
  
  Note:
  The object name must match the application name you set in NetBeans for both the data source and the sequence data source.
  
  - In New JDBC Resource, set the Pool Name from the drop-down list. It should be the JDBC Data Source that you just created and click OK.
  
  - In the JDBC Resources pane, click New.
  
  - In New JDBC Resource, set the JNDI Name as jdbc/object nameSequenceDataSource (for example, jdbc/PatientSequenceDataSource).
  
  - In New JDBC Resource, set the Pool Name from the drop-down list. It should be the JDBC Sequence Data Source that you just created and click OK.
For MySQL only: After creating your DataSource and your SequenceDataSource, you need to set the Associate with Thread connection setting to Enabled. To do this:
1. With GlassFish running in NetBeans, right-click GlassFish in the left pane and select View Admin Console.
  
  The GlassFish Admin Console opens.
2. In the left pane, choose Resources->J DBC->JDBC Resources->Connection Pools, and then select your DataSource or SequenceDataSource.
3. In the right pane, click the Advanced tab.
4. Under Connection Settings, set Associate with Thread to Enabled.
5. Click the Save button.
6. Repeat step 3 for each DataSource and SequenceDataSource.

You are now ready to deploy your application.

Creating JDBC Connection Pools and Setting Data Resources (WebLogic)

To Create JDBC Connection Pools and Data Resources for an MPI Application Project for MySQL

This section provides instructions for creating the JDBC data resources and defining the JDBC connections for an MPI Application Project.

For instructions on how to start and stop your Oracle WebLogic Server, see Starting and Stopping Servers: Quick Reference at http://download.oracle.com/docs/cd/E14571_01/wls.htm.
Launch the Oracle WebLogic Server Administration Console.
Log in using the default user Name (weblogic) and Password (welcome1).

The Oracle WebLogic Administration Console appears.
On the left panel, under Domain Structure, expand Services, click JDBC, and then choose Data Sources.

A Summary of JDBC Data Sources appears in the right panel.
To create a new JDBC Data Source click New at the bottom of the right panel.

Settings for a new JDBC Data Source appears in the right panel of the page. It is here that you will create a new JDBC Data Source.
In the Name field, type object_nameDataSource.

The name you enter here will propagate elsewhere, but it must be the object name that you have already created (for example, PersonDataSource).
In the JNDI Name field, type jdbc/object_nameDataSource.

Use the name you entered in step 6 here (for example, jdbc/PersonDataSource).
Click Save.

A new page appears in the right panel which is for setting the Database Type.
In the Database Type drop-down list, choose the appropriate type (for example: MySQL).
In the Database Driver drop-down list, choose the appropriate driver; for example: MySQL's Driver (Type 4) Versions:using com.mysql.jdbc.Driver.
Make sure that your data source supports Global Transactions.
Click Next.

Connection Properties appears on the Create a New JDBC Data Source panel. Use it to define the connection properties.
In the Database Name field, type a name for the database to which you want to connect (for example: mysql).
In the Host Name field, type the name or the IP address of the database server (for example: localhost).
In the Port field, type the port on the database server that is used to connect to the database (for example: 3306, the default for MySQL).
In the Database User Name field, type the database account user name you want to use to create database connections (for example: root).
In the Password field, type a password for your database account to use to create database connections.
In the Confirm Password field, re-type the password to confirm it.
Click Next.

The Settings for PersonDataSource page appears in the right panel.
Click the Connection Pool tab, click Test Configuration, and then click Next.

Select Targets appears on the Create a New JDBC Data Source page in the right panel. Here you select one or more targets to deploy the new JDBC data source.
In the Servers check list, select one or more target servers and click Finish.

Note:
If you do not select a target, the data source will be created but not deployed. You will need to deploy the data source at a later time.
Repeat the above steps to create jdbc/object_nameSequenceDataSource.

To Create JDBC Connection Pools and Data Resources for an MPI Application Project for Oracle

For instructions on how to start and stop your Oracle WebLogic Server, see Starting and Stopping Servers: Quick Reference at http://download.oracle.com/docs/cd/E14571_01/wls.htm.
Launch the Oracle WebLogic Server Administration Console.
Log in using the default user Name (weblogic) and Password (welcome1).

The Oracle WebLogic Administration Console appears.
On the left panel, under Domain Structure, expand Services, click JDBC, and then choose Data Sources.

A Summary of JDBC Data Sources appears in the right panel.
To create a new JDBC Data Source click New at the bottom of the right panel.

Settings for a new JDBC Data Source appears in the right panel of the page. It is here that you will create a new JDBC Data Source.
In the Name field, type object_nameDataSource.

The name you enter here will propagate elsewhere, and it must be the object name that you have already created (for example, PersonDataSource).
In the JNDI Name field, type jdbc/object_nameDataSource.

Use the name you entered in step 6 here (for example, jdbc/PersonDataSource).
Click Save.

A new page appears in the right panel which is for setting the Database Type.
In the Database Type drop-down list, choose the appropriate type (for example: Oracle).
In the Database Driver drop-down list, choose the appropriate driver; for example: Oracle's Driver (Thin XA) for Instance Connections; Versions: 9.0.1; 9.2.0; 10, 11.
Click Next.
Click Next.

Connection Properties appears on the Create a New JDBC Data Source panel. Use it to define the connection properties.
In the Database Name field, type a name for the database to which you want to connect (for example: Person).
In the Host Name field, type the name or the IP address of the database server (for example: localhost).
In the Port field, type the port on the database server that is used to connect to the database (for example: 1521, the default for Oracle).
In the Database User Name field, type the database account user name you want to use to create database connections (for example: root).
In the Password field, type a password for your database account to use to create database connections.
In the Confirm Password field, re-type the password to confirm it.
Click Next.

The Settings for object_nameDataSource page appears in the right panel.
Click the Connection Pool tab, click Test Configuration, and then click Next.

Select Targets appears on the Create a New JDBC Data Source page in the right panel. Here you select one or more targets to deploy the new JDBC data source.
In the Servers check list, select one or more target servers and click Finish.

Note:
If you do not select a target, the data source will be created but not deployed. You will need to deploy the data source at a later time.
Repeat the above steps to create jdbc/object_nameSequenceDataSource.

You are now ready to deploy your application.

Configuring JMS Resource

You can configure the JMS Resource, <APP-NAME>OutBoundSender, on the server deploying the EJB component. Configuring the JMS resource is optional.

Dropping Oracle Healthcare Master Person Index Database Tables

Scripts are provided to drop the default database tables and indexes created in Step 8: Create the Master Person Index Database Structure. This is useful while testing the master index application implementation.

To Delete Database Tables

Open a standard SQL editor and connect to the master person index database using the user you created in Step 2: Create a Master Person Index Database and User.
Open drop.sql in the NetBeans editor.
Copy and paste the entire text of drop.sql into the SQL editor.
Run the script against the database.
If the database is running on the Oracle 10g platform, open a SQL prompt and run the following command.
```
PURGE RECYCLEBIN;
```