DB2 quick start reference

주요 명령어

./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         =

데이타베이스 생성/삭제

http://www.tutorialspoint.com/db2/db2_databases.htm

db2 콘솔 app에서 create database 사용

db2 => CREATE DATABASE TESTDB3 AUTOMATIC STORAGE YES

또는 쉘에서 db2 명령어 사용

$ db2 CREATE DATABASE TESTDB3 AUTOMATIC STORAGE YES

삭제

db2 drop database <db_name>

$ db2 drop database TESTDB3

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

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

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

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

DB2 TCP/IP listen

루트 권한으로 /etc/services 에 다음 내용 추가
```
db2             50000/tcp               ## ibm db2
```
db2 명령으로 SVCENAME을 위에서 설정한 서비스 이름(db2) 으로 지정
```
$ db2 update dbm cfg using SVCENAME db2
```
db2set 으로 연결 환경 설정
```
db2set DB2COMM=TCPIP
```

-all 옵션으로 설정 출력.

$ db2set -all

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

db2 재시작
```
$ db2stop
$ db2start
```

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)

Instance

생성

db2icrt 명령어 사용

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

유용한 DB2 명령어

http://scn.sap.com/docs/DOC-43859

Command	Description
db2cmd	Open DB2 Command line Tool
db2cc	Open DB2 Control Centre
db2start	Start Database Normaly
db2stop	Stop Database Normaly
db2stop force	Stop Database forcely
db2 force application all	Close all applications that uses DB2 Database.
db2level	Display DB2 version and fix pack level
db6level	Display DB2 Client Version
db2 terminate	Close the database connection
db2_kill -all	Kill a hanged instance
db2licm -l	View license information
db2 connect to <db2sid>	Establish connection to an instance
db2 list tablespaces show detail	Displays table space information
db2 get dbm cfg	Display 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 logs	Apply all pending logs
db2 rollforward db <db2sid> query status	Display rollforward status
db2 backup database <sid> to "disk:\location"	Take an offline backup to specified location
db2 list utilities show detail	Display Database backup status
db2 restore db <sid> from “disk:\path” replace history file	Restore database from a backup image