How to Create a DB2 database in an aix Environment

Creating a DB2 database should be a required knowledge for every DB2 user. The following describes how to create a DB2 database in the aix environment. If you have encountered any problems in creating a DB2 database, take a look.

View the environment variable db2set
If the db2codepage in db2set is not 819, run

 
 

  
  
db2set db2codepage=819      
  
  
db2 create database test05 on /home/db2inst1   
  
  
db2 connect to test05 user db2inst1 using db2 
 
 

Create a buffer pool and tablespace corresponding to pagesize

 
 

  
  
Db2 CREATE Bufferpool USER8 SIZE 1000 PAGESIZE 8 K
  
  
Db2 CREATE Bufferpool USER16 SIZE 1000 PAGESIZE 16 K
  
  
Db2 CREATE Bufferpool USER32 SIZE 1000 PAGESIZE 32 K
  
  
Db2stop force (you can use: db2stop and db2 terminate to stop)
  
  
Db2start
  
  
Db2 connect to test05 user db2inst1 using db2
  
  
Db2 "create regular tablespace TS_USER8 PAGESIZE 8 k managed by system using ('fsms _ 8K_1 ') BUFFERPOOL USER8"
  
  
Db2 "create regular tablespace TS_USER16 PAGESIZE 16 k managed by system using ('fsms _ 16K_1 ') BUFFERPOOL USER16"
  
  
Db2 "create regular tablespace TS_USER32 PAGESIZE 32 k managed by system using ('fsms _ 32K_1 ') BUFFERPOOL USER32"
 
 

Create temporary tablespace (used to buffer query results or temporary tables)

 
 

  
  
db2 CREATE Bufferpool TMPBUF8 SIZE 10000 PAGESIZE 8K   
  
  
db2 "CREATE   TEMPORARY   TABLESPACE TempTS_USER8 PAGESIZE 8K   MANAGED BY SYSTEM   USING ('/home/db2inst1/db2inst1/TEMPNODE0005'   )   EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL "TMPBUF8" DROPPED TABLE RECOVERY OFF" 
 
 

Grant permissions to relevant users

 
 

  
  
db2 GRANT   DBADM,CREATETAB,BINDADD,CONNECT,LOAD ON DATABASE   TO USER EASYMIS  
  
  
db2 GRANT USE OF TABLESPACE TS_USER8 TO easymis  
  
  
db2 GRANT USE OF TABLESPACE TS_USER16 TO easymis  
  
  
db2 GRANT USE OF TABLESPACE TS_USER32 TO easymis 
 
 

You can directly create tables and views by using the db2move method)

 
 

  
  
db2 connect to test05 user easymis using easymis  
  
  
db2 -f dbstruct.sql >crttab.log  
  
  
db2 -f view.sql   >crtviw.log 
 
 

Restore the database backed up by db2move (for the backup process, see the db2move description later)

 
 

  
  
Db2move test05 import-l/home/db2inst1/wk/test04
  
  
Db2-tvf view. SQL> crtview. log # If the script is db2look, the command Terminator is a semicolon and there is no special symbol for the end of the line.
  
  
 
  
  
Db2 update db cfg for easytest using applheapsz 1024
 
 

The preceding steps are used to create a DB2 database in the aix environment.

Nine common DB2 commands

How to Create a tablespace in DB2 under aix

How to Create a tablespace in DB2 in windows

How to delete tables with large data volumes in DB2

DB2 index design guidelines