db2&unix使用总结（五）

1、关于数据移动程序中的文件类型修饰符：

 loadfrom empdata.del of del modified by anyorder insert into newemp cpu_parallelism2 //在多处理系统上能带来更好的性能。

 

import from empdata.ixf of ixf modified bycompound=100 insert into emptemp

//每次插入100条记录

 

load from staffdata.del of del modified bygeneratedignore insert into newstaff

//表中有自动生成的字段时用此修饰符。

 

load fromstaffdata.del of del modified by generatedmissing insert into newstaff

//数据文件不包含自动生成列时用此修饰符

2、 一台电脑一个以太网卡，一个无线局域网卡，既要上内网，又要上外网，要添加路由：

route -p add 130.36.0.0 mask 255.255.0.0 132.151.9.1 metric1，然后在无线局域网卡设置跳跃点数1。

3、导出存储过程和函数：

db2"EXPORT TO cuirong_bo_cx.pro1 OF del MODIFIED BY LOBSINFILE SELECT 'SETCURRENT SCHEMA '||rtrim(procschema)||'@'||chr(10)||'SET CURRENT PATH =SYSIBM,SYSFUN,SYSPROC,'||rtrim(procschema)||'@'||chr(10)||text||chr(10)||'@'||chr(10) FROM syscat.procedures  ORDER BY create_time "

db2 "EXPORT TO cuirong_bo_cx.func OF del MODIFIED BYLOBSINFILE SELECT 'SET CURRENT SCHEMA '||rtrim(funcschema)||'@'||chr(10)||'SETCURRENT PATH = SYSIBM,SYSFUN,SYSPROC,'||rtrim(funcschema)||'@'||chr(10)||body||chr(10)||'@'||chr(10) FROM syscat.functions  ORDER BY create_time "

4、分节点表空间查看数据库存储：

selectsubstr(varchar(t.SNAPSHOT_TIMESTAMP),1,16) as time

       ,substr(t.TBSP_NAME,1,20)

       , t.TBSP_ID

       , t.TBSP_STATE

       , t.DBPARTITIONNUM

       ,1.0 * t.TBSP_TOTAL_PAGES *r.TBSP_PAGE_SIZE /1024/1024/1024 as TOTAL_GB_SIZE

       ,1.0 * t.TBSP_USED_PAGES *r.TBSP_PAGE_SIZE /1024/1024/1024 as USED_GB_SIZE

       ,1.0 * t.TBSP_FREE_PAGES *r.TBSP_PAGE_SIZE /1024/1024/1024 as FREE_GB_SIZE

       ,decimal(1.0 *t.TBSP_FREE_PAGES/t.TBSP_TOTAL_PAGES*100,10,2) as FREE_PERCENT

fromtable(SNAP_GET_TBSP_PART_V91('ZJBI2',-2)) as t,table(SNAP_GET_TBSP_V91('ZJBI2',-2)) as r

wheret.TBSP_ID=r.TBSP_ID and r.TBSP_TYPE='DMS' and r.TBSP_CONTENT_TYPE='LARGE'

order by free_percent asc

5、在审计日志表里查看历史sql：

selecttimestamp,USERID,APPID,replace(varchar(STMTTEXT,8000),CHR(10),'') from sysaudit.execute  where timestamp>=timestamp('2010-12-2100:00:00.0') and appname<>'tclsh' and event='STATEMENT' and authid='BI_BO'

and replace(varchar(STMTTEXT,8000),CHR(10),'') like'%DWD_USER%' order by timestamp