DB2

create database --创建一个新的数据库
drop database --删除一个数据库
connect to <database_name> --连接数据库
create table/create view/create index --分别创建表，视图，和索引

CREATE TABLE DEMO(
ID INT,
NAME VARCHAR(50),
AGE VARCHAR(50),
SEX VARCHAR(10)
);

DROP TABLE DEMO;

INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(1,'JACK','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(2,'TALIE','30','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(3,'CRRIES','18','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(4,'MODEM','20','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(5,'3DM','30','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(6,'JERRY','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(1,'JACK','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(2,'TALIE','30','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(3,'CRRIES','18','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(4,'MODEM','20','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(5,'3DM','30','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(6,'JERRY','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(1,'JACK','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(2,'TALIE','30','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(3,'CRRIES','18','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(4,'MODEM','20','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(5,'3DM','30','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(6,'JERRY','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(1,'JACK','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(2,'TALIE','30','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(3,'CRRIES','18','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(4,'MODEM','20','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(5,'3DM','30','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(6,'JERRY','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(1,'JACK','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(2,'TALIE','30','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(3,'CRRIES','18','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(4,'MODEM','20','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(5,'3DM','30','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(6,'JERRY','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(1,'JACK','18','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(2,'TALIE','30','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(3,'CRRIES','18','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(4,'MODEM','20','男');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(5,'3DM','30','女');
INSERT INTO DEMO(ID,NAME,AGE,SEX) VALUES(666,'JERRY','18','男');
select * from demo ORDER BY AGE DESC;
SELECT SEX,COUNT(*) FROM DEMO GROUP BY SEX;
SELECT * FROM DEMO WHERE ID=666;
DELETE FROM DEMO WHERE ID =666;
--分页
select * from ( select row_number() over() as rownum,t.* from demo t) where rownum >10 and rownum <=20;

--高效清空表
ALTER TABLE DEMO ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

 

--DB2备份命令：BACKUP DATABASE <dbname> [ TO <path> ]
BACKUP DB GTG TO D:\GTG;

 

--使用DB2修改服务器操作系统上的密码
db2 connect to sample user test1 using password new chgpass confirm chgpass

启动数据库： 启动db2服务：db2start 激活数据库实例：db2 activate database <db_name>
查看激活状态的数据库：db2 list active databases

关闭数据库： 失效数据库实例：db2 deactivate database <db_name> 关闭数据库服务：db2stop

查看数据库：db2 list db directory

查看数据库应用：db2 list applications 查看数据库应用和进程号：db2 list applications show detail 查看数据库表空间：db2pd -db <db_name> -tablespace

查看数据库配置：db2 get db cfg for <db_name> 连接数据库：db2 connect to <db_name> db2 connect to <db_name> user[user_name] using [password] 断开数据库连接：db2 connect reset/db2 terminate 创建数据库：db2 create db <db_name> 删除数据库：db2 drop database <db_name> （如果不能删除，尝试断开激活的连接或者重启db2）

列出系统表：db2 list tables for system 列出所有用户表：db2 list tables 列出所有表：db2 list tables for all 列出特定用户表：db2 list tables for schema [user]

复制一张表：db2 create table t1 like t2 显示表结构：db2 describe table tablename 查询表：db2 "select * from table tablename where ..." 执行SQL脚本：db2 -tvf scripts.sql

查看错误代码信息：db2 ? 10054 停止激活的连接：db2 force application all;\db2 force application all;\db2 force application all;\db2stop 查看死锁： db2 get snapshot for locks on <db_name> db2 "select agent_id,tabname,lock_mode from table(snap_get_lock('<db_name>')) as aa" 杀掉进程： db2 force application(NUM) 监控DB2消耗多的SQL语句： eg:（DB_NAME=CMSDB） db2top -d CMSDB -----查看消耗资源 按照提示按 l，出现Application Handle，找到资源消耗大的Application Handle（stat） 记下app handle。 db2pd -d CMSDB -dyn -application > /tmp/db2pd1.txt ----到处会话语句，准备进行调优

设置连接方式（重启才会生效）： db2set DB2COMM=tcpip db2set DB2COMM=

 

 

--注释
select t.Remarks as 注释,t.* from syscat.COLUMNS t
where tabschema='TEPS' and tabname=upper('KFWTSIT');