JDBC Database Utility

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, follow this procedure to create the JMS tables in a Pointbase server:

Set the WLS samples environment:
%SAMPLES_HOME%\server\config\examples\setExamplesEnv.cmd
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.
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.

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.

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.