Use a Script to Create a DB2 Database

vSphere 4.1 - ESX and vCenter > ESX and vCenter Server Installation Guide > vCenter Server Databases > Configure DB2 Databases

 

Use a Script to Create a DB2 Database

When you use a DB2 database with vCenter Server, the database must have certain buffer pools, table spaces, and privileges. To simplify the process of creating the database, you can run a DB2 script.

Prerequisites

■	Configure an IBM DB2 database user and group.
■	Add the database instance registry variables.
■	Add the client instance registry variable.

Procedure

1

Copy the following DB2 script into a text editor and save it with a descriptive filename, such as vcdbcreate.sql.

The script is located in the /<installation directory>/vpx/dbschema/db2_prereq_connection_configuration.txt vCenter Server installation package file.

CREATE DATABASE VCDB 
AUTOMATIC STORAGE YES ON 'C:\' 
DBPATH ON 'C:\' USING CODESET UTF-8 
TERRITORY US 
COLLATE USING SYSTEM PAGESIZE 4096;

UPDATE DB CFG FOR VCDB USING AUTO_MAINT ON;
UPDATE DB CFG FOR VCDB USING AUTO_TBL_MAINT ON;
UPDATE DB CFG FOR VCDB USING AUTO_RUNSTATS ON;
UPDATE DB CFG FOR VCDB USING logprimary 32 logsecond 6 logfilsiz 2048;
UPDATE ALERT CFG FOR DATABASE ON VCDB USING db.db_backup_req SET THRESHOLDSCHECKED YES;
UPDATE ALERT CFG FOR DATABASE ON VCDB USING db.tb_reorg_req SET THRESHOLDSCHECKED YES;
UPDATE ALERT CFG FOR DATABASE ON VCDB USING db.tb_runstats_req SET THRESHOLDSCHECKED YES;

CONNECT TO VCDB;
grant select on sysibmadm.applications to user vcx;
CREATE BUFFERPOOL VCBP_8K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 8K;
CREATE LARGE TABLESPACE VCTS_8k PAGESIZE 8K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 12.67 PREFETCHSIZE 32 TRANSFERRATE 0.18 BUFFERPOOL VCBP_8K;
CREATE BUFFERPOOL VCBP_16K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 16K;
CREATE LARGE TABLESPACE VCTS_16k PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 12.67 PREFETCHSIZE 32 TRANSFERRATE 0.18 BUFFERPOOL VCBP_16K;
CREATE BUFFERPOOL VCBP_32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K;
CREATE LARGE TABLESPACE VCTS_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 12.67 PREFETCHSIZE 32 TRANSFERRATE 0.18 BUFFERPOOL VCBP_32K;
CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4;
CREATE USER TEMPORARY TABLESPACE SYSTOOLSTMPSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4;
CREATE SYSTEM TEMPORARY TABLESPACE VCTEMPTS_8K PAGESIZE 8K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL VCBP_8K;
CREATE SYSTEM TEMPORARY TABLESPACE VCTEMPTS_16K PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL VCBP_16K;
CREATE SYSTEM TEMPORARY TABLESPACE VCTEMPTS_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL VCBP_32K;

GRANT USE OF TABLESPACE VCTS_16K TO USER vcx WITH GRANT OPTION;
GRANT USE OF TABLESPACE VCTS_32K TO USER vcx WITH GRANT OPTION;
GRANT USE OF TABLESPACE VCTS_8K TO USER vcx WITH GRANT OPTION;

commit work;
connect reset; 
terminate;

2

Customize the following values in the script.

■	Database name: VCDB. You must use the same value for the ODBC setup.
■	Database path: C:\ for Microsoft Windows, or a UNIX path with sufficient permissions.
■	User name: vcx. You must use the same value for the ODBC setup.

Do not modify the script in any other way. Changing the setup for table spaces or buffer pools might prevent successful installation of vCenter Server.

3

Run the script in a DB2 Command window.

db2 -svtf vcdbcreate.sql

You now have a DB2 database that you can use with vCenter Server.

What to do next

Configure a connection to a local or remote database.

Previous topic: Add the Client Instance Registry Variable

Next topic: Use a Script to Create the DB2 Database Schema (Optional)

---

Help us improve this information. Send feedback to [e-mail].

 

 

 

***************************************************************************************8

 

Creating a DB2 database on Linux or UNIX

Resource Center » Administrator Library » Administering geodatabases licensed through ArcGIS Server Enterprise » Geodatabases in DB2 » Setting up a geodatabase in DB2 » Setting up a geodatabase on Linux or UNIX

Geodatabases are collections of tables and procedures in a database. Therefore, a database must be created prior to creating a geodatabase.

For DB2, ArcSDE requires the use of a DB2 global temporary table (DECLARE GLOBAL TEMPORARY TABLE). As per DB2 documentation, you must have SYSADMIN or DBADM privileges or have been granted USE privilege on a USER TEMPORARY table space to declare global temporary tables. A user temporary table space can be created using the DB2 Control Center or from the command line using the CREATE USER TEMPORARY TABLESPACE command. Be sure to create the user temporary table space as a system-managed space (SMS).

Tip:

Though you should use SMS for user temporary table spaces, you should use database-managed space (DMS) table spaces for storing user data.

There are many options for table space storage. See the DB2 Information Center topic "Automatic storage table spaces" for more information.

The following steps contain an example script to create a DB2 database, create a user temporary table space, and grant the use of all table spaces to PUBLIC.

Steps:

1. Create a script to create the database.
   Note:

   The name of a database used to store a geodatabase cannot contain special characters.

   The following is an example script. Replace variables such as database name, password names, and container names and paths to match the information needed at your site.

   db2 -tvf <script.clp>
   
   FORCE APPLICATION ALL;
   
   DB2STOP;
   
   DB2START;
   
   CREATE DATABASE mysdedb USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM USER TABLESPACE MANAGED BY DATABASE USING (FILE '/db2_data/mysdedb/sdetbsp' 51200);
   
   CONNECT TO mysdedb user db2admin using <your password>;
   
   CREATE REGULAR TABLESPACE regtbs PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE '/db2_data/mysdedb/regtbs' 125000);
   
   CREATE LONG TABLESPACE lobtbs PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE '/db2_data/mysdedb/lobtbs' 51200);
   
   CREATE USER TEMPORARY TABLESPACE sdespace PAGESIZE 4 K MANAGED BY SYSTEM USING ('/db2_data/mysdedb/sdespace' );
   
   GRANT USE OF TABLESPACE regtbs TO PUBLIC;
   
   GRANT USE OF TABLESPACE lobtbs TO PUBLIC;
   
   GRANT USE OF TABLESPACE sdespace TO PUBLIC;
   
   GRANT DBADM ON DATABASE mysdedb TO USER SDE;
   
   UPDATE DATABASE CONFIGURATION FOR mysdedb USING APP_CTL_HEAP_SZ 2048;
   
   UPDATE DATABASE CONFIGURATION FOR mysdedb USING APPLHEAPSZ 2048;
   
   UPDATE DATABASE CONFIGURATION FOR mysdedb USING LOGPRIMARY 10;
   
   FORCE APPLICATION ALL;
   
   DB2STOP FORCE;
   
   DB2START;

2. Create a user temporary table space as a system-managed space (SMS).

   For example:

   CREATE USER TEMPORARY TABLESPACE sdespace PAGESIZE 4 K MANAGED BY SYSTEM USING ('d:\db2_data\sdespace' );
   
   COMMENT ON TABLESPACE sdespace IS '';

3. Grant the use of all table spaces to PUBLIC.

   GRANT USE OF TABLESPACE regtbs TO PUBLIC;