DB2入门培训讲义.ppt

1、第1页，共49页。认识认识 DB2DB2认识 DB2第2页，共49页。DB2的版本 DB2 UDBl V7、V8、V9l PE(Personal Edition)l EE(Enterprise Edition)l EEE(Enterprise-Extended Edition)其他IBM 主机平台如AS/400上的DB2与DB2 UDB有很大不同，老版本的主机DB2甚至不支持SQL。第3页，共49页。DB2在招行的应用情况l AS/400 DB2l RS/6000 UDB V5 V7 V8 V9l 数据仓库l 开发用：DB2 UDB PE V8 For Windows XP/2K第4页，共49

2、页。DB2 UDB vs SQL Server/Sybasel SQL Server/Sybase是个黑盒子l DB2 是个相对透明的黑盒子l DB2 所支持的SQL语句更完整l DB2 对数据仓库所做的扩展支持非常好，如多分区，MDC技术等。l DB2 具有平台优势和体系优势，对高端应用有更好的支持。第5页，共49页。DB2 UDB的Create Table语句第6页，共49页。SQL Server 的Create TableCREATE TABLE database_name.owner.|owner.table_name(|column_name AS computed_column_e

3、xpression|,.n)ON filegroup|DEFAULT TEXTIMAGE_ON filegroup|DEFAULT 第7页，共49页。l Windows 下的安装、使用 仅安装客户端 DB2 Connect 安装服务器端 DB2 PE Server+DB2 Connectl RS/6000下的安装、使用 如RS/6000上已安装DB2，则只要在登录用户的profile中引用db2inst1的profile或手工添加相应环境变量，即可使用DB2，前提是DBA已授权此用户足够权限。DB2安装、使用第8页，共49页。l 配置助手 l 控制中心l 命令行工具 命令窗口命令窗口、命令行处

4、理器l 信息中心l 开发中心l 健康中心使用 DB2工具-windows第9页，共49页。n 实例实例 Instancen 数据库数据库 Databasen 容器容器 Containern 表空间表空间 Tablespacen 扩展区扩展区 Extendn 表表 Tablen 页页 PageDB2 UDB中一些基本概念第10页，共49页。n Viewn SQL存储过程存储过程 n 触发器触发器n 事务处理事务处理n 索引索引DB2 UDB中的标准SQL设施第11页，共49页。n模式是一个标识符，如用户 ID，它帮助分组表和其他数据库对象。模式可以归个人拥有，拥有者可以控制对数据以及其中的对象的

5、存取。模式也可以是数据库中的对象。它可以在创建模式中的第一个对象时 自动创建。这样的对象可以是任何可以由模式名限定的对象，如表、索引、视图、程序包、单值类型、函数或触发器。若要自动创建模式，则您必须 拥有 IMPLICIT_SCHEMA 权限，也可以隐式地创建模式。模式名用作两部分对象名的第一部分。创建一个对象时，可将其分配给 特定模式。若不指定模式，则它被分配给缺省模式，缺省模式通常是创建该对象的人员的用户 ID。名称的第二部分是对象名。例如，名为 Smith 的用户可以 有一个名为 SMITH.PAYROLL 的表。n简单的比喻：假如DB等同于公司，模式相当一个部门名称。DB2 数据库中的

6、模式名-SCHEMA第12页，共49页。DB2 专用存储器n CURRENT DATEn USERn CURRENT TIMESTAMPn CURRENT TIMEn CURRENT TIMEZONEn CURRENT SERVER 相当于相当于SQL Server中的全局变量中的全局变量第13页，共49页。n 常用的常用的 DB2 DB2 指令指令n CREATE/DROPn LIST/DESCRIBEn FORCEn GET/UPDATEn IMPORT/EXPORT/LOADn BIND/REBINDn REORG/RUNSTATSn 第14页，共49页。n 常用的常用的 DB2 DB2

7、 系统指令系统指令n db2adminn db2advin db2batchn db2cc/db2cmdn db2cfexp/db2cfimpn db2start/db2stopn 第15页，共49页。数据库分区与表空间数据库分区与表空间数据库分区与表空间第16页，共49页。l 数据库分区l 一个数据库可以建立一个或多个分区（即节点），数据可以根据表所定义的分区键通过HASH算法存放于各个分区中。l 作为表格的分区键，建议使用高基数字段（拥有多个不同的值），这样可以使数据被均匀的分布，可以利用节点组中所有的节点资源来执行操作。也可以指定多个字段同时作为分区键来满足要求。二、数据库分区与表空间第

8、17页，共49页。l 表空间的定义DB2是以表空间来定义数据库中存放数据的实体磁盘空间。一个数据库中至少需要具备3个表空间，分别是：lSYSCATSPACE：存放数据库中的所有系统表lTEMPSPACE1：存放DB2在执行SQL指令时所产生的临时数据表lUSERSPACE1：存放用户所建立的表二、数据库分区与表空间第18页，共49页。l 表空间的分类根据存储的数据种类区分l 规则表空间：存放系统表格过用户建立的表格的数据与索引。l 临时表空间：存放在执行SQL指令时产生的临时数据。l 大型表空间：存放LONG VARCHAR与LOB类型的字段数据。根据管理方式区分l 数据库管理空间：DMS，是

9、由DB2直接格式化与管理的空间l 系统管理空间：SMS，是由DB2通过操作系统所管理的空间二、数据库分区与表空间第19页，共49页。数据库中的对象数据库中的对象三、数据库中的对象第20页，共49页。l 对象名称：DB2 的所有对象，都有其对象名称，DB2 会将他全部转成大写，但如果把对象名称用双引号括起来，则就区分大小写。带引号的对象名称也可以包含SQL的保留字，但并不提倡。三、数据库中的对象第21页，共49页。l 表格 注意指定表空间和分区键 可增加字段，新增或删除限制 可更改VARCHAR类型的长度 可用LIKE定义同样的一张表，但除了字段类型和默认值外其他属性不继承l 索引l 视图 只读

10、视图/可更新视图l 别名数据库中的对象第22页，共49页。l 限制 NOT NULL Constraints Column Defaults Unique Constraints Check Constraints Primary Key Constraints Foreign Key Constraintsl 触发器前触发器/后触发器l 实例化查询表（MQT）：REFRESH DEFERRED REFRESH IMMEDIATE三、数据库中的对象第23页，共49页。数据的锁数据的锁四、数据的锁第24页，共49页。l LOCK TABLE 指令l LOCK TABLE DB2.TABLENAM

11、E IN SHARE MODEl LOCK TABLE DB2.TABLENAME IN EXCLUSIVE MODEl 锁定的对象l 数据库/表空间/表格/记录l 锁定的模式（强度）l 写锁定，包括 X、W、NW、Z 等l 读锁定，包括 S、NS、U 等四、数据的锁第25页，共49页。l 锁定等待l LOCK WAITl 锁定超时l LOCKTIMEOUTl 锁定升级l LOCKLIST：定义数据库中预留多少空间存放锁定信息l MAXLOCKS：定义每个事务可以使用多少LOCKLIST内存区四、数据的锁第26页，共49页。数据库系统效能数据库系统效能五、数据库系统效能第27页，共49页。l

12、DB2 Explain 工具l db2expln执行该语句可以查看Package中的SQL指令执行计划。这是从unix或是doc窗口执行的指令，如下：Db2expln-d dbname-c vicky-p cursor-o expln.outl dynexpln显示SQL指令的执行计划，如：dynexpln-d sample-s“sql指令”-o expln.outDynexpln-d sample-f sql文件-o expln.outl Visual Explain从控制中心或命令行中心执行的explain sql 五、数据库系统效能第28页，共49页。l 几张关键的系统表l SYSCAT

13、.TABLESl SYSCAT.COLUMNSl SYSCAT.COLDISTl SYSCAT.INDEXESl SYSCAT.TABLESPACESl RUNSTATS l 索引l 建立索引的方法l where条件的索引的顺序l 索引的副作用五、数据库系统效能第29页，共49页。五、数据库系统效能l REORGl MQT（物化视图）l DATA INITIALLY DEFERREDl REFRESH DEFERREDl REFRESH IMMEDIATEl ENABLE/DISABLE QUERY OPTIMIZATIONCREATE TABLE sch.mqt1 AS(select nam

14、e,location,salary from sch.tab1 a,sch.tab2where a.dept=b.deptnumb and a.salary 20000)DATA INITIALLY DEFERRED REFRESH DEFERRED;REFRESH TABLE sch.mqt1;第30页，共49页。l程序包与BINDl嵌入式SQLlCLIlJDBClSQLJDB2客户端程序开发第31页，共49页。实际应用实际应用六、实际应用第32页，共49页。l SQL存储过程l CREATE PROCEDUREl DROP PROCEDUREl IN/OUT 参数l 调用 CALLl 错误

15、陷阱l 循环语句l COMMIT WORKl BEGIN/END六、实际应用第33页，共49页。lSQL存储过程lC存储过程/函数lJava存储过程l其他：COBOL、OLE存储过程 第34页，共49页。lStore Procedure BuilderlDW：通过一个C函数写成的Function向调试文件写日志存储过程的调试方法 SQL存储过程的调试 第35页，共49页。SQLCODE，SQLSTATE为什么要有Condition Handling?固定写法：Declare SQLSTATE CHAR(5)DEFAULT 00000;Declare SQLCODE INT DEFAULT 0;

16、必须要这么写才能得到SQL Statement执行情况，且只能写在存储过程的开头(Outermost Scope)为什么？为什么？主机风格主机风格 Condition Handling Declare continue handler for sqlstate 42774 begin end SQL存储过程的异常处理方法 Condition Handling第36页，共49页。l Create procdure xx.xx(in xx,out xx)specific specific-name Language SQL begin xxx end DB2允许定义同名但参数不同的存储过程，因此在

17、删除存储过程时使用specific-name删除。假如调用存储过程的参数错误，db2只说找不到对应的存储过程，而不会说参数错误。这一点类似面向对象的参数重载。DB2 SQL存储过程的specific specific-name第37页，共49页。l 静态SQLl 静态SQL(static SQL)语句嵌入到应用程序中，在应用运行之前，所有的嵌入静态SQL语句必须经过预编译并被绑定到程序包(package)中。l DB2编译这些语句时，将基于编译和绑定这些语句时的系统表中的统计信息和数据库参数为每一个静态SQL语句生成一个访问计划(access plan)。l 这些访问计划在运行时永远被使用；这

18、些访问计划不会改变，除非程序包被重新绑定。EXEC SQL SELECT d.deptname,count(e.empno),avg(e.salary)FROM department d left outer join employee e on(e.workdept=d.deptno)WHERE e.salary=:salary_baseline:salary_baseline OR e.salary IS NULL GROUP BY d.deptname;六、实际应用第38页，共49页。l 动态SQLl 动态SQL(dynamic SQL)语句在程序运行时被prepare和执行。l DB2

19、运行动态SQL语句时，将基于当前的系统表中的统计信息和数据库参数为该语句生成一个访问计划(access plan)。l 该访问计划在运行时可能会不断变化。stmt=“select empno,salary from employee where empno=?”;EXEC SQL PREPARE s1 FROM:stmt;EXEC SQL EXECUTE s1;六、实际应用第39页，共49页。六、实际应用l MERGE SQL UPSERTl 对目标表进行更新插入的过程，当存在满足条件的记录时就更新目标表，当不存在满足条件的记录时就向目标表插入新记录。MERGE INTO account AS

20、 a USING(SELECT id,sum(balance)sum_balance from transaction GROUP BY id)AS t ON a.id=t.idWHEN MATCHED THEN UPDATE SET balance=a.balance+t.sum_balanceWHEN NOT MATCHED THEN INSERT(id,balance)=(t.id,t.sum_balance);第40页，共49页。六、实际应用l 权限设置l 过程/函数GRANT EXECUTE ON PROCEDURE/FUNCTIOIN PROCNAME/FUNCNAME TO US

21、ER/GROUP USERNAME/GROUPNAME;l 表GRANT ALL/DELETE/UPDATE/SELECT/INSERT ON TABLE TABNAME TO USER/GROUP USERNAME/GROUPNAME;l SEQUENCEGRANT USAGE ON SEQUENCE SEQNAME TO USER/GROUP USERNAME/GROUPNAME;l 系统权限查看表 SYSCAT.TABAUTH,SYSCAT.TBSPACEAUTH,SYSCAT.SCHEMAAUTH 第41页，共49页。DB2 UDB的数据类型第42页，共49页。DB2 UDB的数据类型

22、l 日期运算：如何求出每月末的日期？第43页，共49页。DB2 UDB的数据类型l 日期运算：values(date(2006-01-31)+1 month)values(date(2006-01-31)+1 year)l 数值运算 decimal的小数位l Null值 任何有Null参与的表达式，结果都是Null 特别注意打印中间结果或调试语句时l 第44页，共49页。存储过程-临时表的使用l 必须要先定义系统临时表空间或用户临时表空间才能使用临时表l 定义临时表不能使用模式名，其模式名固定为session。l 使用完成后最好把临时表Drop掉。第45页，共49页。Get diagnostics 使用说明lSQL Server取到SQL语句所影响的行数的方式？(rowcount)lDB2取到SQL语句所影响的行数的方式 Get diagnostics GET DIAGNOSTICS v_Iupdate_Cnt=ROW_COUNT;第46页，共49页。完整的存储完整的存储过程实例讲解过程实例讲解第47页，共49页。Question?第48页，共49页。演讲完毕，谢谢观看！第49页，共49页。