4.5 Creating a DB2 database

-- Create a database, using a custom USERSPACE1 definition 
-- Configures the database for typical LC use. For large number of connections, add: 
--     num_remote_apps 
-- to the AUTOCONFIGURE section 
-- To use: Globally replace (case sensitive) DB_NAME with the name of the database to be created. 
-- Modify the FILE locations based on system configuration 
-- Note: The size of the file has to be specified in pages (4K pages in this case) when used as part of the create database command 
create database DB_NAME using codeset utf-8 territory default 
USER TABLESPACE MANAGED BY DATABASE USING (FILE'C:\Db2\DB_NAME\DATA_4K_1.db2' 8192) AUTORESIZE YES 
AUTOCONFIGURE using workload_type mixed isolation CS admin_priority performance apply DB and DBM; 
-- Create required buffer pools. Let the DB manage the sizes based on load. 
-- Creates 8K pool for LC 
-- Let DB2 manage the sizes based on usage patterns 
connect to DB_NAME; 
CREATE BUFFERPOOL DB_NAME_BP8K IMMEDIATE SIZE AUTOMATIC PAGESIZE 8192; 
ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE AUTOMATIC; 
connect reset; 
-- Create required tablespaces for tables requiring 8K and 32K pools. 
connect to DB_NAME; 
-- 8K tablespace used by a number of tables. 
-- Note that the majority of data is stored in the 4K tablespace (created as part of the databse create) 
CREATE TEMPORARY TABLESPACE DB_NAME_TEMP_8K IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 8192 MANAGED BY SYSTEM 
USING ('C:\Db2\DB_NAME\TEMP_8K') PREFETCHSIZE AUTOMATIC BUFFERPOOL DB_NAME_BP8K; 
-- Start off large enough to allow a reasonable test to run without needing to extend the space 
-- Should be modified to fit expected usage patterns 
CREATE LARGE TABLESPACE DB_NAME_DATA_8K IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY 
DATABASE USING (FILE'C:\Db2\DB_NAME\DATA_8K.db2'1G) 
PREFETCHSIZE AUTOMATIC BUFFERPOOL DB_NAME_BP8K AUTORESIZE YES INCREASESIZE 1 G; 
commit work; 
connect reset; 
deactivate database DB_NAME; 
activate database DB_NAME;

1. Make the following changes to the script:

   • Replace the instances of dbname and DBNAME with the name you want for the LiveCycle database.

   • If you are creating multiple database instances on the same host, create an uniquely named buffer pool (for example, BP8K_1) for each database instance, using a buffer pool size such that all will not exceed 10% of total physical memory. For example, on a machine with 1GB of physical memory, the total buffer pool size should not exceed 100MB (“SIZE 100000”)

   • Replace DB2_root with the path to the root directory where DB2 is installed.

   • Ensure that no commands include line breaks and each command is terminated by a semicolon (;).

   • Change 9000 in the following line based on your database size:

   (FILE'DB2_root\DBNAME_DATA'9000)

   This number specifies the minimum number of pages required to initialize the database. You can also change this number by using the DB2 administration tools after you initialize the database.

2. Save the text file in a location that DB2 Command Line Processor can access.

3. Open a DB2 command prompt and type the following command to run the script:

db2 -tf <path_to_script_file>/<script_file_name>