DB2 系统函数操作示例_03

转
 
1、时间操作函数
db2 "select current date from sysibm.sysdummy1"
db2 "select current time from sysibm.sysdummy1"
db2 "select current timestamp from sysibm.sysdummy1"
db2 "select year('2010-06-22-12.30.00') from sysibm.sysdummy1"
db2 "select month('2010-06-22-12.30.00') from sysibm.sysdummy1"
db2 "select day('2010-06-22-12.30.00') from sysibm.sysdummy1"
db2 "select week('2010-06-22-12.30.00') from sysibm.sysdummy1"
db2 "select hour('2010-06-22-12.30.00') from sysibm.sysdummy1"
db2 "select minute('2010-06-22-12.30.00') from sysibm.sysdummy1"
db2 "select second('2010-06-22-12.30.55') from sysibm.sysdummy1"
db2 "select dayofyear('2010-06-22-12.30.55') from sysibm.sysdummy1"        -- 返回年内的天数
db2 "select dayofweek('2010-06-22-12.30.55') from sysibm.sysdummy1"        -- 返回周内的天数
db2 "select midnight_seconds('2010-06-22-12.30.55') from sysibm.sysdummy1" -- 返回午夜到参数之间的秒数
2、字符串操作函数
db2 "select length('IBM DB2') length from sysibm.sysdummy1"
db2 "select lcase('ABC def') from sysibm.sysdummy1"
db2 "select ucase('ABC def') from sysibm.sysdummy1"
db2 "select '[' || ltrim('  abc def  ') || ']' string from sysibm.sysdummy1"
db2 "select '[' || rtrim('  abc def  ') || ']' string from sysibm.sysdummy1"
db2 "select '[' || trim('  abc def  ') || ']' string from sysibm.sysdummy1"
db2 "select concat('abc', 'def') from sysibm.sysdummy1"
db2 "select insert('abcdef', 4, 3, 'ABCDEF') from sysibm.sysdummy1"
db2 "select left('abcdef', 3) from sysibm.sysdummy1"
db2 "select right('abcdef', 3) from sysibm.sysdummy1"
db2 "select locate('def', 'abcdef') from sysibm.sysdummy1"
db2 "select posstr('abcdef', 'def') from sysibm.sysdummy1"
db2 "select repeat('abc', 2) from sysibm.sysdummy1"
db2 "select replace('abcdefabcdef', 'def', 'DEF') from sysibm.sysdummy1"
db2 "select '[' || space(8) || ']' from sysibm.sysdummy1"
db2 "select substr('abcdef', 4) from sysibm.sysdummy1"
db2 "select substr('abcdef', 4, 1) from sysibm.sysdummy1"
3、数值操作函数
db2 "select abs(-100) from sysibm.sysdummy1"
db2 "select max(3, 5, 8) from sysibm.sysdummy1"
db2 "select min(3, 5, 8) from sysibm.sysdummy1"
db2 "select sum(field) from tablename"
db2 "select avg(field) from tablename"
db2 "select ceil(3.14259) from sysibm.sysdummy1"                  -- 返回大于或等于参数的最小整数
db2 "select floor(3.14259) from sysibm.sysdummy1"                 -- 返回小于或等于参数的最小整数
db2 "select mod(10, 3) from sysibm.sysdummy1"
db2 "select rand() from sysibm.sysdummy1"
db2 "select truncate(3.14259, 2) from sysibm.sysdummy1"
db2 "select nullif('abc', 'abc') from sysibm.sysdummy1"           -- 两参数相等返回空
db2 "select nullif('abc', 'abcd') from sysibm.sysdummy1"
4、其他
db2 "select hex(10) from sysibm.sysdummy1"                   -- 返回16进制表示
5、索引手工分析
db2 "describe table sysibm.sysindexes"
db2 "select distinct tbname from sysibm.sysindexes"
db2 "select tbname from sysibm.sysindexes where tbname = 'tbl_gasvc_para_sync_task'"     -- 错误，表名大写
db2 "select tbname from sysibm.sysindexes where tbname = 'TBL_GASVC_PARA_SYNC_TASK'"
db2 "select name, colnames from sysibm.sysindexes where tbname = upper