用户、数据库名、Schema关系

一个用户对应多个数据库

一个数据库对应多个schema

数据库名不是schema

用户是用户，与数据库名和schema没有关系

schema和表名的格式：“schema”.“表名”

DB2分页操作

获取前100条数据

SELECT
        RESULT_CODE "result_code",
        TASK_CODE "task_code",
        VER_CODE "ver_code",
        DATA "data"
FROM
        "BXM"."BXM_QUALITY_ERROR_DATA"
where RESULT_CODE='1381785140308611072' and VER_CODE='GopeU9sy'  fetch first 100 rows only

获取100条到200条数据

select
  T.sicCd form Table T 
where
  T.sicCd not in ( 
    select
      sicCd 

   form 

      Table 

        fetch first 100 rows only
  ) fetch first 100 rows only

ROW_NUMBER()分页

db2和Oracle实现分页的方式类似，所需要的参数为要查询的数据开始的条数start，结尾的条数end，并且在sql语句中还需要设定row_number()（Oracle中为rownum）作为查询的辅助函数，此时要使用select语句的嵌套，嵌套在from子句中进行。
子句为：

select row_number() over ( order by date desc ) as r,e.* from emp e

其中
row_number() 作为人为的添加一列作为给每一条数据进行编号
over()中是实现排序的字段和方式，date是字段名，desc是方式，都可以修改，但是over()为必须写的，不写会报错
as r是为row_number()这个列取的一个别名
真正要分页查询的数据在外面的父句中进行实现
完整的语句为

select * from (
select row_number() over(ORDER BY date DESC) as r,e.*
from emp e
where e.name=’A’)
where r between 1 AND 5

此时的start为1，end为5，要注意between…and是>=1并且<=5实际上查出的是6条数据，所以要注意limit和end之间的处理，可以在action中进行，否则可能会出现每页显示条数不正确或者每一页的最后一条数据和下一页第一条数据重复等问题

还有一种方式是用>=和<=直接实现的
语句为

select * from (
select row_number() over(ORDER BY date DESC) as r,e.*
from emp e
where e.name=’A’ AND r<=5)
where r>0

此时显示的是1-5条数据，因为数据库是从1开始的。

1. db2分页查询sql

SELECT
	* 
FROM
	(  SELECT ROW_NUMBER() OVER ( ORDER BY DOC_UUID DESC ) AS ROWNUM, DOC_UUID, DOC_DISPATCHORG, DOC_SIGNER, DOC_TITLE FROM DT_DOCUMENT ) a
WHERE
	ROWNUM > 20 
	AND ROWNUM <= 30 

2.ROW_NUMBER()函数

增加行号，不排序

SELECT
	* 
FROM
	( SELECT ROW_NUMBER() OVER () AS ROWNUM, t.* FROM DT_DOCUMENT t ) a

增加行号，按某列排序

SELECT
	* 
FROM
	( SELECT ROW_NUMBER() OVER ( ORDER BY DOC_UUID DESC ) AS ROWNUM, t.* FROM DT_DOCUMENT t ) a

DB2的NULL与空字符串是两个概念

一直以为空字符串是就是NULL，当插入空字符串的时候就可以通过NULL，但是前段时间开发发现使用not in特定值后发现，空字符集居然也没有包括在内，当时我也认为，应该包括在内，后来做了个如下测试发现其实在DB2中NULL和空串真不是一回事。

create table tab
( id    integer not null,
 name  char(50),
 empid  char(10),
 salary  char(15)
);
insert into tab values(1,'JACK','0001','2000');
insert into tab values(2,'TOM','0002','');
insert into tab values(3,'LUCY','0003','3000');
insert into tab values(4,'JAME','0004','');
insert into tab values(4,'KIM','0005',null);

对此DB2相关文档给出的答案是,NULL是一个不确定值，可以代表任何值，所以不包括在任何一个指定集合中，并且NULL的值不能通过列值过滤，只能通过IS NULL 或者IS NOT NULL方式过滤，所以上面你可以看到这样的结果，这个貌似和Oracle的不同，所以不同的数据库还是不一样的

常见SQL语句总结

判断数据库下某个schema是否存在

SELECT count(1) AS "result" FROM SYSCAT.SCHEMATA WHERE SCHEMANAME='BXM' 

建表语句并给表加注释样例

CREATE TABLE "BXM"."BXM_QUALITY_ERROR_DATA" (
	"RESULT_CODE" VARCHAR(200) NOT null,
	"TASK_CODE" VARCHAR(200) NOT null,
	"VER_CODE" VARCHAR(200)  NOT null,
	"DATA" VARCHAR(4000)  NOT null
);
COMMENT ON TABLE BXM.BXM_QUALITY_ERROR_DATA IS '异常数据表';

判断数据库的schema某表是否存在

SELECT
	CASE
		WHEN TABNAME IS NULL THEN 0
		ELSE 1
	END AS "result"
FROM
	SYSCAT.TABLES
WHERE
	TABSCHEMA = 'DB2INST1'
	AND TABNAME = 'ACT'

函数

类型转换函数

CAST用来转换类型使用

SELECT CAST(CURRENT TIME AS CHAR(8) ) FROM SYSIBM.SYSDUMMY1

转换为字符串类型

CHAR函数

语法：CHAR(ARG)

CHAR函数返回日期时间型、字符串、整数、十进制或双精度浮点数的字符串表示。

SELECT CHAR(SALARY) FROM TEST

CHR函数

语法：CHR(ARG)

CHR函数返回由参数指定的ASCII码的字符，参数可以是INTEGER或SMALLINT。

SELECT CHR(167) FROM TEST

VARCHAR函数

语法：VARCHAR(ARG,LENGTH)

VARCHAR函数返回字符串、日期型、图形串的可变长度的字符串表示。

SELECT VARCHAR(NAME,50) FROM TEST
--50为截取长度，如果name字符串的长度大于50，则返回“SQL0445W 值已被截断。SQLSTATE=01004”。

DIGITS函数

DIGITS函数返回SMALLINT、INTEGER、BIGIT或者DECIMAL参数的字符串值。

SELECT DIGITS(ID) FROM TEST

转换为数字类型

DOUBLE、FLOAT函数

语法:DOUBLE()、FLOAT()

DOUBLE、FLOAT函数如果参数是一个数字表达式，返回与其对应的浮点数，否则返回错误代码。

SELECT DOUBLE('4569') FROM TEST

INT函数

语法:INT()

INT函数返回整型常量中的数字、字符串或者日期、时间的整数表示。

SELECT INT('111') FROM TEST

BIGINT函数

语法:BIGINT()

BIGINT函数返回整型常量中的数字、字符串或者时间戳的64位长整数表示。

SELECT BIGINT('111') FROM TEST

SMALLINT函数

语法:SMALLINT()

SMALLINT函数返回整型常量中的数字、字符串短整数表示。

SELECT SMALLINT('111') FROM TEST

REAL函数

语法:REAL()

REAL函数返回一个数值的单精度浮点数表示。

SELECT REAL(10) FROM TEST

DEC[IMAL]函数

语法:DEC[IMAL]

DEC[IMAL]函数返回一个数值、DECIMAL的字符串、INTEGER的字符串、FLOAT-POINT的字符串、日期、时间或时间戳的DECIMAL数值。

SELECT DEC(10) FROM TEST

HEX函数

语法:HEX()

HEX函数返回一个字符串的值的16进制表示。

SELECT HEX(10) FROM TEST

FLOOR函数

语法:FLOOR()

SELECT FLOOR(10.50) FROM TEST  

正则

DB2 for z/OS需要用PASSING AS语句

SELECT * from table  WHERE 
xmlcast(xmlquery('fn:matches($column, ''[^0-9]+$'')' PASSING column AS column) AS integer)  = 1


SELECT * from table  WHERE 
xmlcast(xmlquery('fn:matches($column, "[^0-9]+$")' PASSING column AS column) AS integer)  = 1

DB2 for LUW则不需要

SELECT * from table  WHERE 
xmlcast(xmlquery('fn:matches($column, ''[^0-9]+$'')') AS integer)  = 1

SELECT * from table  WHERE 
xmlcast(xmlquery('fn:matches($column, "[^0-9]+$")') AS integer)  = 1

注：DB2不支持除了字符串以外的类型的字段进行正则，如果是字符串以外的类型字段进行正则，必须先进行类型转换

SELECT
	"ACTNO",
	"ACTDESC",
	"ACTKWD",
	CASE
		WHEN xmlcast(xmlquery('fn:matches($ACTNO,"(^([1-6][1-9]|50)[0-9]{4}(18|19|20)[0-9]{2}((0[1-9])|10|11|12)(([0-2][1-9])|10|20|30|31)[0-9]{3}[0-9Xx]$)|(^([1-6][1-9]|50)[0-9]{4}[0-9]{2}((0[1-9])|10|11|12)(([0-2][1-9])|10|20|30|31)[0-9]{3}$)")')AS integer) = 0 THEN '0'
		ELSE '1'
	END AS RULE_0
FROM
	(
	SELECT
		replace(VARCHAR("ACTKWD"), '"', '\"') AS "ACTKWD",
		replace(VARCHAR("ACTDESC"), '"', '\"') AS "ACTDESC",
		replace(VARCHAR("ACTNO"), '"', '\"') AS "ACTNO"
	FROM
		"DB2INST1"."ACT" "ACT") ) tmp

COALESCE函数（类似于IFNULL函数）

COALESCE函数返回它的第一个非null参数的值,检索数据时用缺省值替换null值
SELECT
	COALESCE(VARCHAR("ACTKWD"),'') AS "ACTKWD"
FROM
	"DB2INST1"."ACT" "ACT"

日期相关函数

字符串、日期类型之间的转换

SQLselect date(trim(char('2009-09-01 '))),SQL>time(trim(char('12:23:34 '))),
timestamp(trim(char('2009-02-26-14.28.40.234000'))) from sysibm.dual;
---------- -------- --------------------------
2009-09-01 12:23:34 2009-02-26-14.28.40.234000
  1 条记录已选择。

SQL>select char(current date),char(current time),char(current timestamp) from sysibm.dual;
---------- -------- --------------------------
2009-02-26 14:28:40 2009-02-26-14.28.40.234000
  1 条记录已选择。

SQL>select to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss') from sysibm.dual;--DB2 9版本中新增
--------------------------
2009-02-26 14:45:53
1 条记录已选择。

SQL>select to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss') from sysibm.dual;--DB2 9版本中新增
--------------------------
2009-01-01-12.23.45.000000
1 条记录已选择。

获取当前日期

SELECT current date FROM sysibm.sysdummy1;
SELECT current time FROM sysibm.sysdummy1;
SELECT current timestamp FROM sysibm.sysdummy1;

日期截取

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
DATE (current timestamp)
TIME (current timestamp)

异常总结

DB2数据库除了字符串以外的类型的字段，如果进行空串比较（！=‘’），会报错，必须将非字符串类型转化为字符串类型才可以比较

java接收DB2类型的blob类型时，用varchar函数转换接收，处理无法接收的bug

字符串连接(DB2拼接NULL直接报错)

DB2字符串连接和oracle数据库相同，使用“||”进行DB2字符串连接，其使用方式和MSSQLServer中的加号“+”一样。比如执行下面的SQL语句：

SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee
WHERE FName IS NOT NULL

除了“||”，DB2还支持使用CONCAT()函数进行字符串拼接，比如执行下面的SQL语句：

SELECT CONCAT('工号:',FNumber) FROM T_Employee

与Oracle不同，如果CONCAT中连接的值不是字符串，则DB2不会尝试进行类型转换而是报出错误信息，比如执行下面的SQL语句是错误的：

SELECT CONCAT('年龄:',FAge) FROM T_Employee

运行以后DB2会报出下面的错误信息：
未找到类型为"FUNCTION" 命名为 “CONCAT” 且具有兼容自变量的已授权例

与MYSQL的CONCAT()函数不同，DB2的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接，比如下面的SQL语句在Oracle中是错误的：

SELECT CONCAT('工号为',FNumber,'的员工姓名为',FName) FROM T_Employee
WHERE FName IS NOT NULL

运行以后Oracle会报出下面的错误信息：
未找到类型为"FUNCTION" 命名为 “CONCAT” 且具有兼容自变量的已授权例程

如果要进行多个字符串的拼接的话，可以使用多个CONCAT()函数嵌套使用，上面的SQL可以如下改写：

SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓名为'),FName) FROM
T_Employee
WHERE FName IS NOT NULL