DB2常用命令01.doc

1、启动DB2服务器DB2START2、停止DB2服务器DB2STOP3、强制停止DB2服务器DB2STOP FORCE4、创建数据库DB2 CREATE DATABASE PjjTest5、删除数据库DB2 DROP DATABASE PjjTest6、连接数据库-默认以Windows登陆用户连接数据库DB2 CONNECT TO PjjTest-用指定用户连接数据库DB2 CONNECT TO PjjTest USER db2admin USING *7、断开数据库连接-断开连接DB2 CONNECT RESET-终止数据库运行的命令并断开数据库连接DB2 TERMINATE8、列出DB2实例DB2ILIST9、列出DB2当前实例DB2 GET INSTANCE10、察看实例配置文件-默认DB2 GET DBM CFG-分屏显示DB2 GET DBM CFG | More-将显示结果存入 pjj.out 文件中DB2 GET DBM CFG pjj.out11、更新数据库管理器参数DB2 UPDATE DBM CFG USING Para_Name Para_Value12、察看指定数据库的配置参数-默认DB2 GET DB CFG FOR PjjTest-分屏显示DB2 GET DB CFG FOR PjjTest | More-将显示结果存入 pjj.out 文件中DB2 GET DB CFG FOR PjjTest pjj.out13、列出当前活动的数据库DB2 LIST ACTIVE DATABASES14、列出所有系统数据库目录DB2 LIST DATABASE DIRECTORY15、列出所有DBMS节点目录DB2 LIST NODE DIRECTORY16、列出应用程序信息-默认DB2 LIST APPLICATIONS-列出详细信息DB2 LIST APPLICATIONS SHOW DETAIL-列出指定数据库的应用程序连接DB2 LIST APPLICATIONS FOR DB PjjTest-列出指定数据库的应用程序连接的详细信息DB2 LIST APPLICATIONS FOR DB PjjTest SHOW DETAIL17、列出DB2分区信息-默认DB2 LIST DATABASE PARTITION GROUPS-列出详细信息DB2 LIST DATABASE PARTITION GROUPS SHOW DETAIL18、列出数据库表空间-默认DB2 LIST TABLESPACES-列出详细信息DB2 LIST TABLESPACES SHOW DETAIL19、列出 DB2 命令行处理器选项设置DB2 LIST COMMAND OPTIONS20、列出 DB2 相关历史DB2 LIST HISTORY BACKUP | ROLLFORWARD | REORG |CREATE TABLESPACE | ALTER TABLESPACE | DROPPED TABLE | LOAD |RENAME TABLESPACE | ARCHIVE LOGALL | SINCE timestamp |CONTAINING schema.object_name | object_nameFOR DATABASE database-alias21、获取 DB2 相关帮助信息-获取数据库提示信息帮助DB2 ? SQLCODE-获取数据库命令信息帮助DB2 ? LISTdb2命令：db2cmddb2startdb2stopdb2 CREATE DATABASE cpgtestdb2 connect to cpgtest user db2admin using db2admindb2admindb2 disconnect cpgtest先执行db2，然后所执行的命令都可以直接输入db2connect to cpgtest user db2admin using db2admindb2adminCREATE TABLE classtable (FILEID VARCHAR(80) NOT NULL,LF_SENDTIME BIGINT)insert into classtable (fileid,lf_sendtime) values (001,19999999)insert into classtable (fileid,lf_sendtime) values (003,19999999)update classtable set lf_sendtime=29999999 where fileid=002delete from classtable where fileid=003/ 插入查询结果insert into leader_org (select useruuid,orguuid,flag,2006 from leader_org where year=2004)/ 插入查询结果(差集)insert into leader_org (select useruuid,orguuid,flag,2005 from leader_org where year=2004) except (select useruuid,orguuid,flag,2005 from leader_org where year=2005)/ 查询某组织的所有祖先组织select cnname,orglevelcode from ro_org where orglevelcode is not null and ltrim(orglevelcode) and deltag=0 and orglevelcode=substr(0001000200010007aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,1,length(orglevelcode);如果未执行db2，则在所执行的命令前加上“db2 ”如：db2 insert into classtable (fileid,lf_sendtime) values (003,19999999)db2 drop table classtabledb2move cpgtest export 导出数据库db2 drop database cpgtest 删除数据库db2 CREATE DATABASE cpgtest2 创建数据库db2move cpgtest2 import 导入数据库LIST TABLES 列出所有用户表LIST DATABASE DIRECTORY 列出所有可用数据库list node direcotory 列出服务器目录describe table TABLENAME 列出表结构/ 设置TID字段自增1 CREATE TABLE TESTTABLE(TID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER),TNAME VARCHAR(80) NOT NULL,TSEX CHAR NOT NULL DEFAULT 1);/ 设置FM_ID为PK（主键）ALTER TABLE FM_FILEMANAGER ADD PRIMARY KEY (FM_ID );/ 备份数据库，本地数据库将备份到本地C:下，而远程数据库将备份到远程机器上的相应路径下C:db2 backup db pmis233 user db2admin using db2admindb2admin/ 恢复数据库命令restore database pmis233 from c: into new_pmis without prompting/ 命令行方式下连接远程服务节点CATALOG TCPIP NODE pmis remote pmis server 50000/ 命令行方式下连接远程服务节点上的数据库catalog db portal as portal at node pmis/ 除去远程连接uncatalog db portaluncatalog node pmis/运行sql脚本文件C:db2 -tvf d:/createbase.sql/察看数据库portal配置文件的内容get db configuration for portal/查询当前时间SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1/创建序列CREATE SEQUENCE orders_seq AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDERINSERT INTO customer_orders_t VALUES (NEXT VALUE FOR orders_seq)ALTER SEQUENCE orders_seq RESTART WITH 57232/给库表增加字段CONNECT TO OAAO USER db2admin using db2admin;ALTER TABLE DB2ADMIN.FUHE_CLASS ADD COLUMN TS TIMESTAMP ;CONNECT RESET;/ 取前5行 fetch first n rows onlyselect * from ro_org where orgcode like % fetch first 5 rows onlyselect orglevelcode from ro_org where orglevelcode in (00010001,0001000200120004) fetch first 5 rows onlyselect count(*) as num, apersonid from questions group by apersonid order by num fetch first 1 rows onlycoobi 7486709murphy 1597820/ 创建表空间缓冲池CONNECT TO PLAN2;CREATE Bufferpool NEWPOOL IMMEDIATE SIZE 250 PAGESIZE 8 K ;CONNECT RESET;/ 创建表空间CONNECT TO PLAN2;CREATE REGULAR TABLESPACE NEWSPACE PAGESIZE 8 K MANAGED BY SYSTEM USING (C:DB2newspace ) EXTENTSIZE 16 OVERHEAD 12.67 PREFETCHSIZE 16 TRANSFERRATE 0.18 BUFFERPOOL NEWPOOL DROPPED TABLE RECOVERY OFF;COMMENT ON TABLESPACE NEWSPACE IS 8;CONNECT RESET;DB2：当修改了机器名后，要修改文件C:Program FilesIBMSQLLIBDB2db2nodes.cfg/ 列出、修改命令行的执行参数db2 list command optionsdb2 UPDATE COMMAND OPTIONS USING options . 如： update command options using c off - 设置为不自动提交*/修改数据库配置信息，使其可以联机备份DB2 CONNECT TO SAMPLEDB2 UPDATE DATABASE CONFIGURATION USING LOGRETAIN on DEFERRED DB2 UPDATE DATABASE CONFIGURATION USING USEREXIT on DEFERRED DB2 CONNECT RESET/修改数据库配置信息，使其可以增量备份DB2 CONNECT TO SAMPLEDB2 UPDATE DATABASE CONFIGURATION USING TRACKMOD on DEFERRED DB2 CONNECT RESET/脱机备份 完全BACKUP DATABASE SAMPLE TO D: WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;/联机备份BACKUP DATABASE SAMPLE ONLINE TO D: WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;/恢复RESTORE DATABASE SAMPLE FROM D: INTO XMLTREE2/前滚ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE;DB2 CONNECT TO SAMPLEDB2 UPDATE DATABASE CONFIGURATION USING TRACKMOD on DEFERRED DB2 CONNECT RESETDB2 BACKUP DATABASE SAMPLE TO D: WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTINGDB2 BACKUP DATABASE SAMPLE INCREMENTAL TO D: WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTINGDB2 BACKUP DATABASE SAMPLE INCREMENTAL DELTA TO D: WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTINGRESTORE DATABASE SAMPLE FROM D: INTO LAW2RESTORE DATABASE SAMPLE INCREMENTAL FROM D: TAKEN AT 20040910100320 INTO LAW2 WITHOUT PROMPTINGCONNECT TO SAMPLE;QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;CONNECT RESET;BACKUP DATABASE SAMPLE INCREMENTAL TO C: WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;CONNECT TO SAMPLE;UNQUIESCE DATABASE;CONNECT RESET;db2 RESTORE DATABASE SAMPLE INCREMENTAL FROM C: TAKEN AT 20040916104824 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING数据库方法：替换字符串例子：将nodeinfo表里email字段中原先的 cnao01MAIL.CNAO. 替换为 cnao01CNAO.update nodeinfo set email=replace(email,MAIL.,) 导出表：ixf格式db2 export to nodeinfo_all.out of ixf select * from nodeinfo导入表：db2 import from nodeinfo_all.out of ixf replace into nodeinfo设置当前模式：db2 set current schema=sjtj检查上一句sql脚本的执行结果：db2 values( %errorlevel% )windowsdb2 values( $? )linux变量$?保留前一次SQL语句执行的返回码。如果$?为0，则