DB2常用语句

1.执行sql文件

db2 -txf 文件绝对路径

2.执行存储过程文件(后台执行end后加@符)

db2 -td@ -f 存储过程文件绝对路径

3.执行语句

db2 " select a from x with ur " -- 引号中无分号

4.导出存储过程export

db2 -x " export to /a/b/pro_test.sql of del modified by lobsinfile select text || char(10) || '@' from syscat.procedures where procschema='NCRR' and procname ='pro_base' "

5.导出数据文件

db2 " export to /a/b/pro_test.dat of del modified by nochardel coldel0x03 datesiso codepage=1386 select * from aa with ur "

--1208是utf3 1386是gbk

6.导入数据文件

db2 "load from /a/b/test.dat of del modified by nochardel coldel0x03 usedefaults replace into aa.test"

6.1导入excel

db2 load from aaa.csv of del replace into ncrr.aaa

7.rebind存储过程

db2 "call sysproc.rebind_routine_package('p','ncrr.base','')"

8.runstats表

db2 "runstats on table rrsdw.aaa with distribution and detailed indexes all"

9.查看锁并杀进程

db2 get snapshot for locks on ncrrdb | more

db2 "force application(52042)"

10.表结构修改后reorg表

db2 "reorg table aaa"

11.修改字段名

alter table a alter column aaa set data type varchar(10)

tribution and detailed indexes all"

9.获取字符串中特殊字符个数

regexp_count('字段','@')+1 --字段中@符的个数

regexp_substr(字段,'[^|]+',1,数字变量) --获取|字符串中第几个字符