CONNECT TO SAMPLE
CREATE USER TEMPORARY TABLESPACE USER_TEMP PAGESIZE 4 K MANAGED BY SYSTEM USING ('D:\\DB2\\NODE0000\\SAMPLE\\USER_TEMP' ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP CONNECT RESET
B、创建全局临时表
DB2 DECLARE GLOBAL TEMPORARY TABLE TEMPLOYEE LIKE EMPLOYEE NOT LOGGED
C、查看创建过的临时表
SELECT * FROM SESSION.TEMPLOYEE
需要取消事务的自动落实(UPDATE COMMAND OPTIONS USING C OFF) FOR EXAMPLE:
DB2>CONNECT TO SAMPLE DB2>LIST COMMAND OPTIONS
DB2>UPDATE COMMAND OPTIONS USING C OFF DB2>LIST COMMAND OPTIONS
DB2>DECLARE GLOBAL TEMPORARY TABLE TSTAFF LIKE STAFF NOT LOGGED DB2>INSERT INTO SESSION.TSTAFF SELECT * FROM STAFF DB2>SELECT * FROM SESSION.STAFF DB2>COMMIT
DB2>SELECT * FROM STAFF DB2>CONNECT RESET
11、 建立模式名
CREATE SCHEMA SCHEMA_NAME AUTHORIZATION AUTH_NAME
12、 建立视图
CREATE VIEW VIEW_NAME (COLUMN_NAMES) AS FULLSELECT WITH {LOCAL|CASCADED} CHECK OPTION FOR EXAMPLE:
CREATE VIEW NEW.V3 AS SELECT * FROM SALES AS SALES
CREATE VIEW NEW.V4 AS SELECT * FROM SALES AS SALES WITH CASCADED CHECK OPTION
CREATE VIEW NEW.V5 AS SELECT * FROM SALES AS SALES WITH LOCAL CHECK OPTION
13、 建立序列
CREATE SEQUENCE ORG_SEQ START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 24
--GET SEQUENCE
VALUES NEXTVAL FOR ORG_SEQ
14、 建立表空间
CREATE [{REGULAR | LARGE | SYSTEM TEMPORARY | USER TEMPORARY}] TABLESPACE table_space_name [PAGESIZE integer [K]]
MANAGED BY {SYSTEM | DATABASE} USING (container_definition_string) [BUFFERPOOL buffpool_name]
15、 查看系统表视图
SELECT * FROM SYSCAT.TABLES
16、 查看列
SELECT SUBSTR(COLNAME,1,20) AS \列名\AS 类型,LENGTH AS 长度 FROM SYSCAT.COLUMNS WHERE TABNAME='STAFF'
17、 查看表结构
DB2 DESCRIBE TABLE USER1.DEPARTMENT DB2 DESCRIBE SELECT * FROM USER.TABLES
18、 查看当前用户所有表和视图
LIST TABLES
19、 查看表的索引
DB2 DESCRIBE INDEXES FOR TABLE USER1.DEPARTMENT
20、 查看视图
SELECT VIEWNAME FROM VIEWS WHERE VIEWNAME='V_ZJT'
21、 查看索引
SELECT INDNAME FROM SYSCAT.INDEXES WHERE INDNAME='I_ZTABLES'
22、 查看存贮过程
SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES
23、 列出所有的系统表和视图
LIST TABLES FOR SYSTEM
24、 列出当前连接的数据库
LIST ACTIVE DATABASES
25、 列出并设置命令选项
LIST COMMAND OPTIONS
UPDATE COMMAND OPTIONS USING C OFF
26、 列出系统数据库目录
LIST DATABASE DIRECTORY
27、 列出表空间
LIST TABLESPACES
28、 列出表空间容器
LIST TABLESPACE CONTAINERS FOR 1
29、 列出连接
LIST APPLICATION
30、 重新连接
CONNECT RESET
31、 中断数据库连接
DISCONNECT DB2_GCB
32、 杀掉连接
FORCE APPLICATION(0)
DB2 FORCE APPLICATIONS ALL (强迫所有应用程序从数据库断开)
33、 锁定表
LOCK TABLE TEST IN EXCLUSIVE MODE
34、 共享表
LOCK TABLE TEST IN SHARE MODE
35、 启动实例
DB2START
36、 停止实例
DB2STOP
37、 取得当前用户的存取权限
GET AUTHORIZATIONS
38、 表或视图特权
GRANT SELECT,DELETE,INSERT,UPDATE ON TABLES TO USER GRANT ALL ON TABLES TO USER WITH GRANT OPTION
39、 程序包特权
GRANT EXECUTE ON PACKAGE SQLE0F00 TO PUBLIC
40、 模式特权
GRANT CREATEIN ON SCHEMA SCHEMA-NAME TO USER
41、 数据库特权
GRANT CONNECT,CREATETAB,DBADM ON DATABASE TO USER
42、 索引特权
GRANT CONTROL ON INDEX INDEX-NAME TO USER
43、 信息帮助 (? XXXnnnnn )
例:? SQL30081
44、 SQL 帮助(说明 SQL 语句的语法)
? STATEMENT 例如，? CONNECT
45、 SQLSTATE 帮助(说明 SQL 的状态和类别代码)
? SQLSTATE 或 ? CLASS-CODE