주요 명령어

./bin/db2 명령어는 mysql client 나 oracle 의 sqlmgr 과 같은 역할을 수행하는 콘솔 app

데이타베이스 목록

list database DIRECTORY 명령어로 전체 목록 출력. (MySQL 의 show databases 와 비슷)

db2 => list database DIRECTORY
 
 System Database Directory
 Number of entries in the directory = 1
Database 1 entry:
 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/db2inst1
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
CODE

데이타베이스 생성/삭제

db2 콘솔 app에서 create database 사용

db2 => CREATE DATABASE TESTDB3 AUTOMATIC STORAGE YES
CODE

또는 쉘에서 db2 명령어 사용

$ db2 CREATE DATABASE TESTDB3 AUTOMATIC STORAGE YES
CODE

 

삭제

db2 drop database <db_name> 

$ db2 drop database TESTDB3
CODE

 

 

db2 환경 설정 구성 보기

db2 "get dbm cfg" 명령어로 환경 구성을 확인할 수 있음

$ db2 "get dbm cfg"
 
   Database Manager Configuration
     Node type = Database Server with local and remote clients

 Database manager configuration release level            = 0x1000
 CPU speed (millisec/instruction)             (CPUSPEED) = 2.361721e-07
 Max number of concurrently active databases     (NUMDB) = 32
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) = 
 Default charge-back account           (DFT_ACCOUNT_STR) = 
 Java Development Kit installation path       (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64
CODE

오라클의 SQL*NET 처럼 서비스 이름과 구동 포트를 확인할 경우 grep -i svce 로 검색

$ db2 "get dbm cfg"|grep -i svce

 TCP/IP Service name                          (SVCENAME) = 
 SSL service name                         (SSL_SVCENAME) =
CODE

 

DB2 TCP/IP listen

  1. 루트 권한으로 /etc/services 에 다음 내용 추가

    db2             50000/tcp               ## ibm db2
    CODE

  2. db2 명령으로 SVCENAME을 위에서 설정한 서비스 이름(db2) 으로 지정

    $ db2 update dbm cfg using SVCENAME db2
    CODE

  3. db2set 으로 연결 환경 설정

    db2set DB2COMM=TCPIP
    CODE

  4. -all 옵션으로 설정 출력. 

    $ db2set -all

[i] DB2COMM=TCPIP
[g] DB2_COMPATIBILITY_VECTOR=MYS
[g] DB2SYSTEM=whale
[g] DB2INSTDEF=db2inst1
    CODE

  5. db2 재시작

    $ db2stop
$ db2start
    CODE

  6. 50000 포트로 떴는지 확인

    $ lsof -i TCP:50000
COMMAND  PID     USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
db2sysc 1315 db2inst1    8u  IPv4 69047346      0t0  TCP *:db2 (LISTEN)
    CODE

 

Instance

생성

db2icrt 명령어 사용

./instance/db2icrt -d  -a server -u db2fenc1 db2inst1
CODE

 

유용한 DB2 명령어

CommandDescription
db2cmdOpen DB2 Command line Tool
db2ccOpen DB2 Control Centre
db2startStart Database Normaly
db2stopStop Database Normaly
db2stop forceStop Database forcely
db2 force application allClose all applications that uses DB2 Database.
db2levelDisplay DB2 version and fix pack level
db6levelDisplay DB2 Client Version
db2 terminateClose the database connection
db2_kill -allKill a hanged instance
db2licm -lView license information
db2 connect to <db2sid>Establish connection to an instance
db2 list tablespaces show detailDisplays table space information
db2 get dbm cfgDisplay configuration parameter of databasemanager.
db2 get db cfg for <db2 sid>Display configuration parameter of an instance
db2 update dbm cfg using <parameter_name> <new value>Change value of a database manager configuration parameter.
db2 update db cfg for <db2 sid> using <parameter_name> <new value>Change value of a instance configuration parameter.
db2 drop database <target db2sid>Delete and instance
db2 activate db <dbsid>Activate Database
db2 deactivate <dbsid>Deactivate an active database
db2 rollforward db <SID> to end of logsApply all pending logs
db2 rollforward db <db2sid> query statusDisplay rollforward status
db2 backup database <sid> to "disk:\location"Take an offline backup to specified location
db2 list utilities show detailDisplay Database backup status
db2 restore db <sid> from “disk:\path” replace history fileRestore database from a backup image