DB2基础知识(一).ppt

内部资料 注意保密1DB2DB2基础知识（一）基础知识（一）杭州中心杭州中心 数据业务室数据业务室 席晓勇席晓勇 2011年年7月月19日日内部资料 注意保密2内容提要内容提要一、一、DB2DB2概览概览二、二、DB2DB2的访问的访问三、三、DB2DB2数据库对象数据库对象四、学习指引四、学习指引五、总结五、总结内部资料 注意保密3学习目标学习目标nDB2的分类nDB2的访问以及服务器网络结构n理解基础数据库对象及对象间关系：表、表空间、分区、容器、索引n具备自学的基础内部资料 注意保密4一、一、DB2DB2概览概览n1.1 DB2是什么n1.2 DB2的版本n1.3 DB2平台支持n1.4 DB2在招商银行的应用内部资料 注意保密51.1 DB2是什么nIBM公司研制的关系型数据库系统 1969年IMS层次数据库诞生，为IBM第一代数据库也叫DB1。1983年IBM发布Database2（DB2）for MVS，第二代数据库诞生。2006年划时代产品DB2 V9出现，第一个混合模式（既有关系模型，又有直接支持XML的层次模型）。一、一、DB2DB2概览概览内部资料 注意保密61.2 DB2版本n 版本分类 小型企业 Express Edition 中型企业 Workgroup Server Edition 大型企业 Enterprise Server Edition（ESE）Enterprise Server Edition（ESE）with Data Partitioning Feature 免费版 Express-C 我我们目前使用的是目前使用的是DB2 V9.5一、一、DB2DB2基本概述基本概述内部资料 注意保密71.3 DB2平台支持n DB2覆盖多硬件和操作系统平台 大型机平台 DB2 for z/OS(DB2 for OS/390、DB2 for MVS/ESA、DB2 for VM/VSE)中型机平台 DB2 for System i，DB2已嵌入了i5/OS操作系统中 开放平台 DB2 for luw（Linux、UNIX、Windows）一、一、DB2DB2基本概述基本概述内部资料 注意保密81.4 DB2在招商银行的应用n 应用情况 主机 AS400 信用卡中心新一代390服务器 总行数据仓库核心服务器 分行数据集市服务器 开放平台多种业务系统一、一、DB2DB2基本概述基本概述内部资料 注意保密9二、二、DB2DB2的访问的访问n2.1 DB2的登录n2.2 SQL语句的运行n2.3 SQLSTATE&SQLCODEn2.4 服务器网络结构 内部资料 注意保密102.1 DB2的登录n远程登录到DB2服务器n通过本地客户端链接DB2服务器n节点目录、系统数据库目录和本地数据库目录间关系二、二、DB2DB2的访问的访问内部资料 注意保密112.1 DB2的登录n远程登录到DB2服务器：如分行数据集市开发机二、二、DB2DB2的访问的访问内部资料 注意保密122.1 DB2的登录n通过本地客户端链接DB2服务器安装本地DB2客户端程序配置本地Catalog（编目）Db2cmd-db2 connect to bidbdw user scvadm catalog tcpip node mynode remote 99.1.57.103 server 50000;catalog db bidbdw at node mynode;二、二、DB2DB2的访问的访问内部资料 注意保密13n节点目录、系统数据库目录和本地数据库目录间关系 二、二、DB2DB2的访问的访问内部资料 注意保密142.1 DB2的登录n通过本地客户端链接DB2服务器二、二、DB2DB2的访问的访问内部资料 注意保密152.2 SQL语句的执行二、二、DB2DB2的访问的访问内部资料 注意保密162.3 SQLSTATE&SQLCODEnSQL语句执行的状态码；n查询状态码的含义：db2“?42704”参考：http:/ 注意保密172.4 服务器网络结构二、二、DB2DB2的访问的访问终端服务器终端1 终端2 终端n网 络 TCP/IP终 端 主机m RS2322RS2322RS2322RS2322RS2322RS2322DB2服务器内部资料 注意保密182.4 服务器网络结构二、二、DB2DB2的访问的访问内部资料 注意保密19nDB2数据库体系系统实例数据库n数据库对象表、视图、索引、模式、存储过程、程序包等缓冲池、日志文件、表空间物理存储、表空间容器、目录、文件系统或裸设备三、三、DB2DB2数据库对象数据库对象内部资料 注意保密20n系统、实例、数据库 系统是DB2体系结构中最高一层，一个系统表示一个DB2的安装；实例也称数据库管理器，是管理数据的DB2代码，一个系统可包含多个彼此相互独立的实例，每个实例可以管理多个数据库。udb2icrt 创建实例udb2idrop 删除实例udb2start 启动实例udb2stop 停止实例 ucreate database 创建数据库 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密21n系统、实例、数据库 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密22nDB2层次下的对象层次 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密23n数据库CREATE DATABASE testdb(2)DFT_EXTENT_SZ 4(3)CATALOG TABLESPACE MANAGED BY DATABASE USING(4)(FILE C:CATCATALOG.DAT 2000,FILE D:CATCATALOG.DAT 2000)(5)EXTENTSIZE 8(6)PREFETCHSIZE 16(7)TEMPORARY TABLESPACE MANAGED BY SYSTEM USING(8)(C:TEMPTS,D:TEMPTS)(9)USER TABLESPACE MANAGED BY DATABASE USING(10)(FILE C:TSUSERTS.DAT 121)(11)EXTENTSIZE 24(12)PREFETCHSIZE 48 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密24n表空间 什么是表空什么是表空间？表空表空间是定是定义表和索引在数据表和索引在数据库内存内存储位置的位置的逻辑实体，体，所有所有 DB2 表和索引都表和索引都驻留在表空留在表空间中，数据中，数据库允允许对表和索引数据的物理表和索引数据的物理 存存储位置位置进行完全控制。行完全控制。有关数据有关数据库中表空中表空间的更多的更多详细情况可以使用以下命令来情况可以使用以下命令来获得：得：get snapshot for tablespaces list tablespaces 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密25n表空间物理存储结构 三、三、DB2DB2数据库对象数据库对象分区组是数据库内一个或多个数据库分区的组合或集合。表和索引在数据库内存储位置的逻辑实体。表空间的物理存储。容器可以是操作系统目录；大小已预先确定的文件；诸如未格式化的磁盘分区，裸设备等。内部资料 注意保密26n表空间物理存储结构 三、三、DB2DB2数据库对象数据库对象容器，扩展数据块（extent）、数据页（page）和表空间的关系内部资料 注意保密27n模式模式是什么？模式是什么？模式模式是用于数据是用于数据库中中创建的数据建的数据库对象的高象的高级限定符。它是数据限定符。它是数据库对象的集合，象的集合，这些些对象有表、象有表、视图、索引或触、索引或触发器等等。它器等等。它对数据数据库对象象进行了行了逻辑分分类。当您将数据。当您将数据组织成表成表时，将表和其它相关，将表和其它相关对象象组合在一起可能也很有好合在一起可能也很有好处。可以通可以通过使用使用 create schema 命令定命令定义模式来完成。有关模式的信模式来完成。有关模式的信息保存在您所息保存在您所连接的数据接的数据库的系的系统目目录表中。当表中。当创建其它建其它对象象时，可以将它可以将它们放置在放置在该模式内。模式内。三、三、DB2DB2数据库对象数据库对象内部资料 注意保密28n系统模式 系统模式集是和每个数据库一起创建的，并且它们被放置到 SYSCATSPACE 表空间中 SYSIBM：基本系统目录 建议不要进行直接访问 SYSCAT：PUBLIC 被授予该模式的 SELECT 权限 对只读视图编目 这是获取目录信息的推荐方式 SYSSTAT：可更新的目录视图 影响优化器 SYSFUN：用户定义的函数 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密29模式如何在 DB2 中使用模式？使用模式来全限定表或其它对象名，如下所示：schemaname.tablename 多张表可以有同一名称，但是要有不同的模式名 表 user1.staff 不同于 user2.staff创建对象时指定模式例如用户 tjadm显式地指定对象的模式名：create table DWAINE.table1(c1 int,c2 int)隐式地指定对象的模式名：create table t2(c1 int)-table tjadm.t2 created注：隐式地指定对象的模式名，用户 需要IMPLICT_SCHEMA 特权 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密30n表设计考虑选择合适的数据类型根据业务要求选择合适类型，避免类型转换根据需求选择合适长度容易字段内容为数字，建议用整形不用CHARCHAR和VARCHAR的选择大对象数据类型合理使用选择合适的约束类型NOT NULL的约束，NOT NULL WITH DEFAULT 唯一约束主键约束表检查约束外建（参考）约束 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密31n表设计考虑生成列应用例：C3 DOUBLE GENERATED ALWAYS AS（C1+C2）自动编号和标识列应用Id int generated by default as identity Dn int not null generated always as identity(start with 1,increment by 1)-DB2自动生成，用户无法输入制定值 三、三、DB2DB2数据库对象数据库对象内部资料 注意保密32n表设计考虑分区键选择三、三、DB2DB2数据库对象数据库对象内部资料 注意保密33分区键选择 三、三、DB2DB2数据库对象数据库对象案例：CREATE TABLE TABLE_SAM()DISTRIBUTE BY HASH(.);旧语法：partitioning key()内部资料 注意保密34n视图视图是什么？视图是从一个或几个基本表导出的表，也可从其他视图导出。为什么要使用视图？视图允许不同的用户或应用程序以不同的方式查看同一数据。它不仅使数据更易于访问，而且可以利用它限制某些用户能够查看或更新的行和列。某一用户可以定义一个或多个视图，同时经授权一个视图也可为多个用户共享，视图是一个虚表，而基表是一个实表。虚表只有定义，没有对应的物理数据；而实表既有定义，又有对应的物理数据。视图一经定义就可和基表一样被查询、被删除，还可用来定义新的视图。更新（增、删、改）视图的操作有一定限制。三、三、DB2DB2数据库对象数据库对象内部资料 注意保密35n视图创建视图CREATE VIEW DWA_FAT.V_ACCACTDTA AS SELECT*FROM DWA_FAT.ACCACTDTA WHERE ACT_TYP=C删除视图DROP VIEW DWA_FAT.V_ACCACTDTA说明不能修改视图视图不能创建索引如果删除了某视图基于的表或另一视图，则该视图在数据库虽然有定义但不起作用三、三、DB2DB2数据库对象数据库对象内部资料 注意保密36n视图创建可更新视图和只读视图视图的 SELECT 语句决定视图是只读的还是可更新的通常，如果视图的行可映射至基表的行，则视图是可更新的。创建可更新视图的规则是复杂的，并且取决于查询的定义。例如，使用 VALUES、DISTINCT 或 JOIN 特性的视图是不可更新的。查看 SYSCAT.VIEWS 的 READONLY 列，您可以很容易地确定视图是否为可更新的：Y 表示它是只读的，N 表示它不是只读的。带检查选项的视图 WITH CHECK OPTION 定义视图就是告诉 DB2 检查使用该视图的语句是否满足视图的条件。CREATE VIEW NONFICTIONBOOKS AS SELECT*FROM BOOKS WHERE BOOKTYPE=N WITH CHECK OPTION；INSERT INTO NONFICTIONBOOKS VALUES(.,F)三、三、DB2DB2数据库对象数据库对象内部资料 注意保密37n索引索引是什么？索引是表中一列或多列的键值的有序列表为什么要使用索引？确保一列或多列中值的唯一性。提高对表进行查询的性能。三、三、DB2DB2数据库对象数据库对象内部资料 注意保密38n索引使用注意事项根据查询所使用的列建立索引，select根据条件语句中的谓词的选择读创建索引，where子句中所用的列避免在建有索引的列上使用函数，在那些需要被排序的列上创建索引，order by，distinct，group by要避免某些排序，只要有可能，就通过使用 CREATE UNIQUE INDEX 语句定义主键和唯一键。三、三、DB2DB2数据库对象数据库对象内部资料 注意保密39n索引使用注意事项（续）节省索引维护成本和空间a)避免创建的索引是这些列上其它索引键的部分键。例如，如果列 a、b 和 c 上有索引，则列 a 和 b 上的第二个索引一般用处不大。b)不在所有列上任意创建索引。不必要的索引不仅使用空间，而且严重影响插入性能c)使用下列一般规则来确定将为表定义的索引的典型数目对于联机事务处理（OLTP）环境，创建一个或两个索引 对于只读查询环境，可以创建 5 个以上索引 对于混合查询和 OLTP 环境，可以创建 2 到 5 个索引要提高多列索引的连接性能，如果第一个键列有多项选择，则使用最常用“=”（等值连接）谓词指定的那一列。三、三、DB2DB2数据库对象数据库对象内部资料 注意保密40CREATE TABLE DWA_FAT.A80_GRPDIM(GRP_NBR CHAR(2)NOT NULL,GRP_NAM VARCHAR(30),PMP_ETP CHAR(3),MNT_USR CHAR(6),MNT_TIM TIMESTAMP,CONSTRAINT P_KEY_1 PRIMARY KEY(GRP_NBR)IN CURDTA_TBS INDEX IN CURIDX_TBSDISTRIBUTE BY(GRP_NBR);CREATE INDEX DWA_FAT.GRPDIM_IDX ON DWA_FAT.A80_GRPDIM(GRP_NAM);三、三、DB2DB2存储相关的数据库对象存储相关的数据库对象内部资料 注意保密41参考参考Partitioning and Clustering GuideData Servers,Databases,and Database Objects GuideSQL ReferenceDB2 SQL Cookbook-Graeme BirchallTable Spaces and Buffer PoolsTuning Database Performance实例讲解DB2中的表空间表空间说明-ITPUBDB2历史回溯DB2 UDB事务日志概述DB2中的数据类型Partitiong for performance in an ebusiness intelligence world内部资料 注意保密42参考参考-续续Relational Database Index Design and the OptimizersDB2开发人员指南数据恢复与高可用性指南与参考内部资料 注意保密43参考书目参考书目内部资料 注意保密44