数据库基本命令

﹏ヽ暗。殇╰゛Y 2022-08-18 03:20 201阅读 0赞

1.     查看数据库  
     \[ODS-DB/home/dw\]$db2 list db directory  --查看所有数据库名等信息,  
2.     启动数据库实例  
db2start    --启动实例  
3.     连接到数据库  
     \[ODS-DB/home/dw\]$db2 connect to edp user dw using dw  
4.     查看数据库端口号（数据库管理配置）  
\[localhost/db-ods/dw\]$db2 get dbm cfg|grep -i svcename  
5.     查看数据库配置  
\[localhost/db-ods/dw\]$db2 get db cfg for edp  
6.     停止数据库  
db2stop force（停止进程关闭实例）  
7.     修改数据库参数  
Db2 update dbm cfg/db2 update db cfg  
8.     获取快照信息(snapshot)  
\[localhost/db-ods/dw\]$db2 get snapshot for application agentid 2805  
\[localhost/db-ods/dw\]$db2 get snapshot for locks on edp  
9.     DB2LOOK命令  
\[localhost/db-ods/dw\]$db2look -d edp -z dw -t zj\_test –e  
该命令可以获取表的定义信息，还可以导出表（可以多个表）结构到一个文件中：  
\[localhost/db-ods/dw\]$db2look -d edp -z dw -t zj\_test dim\_time -e  -o ./zj\_crt\_tb.sql  
10.     DB2EXPORT命令  
\[localhost/db-ods/dw\]$     
导出表数据，格式可以定制，del：带分隔符，数据可见  
10.    db2load

>      db2 "load client from ./zj.del of del MODIFIED BY COLDEL0X1D NOCHARDEL NOROWWARNINGS insert into dw.zj\_test nonrecoverable CPU\_PARALLELISM 8"  
>

11.     查看sqlcode  
\[localhost/db-ods/dw\]$db2 ? sql30081n  
12.     压缩表空间  
db2 alter tablespace <tabsp ace\_name> reduce  --收缩表空间,压缩表空间  
13.     列出指定用户下的表  
\[localhost/db-ods/dw\]$db2 "list tables for schema dw"  
14.     命令行下编译存储过程：  
db2 –td@ -vf xxx.sql ------ -td指定结束符 –v显示 –f指定文件  
15. 创建数据库：db2  "CREATE DATABASE EDP AUTOMATIC STORAGE YES  ON '/home/dw' DBPATH ON '/home/dw' USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM PAGESIZE 32768 DFT\_EXTENT\_SZ 6"  
16. 创建表空间：  
db2 "CREATE LARGE TABLESPACE "TBS\_SDM\_DATA" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP  
         PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE  
         AUTORESIZE YES  
         INITIALSIZE 30 M  
         MAXSIZE NONE  
         EXTENTSIZE 64  
         PREFETCHSIZE AUTOMATIC  
         BUFFERPOOL TBSSDMBUF32K  
         OVERHEAD 7.500000  
         TRANSFERRATE 0.060000  
         NO FILE SYSTEM CACHING  
         DROPPED TABLE RECOVERY ON";  
17. db2 查看表空间容器：

List tablespace containers for TBANUM show detail

18. （1）查看存储过程

db2 "SELECT COUNT(1) FROM SYSCAT.PROCEDURES WHERE PROCSCHEMA='DW' OR PROCSCHEMA='HYDG'"

（2）查看函数

db2 "SELECT \* FROM SYSCAT.FUNCTIONS WHERE FUNCSCHEMA='DW'"

19. 导出存储过程的方法除了db2look之外还可以使用如下方法：

db2 "EXPORT TO procudure.del OF del MODIFIED BY LOBSINFILE SELECT 'SET CURRENT SCHEMA '||rtrim(procschema)||'@'||chr(10)||'SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,'||rtrim(procschema)||' @'||chr(10)||text||chr(10)||'@'||chr(10) FROM syscat.procedures where PROCNAME='SP\_S\_GL\_BAL\_D'";

20.  编目

db2 catalog tcpip node EDP remote 66.235.97.200 server 50002

db2 catalog db EDP as EDP at node EDP

21. identity字段id int generated always as identity (start with 100 increment by 1)  
和sequence都可以

锁

1.     db2查看死锁  
（1）     db2 get snapshot for locks on EDP>zj\_lock.txt  
（2）     select \* from SYSIBMADM.LOCKS\_HELD  
（3）     db2 list applications show detail | grep -i lock  
2.     解除锁定  
db2 "force application (10131,10132)"  
3.     手动给表上锁  
\[localhost/db-ods/dw\]$db2 +c lock table zj\_test in exclusive mode

清空表 (慎用)

1.     DELETE FROM <TABLENAME>--适合数据量小的表  
2.     TRUNCATE TABLE  EBD.AFA\_BATINFO   IMMEDIATE;  
Db2 v9支持该语句，同时会压缩空间，但是是异步操作。  
3.     db2 alter table S\_CRD\_DBT\_TRS\_BRN\_D ACTIVATE NOT LOGGED INITIALLY with empty table  
4.     \[localhost/db-ods/dw\]$IMPORT FROM /dev/null OF DEL REPLACE INTO zj\_test  
IMPORT可以创建表、索引、支持触发器记录日志，入库方式支持insert、insert\_update、replace、replace\_create(不需要表存在、ixf格式)、create（导入新表、ixf格式）  
5.     \[localhost/db-ods/dw\]$db2 load from /dev/null of del replace into zj\_test nonrecoverable  
LOAD不记录日志，速度较快，支持（insert、replace）但是建议用import  
6.     DROP/CREATE TABLE  
7.     SELECT CARD,TABNAME FROM SYSCAT.TABLES  
WHERE TABSCHEMA='EBD'  
清空某个SCHEMA下的表之后可以用该语句查看是否成功，注意压缩的延迟动作。

修改表

1.     修改表名  
RENAME TABLE <TBNAME> TO <NEW-TBNAME>  
2.     修改字段类型  
ALTER TABLE ZJ\_TEST ALTER NAME SET DATA TYPE VARCHAR(2)  
注意：修改后的数据类型和长度要对照表中已有数据，否则报错  
3.     修改字段名  
ALTER TABLE ZJ\_TEST RENAME COLUMN ID TO NAME  
4.     增加字段  
alter table zj\_test add column id varchar(10);  
5.     更新字段值  
update zj\_test set (name,id)=('jk','123') where name='a'  
6.     增加约束  
ALTER TABLE "CUSTOMER" ADD CONSTRAINT CUST\_ID\_PK PRIMARY KEY (CUST\_ID)--创建约束

有用的SQL

1.     删除重复的行（数据量较小）  
delete from (select \* from(select a,b,c,row\_number() over(partition by a,b,c order by a,b,c) as row\_num from tab) as e where row\_num >1)  
如果数据量太大：如果数据量太大可以采用如下方法：  
Create table emp\_profile\_temp like emp\_profile;

大数据量采用  
LOAD FROM CURSUR  
DECLARE mycursor CURSOR FOR SELECT distinct \* FROM emp\_profile;   
LOAD FROM mycursor OF CURSOR INSERT INTO emp\_profile\_temp;       
drop table emp\_profile;   
          rename table emp\_profile\_temp to emp\_profile

如果记录数很少直接用rid()来区分，并删除  
2. DB2 MERGE的用法  
     db2 "merge into zj\_test as a   
     using (select name,id from zj\_test1) as b  
     on a.id=b.id  
     when matched then update set name=b.name  
     when not matched then insert (name,id) values (b.name,b.id)"  
3.db2后台导出存储过程的办法  
      db2 "EXPORT TO PROCUDURE.DEL OF DEL MODIFIED BY LOBSINFILE SELECT 'SET CURRENT SCHEMA '||RTRIM(PROCSCHEMA)||'@'||CHR(10)||'SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,'||RTRIM(PROCSCHEMA)||' @'||CHR(10)||TEXT||CHR(10)||'@'||CHR(10) FROM SYSCAT.PROCEDURES WHERE PROCNAME='SP\_S\_GL\_BAL\_D';"  
     注意： SYSCAT.PROCEDURES的TEXT为lob字段类型  
4.自定义异常

CREATE  OR  REPLACE  PROCEDURE  ZJ\_TEST()

LANGUAGE SQL

BEGIN

IF  1 <  2  THEN

SIGNAL SQLSTATE  '80000'  SET  MESSAGE\_TEXT  =  '总分核对不通过！' ;

END IF ;

END

分区

1.     删除分区  
ALTER TABLE ZJ\_TEST DETACH PARTITION UPPER('P20130731') INTO ZJ\_TEST\_TMP\_20130926;--异步操作，需等待  
2.     Commit;--commit很重要  
3.     删除临时表  
DROP TABLE ZJ\_TEST\_TMP\_20130926;  
4.     重建分区  
ALTER TABLE ZJ\_TEST ADD PARTITION 'P20130731'  STARTING '20130731' INCLUSIVE ENDING  '20130801' EXCLUSIVE;--包括‘20130731’，不包括‘20130801’  
5.     迁出分区之后必须要有个等待操作（可以参考db2官方文档“waitingfordetach”）