DB2 UDB For LinuxのCLUSTERPRO上での稼動確認

Size: px

Start display at page:

Download "DB2 UDB For LinuxのCLUSTERPRO上での稼動確認"

さやなゆのもと
5 years ago
Views:

1 DB2 UDB for Linux CLUSTERPRO 2002/03/29 IBM NEC DB2_G1: (start.bat)...6 DB2_G1: DB2 (db2start.sh)...7 DB2_G1: DB2 (db2poling. sh)...9 DB2_G1: (stop.bat ) DB2_G1: DB2 (db2stop. sh) DB2 UDB Import Load Export Backup,Restore DB2 tool SELECT DELETE INSERT UPDATE SELECT,DELETE,INSERT,UPDATE HW SW...38

2 DB2 UDB V7 EE for Linux Turbolinux 7 CLUSTERPRO SE DB2 CLUSTERPRO TurbolinuxOS+CLUSTERPRO+DB2 DB2 CLUSTERPRO CLUSTERPRO+DB2 2 DB2 Backup H/W : Fujitsu PRIMERGY P200 (2 ) CPU : Pentium III 1.2GHz(2 - SMP ) Memory: 1GByte Local hard disk: 8GB Shared hard disk: Fujitsu ETERNUS GR710 (36GB x5) HBA : PG-FC102 (Fibre Channel ) OS: Turbolinux 7 Server Cluster: Turbolinux 7 CLUSTERPRO SE (clusterpro i386.rpm) DBMS: DB2 UDBV7.2 Enterprise Edition for Linux + Fixpak5 H/W: Fujitsu FMV-BIBRO MF5/600 OS: Turbolinux 7 Server DB2 UDB (V7.2) + Fixpak5 IBM JDK IBM JDK (cx ) 2

3 H/W: Fujitsu FMV-BIBRO MF5/600 OS: Windows ME : CLUSTERPRO Client2 (WinME) Client1 (Linux) /24 net RS232C (100Base-T) DB2_G1 fip: Inter connect DB2_G2 fip: Server1 (P200) Server1 (P200) Shared-DISK Disk1(/dev/sdd2) db2inst1 Disk2(/dev/sdd3) db2inst2 1 Disk1 /mnt/database1 mount Disk2 /mnt/database2 mount Server1 /mnt/database1 db2inst1 Server2 /mnt/database2 db2inst2 DB /home/db2inst1/sqllib /mnt/database1/sqllib /home/db2inst2/sqllib /mnt/database2/sqllib 3

4 1. CLUSTERPRO Server1, Server2 GR710 2 DB2_G1, DB2_G2 SuperCluster Cluster ###NEC_NP1 COM 1 2 /dev/sdd1 /dev/ttys0 3[s] * 10 SERVER1 Public LAN IP /24 Interconnect IP /24 SERVER2 Public LAN IP /24 Interconnect IP / DB2_G1 1: SERVER1 2: SERVER2 Floating IP Disk1 /dev/sdd2 /mnt/database1 ext3 rw ( ) 1800 [s] ( ) 300 [s] 4

5 2 DB2_G2 1: SERVER2 2: SERVER1 Floating IP Disk2 /dev/sdd3 /mnt/database2 ext3 rw ( ) 1800 [s] ( ) 300 [s] 2. DB2 1. DB2 UDB 3. (start.bat) DB2 (db2start.sh) DB2 (db2poling.sh) (stop.bat) DB2 (db2stop.sh) 5

6 DB2_G1: (start.bat) 6

7 DB2_G1: DB2 (db2start.sh) 7

8 8

9 DB2_G1: DB2 (db2poling.sh) 9

10 10

11 DB2_G1: (stop.bat) 11

12 12

13 DB2_G1: DB2 (db2stop.sh) 13

14 14

15 DB2 Part1: DB2 UDB for Linux 1. DB Import 7. Load 8. Export 9. Backup, Restore 10. DB2 tool 11. SELECT 12. DELETE INSERT 13. UPDATE 14. SELECT,DELETE,INSERT,UPDATE Part2: CLUSTERPRO DB2 UDB for Linux HW 17. SW 15

16 CLUSTERPRO DB2 Part1 No. (OK/NG) 1 DB2 OK 2 OK 3 OK 4 OK 5 OK 6 Import 7 Load 8 Export 9 Backup, Restore OK 10 DB2 tool 11 SELECT OK 12 DELETE INSERT OK 13 UPDATE OK 14 SELECT,DELETE,INSERT,UPDATE OK OK OK OK OK Part2 No. (OK/NG) 15 OK 16 HW OK 17 SW OK 16

17 DB2 UDB DISK Group1 DISK Group2 2 /mnt/database1, /mnt/database2 mount 1. DB2 UDB a) 2 P200 DB2 UDB EE V7.2 for Linux /usr/ibmdb2/ b) Fixpak5 c) Instance User (UDI) Group (GID) 1 (Port 50000) db2inst1 (500) db2iadm1 (500) Fence 1 db2fadm1 (501) db2fadm1 (501) 2 (Port 50002) db2inst2 (503) db2iadm2 (503) Fence 2 db2fadm2 (504) db2fadm2 (504) d) ~/sqllib sym-link # cd /home/db2inst1 # cp -a sqllib /mnt/database1/ # mv sqllib sqllib.org # ln -s /mnt/database1/sqllib. # ln -s /mnt/database2/sqllib /home/db2inst2/ 2 # cd /home/db2inst2 # cp -a sqllib /mnt/database2/ # mv sqllib sqllib.org # ln -s /mnt/database2/sqllib. # ln -s /mnt/database1sqllib /home/db2inst1/ e) DB # chown db2inst1:db2iadm1 /mnt/database1 # chwon db2inst2:db2iadm2 /mnt/database2 f).bashrc 17

18 g) 1 db2inst1 $ db2 create database TURBO on /mnt/database1/ 2 db2inst2 $ db2sampl on /mnt/database2/ h) DB2 db2set DB2_PARALLEL_IO="*" db2set DB2_STRIPED_CONTAINERS=ON db2 update db config for TURBO using dft_degree -1 maxappls 100 maxlocks 50 locklist 2000 sortheap 1024 db2 update dbm config using intra_parallel YES JAVA_HEAP_SZ a) DB2 DB2 $ db2 db2stop DB2 $ db2 db2start b) DB $ db2 list db directory 3. a) $ db2 catalog tcpip node node1 remote server $ db2 catalog tcpip node node2 remote server

19 $ db2 catalog database turbo at node node1 $ db2 catalog database sample at node node2 b) $ db2 connect to turbo user db2inst1 $ db2 connect to sample user db2inst2 4. a) Bufferpool db2 "alter bufferpool ibmdefaultbp size 40960" b) DMS db2 create tablespace dmstest managed by database using (file '/mnt/database1/dms/dms_acc1' , file /mnt/database1/dms/dms_acc2' ) extentsize 256" SMS db2 create tablespace smstest managed by system using ('/mnt/database1/sms_acc1','$db_home/sms_acc2') c) DMS DMS sample.dmstalbea sample.dmstalbeb sample.dmstalbec sample.dmstalbed db2 create table sample.dmstablea (SeqNo int not null primary key, kokyaku_no char(10), 19

20 nenrei smallint, ztnhk smallint, zttel smallint, ztdenki smallint, ztsuidou smallint, ztgasu smallint, cust_name varchar(50), seibetsu char(1)) in dmstest db2 create table sample.dmstableb (SeqNo int not null primary key, kokyaku_no char(10), keiyakubi date, teiki int, ztcredit smallint) in dmstest db2create table sample.dmstablec200109(seqno int not null primary key, kokyaku_no char(10), kingaku int) in dmstest db2 create table sample.dmstabled (SeqNo int not null primary key, kokyaku_no char(10), nenrei smallint, ztnhk smallint, zttel smallint, ztdenki smallint, ztsuidou smallint, ztgasu smallint, cust_name varchar(50), seibetsu char(1)) in dmstest d) SMS SMS sample.smstablea sample.smstableb sample.smstablec

21 sample.smstabled db2 create table sample.smstablea (SeqNo int not null primary key, kokyaku_no char(10), nenrei smallint, ztnhk smallint, zttel smallint, ztdenki smallint, ztsuidou smallint, ztgasu smallint, cust_name varchar(50), seibetsu char(1)) in smstest db2 create table sample.smstableb (SeqNo int not null primary key, kokyaku_no char(10), keiyakubi date, teiki int, ztcredit smallint) in smstest db2create table sample.smstablec200109(seqno int not null primary key, kokyaku_no char(10), kingaku int) in smstest db2 create table sample.smstabled (SeqNo int not null primary key, kokyaku_no char(10), nenrei smallint, ztnhk smallint, zttel smallint, ztdenki smallint, ztsuidou smallint, ztgasu smallint, cust_name varchar(50), seibetsu char(1)) in smstest e) 21

22 syscat.tablespaces select tbspace,tbspacetype from syscat.tablespaces where tbspace='smstest' or tbspace='dmstest' TBSPACE DMSTEST SMSTEST TBSPACETYPE D S D DMS S SMS 2 f) list tables list tables for schema sample / DMSTABLEA SAMPLE T DMSTABLEB SAMPLE T DMSTABLEC SAMPLE T DMSTABLED SAMPLE T SMSTABLEA SAMPLE T SMSTABLEB SAMPLE T SMSTABLEC SAMPLE T SMSTABLED SAMPLE T select cast(name as char(20)) as name,tbspace from sysibm.systables where creator = 'SAMPLE' NAME TBSPACE 22

23 DMSTABLEC SMSTABLEB SMSTABLED DMSTABLEA DMSTABLEB DMSTABLED SMSTABLEA SMSTABLEC DMSTEST SMSTEST SMSTEST DMSTEST DMSTEST DMSTEST SMSTEST SMSTEST 8 5. a) LOG db2 "update db config for turbo using LOGBUFSZ 32 LOGFILSIZ LOGPRIMARY 5" b) db2 get db cfg for turbo (4KB) (LOGFILSIZ) = (LOGPRIMARY) = 5 2 (LOGSECOND) = 2 (NEWLOGPATH) = = /mnt/database1/db2inst1/node0000/sql00001/sqlogdir/ 6. Import a) DMS Import 100 DEL 23

24 sample.dmstablea sample.dmstableb sample.dmstablec import from kokyaku1 of del replace into sample.dmstablea (seqno,kokyaku_no,nenrei, ztnhk,zttel,ztdenki,ztsuidou,ztgasu,cust_name,seibetsu) import from teiki1 of del replace into sample.dmstableb (seqno,kokyaku_no,keiyakubi, teiki,ztcredit) import from kyu of del replace into sample.dmstablec (seqno,kokyaku_no, kingaku) b) SMS import 100 DEL sample.smstablea sample.smstableb sample.smstablec import from kokyaku1 of del replace into sample.smstablea (seqno,kokyaku_no,nenrei, ztnhk,zttel,ztdenki,ztsuidou,ztgasu,cust_name,seibetsu) import from teiki1 of del replace into sample.smstableb (seqno,kokyaku_no,keiyakubi, teiki,ztcredit) import from kyu of del replace into sample.smstablec (seqno,kokyaku_no, kingaku) c) Table SELECT 24

25 select cust_name,nenrei from sample.smstablea where seqno=8888 CUST_NAME NENREI select a.cust_name,a.nenrei,b.keiyakubi,c.kingaku from sample.smstablea a,sample.smstableb b, sample.smstablec c where a.seqno=b.seqno and b.seqno=c.seqno and (a.seqno=800 or a.seqno=888888) CUST_NAME NENREI KEIYAKUBI KINGAKU select cust_name,nenrei from sample.dmstablea where seqno=9999 CUST_NAME NENREI select a.cust_name,a.nenrei,b.keiyakubi,c.kingaku from sample.dmstablea a,sample.dmstableb b, sample.dmstablec c where a.seqno=b.seqno and b.seqno=c.seqno and (a.seqno=800 or a.seqno=999999) CUST_NAME NENREI KEIYAKUBI KINGAKU

26 select count(1) from sample.smstablea select count(1) from sample.smstableb select count(1) from sample.smstablec select count(1) from sample.smstablec

27 select count(1) from sample.dmstablea select count(1) from sample.dmstableb select count(1) from sample.dmstablec Load a) DMS sample.dmstabled 100 DEL load from kokyaku1 of del replace into sample.dmstabled (seqno,kokyaku_no,nenrei, 27

28 ztnhk,zttel,ztdenki,ztsuidou, ztgasu,cust_name,seibetsu) copy no b) SMS sample.smstabled 100 DEL load from kokyaku1 of del replace into sample.smstabled (seqno,kokyaku_no,nenrei, ztnhk,zttel,ztdenki,ztsuidou, ztgasu,cust_name,seibetsu) copy no c) Table SELECT select cust_name,nenrei from sample.dmstabled where seqno=7777 CUST_NAME NENREI select cust_name,nenrei from sample.smstabled where seqno=6666 CUST_NAME NENREI 1 select count(1) from sample.smstabled

29 1 select count(1) from sample.dmstabled Export a) DMS export sample.dmstablea sample.dmstableb sample.dmstablec export to dms_kokyaku.out of del modified by chardel"" coldel, select seqno,kokyaku_no,nenrei, ztnhk,zttel,ztdenki,ztsuidou,ztgasu, cust_name,seibetsu from sample.dmstablea export to dms_eiki.out of del modified by chardel"" coldel, select seqno,kokyaku_no,keiyakubi, teiki,ztcredit from sample.dmstableb export to dms_kyu out of del modified by chardel"" coldel, select seqno,kokyaku_no,kingaku from sample.dmstablec b) SMS export 29

30 sample.smstablea sample.smstableb sample.smstablec export to sms_kokyaku.out of del modified by chardel"" coldel, select seqno,kokyaku_no,nenrei,ztnhk,zttel,ztdenki,ztsuidou, ztgasu, cust_name,seibetsu from sample.smstablea export to sms_teiki.out of del modified by chardel"" coldel select seqno,kokyaku_no,keiyakubi, teiki,ztcredit from sample.smstableb export to sms_kyu out of del modified by chardel"" coldel, select seqno,kokyaku_no,kingaku from sample.smstablec c) -rw-r rw-r rw-r rw-r rw-r rw-r db2inst1 db2iadm :42 dms_kokyaku.out 1 db2inst1 db2iadm :43 dms_kyu out 1 db2inst1 db2iadm :42 dms_teiki.out 1 db2inst1 db2iadm :43 sms_kokyaku.out 1 db2inst1 db2iadm :44 sms_kyu out 1 db2inst1 db2iadm :44 sms_teiki.out 9. Backup, Restore a) backup backup database turbo to /mnt/database1/db2backup b) drop table sample.smstablea 30

31 c) restore restore database turbo from /mnt/database1/db2backup replace existing d) select seqno,cust_name from sample.smstablea where seqno=99999 SEQNO CUST_NAME DB2 tool a) db2look db2look SQL DB2 db2look -d turbo -a -e -m -l -x -f -o lookdb.sql lookdb.sql b) reorg table reorg db2 "reorg table sample.smstablea" db2 reorg table sample.smstableb" db2 reorg table sample.smstablec" db2 reorg table sample.smstabled db2 reorg table sample.dmstablea" db2 reorg table sample.dmstableb" db2 reorg table sample.dmstablec" 31

32 db2 reorg table sample.dmstabled" reorg 11. SELECT a) Java (1) 10 1 SELECT 100 (2) 10 (3) SELECT (4) SQL DB2 SAMPLE EMP_ACT EMP_ACT 75 SELECT SELECT * FROM EMP_ACT b) c) SELECT 12. DELETE INSERT a) Java (1) 1 DELETE 100 INSERT 1 (2) (3) 10 32

33 (4) DELETE,INSERT (5) SQL DB2 Sample 10 COL1 SMALLINT DELETE INSERT DELETE FROM INSERT INTO VALUES(n) [n ] b) c) DELETE INSERT 13. UPDATE a) Java (1) UPDATE (2) 10 (3) UPDATE (4) SQL TURBO 5 33

34 COLSMALLINT COLCHAR SMALLINT CHAR(1) UPDATE UPDATE SET COLCHAR = N WHERE COLSMALLINT = n AND COLCHAR = Y [n ] b) d) UPDATE 14. SELECT,DELETE,INSERT,UPDATE a) Java (1) 13 SQL (2) 13 (3) (4) SQL TURBO SAMPLE.SMSTABLEA 100 SQL SELECT1: SELECT KOKYAKU_NO, CUST_NAME FROM SAMPLE.SMSTABLEA WHERE SEIBETSU = '0' SELECT2: 34

35 SELECT KOKYAKU_NO, CUST_NAME FROM SAMPLE.SMSTABLEA WHERE SEIBETSU = '1' SELECT3: SELECT KOKYAKU_NO, CUST_NAME FROM SAMPLE.SMSTABLEA WHERE NENREI BETWEEN 20 AND 40 SELECT4: SELECT KOKYAKU_NO, CUST_NAME FROM SAMPLE.SMSTABLEA (WHERE NENREI BETWEEN 20 AND 40) AND SEIBETSU = '0' SELECT5: SELECT KOKYAKU_NO, CUST_NAME FROM SAMPLE.SMSTABLEA (WHERE NENREI BETWEEN 20 AND 40) AND WHERE SEIBETSU = '1' UPDATE: UPDATE SAMPLE.SMSTABLEA SET ZTTEL = 1 WHERE KOKYAKU_NO = n [n ] INSERT: INSERT INTO SAMPLE.SMSTABLEA VALUES( ,'10000',26,1,1,1,1,1,'komedani','1') DELETE: DELETE FROM SAMPLE.SMSTABLEA WHERE SEQNO = SQL SELECT SELECT SELECT SELECT SELECT UPDATE INSERT DELETE ( ) b) 35

36 c) CPU CPU 30-80% SQL 36

37 15. a) DB2 Floating IP DB2 b) 1. SERVER1, SERVER SERVER1, SERVER2 3. SERVER1, SERVER2 15. SERVER1 4. SERVER1 16. SERVER2 5. SERVER1 17. SERVER2 6. DB2_G1 SERVER DB2_G1 SERVER2 19. SERVER1, SERVER2 8. DB2_G1 SERVER1 20. SERVER1 9. DB2_G1 SERVER2 21. SERVER SERVER1, SERVER2 11. SERVER1, SERVER2 23. DB2_G1 SERVER1 12. SERVER1 24. DB2_G2 SERVER2 c) DB2 DB2 SERVER1, SERVER2 16. HW a) HW DB2 Floating IP DB2 b) OK 37

38 NIC/LAN COM Public LAN (SERVER1) Public LAN (SERVER2) Interconnect LAN OK OK OK Public LAN Interconnect LAN OK RS232C RS232C SERVER1 RS232C SERVER2 RS232C OK RS232C OK SERVER SERVER1 OK OK OK OK SERVER1 OK SERVER2 OK SERVER2 OK SERVER1 GR710 OK(*1) SERVER2 GR710 OK(*1) SERVER1 GR710 OK SERVER1 GR710 OK SERVER2 GR710 OK SERVER2 GR710 OK *1 DB2 (db2stop.sh) 300[s] 17. SW a) SW DB2 Floating IP 38

39 DB2 b) DB2 UDB for Linux a. b. c. import 100 import 1000 COMMIT db2stop 2. db2stop force 3. kill -9 db2sysc 4. killall -9 armmon armmon 5. killall -9 armmonp armmonp c) OK a. b. c. 1. db2stop OK (*1) (*1) 2. db2stop force OK OK OK 3. kill -9 db2sysc OK OK OK 4. killall -9 armmon OK OK OK 5. killall -9 armmonp OK OK OK *1 - DB2 db2stop 39

NEC Storage series NAS Device

NEC Storage series NAS Device NEC Storage NV Series NAS Device Guide for Oracle Storage Compatibility Program Snapshot Technologies is-wp-04-001 Rev-1.00(J) Oct, 2004 NEC Solutions NEC Corporation. - 1 - Copyright 2004 NEC Corporation