1、修改表结构或重新建表后,对表进行任何操作都不被允许,因为表不活动,需要对表进行reorg操作 来恢复。
语句:reorg table XXX;
2、1个中文字符在表结构中占3个varchar字符
1个字符占一个varchar字符
1个数据占一个varchar字符
3、DECIMAL(m,n):整数的位数不能大于(m-n),但小数位可以大于n,但会从大于n的有效位开始截断。
4、CAST:从一种数据类型转换为另一种数据类型。还有一种用途是截断很长的字符串。
例如:SELECT EMPNO ,CAST(RESUME AS VARCHAR(370)) FROM EMP_RESUME
上面语句使得RESUME字段从CLOB类型变为VARCHAR类型,并且只截取前370个字符。
5、CASE:条件表达式
例如:SELECT DEPTNAME,
CASE DEPTNUMB
WHEN 10 THEN 'MARKETING'
WHEN 15 THEN 'RESEARCH'
ELSE 'SALES'
END AS FUNCTION
FROM ORG
6、db2的distinct和order by冲突
语句SELECT DISTINCT typeNameCode1 ,typeName1 FROM db2admin.shujuziyuanmulu where type='外部机构信息' ORDER BY ORDER_CODE 执行报错:sqlcode:-214 sqlstate:42822
修改语句如下:SELECT DISTINCT typeNameCode1 ,typeName1 , ORDER_CODE FROM db2admin.shujuziyuanmulu where type='外部机构信息' ORDER BY ORDER_CODE可以执行。
7、db2中的sequence
创建sequence:CREATE SEQUENCE app_code AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER;
查询sequence的下一个值:SELECT nextval FOR app_code FROM SYSIBM.SYSDUMMY1;
插入:INSERT INTO RIGHTITEM_ROLE(RIGHTITEM_ROLE_ID) VALUES(NEXTVAL FOR app_code)
8、db2中SYSIBM.SYSDUMMY1就相当于Oracle 的dual
例如:
SELECT nextval FOR app_code FROM SYSIBM.SYSDUMMY1与
SELECT app_code.nextval from dual一样。
9、db2中WITH 语句与Oracle中的 START WITH CONNECT BY迭代语句相近。
例如:
//Oracle中的迭代
SELECT T.RIGHT_ITEM_CODE, T.RIGHT_CLASS_CODE
FROM EP_SYS_RIGHT_ITEM T
WHERE T.IS_ENABLE = 'Y'
START WITH T.RIGHT_ITEM_CODE = 'SYS'
CONNECT BY T.RIGHT_ITEM_CODE = PRIOR T.RIGHT_CLASS_CODE
//db2中的迭代
WITH n(RIGHT_ITEM_CODE,IS_ENABLE,RIGHT_CLASS_CODE) AS
(SELECT RIGHT_ITEM_CODE,IS_ENABLE,RIGHT_CLASS_CODE FROM EP_SYS_RIGHT_ITEM WHERE RIGHT_ITEM_CODE = 'SYS' AND IS_ENABLE = 'Y'
UNION ALL
SELECT T.RIGHT_ITEM_CODE,T.IS_ENABLE,T.RIGHT_CLASS_CODE FROM EP_SYS_RIGHT_ITEM T,n WHERE n.RIGHT_CLASS_CODE=T.RIGHT_ITEM_CODE AND T.IS_ENABLE = 'Y')
SELECT RIGHT_ITEM_CODE,RIGHT_CLASS_CODE FROM n WHERE IS_ENABLE = 'Y'