DB2配置及数据库创建

一、系统用户信息准备

1、创建DB2用户组

[root@YWYYZX03 ~]# groupadd db2iadm
[root@YWYYZX03 ~]# groupadd db2fadm

2、创建DB2用户

实例用户：db2inst

[root@YWYYZX03 ~]# useradd -g db2iadm -m -d /home/db2inst db2inst
[root@YWYYZX03 ~]# useradd -g db2fadm -m -d /home/db2fenc db2fenc

二、DB2实例创建及启动

1、DB2实例创建

[root@YWYYZX ~]# cd /opt/ibm/db2/V9.7/instance
[root@YWYYZX ~]# ./db2icrt -p 60004 -s wse -u db2fenc db2inst

2、DB2实例启动

[root@YWYYZX03 ~]# su - db2inst
[db2inst@YWYYZX03 ~]$ . /home/db2inst/sqllib/db2profile
[db2inst@YWYYZX03 ~]$ db2set DB2COMM=TCPIP
[db2inst@YWYYZX03 ~]$ db2stop
[db2inst@YWYYZX03 ~]$ db2start

3、创建客户端实例

[root@YWYYZX ~]# cd /opt/ibm/db2/V9.7/instance
[root@YWYYZX ~]# ./db2icrt -s client -u db2fenc op_mgmap
[root@YWYYZX ~]# ./db2icrt -s client -u db2fenc op_mgmmn

三、DB2数据库及表创建

1、数据库创建

create db OPSTADB using codeset GBK territory CN collate using identity
;

connect to opstadb
;

drop tablespace userspace1
;

create bufferpool BP32K all nodes size -1 pagesize 32K
;

create large tablespace opsta_32k_1 pagesize 32k managed by automatic storage bufferpool BP32K
;

create system temporary tablespace opsta_32k_tmp pagesize 32k managed by automatic storage bufferpool BP32K
;

update db cfg using LOGFILSIZ 102400
;

2、数据库表创建

SET CURRENT SCHEMA DICDAT;

CREATE TABLE
    DICDAT.TBL_DICDAT_INS_CATA
    (   
        INS_ID_CD VARCHAR(16) NOT NULL WITH DEFAULT,
        INS_CN_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        ROOT_INS_CD VARCHAR(16) NOT NULL WITH DEFAULT,
        ROOT_INS_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        CUP_BRANCH_CD VARCHAR(16) NOT NULL WITH DEFAULT,
        CUP_BRANCH_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        INS_CATA_1ST_ID INTEGER NOT NULL WITH DEFAULT,
        INS_CATA_1ST_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        INS_CATA_2ND_ID INTEGER NOT NULL WITH DEFAULT,
        INS_CATA_2ND_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        INS_CATA_3RD_ID INTEGER NOT NULL WITH DEFAULT,
        INS_CATA_3RD_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        INS_CATA_4TH_ID INTEGER NOT NULL WITH DEFAULT,
        INS_CATA_4TH_NM VARCHAR(100) NOT NULL WITH DEFAULT,
        CONSTRAINT IND_DICDAT_AT_PK PRIMARY KEY (INS_ID_CD)
    );

--CREATE INDEX DICDAT.IND_DICDAT_AT_I1
--ON DICDAT.TBL_DICDAT_INS_CATA (
--        MCHNT_CD
--        ,AUDIT_ST
--    )
--;


GRANT ALL ON DICDAT.TBL_DICDAT_INS_CATA TO USER OP_MGMAP;
GRANT SELECT ON DICDAT.TBL_DICDAT_INS_CATA TO USER OP_MGMMN;

3、访问测试

[root@YWYYZX03 ~]$ su - db2inst
[db2inst@YWYYZX03 ~]$ db2 connect to opstadb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST
 Local database alias   = OPSTADB

[db2inst@YWYYZX03 ~]$ db2 "select * from dicdat.tbl_dicdat_ins_cata"

四、命令行客户端配置及访问

1、编目结点

编目结点是将远程数据实例在本地绑定。

ywyyzx@ywyyzx02:~$ db2 catalog tcpip node ND_LOCAL remote 127.0.0.1 server 60004
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.

2、编目数据库

编目数据库是将结点上的数据库在本地绑定。

ywyyzx@ywyyzx02:~/home_profile/db2$ db2 catalog database OPSTADB at node ND_LOCAL       
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.

3、远程数据库连接测试

ywyyzx@ywyyzx02:~$ db2 connect to OPSTADB user db2inst
Enter current password for db2inst: 
   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST
 Local database alias   = OPSTADB

 

转载于:https://my.oschina.net/slothpig/blog/875743