DB2命令一、常用命令1、建立数据库DB2_GCBCREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCBUSING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 322、连接数据库connect to sample1 user db2admin using 83012063、建立用户、建立表别名create alias db2admin.tables for sysstat.tables;CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWScreate alias db2admin.columns for syscat.columns;create alias guest.columns for syscat.columns;GRANTDBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ ROUTINE ON DATABASE TO USER GUEST;GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA DB2ADMIN TO USER GUEST WITH GRANT OPTION;4、建立、更改、删除表结构CREATE TABLE table_name(column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY], column2 DATATYPE [NOT NULL],...)create table zjt_tables as(select * from tables) definition only;create table zjt_views as(select * from views) definition only;ALTER TABLE table_name ADD COLUMN column_name DATATYPEALTER TABLE table_name ADD PRIMARY KEY (column_name)ALTER TABLE table_name DROP PRIMARY KEY (column_name)DROP table table_name5、插入记录，修改记录，删除记录insert into zjt_tables select * from tables;insert into zjt_views select * from views;insert into test(id,phone) values(1,’5’);update test set phone=’65356675’ where id=1;delete from test where id=1;6、建立视图create view V_zjt_tables as select tabschema,tabname from zjt_tables;7、建立触发器CREATE TRIGGER zjt_tables_delAFTER DELETE ON zjt_tablesREFERENCING OLD AS OFOR EACH ROW MODE DB2SQLInsert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10))8、建立、删除唯一性索引CREATE UNIQUE INDEX I_ztables_tabnameON zjt_tables(tabname);DROP index I_ztables_tabname9、查看表select tabname from tableswhere tabname='ZJT_TABLES';10、查看列select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度from columnswhere tabname='ZJT_TABLES';11、查看表结构db2 describe table user1.departmentdb2 describe select * from user.tables12、查看表的索引db2 describe indexes for table user1.department13、查看视图select viewname from viewswhere viewname='V_ZJT_TABLES';14、查看索引select indname from indexeswhere indname='I_ZTABLES_TABNAME';15、查看存贮过程SELECTSUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES;16、类型转换(cast)ip datatype:varcharselect cast(ip as integer)+50 from log_comm_failed17、重新连接connect reset18、中断数据库连接disconnect db2_gcb19、view applicationLIST APPLICATION;20、kill applicationFORCE APPLICATION(0);db2 force applications all (强迫所有应用程序从数据库断开)21、lock tablelock table test in exclusive mode22、共享lock table test in share mode23、显示当前用户所有表list tables24、列出所有的系统表list tables for system25、显示当前活动数据库list active databases26、查看并设置命令选项list command optionsupdate command options using c off27、系统数据库目录LIST DATABASE DIRECTORY28、表空间list tablespacesCREATE [{REGULAR | LARGE | SYSTEM TEMPORARY | USER TEMPORARY}] TABLESPACE table_space_name [PAGESIZE integer [K]]MANAGED BY {SYSTEM | DATABASE}USING (container_definition_string) [BUFFERPOOL buffpool_name]29、表空间容器LIST TABLESPACE CONTAINERS FORExample: LIST TABLESPACE CONTAINERS FOR 130、显示用户数据库的存取权限GET AUTHORIZATIONS31、启动实例DB2START32、停止实例db2stop33、表或视图特权grant select,delete,insert,update on tables to user grant all on tables to user WITH GRANT OPTION34、程序包特权GRANT EXECUTEON PACKAGE PACKAGE-nameTO PUBLIC35、模式特权GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER36、数据库特权grant connect,createtab,dbadm on database to user 37、索引特权grant control on index index-name to user38、信息帮助 (? XXXnnnnn )例:? SQL3008139、SQL 帮助(说明 SQL 语句的语法)help statement例如，help SELECT40、SQLSTATE 帮助(说明 SQL 的状态和类别代码) sqlstate 或 ? class-code41、更改与"管理服务器"相关的口令db2admin setid username password42、创建 SAMPLE 数据库db2sampldb2sampl F:(指定安装盘)43、使用操作系统命令! dir44、转换数据类型 (cast)SELECT EMPNO, CAST(RESUME AS VARCHAR(370))FROM EMP_RESUMEWHERE RESUME_FORMAT = 'ascii'45、UDF要运行 DB2 Java 存储过程或 UDF，还需要更新服务器上的 DB2 数据库管理程序配置，以包括在该机器上安装 JDK 的路径db2 update dbm cfg using JDK11_PATH d:\sqllib\java\jdkTERMINATEupdate dbm cfg using SPM_NAME sample46、检查 DB2 数据库管理程序配置db2 get dbm cfg47、检索具有特权的所有授权名SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTHORDER BY GRANTEE, GRANTEETYPE, 3create table yhdab(id varchar(10),password varchar(10),ywlx varchar(10),kh varchar(10));create table ywlbb(ywlbbh varchar(8),ywmc varchar(60))48、修改表结构alter table yhdab ALTER kh SET DATA TYPE varchar(13); alter table yhdab ALTER ID SET DATA TYPE varchar(13); alter table lst_bsi alter bsi_money set data type int; insert into yhdab values('20000300001','123456','user01','20000300001'),('20000300002','123456','user02','20000300002');49、业务类型说明insert into ywlbb values('user01','业务申请'),('user02','业务撤消'),('user03','费用查询'),('user04','费用自缴'),('user05','费用预存'),('user06','密码修改'),('user07','发票打印'),('gl01','改用户基本信息'),('gl02','更改支付信息'),('gl03','日统计功能'),('gl04','冲帐功能'),('gl05','对帐功能'),('gl06','计费功能'),('gl07','综合统计')50、事务的提交、回滚commit;rollback;51、查看db2的许可证信息Db2licm –l52、显示实例名称Db2ilist53、更新实例Db2iupdt instance_name54、DAS实例的配置信息Db2 get admin cfg55、其他实例的配置信息Db2 get dbm cfg或者db2 get database manager configuration56、数据库的配置信息Db2 get database configuration for database_name57、创建工具目录数据库db2 create tools catalog cc create new database toolsdb58、数据库备份CONNECT TO TAIS;QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;CONNECT RESET;BACKUP DATABASE TAIS TO "D:\backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;CONNECT TO TAIS;UNQUIESCE DATABASE;59、数据库恢复db2 restore database tais from d:\backup taken at 20061017 without rolling forward60、切换实例（windows）set db2instance=tais61、创建全局临时表a、创建用户临时表空间db2 CREATE USER TEMPORARY TABLESPACE user_tempMANAGED BY SYSTEM USING('/home/inst##/usertemp')b、创建全局临时表DECLARE GLOBAL TEMPORARY TABLE table_name1LIKE table_name2 NOT LOGGEDfor example:db2 DECLARE GLOBAL TEMPORARY TABLE temployee LIKE employee NOT LOGGEDc、查看创建过的临时表select * from session.temployee需要取消事务的自动落实(update command options using c off) for example:db2>CONNECT TO sampledb2>LIST COMMAND OPTIONSdb2>UPDATE COMMAND OPTIONS USING C OFFdb2>LIST COMMAND OPTIONSdb2>DECLARE GLOBAL TEMPORARY TABLE tstaffLIKE staff NOT LOGGEDdb2>INSERT INTO session.tstaff SELECT * FROM staffdb2>SELECT * FROM session.staffdb2>COMMITdb2>SELECT * FROM staffdb2>CONNECT RESET62、创建模式名CREATE SCHEMA schema_name AUTHORIZATION auth_name63、断开当前的连接db2 DISCONNECT CURRENT64、创建视图CREATE VIEW view_name (column_names)AS fullselectWITH {LOCAL|CASCADED} CHECK OPTIONfor example:CREATE view NEW.V3 AS SELECT * FROM NEW.SALES AS SALES;CREATE view NEW.V4 AS SELECT * FROM NEW.SALES AS SALES WITH CASCADED CHECK OPTION;CREATE view NEW.V5 AS SELECT * FROM NEW.SALES AS SALES WITH LOCAL CHECK OPTION;65、创建序列CREATE SEQUENCE ORG_SEQSTART WITH 1INCREMENT BY 1NO MAXVALUENO CYCLECACHE 24--get sequencevalues nextval for seq166、得到当前时间values current timestamp67、创建索引type-2 indexes---对应用和并发进行保护,8版本以后使用a、Unique index —Ensures uniqueness of key column(s) datab、Bidirectional index（双向索引）—Allows scanning of indexes in eitherdirectionc、Clustered index（簇索引）— Places the rows of the tablein the samephysical order as the index keysd、多维索引---数据仓库中使用CREATE UNIQUE INDEX index_nameON table_name (column_name {ASC | DESC} [, column_name {ASC | DESC}…])INCLUDE column_namesCLUSTERPCTFREE integerMINPCTUSED integerALLOW REVERSE SCANSCREATE INDEX inON employee(empno ASC)PCTFREE 10MINPCTUSED 40Design Advisor in the CLP: db2advisfor example:db2advis -d database_name[{-w workload_name |-s "sql_statement" |-i filename}][-a userid[/password] ][-l disklimit][-t max_advise_time][-h][-p][-o out_file]db2advis -d sample-s "SELECT * FROM employee e WHERE firstnmeLIKE ’A%’"-a inst00/inst00-l 53-t 20exercises:a、create normal indexCONNECT TO SAMPLE;CREATE INDEX DB2ADMIN.IDX_STAFF_NAME ON NEW.STAFF ("NAME" ASC) PCTFREE 10 MINPCTUSED 10 COLLECT STATISTICS ;COMMENT ON Index DB2ADMIN.IDX_STAFF_NAME IS 'common index on column name ';CONNECT RESET;b、create unique indexCONNECT TO SAMPLE;CREATE UNIQUE INDEX DB2ADMIN.UIDX_STAFF_ID ON NEW.STAFF (ID ASC) PCTFREE 10 MINPCTUSED 10 COLLECT STATISTICS ;CONNECT RESET;c、create Bidirectional IndexCONNECT TO SAMPLE;CREATE INDEX DB2ADMIN.BIDX_STAFF_SALARY ONNEW.STAFF (SALARY ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS ;CONNECT RESET;d、Create Clustered IndexCONNECT TO SAMPLE;CREATE INDEX DB2ADMIN.CIDX_STAFF_DEPT ON NEW.STAFF (DEPT ASC) CLUSTER PCTFREE 10 MINPCTUSED 10 COLLECT SAMPLED DETAILED STATISTICS ;CONNECT RESET;68、使用约束Using Constraints(主键的字段不可为空，唯一键的字段可以为空)a、Adding a Primary Key to an Existing TableALTER TABLE student ADD CONSTRAINT pk_id PRIMARY KEY(id)exercises:A、Use this command to create a new table called po_masterCREATE 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_masterALTER 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 commandCREATE 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_detailtable 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) REFERENCESDB2ADMIN.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 TOSAMPLE;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 ConstraintsALTER TABLE po_master ADD CONSTRAINT chk_bill_date CHECK (bill_date <= po_date)69、导入导出数据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 "C:\无界定字符" OF DEL MESSAGES "C:\无界定字符.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;EXPORT TO "C:\界定字符" OF DEL MODIFIED BY COLDEL, MESSAGES "C:\界定字符.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;EXPORT TO "C:\工作表格式" OF WSF MODIFIED BY 1 MESSAGES "C:\工作表格式.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;EXPORT TO "C:\集成交换格式" OF IXF MESSAGES "C:\集成交换格式.log" SELECT * FROM NEW.EMPLOYEE;CONNECT RESET;CONNECT TO SAMPLE;IMPORT FROM "C:\界定字符" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) MESSAGES "C:\界定字符.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 "C:\工作表格式" OF WSF MESSAGES "C:\工作表格式.log" INSERT INTO DB2ADMIN.EMPLOYEE_DUP;CONNECT RESET;CONNECT TO SAMPLE;IMPORT FROM "C:\集成交换格式" OF IXF MESSAGES "C:\集成交换格式.log" INSERT INTO DB2ADMIN.EMPLOYEE_DUP;CONNECT RESET;70、装载数据db2 "CONNECT TO sample"db2 "CREATE TABLE exc_emp LIKE employee"db2 "ALTER TABLE exc_emp ADD COLUMN time TIMESTAMP"db2 "ALTER TABLE exc_emp ADD COLUMN message CLOB(32K)"db2 "CREATE TABLE employee_dup LIKE employee"db2 "ALTER TABLE employee_dup ADD CONSTRAINTchk_cnst CHECK(EDLEVEL > 12)"CONNECT TO SAMPLE;LOAD FROM "C:\界定字符" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) MESSAGES "C:\界定字符.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;---check constraintdb2 SET INTEGRITY FOR employee_dup IMMEDIATE CHECKED FOR EXCEPTION IN employee_dup USE exc_emp71、生成DDL语句---single schemadb2look -d SAMPLE -z NEW -u DB2ADMIN -e -l -x -m -r ;---full databasedb2look -d SAMPLE -a -e -l -x -m -r -f ;72、索和并发控制DB2 provides different levels of protection to isolate data:◆ Uncommitted read◆ Cursor stability◆ Read stability◆ Repeatable read---default isolation levelCursor stabilityexercise(using ur isolation):1.1 Open two Command Line Processor windows. We will refer to these windowsas W1 and W2. Make sure they connect to the correct instance before moving to the nextstep.1.2 Set the AUTOCOMMIT feature OFF in both W1 and W2 by executing this command:db2 => update command options using c off1.3 Change isolation level of W1 to uncommitted read.db2 => CHANGE ISOLATION TO URdb2 => CONNECT TO sample1.4 Now go to W2 where the default isolation is cursor stability. Run these commands:db2 => CONNECT TO sampledb2 => UPDATE staff SET salary = salary + 101.5 Go back to W1 and run the following statement to view dirty records from the staff table.db2 => SELECT * FROM staffYou 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 => ROLLBACK1.7 Now, you can get actual data in W1:db2 => SELECT * FROM staff1.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 MODEUSER your_login USING your_passwordNow, try to connect to sample database as any user other than your_login. The followingmessage is returned:db2 connect to sample user test using testSQL1035N The database is currently in use. SQLSTATE=5701973、出错处理实例参数：diaglevel=3日志文件：D:\IBM\SQLLIB\DB274、View Registry VariablesUse the db2set command to view registry variable values:db2set -i for instance-level parametersdb2set -g for global-level parametersdb2set -I for all the defined profilesdb2set -all for all the registry variables with valuesdb2set -lr for all available parametersTo set a parameter for the current instance:Syntax: db2set parameter=valueExample: db2set DB2COMM=tcpip,npipeTo set a parameter’s value for a specific instance:Syntax: db2set parameter=value -i instance_nameExample: db2set DB2COMM=tcpip,npipe -i altinstTo set a parameter at the global level:Syntax: db2set parameter=value -gExample: db2set DB2COMM=tcpip,npipe -g75、Cataloging the ServerSyntax for cataloging a server:CATALOG TCPIP NODE node_nameREMOTE {hostname | ip_address}SERVER {svcename | port_number}Example:CATALOG TCPIP NODE db2serv REMOTE 9.186.128.141 SERVER 370076、cataloging the databaseSyntax for cataloging a database:CATALOG DATABASE db_name AS db_aliasAT NODE node_nameExample:CATALOG DATABASE sample AS srv_sampAT NODE db2server二、目录视图说明说明目录视图检查约束 SYSCAT.CHECKS列 SYSCAT.COLUMNS检查约束引用的列 SYSCAT.COLCHECKS关键字中使用的列 SYSCAT.KEYCOLUSE数据类型 SYSCAT.DATATYPES函数参数或函数结果 SYSCAT.FUNCPARMS参考约束 SYSCAT.REFERENCES模式 SYSCAT.SCHEMATA表约束 SYSCAT.TABCONST表 SYSCAT.TABLES触发器 SYSCAT.TRIGGERS用户定义函数 SYSCAT.FUNCTIONS视图 SYSCAT.VIEWS三、字符串类型二进制大对象 (BLOB) 字符串。
