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 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 MY_DATABASE USING APPLHEAPSZ 4096
STMTHEAP 16000 DBHEAP 4200
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 ENOVIAV5VPM 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 PLMDBSetup 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 create bufferpool BP32K size 3000 pagesize 32768
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"
db2 "create tablespace ENOTBS32 pagesize 32K managed by system
USING('$MyDbDir/ENOTBS.dbf') bufferpool BP32K"
db2 "create tablespace ENOTBS32 pagesize 32K managed by system USING('$MyDbDir/ENOTBS32.dbf') bufferpool BP32K"
# 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 MY_DATABASE USING APPLHEAPSZ 4096
STMTHEAP 16000 DBHEAP 4200
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 ENOVIAV5VPM 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.
DB2 Performance Considerations
ENOVIAV5VPM 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 ENOVIAV5VPM 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 ENOVIAV5VPM 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 ENOVIAV5VPM 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
ENOVIAV5VPM 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 ENOVIAV5VPM 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 ENOVIAV5VPM 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.
