DB2数据库教程


DB2DB2DB2DB2DB2DB2DB2DB2 现有的数据库类型 Master Network interconnect Parallel query planning and dispatch Segment Severs (processing and storage) SQL queries & MapReduce programs DB2产品家族 � DB2 DB2 DB2 DB2 的各个版本从低到高依次为的各个版本从低到高依次为的各个版本从低到高依次为的各个版本从低到高依次为:::: DB2 Everyplace DB2 Everyplace DB2 Everyplace DB2 Everyplace � DB2 ExpressDB2 ExpressDB2 ExpressDB2 Express � DB2 WorkGroupDB2 WorkGroupDB2 WorkGroupDB2 WorkGroup � DB2 EnterpriseDB2 EnterpriseDB2 EnterpriseDB2 Enterprise � DB2 Data WareHouse DB2 Data WareHouse DB2 Data WareHouse DB2 Data WareHouse EnterpriseEnterpriseEnterpriseEnterprise � 每个版本都包含前一个版本的所有特性每个版本都包含前一个版本的所有特性每个版本都包含前一个版本的所有特性每个版本都包含前一个版本的所有特性 和功能,并添加了新特性和新功能。和功能,并添加了新特性和新功能。和功能,并添加了新特性和新功能。和功能,并添加了新特性和新功能。 DB2 DB2 DB2 DB2 在在在在 AIXAIXAIXAIX、、、、Windows Windows Windows Windows 和和和和 UNIX UNIX UNIX UNIX 、、、、Linux Linux Linux Linux 平台上的代码大约有平台上的代码大约有平台上的代码大约有平台上的代码大约有90%90%90%90%是相是相是相是相 同的,另有同的,另有同的,另有同的,另有10%10%10%10%的专用代码,用于使数的专用代码,用于使数的专用代码,用于使数的专用代码,用于使数 据库和底层操作系统紧密集成。据库和底层操作系统紧密集成。据库和底层操作系统紧密集成。据库和底层操作系统紧密集成。 DB2体系结构 DB2存储结构 � 存储三角形 � � 在一个 DB2 DB2 DB2 DB2 数据库中,数据和索引的存储是在四个不同级别上定义和控制的。 为了支持分区数据库,其中有一个名为分区组(partition groupspartition groupspartition groupspartition groups)的抽象层。分区 组是由一个数据库中的一个或多个数据库分区组成的组或集合。当创建一个表空间时 ,它被指定到一个分区组,并且只能创建在该分区组所包含的那些数据库分区上。每 个表空间必须有一个或多个为表空间定义物理存储的容器。容器可以是一个操作系统 目录、具有预定大小的文件、未格式化的硬盘之类的原始设备、硬盘上的一个分区或 者一个逻辑卷。 � SMS和DMS � SMSSMSSMSSMS:系统管理的空间(System-Managed SpaceSystem-Managed SpaceSystem-Managed SpaceSystem-Managed Space) � 在这里,由操作系统的文件系统管理器分配和管理空间 。在 DB2 9 DB2 9 DB2 9 DB2 9 之前,如果不带任何参数创建数据库或表空间 ,就会导致所有表空间作为 SMS SMS SMS SMS 对象创建。 � DMSDMSDMSDMS:数据库管理的空间(Database-Managed SpaceDatabase-Managed SpaceDatabase-Managed SpaceDatabase-Managed Space ) � 在这里,由数据库管理程序控制存储空间。这种表空间 本质上是一种特殊用途的文件系统实现,可以最好地满足 数据库管理程序的需要。 � •DMS 的自动存储(Automatic Storage With DMS) � •From DB2 V8.2.2 IBM Software Group | DB2 Information Management Software DMS、SMS 与自动存储 特性 SMSSMSSMSSMSDMSDMSDMSDMS 自动存储 是否分段 Striping 是 是 是 默认类型 Version 8 无 Version 9 对象管理 操作系统 DB2 DB2 空间分配 按需增长/收缩 预先分配;大小可以收缩和增长,但是需要 DBA 干预。 预先分配;可以自 动增长。 管理的简便性 最好;很少需要调 优,甚至不需要 好,但是需要一些调优(例如, EXTENTSIZE PREFETCHSIZE) 最好;很少需要调 优,甚至不需要 性能 非常好 最好;可通过利用原始容器多获得 5% 到 10% 的收益 最好;但是,可以 不使用原始容器 表空间最大大小 64GB(4K 页面) 2TB(4K 页面) 16TB(32k) 2TB(4K 页面) IBM DB2 Information Management IBM DB2 Information Management IBM DB2 Information Management IBM DB2 Information Management Software | 2007 © 2007 IBM Corporation 整 初始大小 IBM Software Group | DB2 Information Management Software DMS 与自动存储 特性 容器的创建 容器大小的调 容器的修改 管理的简便性 非自动存储 必须在创建表空间时显式地提供容器。 在默认情况下,表空间大小的自动调整是关闭的 (AUTORESIZE NO)。 不能使用 INITIALSIZE 子句指定表空间的初始大 小。 可以使用 ALTER TABLESPACE 语句(ADD、 DROP、BEGIN NEW STRIPE SET 等等)执行容 器操作。 可以使用重定向的恢复操作重新定义与表空间相关 联的容器。 自动存储 不能在创建表空间时提供容器;它们将由 DB2 自动地分配。 在默认情况下,表空间大小的自动调整是打开 的(AUTORESIZE YES)。 使用 INITIALSIZE 子句指定表空间的初始大 小。 不能执行容器操作,因为由 DB2 控制空间管 理。 不能使用重定向的恢复操作重新定义与表空间 相关联的容器,因为由 DB2 控制空间管理。 在 DB2 9 中,数据库在创建时将启用自动存储,除非 DBA 显式地覆盖这个设置。 对于在最初没有启用自动存储的数据库,不能在以后启用这个特性;相反亦然 IBM DB2 Information Management IBM DB2 Information Management IBM DB2 Information Management IBM DB2 Information Management Software | 2007 © 2007 IBM Corporation 通过界面创建数据库 �   1、启动 DB2 Control Center。点击 Start > All Programs > IBM DB2 > General Administration Tools 并选择 Control Center。当提示选择 Control Center 视图时,点击 OK 选择默认视图(Advanced)。 �   2、点击 All Databases(如图 5 所示),然后点击 Create New Database。Create Your Own Database 向导打开。 �   图 5. 从 Control Center 创建新数据库  �    通过界面创建数据库 �   3、在 Create Your Own Database 向导中,指定数据库名和数据库目录的位置。 输入 HelloWld 作为数据库名。指定 C:\ 作为默认路径。输入 hwld 作为数据库别名。在 Comment 字段中输入任何描述性的注释。 �   4、选中 Enable Database for XML。XML 列只能在 UTF-8 代码集(Unicode)数据 库中使用。所以要在数据库中使用 XML,就必须创建 Unicode 数据库。 �   5、不需要指定任何其他数据库选项,所以选择 Summary 面板,然后点击 Show Command 来查看创建这个数据库所用的 DB2 命令。 �   6、因为在这个数据库中启用了 XML,所以使用 CODESET UTF-8。点击 Close。 如果想将命令保存到文件中,供以后参考或重用,那么点击 Save 而不是 Close。 �   7、在 Create New Database 窗口中,点击 Finish 并等待向导完成操作。 �   在 DB2 创建数据库时,它还创建以下东西: �   1、在指定的驱动器或路径上创建必要的数据库目录 �   2、创建一组默认的表空间,包括 SYSCAT、TEMPORARY 和 USER 表空间 �   3、在编目表空间中创建一组系统编目表和视图 �   4、创建数据库配置(DB CFG)文件并设置默认值 �   5、为数据库恢复日志文件创建空间并分配它 �   6、为数据库(如 CLI)和命令行处理器创建的几个应用程序 通过界面创建数据库 �   使用 DB2 Control Center DB2 Control Center DB2 Control Center DB2 Control Center 检查创建的数据库 �   在 Control Center 左边的面板中,找到并展开 All Systems 和 All Databases。看 看是否能够找到刚才创建的 HELLOWLD 数据库。All Systems 提供了数据库对象视图 ,这些对象依次根据机器、实例和数据库进行分组。在这个机器上只有一个实例,DB2 。All Databases 提供了这个机器上的所有数据库的快速查看。 �   图 6. 查看新数据库 通过命令创建数据库 create database bitest AUTOMATIC STORAGE YES ON ‘d:\bitest’ ALIAS bitest USING CODESET UTF-8 TERRITORY CN COLLATE USING SYSTEM --排序类型 PAGESIZE 4096 --默认页大小 WITH ‘BI测试数据库’--注释 默认的数据库 � create database create database create database create database 命令创建三 个默认的表空间: � SYSCATSPACE SYSCATSPACE SYSCATSPACE SYSCATSPACE � 用于系统编目表。SYSCATSPACE 不能被删除。 � TEMPSPACE1 TEMPSPACE1 TEMPSPACE1 TEMPSPACE1 � 用于系统创建的临时表。当创建了另一个临时表空间时,可删除 TEMPSPACE1 表 空间。 � USERSPACE1 USERSPACE1 USERSPACE1 USERSPACE1 � 用于用户创建对象的默认表空间。当创建了另一个用户创建的表空间时,可删除 USERSPACE1 表空间。 创建 TABLESPACE DB2内置数据类型 数字型 � 整数(Integer):SMALLINTSMALLINTSMALLINTSMALLINT、、、、INTEGER INTEGER INTEGER INTEGER 和和和和 BIGINT BIGINT BIGINT BIGINT 用于存储整型数字。用于存储整型数字。用于存储整型数字。用于存储整型数字。 � SMALLINT SMALLINT SMALLINT SMALLINT 可以用可以用可以用可以用 2 2 2 2 个字节存储从个字节存储从个字节存储从个字节存储从 -32768 -32768 -32768 -32768 到到到到 32767 32767 32767 32767 的整数。的整数。的整数。的整数。 � INTEGER INTEGER INTEGER INTEGER 可以用可以用可以用可以用 4 4 4 4 个字节存储从个字节存储从个字节存储从个字节存储从 -2147483648 -2147483648 -2147483648 -2147483648 到到到到 2147483647 2147483647 2147483647 2147483647 的整数。的整数。的整数。的整数。 � BIGINT BIGINT BIGINT BIGINT 可以用可以用可以用可以用 8 8 8 8 个字节存储从个字节存储从个字节存储从个字节存储从 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808 到到到到 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807 的整数。的整数。的整数。的整数。 � 小数(Decimal):DECIMAL DECIMAL DECIMAL DECIMAL 用于存储带小数部分的数字。要定义这种数据类型,必须指定用于存储带小数部分的数字。要定义这种数据类型,必须指定用于存储带小数部分的数字。要定义这种数据类型,必须指定用于存储带小数部分的数字。要定义这种数据类型,必须指定精度 精度 精度 精度 ((((pppp )(指明数字的总位数)和)(指明数字的总位数)和)(指明数字的总位数)和)(指明数字的总位数)和小数位 小数位 小数位 小数位 ((((ssss)(指明小数点右边的数字位数)。)(指明小数点右边的数字位数)。)(指明小数点右边的数字位数)。)(指明小数点右边的数字位数)。 � 数据库中所需的存储量取决于精度,可以用公式数据库中所需的存储量取决于精度,可以用公式数据库中所需的存储量取决于精度,可以用公式数据库中所需的存储量取决于精度,可以用公式 p/2 +1 p/2 +1 p/2 +1 p/2 +1 计算。因此,计算。因此,计算。因此,计算。因此,DECIMAL(10,2) DECIMAL(10,2) DECIMAL(10,2) DECIMAL(10,2) 需要需要需要需要 10/2 + 1 10/2 + 1 10/2 + 1 10/2 + 1 或或或或 6 6 6 6 个字节。个字节。个字节。个字节。 � 浮点数(Floating point):REAL REAL REAL REAL 和和和和 DOUBLE DOUBLE DOUBLE DOUBLE 用于存储数的近似值。用于存储数的近似值。用于存储数的近似值。用于存储数的近似值。 � 定义定义定义定义 REAL REAL REAL REAL 时可将长度定义在时可将长度定义在时可将长度定义在时可将长度定义在 1 1 1 1 到到到到 24 24 24 24 之间,而且需要之间,而且需要之间,而且需要之间,而且需要 4 4 4 4 个字节来存储。个字节来存储。个字节来存储。个字节来存储。 � 可将可将可将可将 DOUBLE DOUBLE DOUBLE DOUBLE 的位数长度定义在的位数长度定义在的位数长度定义在的位数长度定义在 25 25 25 25 到到到到 53 53 53 53 之间,而且需要之间,而且需要之间,而且需要之间,而且需要 8 8 8 8 个字节来存储。个字节来存储。个字节来存储。个字节来存储。FLOAT FLOAT FLOAT FLOAT 可用作可用作可用作可用作 REAL REAL REAL REAL 或或或或 DOUBLE DOUBLE DOUBLE DOUBLE 同义词。同义词。同义词。同义词。 字符串型 � 单字节字符串单字节字符串单字节字符串单字节字符串 � CHAR::::CHAR CHAR CHAR CHAR 或或或或 CHARACTER CHARACTER CHARACTER CHARACTER 用于存储定长字符串(最多用于存储定长字符串(最多用于存储定长字符串(最多用于存储定长字符串(最多 254 254 254 254 个字节)。个字节)。个字节)。个字节)。 � VARCHAR::::VARCHAR VARCHAR VARCHAR VARCHAR 用于存储变长字符串。用于存储变长字符串。用于存储变长字符串。用于存储变长字符串。 � VARCHAR VARCHAR VARCHAR VARCHAR 列的最大长度为列的最大长度为列的最大长度为列的最大长度为 32672 32672 32672 32672 个字节。在数据库中,个字节。在数据库中,个字节。在数据库中,个字节。在数据库中,VARCHAR VARCHAR VARCHAR VARCHAR 数据只数据只数据只数据只 占用必需的空间。占用必需的空间。占用必需的空间。占用必需的空间。 � 双字节字符串双字节字符串双字节字符串双字节字符串 � GRAPHIC::::GRAPHIC GRAPHIC GRAPHIC GRAPHIC 用于存储定长双字节字符串。用于存储定长双字节字符串。用于存储定长双字节字符串。用于存储定长双字节字符串。GRAPHIC GRAPHIC GRAPHIC GRAPHIC 列的最大长度是列的最大长度是列的最大长度是列的最大长度是 127 127 127 127 个字符。个字符。个字符。个字符。 � VARGRAPHIC::::VARGRAPHIC VARGRAPHIC VARGRAPHIC VARGRAPHIC 用于存储变长双字节字符串。用于存储变长双字节字符串。用于存储变长双字节字符串。用于存储变长双字节字符串。VARGRAPHIC VARGRAPHIC VARGRAPHIC VARGRAPHIC 列的最大列的最大列的最大列的最大 长度是长度是长度是长度是 16336 16336 16336 16336 个字符。个字符。个字符。个字符。 日期时间型 � DATE � TIME � TIMESTAMP � TIMESTAMP TIMESTAMP TIMESTAMP TIMESTAMP 数据类型只有一种格式数据类型只有一种格式数据类型只有一种格式数据类型只有一种格式 � YYYY-MM-DD-HH.MM.SS.NNNNNNYYYY-MM-DD-HH.MM.SS.NNNNNNYYYY-MM-DD-HH.MM.SS.NNNNNNYYYY-MM-DD-HH.MM.SS.NNNNNN。。。。 依据数据库国家代码依据数据库国家代码LOC各地自定义 HH.MM.SSHH.MM.SSHH.MM.SSHH.MM.SSYYYY-MM-DDYYYY-MM-DDYYYY-MM-DDYYYY-MM-DDJIS日本工业标准 HH.MM.SSHH.MM.SSHH.MM.SSHH.MM.SSDD.MM.YYYYDD.MM.YYYYDD.MM.YYYYDD.MM.YYYYEURIBM欧洲标准 HH:MM AMHH:MM AMHH:MM AMHH:MM AM或PMPMPMPMMM/DD/YYYYMM/DD/YYYYMM/DD/YYYYMM/DD/YYYYUSAIBM美国标准 HH-MM-SSHH-MM-SSHH-MM-SSHH-MM-SSYYYY-MM-DDYYYY-MM-DDYYYY-MM-DDYYYY-MM-DDISO国际标准化组织 时间格式日期格式缩写格式名 DB2数据组织方案 � 数据分区特性(Data Partitioning Feature,DPF):数据库分区 � 用于将数据均匀地分布在数据库分区上(以启用查询内并行性并平衡每个数据库分区上的负用于将数据均匀地分布在数据库分区上(以启用查询内并行性并平衡每个数据库分区上的负用于将数据均匀地分布在数据库分区上(以启用查询内并行性并平衡每个数据库分区上的负用于将数据均匀地分布在数据库分区上(以启用查询内并行性并平衡每个数据库分区上的负 载)载)载)载) � 分布键(分布键(分布键(分布键(distribution keydistribution keydistribution keydistribution key)))) � CREATE table CREATE table CREATE table CREATE table ………… DISTRIBUTE BY HASH(distribution key) DISTRIBUTE BY HASH(distribution key) DISTRIBUTE BY HASH(distribution key) DISTRIBUTE BY HASH(distribution key) � 多维聚类(Multidimensional Clustering,MDC) :多维集群 � 用于对同一表扩展数据块中在多个维上具有类似值的行进行分组用于对同一表扩展数据块中在多个维上具有类似值的行进行分组用于对同一表扩展数据块中在多个维上具有类似值的行进行分组用于对同一表扩展数据块中在多个维上具有类似值的行进行分组 � CREATE table CREATE table CREATE table CREATE table ………… ORGANIZE BY DIMENSION (distribution key) ORGANIZE BY DIMENSION (distribution key) ORGANIZE BY DIMENSION (distribution key) ORGANIZE BY DIMENSION (distribution key) � 表分区(Table Partitioning,TP) :表分区 � 用于对同一个数据分区中具有类似单维值的行进行分组用于对同一个数据分区中具有类似单维值的行进行分组用于对同一个数据分区中具有类似单维值的行进行分组用于对同一个数据分区中具有类似单维值的行进行分组 � 分区键分区键分区键分区键 ((((partition keypartition keypartition keypartition key)))) � CREATE table CREATE table CREATE table CREATE table ………… PARTITION BY RANGE(partition key) PARTITION BY RANGE(partition key) PARTITION BY RANGE(partition key) PARTITION BY RANGE(partition key) � (PART p1(PART p1(PART p1(PART p1………… � ,PART P2,PART P2,PART P2,PART P2………… � …………)))) 数据库分区: Database partitioning Feature � 一种数据组织方案,即,表数据根据该表中的一个或一种数据组织方案,即,表数据根据该表中的一个或一种数据组织方案,即,表数据根据该表中的一个或一种数据组织方案,即,表数据根据该表中的一个或 多个分布键列中的散列值以及使用的数据库分区的分发映多个分布键列中的散列值以及使用的数据库分区的分发映多个分布键列中的散列值以及使用的数据库分区的分发映多个分布键列中的散列值以及使用的数据库分区的分发映 射分布到多个数据库分区中。给定表的数据根据射分布到多个数据库分区中。给定表的数据根据射分布到多个数据库分区中。给定表的数据根据射分布到多个数据库分区中。给定表的数据根据 CREATE CREATE CREATE CREATE TABLE TABLE TABLE TABLE 语句的语句的语句的语句的 DISTRIBUTE BY HASH DISTRIBUTE BY HASH DISTRIBUTE BY HASH DISTRIBUTE BY HASH 子句中指定的内容进子句中指定的内容进子句中指定的内容进子句中指定的内容进 行分布。行分布。行分布。行分布。 � 每个数据库分区有它自己的一组计算资源,包括每个数据库分区有它自己的一组计算资源,包括每个数据库分区有它自己的一组计算资源,包括每个数据库分区有它自己的一组计算资源,包括 CPU CPU CPU CPU 和存储。在和存储。在和存储。在和存储。在 DPF DPF DPF DPF 环境中,根据环境中,根据环境中,根据环境中,根据 CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE 语句中指定语句中指定语句中指定语句中指定 的分区键,表中的每个行被分布到一个分区上。当处理一的分区键,表中的每个行被分布到一个分区上。当处理一的分区键,表中的每个行被分布到一个分区上。当处理一的分区键,表中的每个行被分布到一个分区上。当处理一 个查询时,请求也相应地被划分成多个部分,以便让各个个查询时,请求也相应地被划分成多个部分,以便让各个个查询时,请求也相应地被划分成多个部分,以便让各个个查询时,请求也相应地被划分成多个部分,以便让各个 数据库分区各自处理其负责的那些行。实际上,数据库分区各自处理其负责的那些行。实际上,数据库分区各自处理其负责的那些行。实际上,数据库分区各自处理其负责的那些行。实际上,DPF DPF DPF DPF 是一是一是一是一 种可伸缩特性。种可伸缩特性。种可伸缩特性。种可伸缩特性。DPF DPF DPF DPF 可以通过增加数据库分区来提高处理可以通过增加数据库分区来提高处理可以通过增加数据库分区来提高处理可以通过增加数据库分区来提高处理 能力,因此,随着表的增长,仍然可以保持较高的查询性能力,因此,随着表的增长,仍然可以保持较高的查询性能力,因此,随着表的增长,仍然可以保持较高的查询性能力,因此,随着表的增长,仍然可以保持较高的查询性 能。这种能力常常被称作使用能。这种能力常常被称作使用能。这种能力常常被称作使用能。这种能力常常被称作使用 DB2 DB2 DB2 DB2 的无共享架构提供线性的无共享架构提供线性的无共享架构提供线性的无共享架构提供线性 的可伸缩性。的可伸缩性。的可伸缩性。的可伸缩性。 多维集群: Multi dimensional Clustering � 一个表,其数据按一个表,其数据按一个表,其数据按一个表,其数据按 ORGANIZE BY DIMENSIONS ORGANIZE BY DIMENSIONS ORGANIZE BY DIMENSIONS ORGANIZE BY DIMENSIONS 子句子句子句子句 中指定的一个或多个维或者集群键以物理方式组织成块。中指定的一个或多个维或者集群键以物理方式组织成块。中指定的一个或多个维或者集群键以物理方式组织成块。中指定的一个或多个维或者集群键以物理方式组织成块。 � MDC MDC MDC MDC 是在是在是在是在 DB2 Version 8 DB2 Version 8 DB2 Version 8 DB2 Version 8 中引入的,通过它可以在物中引入的,通过它可以在物中引入的,通过它可以在物中引入的,通过它可以在物 理上将在多个维上具有类似值的行聚合在一起放在磁盘上理上将在多个维上具有类似值的行聚合在一起放在磁盘上理上将在多个维上具有类似值的行聚合在一起放在磁盘上理上将在多个维上具有类似值的行聚合在一起放在磁盘上 。这种聚合能为常见分析性查询提供高效的。这种聚合能为常见分析性查询提供高效的。这种聚合能为常见分析性查询提供高效的。这种聚合能为常见分析性查询提供高效的 I/OI/OI/OI/O。例如,。例如,。例如,。例如, 对于对于对于对于 Product=carProduct=carProduct=carProduct=car,,,,Region=EastRegion=EastRegion=EastRegion=East,并且,并且,并且,并且 SaleMonthYear = SaleMonthYear = SaleMonthYear = SaleMonthYear = Jan09 Jan09 Jan09 Jan09 的所有行,可以将它们存储在相同的存储位置,即的所有行,可以将它们存储在相同的存储位置,即的所有行,可以将它们存储在相同的存储位置,即的所有行,可以将它们存储在相同的存储位置,即 所谓的块(所谓的块(所谓的块(所谓的块(blockblockblockblock)。在)。在)。在)。在 CREATE table CREATE table CREATE table CREATE table 语句中定义维的时语句中定义维的时语句中定义维的时语句中定义维的时 候,就为每种值的组合预留了存储空间。实际上,候,就为每种值的组合预留了存储空间。实际上,候,就为每种值的组合预留了存储空间。实际上,候,就为每种值的组合预留了存储空间。实际上,MDC MDC MDC MDC 是是是是 一个能最大化查询性能的特性,对于数据仓库中常用的查一个能最大化查询性能的特性,对于数据仓库中常用的查一个能最大化查询性能的特性,对于数据仓库中常用的查一个能最大化查询性能的特性,对于数据仓库中常用的查 询更是如此。这包括需要根据几个列中的值的组合选择行询更是如此。这包括需要根据几个列中的值的组合选择行询更是如此。这包括需要根据几个列中的值的组合选择行询更是如此。这包括需要根据几个列中的值的组合选择行 的查询。例如,的查询。例如,的查询。例如,的查询。例如,DATE is between "Jan-01-2004" and DATE is between "Jan-01-2004" and DATE is between "Jan-01-2004" and DATE is between "Jan-01-2004" and "Feb-01-2005" AND Country IS NOT "United States" "Feb-01-2005" AND Country IS NOT "United States" "Feb-01-2005" AND Country IS NOT "United States" "Feb-01-2005" AND Country IS NOT "United States" AND Product="Cell Phones"AND Product="Cell Phones"AND Product="Cell Phones"AND Product="Cell Phones"。。。。 表分区: Table partitioning � 一种数据组织方案,即,表数据根据该表中一个或多个分区列中的值分 布到多个数据分区中。根据CREATE TABLE 语句的 PARTITION BY RANGE 子句中指定的内容,给定表的数据被划分到多个存储对象中。这 些存储对象可以在不同的表空间中。 � TP 支持按照一个维将一个表分区成多个数据分区。一种常见的设计是 为每个月的数据创建一个数据分区。 � 通过 TP,用户可以手动地定义每个数据分区,包括将被包括到那个分 区的值的范围。 � 每个 TP 分区是一个单独的数据库对象(不同于其他作为单个数据库对 象的表)。因此,TP 支持为 TP 表附加和卸除数据分区。卸除的分区成 为一个常规表。而且,必要时可以将每个数据分区放在它自己的表空间中 。 � 实际上,TP 不同于其他特性的优势在于为表添加或删除大量数据这个 方面,即转入和转出。 DB2 特性简要对比 数据移动数据移动数据移动数据移动 ———————— 通过添加和删除通过添加和删除通过添加和删除通过添加和删除 整个数据分区,可以增加和删除整个数据分区,可以增加和删除整个数据分区,可以增加和删除整个数据分区,可以增加和删除 大量数据大量数据大量数据大量数据 将所有行放在同一个将所有行放在同一个将所有行放在同一个将所有行放在同一个 数据分区的一个指定数据分区的一个指定数据分区的一个指定数据分区的一个指定 范围的维中范围的维中范围的维中范围的维中 TPTPTPTP 查询性能查询性能查询性能查询性能 ———————— 组织数据的方式组织数据的方式组织数据的方式组织数据的方式 有利于获得更快的检索速度,对有利于获得更快的检索速度,对有利于获得更快的检索速度,对有利于获得更快的检索速度,对 于由多个谓词指定范围的查询尤于由多个谓词指定范围的查询尤于由多个谓词指定范围的查询尤于由多个谓词指定范围的查询尤 其有效其有效其有效其有效 将在多个维上具有近将在多个维上具有近将在多个维上具有近将在多个维上具有近 似值的行放在表中相似值的行放在表中相似值的行放在表中相似值的行放在表中相 同的物理位置,即所同的物理位置,即所同的物理位置,即所同的物理位置,即所 谓的块谓的块谓的块谓的块 MDCMDCMDCMDC 可伸缩性可伸缩性可伸缩性可伸缩性 ———————— 随着数据库的增随着数据库的增随着数据库的增随着数据库的增 长增加计算资源(也就是数据库长增加计算资源(也就是数据库长增加计算资源(也就是数据库长增加计算资源(也就是数据库 分区)分区)分区)分区) 将行均匀地分布在多将行均匀地分布在多将行均匀地分布在多将行均匀地分布在多 个数据库分区上个数据库分区上个数据库分区上个数据库分区上 DPFDPFDPFDPF 优点特性如何组织数据特性 事实表拥有适合使用 DB2 分区特性的特征 在事实表中,常常是每天都添加 新数据。通常每月或每个季度删 除过时的数据 这种类型的表:周期性 地添加大量数据,然后 在数据到期后又删除大 量数据 TP 事实表(以及通常所说的数据仓 库)是为支持这种类型的查询而 设计的 结果集返回在多个维上 具有近似值的行的查询 MDC 事实表是最大的数据库表。它们 常常包含数亿行数据,有时候甚 至包含数千亿行数据 大型表 ———————— 大到无法 仅依靠单独一组 CPU 和 I/O 通道来处理 DPF 事实表的特征适合的表特征特性 表设计的经验法则 选择一个基于时间的列。定义与每次 转出的数据量相符的分区 TP ———————— 用作表分区键的列和分 区的数量 一种典型的设计是选择一个表示日期 的列,再加上 0 到 3 个其他列,例 如 region 和 product_type MDC ———————— 用作 MDC 维的列 首选是具有很多不同值的列DPF ———————— 用作分布键的列 经验法则分区特性设计决定 经验分享 � DPF: � 设计表结构时一定要设置分布键,且优先选择变化量比 较大的字段, 切忌选择时间、 地域字段; � 如果是事实表,可以选择两到三个字段结合做为分布键 ; � 有主键时,主键要包含在分布键内; 练习 � 请在电脑上安装好DB2 � 创建一个数据库BITEST � 创建一个表空间MK01 � 按照图所示创建如下的表,使用DPF和TP � 在表中插入一些测试数据 练习 : VARCHAR(16) : VARCHAR(5) : VARCHAR(2) : VARCHAR(16) : TIMESTAMP : VARCHAR(512) : VARCHAR(1) : DECIMAL(18,0) : INTEGER : VARCHAR(16) : DECIMAL(18,0) : DECIMAL(18,0) : TIMESTAMP : VARCHAR(32) : VARCHAR(32) : VARCHAR(40) : VARCHAR(32) : VARCHAR(80) : VARCHAR(200) : VARCHAR(256) : VARCHAR(128) : VARCHAR(15) : VARCHAR(16) (FK) : VARCHAR(16) (FK) : VARCHAR(16) (FK) : VARCHAR(16) (FK) : INTEGER TD_CI_INDI_CUST_D CUST_ID: VARCHAR(16) REGION_CODE: VARCHAR(5) GENDER: VARCHAR(2) BIRTHDAY_TYPE: VARCHAR(16) BIRTHDAY: TIMESTAMP PERSN_HOBBY: VARCHAR(512) IS_MAIN: VARCHAR(1) FMLY_INCOME: DECIMAL(18,0) FMLY_MEMBER_CNT: INTEGER BELNG_TRADE: VARCHAR(16) PRD_FEE: DECIMAL(18,0) SERV_FEE: DECIMAL(18,0) MEMBER_VALID: TIMESTAMP CARD_TYPE: VARCHAR(32) TITLE: VARCHAR(32) UNIT_ZIP_CODE: VARCHAR(40) FAX: VARCHAR(32) BIRTH_AREA: VARCHAR(80) CONSM_HABIT: VARCHAR(200) MAIL_ADDR: VARCHAR(256) SPECIAL_NAME: VARCHAR(128) PRD_MAGR: VARCHAR(15) HOBBY_HOBBY_ID: VARCHAR(16) (FK) INCOME_RANGE_ID: VARCHAR(16) (FK) DUTY_LVL_ID: VARCHAR(16) (FK) EDU_LVL_ID: VARCHAR(16) (FK) DEAL_DATE: INTEGER : VARCHAR(16) : INTEGER : VARCHAR(5) : VARCHAR(20) : VARCHAR(16) : VARCHAR(16) : DATE : INTEGER : DATE : VARCHAR(8) : VARCHAR(100) : VARCHAR(2) : INTEGER : VARCHAR(8) : VARCHAR(32) : VARCHAR(32) : VARCHAR(10) : VARCHAR(1) : VARCHAR(1) : VARCHAR(1) : VARCHAR(16) : VARCHAR(200) : VARCHAR(32) TM_SC_USER_BASE_D SUBS_ID: VARCHAR(16) STATIS_DATE: INTEGER REGION_CODE: VARCHAR(5) PHONE_NO: VARCHAR(20) CUST_ID: VARCHAR(16) BRAND: VARCHAR(16) CHECKIN_DATE: DATE : INTEGER OFF_NET_DATE: DATE START_DATE: VARCHAR(8) CUST_NAME: VARCHAR(100) GENDER: VARCHAR(2) AGE: INTEGER BIRTH_YEAR_MON: VARCHAR(8) : VARCHAR(32) CERT_NUMBER: VARCHAR(32) CUST_MAGR: VARCHAR(10) IS_ON_NET_USER: VARCHAR(1) IS_NEW_USER: VARCHAR(1) IS_OFF_NET_USER: VARCHAR(1) MOBILE_TYPE: VARCHAR(16) CUST_ADDR: VARCHAR(200) CREAT_CHNL: VARCHAR(32) GSM : INTEGER : VARCHAR(16) : VARCHAR(20) : VARCHAR(5) : VARCHAR(2) : VARCHAR(3) : VARCHAR(5) : VARCHAR(33) : VARCHAR(2) : VARCHAR(10) IP : VARCHAR(4) : VARCHAR(8) V : VARCHAR(2) : VARCHAR(10) : VARCHAR(10) : VARCHAR(12) : VARCHAR(20) : VARCHAR(20) : VARCHAR(20) : INTEGER : INTEGER : INTEGER : INTEGER : INT : INT : INTEGER : INT : INT : INTEGER : INTEGER : INT : INT : INT : INTEGER : VARCHAR(1) TM_LS_GSM_VOC_D STATIS_DATE: INTEGER SUBS_ID: VARCHAR(16) PHONE_NO: VARCHAR(20) REGION_CODE: VARCHAR(5) CALL_PERIOD_ID: VARCHAR(2) CDR_TYPE: VARCHAR(3) CALL_TYPE: VARCHAR(10) OPP_TYPE: VARCHAR(33) TOLL_TYPE: VARCHAR(2) ROAM_TYPE: VARCHAR(10) IP_TYPE_ID: VARCHAR(4) CDR_SERV_TYPE: VARCHAR(8) V : VARCHAR(2) : VARCHAR(10) OPP_CARR: VARCHAR(10) OPP_AREA_CD: VARCHAR(12) : VARCHAR(20) : VARCHAR(20) ROAM_AREA_ID: VARCHAR(20) : INTEGER : INTEGER : INTEGER : INTEGER : INT : INT : INTEGER : INT : INT : INTEGER : INTEGER : INT : INT : INT : INTEGER IS_CHRG_USER: VARCHAR(1) : INTEGER : VARCHAR(16) : VARCHAR(20) : VARCHAR(2) : VARCHAR(5) : VARCHAR(3) : VARCHAR(5) : VARCHAR(10) : VARCHAR(33) : VARCHAR(12) : VARCHAR(8) : INTEGER : INTEGER : INT : INT : INT : INT TM_LS_GEN_SMS_D STATIS_DATE: INTEGER SUBS_ID: VARCHAR(16) PHONE_NO: VARCHAR(20) CALL_PERIOD_ID: VARCHAR(2) REGION_CODE: VARCHAR(5) CDR_TYPE: VARCHAR(3) CALL_TYPE: VARCHAR(10) ROAM_TYPE: VARCHAR(10) OPP_CARR: VARCHAR(33) OPP_AREA_CD: VARCHAR(12) CDR_SERV_TYPE: VARCHAR(8) : INTEGER : INTEGER : INT : INT : INT : INT DB2权限 � SYSADM � 具有管理实例的完整特权,还可访问底层数据库中的数据。 � SYSCTRL 和 SYSMAINT � 拥有管理实例、其数据库和数据库对象的特定特权。这些权限不含 访问数据的权限 。例如,像 'SELECT * FROM mytable' 或 'DELETE FROM mytable' 这样的语句 是不允许的。 � DBADM � 拥有在特定数据库上执行管理任务的特权。还具有数据库的完整数据访问权限。 � LOAD � 拥有对指定数据库运行加载实用工具的特权。 DB2权限功能 SELECT语句结构 SELECT子句 [INTO子句] --仅在函数、存储过程等中使用 FROM子句 [WHERE子句] [GROUP BY子句] [HAVING子句] [ORDER BY子句] [FETCH FIRST子句] [UNION子句] SELECT子句 � SELECT CUST_ID FROM TD_CI_INDI_CUST_D � 只选择需要的字段,任何多余的字段都将增加I/O的开销, 降低性能 � 一般情况下禁止使用SELECT *,第一性能最差,第二当 表中字段发生变化时容易出问题 � DISTINCT:当重复值较少时使用DISTINCT,重复值较多 时使用GROUP BY Select distinct subs_id from a; Select cust_type from a group by cust_type WHERE子句 � 查询条件尽可能的详细罗列 � 能够使用分区条件的要显式罗列 � 多表关联时,如果每个表都有相同的分区条件,要将每个 表的分区条件写出 FROM a INNER JOIN b ON a.stat_date=b.stat_date and a.subs_id=b.subs_id WHERE a.stat_date=‘2010-3-1’ and b.stat_date=‘2010-3- 1’ � 索引字段的查询条件不能使用函数处理 � IN、NOT IN、EXIST、NOT EXIST GROUP BY子句 � GROUP BY 地区,品牌 � GROUP BY 地区,品牌 WITH ROLLUP � GROUP BY 地区,品牌 WITH CUBE � SELECT COUNT(SUBS_ID) as SUBS_CNT, GROUPING(地区) as C1, GROUPING(品牌) as C2, FROM TM_SC_USER_BASE_D GROUP BY 地区,品牌 WITH CUBE 在数据汇总过程中经常需要用到ROLLUP和CUBE计算,由 此来进行逐级汇总是效率最高的,GROUPING函数可以帮 助纪录那些是明细数据,哪些是汇总数据 ORDER BY子句 � ORDER BY 地区,品牌 ASC/DESC 注意排序的字段顺序 HAVING子句 � GROUP BY 地区,品牌 HAVING COUNT(SUBS_ID)>10000 � 用于对GROUP BY后的结果进行条件过滤,可以使用聚集 函数 � 在GROUP BY运算完成后执行 FETCH FIRST 子句 � 设置查询结果返回前若干条记录 � SELECT SUBS_ID FROM TM_SC_USER_BASE_D FETCH FIRST 20 ROW ONLY 等同于其他数据库的TOP 20和ROWNUM<21 UNION子句 � 两个结果集的列必须一致 � 前后两个结果集列的数据类型不一致时,以第一个结果集 为准 � UNION 合并时排重 � UNION ALL 合并时不排重 � 一般情况下使用UNION ALL,通过SQL的逻辑保证无重复 避免使用UNION,排重对性能开销较大 CASE子句的使用 � CASE WHEN 条件1 THEN 值1 WHEN 条件2 THEN 值2 ELSE 值3 END CASE WHEN加上GROUP BY可以实现行列表互转 表连接 � INNER JOIN � LEFT OUTER JOIN � RIGHT OUTER JOIN � FULL OUTER JOIN � CROSS JOIN 表连接的优化策略 � 如果WHERE条件中有条件能够过滤单表的大批数据,尽量在JOIN之 前把表的数据量降到最小,虽然很多时候数据库在解析SQL时会对此 进行自动优化,但不少时候由于各种原因会使SQL解析器忽略 � SELECT t1.COL2,t2.COL2 FROM a t1 LEFT OUTER JOIN b t2 ON t1.COL1=t2.COL1 WHERE t1.COL1=1 � SELECT t1.COL2,t2.COL2 FROM (SELECT COL1,COL2 FROM a WHERE COL1=1) t1 LEFT OUTER JOIN b t2 ON t1.COL1=t2.COL1 表连接的优化策略 � 某些情况下使用中间表进行GROUP BY来代替JOIN SELECT t1. SUBS_ID,t1.通话费,t2.短信费 FROM a t1 LEFT OUTER JOIN b t2 ON t1.SUBS_ID=t2.SUBS_ID INSERT INTO c SELECT t1. SUBS_ID,t1.通话费,0 from a t1; INSERT INTO c SELECT t2. SUBS_ID,0,t2.短信费 from b t2; SELECT SUBS_ID,SUM(通话费),SUM(短信费 ) FROM c GROUP BY SUBS_ID 表连接的优化策略 � 使用索引列作为关联条件可以较大提高JOIN性能,但是很 多时候JOIN的条件不是那么简单的 � 分布键在DB2中不可作为索引使用,需另外创建索引 CREATE INDEX idx_a_SUBS_ID ON a (SUBS_ID); CREATE INDEX idx_b_SUBS_ID ON b (SUBS_ID); SELECT t1. SUBS_ID,t1.通话费,t2.短信费 FROM a t1 LEFT OUTER JOIN b t2 ON t1.SUBS_ID=t2.SUBS_ID INSERT 语句 � INSERT a (COL1,COL2) VALUES(‘1’,’2’); � INSERT a ( COL1, COL2 ) SELECT COL1, COL2 FROM b UPDATE语句结构 � 单表UPDATE � UPDATE tablename SET Col1name=value1,Col2name=value2 WHERE子句 � 多表的关联 UPDATE 方法 db2的update语法不支持update table1 set t1.col1=t2.value1 from table1 t1,table2 t2 where 的写法,但是可以通过如下方法解决 update table1 t1 set t1.col1=(select t2.col1 from table2 t2 where 例: update a t1 set t1.col2 = (select t2.col2 from b t2 where t2.col1=t1.col1); 使用 SELECT代替 UPDATE � 需要在大数据量表中做大批量数据更新时,由于数据仓库的数据分布原理,性 能会非常低,因此尽量不使用 � 使用临时表、SELECT和INSERT语句代替UPDATE语句 UPDATE a SET COL2=100 WHERE COL1>100000000 改写为 CREATE TABLE b (COL1 DECIMAL(10),COL2 DECIMAL(10)); INSERT INTO b (COL1,COL2) SELECT COL1,COL2 FROM a; 清空表a ALTER TABLE a ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE; IMPORT FROM /DEV/NULL OF DEL REPLACE INTO a; 插入表a INSERT INTO a (COL1,COL2) SELECT COL1, CASE WHEN COL1>100000000 THEN 100 ELSE COL2 END AS COL2 FROM b DELETE语句 � DELETE FROM tablename WHERE子句 使用SELECT/INSERT语句代替大表的大批数据DELETE,单个表分区数 据删除等特殊方式删除数据除外 CREATE TABLE b (COL1 DECIMAL(10),COL2 DECIMAL(10)); INSERT INTO b (COL1,COL2) SELECT COL1,COL2 FROM a WHERE COL1>100000000; 清空表a ALTER TABLE a ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE; IMPORT FROM /目录/空文件 OF DEL REPLACE INTO a; 插入表a INSERT INTO a (COL1,COL2) SELECT COL1,COL2 FROM b 练习 � 在数据库上练习使用各种SELECT子句 � 在数据库上练习使用UPDATE语句 � 在数据库上练习使用INSERT语句 � 在数据库上练习使用DELETE语句 � 将表中的数据量增加致100万条,演练表连接,更新,删 除操作及其优化策略 数据移动实用程序 � EXPORT � IMPORT � LOAD � 支持的文件格式支持的文件格式支持的文件格式支持的文件格式 � 非定界的或固定长度的 ASCII (ASC): � 顾名思义,这种文件类型包含固定长度的顾名思义,这种文件类型包含固定长度的顾名思义,这种文件类型包含固定长度的顾名思义,这种文件类型包含固定长度的 ASCII ASCII ASCII ASCII 数据,这些数据与列数据相对应。每个数据,这些数据与列数据相对应。每个数据,这些数据与列数据相对应。每个数据,这些数据与列数据相对应。每个 ASC ASC ASC ASC 文件文件文件文件 都是一个都是一个都是一个都是一个 ASCII ASCII ASCII ASCII 字符流,其中包含按行和列排列的数据值。数据库中的行以行分界符分隔开来,这字符流,其中包含按行和列排列的数据值。数据库中的行以行分界符分隔开来,这字符流,其中包含按行和列排列的数据值。数据库中的行以行分界符分隔开来,这字符流,其中包含按行和列排列的数据值。数据库中的行以行分界符分隔开来,这 里假定行分界符就是换行字符。里假定行分界符就是换行字符。里假定行分界符就是换行字符。里假定行分界符就是换行字符。 � 定界的 ASCII (DEL): � 这种文件格式常被各种数据库管理器用于数据交换。它包含这种文件格式常被各种数据库管理器用于数据交换。它包含这种文件格式常被各种数据库管理器用于数据交换。它包含这种文件格式常被各种数据库管理器用于数据交换。它包含 ASCII ASCII ASCII ASCII 数据,并使用特殊的字符分界符数据,并使用特殊的字符分界符数据,并使用特殊的字符分界符数据,并使用特殊的字符分界符 来分隔列值。数据流中的行则以作为行分界符的换行字符分隔开来。来分隔列值。数据流中的行则以作为行分界符的换行字符分隔开来。来分隔列值。数据流中的行则以作为行分界符的换行字符分隔开来。来分隔列值。数据流中的行则以作为行分界符的换行字符分隔开来。 � PC 版本的 Integrated Exchange Format (PC/IXF): � 这是数据库表的结构化描述。这种文件格式不仅可以用于导入数据,还可以用于创建目标数据库中这是数据库表的结构化描述。这种文件格式不仅可以用于导入数据,还可以用于创建目标数据库中这是数据库表的结构化描述。这种文件格式不仅可以用于导入数据,还可以用于创建目标数据库中这是数据库表的结构化描述。这种文件格式不仅可以用于导入数据,还可以用于创建目标数据库中 之前不存在的表。之前不存在的表。之前不存在的表。之前不存在的表。 � 工作表格式 (WSF): � 存储在这种格式中的数据可以在工作表中解释。这种格式只能用于导出和导入。存储在这种格式中的数据可以在工作表中解释。这种格式只能用于导出和导入。存储在这种格式中的数据可以在工作表中解释。这种格式只能用于导出和导入。存储在这种格式中的数据可以在工作表中解释。这种格式只能用于导出和导入。 � 游标: 游标是在查询中声明的。它只能用作一个装载操作的输入。游标是在查询中声明的。它只能用作一个装载操作的输入。游标是在查询中声明的。它只能用作一个装载操作的输入。游标是在查询中声明的。它只能用作一个装载操作的输入。 EXPORT � EXPORT TO file_name OF file_type � MODIFIED BY file_type_modifiers � MESSAGES message_file � select_statement � file_type_modifiers:文件类型修饰符 � chardelx � 指定 x 作为新的单字符字符串分界符。默认值是一个双引号(")。 � coldelx � 指定 x 作为新的单字符列分界符。默认值是一个逗号(,)。 � codepage=x � 指定 x (一个 ASCII 字符串)作为输出数据的新的码页(code page)。在导出操 作期间,字符数据被从应用程序码页转换成这种码页。 � timestampformat="x" � x 是源表中时间戳的格式。 � 示例   � EXPORT TO myfile.del OF DEL  MODIFIED BY chardel! coldel@ codepage=1208 timestampformat="yyyy.mm.dd hh:mm tt"   MESSAGES msg.out   SELECT * FROM schedule �   前面的命令按照以下方式将 SCHEDULE 表中的数据 导出到 DEL 格式的文件中:   字符串以感叹号(!)括起来。   列之间以 @ 符号分隔开。   将字符串转换成码页 1208(UTF-8)。   SCHEDULE 表中用户定义的时间戳的格式为 yyyy.mm.dd hh:mm tt。 IMPORT � IMPORT FROM file_name IMPORT FROM file_name IMPORT FROM file_name IMPORT FROM file_name OF file_type OF file_type OF file_type OF file_type MODIFIED BY MODIFIED BY MODIFIED BY MODIFIED BY WARNINGCOUNT 10 WARNINGCOUNT 10 WARNINGCOUNT 10 WARNINGCOUNT 10 RESTARTCOUNT 55 RESTARTCOUNT 55 RESTARTCOUNT 55 RESTARTCOUNT 55 ROWCOUNT 10000 ROWCOUNT 10000 ROWCOUNT 10000 ROWCOUNT 10000 COMMITCOUNT 10000 COMMITCOUNT 10000 COMMITCOUNT 10000 COMMITCOUNT 10000 METHOD L/P/N() METHOD L/P/N() METHOD L/P/N() METHOD L/P/N() MESSAGES message_file MESSAGES message_file MESSAGES message_file MESSAGES message_file [INSERT|INSERT_UPDATE|REPLACE|REPLACE_CRE[INSERT|INSERT_UPDATE|REPLACE|REPLACE_CRE[INSERT|INSERT_UPDATE|REPLACE|REPLACE_CRE[INSERT|INSERT_UPDATE|REPLACE|REPLACE_CRE ATE|CREATE] ATE|CREATE] ATE|CREATE] ATE|CREATE] INTO target_table_name (col1,col2,INTO target_table_name (col1,col2,INTO target_table_name (col1,col2,INTO target_table_name (col1,col2,……………………)))) � INSERT::::选项将导入的数据插入表中。目标表必须已经存在。选项将导入的数据插入表中。目标表必须已经存在。选项将导入的数据插入表中。目标表必须已经存在。选项将导入的数据插入表中。目标表必须已经存在。 � INSERT_UPDATE::::将数据插入表中,或者更新表中具有匹配主键的行。目标表必将数据插入表中,或者更新表中具有匹配主键的行。目标表必将数据插入表中,或者更新表中具有匹配主键的行。目标表必将数据插入表中,或者更新表中具有匹配主键的行。目标表必 须已经存在,并且定义了一个主键。须已经存在,并且定义了一个主键。须已经存在,并且定义了一个主键。须已经存在,并且定义了一个主键。 � REPLACE::::选项删除所有已有的数据,并将导入的数据插入到一个已有的目标表选项删除所有已有的数据,并将导入的数据插入到一个已有的目标表选项删除所有已有的数据,并将导入的数据插入到一个已有的目标表选项删除所有已有的数据,并将导入的数据插入到一个已有的目标表 中。中。中。中。 � 使用使用使用使用 REPLACE_CREATE 选项时,如果目标表已经存在,则导入实用程序删除已选项时,如果目标表已经存在,则导入实用程序删除已选项时,如果目标表已经存在,则导入实用程序删除已选项时,如果目标表已经存在,则导入实用程序删除已 有的数据,并插入新的数据,就像有的数据,并插入新的数据,就像有的数据,并插入新的数据,就像有的数据,并插入新的数据,就像 REPLACE REPLACE REPLACE REPLACE 选项那样。如果目标表还没有定选项那样。如果目标表还没有定选项那样。如果目标表还没有定选项那样。如果目标表还没有定 义,那么首先创建这个表以及它的相关索引,然后再导入数据。正如您可能想义,那么首先创建这个表以及它的相关索引,然后再导入数据。正如您可能想义,那么首先创建这个表以及它的相关索引,然后再导入数据。正如您可能想义,那么首先创建这个表以及它的相关索引,然后再导入数据。正如您可能想 像的那样,输入文件必须是像的那样,输入文件必须是像的那样,输入文件必须是像的那样,输入文件必须是 PC/IXF PC/IXF PC/IXF PC/IXF 格式的文件,因为那种格式包含对导出表格式的文件,因为那种格式包含对导出表格式的文件,因为那种格式包含对导出表格式的文件,因为那种格式包含对导出表 的结构化描述。如果目标表是被一个外键引用的一个父表,那么就不能使用的结构化描述。如果目标表是被一个外键引用的一个父表,那么就不能使用的结构化描述。如果目标表是被一个外键引用的一个父表,那么就不能使用的结构化描述。如果目标表是被一个外键引用的一个父表,那么就不能使用 REPLACE_CREATEREPLACE_CREATEREPLACE_CREATEREPLACE_CREATE。。。。 � CREATE::::选项首先创建目标表和它的索引,然后将数据导入到新表中。该选项选项首先创建目标表和它的索引,然后将数据导入到新表中。该选项选项首先创建目标表和它的索引,然后将数据导入到新表中。该选项选项首先创建目标表和它的索引,然后将数据导入到新表中。该选项 惟一支持的文件格式是惟一支持的文件格式是惟一支持的文件格式是惟一支持的文件格式是 PC/IXFPC/IXFPC/IXFPC/IXF。还可以指定新表所在表空间的名称。。还可以指定新表所在表空间的名称。。还可以指定新表所在表空间的名称。。还可以指定新表所在表空间的名称。 METHODMETHODMETHODMETHOD参数参数参数参数 � METHOD LMETHOD LMETHOD LMETHOD L() � 适用于ASC文件 � METHOD LMETHOD LMETHOD LMETHOD L(1 3,4 10,11) � METHOD P()METHOD P()METHOD P()METHOD P() � 适用于适用于适用于适用于DELDELDELDEL和和和和IXFIXFIXFIXF文件文件文件文件 � METHOD PMETHOD PMETHOD PMETHOD P(1,2,3,4) � METHOD N METHOD N METHOD N METHOD N (col1name,col2name,col3name) � 适用于IXF文件 � METHOD NMETHOD NMETHOD NMETHOD N(user_id,user_name,income) LOAD � LOAD FROM input_source OF input_type MESSAGES message_file [ INSERT | REPLACE | TERMINATE | RESTART ] INTO target_tablename � INSERT 模式将输入数据添加到一个表中,而不更改已有的表数据。模式将输入数据添加到一个表中,而不更改已有的表数据。模式将输入数据添加到一个表中,而不更改已有的表数据。模式将输入数据添加到一个表中,而不更改已有的表数据。 � REPLACE 模式删除表中所有已有的数据,然后用输入数据填充这个表。模式删除表中所有已有的数据,然后用输入数据填充这个表。模式删除表中所有已有的数据,然后用输入数据填充这个表。模式删除表中所有已有的数据,然后用输入数据填充这个表。 � TERMINATE 模式终止装载操作,并回滚到它开始时所在的时间点。一个例外是模式终止装载操作,并回滚到它开始时所在的时间点。一个例外是模式终止装载操作,并回滚到它开始时所在的时间点。一个例外是模式终止装载操作,并回滚到它开始时所在的时间点。一个例外是 ,如果指定了,如果指定了,如果指定了,如果指定了 REPLACE REPLACE REPLACE REPLACE 模式,那么表将被删节。模式,那么表将被删节。模式,那么表将被删节。模式,那么表将被删节。 � RESTART 模式用于重新开始之前被中断的装载命令。它将自动从最近的一致点模式用于重新开始之前被中断的装载命令。它将自动从最近的一致点模式用于重新开始之前被中断的装载命令。它将自动从最近的一致点模式用于重新开始之前被中断的装载命令。它将自动从最近的一致点 继续。要使用这个模式,可以指定与前一个继续。要使用这个模式,可以指定与前一个继续。要使用这个模式,可以指定与前一个继续。要使用这个模式,可以指定与前一个 LOAD LOAD LOAD LOAD 命令相同的选项,但是这一命令相同的选项,但是这一命令相同的选项,但是这一命令相同的选项,但是这一 次加上次加上次加上次加上 RESTARTRESTARTRESTARTRESTART。它使实用程序可以发现在装载处理中生成的所有需要的临时。它使实用程序可以发现在装载处理中生成的所有需要的临时。它使实用程序可以发现在装载处理中生成的所有需要的临时。它使实用程序可以发现在装载处理中生成的所有需要的临时 表。因此,千万不要手动删除装载命令所生成的任何临时文件,除非您确信不表。因此,千万不要手动删除装载命令所生成的任何临时文件,除非您确信不表。因此,千万不要手动删除装载命令所生成的任何临时文件,除非您确信不表。因此,千万不要手动删除装载命令所生成的任何临时文件,除非您确信不 再需要这些临时文件。一旦装载成功完成,这些临时文件将自动被删除。默认再需要这些临时文件。一旦装载成功完成,这些临时文件将自动被删除。默认再需要这些临时文件。一旦装载成功完成,这些临时文件将自动被删除。默认再需要这些临时文件。一旦装载成功完成,这些临时文件将自动被删除。默认 情况下,这些临时文件是在当前的工作目录中创建的。也可以使用情况下,这些临时文件是在当前的工作目录中创建的。也可以使用情况下,这些临时文件是在当前的工作目录中创建的。也可以使用情况下,这些临时文件是在当前的工作目录中创建的。也可以使用 TEMPFILES TEMPFILES TEMPFILES TEMPFILES PATH PATH PATH PATH 选项指定存放临时文件的目录。选项指定存放临时文件的目录。选项指定存放临时文件的目录。选项指定存放临时文件的目录。 IMPORT 与 LOAD 装载时可同时统计信息导入后RUNSTAT更新统计信息 中断时表处于LOAD PENDING状态,重启或 恢复表生效中断时表在最近提交点保持正常 只验证唯一键约束,其它由SET INTEGRITY命令验证导入时验证约束 使用数据库外临时空间使用数据库内临时空间 不支持触发器调用触发器 最小日志所有记录写日志 只支持表支持表和视图 表&&&&索引必须存在可由IXFIXFIXFIXF文件创建表&&&&索引 大型加载时较快-写格式页大数据量时较慢 LoadImport DB2事务日志 � DB2DB2DB2DB2数据库的日志原理 �   事务日志记录数据库中所有对象和数据的改变,在早前版本中最 大可达256G,其大小为( logprimary + logsecond ) * logfilsiz,其中 logprimary + logsecond的值小于或等于256,logfilsiz的最大为 262144,在9.5版本中,日志最大已经可以达到512G,其中logfilsz的 大小更改为524286。 �   DB2数据库的日志分为主日志和辅助日志,其中主日志在第一个 连接到达数据库或者数据库被激活后立即分配,而辅助日志在主日志 大小不够的时候动态分配。所以需要注意一点,日志所在的文件系统 的大小必须大于主日志文件与辅助日志文件的大小之和。 �   DB2数据库有2种日志配置方式,循环日志与归档日志。 �   循环日志:这是数据库默认的日志使用方式,主日志用来记录所 有的更改,当事务提交后,日志文件会被重用。当主日志文件达到限 制时,辅助日志文件将被使用。这种日志方式可以进行崩溃恢复和版 本恢复,不能进行前滚恢复,不支持在线备份。 �   当活动事务的使用空间超过主日志和辅助日志的限制或者日志空 间超过磁盘可使用空间,将会得到日志满的错误。 DB2事务日志 � 归档日志:启用logarchmetd1、logarchmetd2或打开 logretain参数,注意,在9.5版本中,不推荐使用logretain 参数,其所有的设置值将被忽略。在数据库归档日志规划 时,建议不再使用logretain的方法。日志文件将不会被删 除-保持在线或者离线状态。支持前滚恢复和在线备份。 事务日志满场景一 事务日志满场景一 事务日志满场景一 事务日志满场景一 当前未提交的事务太大,超过日志的限制 当前未提交的事务太大,超过日志的限制 当前未提交的事务太大,超过日志的限制 当前未提交的事务太大,超过日志的限制 � 措施一:避免超大型事务,分拆成多个较小的事务来执行 � 措施二:采用不记日志的方式 � 措施三:使用max_log 限制单个事务占用日志空间的最大百分比,使 用DB2_FORCE_APP_ON_MAX_LOG设置单个事务日志超出范围后 的处理策略 � 措施四:增加日志空间 � get database configuration for bitest ; � update database configuration for bitest using max_log 80; � update database configuration for bitest using LOGFILSIZ 32678; � update database configuration for bitest using LOGPRIMARY 30; � update database configuration for bitest using LOGSECOND 20; 事务日志满场景二: 事务日志满场景二: 事务日志满场景二: 事务日志满场景二: 某个事务 某个事务 某个事务 某个事务 一一一一直未提交,占用的日志不能被重用,导致日志满 直未提交,占用的日志不能被重用,导致日志满 直未提交,占用的日志不能被重用,导致日志满 直未提交,占用的日志不能被重用,导致日志满 � 措施一:在编写处理脚本时,减少事务执行时间,执行增 删改数据的操作尽量简单,可采用中间表分步执行 � 措施二:设置num_log_span参数来规避这个问题,参数 指定是否对一个事务可以跨越多少个日志文件具有限制以 及该限制是多少,当设置这个参数后,未提交的事务所在 的日志与当前日志跨越的个数超过这个值,将被中断,从 而避免事务长时间存在导致系统日志满 练习 � 建立100万以上数据的测试表 � 使用EXPORT将表中的数据导出 � 使用INPORT将文本文件中的数据导入到表 � 使用LOAD将文本文件中的数据导入到表 � 注意事务日志的问题 DB2视图 � CREATE VIEW viewname [col1name,……] [WITH view_attr] AS SELECT子句 WITH CHECK OPTION DB2基本函数 � 字符串操作函数 � 1111、““““ASCIIASCIIASCIIASCII”””” � ASCII(expression)ASCII(expression)ASCII(expression)ASCII(expression)返回左边第一个字符的ASCIIASCIIASCIIASCII码值 � 2222、““““CHARACTER_LENGTHCHARACTER_LENGTHCHARACTER_LENGTHCHARACTER_LENGTH”””” � CHAR_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)CHAR_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)CHAR_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)CHAR_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)返回字符串 的长度 � CHARACTER_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)CHARACTER_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)CHARACTER_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)CHARACTER_LENGTH(expression,CODEUNITS16/CODEUNITS32/OCTETS)返回 字符串的长度 � 3333、““““CHRCHRCHRCHR”””” � CHR(expression)CHR(expression)CHR(expression)CHR(expression)返回ASCIIASCIIASCIIASCII码值所代表的字符 � 4444、““““CONCATCONCATCONCATCONCAT”””” � CONCAT(expression1,expression2)CONCAT(expression1,expression2)CONCAT(expression1,expression2)CONCAT(expression1,expression2)连接两个字符串 � 5555、““““DECRYPT_BIN and DECRYPT_CHARDECRYPT_BIN and DECRYPT_CHARDECRYPT_BIN and DECRYPT_CHARDECRYPT_BIN and DECRYPT_CHAR”””” � DECRYPT_BIN(encrypted-data,password-string-expression)DECRYPT_BIN(encrypted-data,password-string-expression)DECRYPT_BIN(encrypted-data,password-string-expression)DECRYPT_BIN(encrypted-data,password-string-expression)对输入的加密数据采 用密钥进行解密 � DECRYPT_CHAR(encrypted-data,password-string-expression)DECRYPT_CHAR(encrypted-data,password-string-expression)DECRYPT_CHAR(encrypted-data,password-string-expression)DECRYPT_CHAR(encrypted-data,password-string-expression)对输入的加密字符 串采用密钥进行解密 � 6666、““““ENCRYPTENCRYPTENCRYPTENCRYPT”””” � ENCRYPT(data-string-expression,password-string-expression,hint-string-ENCRYPT(data-string-expression,password-string-expression,hint-string-ENCRYPT(data-string-expression,password-string-expression,hint-string-ENCRYPT(data-string-expression,password-string-expression,hint-string- expression)expression)expression)expression)对输入的字符串采用输入的密钥进行加密 DB2基本函数 � 7777、““““GETHINTGETHINTGETHINTGETHINT”””” � GETHINT(hint-string-expression)GETHINT(hint-string-expression)GETHINT(hint-string-expression)GETHINT(hint-string-expression)获取提示语所代表的密码值 � 8888、““““INSERTINSERTINSERTINSERT”””” � INSERT(source-string,start,length,insert-INSERT(source-string,start,length,insert-INSERT(source-string,start,length,insert-INSERT(source-string,start,length,insert- string,CODEUNITS16/CODEUNITS32/OCTETS)string,CODEUNITS16/CODEUNITS32/OCTETS)string,CODEUNITS16/CODEUNITS32/OCTETS)string,CODEUNITS16/CODEUNITS32/OCTETS) � 将源个字符串中的某个部分用另外一个字符串替换 � 9999、LOWERLOWERLOWERLOWER � LOWER(expression)LOWER(expression)LOWER(expression)LOWER(expression)将字符串全部转换为小写字母 � 10101010、LEFTLEFTLEFTLEFT � LEFT(string-LEFT(string-LEFT(string-LEFT(string- expression,length,CODEUNITS16/CODEUNITS32/OCTETS)expression,length,CODEUNITS16/CODEUNITS32/OCTETS)expression,length,CODEUNITS16/CODEUNITS32/OCTETS)expression,length,CODEUNITS16/CODEUNITS32/OCTETS)从最 左边开始截取定长字符串 � 11111111、““““LOCATELOCATELOCATELOCATE”””” � LOCATE(search-string,source-LOCATE(search-string,source-LOCATE(search-string,source-LOCATE(search-string,source- string,start,CODEUNITS16/CODEUNITS32/OCTETS)string,start,CODEUNITS16/CODEUNITS32/OCTETS)string,start,CODEUNITS16/CODEUNITS32/OCTETS)string,start,CODEUNITS16/CODEUNITS32/OCTETS) � 在字符串中从左边指定位置查找目标字符串出现的第一个位置 � 12121212、““““LTRIMLTRIMLTRIMLTRIM”””” � LTRIM(expression)LTRIM(expression)LTRIM(expression)LTRIM(expression)去掉字符串左边的空格 � 13131313、““““POSSTRPOSSTRPOSSTRPOSSTR”””” � POSSTR(source-string,search-string)POSSTR(source-string,search-string)POSSTR(source-string,search-string)POSSTR(source-string,search-string)查找目标字符串在源字符串中的开始 出现位置 � 14141414、““““REPLACEREPLACEREPLACEREPLACE”””” � REPLACE(source-string,search-string,replace-string)REPLACE(source-string,search-string,replace-string)REPLACE(source-string,search-string,replace-string)REPLACE(source-string,search-string,replace-string)用replace-stringreplace-stringreplace-stringreplace-string替 换source-stringsource-stringsource-stringsource-string中的search-stringsearch-stringsearch-stringsearch-string � 15151515、““““RIGHTRIGHTRIGHTRIGHT”””” � RIGHT(string-RIGHT(string-RIGHT(string-RIGHT(string- expression,length,CODEUNITS16/CODEUNITS32/OCTETS)expression,length,CODEUNITS16/CODEUNITS32/OCTETS)expression,length,CODEUNITS16/CODEUNITS32/OCTETS)expression,length,CODEUNITS16/CODEUNITS32/OCTETS)从最右边开始 截取定长字符串 � 16161616、““““RTRIMRTRIMRTRIMRTRIM”””” � RTRIM(expression)RTRIM(expression)RTRIM(expression)RTRIM(expression)去掉字符串右边的空格 � 17171717、““““SPACESPACESPACESPACE”””” � SPACE(num)SPACE(num)SPACE(num)SPACE(num)生成由若干个空格组成的字符串 � 18181818、““““STRIPSTRIPSTRIPSTRIP”””” � STRIP(expression)STRIP(expression)STRIP(expression)STRIP(expression)去掉字符串两侧的空格 � 19191919、““““SUBSTRSUBSTRSUBSTRSUBSTR”””” � SUBSTR(string,start,length)SUBSTR(string,start,length)SUBSTR(string,start,length)SUBSTR(string,start,length)截取stringstringstringstring从startstartstartstart位置开始 ,长度为lengthlengthlengthlength的字符串 � 20202020、““““SUBSTRINGSUBSTRINGSUBSTRINGSUBSTRING”””” � SUBSTRING(expression FROM start FOR length SUBSTRING(expression FROM start FOR length SUBSTRING(expression FROM start FOR length SUBSTRING(expression FROM start FOR length CODEUNITS16/CODEUNITS32/OCTETS)CODEUNITS16/CODEUNITS32/OCTETS)CODEUNITS16/CODEUNITS32/OCTETS)CODEUNITS16/CODEUNITS32/OCTETS) � SUBSTRING(expression,start,length,CODEUNITS16/CSUBSTRING(expression,start,length,CODEUNITS16/CSUBSTRING(expression,start,length,CODEUNITS16/CSUBSTRING(expression,start,length,CODEUNITS16/C ODEUNITS32/OCTETS)ODEUNITS32/OCTETS)ODEUNITS32/OCTETS)ODEUNITS32/OCTETS) � 21212121、““““TRIMTRIMTRIMTRIM”””” � TRIM(expression)TRIM(expression)TRIM(expression)TRIM(expression)去掉字符串两侧的空格 � 22222222、““““UPPERUPPERUPPERUPPER”””” � UPPER(expression)UPPER(expression)UPPER(expression)UPPER(expression)将字符串全部转换为小写字母 DB2基本函数 � 日期函数 � 1、day(date_expression) 返回date_expression中的日期值 � 2、dayname(date_expression)返回date_expression中的日期名称 � 3、dayofweek(date_expression)返回date_expression中的日期是一 周第几天 � 4、dayofyear(date_expression)返回date_expression中的日期是一年 第几天 � 5、days (date_expression) 返回date_expression的数值表示 � 6、hour (time_expression) 返回time_expression的小时值 � 7、minute (time_expression) 返回time_expression的分钟值 � 8、midnight_seconds (time_expression) 返回time_expression从零点 开始的秒数 DB2基本函数 � 9、month (date_expression) 返回date_expression中的月份值 � 10、monthname (date_expression) 返回date_expression的月份名称 � 11、quarter (date_expression) 返回date_expression的所属季度 � 12、second (time_expression) 返回time_expression的秒值 � 13、timestamp_format(’1999-12-31 23:59:59’, ’YYYY-MM-DD HH24:MI:SS’) 格式化timestamp数据类型 � 14、timestampdiff(4,timestamp('1999-12-31 23:59:59')-timestamp('1999-12- 31 23:53:59')) 1 Fractions of a second 2 Seconds 4 Minutes 8 Hours 16 Days 32 Weeks 64 Months 128 Quarters 256 Years � 15、to_char (date_expression,format string) 根据指定的format返回 date_expression的字符表现形式 � 16、to_date (string,format string) 根据指定的format将字符串转换为 日期时间 � 17、year(date_expression) 返回date_expression中的年份值 � 18、week(date_expression) 返回date_expression是一年的第几周 � 19、要计算两个日期之间的天数,您可以对日期作减法, days(date(‘2010-3-1’))-days(date(‘2009-10-22’)) � 20、获取当前系统日期时间 SELECT current date FROM sysibm.sysdummy1 SELECT current time FROM sysibm.sysdummy1 SELECT current timestamp FROM sysibm.sysdummy1 � 21、可以使用英语来执行日期和时间计算: current date + 1 YEAR current date + 3 YEARS + 2 MONTHS + 15 DAYS current time + 5 HOURS - 3 MINUTES + 10 SECONDS DB2基本函数 � 数据类型转换函数 � CAST() � CAST ( AS [ length ]) � 1、转换为字符串类型: � CHAR函数 � 语法:CHAR(ARG) � CHAR函数返回日期时间型、字符串、整数、十进制或双精度浮 点数的字符串表示。 � Sql代码 � SELECT CHAR(SALARY) FROM TEST � CHR函数 � 语法:CHR(ARG) � CHR函数返回由参数指定的ASCII码的字符,参数可以是 INTEGER或SMALLINT。 � Sql代码 � SELECT CHR(167) FROM TEST � VARCHAR函数 � 语法:VARCHAR(ARG,LENGTH) � VARCHAR函数返回字符串、日期型、图形串的可变 长度的字符串表示。 � Sql代码 � SELECT VARCHAR(NAME,50) FROM TEST � --50为截取长度,如果name字符串的长度大于50,则 返回“SQL0445W 值已被截断。SQLSTATE=01004”。 � DIGITS函数 � 语法:DIGITS() � DIGITS函数返回SMALLINT、INTEGER、BIGIT或者 DECIMAL参数的字符串值。Sql代码 � SELECT DIGITS(ID) FROM TEST � 2、转换为数字类型: � DOUBLE、FLOAT函数 � 语法:DOUBLE()、FLOAT() � DOUBLE、FLOAT函数如果参数是一个数字表达式,返回与其对应的浮点数,否 则返回错误代码。 � Sql代码 � SELECT DOUBLE('4569') FROM TEST � INT函数 � 语法:INT() � INT函数返回整型常量中的数字、字符串或者日期、时间的整数表示。 � Sql代码 � SELECT INT('111') FROM TEST � BIGINT函数 � 语法:BIGINT() � BIGINT函数返回整型常量中的数字、字符串或者时间戳的64位长整数表示。 � Sql代码 � SELECT BIGINT('111') FROM TEST � SMALLINT函数 � 语法:SMALLINT() � SMALLINT函数返回整型常量中的数字、字符串短整数表示。 � Sql代码 � SELECT SMALLINT('111') FROM TEST � REAL函数 � 语法:REAL() � REAL函数返回一个数值的单精度浮点数表示。 � Sql代码 � SELECT REAL(10) FROM TEST � DEC[IMAL]函数 � 语法:DEC[IMAL]() � DEC[IMAL]函数返回一个数值、DECIMAL的字符串、INTEGER 的字符串、FLOAT-POINT的字符串、日期、时间或时间戳的 DECIMAL数值。 � Sql代码 � SELECT DEC(10) FROM TEST � HEX函数 � 语法:HEX() � HEX函数返回一个字符串的值的16进制表示。 � Sql代码 � SELECT HEX(10) FROM TEST � FLOOR函数 � 语法:FLOOR() � FLOOR函数小于或等于参数的最大整数。 � Sql代码 � SELECT FLOOR(10.50) FROM TEST � 其他常用函数 � VALUEVALUEVALUEVALUE函数 语法:VALUE(EXPRESSION1,EXPRESSION2) VALUE函数是用返回一个非空的值,当其第一个参数非空,直接返回 该参数的值,如果第一个参数为空,则返回第二个参数的值。 Sql代码 --表示如果T1.ID为空,则返回空串,如果T1.ID不为空,则返回T1.ID SELECT VALUE(ID,'') FROM T1 � COALESCECOALESCECOALESCECOALESCE函数 语法:COALESCE(ARG1,ARG2...) COALESCE返回参数集中第一个非null参数。用法类似于VALUE函数 。 � RAND()函数 产生一个0到1之间的随机数 � ROW_NUMBER() OVER()ROW_NUMBER() OVER()ROW_NUMBER() OVER()ROW_NUMBER() OVER()函数 可以用来为结果集产生排序行号,能够变通实现类似于 ORACLEORACLEORACLEORACLE的ROWNUM,ROWNUM,ROWNUM,ROWNUM, 如下例 SELECT SELECT SELECT SELECT **** FROMFROMFROMFROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT ROW_NUMBER() OVER (ORDER BY COL1, COL2 ) AS SORTNUM, COL1, COL2 COL1, COL2 ) AS SORTNUM, COL1, COL2 COL1, COL2 ) AS SORTNUM, COL1, COL2 COL1, COL2 ) AS SORTNUM, COL1, COL2 FROM aFROM aFROM aFROM a WHEREWHEREWHEREWHERE ORDER BY COL1, COL2)ORDER BY COL1, COL2)ORDER BY COL1, COL2)ORDER BY COL1, COL2) WHERE SORTNUM BETWEEN 20 AND 50WHERE SORTNUM BETWEEN 20 AND 50WHERE SORTNUM BETWEEN 20 AND 50WHERE SORTNUM BETWEEN 20 AND 50 DB2序列使用 � 在 DB2 中可以用两种方法自动生成一个数字序列: � 定义带有 IDENTITY 属性的列。 � 创建 SEQUENCE 对象。 � IDENTITY IDENTITY IDENTITY IDENTITY 列 � 当用 IDENTITY 属性定义表的列时,每当将一行插入表时 ,就会自动为该列生成一个数值。 � SEQUENCE SEQUENCE SEQUENCE SEQUENCE 对象 � 让 DB2 自动生成数字序列的第二种方法是创建 SEQUENCE 对象。可以使用 序列表达式(sequence expression)来引用序列对象。序列表达式可以出现在表 达式能够出现的大多数位置。序列表达式可以指定要返回 的值是新生成的值还是以前生成的值。 IDENTITY 属性列 的创建 � CREATE TABLECREATE TABLECREATE TABLECREATE TABLE ID_TEST ( test_id INT NOT NULLINT NOT NULLINT NOT NULLINT NOT NULL GENERATED ALWAYS AS IDENTITY (GENERATED ALWAYS AS IDENTITY (GENERATED ALWAYS AS IDENTITY (GENERATED ALWAYS AS IDENTITY ( START WITHSTART WITHSTART WITHSTART WITH 1 INCREMENT BYINCREMENT BYINCREMENT BYINCREMENT BY 1 MINVALUEMINVALUEMINVALUEMINVALUE 1 NO MAXVALUE NO MAXVALUE NO MAXVALUE NO MAXVALUE NO CYCLE NO CYCLE NO CYCLE NO CYCLE NO CACHE NO CACHE NO CACHE NO CACHE ORDER)ORDER)ORDER)ORDER) )))) 序列的创建 � CREATE SEQUENCE sequence-name * AS INTEGER/AS data-type START WITH numeric-constant INCREMENT BY 1/INCREMENT BY numeric-constant NO MINVALUE/MINVALUE numeric-constant NO MAXVALUE/MAXVALUE numeric-constant NO CYCLE/CYCLE CACHE 20/CACHE integer-constant/NO CACHE NO ORDER/ORDER 序列的使用 � select NEXT VALUE FOR seq_test from SYSIBM.SYSDUMMY1 � select NEXTVAL FOR seq_test from SYSIBM.SYSDUMMY1 � select PREVIOUS VALUE FOR seq_test from SYSIBM.SYSDUMMY1 � select PREVVAL FOR seq_test from SYSIBM.SYSDUMMY1 练习 � 在数据库上演练各个DB2函数的实际操作 � 在数据库上创建一个序列,使用序列向表中插入数据 DB2索引 � 索引的作用: � 快速存取数据 � 保证数据记录唯一性 � 实现表与表之间的完整性约束 � 索引大幅提高读取数据的速度,但同时降低写入数据的速 度 � 非唯一索引和唯一索引 � 集群索引和非集群索引 创建索引 � CREATE [UNIQUE] INDEX index_name ON table_name (col1_name,col2_name……) [NOT PARTITIONED] [IN tablespace_name] 查看索引 � SELECT * FROM sysibm.sysindexes WHERE NAME=index_name 重命名索引 � RENAME INDEX old_index_name TO new_index_name 删除索引 � DROP INDEX index_name SQL PL数据类型 � 系统数据类型 � 时间、字符、数字、XML � 单值数据类型 � 用户自定义数据类型,基于系统数据类型创建,单个数值结构 � 结构数据类型 � 由一组系统数据类型或单值数据类型组合,用于描述一个单实体 � 数组数据类型 � 一组数据类型的值构成的数组 创建单值数据类型 � CREATE DISTINCT TYPE distinct_type_name AS source_data_type WITH COMPARISONS; � COMMENT ON DISTINCT TYPE distinct_type_name IS ‘ 数据类型名’; � 示例: � CREATE DISTINCT TYPE inc_type AS DECIMAL(14,2) WITH COMPARISONS; � COMMENT ON DISTINCT TYPE inc_type IS ‘每日营业收 入’; 创建结构数据类型 � CREATE TYPE type-name UNDER supertype-name --引用父类型 AS ( attribute-definition) --(col_name data_type,…) INSTANTIABLE/NOT INSTANTIABLE --是否可以创建实例 INLINE LENGTH integer --结构数据类型存储的最大字节数 WITHOUT COMPARISONS --申明没有运算函数 NOT FINAL/FINAL --申明是否可以作为父类型被引用 MODE DB2SQL -- WITH FUNCTION ACCESS -- REF USING rep-type CAST (SOURCE AS REF) WITH funcname1 CAST (REF AS SOURCE) WITH funcname2 method-specification � OVERRIDING METHOD method-name ([parameter-name data-type2 AS LOCATOR]) RETURNS data-type3 AS LOCATOR/data-type4 CAST FROM data- type5 AS LOCATOR SPECIFIC specific-name SELF AS RESULT SQL-routine-characteristics external-routine-characteristics 创建结构数据类型示例 � 示例 � create type user_info as (user_id decimal(12),inc inc_type) not final MODE DB2SQL; 创建数组数据类型 � CREATE TYPE array_type_name AS data_type ARRAY[integer] � 只能是系统数据类型构成的数组 � 示例: CREATE TYPE numbers AS INTEGER ARRAY[100]; CREATE TYPE names AS VARCHAR(30) ARRAY[]; 查询和删除自定义数据类型 � 查询数据类型 � SELECT * FROM sysibm.sysdatatypes; � 删除数据类型 � DROP TYPE type_name; SQL PL变量声明 � DECLARE variable_name data_type DEFAULT [NULL/constant] � 示例: � DECLARE v_inc DECIMAL(14,2) DEFAULT 0.0; � DECLARE v_username VARCHAR(50); � DECLARE v_inc inc_type; � DECLARE v_ user_info user_info; � DECLARE v_ numbers numbers; SQL PL赋值语句 � 给变量赋值 � SET语句 � SET variable_name =val; � Val可以是常量、select语句、函数 � 可以对多个变量同时赋值 set pay =10000,bonus = 1500; set (pay,bonus) = (10000,1500); set (pay,bonus) = select (pay,bonus) from employee where empno = 5 � VALUES INTO � VALUES val INTO variable_name; � SELECT INTO � SELECT COUNT(*) INTO variable_name FROM test; � FETCH INTO � 用于游标 SQL PL条件语句 � IF 布尔表达式 THEN SQL语句/程序模块 ELSE IF 布尔表达式 THEN SQL语句/程序模块 ELSE SQL语句/程序模块 END IF 可以在if/then/else 语句中使用sql运算符,如: if (salary between 10000 and 90000) then... if (deptno in ('a00','b01')) then.. if (exist (select * from employee)) then... if (select count(*) from employee)>0) then.. � CASE WHEN 布尔表达式 THEN WHEN 布尔表达式 THEN ELSE END SQL PL循环语句 � LOOP循环:简单循环 � L1:LOOP SQL语句/程序模块; LEAVE L1; END LOOP L1; � WHILE循环: � WHILE 布尔表达式 DO SQL语句/程序模块; END WHILE; SQL PL循环语句 � REPEAT循环 � REPEAT SQL语句/程序模块; UNTIL 布尔表达式 END REPEAT; � FOR循环 � FOR loop_name AS SELECT … FROM DO SQL语句/程序模块; END FOR; SQL PL退出语句 � LEAVE label_name; 退出本层循环,如有多层循环,则回到上层循环 � ITERATE label_name; 结束本次循环,跳转到下一次循环条件的判断 for emp as select * from employee do if(emp.bonus>10000) then set total_bonus = total_bonus +emp.bonus; else iterate check_bonus; end if; end for � RETURN [integer_expression] 从存储过程、批处理或语句块中无条件退出,可以给调用 者返回一个整数值,用于存储过程时,不能return空值 SQL PL游标的使用 � 游标可以提供对结果集中任意一条进行访问 � 客户端游标和服务器端游标 � 游标的使用过程 � 声明游标 � 打开游标 � 读取数据 � 关闭游标 声明游标 � DECLARE cusor_name CURSOR FOR SELECT语句 [WITHOUT HOLD][WITH HOLD] --without hold选项使游标在任何commit/rollback操作 后自动关闭,with hold可以使游标在commit后保持打开 [WITHOUT RETURN|WITH RETURN[TO CALLER|TO_CLIENT]] --是否将游标作为存储过程的结果返回 [FOR READ ONLY|FOR UPDATE[ OF col1,col2…]] 声明游标 � pcursor1: for loopcs1 as cousor1 cursor as select 语句 for update do SQL语句/程序模块; end for; � 使用比较方便,不用OPEN、FETCH和CLOSE � 不能使用WITH HOLD选项 � 打开游标 OPEN CURSOR_NAME � 读取游标 FETCH [FROM] cursor_name [INTO variable_name1, variable_name2,…] � 关闭游标 CLOSE CURSOR_NAME � begin declare sqlcode integer default 0; declare app_code varchar(10); declare cursor1 cursor for select app_code from kf_app_class ; open cursor1; cursorLoop: loop fecth cursor1 into app_code ; if sqlcode=100 then leave cursorLoop; end if; end loop; end; DB2临时表的使用 � 我们在储存过程开发中经常使用临时表。合理的使用临时表可以简化 程序的编写,提供执行效率,然而滥用临时表同样也会使得程序运行 效率降低。 � 临时表一般在如下情况下使用: � 1. 临时表用于存储程序运行中的临时数据。例如,如果在一个程序中 第一条查询语句执行的结果会被后续的查询语句用到,那么我们可以 把第一次查询的结果存储在一个临时表中供后续查询语句使用,而不 是在后续查询语句中重新查询一次。如果第一条查询语句非常复杂和 耗时,那么上面的策略是非常有效的。 � 2. 临时表可以用于存储在一个程序中需要返回多次的结果集。例如, 程序中有一个很耗资源的多表查询,同时,该查询在程序中需要执行 多次,那么就可以把第一次查询的结果集存储在临时保中,后续的查 询只需要查临时表就可以了。 � 3. 临时表也可以用于让SQL访问非关系型数据库。例如,可以编写程 序把非关系型数据库中的数据插入到一个全局临时表中,那么我们就 可以对其数据进行查询。 DB2临时表的使用 � 要声明临时表,必须存在 USER 临时表空间,以便存储临 时表的定义和内容。(USER 临时表空间与 SYSTEM 临 时表空间不同;后者只在 DB2 内部用于执行排序等操作。 )下面这个简单语句创建一个 USER 临时表空间: � CREATE USER TEMPORARY TABLESPACE usertempspace MANAGED BY SYSTEM USING ('usertempspace') � DB2的临时表是基于会话的,且在会话之间是隔离的。当 会话结束时,临时表的数据被删除,临时表被隐式卸下。 对临时表的定义不会在SYSCAT.TABLES中出现 DB2临时表的使用 � DECLARE GLOBAL TEMPORARY TABLE t_dept ( deptid CHAR(6), deptname CHAR(20) ) ON COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS IN usertempspace � 在例子中,临时表 t_dept 的声明用了两列。ON COMMIT PRESERVE ROWS 子句说明执行 COMMIT 操作时,将保留临时表 中的行。 � 该例子指定对表的更改是 NOT LOGGED。就是说在表上进行的任何 插入、更新或删除操作都不会留下日志记录。但记录表的创建和删除 。如果创建表时执行了一组操作,并且在此之后发生了回滚,则该临 时表将被删除。另一方面,如果删除表时执行了一组操作,则该表将 恢复为没有任何行的空表。 � 无需使用 IN 子句指定临时表将要使用的用户临时表空间。如果不指定 此信息,则 DB2 将搜索最合适的表空间。如果无法找到用户临时表空 间,DB2 将引发一个错误 DB2临时表的使用 � DECLARE GLOBAL TEMPORARY TABLE t_proj   LIKE project   ON COMMIT PRESERVE ROWS   WITH REPLACE   IN usertempspace �   使用关键字 LIKE 声明临时表 t_proj,因此它拥有与名 为 project 的永久表或视图相同的列定义。ON COMMIT PRESERVE ROWS 子句表明在 COMMIT 时将保留临时 表中的所有行。因此,它们可以在后面的事务中进一步进 行处理。 �   在同一会话中使用同一名称声明另一个临时表之前, 必须先删除原临时表。可显式地完成此操作,也可利用 WITH REPLACE 选项,如清单 4 所示。通过 WITH REPLACE 选项,DB2 隐式地删除所有数据和临时表并使 用新定义重新创建该表。 DB2存储过程 � CREATE PROCEDURE--procedure-name � (IN/OUT/INOUT parameter-name data-type[,...n]) � [SPECIFIC--specific-name] � [DYNAMIC RESULT SETS 0/integer] � [MODIFIES SQL DATA|CONTAINS SQL|READS SQL DATA] � [NOT DETERMINISTIC|DETERMINISTIC] � [CALLED ON NULL INPUT] � [INHERIT SPECIAL REGISTERS] � [OLD SAVEPOINT LEVEL] � [NEW SAVEPOINT LEVEL] � [LANGUAGE SQL] � [EXTERNAL ACTION|NO EXTERNAL ACTION] � [PARAMETER CCSID ASCII|UNICODE] � [SQL-procedure-body] � 1、procedure-name: 存储过程的名字,在同一个数据库的同一模式下 ,不能存在存储过程名相同参数数目相同的存储过程,即使参数的类 型不同也不行。 � 2、(IN | OUT | INOUT parameter-name data-type,...) :传入参数 � IN:输入参数 � OUT:输出参数 � INOUT:作为输入输出参数 � parameter-name:参数名字,在此存储过程中唯一的标识符。 � data-type:参数类型,可以接收SQL类型和创建的表。不支持 LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。 � 3、SPECIFIC specific-name:唯一的特定名称(别名),可以用存 储过程名代替,这个特定名称用于dorp存储过程,或者给存储过程添 加注视用,但不能调用存储过程。如果不指定,则数据库会自动生成 一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。 � 4、DYNAMIC RESULT SETS integer:指定存储过程返回结果的最 大数量。存储过程中虽然没有return语句,但是却能返回结果集。 � 5、CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA: 指定存储过程中的SQL访问级别 � CONTAINS SQL: 表示存储过程执行中,既不可读取 SQL 数据, 也不可修改 SQL 数据。 � READS SQL DATA: 表示存储过程执行中,可读取SQL,但不可 修改 SQL 数据。 � MODIFIES SQL DATA: 表示存储过程可以执行任何 SQL 语句。 可以对数据库中的数据进行增加、删除和修改。 � 6、DETERMINISTIC or NOT DETERMINISTIC:表示存储过程是动 态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每 次执行返回的值是相同的。 � 7、CALLED ON NULL INPUT:表示可以调用存储过程而不管任何的 输入参数是否为NULL,并且,任何的OUT或者INOUT参数可以返回 一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。 � 8、INHERIT SPECIAL REGISTERS:表示继承专用寄存器。 � 9、OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL:建立存 储点。OLD SAVEPOINT LEVEL是默认的存储点。 � 10、LANGUAGE SQL:指定程序的主体用的是SQL语言。 � 11、EXTERNAL ACTION or NO EXTERNAL ACTION:表示存储过 程是否执行一些改变理数据库状态的活动,而不通过数据库管理器管 。默认是 EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。 � 12、PARAMETER CCSID:指定所有输出字符串数据的编码,默认为 UNICODE编码数据库为PARAMETER CCSID UNICODE ,其他的数 据库默认为PARAMETER CCSID 3 ASCII。 � 13、SQL-procedure-body:存储过程的主体 异常和条件处理 � sql pl不能执行的sql:table,index,view的create和drop � 查询报错信息查询报错信息查询报错信息查询报错信息 � 打开打开打开打开cmd,cmd,cmd,cmd,运行运行运行运行db2cmddb2cmddb2cmddb2cmd � 连接数据库连接数据库连接数据库连接数据库db2 connect to [dbname] user [username] db2 connect to [dbname] user [username] db2 connect to [dbname] user [username] db2 connect to [dbname] user [username] using [password]using [password]using [password]using [password] � 查询查询查询查询SQLCODESQLCODESQLCODESQLCODE信息:信息:信息:信息:db2 db2 db2 db2 ????xxx;xxx;xxx;xxx; � 查询查询查询查询SQLSTATESQLSTATESQLSTATESQLSTATE信息:信息:信息:信息:db2 db2 db2 db2 ????sqlnnnnn;sqlnnnnn;sqlnnnnn;sqlnnnnn; � DECLARE CONTINUE HANDLER FOR NOT FOUNDDECLARE CONTINUE HANDLER FOR NOT FOUNDDECLARE CONTINUE HANDLER FOR NOT FOUNDDECLARE CONTINUE HANDLER FOR NOT FOUND � DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONDECLARE CONTINUE HANDLER FOR SQLEXCEPTIONDECLARE CONTINUE HANDLER FOR SQLEXCEPTIONDECLARE CONTINUE HANDLER FOR SQLEXCEPTION � DECLARE CONTINUE HANDLER FOR SQLWARNING DECLARE CONTINUE HANDLER FOR SQLWARNING DECLARE CONTINUE HANDLER FOR SQLWARNING DECLARE CONTINUE HANDLER FOR SQLWARNING ATOMIC ATOMIC ATOMIC ATOMIC 和和和和 NOT ATOMIC NOT ATOMIC NOT ATOMIC NOT ATOMIC 复合 复合 复合 复合 SQL SQL SQL SQL 语句 语句 语句 语句 � 有两种类型的复合语句:NOT ATOMIC(默认)和 ATOMIC。 � NOT ATOMIC:NOT ATOMIC:NOT ATOMIC:NOT ATOMIC:如果发生一个未处理的错误条件,不会回 滚 SQL 语句 � ATOMIC:ATOMIC:ATOMIC:ATOMIC:在一条 ATOMIC 复合语句的执行期间,如果其 中发生任何未处理的错误条件,那么在此之前已执行的所 有语句都被回滚。ATOMIC 语句不能被嵌套在其他 ATOMIC 复合语句中。 � BEGIN [NOT ATOMIC| NOT ATOMIC| NOT ATOMIC| NOT ATOMIC| ATOMIC]]]] 程序模块 ENDENDENDEND 复合 复合 复合 复合 SQL SQL SQL SQL 语句与变量的作用域 语句与变量的作用域 语句与变量的作用域 语句与变量的作用域 � 在 SQL 过程中可以有一个或多个复合语句。那些复合语句可以是嵌套 的,也可以是一个接一个的。每个复合语句引入一个新的本地变量的 作用域,那些变量只能在此作用域中使用。正是由于这个原因,当一 个存储过程中有不止一个复合语句时,我们建议使用标签。 � 嵌套的复合块 CREATE PROCEDURE VAR_SCOPE ( ) L1:BEGIN DECLARE v_outer1 INT; DECLARE v_outer2 INT; L2:BEGIN DECLARE v_inner1 INT; DECLARE v_inner2 INT; SET v_outer1 = 100; --(1) -- success SET v_inner1 = 200; END L2; SET v_outer2 = 300; SET v_inner2 = 400; --(2) -- fail END L1 构建这个 SQL 过程时,会收到以下错误消息: DB2ADMIN.VAR_SCOPE: 12: "V_INNER2" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.50.152 注意,语句 (1) 成功地通过,因为 v_outer1v_outer1v_outer1v_outer1 是在复合语句外声明的, 但是语句 (2) 失败了,因为 v_inner2v_inner2v_inner2v_inner2 不是在复合语句外声明的。 存储过程中的错误处理 � 在db2中,如果要使用sqlcode那么必须在使用前declare � 异常处理器类型(handler-type)有以下几种:   CONTINUE 在处理器操作完成之后,会继续执行产生 这个异常语句之后的下一条语句。   EXIT 在处理器操作完成之后,存储过程会终止,并将 控制返回给调用者。   UNDO 在处理器操作执行之前,DB2会回滚存储过程 中执行的SQL操作。在处理器操作完成之后,存储过程会 终止,并将控制返回给调用者。 存储过程中的错误处理 �   异常处理器可以处理基于特定SQLSTATE值的定制异常,或者处 理预定义异常的类。预定义的3种异常如下所示:   NOT FOUND 标识导致SQLCODE值为+100或者SQLSATE值为 02000的异常。这个异常通常在SELECT没有返回行的时候出现。   SQLEXCEPTIOIN 标识导致SQLCODE值为负的异常。   SQLWARNING 标识导致警告异常或者导致+100以外的 SQLCODE正值的异常。 �   如果产生了NOT FOUND 或者SQLWARNING异常,并且没有为 这个异常定义异常处理器,那么就会忽略这个异常,并且将控制流转 向下一个语句。如果产生了SQLEXCEPTION异常,并且没有为这个 异常定义异常处理器,那么存储过程就会失败,并且会将控制流返回 调用者。 存储过程示例 � CREATE PROCEDURE procedure_name � (IN I_STARTTIME TIMESTAMP, � IN I_ENDTIME TIMESTAMP, � OUT O_RETURNCODE INTEGER, � OUT O_RETURNMSG CHARACTER(200) � ) � /*头标注*/ � P1: BEGIN � DECLARE SQLCODE INTEGER DEFAULT 0; � DECLARE RETCODE INTEGER DEFAULT 0; � DECLARE RETURNMSG CHARACTER(200) DEFAULT ‘’; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET returnCode = SQLCODE; SET RETURNMSG = errorLabel; END; DECLARE CONTINUE HANDLER FOR SQLWARNING SET RETCODE=SQLCODE; � DECLARE CONTINUE HANDLER FOR NOT FOUND SET RETCODE=SQLCODE; � SET O_RETURNCODE = 0; � SET O_RETURNMSG = 'BEGIN: 存储过程 未运行!'; � --sql功能注释 � sql。。。 --异常处理 � IF RETCODE < 0 THEN � SET O_RETURNCODE = RETCODE; � SET O_RETURNMSG = RETURNMSG; � ROLLBACK; � RETURN O_RETURNCODE; � ELSE � SET RETCODE = 0 ; COMMIT; � END IF; � --正常结束,返回成功信息 � SET O_RETURNCODE = 0; � SET O_RETURNMSG = 'END:存储过程 执行成功!'; � END P1; 调用存储过程 � 查看存储过程信息 � SELECT * FROM syscat.procedures WHERE procname=UPPER(‘proc_name’) � 调用和调试存储过程 � CALL proc_name(pram1,pram2,……);Out参数可以用declare声明 ,也可用?占用符进行调试 � CALL proc_test(current timestamp,current timestamp,?,?); � begin declare v_int integer; declare v_char CHARACTER(200) ; CALL proc_test (current timestamp,current timestamp,v_int,v_char); end 创建 SQL 存储过程 � 在命令行中可以这样执行包含存储过程在命令行中可以这样执行包含存储过程在命令行中可以这样执行包含存储过程在命令行中可以这样执行包含存储过程 DLL DLL DLL DLL 的脚本:的脚本:的脚本:的脚本: � db2 -td@ -v -f mysprocs.db2db2 -td@ -v -f mysprocs.db2db2 -td@ -v -f mysprocs.db2db2 -td@ -v -f mysprocs.db2 � 在调用包含在调用包含在调用包含在调用包含 SQL SQL SQL SQL 语句的脚本时,有以下几种不同的选项:语句的脚本时,有以下几种不同的选项:语句的脚本时,有以下几种不同的选项:语句的脚本时,有以下几种不同的选项: � t: t: t: t: 表明使用表明使用表明使用表明使用 ; ; ; ; 表示一行的结束。这样一来,在脚本中,一个表示一行的结束。这样一来,在脚本中,一个表示一行的结束。这样一来,在脚本中,一个表示一行的结束。这样一来,在脚本中,一个 SQL SQL SQL SQL 语语语语 句可以写成多行。句可以写成多行。句可以写成多行。句可以写成多行。 � v: v: v: v: 打开打开打开打开 verbose verbose verbose verbose 模式。在该模式下,脚本输出中会再次显示所有脚模式。在该模式下,脚本输出中会再次显示所有脚模式。在该模式下,脚本输出中会再次显示所有脚模式。在该模式下,脚本输出中会再次显示所有脚 本命令。这样便于发现失败的命令。当脚本文件较大时,如果没有打本命令。这样便于发现失败的命令。当脚本文件较大时,如果没有打本命令。这样便于发现失败的命令。当脚本文件较大时,如果没有打本命令。这样便于发现失败的命令。当脚本文件较大时,如果没有打 开开开开 verbose verbose verbose verbose 模式,那么调试起来非常花时间。模式,那么调试起来非常花时间。模式,那么调试起来非常花时间。模式,那么调试起来非常花时间。 � f: f: f: f: 指定输入文件的名称。指定输入文件的名称。指定输入文件的名称。指定输入文件的名称。 � td: td: td: td: 指定使用跟在这个标志后面的字符(而不是一个分号)来界定命指定使用跟在这个标志后面的字符(而不是一个分号)来界定命指定使用跟在这个标志后面的字符(而不是一个分号)来界定命指定使用跟在这个标志后面的字符(而不是一个分号)来界定命 令的结束。令的结束。令的结束。令的结束。 练习 � 通过存储过程练习各种SQL PL语法,临时表使用,错误处 理 � 编写一个存储过程,从用户表生成统计数据插入下表 上月用户数用户数性别品牌地区统计月份 cnt_LMcntgenderBrand_idArea_idStat_month 使用 TOAD工具调试存储过程 � 在TOAD中建立远程数据库连接 � 在TOAD中建立远程数据库连接 � 在客户端建立服务器端数据库的节点 在客户端命令行执行 Db2 catalog tcpip node test remote 192.168.42.102 server 50000 注:test为在客户端定义的节点,名字可以任意 192.168.42.102为客户端的ip,50000为DB2使用的端口 � 使用TOAD的connections面板创建数据库连接 � 连接数据库 � PL/SQL调试 Toad 提供强大易用的PL/SQL 调试功能,可以节省开发人 员在大型项目中用于开发和测试的宝贵时间,提高应用开 发的质量。在存储过程开发的过程中,Toad可以逐行编辑 、调试和运行代码。运行时可以根据需要输入参数,观察 相关参数的变化来检查存储过程的正确性。在调式过程中 ,Toad 可以通过窗口显示所有的断点、参数, 调用堆栈和 输出参数。使用Toad,非常容易检测到存储过程的错误, 开发人员可以一步一步运行PL/SQL语句来识别问题。调试 会话可以和其他程序会话同时进行。 DB2用户自定义函数 � CREATE FUNCTION function-name (parameter-declaration) � RETURNS � � [SPECIFIC--specific-name] � [LANGUAGE SQL] � [PARAMETER CCSID ASCII|UNICODE] � [NOT DETERMINISTIC|DETERMINISTIC] � [EXTERNAL ACTION|NO EXTERNAL ACTION] � [READS SQL DATA|CONTAINS SQL|MODIFIES SQL DATA] � [STATIC DISPATCH] � [CALLED ON NULL INPUT] � [INHERIT SPECIAL REGISTERS] � [PREDICATES (predicate-specification)] � [INHERIT ISOLATION LEVEL LOCK REQUEST] RETURNSRETURNSRETURNSRETURNS � RETURNS 子句确定创建的函数的类型。主要的三种类型是 scalar、row 和 table。 � 简单的标量函数,返回单个数据类型值 CREATE FUNCTION tan (x DOUBLE) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN SIN(x)/COS(x) � 简单的行函数,将一个用户定义类型分解到它的不同部分中 CREATE FUNCTION fromperson (p person) RETURNS ROW (name VARCHAR(10), firstname VARCHAR(10)) LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN VALUES (p..name, p..firstname) � 简单的表函数,表函数返回一个表的 0 到多个行。表可以在 SQL 语句中创建 ,也可以在编程逻辑中创建。 CREATE FUNCTION deptemployees (deptno CHAR(3)) RETURNS TABLE ( empno CHAR(6), lastname VARCHAR(15), firstname VARCHAR(12), deptname VARCHAR(36) ) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC RETURN SELECT empno, lastname, firstnme, deptname FROM employee, department WHERE employee.workdept = department.deptno � SPECIFIC 子句用于为函数提供一个特定的标识符。当在 函数中添加注释、删除注释或者将注释变为源代码时,可 以使用这个标识符。当使用重载函数时,标识符也特别有 用。 � DETERMINISTIC 子句用于指定一个函数是否总是返回相 同的值。然后,DB2 可以使用该信息来优化调用函数的方 式,如果之前该函数已经执行过一次,而返回的值又是确 定的,那么 DB2 可以将函数的值缓存起来。如果函数使用 了专用寄存器,或者调用了非确定性函数,那么该函数就 是非确定性函数。 � EXTERNAL ACTIONEXTERNAL ACTIONEXTERNAL ACTIONEXTERNAL ACTION子句指定一个函数是否更改数据库之 外的任何对象。如果函数要执行会产生外部影响的动作, 那么必须将该选项设置为 EXTERNAL ACTION。例如, 对于修改文件系统中的文件或者更改外部源中的数据的函 数,就需要使用该子句。 自定义函数示例 � DROP FUNCTION FUNC_CAN_MANAGE; � CREATE FUNCTION FUNC_CAN_MANAGE ( � in_opId VARCHAR(20), � in_id VARCHAR(20) � ) � RETURNS INTEGER � LANGUAGE SQL � BEGIN ATOMIC DECLARE v_id VARCHAR(20);--注释 DECLARE v_PId VARCHAR(20);--注释 IF in_opId = in_id THEN RETURN 0;--注释 END IF;--注释 SET v_id = in_id;--注释 WHILE v_id IS NOT NULL DO SET v_PId =( SELECT PARENTID FROM BRANCH WHERE ID = v_id);--注释 IF v_PId = in_opId THEN RETURN 0;--注释 END IF;--注释 SET v_id = v_PId;--注释 END WHILE;--注释 RETURN 1;--注释 � END; 函数调用 � CREATE FUNCTION edCount (v_edLevel DOUBLE) RETURNS INT LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION RETURN SELECT count(*) FROM employee WHERE edLevel = v_edLevel � SELECT edLevel, edCount(edLevel) AS edQuantity FROM employee GROUP BY edlevel SQL PL性能调优 � 关注数据处理的算法 � 数据处理的过程逻辑是否最优,对于需要的数据表,尽量只扫描需 要的数据项,并且同样的表或数据只扫描一次 � 在SQL过程中使用自定义函数要格外小心,特别是带SQL的函数 � 关注被执行多次的单sql性能 � 尽量不要使用循环来执行SQL语句,在做大数据量处理的时候慎用 游标 � 被执行多次的单sql性能保持最优,如果逻辑复杂,可采用临时表 进行缓存 � 关注大数据表的处理SQL的性能 DB2 SQL优化器 � DB2数据库通过优化器来分析你的SQL,生成它认为最优 的执行计划(Access Plan)。DB2的优化器实际上是一个标 准规则集合,一般来说我们只要告诉DB2要检索什么,而 不是如何检索。 � DB2的优化器是基于成本的优化器,也就是CBO(Cost Based Optmizer)。也就是说DB2 优化器会应用查询成本 公式,该公式对每条可能的存取路径的四个因素进行评估 和权衡:CPU 成本、I/O 成本、DB2 系统目录中的统计信息 和实际的 SQL 语句。 DB2的优化器的工作流程 � 1. DB2的优化器,在接收到SQL语句后,会首先校验SQL 的语法,确保是正确的SQL � 2. 根据当前的系统环境信息,生成最优的执行计划来优化 SQL语句 � 3. 把SQL翻译成计算机指令语言,并执行这个优化后的 SQL � 4. 返回结果,或者存储它们,以便将来的执行 优化器的判断依据 � DB2 系统目录中统计信息是让DB2优化器正确工作的一个非常重要的 依据。这些统计信息向优化器提供了与正在被优化的 SQL 语句将要访 问的表状态相关的信息。这些信息主要包括: � Table--包括表的记录数、PAGE、PCTFREE以及COMPRESS等信息 ,相关的系统视图是:sysstat.tables、syscat.tables � Columns—包括COLUMNS的数量、长度、分布特征以及 COMPRESS等信息,相关的系统视图是:sysstat.columns、syscat. columns � Index--包括是否存在索引、索引的组织(叶子页的数量和级别的数量) 、索引键的离散值的数量以及是否群集索引, 相关的系统视图是 :sysstat.indexes、syscat. indexes � 其他的还有分区/节点组信息和表空间的信息 � 及时更新这些信息是保证DB2优化器正确工作的必要条件 执行计划需重点关注的部分 � SQL语句的总成本cost (timeron)数 � 是否对大表使用了全表扫描 � 判断表连接的方式是否有问题 � Nest Loop Join 1, 被连接的表较小。 2, 扫描整张表,每扫描一条记录,就去另外一个表中去找,因此如果被找 表比较小的话,这个也是一种很好的选择。当然前提是要有索引 3, 驱动表不大,被驱动表要有索引 � Hash Join 1, 驱动表不大,在内存中建立连接字段的Hash表,然后再扫描被驱动表 ,找到匹配的hash值。 2, 被驱动表是没有索引的 � Sort Merge Join 1, 连接的2张表进行全表扫描 2, 对扫描的表进行排序Sort 3, 对排序的结果进行合并Merge 使用 RUNSTAT收集数据库对象信息 � Runstats这个命令的功能主要就是收集数据库对象的状态信息,这对数据库使 用合理的Access PLAN是至关重要的。一般来说,以下几种情况下面,我们 需要用runstats来收集统计信息: � 1. 在给表创建一个index后,我们最好做一次runstat。这个情况也是大家经常 忽略的。很多时候大家在给表增加了一个index后,分析执行计划,发现没有 变化,觉得很奇怪。其实这个时候,你需要做一次runstats,就可以了。在8.2 以后版本,DB2做了很好的改进,可以避免这个问题,在创建index的时候, 可以立即更新你的信息。 � 2. 在对table做了一次reorg后,记得要做一次runstats。因为对表做reorg,会 修改表的很多信息,比如高水位等,所以做一次runstats,可以更新统计信息。 � 3. 当你的表里面的数据发生了比较大的变化,一般来说,大约表里面的数据 量的10%-20%发生了变化,就应该作一次runstats。这些变化包括删除,修改 ,插入。对于一些非常大的表,比方在数据仓库的项目里面,某些事实表非常 巨大。这个时候,完整的对一个大表作runstats可能花费时间相当大,DB2 8.1里面支持我们对这些大表作抽样,比方说只对20%的数据作runstats,这样 的话,一般来说也能保证得到正确的执行计划。当然首先要确保这个表里面的 数据最好分布比较均匀。 � 4. 当你在分区(DPF)数据库里面使用了REDISTRIBUTE DATABASE PARTITION GROUP这个命令,那么就需要用runstats来收集新的统计信息。 RUNSTATS命令 � RUNSTATS命令的语法如下: � 如果表名为BITEST.STAFF,表上有索引,则可以用下面 的例子完成RUNSTATS命令: � db2 runstats on table BITEST.staff with distribution and detailed indexes all 迁移统计信息 � DB2LOOKDB2LOOKDB2LOOKDB2LOOK � Db2look是DB2一个相当强大的一辅助工具,可以帮助我们从当前数 据库里面把数据库结构抽取出来。在数据迁移的很多场合,我们都非 常需要这个强大的工具。 � 在一些场合,特别是开发库迁移到生产库,生产库迁移到开发库的环 境中,为了保证SQL执行计划的一致,我们需要用db2look这个工具, 利用 db2look 工具提供的抽取数据库对象统计信息的功能,把数据库 的统计信息进行迁移。 � 比如: � 1) 在用户环境下提取统计信息: � db2 runstats on table <用户表模式名>.<表名> � db2look -d <用户数据库名> -t <表名> -m -o statis.sql � 输出文件中是对用户的 DB2 系统编目表中与该表统计信息相关的各字 段值的 UPDATE 语句。 � db2 -svtf statis.sql � 2) 利用用户提供的统计信息更新测试环境下测试表的统计信息: 练习 � 各种函数的创建和使用 � 使用SQL解析器查看SQL的执行计划,从简单到复杂
还剩135页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

open_xxg

贡献于2014-08-11

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf