Skip navigation.
Downloads | Product Documentation | Support

Programming WebLogic JMS

# JDBC Database Utility

The following sections describe JDBC database stores for WebLogic JMS, and how to use the JDBC database utility to regenerate existing JDBC database stores:

- Overview
- About JMS Tables
- Regenerating JDBC Database Stores

# Overview

The JDBC `utils.Schema` utility allows you to regenerate new JDBC database stores by deleting the existing versions. Running this utility is usually not necessary, since JMS automatically creates these stores for you. However, if your existing JDBC database stores somehow become corrupted, you can regenerate them using the `utils.Schema` utility. In addition, if your JDBC driver is not supported by WebLogic JMS (as described in "JMS JDBC Stores Tasks" in the Administration Console Online Help), then the tables required by JMS must be created manually.

Caution: Use caution when running the `utils.Schema` command as it will delete all existing database tables and then recreate new ones.

# About JMS Tables

The JMS database contains two system tables that are generated automatically and are used internally by JMS, as follows:

- `<prefix>JMSStore`
- `<prefix>JMSState`

The prefix name uniquely identifies JMS tables in the backing store. Specifying unique prefixes allows multiple stores to exist in the same database. The prefix is configured via the Administration Console when configuring the JDBC store. A prefix is prepended to table names when:

- The DBMS requires fully qualified names.
- You must differentiate between JMS tables for two WebLogic servers, enabling multiple tables to be stored on a single DBMS.

The prefix should be specified using the following format, which will result in a valid table name when prepended to the JMS table name:

>     [[catalog.]schema.]prefix

Note: No two JMS stores should be allowed to use the same database tables, as this will result in data corruption.

For more information on configuring JDBC database stores for WebLogic JMS, see "JMS JDBC Store Tasks" in the Administration Console Online Help.

# Regenerating JDBC Database Stores

The `utils.Schema` utility is a Java program that takes command line arguments to specify the following:

- JDBC driver
- Database connection information
- Name of a file containing the SQL Data Definition Language (DDL) commands (terminated by semicolons) that create the database tables

By convention, the DDL file has a .`ddl` extension. DDL files are provided for Pointbase, Cloudscape, Informix, Sybase, Oracle, MS SQL Server, IBM DB2, and Times Ten databases.

To execute `utils.Schema`, your `CLASSPATH` must contain the `weblogic.jar` file.

Enter the `utils.Schema` command, as follows:

>     java utils.Schema url JDBC_driver [options] DDL_file

The following table lists the `utils.Schema` command-line arguments.

Argument | Description
--- | ---
`url` | Database connection URL. This value must be a colon-separated URL as defined by the JDBC specification.
`JDBC_driver` | Full package name of the JDBC Driver class.
`options` | Optional command options. If required by the database, you can specify: The username and password as follows: `-u <``username>``-p <``password>` The Domain Name Server (DNS) name of the JDBC database server as follows: `-s <``dbserver``>` You can also specify the -`verbose` option, which causes `utils.Schema` to echo SQL commands as they are executed.
`DDL_file` | The full pathname of a text file containing the SQL commands that you wish to execute. An SQL command can span several lines and is terminated with a semicolon (;). Lines beginning with pound signs (#) are comments. The `weblogic/jms/ddl` directory within the `weblogic.jar` file contains JMS DDL files for Pointbase, Cloudscape, Informix, Sybase, Oracle, MS SQL Server, IBM DB2, and Times Ten databases. To use a different database, copy and edit any one of these files. Use the jar utility supplied with the JDK to extract them to the `weblogic/jms/ddl` directory using the following command: `jar xf weblogic.jar weblogic/jms/ddl` Note: If you omit the `weblogic/jms/ddl` parameter, the entire jar file is extracted.

For example, the following command recreates the JMS tables in an Oracle server named `DEMO`, with the username `user1` and password `foobar`:

>     java utils.Schema jdbc:weblogic:oracle:DEMO \
>       weblogic.jdbc.oci.Driver -u user1 -p foobar -verbose \
>       weblogic/jms/ddl/jms_oracle.ddl

With the Pointbase demo database that is shipped with WebLogic Server, no username or password is required. However, you must follow this procedure to create the JMS tables in a Pointbase server:

1. Set the WLS samples environment:

`%SAMPLES_HOME%\server\config\examples\setExamplesEnv.cmd`

2. Change to the `%WL_HOME%\server\lib\`directory, and then extract the `jms_pointbase.ddl` file from the `weblogic.jar` file to the current directory.
3. Execute the following command to create the JMS tables:

>     java utils.Schema jdbc:pointbase:server://localhost/demo
>       com.pointbase.jdbc.jdbcUniversalDriver
>       -u examples -p examples -verbose jms_pointbase.ddl

The Pointbase JDBC URL specifies the demo database, which is included with the WebLogic JMS samples. For the samples, the JMS tables have already been created in this database.

1. Start the Pointbase server and open the Pointbase console.

For detailed information on using the Pointbase Server console to monitor and manipulate the JMS tables, see the Pointbase.html file in the `WL_HOME``\samples\server\src\examples` directory, where `WL_HOME` is the top-level directory of your WebLogic Platform installation.