Manually setting up the property extension repository for federated repositories

You can use the createIdMgrPropExtDbTables script to create tables in the property extension repository for federated repositories.

Before you begin

The following databases are supported by the script when the database exists on a distributed operating system:

IBM® DB2®
Apache Derby
IBM Informix® Dynamic Server
Oracle 11g
Microsoft SQL Server

For a list of the supported database versions, see the IBM WebSphere® Application Server detailed system requirements.

To use the IBM DB2 on z/OS® or IBM DB2 on iSeries database, read about manually setting up the property extension repository in DB2.

If you do not have WebSphere Application Server installed on the same system on which you are setting up the database, you must copy the following files from a system where WebSphere Application Server is installed to the system on which you are setting up the database. Ensure that you replicate the same directory structure within the setup directory. The db_type variable represents one of the following directory names: db2, oracle, informix, derby, or sqlserver.

app_server_root\etc\wim\setup\bin\createIdMgrPropExtDbTables.sh
app_server_root\etc\wim\setup\bin\createIdMgrPropExtDbTables.bat
app_server_root\etc\wim\setup\lookaside\db_type\dbclean.sql 
app_server_root\etc\wim\setup\lookaside\db_type\schema.sql 
app_server_root\etc\wim\setup\lookaside\db_type\primarykeys.sql 
app_server_root\etc\wim\setup\lookaside\db_type\indexes.sql 
app_server_root\etc\wim\setup\lookaside\db_type\references.sql 
app_server_root\etc\wim\setup\lookaside\keys.sql 
app_server_root\etc\wim\setup\lookaside\bootstrap.sql

Specifying the database schema:

You can specify the database schema where you want to create the federated repository tables when you are manually setting up the property extension repository.

If you want to use the default schema of the database, you must execute the following commands without specifying the DBSCHEMA parameter. Typically, the default schema is the namespace of the current database user.

Complete these steps to replace the schema variable in the SQL files with the actual database schema name. If WebSphere Application Server and the database are not on the same system, set the SCHEMA_LOCATION value to the location where you copied the SQL files.

Windows operating systems:

Open a command window.
Change to the app_server_root\etc\wim\setup directory.

Enter the following commands:

set SCHEMA_LOCATION=app_server_root\etc\wim\setup\lookaside
set DBTYPE=<db_type>
set DBSCHEMA=dbschemaname
set SCHEMA_DEST_LOCATION=<location where the updated SQL files with replaced variables should be copied>
ws_ant.bat -f app_server_root\etc\wim\setup\filterbuild.xml

where the value of <db_type> is db2, derby, informix, oracle, or sqlserver.

Note: : If SCHEMA_DEST_LOCATION is not set, the updated SQL files are copied to a directory with the name as the value not substituted under the current directory. The output shows where the files are copied.

AIX®, HP-UX, Linux®, and Solaris operating systems:

Open a command window
Change to the app_server_root/etc/wim/setup directory.

Enter the following commands:

export SCHEMA_LOCATION=app_server_root/etc/wim/setup/lookaside
export DBTYPE=<db_type>
export DBSCHEMA=dbschemaname
export SCHEMA_DEST_LOCATION=<location where the updated SQL files with replaced variables should be copied>
ws_ant.sh -f app_server_root/etc/wim/setup/filterbuild.xml

where the value of <db_type> is db2, derby, informix, oracle, or sqlserver.

Note: If SCHEMA_DEST_LOCATION is not set, the updated SQL files are copied to a directory with the name as the value not substituted under the current directory. The output shows where the files are copied.

About this task

The following notes apply to specific databases:

Oracle 11g
- If you did not create the default database when you installed Oracle product, you must manually create the database before you run the createIdMgrPropExtDbTables script. The value of the ORACLE_SID variable is the same value as the name of the database.
- If you want to create the tables in the schema that you specified using DBSCHEMA (described in the previous section, Specifying the database schema) ensure that you create the specified schema in this database before you run the createIdMgrPropExtDbTables script.
- On the AIX, HP-UX, Linux, and Solaris operating systems, run the createIdMgrPropExtDbTables script either as an Oracle user or as a root user with database administrator (dba) rights and appropriate permissions to run SQL queries as a system database administrator (sysdba).
IBM DB2
- On the Windows operating systems, you must initialize the DB2 environment before you run the createIdMgrPropExtDbTables script. At the Windows command prompt, enter db2cmd to open a new DB2 command window and run the createIdMgrPropExtDbTables batch file from this prompt.
Microsoft SQL Server
- Open a command window, change to the app_server_root\bin directory, and enter the following commands to replace the variables in the SQL files. If WebSphere Application Server and the database are not on the same system, set the SCHEMA_LOCATION value to the location where you copied the SQL files.
```
set SCHEMA_LOCATION=app_server_root\etc\wim\setup\lookaside
set DBTYPE=sqlserver
set SCHEMA_DEST_LOCATION=<location where the updated SQL files with replaced variables should be copied>
set DBOWNER=dbo
ws_ant.bat -f app_server_root\etc\wim\setup\filterbuild.xml
```
Note: If SCHEMA_DEST_LOCATION is not set, the updated SQL files are copied to a directory with the name as the value not substituted under the current directory. The output shows where the files are copied.

The following default instance is created as a part of the database installation:

DB2: DB2
Informix: demo_on
SQL Server: %computername%

The Informix database is created with the following environment:

CLIENT_LOCALE=EN_US.CP1252  
DB_LOCALE=EN_US.8859-1
SERVER_LOCALE=EN_US.CP1252
DBLANG=EN_US.CP125

Procedure

Run the createIdMgrPropExtDbTables.sh script or createIdMgrPropExtDbTables.bat script to create the tables in the property extension repository.

Run the script from the following location or from the directory to which you previously copied the script file:

AIX, HP-UX, Linux, and Solaris operating systems: app_server_root/etc/wim/setup/bin/createIdMgrPropExtDbTables.sh
Windows: app_server_root\etc\wim\setup\bin\createIdMgrPropExtDbTables.bat

Use the following parameters to specify the values that you require when you run the script:

-b

Use this parameter to specify the home directory of the database.

This value is a string value that is required for all database types.

-d

Use this parameter to specify the schema of the database.

The value of this parameter should be the same value that you specified for DBSCHEMA (described in the previous section, Specifying the database schema).

This value is a string value that is optional for DB2, Derby, and SQL Server databases, if you want to specify the database schema where you want to create the federated repository tables. This value is not required for Oracle and Informix databases.

-h

Use this parameter to display the help information. (Optional)

-i

Use this parameter to specify the home directory of the database instance.

This value is a string value that is required for a DB2 database only; do not specify a value for other database types.

This parameter applies to the AIX, HP-UX, Linux, and Solaris operating systems.

-n

Use this parameter to specify the name of the database to which you are connecting.

For an Oracle database, the value of the ORACLE_SID variable is the same as the name of the database.

This value is a string value that is required for all database types.

-p

Use this parameter to specify the password of the database administrator.

This value is a string value that is required for DB2, Oracle, Informix, and SQL Server databases only; do not specify a value for a Derby database.

-s

On AIX, HP-UX, Linux, and Solaris operating systems, this parameter specifies the location of the app_server_root/etc/wim/setup directory, or the location to which the updated files are copied according to the steps in the previous section, Specifying the database schema.

On Windows operating systems, this parameter specifies the location of the app_server_root\etc\wim\setup directory, or the location to which the updated files are copied according to the steps in the previous section, Specifying the database schema.

This value is a string value that is required for all database types.

-t

Use this parameter to specify a database type.

On the AIX, HP-UX, Linux, and Solaris operating systems, specify one of the following valid values: db2, oracle, informix, derby.
On the Windows operating systems, specify one of the following valid values: db2, oracle, informix, derby, or sqlserver.

This value is a string value that is required for all database types.

-u

Use this parameter to specify the user ID of the database administrator.

This value is a string value that is required for DB2, Oracle, Informix, and SQL Server databases only; do not specify a value for a Derby database.

Example

Run the appropriate script for your database and operating system to create tables in the property extension repository. Use the sample values to specify database parameters. If the database exists on a system where WebSphere Application Server is not installed, the following examples assume that your PATH variable includes an entry for the location to which you copied the script files. For the AIX, HP-UX, Linux, and Solaris operating systems, the entry might be the app_server_root/etc/wim/setup/bin/ or the /setup/bin/ directory. For Windows operating systems, the entry might be the app_server_root\etc\wim\setup\bin\ or the \setup\bin\ directory.

The examples in the following section are organized into multiple lines for illustration purposes only.

On the AIX, HP-UX, Linux, and Solaris operating systems:

Oracle databases

createIdMgrPropExtDbTables.sh 
-b /space/oracle/product/10.2.0/Db_1/ 
-n orcl 
-u system 
-p manager 
-s /opt/IBM/WebSphere/AppServer1/etc/wim/setup 
-t oracle

Informix databases

createIdMgrPropExtDbTables.sh 
-b /opt/IBM/informix/ 
-n demo_on 
-u informix 
-p informix 
-s /opt/IBM/WebSphere/AppServer/etc/wim/setup 
-t informix

DB2 databases

createIdMgrPropExtDbTables.sh 
-b /opt/ibm/db2/V9.1/ 
-n db2inst1 
-p db2inst1  
-s /opt/IBM/WebSphere/AppServer/etc/wim/setup 
-t DB2 
-u db2inst1 
-i /home/db2inst1/

Derby databases

createIdMgrPropExtDbTables.sh 
-b /opt/ibm/derby/ 
-n test11 
-s /opt/IBM/WebSphere/AppServer/etc/wim/setup 
-t derby

On the Windows operating systems:

Oracle databases

createIdMgrPropExtDbTables.bat 
-b "c:\oracle\product\10.2.0\Db_1" 
-n orcl 
-u system 
-p manager 
-s "c:\Program Files\IBM\WebSphere\AppServer1\etc\wim\setup" 
-t oracle

Informix databases

createIdMgrPropExtDbTables.bat 
-b "c:\Program Files\IBM\informix" 
-n demo_on 
-u informix 
-p informix 
-s "c:\Program Files\IBM\WebSphere\AppServer\etc\wim\setup" 
-t informix

DB2 databases

createIdMgrPropExtDbTables.bat 
-t db2 
-u db2admin 
-p sec001ret# 
-n test23 
-b "c:\Program Files\IBM\SQLLIB" 
-s "c:\Program Files\IBM\WebSphere\AppServer1\etc\wim\setup"

Derby databases

createIdMgrPropExtDbTables.bat 
-t derby 
-b "c:\Derby" 
-n test11 
-s "c:\Program Files\IBM\WebSphere\AppServer1\etc\wim\setup"

Microsoft SQL Server databases

createIdMgrPropExtDbTables.bat 
-t sqlserver 
-u sa 
-p sec001ret# 
-n sqlsrv
-b "c:\Progra~1\Micros~1\90\Tools" 
-s "C:\Progra~1\IBM\WebSphere\AppServer1\etc\wim\setup"

What to do next

Run the setupIdMgrPropertyExtensionRepositoryTables command with the skipDBCreation parameter set to true to populate the tables that are created. For more information, read about setting up an entry mapping repository, a property extension repository, or a custom registry database repository using wsadmin commands.