Steps to Install IBM DB2

Note: Install the database according to the vendor's instructions. Be sure to note the database installation user name and password for use later in the installation process.

Configure DB2

Several database settings must be configured prior to creating the OIPA databases. To configure the database settings, use the db2 and db2set commands for DB2:

Global Settings

db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES
db2set DB2_REDUCED_OPTIMIZATION=TRUE
db2set DB2_EVALUNCOMMITTED=TRUE
db2set DB2_CORRELATED_PREDICATES=YES
db2set DB2_SKIPINSERTED=YES
db2set DB2_SKIPDELETED=YES
db2set DB2_COMPATIBILITY_VECTOR=MYS

DBM Changes

db2 update dbm cfg using sheapthres 120000
db2 update dbm cfg using mon_heap_sz 256
db2 update dbm cfg using query_heap_sz 2048

Configure Environment to enable Database Encryption

  1. PATH and LD_LIBRARY_PATH variables needs to be set as below in the file $HOME/sqllib/db2profile.

  2. Find “export LD_LIBRARY_PATH” and change the line as below in $HOME/sqllib/db2profile

    3. export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib64/gskit

  3. Find “export PATH” and change the line as below in $HOME/sqllib/db2profile

    4. export PATH=$PATH:/opt/ibm/db2/V11.1/gskit/bin

  4. Import the variables defined in $HOME/sqllib/db2profile by issuing the below command

    5. . $HOME/sqllib/db2profile

  5. Log in as the Db2® instance owner, and then create the local keystore by executing the gsk8capicmd_64 command

    6. gsk8capicmd_64 -keydb -create -db "<<location where keystore will be created>>/oipa.p12" -pw "<<password>>" -type pkcs12

  6. Set two database manager configuration parameters: keystore_type and keystore_location.

    7. db2 update dbm cfg using keystore_type pkcs12 keystore_location <<Keystore location>>/oipa.p12

  7. Restart the database manager instance

    8. db2stop force
    db2start open keystore using "<<same password which was used when keystore was created in the above>>"

    Note: Make sure all the above referenced paths exist

Create the Database

A database with two schemas will need to be created; one schema for OIPA data and one for IVS data. Use the db2 create command to create the database.

db2create

db2 create database OIPA encrypt.

Create Users

Three database users must be created:

  • OIPA_PAS - OIPA user with full privileges
  • OIPA_RO - OIPA user with read-only privileges
  • OIPA_IVS - IVS user with full privileges

Since DB2 uses the operating system for authentication, these users must first be created at the operating system level. Please consult the operating system documentation for creating users.

Configure the Databases

The database settings for the database must be configured.

db2 connect to OIPA
db2 update db cfg using dbheap 2400
db2 update db cfg using logbufsz 512
db2 update db cfg using locklist 10000
db2 update db cfg using app_ctl_heap_sz 256
db2 update db cfg using sortheap 1024
db2 update db cfg using applheapsz 4096
db2 update db cfg using locktimeout 360
db2 update db cfg using maxlocks 76
db2 update db cfg using chngpgs_thresh 30
db2 update db cfg using num_iocleaners 7
db2 update db cfg using num_ioservers 7
db2 update db cfg using logfilsiz 20000
db2 update db cfg using logprimary 30
db2 update db cfg using logsecond 0
db2 update db cfg using pckcachesz 1024
db2 update db cfg using catalogcache_sz 512
db2 update db cfg using maxfilop 256
db2 update db cfg using maxappls 60
db2 update db cfg using avg_appls 1
db2 update db cfg using PCKCACHESZ 2048
db2 update db cfg using SORTHEAP 512
db2 update db cfg using dft_queryopt 3

Prepare the DDL Script

The db2look_oipa_pas.ddl and the db2look_oipa_ivs.ddl files must be edited to include the fully-qualified path names for each table space creation command.

Create the Schema Using the DDL Script

The db2look_oipa_pas.dll file and the db2look_oipa_ivs.ddl file, which were modified in the previous step, will now be used to create the database schemas.

db2 -tvf <ddl_file> > <create_schema_log>
##Example: db2 -tvf db2look_oipa_pas.ddl >db2look_oipa_pas.log
db2 -tvf db2look_oipa_ivs.ddl >db2look_oipa_ivs.log

After execution has completed, reference the created log files, db2look_oipa_pas.log and db2look_oipa_ivs.log, to ensure the schemas were successfully created.

Load the Database Data

The db2move command will be used to load the data into the database.

To load the OIPA database, first ensure that you are currently in the directory that contains the OIPA database data from the Oracle Insurance Policy Administration Media Pack.

To load the IVS database, first ensure that you are currently in the directory that contains the IVS database data from the Oracle Insurance Policy Administration Media Pack.

db2move <dbname> load -lo replace <lobpath file>
##Example: db2move OIPA load -lo replace > OIPA.log
db2move <dbname> load -lo replace <lobpath file>
##Example: db2move IVS load -lo replace > IVS.log

Import Client Data

After the database load is complete, run the following command to insert the required AsClient records.

db2 load from tab<number>.ixf

Where ixf modified by generatedignore is replaced into "<schema>". ASCLIENTwhere tab<number>.ixf is corresponding ixf file for ASCLIENT, which is listed right alongside ASCLIENT in db2move.lst file

Encrypting existing database

If the database is already existing and needs to be encrypted, follow the below steps. Make sure no other applications are connected to the database instance.

Also make sure that, the environment is already set for database encryption, refer to Configure Environment to enable Database Encryption section above.

  1. Issue the below command to take a backup of the existing database

    2. db2 backup database <<database_name>>

  2. Drop the existing database

    3. db2 drop database <<database_name>>

  3. Restore the database as an encrypted one

    4. db2 restore database <<database_name>> encrypt

  4. Check whether the database is encrypted now

    5. db2 get db cfg for <<database name>> | grep Encrypted

Performance Recommendations

Cycle Recommendations:

For optimum Client level cycle(07) activity processing following indexes needs to be applied:

S.No. Coumn Name Table Name
1 ACTIVITYGUID ASACTIVITYSEQUENCEDETAIL
2 CLIENTRELATIONSHIPPARENTGUID ASCLIENTRELATIONSHIP
3 CLIENTGUID ASCYCLE

Redo Log File Sizing:

In-order to get optimum performance, the redo log file should be sized in such a way that there should be around five log file switches per hour.