db2 操作常用命令

---清空cls

----查看数据版本

select * from sysibm.sysversions

 

----创建数据库实例

create db tsissdb using codeset gbk territory cn pagesize 8 K catalog tablespace managed by database using (device '/dev/rlv_sdb_clg' 1048576);

--备份路径可以修改

db2 backup db tsissdb to /tmp/initdata/ compress

---查看用户，表，及其主键

select tabschema,tabname,colname  from syscat.colidentattributes order by tabschema,tabname

---重启数据

Restart db  twcs177

---激活数据库

Activate db twcs177

---停止数据库

Deactivate db twcs177

---数据库链接名

db2 catalog tcpip mode mynode remote 10.2.2.177 server 62000

---链接数据库

connect to twcs177  user tsisusr  using  tsisusr

 

--附权限load用  要切到实例

db2 grant load on database to user tsisusr

---导入csv数据

import from D:\DB2\tg_debtcash_detail.csv of del insert into tg_debtcash_detail

import from D:\TG_DEBT_TRADE.del of del insert into TG_DEBT_TRADE;

import from D:\htr_mon_total_divide_normal.del of del commitcount 10000 insert_update into tabname

---导入del数据

export to D:\TD_ENUMVALUE.del  of del select * from TD_ENUMVALUE where  S_TYpecode ='TW0086'

 

---查询表10条数据

select * from tg_debtissue_detail where 1=1 fetch first 10 rows only with ur

--查看表的外键

SELECT * FROM SYSCAT.REFERENCES WHERE TABNAME='TG_DEBTISSUE_DETAIL'

----查看表空间

List tablespaces show  detail;

----查看表空间使用情况

select * from sysibm.systablespaces

---查看表所属表空间

select TABNAME,tbspace from syscat.tables where TABNAME='TA_LOCALCASH_INFO';

---查看表空间的表

select TABNAME from syscat.tables where tBSPACE='USERSPACE1';

select * from sysibm.systablespaces

--查看表分区

SELECT * FROM SYSCAT.datapartitions WHERE TABNAME='TG_DEBTISSUE_DETAIL'

--添加表分区

alter table TG_DEBTISSUE_DETAIL add partition statis_month STARTINT'201301' INCLUSIVE ENDING '20090102' INCLUSIVE

---创建表时指定表空间

create table_xxx(col1.....) in  USERSPACE1

---查看表索引

describe indexes for table TA_TRL_GO_COMEBILL show detail

---查看表字段状况

select * from sysibm.syscolumns where TBNAME='TG_CA_OPERPLAN';

---查看表结构

describe table  TA_LOCALCASH_INFO

---查看表分区

db2 describe data partitions  for table TA_LOCALCASH_INFO

db2 describe data partitions  for table TA_LOCALCASH_INFO show detail

---查看表活动分区名称

select seqno PART0  from syscat.datapartitions where tabname='TA_LOCALCASH_INFO' order by seqno;

---清除一个大表、

alter TA_LOCALCASH_INFO activate not logged initally with empty table  or

import from null_file of del into TA_LOCALCASH_INFO

---重命名数据表

rename old_table_name to new_table_name

---查看BUFFER情况

select * from sysibm.sysbufferpools

---修改缓冲池大小

alter bufferpool ibmdefaultbp size 10240

--- 如果s_prepfieldtwo字段值为空则取字段 s_prepfieldone的值

select  value(s_prepfieldtwo,s_prepfieldone) s_prepfieldtwo  from  TA_FUND_OTHER where s_orgcode='230000000002'

select  coalesce (s_prepfieldtwo,s_prepfieldone) s_prepfieldtwo  from  TA_FUND_OTHER where s_orgcode='230000000002'