46、 更改与\管理服务器\相关的口令
DB2ADMIN SETID USERNAME PASSWORD
47、 使用操作系统命令
! DIR
48、 转换数据类型 (cast)
SELECT EMPNO, CAST(RESUME AS VARCHAR(370)) FROM EMP_RESUME WHERE RESUME_FORMAT = 'ascii'
49、 UDF
要运行 DB2 Java 存储过程或 UDF，还需要更新服务器上的 DB2 数据库管理 程序配置，以包括在该机器上安装 JDK 的路径
DB2 UPDATE DBM CFG USING JDK_PATH D:\\IBM\\SQLLIB\\JAVA\\JDK
TERMINATE
UPDATE DBM CFG USING SPM_NAME SAMPLE
50、 检索具有特权的所有授权名
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTH
ORDER BY GRANTEE, GRANTEETYPE, 3
51、 事务的提交、回滚
COMMIT ROLLBACK
52、 查看db2的许可证信息
DB2LICM -L
53、 显示实例名称
DB2ILIST
54、 更新实例
DB2IUPDT INSTANCE_NAME
55、 取得DAS实例的配置信息
DB2 GET ADMIN CFG
56、 取得其他实例的配置信息
DB2 GET DBM CFG或者 DB2 GET DATABASE MANAGER CONFIGURATION
57、 取得数据库的配置信息
DB2 GET DATABASE CONFIGURATION FOR DATABASE_NAME
58、 数据库备份
CONNECT TO TAIS
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS CONNECT RESET
BACKUP DATABASE TAIS TO \PARALLELISM 1 WITHOUT PROMPTING CONNECT TO TAIS UNQUIESCE DATABASE
59、 数据库恢复
DB2 RESTORE DATABASE TAIS FROM D:\\BACKUP TAKEN AT 20061017 WITHOUT ROLLING FORWARD
60、 切换实例（windows）
SET DB2INSTANCE=TAIS
61、 取得当前时间
VALUES CURRENT TIMESTAMP
62、 使用约束
A、Use this command to create a new table called po_master CREATE TABLE PO_MASTER ( PO_NO INTEGER NOT NULL, PO_DATE DATE NOT NULL, BILL_NO INTEGER NOT NULL, BILL_DATE DATE NOT NULL, DESCRIPTION VARCHAR (200),
CONSTRAINT PK_PO_MASTER PRIMARY KEY (PO_NO), CONSTRAINT U_KEY_BILL_NO UNIQUE (BILL_NO))
B、Use the following command to alter an existing table called po_master
ALTER TABLE PO_MASTER ADD CONSTRAINT U_KEY_BILL_NO UNIQUE (BILL_NO)
C、Using the CLP, create a table called po_master with a primary key on po_no using the following command CREATE TABLE PO_DETAIL ( PO_NO INTEGER NOT NULL, S_NO INTEGER NOT NULL,
ITEM_CODE INTEGER NOT NULL, DESCRIPTION VARCHAR (100), QUANTITY INTEGER NOT NULL, RATE INTEGER NOT NULL, PRIMARY KEY (PO_NO, S_NO),
CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY(PO_NO)
REFERENCES ADMINISTRATOR.PO_MASTER (PO_NO))
D、There are four CREATE TABLE options for defining the delete rule: NO ACTION,RESTRICT :If RESTRICT or NO ACTION is selected, an error occurs and no records are deleted if you try to delete records from parent table.
CASCADE: If CASCADE is selected, the delete operation is propagated to the dependent tables, that is, records in the po_master table as well as all the related records in the po_detail table are automatically deleted.
SET NULL: If SET NULL is selected, the delete operation in po_master table is allowed and the the related records in po_detail table are set to NULL.
E、modify table
---add restrict constraint(delete) CONNECT TO SAMPLE ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE RESTRICT ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION CONNECT RESET
---add no action constraint(delete) CONNECT TO SAMPLE ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION CONNECT RESET
---add cascade constraint(delete) CONNECT TO SAMPLE; ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ; CONNECT RESET;
---add set null constraint(delete) CONNECT TO SAMPLE; ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE SET NULL ON
UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ; CONNECT RESET;
---add restrict constraint(update) CONNECT TO SAMPLE;
ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE RESTRICT ON UPDATE RESTRICT ENFORCED ENABLE QUERY OPTIMIZATION ; CONNECT RESET;
---add no action constraint(update) CONNECT TO SAMPLE;
ALTER TABLE DB2ADMIN.PO_DETAIL DROP FOREIGN KEY
FK_PO_MASTERDETAIL ADD CONSTRAINT FK_PO_MASTERDETAIL FOREIGN KEY (PO_NO) REFERENCES DB2ADMIN.PO_MASTER (PO_NO) ON DELETE RESTRICT ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ; CONNECT RESET;
F、Using Check Constraints
ALTER TABLE po_master ADD CONSTRAINT chk_bill_date CHECK (bill_date <= po_date)
63、 导入导出数据
DB2 supports the following data formats for extraction and insertion: ◆ Delimited ASCII format (DEL) ◆ Integrated exchange format (IXF) ◆ Worksheet format (WSF) ◆ Non-delimited ASCII (ASC)
CONNECT TO SAMPLE;
EXPORT TO \无界定字符\无界定字符.log\SELECT * FROM NEW.EMPLOYEE; CONNECT RESET;
CONNECT TO SAMPLE;
EXPORT TO \界定字符\界定字符.log\SELECT * FROM NEW.EMPLOYEE; CONNECT RESET;
CONNECT TO SAMPLE;
EXPORT TO \工作表格式\工 作表格式.log\SELECT * FROM NEW.EMPLOYEE;