连接数据库:
db2 connect to <database_name> user <db2_userID> using <db2Password>
查看当前连接哪个库:
db2 get connection state
查看node:
db2 list node directory
查实例:
db2ilist
查看系统数据库:db2 list db directory
编目节点:
db2 catalog tcpip node node_name remote server_ip server server_port
编目数据库
db2 catalog db db_name as db_alias at node node_name
查:
select count(*) from TBL
select * from TBL
describe table TBL
select * from TBL where ...
插:
insert into TBL (activity_id) values ("111")
删:
delete from TBL where .....
更新:
update TBL set 列 = 值, where 列 = 值
修改列名称:
alter table "table_name" rename column "column1" to "column2"
复制表结构:
create table table_name_new as (select * from table_name_old) definition only
删除表:
drop table table_name
显示当前schema:
values current schema
修改当前schema:
set current schema = 'ac_onlde'
创建schema:
db2 "create schema test"
查看某schema下的所有表
db2 list tables for schema xxx
导入ixf表:
db2 import from table_name.ixf of ixf modified by forcecreate commitcount 1000 replace_create into table_name
导出ixf 可以写shell脚本
select * from tablename fetch first n rows only (取前n条数据)
select ACQ_INS_ID_CD,ACQ_INS_NM fromTBL_ACMGM_QRC_ACQ_INF order by ACQ_INS_ID_CD (排序)(desc 降序)
select * from ( selectACQ_INS_ID_CD,ACQ_INS_NM from TBL_ACMGM_QRC_ACQ_INF order by ACQ_INS_ID_CD ) asv fetch first 2 rows only (取排序的前n条数据)
select count(*) from TBL_ACMGM_QRC_ACQ_INFwhere ACQ_INS_ID_CD in ('01029801','01029802') (字段范围)
select AREA_CD,proc_st,order_type,trans_tp,resp_cdfrom TBL_QRONL_TRANS_FLOW0601_0607 where ACQ_INS_ID_CD = '03010000' fetch first10 rows only;
selectproc_st,order_type,trans_tp,resp_cd,qr_code from TBL_QRONL_TRANS_FLOW0614_0618where order_type = '0210' fetch first 2 rows only
groupby
select resp_cd, count(*) fromtbl_qronl_trans_flow01 group by resp_cd having count(*) > '100'
分区间统计
select (case when trans_at >= 1 then '100' when trans_at < 1then '200' else '00000' end) as Gender,count(*) from tbl_qronl_trans_flow03group by (case when trans_at >= 1 then '100' when trans_at < 1 then '200'else '00000' end)