DB2 创建数据库及数据库用户命令行示例

    日常开发工作中，搭建个数据库测试环境的操作虽然很少，但是能掌握高效快捷的搭建过程定会让工作简单不少。

    描述一下数据库搭建的基本要求：

    数据库应用用户
用户名UID组名GID家目录说明evcz1008users100/home/evcz应用管理用户    evcz用户具有的数据库权限：connect,bindadd,createtab,implicit_schema,load  || use of 数据库的 数据表空间 和索引表空间

  

     数据库创建要求

     简单起见，数据文件大小统一设为1GB。evczdbsyscatspace0/db2ffcsdata1/evczdb/syscatspace_c18ktempspace11/db2ffcstmptbs/evczdb/tempspace18kuserspace12/db2ffcsdata1/evczdb/userspace1_c18kassp3/db2ffcsdata1/evczdb/asspspace_c18kassp_index4/db2ffcsdata1/evczdb/idxspace_c18kassp_clob5/db2ffcsdata1/evczdb/clobspace_c132k   BufferPool大小统一设为200MB

IBMDEFAULTBP1所有表空间8kbuffer_assp8k()2assp8kbuffer_idx8k()3assp_index8kbuffer_clob32k()4assp_clob32k

下面是创建符合上述要求的数据库的基本操作：

   1. 创建操作系统用户及相关的目录

useradd -u 1008 -g users -d /home/evcz evczmkdir -pv /db2ffcsdata1/evczdb/mkdir -pv /db2ffcstmptbs/evczdb/chown db2iffcs.db2igfcs -R /db2ffcsdata1/evczdb/chown db2iffcs.db2igfcs -R /db2ffcstmptbs/evczdb/

    2.创建数据库

        简单说明一下，当前DB2实例用户是db2iffcs

         采用脚本方式创建，原因是命令太长，编辑不方便。

[db2iffcs@h15693 ~]$ cat /tmp/db2_create_eassdb_20140901150303.sh db2 "create db evczdb using codeset GBK territory CN pagesize 8192 catalog tablespace managed by database using (file '/db2ffcsdata1/evczdb/syscatspace_c1' 131072 ) user tablespace managed by database using (file '/db2ffcsdata1/evczdb/userspace1_c1' 131072 ) temporary tablespace managed by system using ('/db2ffcstmptbs/evczdb/tempspace1')"

[db2iffcs@h15693 tmp]$ sh db2_create_eassdb_20140901150303.sh DB20000I  The CREATE DATABASE command completed successfully.

     3.创建额外BufferPool及表空间

db2 => connect to evczdb   Database Connection Information Database server        = DB2/LINUXX8664 9.7.7 SQL authorization ID   = DB2IFFCS Local database alias   = EVCZDBdb2 => create bufferpool buffer_assp8k size 25600 pagesize 8kDB20000I  The SQL command completed successfully.db2 => create bufferpool buffer_idx8k size 25600 pagesize 8kDB20000I  The SQL command completed successfully.db2 =>  create bufferpool buffer_clob32k size 6400 pagesize 32kDB20000I  The SQL command completed successfully.db2 => create tablespace assp pagesize 8192 managed by database using (file '/db2ffcsdata1/evczdb/asspspace_c1' 131072) bufferpool buffer_assp8k autoresize no DB20000I  The SQL command completed successfully.db2 => create tablespace assp_index pagesize 8192 managed by database using (file '/db2ffcsdata1/evczdb/idxspace_c1' 131072) bufferpool buffer_idx8k autoresize noDB20000I  The SQL command completed successfully.db2 => create tablespace assp_clob pagesize 32768 managed by database using (file '/db2ffcsdata1/evczdb/clobspace_c1' 32768) bufferpool buffer_clob32kDB20000I  The SQL command completed successfully.

    4.为数据库应用用户授权

db2 => grant connect on database to user evczgrant bindadd on database to user evczgrant createtab on database to user evczDB20000I  The SQL command completed successfully.db2 => DB20000I  The SQL command completed successfully.db2 => DB20000I  The SQL command completed successfully.db2 => grant implicit_schema on database to user evczDB20000I  The SQL command completed successfully.db2 => grant load on database to user evczDB20000I  The SQL command completed successfully.db2 => grant use of tablespace assp to user evczDB20000I  The SQL command completed successfully.db2 => grant use of tablespace assp_index to user evczDB20000I  The SQL command completed successfully.db2 => grant use of tablespace assp_clob to user evczDB20000I  The SQL command completed successfully.db2 =>

    5.做必要核对检查工作

      测试数据库应用用户evcz是否可以正常登陆，列出当前数据库中的所有表空间。

   

[db2iffcs@h15693 tmp]$ su evczPassword: [evcz@h15693 tmp]$ db2(c) Copyright IBM Corporation 1993,2007Command Line Processor for DB2 Client 9.7.7You can issue database manager commands and SQL statements from the command prompt. For example:    db2 => connect to sample    db2 => bind sample.bndFor general help, type: ?.For command help, type: ? command, where command can bethe first few keywords of a database manager command. For example: ? CATALOG DATABASE for help on the CATALOG DATABASE command ? CATALOG          for help on all of the CATALOG commands.To exit db2 interactive mode, type QUIT at the command prompt. Outside interactive mode, all commands must be prefixed with 'db2'.To list the current command option settings, type LIST COMMAND OPTIONS.For more detailed help, refer to the Online Reference Manual.db2 => connect to evczdb user evcz using evcz   Database Connection Information Database server        = DB2/LINUXX8664 9.7.7 SQL authorization ID   = EVCZ Local database alias   = EVCZDBdb2 => LIST TABLESPACES SHOW DETAIL           Tablespaces for Current Database Tablespace ID                        = 0 Name                                 = SYSCATSPACE Type                                 = Database managed space Contents                             = All permanent data. Regular table space. State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 131072 Useable pages                        = 131040 Used pages                           = 31872 Free pages                           = 99168 High water mark (pages)              = 31872 Page size (bytes)                    = 8192 Extent size (pages)                  = 32 Prefetch size (pages)                = 32 Number of containers                 = 1 Tablespace ID                        = 1 Name                                 = TEMPSPACE1 Type                                 = System managed space Contents                             = System Temporary data State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 1 Useable pages                        = 1 Used pages                           = 1 Free pages                           = Not applicable High water mark (pages)              = Not applicable Page size (bytes)                    = 8192 Extent size (pages)                  = 32 Prefetch size (pages)                = 32 Number of containers                 = 1 Tablespace ID                        = 2 Name                                 = USERSPACE1 Type                                 = Database managed space Contents                             = All permanent data. Large table space. State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 131072 Useable pages                        = 131040 Used pages                           = 96 Free pages                           = 130944 High water mark (pages)              = 96 Page size (bytes)                    = 8192 Extent size (pages)                  = 32 Prefetch size (pages)                = 32 Number of containers                 = 1 Tablespace ID                        = 3 Name                                 = SYSTOOLSPACE Type                                 = Database managed space Contents                             = All permanent data. Large table space. State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 4096 Useable pages                        = 4092 Used pages                           = 108 Free pages                           = 3984 High water mark (pages)              = 108 Page size (bytes)                    = 8192 Extent size (pages)                  = 4 Prefetch size (pages)                = 4 Number of containers                 = 1 Tablespace ID                        = 4 Name                                 = ASSP Type                                 = Database managed space Contents                             = All permanent data. Large table space. State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 131072 Useable pages                        = 131040 Used pages                           = 96 Free pages                           = 130944 High water mark (pages)              = 96 Page size (bytes)                    = 8192 Extent size (pages)                  = 32 Prefetch size (pages)                = 32 Number of containers                 = 1 Tablespace ID                        = 5 Name                                 = ASSP_INDEX Type                                 = Database managed space Contents                             = All permanent data. Large table space. State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 131072 Useable pages                        = 131040 Used pages                           = 96 Free pages                           = 130944 High water mark (pages)              = 96 Page size (bytes)                    = 8192 Extent size (pages)                  = 32 Prefetch size (pages)                = 32 Number of containers                 = 1 Tablespace ID                        = 6 Name                                 = ASSP_CLOB Type                                 = Database managed space Contents                             = All permanent data. Large table space. State                                = 0x0000   Detailed explanation:     Normal Total pages                          = 32768 Useable pages                        = 32736 Used pages                           = 96 Free pages                           = 32640 High water mark (pages)              = 96 Page size (bytes)                    = 32768 Extent size (pages)                  = 32 Prefetch size (pages)                = 32 Number of containers                 = 1db2 => 

好了，至此数据库已搭建完成。