Db2_SQL命令大全(完整版)

DB2/SQL命令大全连接数据库：connectto[数据库名]user[操作用户名]using[密码]创建缓冲池(8K)：createbufferpoolibmdefault8kIMMEDIATESIZE5000PAGESIZE8K;创建缓冲池(16K)(OA_DIVERTASKRECORD)：createbufferpoolibmdefault16kIMMEDIATESIZE5000PAGESIZE16K;创建缓冲池(32K)(OA_TASK)：createbufferpoolibmdefault32kIMMEDIATESIZE5000PAGESIZE32K;创建表空间：CREATETABLESPACEexoatbsINDATABASEPARTITIONGROUPIBMDEFAULTGROUPPAGESIZE8KMANAGEDBYSYSTEMUSING('/home/exoa2/exoacontainer')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT8KOVERHEAD24.10TRANSFERRATE0.90DROPPEDTABLERECOVERYOFF;CREATETABLESPACEexoatbs16kINDATABASEPARTITIONGROUPIBMDEFAULTGROUPPAGESIZE16KMANAGEDBYSYSTEMUSING('/home/exoa2/exoacontainer16k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT16KOVERHEAD24.1TRANSFERRATE0.90DROPPEDTABLERECOVERYOFF;CREATETABLESPACEexoatbs32kINDATABASEPARTITIONGROUPIBMDEFAULTGROUPPAGESIZE32KMANAGEDBYSYSTEMUSING('/home/exoa2/exoacontainer32k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT32KOVERHEAD24.1TRANSFERRATE0.90DROPPEDTABLERECOVERYOFF;GRANTUSEOFTABLESPACEexoatbsTOPUBLIC;GRANTUSEOFTABLESPACEexoatbs16kTOPUBLIC;GRANTUSEOFTABLESPACEexoatbs32kTOPUBLIC;创建系统表空间：CREATETEMPORARYTABLESPACEexoasystmpINDATABASEPARTITIONGROUPIBMTEMPGROUPPAGESIZE8KMANAGEDBYSYSTEMUSING('/home/exoa2/exoasystmp')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT8KOVERHEAD24.10TRANSFERRATE0.90DROPPEDTABLERECOVERYOFF;CREATETEMPORARYTABLESPACEexoasystmp16kINDATABASEPARTITIONGROUPIBMTEMPGROUPPAGESIZE16KMANAGEDBYSYSTEMUSING('/home/exoa2/exoasystmp16k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT16KOVERHEAD24.10TRANSFERRATE0.90DROPPEDTABLERECOVERYOFF;CREATETEMPORARYTABLESPACEexoasystmp32kINDATABASEPARTITIONGROUPIBMTEMPGROUPPAGESIZE32KMANAGEDBYSYSTEMUSING('/home/exoa2/exoasystmp32k')EXTENTSIZE32PREFETCHSIZE16BUFFERPOOLIBMDEFAULT32KOVERHEAD24.10TRANSFERRATE0.90DROPPEDTABLERECOVERYOFF;1.启动实例(db2inst1):db2start2.停止实例(db2inst1):db2stop3.列出所有实例(db2inst1)db2ilist5.列出当前实例:db2getinstance4.察看示例配置文件:db2getdbmcfg|more5.更新数据库管理器参数信息:db2updatedbmcfgusingpara_namepara_value6.创建数据库:db2createdbtest7.察看数据库配置参数信息db2getdbcfgfortest|more8.更新数据库参数配置信息db2updatedbcfgfortestusingpara_namepara_value10.删除数据库:db2dropdbtest11.连接数据库db2connecttotest12.列出所有表空间的详细信息。db2listtablespacesshowdetail13.查询数据:db2select*fromtb114.删除数据:db2deletefromtb1whereid=115.创建索引:db2createindexidx1ontb1(id);16.创建视图:db2createviewview1asselectidfromtb117.查询视图:db2select*fromview118.节点编目db2catalogtcpnodenode_nameremoteserver_ipserverserver_port19.察看端口号db2getdbmcfg|grepSVCENAME20.测试节点的附接db2attachtonode_name21.察看本地节点db2listnodedirecotry22.节点反编目db2uncatalognodenode_name23.数据库编目db2catalogdbdb_nameasdb_aliasatnodenode_name24.察看数据库的编目db2listdbdirectory25.连接数据库db2connecttodb_aliasuseruser_nameusinguser_password26.数据库反编目db2uncatalogdbdb_alias27.导出数据db2exporttomyfileofixfmessagesmsgselect*fromtb128.导入数据db2importfrommyfileofixfmessagesmsgreplaceintotb129.导出数据库的所有表数据db2movetestexport30.生成数据库的定义db2look-ddb_alias-a-e-m-l-x-f-odb2look.sql31.创建数据库db2createdbtest132.生成定义db2-tvfdb2look.sql33.导入数据库所有的数据db2movedb_aliasimport34.重组检查db2reorgchk35.重组表tb1db2reorgtabletb136.更新统计信息db2runstatsontabletb137.备份数据库testdb2backupdbtest38.恢复数据库testdb2restoredbtest399\.列出容器的信息db2listtablespacecontainersfortbs_idshowdetail40.创建表:db2ceatetabletb1(idintegernotnull,namechar(10))41.列出所有表db2listtables42.插入数据:db2insertintotb1values(1,’sam’);db2insertintotb2values(2,’smitty’);.建立数据库DB2_GCBCREATEDATABASEDB2_GCBONG:ALIASDB2_GCBUSINGCODESETGBKTERRITORYCNCOLLATEUSINGSYSTEMDFT_EXTENT_SZ322.连接数据库connecttosample1userdb2adminusing83012063.建立别名createaliasdb2admin.tablesforsysstat.tables;CREATEALIASDB2ADMIN.VIEWSFORSYSCAT.VIEWScreatealiasdb2admin.columnsforsyscat.columns;createaliasguest.columnsforsyscat.columns;4.建立表createtablezjt_tablesas(select*fromtables)definitiononly;createtablezjt_viewsas(select*fromviews)definitiononly;5.插入记录insertintozjt_tablesselect*fromtables;insertintozjt_viewsselect*fromviews;6.建立视图createviewV_zjt_tablesasselecttabschema,tabnamefromzjt_tables;7.建立触发器CREATETRIGGERzjt_tables_delAFTERDELETEONzjt_tablesREFERENCINGOLDASOFOREACHROWMODEDB2SQLInsertintozjt_tables1values(substr(o.tabschema,1,8),substr(o.tabname,1,10))8.建立唯一性索引CREATEUNIQUEINDEXI_ztables_tabnameONzjt_tables(tabname);9.查看表selecttabnamefromtableswheretabname='ZJT_TABLES';10.查看列selectSUBSTR(COLNAME,1,20)as列名,TYPENAMEas类型,LENGTHas长度fromcolumnswheretabname='ZJT_TABLES';11.查看表结构db2describetableuser1.departmentdb2describeselect*fromuser.tables12.查看表的索引db2describeindexesfortableuser1.department13.查看视图selectviewnamefromviewswhereviewname='V_ZJT_TABLES';14.查看索引selectindnamefromindexeswhereindname='I_ZTABLES_TABNAME';15.查看存贮过程SELECTSUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)FROMSYSCAT.PROCEDURES;16.类型转换(cast)ipdatatype:varcharselectcast(ipasinteger)+50fromlog_comm_failed17.重新连接connectreset18.中断数据库连接disconnectdb2_gcb19.viewapplicationLISTAPPLICATION;20.killapplicationFORCEAPPLICATION(0);db2forceapplicationsall(强迫所有应用程序从数据库断开)21.locktablelocktabletestinexclusivemode22.共享locktabletestinsharemode23.显示当前用户所有表listtables24.列出所有的系统表listtablesforsystem25.显示当前活动数据库list