db2常用命令(一)

bhdweb

今天有幸把公司产品向db2移植，于是就有了下面的经历。
 
1. 建立数据库DB2_GCB
CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32  
2. 连接数据库 
connect to sample1 user db2admin using 8301206 
3. 建立别名
create alias db2admin.tables for sysstat.tables; CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWS create alias db2admin.columns for syscat.columns; create alias guest.columns for syscat.columns;   
4. 建立表
create table zjt_tables as (select * from tables) definition only; create table zjt_views as (select * from views) definition only;  
5. 插入记录
insert into zjt_tables select * from tables; insert into zjt_views select * from views;  
6. 建立视图
create view V_zjt_tables as select tabschema,tabname from zjt_tables;  
7. 建立触发器
CREATE TRIGGER zjt_tables_del AFTER DELETE ON zjt_tables REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL Insert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10))  
8. 建立唯一性索引
CREATE UNIQUE INDEX I_ztables_tabname ON zjt_tables(tabname);  
9. 查看表
select tabname from tables  where tabname='ZJT_TABLES';  
10. 查看列 
select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度 from columns where tabname='ZJT_TABLES'; 
11. 查看表结构
db2 describe table user1.department db2 describe select * from user.tables  
12. 查看表的索引
db2 describe indexes for table user1.department  
13. 查看视图
select viewname from views  where viewname='V_ZJT_TABLES';  
14. 查看索引
select indname from indexes where indname='I_ZTABLES_TABNAME';  
15. 查看存贮过程
SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES;  
16. 类型转换(cast) 
ip datatype:varchar  select cast(ip as integer)+50 from log_comm_failed