数据库基本命令

来源：互联网发布：北电网络破产编辑：程序博客网时间：2024/06/05 09:07

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”）

0 0