db2命令记录(一)

合集下载

db2 常用命令

db2 常用命令db2 常用命令1. db2 "get dbm cfg" ——获取当前数据库配置信息。

2. db2 list db directory ——列出数据库的目录。

3. db2 list node directory ——列出节点的目录。

4. db2 "start database <dbname>" ——启动指定的数据库。

5. db2 "stop database <dbname>" ——停止指定的数据库。

6. db2 "connect to <dbname>" ——连接到指定的数据库。

7. db2 "update dbm cfg using max application <number>" ——修改数据库最大连接数。

8. db2 list active databases ——列出所有活动的数据库。

9. db2 get snapshot for dynamic sql on <database> ——列出动态SQL的快照。

10. db2 terminate ——立即终止当前会话。

11. db2 "create database <dbname>" ——创建新的数据库。

12. db2 connect reset ——重置数据库连接。

13. db2 "list tables" ——列出表格列表。

14. db2 "describe table <tablename>" ——查看表格结构。

15. db2 "select * from <tablename>" ——查询指定表格的信息。

16. db2 "drop table <tablename>" ——删除指定的表格。

DB2命令大全

Db2 ? sql-204
1.12 备份数据库
备份表空间
Db2 “backup database databasename” tablespace tablespaceName to /path
Db2 backup database dbname to /path
db2 list db directory
1.16插入空值到表中
import from /dev/null of del replace into db2inst1.表名
1.17建立nickname
1、在db2命令窗口下，运行connect to 目标库 user 用户名 using 密码
Select stmt_text ,(stop_time-start_time) from stmt_ monitor_name Where stmt_operation not in (7,8,9,19) order by decimal(stop_time-start_time) desc fetch first 10 rows only
Lock Object Name = 1163533 ＃被锁对象名称
Object Type = Row ＃被锁对象类型
Tablespace Name = tbs_data ＃被锁对象所在的表空间
－LOCKTIMEOUT单位是秒，是锁等待最长时间，超过该时间仍未获得锁，则返回错误。
设置提示：
－缺省情况下，LOCKTIMEOUT是-1，意味着锁等待时间无限期，这和实际应用需求一般是不太相符的，需要将其值设为大于0的一个数。
－DLCHKTIME时间通常要设得比LOCKTIMEOUT时间小一些，否则未等发现死锁，就会被以锁等待超时而返回错误。

db2命令集

> --
查看表结构
13
、
db2 list tables --
查看数据库中所有表结构
list tables for system --
列出所有系统表
14
、
db2 list tablespaces --
列出表空间
日期
) --
计算出日期中是周几（
1
是周日，
2
是周一
.......7
是周六）
dayofweek_iso --
计算出日期中是周几
(1
是周一
.......7
是周日
)
例如：
dayofweek(date(2008-01-16)) --
字段名，转换后的值
) --
对是
null
的字段进行值转换
例如：
select coalesce(id,1) from <
表名
> --
对表中
id
如果为
null
转换成
1
17
、
dayofweek(
YYYY-MM-DD
的形式，如果不是需要进行转换，否则函数不
能使用
例如：日期是
20080116
必须要进行转换
dayofweek(concat(concat(concat(substr(openDate,1,4),'-'),concat(substr(openDate,5,2),'-')),substr(
创建缓冲池
(32K)(OA_TASK)

DB2命令大全

DB2命令⼤全check Archiving processing查看⽇志归档情况db2 "SELECT DATE(CAST(START_TIME as TIMESTAMP)) as DATE,count(*) as NUMBER_OF_LOGS_PER_DAY,(count(*)*23.4375) as AMOUNT_LOGS_DAY_MB,DBPARTITIONNUM as DBPARTFROM SYSIBMADM.DB_HISTORYWHERE operation = 'X' -- Archive logsand OPERATIONTYPE = '1' -- 1 = first log archive methodand TIMESTAMP(END_TIME) > CURRENT_TIMESTAMP - 10 DAYSGROUP BY DATE(CAST(START_TIME as TIMESTAMP)) , DBPARTITIONNUMORDER BY DATE DESC "查看过去24⼩时是否进⾏过备份[db2inst1@db2v9r7 ~]$]db2 "select substr(comment,1,30) as comment, timestamp(start_time) as start_time, timestamp(end_time) as end_time, substr(firstlog,1,25) as firstlog, substr(lastlog,1,25) as lastlog, seqnum, substr(location,1,50) as location from sysibmadm.db_history where operation = 'B' and timestamp(start_time) > current_timestamp - 24 hours and sqlcode is null "Dprop checkCapture side:db2 "SELECT SYNCHTIME, CURRENT TIMESTAMP AS CURRENT_TIMESTAMP FROM ASN.IBMSNAP_REGISTER WHERE GLOBAL_RECORD='Y' with ur"Apply side:db2 "select APPLY_QUAL, SET_NAME, SOURCE_ALIAS, TARGET_ALIAS, ACTIVATE, STATUS, LASTRUN, LASTSUCCESS, SYNCHTIME, SLEEP_MINUTES,REFRESH_TYPE from ASN.IBMSNAP_SUBS_SET"### 查看hadr 环境$ db2pd -alldbs -hadrdb2pd -db sfa -hadr#### 在 server端查询node 使⽤空间SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 /1024 AS DEC(8,2))as "Space in TB" ,SUM(num_files)as "Number of files" FROM occupancy GROUP BY node_name ORDER BY "Space in TB" DESCDB2跟oracle不⼀样，⽤户都是操作系统创建的⽽且⽤户没有所谓的默认表空间，默认临时表空间等等整个数据库的默认表空间就是数据库创建的默认表空间，usertablespace没有专门记录所有⽤户的视图，但是有个sysibmadm.privileges记录所有的⽤户权限所以可以认为它就是专门记录⽤户的视图。

DB2命令

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) 字符串。

db2 实战常用命令

db2 force application all –断开所有链接数据库的应用db2 list application-查看连接数据库的应用db2 bakup db ksdbs 备份数据库db2start db2stop启停数据库db2 connect reset断开所有链接scp get trans.ini -r back@10.10.9.160/home/back/bccbin \scp local_file remote_username@remote_ip:remote_folder或者scp local_file remote_username@remote_ip:remote_filescp -r ip:/db/dbhome/dbguard【1】db2top –d ksdbsdb2pd -d ksdbs -stat >stat.log 查看数据库状态（数据超大超详细）【1】find -type f | xargs dos2unix遍历格式转换【1】find . -name [A-Z]* -print查找当前目录下以大写字母命名的文件【1】>db2ckbkp 检查数据库的完整性>tee 命令用途--显示程序的输出并将其复制到一个文件中。

【1】db2 connect resetdb2 list directorydb2 list active databasesdb2 get db cfgdb2 get db cfg【1】归档日志db2 update db cfg for db_name using LOGRETAIN ON更改归档目录:db2 update db cfg for db_name using LOGARCHMETH1 "disk:/archive/db_name_db_log"在我重新连接数据库的时候提示：db2 connect to t_1 to mydbSQL1116N A connection to or activation of database "T_1" cannot be madebecause of BACKUP PENDING. SQLSTATE=57019网上找了n多最后才知道若修改数据库LOGRETAIN参数，从循环日志模式改为归档日志模式，则会导致数据库backup pending状态。

DB2 命令大全

db2 update db cfg for test using para_name para_value
10.删除数据库:
db2 drop db test
11.连接数据库
db2 connect to test
12.列出所有表空间的详细信息。
db2 list tablespaces show detail
CREATE TABLESPACE exoatbs32k IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32K MANAGED BY SYSTEM USING ('/home/exoa2/exoacontainer32k' ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT32K OVERHEAD 24.1 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;
30.生成数据库的定义
db2look -d db_alias -a -e -m -l -x -f -o db2look.sql
31.创建数据库
db2 create db test1
32.生成定义
db2 -tvf db2look.sql
33.导入数据库所有的数据
db2move db_alias import
13.查询数据:
db2 select * from tb1
14.删除数据:
db2 delete from tb1 where id=1
15.创建索引:
db2 create index idx1 on tb1(id);

DB2数据库的常用操作指令

DB2数据库的常用操作指令DB2是一种关系型数据库管理系统，其常用操作指令可以帮助用户在数据库中执行各种操作。

以下是DB2数据库的一些常用操作指令。

1.连接数据库：CONNECT TO database_name [USER username USING password]2.断开数据库连接：CONNECTRESET3.创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, ...)4.删除表：DROP TABLE table_name5.修改表结构：ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE datatype6.插入数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)7.更新数据：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition8.删除数据：DELETE FROM table_name WHERE condition9.查询数据：SELECT column1, column2, ... FROM table_name WHERE condition 10.创建索引：CREATE INDEX index_name ON table_name (column1, column2, ...)11.删除索引：DROP INDEX index_name12.创建视图：CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition13.修改视图：ALTER VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition14.删除视图：DROP VIEW view_name15.创建存储过程：CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)LANGUAGESQLBEGIN--存储过程代码END16.删除存储过程：DROP PROCEDURE procedure_name17.创建触发器：CREATE TRIGGER trigger_name BEFORE/AFTERINSERT/UPDATE/DELETE ON table_nameREFERENCING OLD ROW AS old NEW ROW AS newFOREACHROW--触发器代码18.删除触发器：DROP TRIGGER trigger_name19.提交事务：COMMIT20.回滚事务：ROLLBACK21.创建数据库：CREATE DATABASE database_name22.删除数据库：DROP DATABASE database_name以上是DB2数据库的一些常用操作指令，可以帮助用户在数据库中执行各种操作。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

第一.请问高手，如何查看tablespace里包含哪些表？select tabschema,tabname from syscat.tables where tbspace='TABLESPACEA' and type='T' 这里的TABLESPACEA具体情况定，如用USERSPACE1（158.222.2.110）第二.DB2 CLP从DOS进入db2cmddb2cmd在本地DB2数据库添加一个远程结点db2 catalog tcpip node xdzx1 remote 155.222.2.110 server 60064db2 catalog database xdzx1 at node xdzx1查看版本信息db2licm -l启动,停止db2数据库db2admin start db2admin stopdb2start db2stop执行存储过程db2 " call p01_acct_depacct_y('2007-4-15',?,?)"创建t01_acct_depacct_y表,in dms_data 代表数据存在dms_data命名空间 index in dms_idx代表索引创建在dms_idx里面create table t01_acct_depacct_y(record_date DATE not null,etl_date DATE,dep_acct_no CHAR(15) not null,curr_cd CHAR(3) not null,curr_iden CHAR(1) not null,bank_no CHAR(6) not null,summary_bank_no CHAR(6),xyz_no CHAR(2),dep_categ CHAR(1),acct_name VARCHAR(160),acct_short_name VARCHAR(60),busi_typ CHAR(3),assu_busi_typ CHAR(2),dep_term INT,open_date DATE,close_date DATE,cust_open_date DATE,dep_bal_tsum DEC(24,2),dep_bal_msum DEC(24,2),dep_bal_qsum DEC(24,2),dep_bal_qend_ysum DEC(24,2),year_dep_amt DEC(17,2),year_dep_cnt INT,year_oth_amt DEC(17,2),year_oth_cnt INT,year_d_sum DEC(17,2),year_c_sum DEC(17,2),year_d_cnt INT,year_c_cnt INT,constraint P_pk01_acct_saacct primary key (record_date, dep_acct_no, curr_cd, curr_iden))in dms_dataindex in dms_idx获取数据库中现有存储过程select substr(procschema,1,15),substr(procname,1,15) from syscat.procedures; select * from syscat.procedures where procschema='XXGL'获取当前时间select current time from (values 1) as test;select current date from (values 2) as test;select (current_date)- 2 year,(current_date)+ 1 day,current_timestamp + 1 hour from (values 1) test在列中查询以删除重复的记录我们有一个表，字段A有重复的记录，我们想要编写一个SQL 查询来让字段A作为主键。

在这样的情况下，我们需要一个删除查询来删除重复的记录，这样字段A才能具有惟一的数值。

要删除重复的数据行，可以使用如下的语句：DELETE FROM my_tableWHERE rowid NOT IN ( SELECT MAX(ROWID) FROM my_tableGROUP BY colA,colB,colC );在GROUP BY子句中，列出你的表中所有的字段，或者你认为应该成为主键字段的字段。

子查询可以得到这些分组的最大行id。

DELETE可以删除所有这些行id的数值。

将表中数据导出到文件(.del,.xls,.txt)方法一:export to <filename> of del select * from <table name>方法二:shell里面操作select * from <table name> > <filename>将文件中数据导入到表中:db2 "import from *.txt of del insert into 表"建表的时候可以设置列为自动增长的CREATE TABLE T1 (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE ),PRIMARY KEY ( ID) ) ;在DB2/400中如何检索一个表名中含有'.'号的表例如：如果直接strsql->SELECT * FROM ABIS/ABC.ABC系统将会报错：“Token . was not valid.“解决的方法是用双引号将'.'括起来SELECT * FROM ABIS/"ABC.ABC"即可。

如果库名也是这种情况，一样处理即可SELECT * FROM ABIS/ABC.ABC在AS400中不应该这样写的。

如果ABC是一个数据库那么你应该这样写SELECT * FROM ABIS/ABC如何提高IMPORT命令的性能1如果是在分区数据库的环境下，可以利用Buffered Insert来提高IMPORT的性能:在执行IMPORT命令前，要先用INSERT BUF参数重新绑定IMPORT命令对应的绑定文件db2uimpm.bnd 。

例如：db2 connect to 数据库名db2 bind db2uimpm.bnd blocking all insert bufdb2uimpm.bnd在..sqllib\bnd目录下。

2执行IMPORT命令时使用COMPOUND参数:例如：db2 connect to 数据库名db2 import from 数据文件名 of ixf modified by compound=100 insert into 表名上面的命令中IMPORT会在每100条记录而不是每条记录插入后等待返回的SQL执行结果。

3如果表中已有数据，将表的属性修改为APPEND MODE也可以加快IMPORT的性能。

添加实例instaince创建实例时的 UNIX 详细信息当使用 UNIX 操作系统时， db2icrt 命令具有下列可选的参数：-h 或 -?此参数用于显示此命令的帮助菜单。

-d此参数设置在确定问题期间要使用的调试方式。

-a AuthType 7 此参数指定该实例的认证类型。

有效的认证类型是 SERVER、SERVER_ENCRYPT 或 CLIENT。

如果未指定此参数，且如果安装了 DB2 Universal Database(TM)（DB2 UDB）（DB2 通用数据库）服务器，则缺省值为 SERVER。

否则，将它设置为 CLIENT。

注:该实例的认证类型适用于实例拥有的所有数据库。

7 在 UNIX 操作系统上，认证类型 DCE 不是有效的选项。

7 -u FencedID此参数是受防护的用户定义的函数（UDF）和存储过程执行期间所归属的用户。

如果安装了 DB2 UDB 客户机或“DB2 UDB 应用程序开发客户机”，则不需要此参数。

对于其它 DB2 UDB 产品，这是必需参数。

注:FencedID 不能是“root”或“bin”。

-p PortName此参数指定要使用的 TCP/IP 服务名称或端口号码。

对于实例中的每个数据库，将在实例的数据库配置文件中设置此值。

-s InstType允许创建不同类型的实例。

有效的的实例类型是：ese、wse、client 和 standalone。

示例：要为 DB2 UDB 服务器添加实例，您可以使用下列命令：db2icrt -u db2fenc1 db2inst1若只安装了 DB2 Connect 企业版，也可将该实例名用作 Fenced ID：db2icrt -u db2inst1 db2inst1要为 DB2 UDB 客户机添加实例，您可以使用下列命令：db2icrt db2inst1 -s client -u fencedID当要让一个工作站与其它数据库服务器连接，且该工作站上不需要本地数据库时，创建 DB2 UDB 客户机实例。

列出实例信息db2ilist获取当前会话所在的实例db2 get instance除去实例db2idrop <instance_name>更新实例配置db2iupdt InstName在 UNIX 上更新实例配置运行 db2iupdt 命令，并执行以下操作来更新指定的实例：替换实例所有者主目录下 sqllib 子目录中的文件。

若更改了节点类型，则会创建一个新的数据库管理器配置文件。

为此，可将现有的数据库管理器配置文件的相关值与新节点类型的缺省数据库管理器配置文件合并。

若创建了一个新的数据库管理器配置文件，则将旧文件备份到实例所有者主目录下的 sqllib 子目录的 backup 子目录中。

过程2 db2iupdt 命令可在 2 AIX 上的/usr/opt/db2_08_01/instance/ 2 目录中找到。

db2iupdt 命令可在HP-UX、Solaris Operating Environment 或Linux 上的/opt/IBM/db2/V8.1/instance/ 目录中找到。

按如下所示使用该命令：db2iupdt InstNameInstName 是实例所有者的登录名。

此命令还有其它可选的参数：-h 或 -?显示此命令的帮助菜单。