The most common use of the datastoreUtil utility is to assist in the installation of the Business Transaction Management product. During configuration, the system automatically creates the appropriate database tables for database users for the sphere, performance, and transaction databases unless you choose to create them beforehand with the datastoreUtil utility.
By using the generateSchema command, you can create DDL for the targeted database environment. The output of the generateSchema command can be used as input (with the appropriate database utility) to create the necessary tables and views for the Business Transaction Management product.
Use the datastoreUtil utility to connect to a database. Once connected, you can do the following:
generate the DDL of the specified schema definition within the connected database
check the status of the specified schema within the connected database
create the specified schema within the connected database
upgrade the specified schema within the connected database
drop the entire specified schema from the connected database
save the database connection information into a file for easy reconnection to the database at a later time
display the owner of the database instance (service name and UUID)
reset ownership of the database instance by removing knowledge of its current owner
dump the definition of the connected database to a text file containing SQL dml statements in the given database format
enter the database explore mode (commands to search and display data)
Invoking the datastoreUtil Utility
To invoke the datastoreUtil utility, navigate to the tools directory and use the following from the command line:
Windows:datastoreUtil.bat
Unix: datastoreUtil.sh
After starting the utility, you can call the commands described in the next subsection. For commands that have multiple arguments, you must call the arguments in the order described.
Use the exit command to exit the utility.
datastoreUtil commands include the following:
| Command | Command | Command |
|---|---|---|
| help | generateSchema | checkOwner |
| connect | createSchema | resetOwner |
| saveConnection | checkSchema | dumpDataBase |
| close | upgradeSchema | -- |
| exit | dropSchema | -- |
Use the following syntax to get help:
help | help command
Use the help command to view help for all commands, or enter a command name to receive help for a single command
Connect to a database using the user-specified connection information
connect databaseType|filename
databaseType - specify oracle. This is the only supported value.
filename - name of a file as specified by the saveConnection command
Use the connect command to enter database connection information and connect to the database. You must have the following information for the database to which you want to connect:
driver name
username
password
URL connection string
Once connected, you might issue the saveConnection command to save the connection information within a file. The next time you want to connect to the same database, you can provide the file name with the connect command. If you provide the database type, the utility automatically selects the corresponding default driver.
Close a connection previously opened with the connect command. This command takes no parameters.
Generate the DDL of the specified schema definition. You do not have to be connected to a database to call this command.
Alternatively, you can use generate for this command.
generateSchema schemaType databaseType [[directory] targetSchema] -partition|-nopartition generate schemaType databaseType [[directory] targetSchema] -partition|-nopartition
schemaType - one of the known schema types:
sphere - schema for the sphere database (the sphereDB user)
exm - schema for the transaction database (the transactionDB user)
performance - schema for the performance database (the measurementDB user)
monitorgroup - schema for a monitor group
msglog - schema for the system message log
databaseType - one of the supported database types:
oracle
directory - location to generate the DDL file (defaults to the local directory)
targetSchema -target the generated schema for a specific user, for example sphereDB, transactionDB, or measurementDB.
For example, you have a high-level administrative permissions on the database, but you want to create the schema for someone with much lower-level permissions. You would use something like the following example:
generateSchema sphere oracle \temp sphereUser
-partition | -nopartition
This flag is required if your specified schemaType is performance or monitorgroup. If your specified schemaType is any other value, this flag is not required and is ignored if you use it.
If you are using Oracle Enterprise Edition, you can create a performance or monitorgroup schema that takes advantage of Oracle's partitioning feature by specifying the -partition flag. If you do not want to take advantage of this feature or if your Oracle edition does not provide the partitioning feature, you must specify -nopartition (if you are creating a performance or monitorgroup schema).
Check the status of the specified schema within the connected database
Alternatively, you can use check for this command as the syntax diagram shows.
checkSchema schemaType check schemaType
schemaType - one of the known schema types:
sphere - schema for the sphere database (the sphereDB user)
exm - schema for the transaction database (the transactionDB user)
performance - schema for the performance database (the measurementDB user)
monitorgroup - schema for a monitor group
msglog - schema for the system message log
Use the checkSchema command to check for the specified schema type within a connected database. You must successfully execute the connect command before executing the checkSchema command. The checkSchema command displays the status of the schema as found in the database. If the status of the database schema is not up-to-date, this command displays the differences found, and the DDL you must apply to upgrade the database schema.
This command does not change the database schema. If you wish to automatically upgrade the database schema from this command line utility, use the command upgradeSchema.
Create the specified schema within the connected database. You must successfully execute the connect command before executing the createSchema command.
Alternatively, you can use create for this command.
createSchema schemaType -partition | -nopartition create schemaType -partition | -nopartition
schemaType - one of the known schema types:
sphere - schema for the sphere database (the sphereDB user)
exm - schema for the transaction database (the transactionDB user)
performance - schema for the performance database (the measurementDB user)
monitorgroup - schema for a monitor group
msglog - schema for the system message log
-partition | -nopartition
This flag is required if your specified schemaType is performance or monitorgroup. If your specified schemaType is any other value, this flag is not required and is ignored if you use it.
If you are using Oracle Enterprise Edition, you can create a performance or monitorgroup schema that takes advantage of Oracle's partitioning feature by specifying the -partition flag. If you do not want to take advantage of this feature or if your Oracle edition does not provide the partitioning feature, you must specify -nopartition (if you are creating a performance or monitorgroup schema).
You may want to use the commands checkSchema and upgradeSchema before or instead of the createSchema command.
upgrade the specified schema within the connected database
Alternatively, you can use upgrade for this command.
upgradeSchema schemaType
upgrade schemaType
schemaType - one of the known schema types:
sphere - schema for the sphere database (the sphereDB user)
exm - schema for the transaction database (the transactionDB user)
performance - schema for the performance database (the measurementDB user)
monitorgroup - schema for a monitor group
msglog - schema for the system message log
Use the upgradeSchema command to upgrade the specified schema of the connected database. You must successfully execute the connect command before executing the upgradeSchema command. The upgradeSchema command will add any missing table, column, or index to the database.
You may want to execute the checkSchema command before executing the upgradeSchema command.
Note that the upgradeSchema command only adds missing elements; it does not remove anything. You cannot roll back the upgradeSchema command.
Drop the entire specified schema from the connected database
Alternatively, you can use drop for this command.
dropSchema schemaType drop schemaType
schemaType - one of the known schema types:
sphere - schema for the sphere database (the sphereDB user)
exm - schema for the transaction database (the transactionDB user)
performance - schema for the performance database (the measurementDB user)
monitorgroup - schema for a monitor group
msglog - schema for the system message log
Use the dropSchema command to drop the entire schema from the connected database. You must successfully execute the connect command before executing the dropSchema command.
Warning: the dropSchema command removes all tables and any data stored within these tables. If you have any unsaved data you want to keep from the connected database, export or save the data before executing this command. You cannot roll back the dropSchema command.
Save the database connection information to a file
Alternatively, you can use save for this command.
saveConnection filename save filename
filename - name of the file where you want to save user, password, url, and driver values.
Use the saveConnection command to save user, password, url, and driver values to a file.
Once connected to a database, you might issue the saveConnection command to save the connection information to a file. You can later reconnect to the same database by providing the file name with the connect command. Make sure to remove any unused files as they contain connection information.
Displays the owner of the database instance (service name and UUID)
Alternatively, you can use owner for this command.
checkOwner owner
Once connected to a database, you might issue the checkOwner command to display the service UUID and name that owns the database instance. Only a service owner can connect to a specific database instance. To reset ownership, use the resetOwner command.
Reset ownership of the database instance by removing knowledge of its current owner
Alternatively, you can use reset for this command.
resetOwner reset
Use the resetOwner command to remove the service ownership information associated with a specific database. You must successfully execute the connect command before executing the resetOwner command. Once reset, the next service that accesses the database takes ownership of the database instance.
Export the contents of the given database to a text file containing SQL dml statements in the given database format
Alternatively, you can use dump for this command.
A command to connect must have been successfully executed before this command can be issued.
dumpDatabase schemaType databaseType [directory] dump schemaType databaseType [directory]
Use the dumpDatabase command to export the data contained in the specified schema to a text file containing SQL dml statements that you can use to import into another database. You specify the database type and the text file is formatted into SQL statements supported by that database type.
Note: If your database contains clob fields, they are truncated to 4000 characters in the resulting text file.
schemaType - one of the known schema types:
sphere - schema for the sphere database (the sphereDB user)
exm - schema for the transaction database (the transactionDB user)
performance - schema for the performance database (the measurementDB user)
monitorgroup - schema for a monitor group
msglog - schema for the system message log
databaseType - one of the supported database types:
oracle
directory - where the SQL file is generated. The default location is the local directory.
Use the following command to exit the datastoreUtil utility.
exit