Creating a DB2 database on Windows

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.

Steps:

1. Create a script to create the database.

   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.

   Note:

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

   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 'd:\db2_data\mysdedb\regtbs' 102400); 
   
   CREATE REGULAR TABLESPACE idxtbs PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE 'd:\db2_data\mysdedb\idxtbs' 51200); 
   
   CREATE LONG TABLESPACE lobtbs PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE 'd:\db2_data\mysdedb\lobtbs' 51200); 
   
   CREATE USER TEMPORARY TABLESPACE sdespace PAGESIZE 4 K MANAGED BY SYSTEM USING ('d:\db2_data\ sdespace' ); 
   
   GRANT USE OF TABLESPACE regtbs TO PUBLIC;  
   
   GRANT USE OF TABLESPACE lobtbs TO PUBLIC; 
   
   COMMENT ON TABLESPACE sdespace IS ''; 
   
   GRANT DBADM ON DATABASE TO USER SDE; 
   
   UPDATE DATABASE CONFIGURATION FOR mydb USING APP_CTL_HEAP_SZ 2048; 
   
   UPDATE DATABASE CONFIGURATION FOR mydb USING APPLHEAPSZ 2048;  
   
   UPDATE DATABASE CONFIGURATION FOR mydb 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;