CONNECT RESET;
CONNECT TO SAMPLE;
EXPORT TO \集成交换格式\集成交换格式.log\SELECT * FROM NEW.EMPLOYEE; CONNECT RESET;
CONNECT TO SAMPLE;
IMPORT FROM \界定字符\9, 10, 11, 12, 13, 14) MESSAGES \界定字符.log\INSERT INTO DB2ADMIN.EMPLOYEE_DUP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM);
CONNECT RESET;
CONNECT TO SAMPLE;
IMPORT FROM \工作表格式\工作表格式.log\INSERT INTO DB2ADMIN.EMPLOYEE_DUP; CONNECT RESET;
CONNECT TO SAMPLE;
IMPORT FROM \集成交换格式\集成交换格式.log\INSERT INTO DB2ADMIN.EMPLOYEE_DUP; CONNECT RESET;
64、 装载数据
DB2 \
DB2 \
DB2 \ DB2 \ DB2 \ DB2 \TABLE EMPLOYEE_DUP ADD CONSTRAINT CHK_CNST CHECK(EDLEVEL > 12)\
CONNECT TO SAMPLE;
LOAD FROM \界定字符\10, 11, 12, 13, 14) MESSAGES \界定字符.LOG\INSERT INTO DB2ADMIN.EMPLOYEE_DUP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) COPY NO INDEXING MODE AUTOSELECT; CONNECT RESET;
65、 生成DDL语句
---SINGLE SCHEMA
DB2LOOK -D SAMPLE -Z NEW -U DB2ADMIN -E -L -X -M -R ;
---FULL DATABASE
DB2LOOK -D SAMPLE -A -E -L -X -M -R -F ;
66、 锁和并发控制
DB2 PROVIDES DIFFERENT LEVELS OF PROTECTION TO ISOLATE DATA: ◆ UNCOMMITTED READ ◆ CURSOR STABILITY ◆ READ STABILITY ◆ REPEATABLE READ
---DEFAULT ISOLATION LEVEL CURSOR STABILITY
EXERCISE(USING UR ISOLATION):
1.1 OPEN TWO COMMAND LINE PROCESSOR WINDOWS. WE WILL REFER TO THESE WINDOWS
AS W1 AND W2. MAKE SURE THEY CONNECT TO THE CORRECT INSTANCE BEFORE MOVING TO THE NEXT STEP.
1.2 SET THE AUTOCOMMIT FEATURE OFF IN BOTH W1 AND W2 BY EXECUTING THIS COMMAND:
DB2 => UPDATE COMMAND OPTIONS USING C OFF
1.3 CHANGE ISOLATION LEVEL OF W1 TO UNCOMMITTED READ. DB2 => CHANGE ISOLATION TO UR DB2 => CONNECT TO SAMPLE 1.4 NOW GO TO W2 WHERE THE DEFAULT ISOLATION IS CURSOR STABILITY. RUN THESE COMMANDS:
DB2 => CONNECT TO SAMPLE
DB2 => UPDATE STAFF SET SALARY = SALARY + 10
1.5 GO BACK TO W1 AND RUN THE FOLLOWING STATEMENT TO VIEW DIRTY RECORDS FROM THE STAFF TABLE.
DB2 => SELECT * FROM STAFF
YOU WILL SEE UPDATED, BUT NOT COMMITTED DATA IN W2. THIS IS KNOWN AS A DIRTY READ.
1.6 AGAIN SWITCH TO W2 AND ROLL BACK THE TRANSACTION. DB2 => ROLLBACK
1.7 NOW, YOU CAN GET ACTUAL DATA IN W1: DB2 => SELECT * FROM STAFF
1.8 CLOSE BOTH WINDOWS W1 AND W2.
EXERCISE(LOCKING A DATABASE)
THE SYNTAX FOR THE CONNECT COMMAND IS SHOWN HERE: CONNECT TO DATABASE_NAME [IN EXCLUSIVE MODE]
EXECUTE THE FOLLOWING COMMAND:
DB2 CONNECT TO SAMPLE IN EXCLUSIVE MODE USER YOUR_LOGIN USING YOUR_PASSWORD
NOW, TRY TO CONNECT TO SAMPLE DATABASE AS ANY USER OTHER THAN YOUR_LOGIN. THE FOLLOWING
MESSAGE IS RETURNED:
DB2 CONNECT TO SAMPLE USER TEST USING TEST
SQL1035N THE DATABASE IS CURRENTLY IN USE. SQLSTATE=57019
67、 警告级别设置
实例参数：DIAGLEVEL=3
日志文件：D:\\IBM\\SQLLIB\\DB2
68、 查看并设置注册变量
USE THE DB2SET COMMAND TO VIEW REGISTRY VARIABLE VALUES: ?DB2SET -I FOR INSTANCE-LEVEL PARAMETERS ?DB2SET -G FOR GLOBAL-LEVEL PARAMETERS ?DB2SET -I FOR ALL THE DEFINED PROFILES
?DB2SET -ALL FOR ALL THE REGISTRY VARIABLES WITH VALUES ?DB2SET -LR FOR ALL AVAILABLE PARAMETERS
TO SET A PARAMETER FOR THE CURRENT INSTANCE: ?SYNTAX: DB2SET PARAMETER=VALUE
?EXAMPLE: DB2SET DB2COMM=TCPIP,NPIPE
TO SET A PARAMETER’S VALUE FOR A SPECIFIC INSTANCE: ?SYNTAX: DB2SET PARAMETER=VALUE -I INSTANCE_NAME ?EXAMPLE: DB2SET DB2COMM=TCPIP,NPIPE -I ALTINST
TO SET A PARAMETER AT THE GLOBAL LEVEL: ?SYNTAX: DB2SET PARAMETER=VALUE -G
?EXAMPLE: DB2SET DB2COMM=TCPIP,NPIPE -G
69、 编目服务器
SYNTAX FOR CATALOGING A SERVER:
CATALOG TCPIP NODE NODE_NAME REMOTE {HOSTNAME | IP_ADDRESS} SERVER {SVCENAME | PORT_NUMBER}
EXAMPLE:
CATALOG TCPIP NODE DB2SERV REMOTE 9.186.128.141 SERVER 3700
db2 catalog tcpip node EAST remote 9.181.138.233 server 50000 db2 catalog database ISOURCE at node EAST db2 terminate
―――编目本地实例，当数据库出现多于一个实例时，其余的实例都会在本地进行编目结点 CATALOG LOCAL NODE test INSTANCE test SYSTEM TOP-GONGSHANG OSTYPE NT;
70、 刷新目录高速缓存
db2 terminate
71、 编目数据库
SYNTAX FOR CATALOGING A DATABASE:
CATALOG DATABASE DB_NAME AS DB_ALIAS AT NODE NODE_NAME
EXAMPLE:
CATALOG DATABASE SAMPLE AS SRV_SAMP AT NODE DB2SERVER
72、 输出重新定向到文件中
可以使用 -R 或 -Z CLP 选项将输出重定向到文件中（参见表 1）。例如： DB2 -R MYDATA\\ORGLIST.TXT \
73、 特殊字符在unix或者linux下的用法
建议在基于 LINUX 和 UNIX 的系统中使用双引号分隔符。如果在 CLP 命令模式下使用了特殊字符，那么这些特殊符号将由操作系统 SHELL 解释。这可能会生成意想不到的结果，除非使用双引号或换码符（如反斜线字符 \\）。例如，在 AIX? KORN SHELL 环境中执行下列命令时，如下所示：
DB2 SELECT * FROM EMPLOYEE WHERE EDLEVEL > 18
该命令被解释为“选择 EMPLOYEE 表中所有 EDLEVEL 大于 18 的记录，并将输出重定向到名为‘18’的文件”。下列命令将返回正确的输出：
DB2 \
或者
DB2 SELECT \\* FROM EMPLOYEE WHERE EDLEVEL \\> 18
74、 CLP 将“NULL”（以大写字符指定）识别为空字符串
DB2 UPDATE DATABASE CONFIGURATION USING MIRRORLOGPATH NULL
75、 查看缓存命令
HISTORY
76、 编辑缓存命令
EDIT OR E 3
77、 重新执行缓存命令
RUNCMD OR R 3
78、 选择编辑器
DB2SET DB2_CLP_EDITOR=\