|
DB2 사용(리눅스)
작성일: 2002-04-14
작성자: 강명규
OS : LINUX 7.0(kernel 2.4.10) with glibc-2.2-9
CPU : Pentium III 500(Katmai)
RAM : 512MB
DB : DB2 Universal Database Enterprise - Extended Edition Version 7.2
원본출처 : http://211.227.44.198/linux/db2_usage.html
[DB2 테스트]
X윈도상에서 관리서버 사용자로 로그인하여 Control Center, Information Center등을 실행한다.
IBM의 Java Runtime을 사용해야 한다. blackdown은 되지 않았다.
[root@dev2 /root]# su - db2as
[db2as@dev2 db2as]$ l
total 2
drwxr-xr-x 5 db2as db2asgrp 1024 Apr 7 22:44 Desktop
drwxrwsr-t 14 db2as db2asgrp 1024 Apr 7 22:45 sqllib
[db2as@dev2 db2as]$ export JAVA_HOME=/usr/local/jre118_v3
[db2as@dev2 db2as]$ db2cc <= 이거 안된다
[db2as@dev2 db2as]$ export JAVA_HOME=/u01/app/oracle/jre/1.1.8 <= 오라클의 jre는 IBM꺼다.
[db2as@dev2 db2as]$ db2cc (Control Center)
[db2as@dev2 db2as]$ db2ic (Information Center)
sqlplus와 같은 것으로 연결해보자
인스턴스사용자(db2inst1)로 로그인한다.
앞서 우리는 샘플 DB를 생성했지만, 못했을 때는 db2sampl 로 생성할 수 있다.
sqlplus와 비슷하게 db2라는 명령이 있다.
[[db2inst1@dev2 db2inst1]$ db2
(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the 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 sample sample데이터베이스에 접속
Database Connection Information
Database server = DB2/LINUX 7.2.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2 => ? 명령어가 뭐가 있나?
ACTIVATE DATABASE ECHO PREP/PRECOMPILE
ADD DATALINKS MANAGER EXPORT PRUNE HISTORY/LOGFILE
ADD NODE FORCE APPLICATION QUERY CLIENT
ATTACH GET/RESET/UPDATE ADMIN CFG QUIESCE TABLESPACES
ATTACH TO GET AUTHORIZATIONS QUIT
BACKUP DATABASE GET CONNECTION STATE REBIND
BIND GET INSTANCE RECONCILE
CATALOG APPC NODE GET MONITOR SWITCHES REDISTRIBUTE NODEGROUP
CATALOG APPCLU NODE GET SNAPSHOT REFRESH LDAP
CATALOG APPN NODE GET/RESET/UPDATE CLI CFG REGISTER
CATALOG DATABASE GET/RESET/UPDATE DB CFG RELEASE
CATALOG DCS DATABASE GET/RESET/UPDATE DBM CFG REORG TABLE
CATALOG GLOBAL DATABASE HELP REORGCHK
CATALOG IPXSPX NODE IMPORT RESET MONITOR
CATALOG LDAP DATABASE INITIALIZE TAPE RESTART DATABASE
CATALOG LDAP NODE INVOKE RESTORE DATABASE
CATALOG LOCAL NODE LIST ACTIVE DATABASES REWIND TAPE
CATALOG NETBIOS NODE LIST APPLICATIONS ROLLFORWARD DATABASE
CATALOG NPIPE NODE LIST BACKUP/HISTORY RUNSTATS
CATALOG ODBC DATA S. LIST COMMAND OPTIONS SET CLIENT
CATALOG TCPIP NODE LIST DATABASE DIRECTORY SET CONNECTION
CHANGE DATABASE COMMENT LIST DATALINKS MANAGERS SET RUNTIME DEGREE
CHANGE SQLISL LIST DCS APPLICATIONS SET TABLESPACE CONTAINERS
CONNECT LIST DCS DIRECTORY SET TAPE POSITION
CONNECT RESET LIST INDOUBT TRANSACTIONS TERMINATE
CONNECT TO LIST NODE DIRECTORY UNCATALOG DATABASE
CREATE DATABASE LIST NODEGROUPS UNCATALOG DCS DATABASE
DB2START/DB2STOP LIST NODES UNCATALOG LDAP DATABASE
DEACTIVATE DATABASE LIST ODBC DATA SOURCES UNCATALOG LDAP NODE
DEREGISTER LIST PACKAGES/TABLES UNCATALOG NODE
DESCRIBE LIST TABLESPACE CONTAINERS UPDATE ODBC DATA S.
DETACH LIST TABLESPACES UPDATE COMMAND OPTIONS
DISCONNECT LOAD UPDATE HISTORY
DROP DATABASE LOAD QUERY UPDATE LDAP NODE
DROP NODE PING UPDATE MONITOR SWITCHES
Note: Some commands are operating system specific and may not be available.
For further help: ? db2-command - help for specified command
? OPTIONS - help for all command options
? HELP - help for reading help screens
The preceding three options can be run as DB2 from an OS prompt.
!db2ic - DB2 Information Center (Windows/NT and OS/2 only)
This command can also be run as db2ic from an OS prompt.
db2 => list tables 오라클의 select * from tab; MySQL의 show tables와 같다.
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2002-04-07-22.45.03.976038
DEPARTMENT DB2INST1 T 2002-04-07-22.45.00.653347
EMP_ACT DB2INST1 T 2002-04-07-22.45.01.102012
EMP_PHOTO DB2INST1 T 2002-04-07-22.45.01.766518
EMP_RESUME DB2INST1 T 2002-04-07-22.45.03.141207
EMPLOYEE DB2INST1 T 2002-04-07-22.45.00.772035
IN_TRAY DB2INST1 T 2002-04-07-22.45.04.051857
ORG DB2INST1 T 2002-04-07-22.45.00.194301
PROJECT DB2INST1 T 2002-04-07-22.45.01.593723
SALES DB2INST1 T 2002-04-07-22.45.03.658487
STAFF DB2INST1 T 2002-04-07-22.45.00.403093
db2 => create table test(id varchar(10), name varchar(10)) 데이블을 하나 생성해 본다. 질의가 오라클처럼 ;로 끝나지 않음을 주의
DB20000I The SQL command completed successfully.
db2 => insert into test values('myunggyu','강명규') INSERT
DB20000I The SQL command completed successfully.
db2 => select * from test
ID NAME
---------- ----------
myunggyu 강명규
1 record(s) selected.
db2 => update test set id='maddog' where name='강명규' UPDATE
DB20000I The SQL command completed successfully.
db2 => select * from test
ID NAME
---------- ----------
maddog 강명규
1 record(s) selected.
db2 => delete from test DELETE
DB20000I The SQL command completed successfully.
db2 => select * from test
ID NAME
---------- ----------
0 record(s) selected.
간단히 데이터베이스 정보를 보자
db2 => list active databases
Active Databases
Database name = SAMPLE
Applications connected currently = 1
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00001/
db2 => list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
DB21011I In a partitioned database server environment, only the table spaces
on the current node are listed.
db2 => connect to sample
Database Connection Information
Database server = DB2/LINUX 7.2.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2 => get db cfg for sample
Database Configuration for Database sample
Database configuration release level = 0x0900
Database release level = 0x0900
Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country code = 1
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Directory object name (DIR_OBJ_NAME) =
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 1200
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 100
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 512
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 64
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
First active log file =
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM (RESTART)
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
db2 => connect reset
DB20000I The SQL command completed successfully.
db2 => quit
DB20000I The QUIT command completed successfully.
sql스크립트 파일 실행시
db2 -tvf create_schema.sql
db2 => values(current date)
1
----------
04/14/2002
1 record(s) selected.
db2 => values(current time)
1
--------
21:33:03
1 record(s) selected.
db2 => values(current timestamp)
1
--------------------------
2002-04-14-21.33.09.537881
1 record(s) selected.
나머지는 IBM의 DB2 UDB운영가이드를 참조하기 바란다.
링크
IBM DB2사이트
리눅스용 DB2 다운로드
DB2 기술정보
DB2 FAQ
DB2 사용자 게시판
IBM세미나 자료
Copyleft(C) 명규의 DBAKOREA All rights free
|
