JDBC Database Utility

 

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

• Overview

• About JMS Stores

• Regenerating JDBC Stores

Overview

The JDBC utils.Schema utility allows you to regenerate new JDBC 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.

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

About JMS Stores

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 instructions on creating and configuring a store, see "JMS File Stores" and "JMS JDBC Stores" for information about file and JDBC database stores, respectively, in the Administration Console Online Help.

Regenerating JDBC 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 Cloudscape, Sybase, Oracle, MSSQL Server, and IBM DB2 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.

Table 6-2 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/classes/jms/ddl directory within the weblogic.jar file contains JMS DDL files for Cloudscape, Sybase, Oracle, MSSQL Server, and IBM DB2 databases. To use a different database, copy and edit any one of these files.

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/classes/jms/ddl/jms_oracle.ddl

With the Cloudscape database, no username or password is required. However, the Cloudscape JDBC driver uses the cloudscape.system.home system property to find the directory containing its database files. You must supply the value for this property with the -D Java command option. In addition, you must specify the Cloudscape classes in your CLASSPATH, which exists in weblogic/samples/eval/cloudscape/lib.

For example, the following command creates the JMS tables in a Cloudscape server:

java -Dcloudscape.system.home=/weblogic/samples/eval/cloudscape/data 
  utils.Schema jdbc:cloudscape:demoPool;create=true 
  COM.cloudscape.core.JDBCDriver -verbose 
  weblogic/classes/jms/ddl/jms_cloudscape.ddl

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