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:
db2stop
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:
DB2_install_path/sqllib/bin/db2cc
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
db2 "UPDATE DB CFG FOR $MyInstance USING APPLHEAPSZ 4096 STMTHEAP 16000 DBHEAP 4200 SORTHEAP 2500 LOGFILSIZ 16000 LOGSECOND 12
UTIL_HEAP_SZ 10000 CATALOGCACHE_SZ 96 LOCKLIST 1000 APP_CTL_HEAP_SZ 512
STAT_HEAP_SZ 5022 NUM_IOCLEANERS 2 MAXAPPLS 200"
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
# HOW TO INIT THE NEW DB2 INSTANCE FOR INSTALL
set -x
MyInstance=DBLCA
MyDbDir=/home/data/db2adm/$MyInstance
# 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 "UPDATE DB CFG FOR $MyInstance USING APPLHEAPSZ 4096 STMTHEAP 16000 DBHEAP 4200 SORTHEAP 2500 LOGFILSIZ 16000 LOGSECOND 12
UTIL_HEAP_SZ 10000 CATALOGCACHE_SZ 96 LOCKLIST 1000 APP_CTL_HEAP_SZ 512 STAT_HEAP_SZ 5022 NUM_IOCLEANERS 2 MAXAPPLS 200 "
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
then:
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.
- 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 VPMDBIf 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)
- 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
- 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:
db2set DB2_HASH_JOIN=ON
db2stop;
db2start;
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.
