DB2小记和NSE命令

ps：DB2和NSE都是9.5版本

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

[color=red]表定义：[/color]
db2look -d appdev -z administrator -t BYS_RK_YWGL -e -o C:

[color=red]输出sql返回结果：[/color]
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')

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

[color=red]数据库导出 导入:[/color]db2 backup database date_name to c:\
db2 restore database date_name from . ON E: INTO name

[color=red]取消DB2 产生日志信息[/color]alter table tableName activate not logged initially;

[color=red]去重复[/color]
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 );


[color=red]
分区内并行[/color]
UPDATE DBM CFG USING INTRA_PARALLEL YES;

[color=red]主键自增：[/color]
ID int not null generated always as identity (start with 1, increment by 1) primary key,

[color=blue]下面是NSE的命令：[/color]启动：db2text start
结束: db2text dtop force
[color=red]
启动对数据库[/color] ; db2text enable database for text connect to appdev1 user db2admin using db2admin

[color=red]查询index [/color]: db2 select indName,tabName,INDEXDIRECTORY from db2ext.textindexes

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

[color=red]创建index[/color] : db2text create index db2ext.xml for text on db2admin.DT_RENYUAN(content) format xml connect to appdev1 user db2admin using db2admin

[color=red]更新index [/color]: db2text update index db2ext.xml for text connect to appdev1 user db2admin using db2admin

[color=red]删除index[/color] : db2text drop index db2ext.xml for text connect to appdev2

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