Skip to main content link. Accesskey S
  • Log In
  • Help
  • IBM Logo
  • Lotus Expeditor wiki
  • All Wikis
  • All Forums
  • Home
  • Product Documentation
  • Community Articles
  • Learning Center
  • IBM Redbooks
  • API Documentation
Search
Community Articles > Expeditor Server > Manually setting up the Expeditor Client Management Oracle database schema
  • New Article
  • Share Show Menu▼
  • Subscribe Show Menu▼

About the Original Author

Click to view profileRusty Godwin
Contribution Summary:
  • Articles authored: 1
  • Articles edited: 1
  • Comments Posted: 0

Recent articles by this author

Manually setting up the Expeditor Client Management Oracle database schema

If you plan to use Expeditor Client Management component of the Expeditor Server with an Oracle database, you are required to grant DBA permissions to the database's administration id. This is because
Community articleManually setting up the Expeditor Client Management Oracle database schema
Added by Rusty Godwin | Edited by IBM contributorRusty Godwin on February 5, 2009 | Version 7
  • Edit
  • More Actions Show Menu▼
Rate this article 1 starsRate this article 2 starsRate this article 3 starsRate this article 4 starsRate this article 5 stars
expanded Abstract
collapsed Abstract
No abstract provided.
Tags: configure, database, how-to, server, xcm
If you plan to use Expeditor Client Management component of the Expeditor Server with an Oracle database, you are required to grant DBA permissions to the database's administration id. This is because the Expeditor Client Management configuration uses the database's administration id to create the database schema. If your company's security policy does not allow this, the following instructions can be used to manually create the schema in the Oracle database before running the Expeditor Server configurator. If you setup the required schema before running the Expeditor Server configurator, when you run the configurator it will skip the configuration of the tables and therefore not require DBA permission to be granted to the database's administration id.

To create the required database scripts to setup the Expeditor Client Management schema in the database follow these steps:

1. On the Expeditor Server CD image, extract the file ClientMgmt\ClientMgmt.zip to a temporary directory.

2. Copy the following files to the temporary directory:

was_home\Scheduler\createTablespaceOracle.ddl
was_home\Scheduler\createSchemaOracle.ddl

3. Copy the attached configSettings.sql file to the temporary directory.

4. Edit configSettings.sql and change the ThreadAuthUser AttrValue to the Expeditor Server administrator user id (e.g. xpdadmin). To set the ThreadAuthPassword, you will need to get the encoded password. To do that use the WebSphere password encoder as follows:

a. Open a command prompt and change to the was_home/bin directory and issue the setupCmdLine command.
b. Set WAS_CLASSPATH=%WAS_CLASSPATH%;was_home\runtimes\com.ibm.ws.admin.client_6.1.0.jar
c. Issue the following command to encode your password. Replace password with the password of your WAS admin id.

java -cp "%WAS_CLASSPATH%" com.ibm.ws.security.util.PasswordEncoder password


d. Set the value of the encoded password (minus the quotes) as the ThreadAuthPassword AttrValue in configSettings.sql


5. Edit the following files in the temporary directory and replace the specified variables in the files:

a. database\createtablespace_ora.sql.tmpl

Replace @db.index.tablespace@ with the directory where the index tablespace file should be created.
Replace @db.user.temp.tablespace@ with the directory where the user temporary tablespace file should be created.
Replace @db.data.tablespace@ with the directory where the data tablespace file should be created.
Replace @db.user@ with the database administrator user id you plan to use when you install the server.

b. createTablespaceOracle.ddl

Replace @SCHED_TABLESPACE@ with a tablespace name like XCM_SCHED
Replace @location@ with the directory where the scheduler tablespace file should be created.

c. createSchemaOracle.ddl

Replace @SCHED_TABLESPACE@ with the tablespace name used in createTablespaceOracle.ddl
Replace @TABLE_PREFIX@ with EJBTIMER_

6. Edit database\sql\create_tables_oracle.sql and remove all the blank lines before the constraint keyword in the create table statements. SQL Plus will fail with an error due to the blank line.


Next, create the Client Management Server database and a database administrator user. You will need to grant the following permissions to the database administrator user id you plan to use when you install the server in order for the Client Management Server to operator correctly: CONNECT, RESOURCE, CREATE SYNONYM, DROP PUBLIC SYNONYM, CREATE PUBLIC SYNONYM, CREATE VIEW

Now run the script files created above to create the database schema.

1. Hav e your Oracle DBA run the following in the temporary directory files using an ID with DBA permissions. This will create the required tablespaces:

a. database\createtablespace_ora.sql.tmpl
b. createTablespaceOracle.ddl

2. Run the following files in the temporary directory as the database administrator user id you plan to use when you install the server, to create the schema and create the server configuration settings:

a. otis\jython\sql\createOMADMTables_ora.sql
b. otis\jython\sql\TaskManagerSchema_ora.sql
c. database\sql\create_tables_oracle.sql
d. createSchemaOracle.ddl
e. configSettings.sql

Now that the database schema has been created, install the Expeditor Server using the custom install method and install the Client Management component. See http://publib.boulder.ibm.com/infocenter/ledoc/v6r2/topic/com.ibm.wedserver.doc/InstallingLotusExpeditorServerCoreFiles.html for information on the custom install. At the end of the install you can start the configurator to configure the Client Management component. However, note that the configurator will query the Oracle database to validate if it is the correct version. To allow this, you can grant the database administrator user permission to issue select on the v$instance table:

grant select on v_$instance to id;


If your company's security policy does not allow this, then after the install completes, do not launch the configurator from the installer. Instead exit the installer and start the configurator manually using this command from the xpd_install_root/config/ directory:

config -NOSWCHECK


This will not validate any of the software levels you have installed.

After starting the configurator, when prompted to use existing data, respond yes.

  • Edit
  • More Actions Show Menu▼


expanded Attachments (0)
collapsed Attachments (0)
Edit the article to add or modify attachments.
expanded Versions (3)
collapsed Versions (3)
Version Comparison     
VersionDateChanged by              Summary of changes
This version (7)Feb 5, 2009, 5:07:47 PMRusty Godwin  IBM contributor
6Feb 5, 2009, 5:06:06 PMRusty Godwin  IBM contributor
4Feb 5, 2009, 4:42:16 PMRusty Godwin  IBM contributor
expanded Comments (0)
collapsed Comments (0)
Copy and paste this wiki markup to link to this article from another article in this wiki.
Go ElsewhereStay ConnectedHelpAbout
  • IBM Collaboration Solutions wikis
  • IBM developerWorks
  • IBM Software support
  • Twitter LinkIBMSocialBizUX on Twitter
  • FacebookIBMSocialBizUX on Facebook
  • ForumsLotus product forums
  • BlogsIBM Social Business UX blog
  • Community LinkThe Social Lounge
  • Wiki Help
  • Forgot user name/password
  • Wiki design feedback
  • Content feedback
  • About the wiki
  • About IBM
  • Privacy
  • Accessibility
  • IBM Terms of use
  • Wiki terms of use