Creating the Worklist Manager Database

Perform the following steps to create the Worklist Manager database and configure the connection information so the Worklist Manager Service Engine can connect to the database. Note that if you are using a Java DB (Derby) database, most of these steps are performed automatically for you.

• Creating the Worklist Manager Database

• Setting the GlassFish JVM Classpath to the Database Drivers

• Creating the JDBC Connection Pool and JDBC Resource

• Configuring the Service Engine to Use the Worklist Manager Database

Creating the Worklist Manager Database

The way you create the database depends on the database platform you are using. Follow the appropriate procedure below for your database platform.

• Creating the Database for JavaDB (Derby)

• Creating the Database for MySQL

• Creating the Database for Oracle

Creating the Database for JavaDB (Derby)

If you are using a JavaDB (Derby) database, you only need to start the WLM SE to create the database. The WLM SE installation automatically creates the default JDBC connection pool and data resource for the database (because the create flag is set to true for the connection pool). Starting the service engine automatically creates a Derby database named WORKFLOWDB with a schema named WORKFLOW. The login username and password for this database are both WORKFLOW.

To Start the WLM Service Engine

1. In the NetBeans IDE, click the Services tab.
2. Expand Servers > GlassFish v2.1 > JBI > Service Engines.
3. Right-click sun-wlm-engine, and then select Start.

To Connect to the Database From NetBeans

1. In the NetBeans IDE, click the Services tab.
2. Expand Databases, and then expand Drivers.
3. Right-click Java DB (Network), and then select Connect Using.

   The New Database Connection dialog box appears.

4. Enter the following information:

   • Host: The name of the server on which the database is located. By default, this is localhost.

   • Port: The database port number. By default, this is 1527.

   • Database: The name of the Worklist Manager database. By default, this is WORKFLOWDB.

   • User Name: The user name under which the database schema was created. By default, this is WORKFLOW.

   • Password: The password for the above user. By default, this is WORKFLOW.

5. If you do not want to enter the password each time you connect to the database, select Remember Password.
6. Click OK.
7. Verify that the WORKFLOW schema is selected.
8. Click OK again.

Creating the Database for MySQL

To use MySQL server for the Worklist Manager database, create a database and user by running the following SQL commands. Run these commands by logging into MySQL as the root user. You can change the name of the database from WLMSE_USER_DB, and you can change the name of the user from WLMSE_USER.

CREATE DATABASE WLMSE_USER_DB;
 GRANT ALL ON WLMSE_USER_DB.* TO 'WLMSE_USER'@'%' IDENTIFIED BY 'WLMSE_USER';

Creating the Database for Oracle

To use Oracle for the Worklist Manager database, create a tablespace and user in an Oracle instance using the SQL commands below. You can use any database name in place of WLMSE_USER_DB, and any user name and password in place of WLMSE_USER.

CREATE TABLESPACE "WLMSE_USER_DB"
 DATAFILE
   'WLMSE_USER_DB1.dat' SIZE 2000M,
   'WLMSE_USER_DB2.dat' SIZE 2000M;

CREATE USER WLMSE_USER IDENTIFIED BY WLMSE_USER
DEFAULT TABLESPACE WLMSE_USER_DB
QUOTA UNLIMITED ON WLMSE_USER_DB
TEMPORARY TABLESPACE temp
QUOTA 0M ON system

GRANT CREATE session to WLMSE_USER;
GRANT CREATE table to WLMSE_USER;
GRANT CREATE procedure to WLMSE_USER;
GRANT CREATE sequence to WLMSE_USER;

-- To run these commands you need to be logged in as "sys as sysdba"
grant select on sys.dba_pending_transaction to WLMSE_USER;
grant select on sys.pending_trans$ to WLMSE_USER;
grant select on sys.dba_2pc_pending to WLMSE_USER;
grant execute on sys.dbms_system to WLMSE_USER;
grant select on SYS.dba_2pc_neighbors to WLMSE_USER;
grant force any transaction to WLMSE_USER;

Setting the GlassFish JVM Classpath to the Database Drivers

The database JDBC drivers for Oracle or MySQL must be either set in the GlassFish JVM classpath (preferred) or placed in the /glassfish/lib directory before you create the connection pools. This is not necessary for the Derby (JavaDB) database. For example, if you are using Oracle and JDK 6, you would set your GlassFish JVM classpath to the ojdbc6.jar file.

To set the GlassFish JVM Classpath settings

1. Open the GlassFish Admin Console. To access the Admin Console, do the following:
   1. From the Services window of the NetBeans IDE, start the GlassFish server.
   2. Right-click the GlassFish server and select View Admin Console.

      The login screen of the Admin Console appears.

   3. Enter your username and password.

      The default user name is admin; the password is defined during installation.

2. To access the GlassFish JVM classpath settings, do the following:
   1. From the Admin Console's Common Tasks window, click Application Server.

      The Application Server settings appear in the console window to the right.

   2. Click the JVM Settings tab and the Path Settings sub-tab.
3. In the Classpath Suffix field, type the full path for your database JDBC driver on your local directory.

   For example, C:\GlassFishESB\drivers\oracle11gDriver\ojdbc6.jar.

4. Click Save, and then restart GlassFish.

Creating the JDBC Connection Pool and JDBC Resource

The JDBC connection pool and data resource provide the connection information needed by the WLM SE to connect to the Worklist Manager database. You only need to perform these steps if you are using an Oracle or MySQL database.

To Create the JDBC Connection Pool

1. Log in to the GlassFish Admin Console.

   You can access the console from the Services window in NetBeans.

2. In the left portion of the Admin Console, expand Resources, expand JDBC, and then select Connection Pools.
3. On the Create Connection Pool page, click New.
4. Fill in the following information:

   • Name: A name for the connection pool.

   • Resource Type: The Java class to use for Worklist Manager database transactions.

   • Database Vendor: The database platform you are using.

5. Click Next.
6. In the DataSource Classname field, accept the default class or enter a new one to use.
7. In the additional properties section, enter the connection values for the Worklist Manager database. Be sure to enter the following information at a minimum (you might need to create some of these properties).
   • For Oracle:

     • URL – The URL that points to the database. The syntax of the URL is jdbc:oracle:thin:@host:port:database_name.

     • user – The login ID for the user you created for the database.

     • password – The password for the above user.

   • For MySQL:

     • URL – The URL that points to the database. The syntax of the URL is jdbc:mysql://server:port/database_name.

     • user – The login ID for the user you created for the database.

     • password – The password for the above user.

     • DatabaseName – The name of the database.

8. Click Finish.

To Create the JDBC Resources

1. In the left portion of the Admin Console, expand Resources, expand JDBC, and then select JDBC Resources.
2. On the Create JDBC Resource page, click New.
3. In the JNDI Name field, enter a unique name for the JDBC resource.
4. In the Pool Name field, select the name of the JDBC connection pool you created above.
5. (Optional) In the Description field, enter a brief description of the resource.
6. In the Status field, select the Enabled check box.
7. Click OK.

Configuring the Service Engine to Use the Worklist Manager Database

You configure the service engine to connect to the database by modifying the service engine properties. If you are using the default Java DB database, you do not need to perform this step. The default JDBC resource is already specified.

To Configure the Service Engine for the Database

1. In the Services window of the NetBeans IDE, expand Servers > GlassFish v2.x > JBI > Service Engines.
2. Right-click sun-wlm-engine, and then select Properties.

   The sun-wlm-engine Properties window appears.

3. In the DataSource JNDI field, enter the JNDI name you entered for the JDBC resource above.
4. In the DataSource Type field, select the database platform you are using.
5. Click Close.