Setting up an IBM DB2 database

Installing : Installing Focal Point : Setting up the database : Setting up an IBM DB2 database

1 Install DB2 by following the manufacturer’s instructions.

Focal Point uses the default schema, which has the same name as the database user specified.

The schema name is not configurable, other than using another database user.

2 Create a file named create_focalpoint_db2.sql that contains the SQL code that is shown below (see SQL code for IBM DB2).

3 Change the parameter values to match your environment.

▪ Do not change the table space names.

▪ For each table space, change the path to an appropriate local directory. Make sure that the local directory has read and write permissions.

▪ Based on the server configuration, specify the size for each table space.

▪ Modify the territory name, if required.

▪ Make sure that you have at least 1 GB of disk space. For large installations, you must allocate more space.

▪ If you are executing each SQL code from a command line editor, make sure that the carriage returns are removed.

4 Log on to DB2 as the DB2 administrator.

5 Execute the create_focalpoint_db2.sql file by doing one of these:

Operating system	Do this
Windows	In the command editor, type: create_focalpoint_db2.sql
Linux	In the command editor, type: db2 ‑tvf_SQL_File_path/create_focalpoint_db2.sql

6 When you have configured the database, continue with Setting up the application server.

SQL code for IBM DB2

CREATE DATABASE FOCALDB AUTOMATIC STORAGE YES USING CODESET UTF-8 TERRITORY US PAGESIZE 32 K;

CONNECT TO FOCALDB;

CREATE BUFFERPOOL FPDATABUFF SIZE 2000 AUTOMATIC PAGESIZE 32K;
CREATE BUFFERPOOL FPINDEXBUFF SIZE 1000 AUTOMATIC PAGESIZE 32K;
CREATE BUFFERPOOL FPBIGOBJBUFF SIZE 500 AUTOMATIC PAGESIZE 32K;

CREATE LARGE TABLESPACE FP_DATA PAGESIZE 32K

MANAGED BY DATABASE

--Use one Container per disk per tablespace

USING (FILE 'C:\DB2\fp_data_cont11' 150M, FILE 'C:\DB2\fp_data_cont12' 150M) PREFETCHSIZE AUTOMATIC

BUFFERPOOL FPDATABUFF AUTORESIZE YES ;

CREATE LARGE TABLESPACE FP_INDEX PAGESIZE 32K

MANAGED BY DATABASE

--Use one Container per disk per tablespace

USING (FILE 'C:\DB2\fp_index_cont11' 100M, FILE 'C:\DB2\fp_index_cont12' 100M) PREFETCHSIZE AUTOMATIC

BUFFERPOOL FPINDEXBUFF AUTORESIZE YES ;

CREATE TEMPORARY TABLESPACE FP_BIGOBJ PAGESIZE 32K

MANAGED BY DATABASE

--Use one Container per disk per tablespace

USING (FILE 'C:\DB2\fp_temp_cont11' 100M) PREFETCHSIZE AUTOMATIC BUFFERPOOL FPBIGOBJBUFF AUTORESIZE YES ;

UPDATE db cfg for FOCALDB using logprimary 50; UPDATE db cfg for FOCALDB using logsecond 50; UPDATE db cfg for FOCALDB using logfilsiz 5000;

UPDATE DB CONFIG USING AUTO_STATS_PROF OFF AUTO_PROF_UPD ON AUTO_REORG ON

Important: If you are using DB2 version 10.1 FP3 or later, change AUTO_STATS_PROF to ON:

UPDATE DB CONFIG USING AUTO_STATS_PROF ON AUTO_PROF_UPD ON AUTO_REORG ON