Manually set up the property extension repository for federated repositories

Manually set up the property extension repository for federated repositories

We can use the createIdMgrPropExtDbTables script to create tables in the property extension repository for federated repositories.
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 we do not have WAS installed on the same system on which we are setting up the database, copy the following files from a system where WAS is installed to the system on which we are setting up the database. 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

Database schema:
We can specify the database schema where to create the federated repository tables when we are manually setting up the property extension repository.
To use the default schema of the database, 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 WAS 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.

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.

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 notes apply to specific databases:

Oracle 11g

If we did not create the default database when you installed Oracle product, you must manually create the database before running the createIdMgrPropExtDbTables script. The value of the ORACLE_SID variable is the same value as the name of the database.

To create the tables in the schema that specified using DBSCHEMA (described in the previous section, Specifying the database schema) ensure that we create the specified schema in this database before running 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 running 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 WAS 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

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

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 Home directory of the database. This value is a string value required for all database types.
-d Schema of the database. The value of this parameter should be the same value that 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, to specify the database schema where to create the federated repository tables. Not required for Oracle and Informix databases.
-h Display the help information. (Optional)
-i Home directory of the database instance. This value is a string value 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 Name of the database to which we 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 required for all database types.
-p Password of the database administrator. This value is a string value 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 required for all database types.
-t 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 required for all database types.
-u User ID of the database administrator. This value is a string value 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 the 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 the 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 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.

Related tasks
Manually set up the property extension repository for DB2 for iSeries or DB2 for z/OS

Related information:
WebSphere Application Server detailed system requirements

-b	Home directory of the database. This value is a string value required for all database types.
-d	Schema of the database. The value of this parameter should be the same value that 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, to specify the database schema where to create the federated repository tables. Not required for Oracle and Informix databases.
-h	Display the help information. (Optional)
-i	Home directory of the database instance. This value is a string value 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	Name of the database to which we 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 required for all database types.
-p	Password of the database administrator. This value is a string value 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 required for all database types.
-t	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 required for all database types.
-u	User ID of the database administrator. This value is a string value required for DB2, Oracle, Informix, and SQL Server databases only; do not specify a value for a Derby database.