常用系統目錄查詢速查表
| 查詢目的 | SQL (建議使用 SYSCAT 視圖) |
|---|
| 使用者擁有的表 | SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = 'USER' |
| 使用者的函數 | SELECT * FROM SYSCAT.FUNCTIONS WHERE FUNCSCHEMA = 'USER' |
| 使用者的 View | SELECT * FROM SYSCAT.VIEWS WHERE VIEWSCHEMA = 'USER' |
| DB2 版本資訊 | SELECT * FROM SYSIBM.SYSVERSIONS |
| 使用者的 Trigger | SELECT * FROM SYSCAT.TRIGGERS WHERE TRIGSCHEMA = 'USER' |
| 表空間狀態 | SELECT * FROM SYSCAT.TABLESPACES |
| Sequence 狀態 | SELECT * FROM SYSCAT.SEQUENCES |
| Schema 狀態 | SELECT * FROM SYSCAT.SCHEMATA |
| 索引狀態 | SELECT * FROM SYSCAT.INDEXES |
| 表欄位定義 | SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME = 'AAAA' |
| 資料類型定義 | SELECT * FROM SYSCAT.DATATYPES |
| 緩衝池狀態 | SELECT * FROM SYSCAT.BUFFERPOOLS |
欄位修改限制
- 僅能修改 VARCHAR 類型長度，且只能增加不能減少。
檢視表結構
DESCRIBE TABLE TABLE_NAME
DESCRIBE SELECT * FROM SCHEMA.TABLE_NAME
索引進階建立語法
- UNIQUE：強制唯一性
- ALLOW REVERSE SCANS：雙向掃描
- INCLUDE 欄位 (限 UNIQUE)：
CREATE UNIQUE INDEX IX_EMP ON EMP (EMPNO) INCLUDE (LASTNAME, FIRSTNAME)
- CLUSTER：
CREATE INDEX IX_DEPT ON EMP (WORKDEPT) CLUSTER
鎖定與日誌控制
- 取得排他鎖：
LOCK TABLE TBL IN EXCLUSIVE MODE，COMMIT 釋放。
- 關閉表日誌：
ALTER TABLE TBL ACTIVATE NOT LOGGED INITIALLY (僅首次 UOW 有效) 或建表時 NOT LOGGED INITIALLY。
- 刪除表：
DROP TABLE SCHEMA.TABLE_NAME
- 重命名表：
RENAME TABLE OLD TO NEW
- 目前時間戳：
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1
概要表 (MQT) - 類似 Oracle Materialized View
CREATE SUMMARY TABLE SUM_TBL AS (SELECT * FROM SRC WHERE COL1='AAA')
DATA INITIALLY DEFERRED REFRESH DEFERRED;
REFRESH TABLE SUM_TBL;
全域暫存表 (DGTT)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_EMP
AS (SELECT * FROM EMP) DEFINITION ONLY
EXCLUDING IDENTITY COLUMN ATTRIBUTES
ON COMMIT DELETE ROWS
NOT LOGGED;
視圖與別名
CREATE VIEW V_NAME AS SELECT * FROM TBL WHERE ...;
DROP VIEW V_NAME;
SELECT TEXT FROM SYSCAT.VIEWS WHERE VIEWNAME='V_NAME';
CREATE ALIAS ALIAS_NAME FOR PRO_NAME; -- TABLE/VIEW/ALIAS/NICKNAME
Sequence 管理
CREATE SEQUENCE SEQ_NAME START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 24;
ALTER SEQUENCE SEQ_NAME ...;
DROP SEQUENCE SEQ_NAME;
