Database

DB2 V9.7 Rename Column注意事项

Categories: Database; Tagged with: DB2 • RENAME COLUMN • SQL; @ October 6th, 2010 10:01

DB2需要呼叫存储过程ALTOBJ存储过程以完成重命名. 该过程将重建Table, 并恢复就有的PrimaryKey, Index信息及Table的内容.
在Rename带有Index的Column时需要额外注意, 可能先DROP掉Index, 在重建完毕后再CREATE.

问题描述:

通常情况下, 使用UI或SQL均可顺利执行该过程, 但在Rename带有Index的Column时, 会产生错误:
原因是Table重建完毕后, DB2试图恢复之前的Index – 但此时使用了Rename前的ColumnName, 于是因为找不到Column而出错.

如: Rename Table中的 Column "NAME"为"NAME2"的SQL:

CALL SYSPROC.ALTOBJ ( ‘APPLY_CONTINUE_ON_ERROR’, ‘CREATE TABLE ADMINISTRATOR.STU ( ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, CACHE 20) , NAME2 VARCHAR (64) NOT NULL ) IN USERSPACE1 ‘, -1, ? );

AlertTable UI提示信息:

解决方法:

1. 在进行Rename之前, 先Drop掉Column相关的Index.
2. 进行Rename操作
3. 恢复之前创建的Index – 注意使用新的Column名称创建

该方法适用于UI手工操作, 也适用于代码编写.

DB2 Derby Paged Select SQL分页查询语句

Categories: Database; Tagged with: DB2 • Derby • Paged Select • SQL; @ October 5th, 2010 21:50

DB2 Paged Select SQL 分页:

分页举例:
SELECT * FROM (
SELECT DEPT.ID, DEPT.MGRNO, ROWNUMBER() OVER (ORDER BY DEPT.ID) AS NS FROM DEPT WHERE DEPT.MGRNO > 0
) AS TEMP WHERE TEMP.NS BETWEEN 1 and 5;

See:

1. API: http://www.ibm.com/developerworks/data/library/techarticle/0307balani/0307balani.html
2. db2分页sql语法
3. db2排序rownumber函数讨论
4. Oracle, DB2 及 MySQL 分页查询写法

Derby Paged Select SQL 分页语句:

SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

See: Derby 10.4之后开始支持 http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html

DB2 Alter 增加自增列 Add AutoIncrement Column

Categories: Database; Tagged with: AUTO INCREMENT • DB2 • SQL; @ October 5th, 2010 21:17

ALTER TABLE TABLE2 ALTER COLUMN ID2 DROP DEFAULT SET GENERATED AS IDENTITY ( START WITH 0 INCREMENT BY 1 NO CACHE ) ;

(more…)

DB2 PrimaryKey 主键操作SQL总结

Categories: Database; Tagged with: DB2 • Primary key • SQL; @ October 5th, 2010 21:06

1. ADD PRIMARY KEY 增加主键

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMPLOYEE_ID, ORGNO);

2. DROP PRIMARY KEY 删除主键

ALTER TABLE EMPLOYEE DROP PRIMARY KEY

3. SET AUTOINCREMENT 设置自增

ALTER TABLE TB2 ALTER COLUMN ID (DROP DEFAULT) SET GENERATED AS IDENTITY ( START WITH 0 INCREMENT BY 1 NO CACHE ) ;

DROP DEFATULT: 如果之前已有DEFAULT值时使用.

DB2 Index 索引操作SQL

Categories: Database; Tagged with: DB2 • Index • SQL; @ October 5th, 2010 20:15

以下罗列与Index有关操作的SQL, 如: List/CREATE/DROP 等操作.

1. List Indexes 获得所有索引

SELECT * FROM SYSIBM.SYSINDEXES WHERE CREATOR NOT LIKE ‘SYS%’ AND TBNAME=’tablename‘;

2. CREATE INDEX 创建索引

CREATE INDEX EMPLOYEE_EMPLOYEE_ID_IDX ON EMPLOYEE(EMPLOYEE_ID);
CREATE UNIQUE INDEX EMPLOYEE_NAME_IDX ON EMPLOYEE(NAME);

3. DROP INDEX 删除索引

DROP INDEX EMPLOYEE_ORGNO_IDX;

Newer Posts <-> Older Posts