db2IBM的DB29.1Oracle相比一些命令有db2客戶端并通過在運(yùn)行中輸入db2cmd進(jìn)行初始1、db2connectto數(shù)據(jù)庫名db2connectto<數(shù)據(jù)庫名>user<用戶名>using<>--連接到遠(yuǎn)端數(shù)據(jù)庫2、db2 applicationall--強(qiáng)迫所有應(yīng)用斷開數(shù)據(jù)庫連接3、db2backupdbdb2name<數(shù)據(jù)庫名稱備份整個數(shù)據(jù)庫數(shù)據(jù)db2restoredb<db2name>--還原數(shù)據(jù)庫4、db2listapplication查看所有連接（需要連接到具體數(shù)據(jù)庫才能查看）5、db2start--啟動數(shù)據(jù)庫db2stop6、createdatabase數(shù)據(jù)庫名usingcodesetutf-8territoryCNutf-87、db2catalogdb2catalogtcpipnode<接點(diǎn)名稱>remote<數(shù)據(jù)庫地址>server<端>--把數(shù)據(jù)50000db2catalogdb<數(shù)據(jù)庫名稱>as<接點(diǎn)名稱>atnodePUB11--數(shù)據(jù)庫名稱到本地接db2CONNECTTO<接點(diǎn)名稱>user<用戶名>using<>--連接本地接點(diǎn)數(shù)據(jù)db2look-d<數(shù)據(jù)庫名>-u<用戶>-e-o<名稱>.sql--導(dǎo)出數(shù)據(jù)庫的表結(jié)構(gòu)，其中用戶空db2admin/db2inst1db2look-d<數(shù)據(jù)庫名>-u<用戶>-t<表1><表2>-e-o<名稱>.sql--導(dǎo)出數(shù)據(jù)庫中表2db2move數(shù)據(jù)庫名exportdb2move數(shù)據(jù)庫名exporttn1>,<2>導(dǎo)出數(shù)據(jù)庫中表和表數(shù)據(jù)db2-tvf<名稱>.sql--把上述導(dǎo)出的表結(jié)構(gòu)導(dǎo)入到數(shù)據(jù)庫表結(jié)db2move<數(shù)據(jù)庫名>load-lorece--把上述“db2move<數(shù)據(jù)庫名>export“導(dǎo)出的數(shù)據(jù)導(dǎo)入在實(shí)際使用過程中，如果用到db2bydefault，而不是always，功能是便10、db2connectresetdb2terminate斷開與數(shù)據(jù)庫的連接11、db2setdb2codepage=1208--120812、db2describetable<表名>--查看表結(jié)構(gòu)13、db2listtables查看數(shù)據(jù)庫中所有表結(jié)構(gòu)listtablesforsystem--列出所有系統(tǒng)表14、db2listtablespaces列出表空間15、fetchfirst10rowsonly10條數(shù)據(jù)例如：select*from<表名>fetchfirst10rowsonly)--例如：selectcoalesce(id,1)from表名idnull17、dayofweek(日期)--計算出日期中是（1是周日，2是周 7是周六dayofweek_iso--計算出日期中是(1是周 7是周日例如：dayofweek(date(2008-01-164，代表星期三dayofweek_iso(date(2008-01-16))--3，代表星期三18、dayofyear(日期)--1-366范圍之內(nèi) ')),substr(openDate,7,2)))asweek)19、concatt(1，連接值把參數(shù)１加上連接值組成一個新值。例如：concat('aa','b')--aaboracledb2數(shù)據(jù)庫中，有些字段類型需要進(jìn)行轉(zhuǎn)換，具體看：oracledb2數(shù)據(jù)connectto[數(shù)據(jù)庫名]user[操作用戶名]using[createbufferpoolibmdefault8kIMMEDIATESIZE5000PAGESIZE8Kcreatebufferpoolibmdefault16kIMMEDIATESIZE5000PAGESIZE16Kcreatebufferpoolibmdefault32kIMMEDIATESIZE5000PAGESIZE32KPAGESIZE8KMANAGEDBYSYSTEMUSING('/home/exoa2/exoacontainer') ('/home/exoa2/exoacontainer16k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT16KOVERHEAD24.1TRANSFERRATE0.90DROPPEDTABLERECOVERYCREATETABLESPACEexoatbs32kINDATABASEPARTITIONGROUPIBMDEFAULTGROUPPAGESIZE32KMANAGEDBYSYSTEMUSING('/home/exoa2/exoacontainer32k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT32KOVERHEAD24.1TRANSFERRATE0.90DROPPEDTABLERECOVERYGRANTUSEOFTABLESPACEexoatbsTOPUBLIC;GRANTUSEOFTABLESPACEexoatbs16kTOPUBLIC;CREATETEMPORARYTABLESPACEexoasystmpINDATABASEPARTITIONGROUPIBMTEMPGROUPPAGESIZE8KMANAGEDBYSYSTEMUSING('/home/exoa2/exoasystmp')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT8KOVERHEAD24.10TRANSFERRATE0.90DROPPEDTABLERECOVERYCREATETEMPORARYTABLESPACEexoasystmp16kINDATABASEPARTITIONGROUPIBMTEMPGROUPPAGESIZE16KMANAGEDBYSYSTEMUSING('/home/exoa2/exoasystmp16k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLCREATETEMPORARYTABLESPACEexoasystmp32kINDATABASEPARTITIONGROUPIBMTEMPGROUPPAGESIZE32KMANAGEDBYSYSTEMUSING('/home/exoa2/exoasystmp32k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLdb2getdb2getdbmdb2updatedbmcfgusingpara_namedb2createdbdb2getdbcfgfordb2updatedbcfgfortestusingpara_namedb2dropdbtestdb2connecttotestdb2listtablespacesshowdetail查詢數(shù)據(jù)db2select*from刪除數(shù)據(jù)db2deletefromtb1where創(chuàng)建索引db2createindexidx1on創(chuàng)建視圖db2createviewview1asselectidfrom查詢視圖db2select*fromdb2catalogtcpnodenode_nameremoteserver_ipserver察看端db2getdbmcfg|grepdb2attachtodb2listnodedirecotrydb2uncatalognodedb2catalogdbdb_nameasdb_aliasatnodedb2listdbdirectorydb2connecttodb_aliasuseruser_nameusingdb2uncatalogdbdb2exporttomyfileofixfmessagesmsgselect*fromdb2importfrommyfileofixfmessagesmsgreceintodb2movetestexportdb2look-ddb_alias-a-e-m-l-x-f-odb2createdbdb2-tvfdb2movedb_aliasimportdb2reorgchkdb2reorgtabledb2runstatsontabletestdb2backupdbtesttestdb2restoredbtestdb2listtablespacecontainersfortbs_idshow創(chuàng)建表db2ceatetabletb1(idintegernotnull,namedb2listtables插入數(shù)據(jù)db2insertintotb1values(1,’sam’);db2insertintotb2values(2,’smitty’);.connecttosample1userdb2admincreatealiasdb2admin.tablesforcreatealiasdb2admin.columnsforsyscat.columns;createaliasguest.columnsforsyscat.columns;createtablezjt_tables(select*fromtables)definitiononly;createtablezjt_viewsas(select*fromviews)definitioninsertintozjt_tablesselect*fromtables;insertintozjt_viewsselect*fromcreateviewV_zjt_tablesasselecttabschema,tabnamefromCREATETRIGGERzjt_tables_delAFTERDELETEONzjt_tablesREFERENCINGOLDASOFOREACHROWMODEInsertintozjt_tables1CREATEUNIQUEINDEXI_ztables_tabname[size=3]ONzjt_tables(tabname);selecttabnamefromtablesselectSUBSTR(COLNAME,1,20)as列名,TYPENAMEas類型,LENGTHasfromwheredb2describetableuser1.departmentdb2describeselect*fromuser.tablesdb2describeindexesfortableselectviewnamefromwhereselectindnamefromwhereSELECTSUBSTR(PROCSCHEMA,1,15),SUBSTR( FROMSYSCAT.PROCEDURES;ipselectcast(ipasinteger)+50 connectdisconnectviewapplicationkillAPPLICATION(0);db2applicationsall(強(qiáng)迫所有應(yīng)用程序從數(shù)據(jù)庫斷開locklocktabletestinexclusivelocktabletestinsharelistlisttablesforlistactivelistcommandlistGETgrantselect,delete,insert,updateontablestousergrantallontablestouserWITHGRANTOPTION程序包GRANTTOPUBLIC模式數(shù)據(jù)庫grantconnect,createtab,dbadmondatabaseto索引grantcontrolonindexindex-nametoXXXnnnnn例:?SQL幫助(SQL語句的語法)helpstatement例如，helpSQLSTATE幫助(SQL的狀態(tài)和類別代碼sqlstate?class-db2adminsetidusernameSAMPLE!SELECTEMPNO,CAST(RESUMEASVARCHAR(370))FROMEMP_RESUME要運(yùn)行DB2Java過程或UDF，還需要更新服務(wù)器上的DB2數(shù)據(jù)庫管理程序配置，以包括在該機(jī)器上安裝JDK的路徑db2updatedbmcfgusingJDK11_PATHd:sqllibjavajdkupdatedbmcfgusingSPM_NAMEDB2db2getdbmSELECTDISTINCTGRANTEE,GRANTEETYPE,'TABLE'FROMSYSCAT.TABAUTH SELECTDISTINCTGRANTEE,GRANTEETYPE,'INDEX'FROMSYSCAT.INDEXAUTHSELECTDISTINCTGRANTEE,GRANTEETYPE,'COLUMN'FROMSYSCAT.COLAUTH ORDERBYGRANTEE,GRANTEETYPE,createtableyhdab(idvarchar(10),passwordvarchar(10),ywlxvarchar(10),khvarchar(10));createtableywlbb(ywlbbhvarchar(8),ywmcvarchar(60))altertableyhdabALTERkhSETDATATYPEvarchar(13);altertableyhdabALTERIDSETDATATYPEvarchar(13);altertablelst_bsialterbsi_moneysetdatatypeint;insertintoyhdabvaluesinsertintoywlbbvalues('user06','修改('user07','打印CONNECTTOCONNECTRESET;BACKUPDATABASEEXOATO"/home/exoa2/db2bak/"WITH2BUFFERSBUFFER1024PARALLELISM1WITHOUTPROMPTING;CONNECTTOEXOA;CONNECTRESET;db2connecttodb2look-dYOURDB-a-e-x-oSQLdb2moveYOURDBexportdb2movevi如要導(dǎo)入的數(shù)據(jù)庫名與原數(shù)據(jù)庫不同，要修改creatab.sqlCONNECT項(xiàng)db2moveNEWDBdb2listtablespacesshow總頁數(shù)=可用頁數(shù)已用頁數(shù)空閑頁數(shù)=高水位標(biāo)記（頁）=不適用頁大?。ㄗ止?jié)）=4096盤區(qū)大?。摚?32預(yù)大小（頁）=32容器數(shù)=1db2selecttabname,tableidfromsyscat.tableswhere查看是哪掛 db2loadfromtab11.ixfofixfterminateintodb2admin.xxxxxxxxxtab11.ixfxxxxxxxxx表db2listtablespacesshowdetail不能通過GRANT的權(quán)限有哪種?如何知道用戶下的VIEW數(shù)?DB2的版本?如何知道用戶下的TRIGGER數(shù)?TABLESPACE的狀況?SEQUENCE的狀況?SCHEMA的狀況?如何知道INDEX的狀況?如何知道DB2的數(shù)據(jù)類型?DB2數(shù)據(jù)庫的方法如下：db2inst1用戶下操作. $db2stop（停止數(shù)據(jù)庫$db2start（啟動數(shù)據(jù)庫$db2connectto數(shù)據(jù)庫名user用戶名using（連接數(shù)據(jù)庫$db2backupdb $db2restoredbtakenat20070814031212（DB2數(shù)據(jù)庫，所以總是努力去看這方面的書，一段時間來有點(diǎn)體會也總結(jié)了一些常用的DB2命令，發(fā)出來給大家吧！希望對大家會有所幫忙，DB2服務(wù)：db2startDB2服務(wù):db2stop1,默認(rèn)為“,db2"importfrombtpoper.txtofdelinsertintobtpoper"2、以指定分隔符“|”加載db2"importfrombtpoper.txtofdelmodifiedbycoldel|insertinto1db2"exporttobtpoper.txtofdelselect*fromdb2exporttobtpoper.txtofdelmodifiedbycoldel|select*frombtpoper"2、帶條件卸載一個表中數(shù)據(jù)db2"exporttobtpoper.txtofdelselect*frombtpoperwherebrhid=' db2"exporttocmmcode.txtofdelselect*fromcmmcodewherecodtp='01'"db2"exporttocmmcode.txtofdelmodifiedbycoldel|select*fromcmmcodewheredb2"select*fromdb2"select*frombtpoperwherebrhid=' 'andoprid='0001'"db2"selectoprid,oprnm,brhid,passwdfrombtpoper"db2"deletefromdb2"deletefrombtpoperwhere 'or db2"updatesvmmstsetprtlines=0wherebrhid=' 'andjobtp='02'"db2"updatesvmmstsetprtlines=0wherejobtp='02'orjobtp='03'"db2connecttodb2connectresetdb2terminatedb2applicationsall斷開所有數(shù)據(jù)庫連接1、db2backupdbbtpdbs2db2movebtpdbsexportdb2look-dbtpdbs-e-x[-a]-o1db2restoredbbtpdbswithoutrollingforward2、db2-tvfcrtdb.sqlcrtdb.sql文件內(nèi)容：createdbbtpdbsondb2catalogdb2-stvfcrttbl.sqldb2movebtpdbsimport十、DB2幫助命令：db2?db2?db2sqlcode例：db2sql0803)注：code44db2bind/btp/bin/bndallbtp/tran/bnddb2getdbmcfgdb2getdbcfgfordb2updatedbcfgforbtpdbsusingLOGBUFSZ20db2updatedbcfgforbtpdbsusingLOGFILSIZ5120db2stopdb2db2setschemabtp修改當(dāng)前模式為db2listtablespacesshowdetaildb2listtablespacecontainersfor2showdetailtablespaceid=2db2listdb2listdbdirectorydb2listactivedatabasesdb2listtablesforalldb2listtablesforschemabtpschemabtpdb2listtablespacesshowdetaildb2listpackagesfordb2"importfromtab76.ixfofixfcommitcount5000insertintoachact"db2"createtableachact_tlikeachact"db2"renametableachact_ttodb2"insertintoachact_tselect*fromachactwheretxndt>=(selectlstpgdtfromacmactwhereactno=achact.actno)"db2getsnapshotfordynaimicsqlon#cd/usr/lpp/db2_07_01/instance#./db2idropInstName列出所有DB2#cd/usr/lpp/db2_07_01/bin#./db2ilist$db2catalogdbbtpdbson取消已編目的數(shù)據(jù)庫$db2uncatalogdb#$db2get$db2iautoon$db2iautooff不自動啟動delete掉reorg、runstatsdelete的數(shù)據(jù)，優(yōu)化數(shù)據(jù)結(jié)構(gòu)。db2reorgtabledb2runstatsontablewithdistributionandindexes 下提供了一個sh程序runsall，可在當(dāng)天業(yè)務(wù)結(jié)束后，運(yùn)行runsall，對數(shù)據(jù)庫進(jìn)行優(yōu)化IBMDB2forAIXDB2 來安裝該CD-ROM：#mkdir-p/cdrom其中cdrom表示CD-ROM安裝 輸入如下命令，來分配CD-ROM文件系統(tǒng)：#smittyCDROMCDROMCD-ROMsmit在安 字段中輸入值。例如，此值可能是/cdromcd./db2setupDB2DB2V6TabEnterDB2DB2AdministrationDB2UDBenterpriseeditionDB2SoftwareDeveloperKit 支持是否建立DB2管理帳戶,一般選擇建立db2管理用戶db2as（UID，GroupIdhome路徑，在語 支持是否建立DB2的實(shí)例,一般選擇建立db2db2inst1:db2iadm1（UID，GroupId路徑 支持， 庫如果在獨(dú)立服務(wù)器上安裝使用，可選擇autostart，但在aix+hacmp+db2 中DB2DIR=或者要創(chuàng)建或添加新的實(shí)例DB2sample數(shù)據(jù)庫，在安裝完成后可以通過命令（db2sampl）創(chuàng)建SAMPLE數(shù)據(jù)庫，作為具有系統(tǒng)管理(SYSADM)權(quán)限的用戶向系統(tǒng)。如DB2samplSAMPLESAMPLESAMPLEDB2startSAMPLE20中工作的所有雇員的DB2connecttoDB2"select*fromstaffwheredept=DB2connectSAMPLEDB2dropdatabasesample命令SAMPLE若尚未未安裝DB2工具，則您可以通過創(chuàng)建SAMPLE數(shù)據(jù)庫，并通過使用命令行步驟作為具有系統(tǒng)管理(SYSADM)權(quán)限的用戶向系統(tǒng)步驟db2samplSAMPLESAMPLESAMPLE將它自動編目。步驟3.db2start步驟SAMPLE20中工作的所有雇員的列表，db2connecttodb2"select*fromstaffwheredept=20"db2connectreset如以上命令均正確執(zhí)行,則證明DB2UDB數(shù)據(jù)庫服務(wù)器的安裝已經(jīng)成功完成db2中的常用命令及使用方法一高（重要度）db2netstartdb2netstopdb2=>catalogdb2=>dropviewdb2=>createtabledb2=>listactivedb2=>application以用戶<userid>通過使用<pwd>與標(biāo)識為<node>的實(shí)例連接db2=>attachto<node>user<userid>using<dbname>db2=>createdatabasedb2=>deactivatedatabase根據(jù)需要，顯式地以用戶<userid>和<pwd>與數(shù)據(jù)庫<dbname>連接db2=>connectto<dbname>[[user<userid>]usingdb2=>connectdb2=>listtables[for{user|all|system|schema<schemaname>}][showdb2=>describetablerunstatsdb2=>reorgchkontabledb2=>reorgtabledb2=>IMPORTdb2=>LOADdb2=>loadquerytable<tbname>[tolocal-message-file][nosummary|summaryonly]db2=>createindexindexnameontabledb2=>createindexindexnameontable(collumnallowreversescans23執(zhí)行數(shù)據(jù)庫備份db2=>backupdatabase<dbnametopath>]24執(zhí)行數(shù)據(jù)庫恢復(fù)db2=>restoredatabase<dbname>[fromdb2=>createtriggerdb2=>listtablespace[showdb2=>listtablespacecontainersfornamesdelname.deldb2=>exporttoname.delofdelselect*fromnamesDELNAMES.DEL文件導(dǎo)入到名為NAMESdb2=>importfromnames.delofdelinsertintoIXF類型文件product.ixfproductdb2=>loadformproduct.ixfofixfinsertintodb2=>loadquerytablesalestod:\將數(shù)據(jù)庫db2certnewcertdb2=>db2movedb2certdb2=>db2movenewcertimport–ioredb2=>reorgtabledb2=>reorgtabletablenameindex將數(shù)據(jù)庫db2cert備份到c:\dbbackupdb2=>backupdatabasedb2certtoc:\dbbackup c:\dbbackup下還原數(shù)據(jù)庫db2certdb2=>restoredatabasedb2certfromc:\dbbackupdb2=>restoredatabasedb2certfromc:\dbbackupdb2=>intonewdbdb2=>with2db2=>buffer512db2=>withoutrollingforword備份數(shù)據(jù)庫db2cert的編目表空間syscatspace和用戶表空間filets到 db2=>backupdatabasedb2certtablespace(syscatspace,filets)to以聯(lián)機(jī)模式從c:\dbbackup 還原數(shù)據(jù)庫db2cert的表空間filetsdb2=>restoredatabasedb2certtablespace(filets)onlinefrom前滾由restoredb2cert,前滾執(zhí)行至日志的結(jié)尾db2=>rollforwarddatabasedb2certtoendoflogsdb2=>quiescetablespacefordb2=>declarecursor1cursorwithhold(如果沒有加withhold選項(xiàng),CommitRollback時,該游標(biāo)將被關(guān)閉forselectmarket_codefromtb_market_codeforupdate(cursor1定義為可修改db2=>db2調(diào)用過db2=>call過程名(參數(shù)列表將過程導(dǎo)出db2=>getroutineinto文件名fromprocedure過程導(dǎo)入過db2=>putroutinefromdb2=>connecttodatabase_nameuser用戶名usingcreatealiasdb2admin.tablesforsysstat.tablescreatealiasdb2admin.viewsfrosyscat.viewscreatealiasdb2admin.columnsforsyscat.columnscreatealiasguest.columnsforsyscat.columnsdb2=>insertintozjt_tablesselect*fromtables53建立唯一性索引db2=>CREATEUNIQUEINDEXI_ztables_tabnameONzjt_tables(tabname)54db2db2describeindexesfortableuser1.department55重新連接db2=>connectreset56db2=>disconnect（重要度<dbname>的所有正?？煺眨╒8）db2=>gethealthsnapshotforallon<dbname>db2=>getadmin<p><v>db2=>updateadmincfgusing<p><v>db2=>createtablespacedb2=>reorgchkupdatestatisticsontabledb2=>reorgchkcurrentstatisticsontabledb2=>reorgchkcurrentstatisticsontabledb2=>rollforwarddatabasedb2certquerydb2=>listhistoryallfordb2=>updatedbmconfigurationusingdft_mon_stmtsql信息db2=>updatemonitorswitchesusingstatementon12顯示所有連接進(jìn)程db2=>db2listdb2=>db2listapplicationsshowdb2=>db2getsnapshotforlocksons