DB2常用指令

db2 常用命令db2 常用命令1. db2 "get dbm cfg" ——获取当前数据库配置信息。

2. db2 list db directory ——列出数据库的目录。

3. db2 list node directory ——列出节点的目录。

4. db2 "start database <dbname>" ——启动指定的数据库。

5. db2 "stop database <dbname>" ——停止指定的数据库。

6. db2 "connect to <dbname>" ——连接到指定的数据库。

7. db2 "update dbm cfg using max application <number>" ——修改数据库最大连接数。

8. db2 list active databases ——列出所有活动的数据库。

9. db2 get snapshot for dynamic sql on <database> ——列出动态SQL的快照。

10. db2 terminate ——立即终止当前会话。

11. db2 "create database <dbname>" ——创建新的数据库。

12. db2 connect reset ——重置数据库连接。

13. db2 "list tables" ——列出表格列表。

14. db2 "describe table <tablename>" ——查看表格结构。

15. db2 "select * from <tablename>" ——查询指定表格的信息。

16. db2 "drop table <tablename>" ——删除指定的表格。

DB2数据库必须掌握的五十四条常用语句数据库查询语句学习DB2数据库必须掌握的五十四条常用语句1、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，显示日期不详,并按部门排序输出,日期格式为yyyy-mm-ddselectemp_no,emp_name,dept,isnull(convert(char(10),birthday,120),'日期不详') birthdayfrom employeeorder by dept2、查找与喻自强在同一个单位的员工姓名、性别、部门和职称select emp_no,emp_name,dept,titlefrom employeewhere emp_name<>'喻自强' and dept in(select dept from employeewhere emp_name='喻自强')3、按部门进行汇总，统计每个部门的总工资select dept,sum(salary)from employeegroup by dept4、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售数量、单价和金额select a.prod_id,qty,unit_price,unit_price*qty totpricefrom sale_item a,product bwhere a.prod_id=b.prod_id and prod_name='14寸显示器'5、在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额select prod_id,sum(qty) totqty,sum(qty*unit_price) totpricefrom sale_itemgroup by prod_id6、使用convert函数按客户编号统计每个客户1996年的订单总金额select cust_id,sum(tot_amt) totpricefrom saleswhere convert(char(4),order_date,120)='1996'group by cust_id7、查找有销售记录的客户编号、名称和订单总额select a.cust_id,cust_name,sum(tot_amt) totpricefrom customer a,sales bwhere a.cust_id=b.cust_idgroup by a.cust_id,cust_name8、查找在1997年中有销售记录的客户编号、名称和订单总额select a.cust_id,cust_name,sum(tot_amt) totpricefrom customer a,sales bwhere a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'group by a.cust_id,cust_name9、查找一次销售最大的销售记录select order_no,cust_id,sale_id,tot_amtfrom saleswhere tot_amt=(select max(tot_amt)from sales)10、查找至少有3次销售的业务员名单和销售日期select emp_name,order_datefrom employee a,sales bwhere emp_no=sale_id and a.emp_no in (select sale_idfrom salesgroup by sale_idhaving count(*)>=3)order by emp_name11、用存在量词查找没有订货记录的客户名称select cust_namefrom customer awhere not exists(select *from sales bwhere a.cust_id=b.cust_id)12、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额订货日期不要显示时间，日期格式为yyyy-mm-dd按客户编号排序，同一客户再按订单降序排序输出selecta.cust_id,cust_name,convert(char(10),order_date,120),tot_amtfrom customer a left outer join sales b on a.cust_id=b.cust_idorder by a.cust_id,tot_amt desc13、查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用男、女表示select emp_name 姓名, 性别= case a.sex when 'm' then '男'when 'f' then '女'else '未'end,销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),qty 数量, qty*unit_price as 金额from employee a, sales b, sale_item c,product dwhere d.prod_name='16M DRAM' and d.prod_id=c.prod_id anda.emp_no=b.sale_id and b.order_no=c.order_no14、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、产品名称、数量、单价、金额和销售日期select emp_no 编号,emp_name 姓名, 性别= case a.sex when 'm' then '男'when 'f' then '女'else '未'end,prod_name 产品名称,销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),qty 数量, qty*unit_price as 金额from employee a left outer join sales b on a.emp_no=b.sale_id , sale_item c,product dwhere d.prod_id=c.prod_id and b.order_no=c.order_no15、查找销售金额最大的客户名称和总货款select cust_name,d.cust_sumfrom customer a,(select cust_id,cust_sumfrom (select cust_id, sum(tot_amt) as cust_sumfrom salesgroup by cust_id ) bwhere b.cust_sum =( select max(cust_sum)from (select cust_id, sum(tot_amt) as cust_sumfrom salesgroup by cust_id ) c )) dwhere a.cust_id=d.cust_id16、查找销售总额少于1000元的销售员编号、姓名和销售额select emp_no,emp_name,d.sale_sumfrom employee a,(select sale_id,sale_sumfrom (select sale_id, sum(tot_amt) as sale_sumfrom salesgroup by sale_id ) bwhere b.sale_sum <1000) dwhere a.emp_no=d.sale_id17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额selecta.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_pric efrom customer a, product b, sales c, sale_item dwhere a.cust_id=c.cust_id and d.prod_id=b.prod_id andc.order_no=d.order_no and a.cust_id in (select cust_idfrom (select cust_id,count(distinct prod_id) prodidfrom (select cust_id,prod_idfrom sales e,sale_item fwhere e.order_no=f.order_no) ggroup by cust_idhaving count(distinct prod_id)>=3) h )18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、商品名称、数量和金额selecta.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_pricefrom customer a, product b, sales c, sale_item dwhere a.cust_id=c.cust_id and d.prod_id=b.prod_id andc.order_no=d.order_no and not exists(select f.*from customer x ,sales e, sale_item fwhere cust_name='世界技术开发公司' and x.cust_id=e.cust_id ande.order_no=f.order_no and not exists( select g.*from sale_item g, sales hwhere g.prod_id = f.prod_id and g.order_no=h.order_no andh.cust_id=a.cust_id))19、查找表中所有姓刘的职工的工号，部门，薪水select emp_no,emp_name,dept,salaryfrom employeewhere emp_name like '刘%'20、查找所有定单金额高于2000的所有客户编号select cust_idfrom saleswhere tot_amt>200021、统计表中员工的薪水在4000-6000之间的人数select count(*)as 人数from employeewhere salary between 4000 and 600022、查询表中的同一部门的职工的平均工资，但只查询"住址"是"上海市"的员工select avg(salary) avg_sal,deptfrom employeewhere addr like '上海市%'group by dept23、将表中住址为"上海市"的员工住址改为"北京市"update employeeset addr like '北京市'where addr like '上海市'24、查找业务部或会计部的女员工的基本信息select emp_no,emp_name,deptfrom employeewhere sex='F'and dept in ('业务','会计')25、显示每种产品的销售金额总和，并依销售金额由大到小输出select prod_id ,sum(qty*unit_price)from sale_itemgroup by prod_idorder by sum(qty*unit_price) desc26、选取编号界于'C0001'和'C0004'的客户编号、客户名称、客户地址select CUST_ID,cust_name,addrfrom customerwhere cust_id between 'C0001' AND 'C0004'27、计算出一共销售了几种产品select count(distinct prod_id) as '共销售产品数'from sale_item28、将业务部员工的薪水上调3%update employeeset salary=salary*1.03where dept='业务'29、由employee表中查找出薪水最低的员工信息select *from employeewhere salary=(select min(salary )from employee )30、使用join查询客户姓名为"客户丙"所购货物的"客户名称","定单金额","定货日期","电话号码"select a.cust_id,b.tot_amt,b.order_date,a.tel_nofrom customer a join sales bon a.cust_id=b.cust_id and cust_name like '客户丙'31、由sales表中查找出订单金额大于"E0013业务员在1996/10/15这天所接每一张订单的金额"的所有订单select *from saleswhere tot_amt>all(select tot_amtfrom saleswhere sale_id='E0013'and order_date='1996/10/15') order by tot_amt32、计算'P0001'产品的平均销售单价select avg(unit_price)from sale_itemwhere prod_id='P0001'33、找出公司女员工所接的定单select sale_id,tot_amtfrom saleswhere sale_id in(select sale_id from employeewhere sex='F')34、找出同一天进入公司服务的员工select a.emp_no,a.emp_name,a.date_hiredfrom employee ajoin employee bon (a.emp_no!=b.emp_no and a.date_hired=b.date_hired) order by a.date_hired35、找出目前业绩超过232000元的员工编号和姓名select emp_no,emp_namefrom employeewhere emp_no in(select sale_idfrom salesgroup by sale_idhaving sum(tot_amt)<232000)36、查询出employee表中所有女职工的平均工资和住址在"上海市"的所有女职工的平均工资select avg(salary)from employeewhere sex like 'f'unionselect avg(salary)from employeewhere sex like 'f' and addr like '上海市%'37、在employee表中查询薪水超过员工平均薪水的员工信息Select *from employeewhere salary>( select avg(salary)from employee)38、找出目前销售业绩超过10000元的业务员编号及销售业绩，并按销售业绩从大到小排序Select sale_id ,sum(tot_amt)from salesgroup by sale_idhaving sum(tot_amt)>10000order by sum(tot_amt) desc39、找出公司男业务员所接且订单金额超过2000元的订单号及订单金额Select order_no,tot_amtFrom sales ,employeeWhere sale_id=emp_no and sex='M' and tot_amt>200040、查询sales表中订单金额最高的订单号及订单金额Select order_no,tot_amt from saleswhere tot_amt=(select max(tot_amt) from sales)41、查询在每张订单中订购金额超过4000元的客户名及其地址Select cust_name,addr from customer a,sales bwhere a.cust_id=b.cust_id and tot_amt>400042、求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列Select cust_id,sum(tot_amt) from salesGroup by cust_idOrder by sum(tot_amt) desc43、求每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列Select cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)From sales a, sale_item bWhere a.order_no=b.order_noGroup by cust_id,prod_idOrder by cust_id,prod_id44、查询订购了三种以上产品的订单号Select order_nofrom sale_itemGroup by order_noHaving count(*)>345、查询订购的产品至少包含了订单3号中所订购产品的订单Select distinct order_noFrom sale_item aWhere order_no<>'3'and not exists (Select * from sale_item b where order_no ='3' and not exists(select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id))46、在sales表中查找出订单金额大于"E0013业务员在1996/11/10这天所接每一张订单的金额"的所有订单，并显示承接这些订单的业务员和该订单的金额Select sale_id,tot_amt from saleswhere tot_amt>all(select tot_amtfrom saleswhere sale_id='E0013' and order_date='1996-11-10')47、查询末承接业务的员工的信息Select *From employee aWhere not exists(select * from sales b where a.emp_no=b.sale_id)48、查询来自上海市的客户的姓名，电话、订单号及订单金额Select cust_name,tel_no,order_no,tot_amtFrom customer a ,sales bWhere a.cust_id=b.cust_id and addr='上海市'49、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序Select sale_id,month(order_date), sum(tot_amt)from salesgroup by sale_id,month(order_date)order by sale_id,month(order_date) desc50、求每种产品的总销售数量及总销售金额，要求显示出产品编号、产品名称，总数量及总金额，并按产品号从小到大排列Select a.prod_id,prod_name,sum(qty),sum(qty*unit_price)From sale_item a,product bWhere a.prod_id=b.prod_idGroup by a.prod_id,prod_nameOrder by a.prod_id51、查询总订购金额超过'C0002'客户的总订购金额的客户号，客户名及其住址Select cust_id, cust_name,addrFrom customerWhere cust_id in (select cust_id from salesGroup by cust_idHaving sum(tot_amt)>(Select sum(tot_amt) from sales where cust_id='C0002'))52、查询业绩最好的的业务员号、业务员名及其总销售金额select emp_no,emp_name,sum(tot_amt)from employee a,sales bwhere a.emp_no=b.sale_idgroup by emp_no,emp_namehaving sum(tot_amt)=(select max(totamt)from (select sale_id,sum(tot_amt) totamtfrom salesgroup by sale_id) c)53、查询每位客户所订购的每种产品的详细清单，要求显示出客户号，客户名，产品号，产品名，数量及单价select a.cust_id, cust_name,c.prod_id,prod_name,qty, unit_pricefrom customer a,sales b, sale_item c ,product dwhere a.cust_id=b.cust_id and b.order_no=c.order_no and c.prod_id=d.prod_id54、求各部门的平均薪水，要求按平均薪水从小到大排序select dept,avg(salary)from employeegroup by deptorder by avg(salary)如果DB2 v8 的用户需要提取数据库所有存储过程的定义，即用CREATE PROCEDURE 创建存储过程的语句，以便保留或在其它数据库环境下重新创建这些存储过程，可尝试如下介绍的三种方法：方法一：从 DB2 的目录表 SYSCAT.ROUTINES 中选取：在 SYSCAT.ROUTINES 系统目录表中，有一个名为 TEXT 的字段，其数据类型定义为CLOB，长度为2M（2097152 个字节），用于存放存储过程的创建语句。

DB2命令⼤全check Archiving processing查看⽇志归档情况db2 "SELECT DATE(CAST(START_TIME as TIMESTAMP)) as DATE,count(*) as NUMBER_OF_LOGS_PER_DAY,(count(*)*23.4375) as AMOUNT_LOGS_DAY_MB,DBPARTITIONNUM as DBPARTFROM SYSIBMADM.DB_HISTORYWHERE operation = 'X' -- Archive logsand OPERATIONTYPE = '1' -- 1 = first log archive methodand TIMESTAMP(END_TIME) > CURRENT_TIMESTAMP - 10 DAYSGROUP BY DATE(CAST(START_TIME as TIMESTAMP)) , DBPARTITIONNUMORDER BY DATE DESC "查看过去24⼩时是否进⾏过备份[db2inst1@db2v9r7 ~]$]db2 "select substr(comment,1,30) as comment, timestamp(start_time) as start_time, timestamp(end_time) as end_time, substr(firstlog,1,25) as firstlog, substr(lastlog,1,25) as lastlog, seqnum, substr(location,1,50) as location from sysibmadm.db_history where operation = 'B' and timestamp(start_time) > current_timestamp - 24 hours and sqlcode is null "Dprop checkCapture side:db2 "SELECT SYNCHTIME, CURRENT TIMESTAMP AS CURRENT_TIMESTAMP FROM ASN.IBMSNAP_REGISTER WHERE GLOBAL_RECORD='Y' with ur"Apply side:db2 "select APPLY_QUAL, SET_NAME, SOURCE_ALIAS, TARGET_ALIAS, ACTIVATE, STATUS, LASTRUN, LASTSUCCESS, SYNCHTIME, SLEEP_MINUTES,REFRESH_TYPE from ASN.IBMSNAP_SUBS_SET"### 查看hadr 环境$ db2pd -alldbs -hadrdb2pd -db sfa -hadr#### 在 server端查询node 使⽤空间SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 /1024 AS DEC(8,2))as "Space in TB" ,SUM(num_files)as "Number of files" FROM occupancy GROUP BY node_name ORDER BY "Space in TB" DESCDB2跟oracle不⼀样，⽤户都是操作系统创建的⽽且⽤户没有所谓的默认表空间，默认临时表空间等等整个数据库的默认表空间就是数据库创建的默认表空间，usertablespace没有专门记录所有⽤户的视图，但是有个sysibmadm.privileges记录所有的⽤户权限所以可以认为它就是专门记录⽤户的视图。

DB2命令一、常用命令1、建立数据库DB2_GCBCREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCBUSING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 322、连接数据库connect to sample1 user db2admin using 83012063、建立用户、建立表别名create alias db2admin.tables for sysstat.tables;CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWScreate alias db2admin.columns for syscat.columns;create alias guest.columns for syscat.columns;GRANTDBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ ROUTINE ON DATABASE TO USER GUEST;GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA DB2ADMIN TO USER GUEST WITH GRANT OPTION;4、建立、更改、删除表结构CREATE TABLE table_name(column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY], column2 DATATYPE [NOT NULL],...)create table zjt_tables as(select * from tables) definition only;create table zjt_views as(select * from views) definition only;ALTER TABLE table_name ADD COLUMN column_name DATATYPEALTER TABLE table_name ADD PRIMARY KEY (column_name)ALTER TABLE table_name DROP PRIMARY KEY (column_name)DROP table table_name5、插入记录，修改记录，删除记录insert into zjt_tables select * from tables;insert into zjt_views select * from views;insert into test(id,phone) values(1,’5’);update test set phone=’65356675’ where id=1;delete from test where id=1;6、建立视图create view V_zjt_tables as select tabschema,tabname from zjt_tables;7、建立触发器CREATE TRIGGER zjt_tables_delAFTER DELETE ON zjt_tablesREFERENCING OLD AS OFOR EACH ROW MODE DB2SQLInsert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10))8、建立、删除唯一性索引CREATE UNIQUE INDEX I_ztables_tabnameON zjt_tables(tabname);DROP index I_ztables_tabname9、查看表select tabname from tableswhere tabname='ZJT_TABLES';10、查看列select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度from columnswhere tabname='ZJT_TABLES';11、查看表结构db2 describe table user1.departmentdb2 describe select * from user.tables12、查看表的索引db2 describe indexes for table user1.department13、查看视图select viewname from viewswhere viewname='V_ZJT_TABLES';14、查看索引select indname from indexeswhere indname='I_ZTABLES_TABNAME';15、查看存贮过程SELECTSUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES;16、类型转换(cast)ip datatype:varcharselect cast(ip as integer)+50 from log_comm_failed17、重新连接connect reset18、中断数据库连接disconnect db2_gcb19、view applicationLIST APPLICATION;20、kill applicationFORCE APPLICATION(0);db2 force applications all (强迫所有应用程序从数据库断开)21、lock tablelock table test in exclusive mode22、共享lock table test in share mode23、显示当前用户所有表list tables24、列出所有的系统表list tables for system25、显示当前活动数据库list active databases26、查看并设置命令选项list command optionsupdate command options using c off27、系统数据库目录LIST DATABASE DIRECTORY28、表空间list tablespacesCREATE [{REGULAR | LARGE | SYSTEM TEMPORARY | USER TEMPORARY}] TABLESPACE table_space_name [PAGESIZE integer [K]]MANAGED BY {SYSTEM | DATABASE}USING (container_definition_string) [BUFFERPOOL buffpool_name]29、表空间容器LIST TABLESPACE CONTAINERS FORExample: LIST TABLESPACE CONTAINERS FOR 130、显示用户数据库的存取权限GET AUTHORIZATIONS31、启动实例DB2START32、停止实例db2stop33、表或视图特权grant select,delete,insert,update on tables to user grant all on tables to user WITH GRANT OPTION34、程序包特权GRANT EXECUTEON PACKAGE PACKAGE-nameTO PUBLIC35、模式特权GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER36、数据库特权grant connect,createtab,dbadm on database to user 37、索引特权grant control on index index-name to user38、信息帮助 (? XXXnnnnn )例:? SQL3008139、SQL 帮助(说明 SQL 语句的语法)help statement例如，help SELECT40、SQLSTATE 帮助(说明 SQL 的状态和类别代码) sqlstate 或 ? class-code41、更改与"管理服务器"相关的口令db2admin setid username password42、创建 SAMPLE 数据库db2sampldb2sampl F:(指定安装盘)43、使用操作系统命令! dir44、转换数据类型 (cast)SELECT EMPNO, CAST(RESUME AS VARCHAR(370))FROM EMP_RESUMEWHERE RESUME_FORMAT = 'ascii'45、UDF要运行 DB2 Java 存储过程或 UDF，还需要更新服务器上的 DB2 数据库管理程序配置，以包括在该机器上安装 JDK 的路径db2 update dbm cfg using JDK11_PATH d:\sqllib\java\jdkTERMINATEupdate dbm cfg using SPM_NAME sample46、检查 DB2 数据库管理程序配置db2 get dbm cfg47、检索具有特权的所有授权名SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTHORDER BY GRANTEE, GRANTEETYPE, 3create table yhdab(id varchar(10),password varchar(10),ywlx varchar(10),kh varchar(10));create table ywlbb(ywlbbh varchar(8),ywmc varchar(60))48、修改表结构alter table yhdab ALTER kh SET DATA TYPE varchar(13); alter table yhdab ALTER ID SET DATA TYPE varchar(13); alter table lst_bsi alter bsi_money set data type int; insert into yhdab values('20000300001','123456','user01','20000300001'),('20000300002','123456','user02','20000300002');49、业务类型说明insert into ywlbb values('user01','业务申请'),('user02','业务撤消'),('user03','费用查询'),('user04','费用自缴'),('user05','费用预存'),('user06','密码修改'),('user07','发票打印'),('gl01','改用户基本信息'),('gl02','更改支付信息'),('gl03','日统计功能'),('gl04','冲帐功能'),('gl05','对帐功能'),('gl06','计费功能'),('gl07','综合统计')50、事务的提交、回滚commit;rollback;51、查看db2的许可证信息Db2licm –l52、显示实例名称Db2ilist53、更新实例Db2iupdt instance_name54、DAS实例的配置信息Db2 get admin cfg55、其他实例的配置信息Db2 get dbm cfg或者db2 get database manager configuration56、数据库的配置信息Db2 get database configuration for database_name57、创建工具目录数据库db2 create tools catalog cc create new database toolsdb58、数据库备份CONNECT TO TAIS;QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;CONNECT RESET;BACKUP DATABASE TAIS TO "D:\backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;CONNECT TO TAIS;UNQUIESCE DATABASE;59、数据库恢复db2 restore database tais from d:\backup taken at 20061017 without rolling forward60、切换实例（windows）set db2instance=tais61、创建全局临时表a、创建用户临时表空间db2 CREATE USER TEMPORARY TABLESPACE user_tempMANAGED BY SYSTEM USING('/home/inst##/usertemp')b、创建全局临时表DECLARE GLOBAL TEMPORARY TABLE table_name1LIKE table_name2 NOT LOGGEDfor example:db2 DECLARE GLOBAL TEMPORARY TABLE temployee LIKE employee NOT LOGGEDc、查看创建过的临时表select * from session.temployee需要取消事务的自动落实(update command options using c off) for example:db2>CONNECT TO sampledb2>LIST COMMAND OPTIONSdb2>UPDATE COMMAND OPTIONS USING C OFFdb2>LIST COMMAND OPTIONSdb2>DECLARE GLOBAL TEMPORARY TABLE tstaffLIKE staff NOT LOGGEDdb2>INSERT INTO session.tstaff SELECT * FROM staffdb2>SELECT * FROM session.staffdb2>COMMITdb2>SELECT * FROM staffdb2>CONNECT RESET62、创建模式名CREATE SCHEMA schema_name AUTHORIZATION auth_name63、断开当前的连接db2 DISCONNECT CURRENT64、创建视图CREATE VIEW view_name (column_names)AS fullselectWITH {LOCAL|CASCADED} CHECK OPTIONfor example:CREATE view NEW.V3 AS SELECT * FROM NEW.SALES AS SALES;CREATE view NEW.V4 AS SELECT * FROM NEW.SALES AS SALES WITH CASCADED CHECK OPTION;CREATE view NEW.V5 AS SELECT * FROM NEW.SALES AS SALES WITH LOCAL CHECK OPTION;65、创建序列CREATE SEQUENCE ORG_SEQSTART WITH 1INCREMENT BY 1NO MAXVALUENO CYCLECACHE 24--get sequencevalues nextval for seq166、得到当前时间values current timestamp67、创建索引type-2 indexes---对应用和并发进行保护,8版本以后使用a、Unique index —Ensures uniqueness of key column(s) datab、Bidirectional index（双向索引）—Allows scanning of indexes in eitherdirectionc、Clustered index（簇索引）— Places the rows of the tablein the samephysical order as the index keysd、多维索引---数据仓库中使用CREATE UNIQUE INDEX index_nameON table_name (column_name {ASC | DESC} [, column_name {ASC | DESC}…])INCLUDE column_namesCLUSTERPCTFREE integerMINPCTUSED integerALLOW REVERSE SCANSCREATE INDEX inON employee(empno ASC)PCTFREE 10MINPCTUSED 40Design Advisor in the CLP: db2advisfor example:db2advis -d database_name[{-w workload_name |-s "sql_statement" |-i filename}][-a userid[/password] ][-l disklimit][-t max_advise_time][-h][-p][-o out_file]db2advis -d sample-s "SELECT * FROM employee e WHERE firstnmeLIKE ’A%’"-a inst00/inst00-l 53-t 20exercises:a、create normal indexCONNECT TO SAMPLE;CREATE INDEX DB2ADMIN.IDX_STAFF_NAME ON NEW.STAFF ("NAME" ASC) PCTFREE 10 MINPCTUSED 10 COLLECT STATISTICS ;COMMENT ON Index DB2ADMIN.IDX_STAFF_NAME IS 'common index on column name ';CONNECT RESET;b、create unique indexCONNECT TO SAMPLE;CREATE UNIQUE INDEX DB2ADMIN.UIDX_STAFF_ID ON NEW.STAFF (ID ASC) PCTFREE 10 MINPCTUSED 10 COLLECT STATISTICS ;CONNECT RESET;c、create Bidirectional IndexCONNECT TO SAMPLE;CREATE INDEX DB2ADMIN.BIDX_STAFF_SALARY ONNEW.STAFF (SALARY ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS ;CONNECT RESET;d、Create Clustered IndexCONNECT TO SAMPLE;CREATE INDEX DB2ADMIN.CIDX_STAFF_DEPT ON NEW.STAFF (DEPT ASC) CLUSTER PCTFREE 10 MINPCTUSED 10 COLLECT SAMPLED DETAILED STATISTICS ;CONNECT RESET;68、使用约束Using Constraints(主键的字段不可为空，唯一键的字段可以为空)a、Adding a Primary Key to an Existing TableALTER TABLE student ADD CONSTRAINT pk_id PRIMARY KEY(id)exercises:A、Use this command to create a new table called po_masterCREATE TABLE po_master (po_no INTEGER NOT NULL,po_date DATE NOT NULL,bill_no INTEGER NOT NULL,bill_date DATE NOT NULL,description VARCHAR (200),CONSTRAINT pk_po_master PRIMARY KEY (po_no),CONSTRAINT u_key_bill_no UNIQUE (bill_no))B、Use the following command to alter an existing table called po_masterALTER TABLE po_master ADD CONSTRAINT u_key_bill_no UNIQUE (bill_no)C、Using the CLP, create a table called po_master with a primary key on po_no using the following commandCREATE TABLE PO_DETAIL (po_no INTEGER NOT NULL,s_no INTEGER NOT NULL,item_code INTEGER NOT NULL,description VARCHAR (100),quantity INTEGER NOT NULL,rate INTEGER NOT NULL,PRIMARY KEY (po_no, s_no),CONSTRAINT fk_po_masterdetail FOREIGN KEY(po_no)REFERENCES administrator.po_master (po_no))D、There are four CREATE TABLE options for defining the delete rule:NO ACTION,RESTRICT :If RESTRICT or NO ACTION is selected, an error occurs and no records are deleted if you try to delete records from parent table.CASCADE: If CASCADE is selected, the delete operation is propagated to the dependent tables, that is, records in the po_master table as well as all the related records in the po_detailtable are automatically deleted.SET NULL: If SET NULL is selected, the delete operation in po_master table is allowed and the the related records in po_detail table are set to NULL.E、modify table---add restrict constraint(delete)CONNECT TO SAMPLE;ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE RESTRICT ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ;CONNECT RESET;---add no action constraint(delete)CONNECT TO SAMPLE;ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ;CONNECT RESET;---add cascade constraint(delete)CONNECT TO SAMPLE;ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCESDB2ADMIN.PO_MASTER (PO_NO) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ;CONNECT RESET;---add set null constraint(delete)CONNECT TO SAMPLE;ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE SET NULL ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ;CONNECT RESET;---add restrict constraint(update)CONNECT TO SAMPLE;ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE RESTRICT ON UPDATE RESTRICT ENFORCED ENABLE QUERY OPTIMIZATION ;CONNECT RESET;---add no action constraint(update)CONNECT TOSAMPLE;ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE RESTRICT ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ;CONNECT RESET;F、Using Check ConstraintsALTER TABLE po_master ADD CONSTRAINT chk_bill_date CHECK (bill_date <= po_date)69、导入导出数据DB2 supports the following data formats for extraction and insertion:◆ Delimited ASCII format (DEL)◆ Integrated exchange format (IXF)◆ Worksheet format (WSF)◆ Non-delimited ASCII (ASC)CONNECT TO SAMPLE;EXPORT TO "C:\无界定字符" OF DEL MESSAGES "C:\无界定字符.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;EXPORT TO "C:\界定字符" OF DEL MODIFIED BY COLDEL, MESSAGES "C:\界定字符.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;EXPORT TO "C:\工作表格式" OF WSF MODIFIED BY 1 MESSAGES "C:\工作表格式.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;EXPORT TO "C:\集成交换格式" OF IXF MESSAGES "C:\集成交换格式.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;IMPORT FROM "C:\界定字符" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) MESSAGES "C:\界定字符.log" INSERT INTO DB2ADMIN.EMPLOYEE_DUP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM);CONNECT RESET;CONNECT TO SAMPLE;IMPORT FROM "C:\工作表格式" OF WSF MESSAGES "C:\工作表格式.log" INSERT INTO DB2ADMIN.EMPLOYEE_DUP;CONNECT RESET;CONNECT TO SAMPLE;IMPORT FROM "C:\集成交换格式" OF IXF MESSAGES "C:\集成交换格式.log" INSERT INTO DB2ADMIN.EMPLOYEE_DUP;CONNECT RESET;70、装载数据db2 "CONNECT TO sample"db2 "CREATE TABLE exc_emp LIKE employee"db2 "ALTER TABLE exc_emp ADD COLUMN time TIMESTAMP"db2 "ALTER TABLE exc_emp ADD COLUMN message CLOB(32K)"db2 "CREATE TABLE employee_dup LIKE employee"db2 "ALTER TABLE employee_dup ADD CONSTRAINTchk_cnst CHECK(EDLEVEL > 12)"CONNECT TO SAMPLE;LOAD FROM "C:\界定字符" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) MESSAGES "C:\界定字符.log" INSERT INTO DB2ADMIN.EMPLOYEE_DUP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) COPY NO INDEXING MODE AUTOSELECT;CONNECT RESET;---check constraintdb2 SET INTEGRITY FOR employee_dup IMMEDIATE CHECKED FOR EXCEPTION IN employee_dup USE exc_emp71、生成DDL语句---single schemadb2look -d SAMPLE -z NEW -u DB2ADMIN -e -l -x -m -r ;---full databasedb2look -d SAMPLE -a -e -l -x -m -r -f ;72、索和并发控制DB2 provides different levels of protection to isolate data:◆ Uncommitted read◆ Cursor stability◆ Read stability◆ Repeatable read---default isolation levelCursor stabilityexercise(using ur isolation):1.1 Open two Command Line Processor windows. We will refer to these windowsas W1 and W2. Make sure they connect to the correct instance before moving to the nextstep.1.2 Set the AUTOCOMMIT feature OFF in both W1 and W2 by executing this command:db2 => update command options using c off1.3 Change isolation level of W1 to uncommitted read.db2 => CHANGE ISOLATION TO URdb2 => CONNECT TO sample1.4 Now go to W2 where the default isolation is cursor stability. Run these commands:db2 => CONNECT TO sampledb2 => UPDATE staff SET salary = salary + 101.5 Go back to W1 and run the following statement to view dirty records from the staff table.db2 => SELECT * FROM staffYou will see updated, but not committed data in W2. This is known as a dirty read.1.6 Again switch to W2 and roll back the transaction.db2 => ROLLBACK1.7 Now, you can get actual data in W1:db2 => SELECT * FROM staff1.8 Close both windows W1 and W2.exercise(Locking a Database)The syntax for the CONNECT command is shown here:CONNECT TO database_name [IN EXCLUSIVE MODE]Execute the following command:db2 CONNECT TO sample IN EXCLUSIVE MODEUSER your_login USING your_passwordNow, try to connect to sample database as any user other than your_login. The followingmessage is returned:db2 connect to sample user test using testSQL1035N The database is currently in use. SQLSTATE=5701973、出错处理实例参数：diaglevel=3日志文件：D:\IBM\SQLLIB\DB274、View Registry VariablesUse the db2set command to view registry variable values:db2set -i for instance-level parametersdb2set -g for global-level parametersdb2set -I for all the defined profilesdb2set -all for all the registry variables with valuesdb2set -lr for all available parametersTo set a parameter for the current instance:Syntax: db2set parameter=valueExample: db2set DB2COMM=tcpip,npipeTo set a parameter’s value for a specific instance:Syntax: db2set parameter=value -i instance_nameExample: db2set DB2COMM=tcpip,npipe -i altinstTo set a parameter at the global level:Syntax: db2set parameter=value -gExample: db2set DB2COMM=tcpip,npipe -g75、Cataloging the ServerSyntax for cataloging a server:CATALOG TCPIP NODE node_nameREMOTE {hostname | ip_address}SERVER {svcename | port_number}Example:CATALOG TCPIP NODE db2serv REMOTE 9.186.128.141 SERVER 370076、cataloging the databaseSyntax for cataloging a database:CATALOG DATABASE db_name AS db_aliasAT NODE node_nameExample:CATALOG DATABASE sample AS srv_sampAT NODE db2server二、目录视图说明说明目录视图检查约束 SYSCAT.CHECKS列 SYSCAT.COLUMNS检查约束引用的列 SYSCAT.COLCHECKS关键字中使用的列 SYSCAT.KEYCOLUSE数据类型 SYSCAT.DATATYPES函数参数或函数结果 SYSCAT.FUNCPARMS参考约束 SYSCAT.REFERENCES模式 SYSCAT.SCHEMATA表约束 SYSCAT.TABCONST表 SYSCAT.TABLES触发器 SYSCAT.TRIGGERS用户定义函数 SYSCAT.FUNCTIONS视图 SYSCAT.VIEWS三、字符串类型二进制大对象 (BLOB) 字符串。

db2常用命令DB2，全称为DataBase 2，是一种常见的关系数据库管理系统。

作为IBM的一款商业软件，DB2一直获得了正面的反响。

它提供了一丛强大的数据库命令来帮助用户管理关系数据库。

下面介绍一些常用的DB2命令：1、CREATE：该命令可用于创建数据库，表，索引等，例如：CREATE DATABASE dbname;2、DROP：该命令可用于删除数据库，表，索引，和其他数据库对象，例如： DROP DATABASE dbname;3、ALTER：该命令可用于修改数据库，表，索引等，例如：ALTER TABLE tablename ADD COLUMN columnname VARCHAR(20) DEFAULT ‘default value’;4、SELECT：该命令可用于从数据库中检索数据，例如： SELECT * FROM tablename WHERE columnname =’value’;5、INSERT：该命令可用于向数据库中插入数据，例如： INSERT INTO tablename (column1,column2,column3) VALUES(value1,value2,value3 );6、UPDATE：该命令可用于更新数据库中的数据，例如： UPDATE tablename SET column=value WHERE …;7、DELETE：该命令可用于从数据库中删除数据，例如： DELETE FROM tablename WHERE column=value;8、COMMIT：该命令可以提交数据变更，例如： COMMIT;9、GRANT：该命令可用于授权指定用户使用数据库中的资源，例如： GRANT SELECT， INSERT，UPDATE ON tablename TO user1;10、REVOKE：该命令可用于取消指定用户使用数据库中的资源，例如： REVOKE SELECT，INSERT，UPDATE ON tablename FROM user1;综上所述，DB2拥有一系列非常强大的命令，能够方便用户管理关系数据库，帮助用户更高效地完成任务。

DB2数据库的常用操作指令DB2是一种关系型数据库管理系统，其常用操作指令可以帮助用户在数据库中执行各种操作。

以下是DB2数据库的一些常用操作指令。

1.连接数据库：CONNECT TO database_name [USER username USING password]2.断开数据库连接：CONNECTRESET3.创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, ...)4.删除表：DROP TABLE table_name5.修改表结构：ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE datatype6.插入数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)7.更新数据：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition8.删除数据：DELETE FROM table_name WHERE condition9.查询数据：SELECT column1, column2, ... FROM table_name WHERE condition 10.创建索引：CREATE INDEX index_name ON table_name (column1, column2, ...)11.删除索引：DROP INDEX index_name12.创建视图：CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition13.修改视图：ALTER VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition14.删除视图：DROP VIEW view_name15.创建存储过程：CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)LANGUAGESQLBEGIN--存储过程代码END16.删除存储过程：DROP PROCEDURE procedure_name17.创建触发器：CREATE TRIGGER trigger_name BEFORE/AFTERINSERT/UPDATE/DELETE ON table_nameREFERENCING OLD ROW AS old NEW ROW AS newFOREACHROW--触发器代码18.删除触发器：DROP TRIGGER trigger_name19.提交事务：COMMIT20.回滚事务：ROLLBACK21.创建数据库：CREATE DATABASE database_name22.删除数据库：DROP DATABASE database_name以上是DB2数据库的一些常用操作指令，可以帮助用户在数据库中执行各种操作。