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.