Setting Up Databases for the ENOVIA Version 5 Server on UNIX

This section explains how to create a database for ENOVIA V5 VPM using DB2 and Oracle.

DB2 Databases

Starting and Stopping DB2 Databases

A DB2 server needs to be active, and you need to create at least one database before starting the installation.

To start the DB2 server, enter the commands:

su - db2adm

where "db2adm" is the DB2 administrator logon, then:


To check whether a DB2 server is already running, enter the command:

ps -fu db2adm

To create a simple database, enter the command:

db2 "create database base_name COLLATE USING IDENTITY"

By default, the database is created in the home directory of user "db2adm".

To stop the DB2 server, enter the command:

su - db2adm 

where "db2adm" is the DB2 administrator logon, then:



Setting Up Databases on DB2

Using the Control Center

You can create databases using the DB2 Control Center which works the same way as already illustrated on Windows as described in Setting Up Databases for the ENOVIA Version 5 Server on Windows. To start the Control Center, export your display then, still logged on as database administrator, enter the command:


Using a Shell Script

Experienced administrators may prefer to use a shell script to create databases. The default installation illustrated in this guide was based on a DB2 database created using the shell script illustrated below.

Pay particular attention to the end of the shell which contains an example of how to set up database parameters properly for the new instance you are creating:

# Set up properly database parameters for new instance


These values are the minimum recommended values which will allow normal use of ENOVIA V5 VPM at installation.  For example, STMTHEAP is set to 16000.

This is the whole shell:

#! /bin/ksh


set -x



# When reinstalling, you first have to drop the database

#db2 drop db $MyInstance

# Add new database on server for enoviadbsetup step

mkdir $MyDbDir

db2 "create db $MyInstance ON '$MyDbDir' COLLATE USING IDENTITY"

# Add new bufferpool for new tbs

db2 connect to $MyInstance

db2 create bufferpool BP8K size 3000 pagesize 8192

db2 terminate

# Add new tbs on previously created bufferpool

db2 connect to $MyInstance

db2 "create tablespace ENOTBS pagesize 8K managed by system USING ('$MyDbDir/ENOTBS.dbf') bufferpool BP8K"

# You can also create a tablespace spread over several file systems by customizing the following

#db2 create tablespace TBS1_8K pagesize 8K managed by system
#USING ('/vpmdb_fs1', '/vpmdb_fs2', '/vpmdb_fs3')
#bufferpool VPM_BP1

# Set up properly database parameters for new instance


db2 terminate

Note about the LOGFILSIZ Parameter

If you use the database configuration parameters documented above, during an ENOVIA V5 VPM installation, the ENOVIA database will be set up correctly, and the default security mask will be imported. However, if you neglect to set the LOGFILESIZ parameter correctly, the default security mask import step may fail.

If a problem with the default mask is detected, the following error message:

Although commit succeeded, mask was not created - Check Database configuration

means that the mask was not created, but no problem occurred during the commit phase. It may be because the database was not created using the LOGFILSIZ parameter. In this case, update the database configuration using the correct LOGFILSIZ parameter value and reimport the mask using the command VPMPeopleUpdate -m as explained in "People, Organization and Security Tools" in your Enterprise Architecture Administration Guide.


Running a Database and Vault Server on the Same Workstation on AIX

When you have a vault server and a DB2 database running on the same workstation on AIX, the number of connections for the vault server database connection pool (connections between the java vault server process and the database) is limited. To bypass this problem, you must catalog your database using the following commands:

CATALOG TCPIP NODE node-name REMOTE hostname SERVER service-name


CATALOG DATABASE database-name AT NODE node-name

If you already cataloged your database, but not exactly as above, uncatalog it then catalog it again as above.


Solaris server - Kernel Parameters

The DB2 server may crash while ENOVIA V5 VPM is running, if the Solaris kernel parameters are not updated as per the DB2 installation instructions.

Please refer to the DB2 Quick Beginnings for Unix for the complete list of parameters to modify, as the required settings depend on the amount of memory available on your server. 


DB2 Performance Considerations

ENOVIA V5 VPM has been developed to work optimally with DB2. However to take full advantage of the hardware available, a minimum amount of configuration may be necessary. 

The most important considerations consist in allocating enough BUFFERPOOL space, and in updating the database statistics by running the db2 RUNSTATS command. 

Note: each ENOVIA V5 VPM installation may be unique due to a specific hardware configuration and system load (and that is no universal configuration that will be optimal). However, it is recommended to start with a simple configuration and tune it over time, using the input provided by the System Monitor tool. 

Refer to the DB2 System Monitor Guide and Reference for information on monitoring the performance of a DB2 database.

  1. Bufferpool sizing

This parameter is often the single most important factor affecting performance. A bufferpool is a cache used for reading/writing database pages during SQL operations. In the ideal situation, the bufferpools will be large enough so that all the data read by ENOVIA V5 VPM stays resident in memory, therefore eliminating most I/O. To determine if your bufferpool(s) is/are performing well, you can use the following monitor commands:

db2 update monitor switches using bufferpool on # ask DB2 to # monitor bufferpool activity
... run ENOVIA V5 VPM for a while, under high load ...
db2 get snapshot for bufferpools on VPMDB

If the ratio between the number of data and index LOGICAL reads, and the number of data and index PHYSICAL read is very large, then a bufferpool is performing well and is large enough.

A logical read is a request for reading a page, a physical read is reading this page from disk. Once all required pages have been read in the bufferpool, no more physical reads are required. You should allocate as much space as necessary or, if possible, to the bufferpool(s). You may want to allocate as much as half the available memory to the bufferpool(s).

To change the size of a bufferpool:

db2 connect to VPMDB
db2 alter bufferpool IBMDEFAULTBP size 10000 # 40 Megabytes
db2 alter bufferpool VPM_BP1 size 125000 # 1 Gygabyte (8K pages)

  1. Update the database statistics

ENOVIA V5 VPM issues dynamic SQL to the DB2 server. This type of SQL is compiled at run-time by the DBMS. The DB2 SQL optimizer will choose what it considers to be the optimal Access Plan based on the statistics existing on the database. It is very important for these statistics to be updated after the database has grown, because an access plan that is optimal with a table that contains for example 100 rows, may become catastrophic from a performance point-of-view for a table that contains 100000 rows.

You should update the statistics every time the ENOVIA V5 VPM data have been considerably modified, such as after importing data into the database.

db2 reorgchk update statistics on table all

  1. Other configuration parameters improving performance

The following parameters are recommended as they may contribute to improve the performance of an ENOVIA V5 VPM installation:

Database registry:


Note: to avoid errors occurring when saving parts, we recommend that you increase DB2 database APP_CTL_HEAP_SZ. The default is 128. Increase it to 512 to avoid the problem.

Oracle Databases

This section explains how to create a database for ENOVIA V5 VPM using Oracle. An Oracle server needs to be active, and you need to create at least one database before starting the installation.

Before launching the ENOVIA V5 VPM installation process on Oracle, the following operations must be performed:

  • The database administrator must have access to the SQLNet, SQLPLUS and Pro*C products. The ENOVIA V5 installation will use this product during processing.
  • Verify that the library named "libclntsh" is created (if it doesn't exist, you must build it). This library can be invoked from an ENOVIA V5  process, so it must be known in the current environment.
  • Verify that the database and the listener are started.

Note: because the installation on a remote database does not allow the creation of a new ENOTBS tablespace for storing ENOVIA V5 VPM tables, create it beforehand using the following procedure:

su - ora10g
sqlplus system/manager@my_database_name
create tablespace ENOTBS datafile '$MyPathTablespace/ENOTBS.dbf' SIZE 70M AUTOEXTEND ON ;

Oracle Library Error

The following error may occur:

Cannot open library libOracleGLUEV9.a

This error will occur with the ENOVIA VPM Product Editor (VPC product) installed on Oracle 10 level database unless certain steps are taken before ENOVIA V5 VPM is activated. The installation of ENOVIA V5 VPM will work but both the ENOVIA VPM Product Editor client and VPMPeopleEdit will fail.

The following steps are to be taken to correct a bug in Oracle 10:

  1. Install service Pack 2 of Oracle 10.
  2. Login as root and go to the Oracle home directory, and to the "install" path under it.
  3. Run the command:

    ./ -o /path

    where /path is the ORACLE_HOME value for the Oracle installation that needs to be corrected.

  4. Run the command:

    echo $?

    to verify that changePerm completed with a good return code. If the answer to this value is not zero, stop and correct the problem.

  5. Run the command:


    to go to the Oracle home directory.

  6. Run the command:

chmod -R 755 lib32

Setting Up a Database Using Oracle

Your workstation needs to be an Oracle server, and the Oracle server must be running.

  1. Once your computer has been set up as an Oracle server, open a shell window and log on as Oracle database administrator.

    For example, run the command:

    su - ora10g

    where "ora10g" is the Oracle database administrator userid.

  2. Go to the following Oracle directory like this:

    cd oracle_homedirectory/bin

    where "oracle_homedirectory" is the directory where Oracle is installed.

  3. Enter the command:


    to start the Database Configuration Assistant for Oracle. Once the assistant has been started, you can create databases for Oracle the same way as already illustrated on Windows as described in Setting Up a Database Using Oracle.



Instance Maintenance

For reasons of general query performance (response time improvements of up to 70 %), it is important to regularly perform an analysis of the database. Due to the naming convention adopted by ENOVIA (differentiation by capital and lowercase letters) the procedure DBMS_UTILITY.ANALYZE_SCHEMA() cannot be used. The following routine should be used in its place and should be executed in the VPM schema (the variable <schema>   found in the below script):

/* Set parameters for analysis script */
set echo off
set header off
set pagesize off
set feedback off
set linesize 255
/* Open script file */
spool analyze.sql
/* Create script in the file using a SELECT */
select 'ANALYZE TABLE <schema>."' || table_name || '" COMPUTE STATISTICS;' from user_tables;
/* Close script file */
spool off
/* Execution of script file */

The above script should be added to a file, for example creana.sql, and executed in an SQLPLUS session corresponding to a user having access to the database schema to be analyzed. The frequency with which the above code should be executed depends on the volume of updates to the database (commands INSERT, UPDATE, DELETE).

For more information about Oracle administration, see the Oracle10 Server Administrator's Guide.


Problems with the Fetched Query Function in ENOVIA V5 VPM

The way DB2 and Oracle databases are configured may cause problems with some ENOVIA V5 VPM functionalities (for example, fetched query).

This is due to the fact that the databases are configured to sort data according to the locale. The problem is that the result obtained after a sort from DB2 is different from a sort with Oracle which is different from a sort in the server. The only combination that actually works is to have everybody do a binary sort.

Therefore we strongly recommend configuring the database as explained below.

NOTE: There is no absolute emergency to do it. The only impact is weird behavior of the fetch-query when using characters out of the "a-z 0-9" range.


When creating the database, add the "COLLATE USING IDENTITY" option to your CREATE DATABASE statement.


With DB2, it is not possible to modify this parameter once the database has been created. This means that the only way to change it afterward is to do a full export, drop the DB, recreate it with the proper parameters and do a full import.

Note : A backup/restore won't work as the old parameters will be restored as well.

With your own database, you need to go through the export/import process. We recommend the following:

  1. Get a backup of your database.
  2. Get an exact image of the data structures using the command:

    db2look -d db2name -e -o db2output.clp -a -l -xp -f -P -i userid -w password
  3. Export all data from the database using the command:

    db2move db2name EXPORT
  4. Drop your database.
  5. Recreate it with the proper parameters
  6. Recreate the data structures using the db2output.clp file generated during the step 1.
  7. Import everything using the command:

    db2move db2name IMPORT (or LOAD)


Add the two following parameters to the initSID.ora configuration file (SID stands for the Oracle instance name):