{"id":606,"date":"2019-10-23T14:50:16","date_gmt":"2019-10-23T06:50:16","guid":{"rendered":"https:\/\/www.linuxdevops.cn\/?p=606"},"modified":"2019-10-23T14:50:18","modified_gmt":"2019-10-23T06:50:18","slug":"db2-common-commands","status":"publish","type":"post","link":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/","title":{"rendered":"DB2\u5e38\u7528\u547d\u4ee4"},"content":{"rendered":"\n

<\/h2>\n\n\n\n

1\u3001 \u6253\u5f00\u547d\u4ee4\u884c\u7a97\u53e3
\u3000 #db2cmd
2\u3001 \u6253\u5f00\u63a7\u5236\u4e2d\u5fc3
\u3000 #db2cmd db2cc
3\u3001 \u6253\u5f00\u547d\u4ee4\u7f16\u8f91\u5668
\u3000#db2cmd db2ce
=====\u64cd\u4f5c\u6570\u636e\u5e93\u547d\u4ee4=====<\/p>\n\n\n\n

4\u3001 \u542f\u52a8\u6570\u636e\u5e93\u5b9e\u4f8b
\u3000 #db2start<\/p>\n\n\n\n

5\u3001 \u505c\u6b62\u6570\u636e\u5e93\u5b9e\u4f8b
\u3000 #db2stop<\/p>\n\n\n\n

\u3000\u5982\u679c\u4f60\u4e0d\u80fd\u505c\u6b62\u6570\u636e\u5e93\u7531\u4e8e\u6fc0\u6d3b\u7684\u8fde\u63a5\uff0c\u5728\u8fd0\u884cdb2stop\u524d\u6267\u884cdb2 force application all\u5c31\u53ef\u4ee5\u4e86 \/db2stop force
6\u3001 \u521b\u5efa\u6570\u636e\u5e93
\u3000 #db2 create db [dbname]
7\u3001 \u8fde\u63a5\u5230\u6570\u636e\u5e93
\u3000 #db2 connect to [dbname] user [username] using [password]
8\u3001 \u65ad\u5f00\u6570\u636e\u5e93\u8fde\u63a5
\u3000 #db2 connect reset
9\u3001 \u5217\u51fa\u6240\u6709\u6570\u636e\u5e93
\u3000#db2 list db directory<\/p>\n\n\n\n

10\u3001 \u5217\u51fa\u6240\u6709\u6fc0\u6d3b\u7684\u6570\u636e\u5e93
\u3000 #db2 list active databases
11\u3001 \u5217\u51fa\u6240\u6709\u6570\u636e\u5e93\u914d\u7f6e
\u3000 #db2 get db cfg
12\u3001 \u5220\u9664\u6570\u636e\u5e93
\u3000 #db2 drop database [dbname]
\uff08\u6267\u884c\u6b64\u64cd\u4f5c\u8981\u5c0f\u5fc3\uff09
\u5982\u679c\u4e0d\u80fd\u5220\u9664\uff0c\u65ad\u5f00\u6240\u6709\u6570\u636e\u5e93\u8fde\u63a5\u6216\u8005\u91cd\u542fdb2<\/p>\n\n\n\n

=========\u64cd\u4f5c\u6570\u636e\u8868\u547d\u4ee4==========
13\u3001 \u5217\u51fa\u6240\u6709\u7528\u6237\u8868
\u3000 #db2 list tables
14\u3001\u5217\u51fa\u6240\u6709\u7cfb\u7edf\u8868
\u3000#db2 list tables for system
15\u3001\u5217\u51fa\u6240\u6709\u8868
\u3000 #db2 list tables for all
16\u3001 \u5217\u51fa\u7cfb\u7edf\u8868
\u3000 #db2 list tables for system
17\u3001\u5217\u51fa\u7528\u6237\u8868
\u3000 #db2 list tables for user
18\u3001 \u5217\u51fa\u7279\u5b9a\u7528\u6237\u8868
\u3000 #db2 list tables for schema [user]
19\u3001 \u521b\u5efa\u4e00\u4e2a\u4e0e\u6570\u636e\u5e93\u4e2d\u67d0\u4e2a\u8868(t2)\u7ed3\u6784\u76f8\u540c\u7684\u65b0\u8868(t1)
\u3000 #db2 create table t1 like t2
20\u3001 \u5c06\u4e00\u4e2a\u8868t1\u7684\u6570\u636e\u5bfc\u5165\u5230\u53e6\u4e00\u4e2a\u8868t2
#db2 \"insert into t1 select * from t2\"
21\u3001 \u67e5\u8be2\u8868
\u3000 #db2 \"select * from table name where ...\"
22\u3001 \u663e\u793a\u8868\u7ed3\u6784
\u3000 #db2 describe table tablename
23\u3001 \u4fee\u6539\u5217
\u3000 #db2 alter table [tablename] alter column [columname] set data type varchar(24)
======\u811a\u672c\u6587\u4ef6\u64cd\u4f5c\u547d\u4ee4=======
24\u3001 \u6267\u884c\u811a\u672c\u6587\u4ef6
\u3000 #db2 -tvf scripts.sql
25\u3001\u5e2e\u52a9\u547d\u4ee4
* \u67e5\u770b\u547d\u4ee4\u5e2e\u52a9
\u3000#db2 ? db2start
* \u67e5\u770b\u9519\u8bef\u7801\u4fe1\u606f
#db2 ? 22001
* memo: \u8be6\u7ec6\u547d\u4ee4\u8bf7\u4f7f\u7528\"db2 ? <command>\"\u8fdb\u884c\u67e5\u770b\u3002\u3000
=========================
26\u3001\u5907\u4efd\u6570\u636e\u5e93
#db2 backup db <db name>
\u5907\u6ce8\uff1a\u6267\u884c\u4ee5\u4e0a\u547d\u4ee4\u4e4b\u524d\u9700\u8981\u65ad\u5f00\u6570\u636e\u5e93\u8fde\u63a5<\/p>\n\n\n\n

27\u3001\u5728\u7ebf\u5907\u4efd\u6570\u636e\u5e93
#db2 -v \"BACKUP DATABASE <database name> ONLINE TO <path> WITH 2 BUFFERS BUFFER 1024 INCLUDE LOGS WITHOUT PROMPTING\"
28\u3001\u6062\u590d\u6570\u636e\u5e93
#db2 restore db <source db name><\/p>\n\n\n\n

29\u3001\u5728\u7ebf\u6062\u590d\u6570\u636e\u5e93
#db2 \"RESTORE DB <database name> TO <db path> LOGTARGET <logpath> WITHOUT PROMPTING\"
#db2 \"ROLLFORWARD DB <database name> TO END OF LOGS AND STOP\" ...
30\u3001\u5bfc\u51fa\u6570\u636e\u6587\u4ef6<\/p>\n\n\n\n

#db2move <db name> export<\/p>\n\n\n\n

[-sn <\u6a21\u5f0f\u540d\u79f0\uff0c\u4e00\u822c\u4e3adb2admin>]
[-tn <\u8868\u540d\uff0c\u591a\u4e2a\u4e4b\u95f4\u7528\u9017\u53f7\u5206\u9694>]
31\u3001\u5bfc\u5165\u6570\u636e\u6587\u4ef6
#db2move <db name> import
32\u3001\u83b7\u53d6db2\u6570\u636e\u5e93\u7ba1\u7406\u914d\u7f6e\u73af\u5883\u4fe1\u606f
#db2 get dbm cfg
33\u3001.\u83b7\u53d6db2\u67d0\u4e2a\u6570\u636e\u5e93\u6570\u636e\u5e93\u7ba1\u7406\u914d\u7f6e\u73af\u5883\u4fe1\u606f
#db2 get db cfg for <db name><\/p>\n\n\n\n

\u6216\u8005\uff1a\u8fde\u63a5\u81f3\u67d0\u4e2a\u6570\u636e\u5e93\u4ee5\u540e\u6267\u884cdb2 get db cfg<\/p>\n\n\n\n

34\u3001\u66f4\u6539db2\u65e5\u5fd7\u7a7a\u95f4\u7684\u5927\u5c0f
\u5907\u6ce8\uff1a\u4ee5\u4e0b\u547d\u4ee4\u4e3a\u4e86\u9632\u6b62db2\u6570\u636e\u5e93\u8fc7\u4efd\u4f7f\u7528\u786c\u76d8\u7a7a\u95f4\u800c\u8bbe\uff0c\u4ec5\u7528\u4e8e\u5f00\u53d1\u8005\u81ea\u5df1\u673a\u5668\u4e0a\u7684db2\uff0c\u5982\u679c\u662f\u670d\u52a1\u5668\uff0c\u5219\u53c2\u6570\u9700\u8981\u4fee\u6539\u3002<\/p>\n\n\n\n

#db2 UPDATE DB CFG FOR <db name> USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600;
\u5982\u679c\u9875\u5927\u5c0f\u662f4KB\uff0c\u5219\u4ee5\u4e0a\u547d\u4ee4\u521b\u5efa3\u4e2a100M\u7684\u65e5\u5fd7\u6587\u4ef6\uff0c\u5360\u7528300MB\u786c\u76d8\u7a7a\u95f4\u300225600*4KB=102400KB\u3002
35\u3001\u521b\u5efa\u4e34\u65f6\u8868\u7a7a\u95f4
#DB2 CREATE USER TEMPORARY TABLESPACE STMASPACE PAGESIZE 32 K MANAGED BY DATABASE USING (FILE 'D:\\DB2_TAB\\STMASPACE.F1' 10000)
EXTENTSIZE 256
36\u3001\u83b7\u53d6\u6570\u636e\u5e93\u7ba1\u7406\u5668\u7684\u5feb\u7167\u6570\u636e
#db2 \u2013v get snapshot for dbm
37\u3001\u663e\u793a\u8fdb\u884c\u7a0b\u53f7
#db2 list applications show detail
===================================================
\u4e00\u3001\u52a0\u8f7d\u6570\u636e\uff1a
1\u3001 \u4ee5\u9ed8\u8ba4\u5206\u9694\u7b26\u52a0\u8f7d,\u9ed8\u8ba4\u4e3a\u201c,\u201d\u53f7
db2 \"import from btpoper.txt of del insert into btpoper\"
2\u3001 \u4ee5\u6307\u5b9a\u5206\u9694\u7b26\u201c|\u201d\u52a0\u8f7d
db2 \"import from btpoper.txt of del modified by coldel| insert into btpoper\"
\u4e8c\u3001\u5378\u8f7d\u6570\u636e\uff1a
1\u3001 \u5378\u8f7d\u4e00\u4e2a\u8868\u4e2d\u5168\u90e8\u6570\u636e
db2 \"export to btpoper.txt of del select * from btpoper\"
db2 \"export to btpoper.txt of del modified by coldel| select * from btpoper\"
2\u3001 \u5e26\u6761\u4ef6\u5378\u8f7d\u4e00\u4e2a\u8868\u4e2d\u6570\u636e
db2 \"export to btpoper.txt of del select * from btpoper where brhid='907020000'\"
db2 \"export to cmmcode.txt of del select * from cmmcode where codtp='01'\"
db2 \"export to cmmcode.txt of del modified by coldel| select * from cmmcode where codtp='01'\"
\u4e09\u3001\u67e5\u8be2\u6570\u636e\u7ed3\u6784\u53ca\u6570\u636e\uff1a
db2 \"select * from btpoper\"
db2 \"select * from btpoper where brhid='907020000' and oprid='0001'\"
db2 \"select oprid,oprnm,brhid,passwd from btpoper\"
\u56db\u3001\u5220\u9664\u8868\u4e2d\u6570\u636e\uff1a
db2 \"delete from btpoper\"
db2 \"delete from btpoper where brhid='907020000' or brhid='907010000'\"
\u4e94\u3001\u4fee\u6539\u8868\u4e2d\u6570\u636e\uff1a
db2 \"update svmmst set prtlines=0 where brhid='907010000' and jobtp='02'\"
db2 \"update svmmst set prtlines=0 where jobtp='02' or jobtp='03'\"
\u516d\u3001\u8054\u63a5\u6570\u636e\u5e93
db2 connect to btpdbs
\u4e03\u3001\u6e05\u9664\u6570\u636e\u5e93\u8054\u63a5
db2 connect reset \u65ad\u5f00\u6570\u636e\u5e93\u8fde\u63a5
db2 terminate \u65ad\u5f00\u6570\u636e\u5e93\u8fde\u63a5
db2 force applications all \u65ad\u5f00\u6240\u6709\u6570\u636e\u5e93\u8fde\u63a5
\u516b\u3001\u5907\u4efd\u6570\u636e\u5e93
1\u3001 db2 backup db btpdbs
2\u3001 db2move btpdbs export
db2look -d btpdbs -e -x [-a] -o crttbl.sql
\u4e5d\u3001\u6062\u590d\u6570\u636e\u5e93
1\u3001 db2 restore db btpdbs without rolling forward
2\u3001 db2 -tvf crtdb.sql
crtdb.sql\u6587\u4ef6\u5185\u5bb9\uff1acreate db btpdbs on \/db2catalog
db2 -stvf crttbl.sql
db2move btpdbs import
\u5341\u3001DB2\u5e2e\u52a9\u547d\u4ee4\uff1a
db2 ?
db2 ? restroe
db2 ? sqlcode (\u4f8b\uff1adb2 ? sql0803) \u6ce8\uff1acode\u5fc5\u987b\u4e3a4\u4f4d\u6570\uff0c\u4e0d\u591f4\u4f4d\uff0c\u524d\u9762\u88650<\/p>\n\n\n\n

\u5341\u4e00\u3001bind\u547d\u4ee4\uff1a\u5c06\u5e94\u7528\u7a0b\u5e8f\u4e0e\u6570\u636e\u5e93\u4f5c\u4e00\u6346\u7ed1,\u6bcf\u6b21\u6062\u590d\u6570\u636e\u5e93\u540e\uff0c\u5efa\u8bae\u90fd\u8981\u505a\u4e00\u6b21bind
(1) db2 bind br8200.bnd
(2) \/btp\/bin\/bndall \/btp\/bnd
\/btp\/bin\/bndall \/btp\/tran\/bnd
\u5341\u4e8c\u3001\u67e5\u770b\u6570\u636e\u5e93\u53c2\u6570\uff1a
db2 get dbm cfg
db2 get db cfg for btpdbs
\u5341\u4e09\u3001\u4fee\u6539\u6570\u636e\u5e93\u53c2\u6570\uff1a
db2 update db cfg for btpdbs using LOGBUFSZ 20
db2 update db cfg for btpdbs using LOGFILSIZ 5120
\u6539\u5b8c\u540e\uff0c\u5e94\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4\u4f7f\u5176\u751f\u6548\uff1a
db2 stop
db2 start<\/p>\n\n\n\n

\u8865\u5145\uff1a
db2 set schema btp \u4fee\u6539\u5f53\u524d\u6a21\u5f0f\u4e3a\"btp\"
db2 list tablespaces show detail \u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u8868\u7a7a\u95f4\u5206\u914d\u72b6\u51b5
db2 list tablespace containers for 2 show detail \u67e5\u770btablespace id=2\u4f7f\u7528\u5bb9\u5668\u6240\u5728\u76ee\u5f55
db2 list application
db2 list db directory \u5217\u51fa\u6240\u6709\u6570\u636e\u5e93
db2 list active databases \u5217\u51fa\u6240\u6709\u6d3b\u52a8\u7684\u6570\u636e\u5e93
db2 list tables for all \u5217\u51fa\u5f53\u524d\u6570\u636e\u5e93\u4e0b\u6240\u6709\u7684\u8868
db2 list tables for schema btp \u5217\u51fa\u5f53\u524d\u6570\u636e\u5e93\u4e2dschema\u4e3abtp\u7684\u8868
db2 list tablespaces show detail \u663e\u793a\u6570\u636e\u5e93\u7a7a\u95f4\u4f7f\u7528\u60c5\u51b5
db2 list packages for all<\/p>\n\n\n\n

db2 \"import from tab76.ixf of ixf commitcount 5000 insert into achact\"
db2 \"create table achact_t like achact\"
db2 \"rename table achact_t to achact\"
db2 \"insert into achact_t select * from achact where txndt>=(select lstpgdt from
acmact where actno=achact.actno)\"
db2 get snapshot for dynaimic sql on jining
\u5220\u9664\u4e00\u4e2a\u5b9e\u4f8b\uff1a
# cd \/usr\/lpp\/db2_07_01\/instance
# .\/db2idrop InstName
\u5217\u51fa\u6240\u6709DB2\u5b9e\u4f8b\uff1a
# cd \/usr\/lpp\/db2_07_01\/bin
# .\/db2ilist
\u4e3a\u6570\u636e\u5e93\u5efa\u7acb\u7f16\u76ee
$ db2 catalog db btpdbs on \/db2catalog
\u53d6\u6d88\u5df2\u7f16\u76ee\u7684\u6570\u636e\u5e93btpdbs
$ db2 uncatalog db btpdbs
\u67e5\u770b\u7248\u672c
# db2level
\u663e\u793a\u5f53\u524d\u6570\u636e\u5e93\u7ba1\u7406\u5b9e\u4f8b
$ db2 get instance
\u8bbe\u7f6e\u5b9e\u4f8b\u7cfb\u7edf\u542f\u52a8\u65f6\u662f\u5426\u81ea\u52a8\u542f\u52a8\u3002
$ db2iauto -on \u81ea\u52a8\u542f\u52a8
$ db2iauto -off \u4e0d\u81ea\u52a8\u542f\u52a8
\u6570\u636e\u5e93\u4f18\u5316\u547d\u4ee4\uff1a
reorg\u3001runstats
\u5f53\u6570\u636e\u5e93\u7ecf\u8fc7\u4e00\u6bb5\u65f6\u95f4\u4f7f\u7528\uff0c\u6570\u636e\u7a7a\u95f4\u4f1a\u53d8\u5f97\u8d8a\u6765\u8d8a\u5e9e\u5927\u3002\u4e00\u4e9bdelete\u6389
\u7684\u6570\u636e\u4ecd\u5b58\u653e\u5728\u6570\u636e\u5e93\u4e2d\uff0c\u5360\u7528\u6570\u636e\u7a7a\u95f4\uff0c\u5f71\u54cd\u7cfb\u7edf\u6027\u80fd\u3002\u56e0\u6b64\u9700\u8981\u5b9a\u671f
\u8fd0\u884creorg\u3001runstats\u547d\u4ee4\uff0c\u6e05\u9664\u5df2delete\u7684\u6570\u636e\uff0c\u4f18\u5316\u6570\u636e\u7ed3\u6784\u3002
db2 reorg table \u8868\u540d
db2 runstats on table \u8868\u540d with distribution and indexes all
\u56e0\u4e3a\u8981\u4f18\u5316\u7684\u8868\u6bd4\u8f83\u591a\uff0c\u6240\u4ee5\u5728\/btp\/bin\u76ee\u5f55\u4e0b\u63d0\u4f9b\u4e86\u4e00\u4e2ash\u7a0b\u5e8frunsall\uff0c
\u53ef\u5728\u5f53\u5929\u4e1a\u52a1\u7ed3\u675f\u540e\uff0c\u8fd0\u884crunsall\uff0c\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u4f18\u5316<\/p>\n\n\n\n

\u5728DB2\u7684\u5f00\u53d1\u8fc7\u7a0b\u4e2d\uff0c\u8d2f\u7a7f\u6574\u4e2a\u5f00\u53d1\u8fc7\u7a0b\u8fd8\u6709\u5f88\u91cd\u8981\u7684\u4e00\u90e8\u5206\u5de5\u4f5c\u5c31\u662f\u6570\u636e\u5e93\u7684\u7ef4\u62a4\uff1b\u5bf9\u4e8e\u7ef4\u62a4\u4e00\u4e2a\u5e9e\u5927\u4fe1\u606f\u7cfb\u7edf\u6765\u8bf4\u662f\u975e\u5e38\u5fc5\u8981\u7684\uff1b\u7559\u4e00\u4efd\u7b80\u6613\u7684\u7ef4\u62a4\u624b\u518c\uff0c\u4ee5\u5907\u4e0d\u65f6\u4e4b\u9700\uff1b\u4ee5\u4e0b\u6536\u96c6\u5230\u7684\u90e8\u5206\u7ef4\u62a4\u547d\u4ee4\uff0c\u4ee5\u98e8\u6211\u4eec\u7684\u7ef4\u62a4\u5de5\u7a0b\u5e08\u548c\u9879\u76ee\u7ecf\u7406\u3002
=================================================================
38\u3001\u66f4\u6539db2\u65e5\u5fd7\u7a7a\u95f4\u7684\u5927\u5c0f
\u5907\u6ce8\uff1a\u4ee5\u4e0b\u547d\u4ee4\u4e3a\u4e86\u9632\u6b62db2\u6570\u636e\u5e93\u8fc7\u4efd\u4f7f\u7528\u786c\u76d8\u7a7a\u95f4\u800c\u8bbe\uff0c\u4ec5\u7528\u4e8e\u5f00\u53d1\u8005\u81ea\u5df1\u673a\u5668\u4e0a\u7684db2\uff0c\u5982\u679c\u662f\u670d\u52a1\u5668\uff0c\u5219\u53c2\u6570\u9700\u8981\u4fee\u6539\u3002
# db2 UPDATE DB CFG FOR <db name> USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600;
\u5982\u679c\u9875\u5927\u5c0f\u662f4KB\uff0c\u5219\u4ee5\u4e0a\u547d\u4ee4\u521b\u5efa3\u4e2a100M\u7684\u65e5\u5fd7\u6587\u4ef6\uff0c\u5360\u7528300MB\u786c\u76d8\u7a7a\u95f4\u300225600*4KB=102400KB\u3002
39\u3001\u521b\u5efa\u4e34\u65f6\u8868\u7a7a\u95f4
#DB2 CREATE USER TEMPORARY TABLESPACE STMASPACE PAGESIZE 32 K MANAGED BY DATABASE USING (FILE 'D:\\DB2_TAB\\STMASPACE.F1' 10000) EXTENTSIZE 256
40\u3001\u521b\u5efa\u8868\u7a7a\u95f4
rem \u521b\u5efa\u7f13\u51b2\u6c60\u7a7a\u95f4 8K
#db2 connect to gather
#db2 CREATE BUFFERPOOL STMABMP IMMEDIATE SIZE 25000 PAGESIZE 8K
rem \u521b\u5efa\u8868\u7a7a\u95f4\uff1aSTMA
rem \u5fc5\u987b\u786e\u8ba4\u8def\u5f84\u6b63\u786e
rem D:\\DB2Container\\Stma
#db2 drop tablespace stma
#db2 CREATE REGULAR TABLESPACE STMA PAGESIZE 8 K MANAGED BY SYSTEM USING ('D:\\DB2Container\\Stma' ) EXTENTSIZE 8 OVERHEAD 10.5 PREFETCHSIZE 8 TRANSFERRATE 0.14 BUFFERPOOL STMABMP DROPPED TABLE RECOVERY OFF
#db2 connect reset
41\u3001\u5c06\u6682\u6302\u7684\u6570\u636e\u6062\u590d\u5230\u524d\u6eda\u72b6\u6001
#db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE<\/p>\n\n\n\n

42\u3001\u5907\u4efd\u8868\u7a7a\u95f4
#BACKUP DATABASE YNDC TABLESPACE ( USERSPACE1 ) TO \"D:\\temp\" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
43\u3001\u521b\u5efadb2\u5de5\u5177\u6570\u636e\u5e93
#db2 create tools catalog systools create new database toolsdb
44\u3001\u5982\u4f55\u8fdb\u884c\u589e\u91cf\/\u5dee\u91cf\u5907\u4efd
\u589e\u91cf\uff1a\u4e0a\u4e00\u6b21\u5b8c\u6574\u5907\u4efd\u81f3\u672c\u6b21\u5907\u4efd\u4e4b\u95f4\u589e\u52a0\u7684\u6570\u636e\u90e8\u5206\uff1b
\u5dee\u91cf(delta)\uff1a\u4e0a\u6b21\u5907\u4efd\u4ee5\u6765\uff08\u53ef\u80fd\u662f\u5b8c\u6574\u5907\u4efd\u3001\u589e\u91cf\u5907\u4efd\u6216\u8005\u5dee\u91cf\u5907\u4efd\uff09\u81f3\u672c\u6b21\u5907\u4efd\u4e4b\u95f4\u589e\u52a0\u7684\u6570\u636e\u90e8\u5206\uff1b
45\u3001\u66f4\u65b0\u6240\u6709\u8868\u7684\u7edf\u8ba1\u4fe1\u606f
#db2 -v connect to DB_NAME
#db2 -v \"select tbname, nleaf, nlevels, stats_timefrom sysibm.sysindexes\"
#db2 -v reorgchkupdate statistics on table all
#db2 -v \"select tbname, nleaf, nlevels, stats_timefrom sysibm.sysindexes\"
#db2 -v terminate<\/p>\n\n\n\n

46\u3001\u5bf9\u4e00\u5f20\u8868\u8fd0\u884c\u7edf\u8ba1\u4fe1\u606f
#db2 -v runstatson table TAB_NAMEand indexes all
47\u3001\u67e5\u770b\u662f\u5426\u5bf9\u6570\u636e\u5e93\u6267\u884c\u4e86RUNSTATS
#db2 -v \"select tbname, nleaf, nlevels,stats_timefrom sysibm.sysindexes\"
48\u3001\u66f4\u6539\u7f13\u51b2\u6c60\u7684\u5927\u5c0f
\u7f13\u51b2\u6c60\u4e2d\uff0c\u5f53syscat.bufferpools\u7684npages\u662f-1\u65f6\uff0c\u7531\u6570\u636e\u5e93\u7684\u914d\u7f6e\u53c2\u6570bufferpage\u63a7\u5236\u7f13\u51b2\u6c60\u7684\u5927\u5c0f\u3002
\u5c06npages\u7684\u503c\u66f4\u6539\u4e3a-1\u7684\u547d\u4ee4\uff1a
#db2 -v connect to DB_NAME
#db2 -v select * from syscat.bufferpools
#db2 -v alter bufferpoolIBMDEFAULTBP size -1
#db2 -v connect reset
#db2 -v terminate
\u66f4\u6539\u6570\u636e\u5e93\u914d\u7f6e\u53c2\u6570BufferPages\u7684\u547d\u4ee4\u5982\u4e0b\uff1a
#db2 -v update db cfgfor dbnameusing BUFFPAGE bigger_value
#db2 -v terminate
49\u3001\u770b\u6570\u636e\u5e93\u76d1\u89c6\u5185\u5bb9\u5217\u8868
#db2 -v get monitor switches
50\u3001\u6253\u5f00\u67d0\u4e2a\u6570\u636e\u5e93\u76d1\u89c6\u5185\u5bb9
#db2 -v update monitor switches using bufferpoolon
51\u3001\u83b7\u53d6\u6570\u636e\u5e93\u5feb\u7167
#db2 -v get snapshot for all databases > snap.out
#db2 -v get snapshot for dbm>> snap.out
#db2 -v get snapshot for all bufferpools>> snap.out
#db2 -v terminate<\/p>\n\n\n\n

52\u3001\u91cd\u7f6e\u6570\u636e\u5e93\u5feb\u7167
#db2 -v reset monitor all
53\u3001\u8ba1\u7b97\u7f13\u51b2\u6c60\u547d\u4e2d\u7387
\u7406\u60f3\u60c5\u51b5\u4e0b\u7f13\u51b2\u6c60\u547d\u4e2d\u7387\u572895%\u4ee5\u4e0a\uff0c\u8ba1\u7b97\u516c\u5f0f\u5982\u4e0b\uff1a
(1 -((buffer pool data physical reads + buffer pool index physical reads)
\/(buffer pool data logical reads + pool index logical reads))) *100%
=========\u6570\u636e\u5e93\u5b9e\u4f8b========================
54\u3001\u521b\u5efadb2\u5b9e\u4f8b
#db2icrt <\u5b9e\u4f8b\u540d\u79f0>
55\u3001\u5220\u9664db2\u5b9e\u4f8b
#db2idrop <\u5b9e\u4f8b\u540d\u79f0>
56\u3001\u8bbe\u7f6e\u5f53\u524ddb2\u5b9e\u4f8b
#set db2intance=db2
57\u3001\u663e\u793adb2\u62e5\u6709\u7684\u5b9e\u4f8b
#db2ilist
58\u3001\u6062\u590d\u79bb\u7ebf\u589e\u91cf\u5907\u4efd\u6570\u636e\u5e93\u7684\u547d\u4ee4
#DB2 RESTORE DATABASE YNDC INCREMENTAL AUTOMATIC FROM D:\\backup\\autobak\\db2 TAKEN AT 20060314232015
59\u3001\u521b\u5efa\u6837\u672c\u6570\u636e\u5e93
\u5728unix\u5e73\u53f0\uff0c\u4f7f\u7528\uff1a
#sqllib\/bin\/db2sampl <path>
\u5728windows,os\/2\u5e73\u53f0\uff0c\u4f7f\u7528\uff1adb2sampl e,e\u662f\u53ef\u9009\u53c2\u6570\uff0c\u6307\u5b9a\u5c06\u521b\u5efa\u6570\u636e\u5e93\u7684\u9a71\u52a8\u5668<\/p>\n\n\n\n

60\u3001\u8bbe\u7f6e\u8054\u5408\u6570\u636e\u5e93\u4e3a\u53ef\u7528\uff08\u9ed8\u8ba4\u8054\u5408\u6570\u636e\u5e93\u4e0d\u53ef\u7528\uff09<\/p>\n\n\n\n

#db2 update dbm cfg using federated yes<\/p>\n\n\n\n

61\u3001\u5217\u51fa\u6570\u636e\u5e93\u4e2d\u6240\u6709\u7684\u8868
#db2 list tables
62\u3001\u6570\u636e\u8fc1\u79fb\u65b9\u6cd51
export\u811a\u672c\u793a\u4f8b
#db2 connect to testdb user test password test
#db2 \"export to aa1.ixf of ixf select * from table1\"
#db2 \"export to aa2.ixf of ixf select * from table2\"
#db2 connect reset
import\u811a\u672c\u793a\u4f8b
#db2 connect to testdb user test password test
#db2 \"load from aa1.ixf of ixf replace into table1 COPY NO without prompting \"
#db2 \"load from aa2.ixf of ixf replace into table2 COPY NO without prompting \"
<\/p>\n\n\n\n

#db2 connect reset<\/p>\n\n\n\n

\u8f6c\u8f7d\u81eahttp:\/\/www.jb51.net\/article\/21172.htm<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

1\u3001 \u6253\u5f00\u547d\u4ee4\u884c\u7a97\u53e3\u3000 #db2cmd2\u3001 \u6253\u5f00\u63a7\u5236\u4e2d\u5fc3\u3000 #db2cmd db2cc3\u3001<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,18],"tags":[],"yoast_head":"\nDB2\u5e38\u7528\u547d\u4ee4 - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DB2\u5e38\u7528\u547d\u4ee4 - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4\" \/>\n<meta property=\"og:description\" content=\"1\u3001 \u6253\u5f00\u547d\u4ee4\u884c\u7a97\u53e3\u3000 #db2cmd2\u3001 \u6253\u5f00\u63a7\u5236\u4e2d\u5fc3\u3000 #db2cmd db2cc3\u3001\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\" \/>\n<meta property=\"og:site_name\" content=\"Linux\u81ea\u52a8\u5316\u8fd0\u7ef4\" \/>\n<meta property=\"article:published_time\" content=\"2019-10-23T06:50:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-10-23T06:50:18+00:00\" \/>\n<meta name=\"author\" content=\"\u7ba1\u7406\u5458\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\"},\"author\":{\"name\":\"\u7ba1\u7406\u5458\",\"@id\":\"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1\"},\"headline\":\"DB2\u5e38\u7528\u547d\u4ee4\",\"datePublished\":\"2019-10-23T06:50:16+00:00\",\"dateModified\":\"2019-10-23T06:50:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\"},\"wordCount\":1172,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1\"},\"articleSection\":[\"Linux\",\"Linux\u670d\u52a1\"],\"inLanguage\":\"zh-CN\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\",\"url\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\",\"name\":\"DB2\u5e38\u7528\u547d\u4ee4 - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4\",\"isPartOf\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/#website\"},\"datePublished\":\"2019-10-23T06:50:16+00:00\",\"dateModified\":\"2019-10-23T06:50:18+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#breadcrumb\"},\"inLanguage\":\"zh-CN\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.linuxdevops.cn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Linux\",\"item\":\"https:\/\/www.linuxdevops.cn\/linux\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"DB2\u5e38\u7528\u547d\u4ee4\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.linuxdevops.cn\/#website\",\"url\":\"https:\/\/www.linuxdevops.cn\/\",\"name\":\"Linux\u81ea\u52a8\u5316\u8fd0\u7ef4\",\"description\":\"Linux\u81ea\u52a8\u5316\u8fd0\u7ef4\u7b14\u8bb0\",\"publisher\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1\"},\"alternateName\":\"linuxdevops\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.linuxdevops.cn\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-CN\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1\",\"name\":\"\u7ba1\u7406\u5458\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-CN\",\"@id\":\"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/www.linuxdevops.cn\/wp-content\/uploads\/2019\/07\/cropped-index.jpg\",\"contentUrl\":\"https:\/\/www.linuxdevops.cn\/wp-content\/uploads\/2019\/07\/cropped-index.jpg\",\"width\":512,\"height\":512,\"caption\":\"\u7ba1\u7406\u5458\"},\"logo\":{\"@id\":\"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/image\/\"},\"description\":\"\u7ba1\u7406\u5458\",\"url\":\"https:\/\/www.linuxdevops.cn\/author\/root\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DB2\u5e38\u7528\u547d\u4ee4 - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/","og_locale":"zh_CN","og_type":"article","og_title":"DB2\u5e38\u7528\u547d\u4ee4 - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4","og_description":"1\u3001 \u6253\u5f00\u547d\u4ee4\u884c\u7a97\u53e3\u3000 #db2cmd2\u3001 \u6253\u5f00\u63a7\u5236\u4e2d\u5fc3\u3000 #db2cmd db2cc3\u3001","og_url":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/","og_site_name":"Linux\u81ea\u52a8\u5316\u8fd0\u7ef4","article_published_time":"2019-10-23T06:50:16+00:00","article_modified_time":"2019-10-23T06:50:18+00:00","author":"\u7ba1\u7406\u5458","twitter_card":"summary_large_image","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#article","isPartOf":{"@id":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/"},"author":{"name":"\u7ba1\u7406\u5458","@id":"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1"},"headline":"DB2\u5e38\u7528\u547d\u4ee4","datePublished":"2019-10-23T06:50:16+00:00","dateModified":"2019-10-23T06:50:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/"},"wordCount":1172,"commentCount":0,"publisher":{"@id":"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1"},"articleSection":["Linux","Linux\u670d\u52a1"],"inLanguage":"zh-CN","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/","url":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/","name":"DB2\u5e38\u7528\u547d\u4ee4 - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4","isPartOf":{"@id":"https:\/\/www.linuxdevops.cn\/#website"},"datePublished":"2019-10-23T06:50:16+00:00","dateModified":"2019-10-23T06:50:18+00:00","breadcrumb":{"@id":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#breadcrumb"},"inLanguage":"zh-CN","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.linuxdevops.cn\/2019\/10\/db2-common-commands\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.linuxdevops.cn\/"},{"@type":"ListItem","position":2,"name":"Linux","item":"https:\/\/www.linuxdevops.cn\/linux\/"},{"@type":"ListItem","position":3,"name":"DB2\u5e38\u7528\u547d\u4ee4"}]},{"@type":"WebSite","@id":"https:\/\/www.linuxdevops.cn\/#website","url":"https:\/\/www.linuxdevops.cn\/","name":"Linux\u81ea\u52a8\u5316\u8fd0\u7ef4","description":"Linux\u81ea\u52a8\u5316\u8fd0\u7ef4\u7b14\u8bb0","publisher":{"@id":"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1"},"alternateName":"linuxdevops","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.linuxdevops.cn\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-CN"},{"@type":["Person","Organization"],"@id":"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/3e206335d5796fdd8679e449df72a0d1","name":"\u7ba1\u7406\u5458","image":{"@type":"ImageObject","inLanguage":"zh-CN","@id":"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/image\/","url":"https:\/\/www.linuxdevops.cn\/wp-content\/uploads\/2019\/07\/cropped-index.jpg","contentUrl":"https:\/\/www.linuxdevops.cn\/wp-content\/uploads\/2019\/07\/cropped-index.jpg","width":512,"height":512,"caption":"\u7ba1\u7406\u5458"},"logo":{"@id":"https:\/\/www.linuxdevops.cn\/#\/schema\/person\/image\/"},"description":"\u7ba1\u7406\u5458","url":"https:\/\/www.linuxdevops.cn\/author\/root\/"}]}},"_links":{"self":[{"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/posts\/606"}],"collection":[{"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/comments?post=606"}],"version-history":[{"count":1,"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/posts\/606\/revisions"}],"predecessor-version":[{"id":607,"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/posts\/606\/revisions\/607"}],"wp:attachment":[{"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/media?parent=606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/categories?post=606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.linuxdevops.cn\/wp-json\/wp\/v2\/tags?post=606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}