Creating a New DB2 Database Using a SQL Script

​

To create a new DB2 database using a SQL script

1. Create a user for the new database. For example, to create a user named csuser on Linux:

   useradd -d /home/csuser -m -p welcome1 csuser  

2. Log in with DB2 instance owner credentials. For example, db2inst1.
3. Create a file with the following DB2 commands. (For example, create db.sql and modify the database name, path, and user variables to match your installation):

   CREATE DATABASE <DBNAME> AUTOMATIC STORAGE YES ON '<DB2_HOME>/Databases/<DBNAME>'   DBPATH ON '<DB2_HOME>/Databases/<DBNAME>'   USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768;  CONNECT TO <DBNAME>;  GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER <DBUSER>;  UPDATE DATABASE CONFIGURATION USING APPLHEAPSZ 1024 DEFERRED;  UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 30 DEFERRED;  UPDATE DATABASE CONFIGURATION USING APP_CTL_HEAP_SZ 1024 DEFERRED;  UPDATE DATABASE CONFIGURATION USING LOGFILSIZ 32768 DEFERRED;  UPDATE DATABASE CONFIGURATION USING LOGSECOND 8 IMMEDIATE ;  CONNECT RESET;  

4. Create the directory for your database.

   mkdir -p <DB2_HOME>/Databases/<DBNAME>  

5. Execute the sql script. For example:

   db2 -tvsf createdb.sql  

​