DB2小记和NSE命令

hncys

ps：DB2和NSE都是9.5版本

搜集表的统计信息，如多少行、多少字段、字段值的分布等
runstats on table table_name with distribution and detailed indexes all

表定义：
db2look -d appdev -z administrator -t BYS_RK_YWGL -e -o C:

输出sql返回结果：
db2 export to C:/123.txt  of del SQL

因为oracle的varchar2和db2的varchar在做比较时候，含义不一样
oracle认为 'aaa '和'aaa'不相等，而db2认为是相等
所以II在执行前加了rtrim的处理，导致索引不能用上
执行下面的语句可加快速度：
ALTER NICKNAME  table_name ALTER COLUMN YWID OPTIONS (ADD VARCHAR_NO_TRAILING_BLANKS 'Y')

输出执行计划：c:\db2 connect to dbname
c:\db2 set current explain mode explain
c:\db2 -tvf your.sql
c:\db2exfmt -d dbname 回车，知道出现output file

数据库导出 导入:db2 backup database date_name to c:\
db2 restore database date_name from . ON E: INTO  name

取消DB2 产生日志信息alter table  tableName activate not logged initially;

去重复
select distinct name, email from (
  select name, email, rank() over (partition by name order by email ) as rnk from test
) a

delete from ( select * from( select name, email, rownumber() over (partition by name,email order by name, email ) as rn from test ) where rn<>1 );



分区内并行
UPDATE DBM CFG USING INTRA_PARALLEL YES;

主键自增：
ID int not null generated always as identity (start with 1, increment by 1) primary key,

下面是NSE的命令：启动：db2text start
结束: db2text dtop force

启动对数据库 ; db2text enable database for text connect to appdev1 user db2admin using db2admin

查询index :  db2 select indName,tabName,INDEXDIRECTORY from db2ext.textindexes

设置index存放路径: db2 update DB2EXT.DBDEFAULTS set defaultvalue='D:\Program Files\IBM\SQLLIB\db2ext\indexes' where defaultname='INDEXDIRECTORY'

创建index : db2text create index db2ext.xml for text on db2admin.DT_RENYUAN(content) format xml connect to appdev1 user db2admin using db2admin

更新index : db2text update index db2ext.xml for text connect to appdev1 user db2admin using db2admin

删除index : db2text drop index db2ext.xml for text connect to appdev2

查询数据 :  db2 select * from db2admin.DT_RENYUAN where contains(content,'sections(\"/人员信息/名称属性/姓名\") \"黄广飞\"')=1