database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
ㆍDB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DB2 Tutorials 448 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 448
리눅스용 DB2 테스트하기
작성자
정재익(advance)
작성일
2002-10-26 14:32
조회수
22,134

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 
[Top]
No.
제목
작성자
작성일
조회
1614db2 관련 가이드 북 입니다.
김기복
2008-05-26
11928
884추천 구독 리스트: DB2 UDB for Linux, UNIX, Windows 데이터베이스 관리
문태준
2005-11-30
15590
457[참고] iSeries(AS/400) ODBC Driver for Linux
문태준
2002-12-04
13509
448리눅스용 DB2 테스트하기
정재익
2002-10-26
22134
447Linux 에서 DB2 설치하기 [1]
정재익
2002-10-26
16935
442병렬처리
정재익
2002-10-17
11504
441DB2 개별적인 권한 종류
정재익
2002-10-17
14321
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다