究竟是什么让Redshift比Hive快10倍？！
发表于 16:22|
来源Hapqurus&Quora|
作者Hapqurus&Quora
摘要：在Hapqrus上一个Hive与Redshift的基准测试中，Redshift以十倍的速度完胜Hive。Reynold Xin用三个可互换术语“并行数据库”、“关系型数据库”和“分析型数据库”解释了这个悬殊。比起并行数据库，Hive在可扩展性、灵活度方面遥遥领先；然而这些优势，同样阻碍了查询引擎的性能。
Koichi Fujikawa近日对比了Redshift与Hive，并在描述了这一基准测试，Koichi发现Redshift竟然比Hive快10倍？
以下为译文：
Benchmark（基准测试）
初步测试后，我们想输入些数据做基准测试，以此来比较Redshift和我们正在使用的系统有何差距。我们想在真实场景中作比较，因此我们根据广告网络这一领域来量身定制的测试内容。这是因为广告网络需要频繁地以最快的速度将它们的统计结果反馈给客户，这些数据包括总收视次数（total
impressions）、总点击量、广告花费、点击付费（CPC）、千人成本（CPM）、广告点击率（CTR）等。我是某基于位置的广告网络系统的产品/技术经理，根据我的亲身所见，广告网络系统是用来做基准测试的绝佳场景。然而，广告网络系统定期采集海量数据（尤其是收视次数），所以一个相关的普通应用应当部署在分布式系统上。在这种情况下，广告网络系统往往需要使用Hadoop，即使数据量只有几TB。
该基准测试最困难、也最耗时的部分是将数据导入Redshift集群。我们第一次尝试导入300GB数据，大约花了5个小时。我们感到有必要将这事告诉大家，因为传输速度可能会限制其在某些场景下的应用。接着，我们试着上传1.2TB的数据到Redshift，共2880个文件夹，每个大小为300MB。这次上传花费了17个小时。
上传完数据后，我们马上构建了一个查询，以此来比较Hadoop和Redshift的效率。我们设计的查询能够在Hadoop和Redshift上以对等的方式运行，我们连接了4个表做全盘扫描。在我们的第一个数据集（300GB）上，该查询在1分钟内完成。令我们异常惊讶的是，在Hadoop上，与之对等的查询整整花费了10分钟。由此，我们看到Redshift大量减少了成本，这是因为我们没必要在Redshift开这么多的服务，同时，必要的维护成本也大大下降。随后，我们在1.2TB的数据集上执行该查询。所花的时间成线性增长，在Redshift上仅花了不到两分钟，而在Hadoop上则花了20多分钟。
究竟是什么原因产生了如此悬殊的速度，有，并得到了Reynold Xin的解答：
Redshift采用了专有的叫做ParAccel的并行数据库实现机制。我想在很多工作情境中，你会发现大多数并行数据库引擎要比Hive快。接下来，我将给出答案，并解释其中的某些原因。请注意的是，虽然该答案针对的是ParAccel，其中的大部分因素也适用于Vertica、Greenplum、OracleRAC等并行数据库。
在答案中呢，我将使用三个可互换的术语“并行数据库”、“关系型数据库”和“分析型数据库”。
比起并行数据库，Hive在可扩展性、灵活度方面遥遥领先。例如，Facebook使用Hive数据仓库跨越成千上万个节点。说起灵活度，Hive设计的初衷是与一些系列存储系统（HDFS、HBase、S3）配合使用，并能够支持多种输入格式（压缩、未压缩、Avro、纯文本、JSON）。
易扩展和高灵活度在给你带来便利的同时，却也阻碍了你构建性能更好的查询引擎。接下来，我将列举哪些特征会影响查询性能：
数据格式：数据以类似纯文本文件，相对未优化的形式存储在HDFS中。Hive 作业在处理数据之前，需要先花大量时间从硬盘中读取数据，再反序列化这些数据。
发起任务的系统开销：Hadoop MapReduce 使用心跳机制（heartbeats）制定作业计划，每项任务作为一独立的JVM过程发起。在Hadoop
MapReduce 中，仅仅是发起一项作业就需要几十秒钟，在秒级时间单位内，是无法运行任何进程的。相反，并行数据库拥有持续进程或线程池，它们能够大大减少任务安排及发起所需要的系统开销。
中间数据物化 vs数据传输：Hive 使用拥有二阶模型(Map和Reduce)的MapReduce来执行。通常一个复杂的SQL查询被映射为MapReduce的多个阶段，不同阶段的中间数据在硬盘上物化。并行数据库内置有用于执行SQL查询的引擎，执行查询时，该引擎在查询操作符和数据流（steram
data）之间跨节点传递数据。
列数据格式：列数据库将数据按照列式的格式进行存储。在典型的数据仓库中，每张数据表能够存储成百上千列，而大多数查询仅查找少数列。让我们来考虑一下如下查询，要查找的是沃尔玛每家店的营业额。它仅需要查找两三列（商店的编号、每件商品的零售价，或者还有销售日期）。以列式存储数据，执行查询时，引擎可以跳过不相关的列。这样可以减少上百次的硬盘I/O。此外，按列存储数据能够大大增加压缩比率。
列查询引擎：除了上面提到的按列式存储的数据格式，还可以按列构建查询执行引擎，该引擎在分析型工作负载方面得到了较好的优化。其中的技巧包括：晚期物化（late
materialization）、直接操作压缩过的数据、利用现代CPU提供的向量化操作（SIMD）。
更快的S3连接：在这里我将给出一个大胆的猜测：AWS可能已经为他们的Redshift实例实现了一个比普通S3能够提供的更高带宽的S3整体负载。
我需要申明，我们刚刚讨论的这些因素是基于Hive当前版本（2013年2月）。毫无置疑，Hive社区将会推进开发工作，并解决其中的一些难题。
显然随着数据体积的暴增，Hadoop相关技术已经明显满足不了数据玩家的需求。许多个人和机构纷纷打造不同的实时处理框架/平台，而对于根基较薄的机构来说，公有云确实是一个明智的选择。（文/云泽
审校/仲浩）
相关链接：
”将于-7日在北京国家会议中心隆重举行。猛击！
相关活动已经火热启动：
，欢迎研发者、团队和创业企业参加！
推荐阅读相关主题：
CSDN官方微信
扫描二维码,向CSDN吐槽
微信号：CSDNnews
相关热门文章1173人阅读
spark（61）
在spark1.2以后，开始提供插件诗的机制，并与各种结构化数据源整合。spark 用户可以读取各种各样数据源的数据，比如Hive表、JSON文件、列式的Parquet表、以及其他表。通过可以获取第三方数据源。
而这篇文章主要讨论spark 新的数据源，通过spark-redshift包，去访问。
spark-redshift 包主要由Databricks以及来自SwiftKey社区贡献者和其他公司维护。
在诞生 spark-redshift之前，spark JDBC数据源是唯一可以访问Redshift库。这种方式在查询非常少的数据（比如100条）的情况下是非常适合的，但是如果用于处理大规模数据，将会变得非常慢！这是因为JDBC提供基于ResultSet方法，使用单线程获取小批量数据。
此外，如果使用JDBC在Redshift中存储大规模的数据集，唯一可行的方式就是在Redshift中同一个数据库中进行。基于JDBC的INSERT/UPDATE查询也仅仅适合小规模的数据。对于那些想在Redshift中加载或者存储大规模数据的用户来说，JDBC实在是有太多的性能和吞吐量问题亟待改变。
然而使用spark-redshift可以简化和Redshift整合的步骤，使得我们可以从Redshift中加载或者存储大规模的数据。为了了解它是怎么工作的，让我们来看看如何用Redshift数据库来和其他的数据源的数据集进行集成。
我们还将在本文探讨spark-redshift是如何扩展的。一般情况下，数据需要从HDFS上移到Redshift中进行分析。然而spark-redshift将允许Redshift无缝地操作（通过统一的数据源API）存储在S3、Hive tables、CSV或者存储在HDFS上的Parquet文件。这将使得ETL工作变得简单，用户只需要将注意力放在业务逻辑上，而且这提供了统一的系统视图。
从Redshift读取数据
假如你想要使用spark处理一张完整表（或查询返回大规模数据行），与另一个大数据集比如是hive数据源整合。如下是使用Redshift表查询数据，然后转成DataFrame操作：
val jdbcURL =
"""jdbc:redshift://test-redshift.czac2vcs84ci.
us-east-.:5439/
testredshift?user=redshift&
password= W9P3GC42GJYFpGxBitxPszAc8iZFW"""
val tempS3Dir = "s3n://spark-redshift-testing/temp/"
val salesDF = sqlContext.read
.format("com.databricks.spark.redshift")
.option("url", jdbcURL)
.option("tempdir", tempS3Dir)
.option("dbtable", "sales")
上面命令提供一个DataFrame实例查询Redshift表。使用者只需要提供JDBC URL、用于缓存Redshift表数据的S3上面的临时文件夹以及需要查询表的名字。
在spark中DataFrame实例可注册为临时表，然后直接执行查询操作。
salesDF.registerTempTable("sales_from_redshift")
val newSalesDF = sqlContext.sql("SELECT count(*) FROM sales_from_redshift")
下面使用SQL命令行接口(CLI)可以获取同样的结果：
CREATE TEMPORARY TABLE sales_from_redshift
USING com.databricks.spark.redshift
dbtable 'sales',
tempdir 's3n://spark-redshift-testing/temp/',
url 'jdbc:redshift://test-redshift.czac2vcs84ci.us-east-.:5439/
testredshift?user=redshift&password=W9P3GC42GJYFpGxQtaCBitxPszAc8iZFW');
SELECT count(*) FROM sales_from_
在Spark中，我们将查询的Redshift表注册成一个名为sales_from_redshift的临时表，我们可以直接在这个名字的表上进行查询：
SELECT count(*) FROM sales_from_
在这个语句的背后，spark-redshift执行Redshift的命令来并行地复制Redshift上表的数据，并缓存在用户指定的S3文件夹中。然后使用Hadoop InputFormat API来并行地读取这些存储在S3上面的文件，并将它映射成RDD实例。最后它利用JDBC元数据检索功能来将被检索表的模式生成DataFrame实例。如下图所示：
spark-redshift工具包并不能自动删除存储在S3上面的文件。所以我们建议使用S3上，使得这些临时文件可以在指定时间后自动删除。
写数据到Redshift
spark 数据源API是一个强有力ETL工具。在大数据系统中一种常用操作是从一个系统中获取大规模数据，然后在这些数据上分布式地进行分布式操作，最终存入另一个系统中。例如，一般我们常见的从Hive数据表去读数据，然后将表中的数据复制到Redshift中，以便允许我们进行交互式地处理。spark-redshift工具包特别适合这种应用场景。
假如我们可以直接在Spark环境中读取Hive中表的数据，然后我们需要将这些数据拷贝到Redshift相应的redshift_transaction表中，我们可以进行如下操作：
sqlContext.sql("SELECT * FROM transaction")
.write.format("com.databricks.spark.redshift")
.option("url", jdbcURL)
.option("tempdir", tempS3Dir)
.option("dbtable", "redshift_transaction")
.mode(SaveMode.Overwrite)
使用SQL CLI也可以达到相同的目的，如下：
CREATE TABLE redshift_transaction
USING com.databricks.spark.redshift
dbtable 'redshift_transaction',
tempdir 's3n://spark-redshift-testing/temp/',
url 'jdbc:redshift://test-redshift.czac2vcs84ci.us-east-.:5439/
testredshift?user=redshift&password=W9P3GC42GJYFpGxQtaCBitxPszAc8iZFW')
AS SELECT * FROM transaction;
SaveMode.Overwrite模式，表示如果表存储，直接覆盖。默认在在SQL CLI模式，表示如果表存在，将抛出异常。而SaveMode.ErrorIfExists模式这也是SaveMode.Append模式表示如果表不存在那就创建一张表，否则就追加数据。最后一种模式是SaveMode.Ignore，表示如果不存在表那就创建表，如果表存在，那忽略整个命令操作。
在这个语句的背后，在写数据的时候，spark-redshift工具包将使用JDBC首先在Redshift 中创建表，然后它将分区好的RDD复制到S3临时目录中；最后，它允许Redshift的命令分布式的来将S3上保存的数据复制到Redshift相应的表中。如下图所述：
整合其他数据源
通过spark-redshift工具包读取的数据将自动地转换成DataFrame对象。
spark 核心是大型数据集的抽象。这可以促进数据源之间的互操作性,因为类型自动转换为spark的标准表示(例如StringType DecimalType)
Redshift用户可以将存储在S3上的Redshift表和存储在HDFS上的Hive表、CSV或者Parquet进行Join操作。这个特性对用户来说是非常重要的。
使用spark-redshift
在这表文章介绍spark-redshift，提供一个概述如何解析将Redshift整合到spark数据处理平台。为了尝试使用这些新的特性，或者。我们也听过非常详细的教程。这个教程将会指导您完成创建一个示例Redshift的数据库。然后显示通过spark-redshift包从本机的开发环境如何与Redshift数据库交互操作。
原文来自于：
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1940355次
积分：20548
积分：20548
排名：第316名
原创：205篇
转载：10篇
评论：154条
专注于开发分布式任务调度框架、分布式同步RPC、异步MQ消息队列、分布式日志检索框架、hadoop、spark、scala等技术
写博客很辛苦的，如果我的写的文章能对您有帮助，请您能给点捐助（支付宝账号：），谢谢大家的支持了
个人所有博客，目前四个博客文章同步更新：
(1)(6)(5)(55)(31)(22)(4)(2)(6)(8)(8)(48)(3)(1)(2)(1)(9)(5)
本博客内容，由本人精心整理
欢迎交流，欢迎转载，大家转载注明出处，禁止用于商业目的。3031人阅读
DB2 提供了关连式资料库的查询语言sql(structured query language)，是一种非常口语化、既易学又易懂的语法。此一语言几乎是每个资料库系统都必须提供的，用以表示关连式的操作，包含了资料的定义（ddl）以及资料的处理（dml）。sql原来拼成sequel，这语言的原型以&系统 r&的名字在 ibm 圣荷西实验室完成，经过ibm内部及其他的许多使用性及效率测试，其结果相当令人满意，并决定在系统r 的技术基础发展出来 ibm 的产品。而且美国国家标准学会（ansi）及国际标准化组织（iso）在1987遵循一个几乎是以
ibm sql 为基础的标准关连式资料语言定义。
一、资料定义 DDL（data definition language)
资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。
１、建表格：
create table table_name(
column1 datatype [not null] [not null primary key],
column2 datatype [not null],
datatype --是资料的格式，详见表。
nut null --可不可以允许资料有空的（尚未有资料填入）。
primary key --是本表的主键。
２、更改表格　
alter table table_name
add column column_name datatype
说明：增加一个栏位（没有删除某个栏位的语法。
alter table table_name
add primary key (column_name)
说明：更改表得的定义把某个栏位设为主键。
alter table table_name
drop primary key (column_name)
说明：把主键的定义删除。
３、建立索引　
create index index_name on table_name (column_name)
说明：对某个表格的栏位建立索引以增加查询时的速度。
４、删除　
drop table_name
drop index_name
二、DDL的资料形态 datatypes
smallint 16 位元的整数。
interger 32 位元的整数。
decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点后有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
三、资料操作DML （data manipulation language)
资料定义好之後接下来的就是资料的操作。资料的操作不外乎增加资料（insert)、查询资料（query）、更改资料（update) 、删除资料（delete）四种模式，以下分 别介绍他们的语法：
１、增加资料：
insert into table_name (column1,column2,...)
values ( value1,value2, ...)
1.若没有指定column 系统则会按表格内的栏位顺序填入资料。
2.栏位的资料形态和所填入的资料必须吻合。
3.table_name 也可以是景观 view_name。
insert into table_name (column1,column2,...)
select columnx,columny,... from another_table
说明：也可以经过一个子查询（subquery）把别的表格的资料填入。
２、查询资料：
select column1,columns2,...
from table_name
说明：把table_name 的特定栏位资料全部列出来
from table_name
where column1 = xxx
[and column2 & yyy] [or column3 && zzz]
1.''''*''''表示全部的栏位都列出来。
2.where 之後是接条件式，把符合条件的资料列出来。
select column1,column2
from table_name
order by column2 [desc]
说明：order by 是指定以某个栏位做排序，[desc]是指从大到小排列，若没有指明，则是从小到大排列
组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的表格才能够得到结果的。
from table1,table2
where table1.colum1=table2.column1
1.查询两个表格中其中 column1 值相同的资料。
2.当然两个表格相互比较的栏位，其资料形态必须相同。
3.一个复杂的查询其动用到的表格可能会很多个。
整合性的查询：
select count (*)
from table_name
where column_name = xxx
说明：查询符合条件的资料共有几笔。
select sum(column1)
from table_name
说明：1.计算出总和，所选的栏位必须是可数的数字形态。
2.除此以外还有 avg() 是计算平均、max()、min()计算最大最小值的整合性查询。
select column1,avg(column2)
from table_name
group by column1
having avg(column2) & xxx
说明：1.group by: 以column1 为一组计算 column2 的平均值必须和 avg、sum等整合性查询的关键字一起使用。
2.having : 必须和 group by 一起使用作为整合性的限制。
复合性的查询
from table_name1
where exists (
from table_name2
where conditions )
说明：1.where 的 conditions 可以是另外一个的 query。
2.exists 在此是指存在与否。
from table_name1
where column1 in (
select column1
from table_name2
where conditions )
说明：1. in 後面接的是一个集合，表示column1 存在集合里面。
2. select 出来的资料形态必须符合 column1。
from table_name1
where column1 like ''''x%''''
说明：like 必须和後面的''''x%'''' 相呼应表示以 x为开头的字串。
from table_name1
where column1 in (''''xxx'''',''''yyy'''',..)
说明：in 後面接的是一个集合，表示column1 存在集合里面。
from table_name1
where column1 between xx and yy
说明：between 表示 column1 的值介於 xx 和 yy 之间。
３、更改资料：
update table_name
set column1=''''xxx''''
where conditoins
1.更改某个栏位设定其值为''''xxx''''。
2.conditions 是所要符合的条件、若没有 where 则整个 table 的那个栏位都会全部被更改。
４、删除资料：
delete from table_name
where conditions
说明：删除符合条件的资料。
说明：关于where条件后面如果包含有日期的比较，不同数据库有不同的表达式。具体如下：
(1)如果是access数据库，则为：where mydate&##
(2)如果是oracle数据库，则为：where mydate&cast('''''''' as date)
或：where mydate&to_date('''''''',''''yyyy-mm-dd'''')
在delphi中写成：thedate='''''''';
query1.sql.add(''''select * from abc where mydate&cast(''''+''''''''''''''''+thedate+''''''''''''''''+'''' as date)'''');
如果比较日期时间型，则为：where mydatetime&to_date('''' 10:00:01'''',''''yyyy-mm-dd hh24:mi:ss'''')
审校by lei
--------------
1、组合语句执行
BEGIN&& ATOMIC
表达式1 分号 空格/回车
表达式2 分号 空格/回车
2、应该限制访问权限的表（应该撤销这些表PUBLIC SELECT访问权）
SYSCAT.DBAUTH
SYSCAT.TABAUTH
SYSCAT.PACKAGEAUTH
SYSCAT.INDEXAUTH
SYSCAT.COLAUTH
SYSCAT.PASSTHRUAUTH
SYSCAT.SCHEMAAUTH
比较有用的目录表
SYSCAT.COLUMNS：包含每一行对应于表或视图中定义的列
SYSCAT.INDEXCOLUSE：包含每一行包含的所有列
SYSCAT.INDEXES：包含每一行对应于表或视图中定义的每个索引
SYSCAT.TABLES：所创建每个表，视图，别名对应其中一行
SYSCAT.VIEWS：所创建每个视图对应其中一行或几行
通过索引保持数据唯一性：CREATE UNIQUE INDEX INDEXNAME ON TABLE (COLUMN)
消除重复行：SELECT DISTINCT COLUMN FROM TABLE
3、DB2关于时间的一些函数
得到当前时间的年份、月份、天、小时等等：
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
分别得到当时的日期和时间
DATE (current timestamp)
TIME (current timestamp)
关于时间的一些计算：
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
计算两个日期之间有多少天：
days (current date) - days (date(’′))
得到去除毫秒的当前时间:
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
将时间转换成字符串：
char(current date)
char(current time)
char(current date + 12 hours)
将字符串转换成时间：
TIMESTAMP (’-12.00.00.000000′)
TIMESTAMP (’ 12:00:00′)
DATE (’′)
DATE (’10/20/2002′)
TIME (’12:00:00′)
TIME (’12.00.00′)
注意：在DB2的命令编辑器中可以输入SQL语句和DB2中的内部命令。要想显示当前时间的话，不能直接输入current time，这个只能在SQL语言中引用，要想显示有下面方式：
1） VALUES （current time）
2） SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1
这个与SQL SERVER2000中不一样，在SQL SERVER2000中可以输入Getdate（）得到时间，既可以显示，也可以在语句SQL中用。
4、所有返回前N条数据的表达式
在SQL SERVER2000中使用TOP N 格式
比如： SELECT TOP 10 CARDNO FROM CARD
在DB2中使用fetch first N rows only 格式
比如：SELECT CARDNO FROM SEALCARD fetch first 10 rows only
5、函数使用
查看系统函数： SELECT * FROM SYSibm.
比如：ABS（-89）可以作为值输入到SQL中，但是要想在命令编辑器中显示函数的结果的话可以用下列方式：
1）SELECT ABS(-89) FROM SYSIBM.SYSDUMMY1;
2）VALUES ABS(-89);
6、存储过程
在进行DB2存储过程开发时，我们可以利用很多DB2自带的工具，例如开发中心，控制中心等。但有时使用脚本能带给开发人员更大的灵活性和更高的工作效率。
在开始开发一个新的或修改一个已存在的存储过程时，我们通常会进行下面一些准备工作：
1.&&&&&&&& 查看该存储过程会用到的表的表结构、字段类型、相关索引和示例数据。
2.&&&&&&&& 查看相关的存储过程或用户自定义函数（UDF）的定义。
3.&&&&&&&& 查找无效的存储过程并生成绑定语句。
4.&&&&&&&& 如某个表发生了改变，查看依赖于该表的所有视图、存储过程和用户自定义函数（UDF）
虽然上述信息都可以通过DB2提供的开发工具和管理工具获得,但是通过脚本可以更快速的获得所需信息,并且可以重复执行。
使用脚本完成上述任务的关键是理解和使用DB2的系统表。我们先简单回顾一下有关的DB2的系统表和视图:
1.&&&&&&&& syscat.routines：存储所有的存储过程和用户自定义函数（UDF）信息。其中routinename字段为存储过程或用户自定义函数（UDF）的名称，routinetype字段表示该记录表示的是存储过程（P）还是用户自定义函数（F），lib_id字段为存储过程编译后生成的包序列号，origin字段表示该存储过程或用户自定义函数的出处（Q表示是由SQL组成的，E表示用户定义的且是外部的），valid字段表示该存储过程或用户自定义函数是否有效，如果origin字段不是Q的话，该域为空。
2.&&&&&&&& syscat.packages：存储所有被绑定的程序包。其中pkgname表示包名称，valid字段表示该包是否合法。
3.&&&&&&&& syscat.packagedep：存储关于程序包的依赖关系。其中pkgname字段表示程序包名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
4.&&&&&&&& syscat.routinedep：存储关于程序（routine）的依赖关系。其中routinename字段表示程序名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
5.&&&&&&&& syscat.viewdep：存储了关于视图的依赖关系。其中viewname字段表示视图名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
回顾并了解了上述系统表和视图，我们就可以很容易的创建脚本以完成前面提到的开发存储过程所要做的准备工作。
1． 查看该表结构、字段类型、相关索引和示例数据
虽然我们可以查询sysibm.systables表获得表结构，但有一种更简便的方法来获取表结构，即使用db2look工具。该工具可以生成创建该表和相关的索引的DDL。如我们要获取指定数据库中指定表的结构和前20条数据作为参考，可编写脚本viewtbl.cmd如下，传入参数分别为数据库名和表名。
@echo ------------------ DDL of table %2 and related index(ex) ------------------
@db2look -d %1 -t %2 -e
@echo ------------------ fisrt 20 rows in table %2 ------------------
@db2 select * from %2 fetch first 20 rows only
2．查看已存在的存储过程和用户自定义函数（UDF）的定义，将结果存入文件并自动打开结果文件。
可以从syscat.routines表中做简单查询实现脚本viewrtn.cmd。
@db2 SELECT text FROM SYSCAT.ROUTINES WHERE ROUTINENAME=upper('%1') & %1.sql
@start %1.sql
3．查看所有无效的存储过程并生成绑定语句
删除存储过程引用的表会引起该存储过程无效。无效存储过程可以采用查询syscat.routines和syscat.packages的方法获得：
&&& RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname ,
&&& RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+) AS pkgname
&&& SYSCAT.routines r
&&& r.routinetype = 'P'
&&&&&&& (r.origin = 'Q' AND r.valid != 'Y')
&&&&&&& OR EXISTS (
&&&&&&&&&&& SELECT 1 FROM syscat.packages
&&&&&&&&&&& WHERE pkgschema = r.routineschema
&&&&&&&&&&& AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+)
&&&&&&&&&&& AND valid !='Y'
ORDER BY spname
注意要同时查询syscat.routines和syscat.packages表，因为在程序包无效时syscat.routines中的valid值仍可能为Y。
如果要自动生成重新绑定语句，只需将上述SQL改写并存成invalidSP.cmd：
db2 &SELECT
rebind package '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+)||' resolve any' FROM SYSCAT.routines r WHERE r.routinetype = 'P'
AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS (SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+) AND valid !='Y') )& &rebindsp.bat
4．查看某个表所依赖的视图、存储过程和用户自定义函数（UDF）
使用上述系统视图，我们很容易编写出脚本：
echo --- dependent SPs ---
db2 &select proc.procschema, proc.procname from syscat.routines r, syscat.procedures proc, syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1') and r.specificname=proc.specificname AND pdep.pkgname = 'P'||SUBSTR(CHAR(r.lib_id+)&
echo --- dependent UDF ---
db2 select routineschema, routinename from syscat.routinedep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
echo --- dependent view ---
db2 select viewschema, viewname from syscat.viewdep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
行业借鉴经验：提高DB2存储过程性能和健壮性的3个最佳实践
最佳实践1：在创建存储过程语句中提供必要的参数
创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供这些参数可以提高执行效率。下面是一些常用的参数:
l&&&&&&&& 容许SQL（allowed－SQL）
容许SQL（allowed－SQL）子句的值指定了存储过程是否会使用SQL语句，如果使用，其类型如何。它的可能值如下所示：
NO SQL： 表示存储过程不能够执行任何SQL语句。
CONTAINS SQL： 表示存储过程可以执行SQL语句，但不会读取SQL数据，也不会修改SQL数据。
READS SQL DATA： 表示在存储过程中包含不会修改SQL数据的SQL语句。也就是说该储存过程只从数据库中读取数据。
MODIFIES SQL DATA： 表示存储过程可以执行任何SQL语句。即可以对数据库中的数据进行增加、删除和修改。
如果没有明确声明allowed-SQL，其默认值是MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中NO SQL效率最好，MODIFIES SQL DATA最差。如果存储过程只是读取数据，但是因为没有声明allowed-SQL类型，它会被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其allowed-SQL类型。
l&&&&&&&& 返回结果集个数（DYNAMIC RESULT SETS n）
存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：
在CREATE PROCEDURE 语句的DYNAMIC RESULT SETS子句中声明存储过程将要返回的结果集的数量。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。
使用WITH RETURN子句，在存储过程体中声明游标。
为结果集打开游标。当存储过程返回的时候，保持游标打开。
在创建存储过程时指定返回结果集的个数可以帮助程序员验证存储过程是否返回了所期待数量的结果集，提高了程序的完整性。
最佳实践2：对输入参数进行必要的的检查和预处理
无论使用哪种编程语言，对输入参数的判断都是必须的。正确的参数验证是保证程序良好运行的前提。同样的，在DB2存储过程中对输入参数的验证和处理也是很重要的。正确的验证和预处理操作包括：
如果输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。
根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，NULL与空字符串或0的转换等。
在DB2储存过程开发中，如需要遇到对空(NULL)进行初始化，我们可以使用COALESCE函数。该函数返回第一个非NULL的参数。例如，COALESCE(piName,'')，如果变量piName为NULL，那么函数会返回''，否则就会返回piName本身的值。因此，可以用下面的代码检查piName是否非NULL并且非空字符串：
SET poGenStatus = 0;&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&& SET piName&& =RTRIM(COALESCE(piName, ''));
&&& IF (piName ='')&&
&&& THEN&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&& SET poGenStatus = 34100;&&&&&
&&&&&&& RETURN poGenS&&&&&&&&&&
&&& ENDIF;&&&&&&&
同理，使用COALESCE可以对任何类型的输入参数进行初始化或验证。下面是对参数初始化规则的一个总结：
1.&&&&&& 输入参数为字符类型，且允许为NULL的，如希望缺省值为空字符串，可以使用COALESCE(inputParameter, '')把NULL转换成空字符串;
2.&&&&&& 输入类型为整型，且允许为NULL的，如希望缺省值为0，可以使用COALESCE(inputParameter,0)，把NULL转换成0；
3.&&&&&& 输入参数为字符类型，且不允许是NULL或空字符串的，可以使用RTRIM(COALESCE(inputParameter, ''))把NULL转换成空字符串，然后验证函数返回值是否为空字符串；
4.&&&&&& 输入类型为整型，且不允许是NULL的，不需要使用COALESCE函数，直接使用IS NULL进行验证。
最佳实践3：异常（condition）处理
在存储过程执行的过程中，经常因为数据或者其他问题产生异常（condition）。根据业务逻辑，存储过程应该对异常进行相应处理或直接返回给调用者。此处暂且将condition译为异常以方便理解。实际上有些异常（condition）并非是由于错误引起的。
当存储过程中的语句返回的SQLSTATE
===============审校bylei===============
1.&建立数据库DB2_GCB&
　　CREATE&DATABASE&DB2_GCB&ON&G:&ALIAS&DB2_GCB&
　　USING&CODESET&GBK&TERRITORY&CN&COLLATE&USING&SYSTEM&DFT_EXTENT_SZ&32&
　　2.&连接数据库&
　　connect&to&sample1&user&db2admin&using&8301206&
　　3.&建立别名&
　　create&alias&db2admin.tables&for&sysstat.&
　　CREATE&ALIAS&DB2ADMIN.VIEWS&FOR&SYSCAT.VIEWS&
　　create&alias&db2admin.columns&for&syscat.&
　　create&alias&guest.columns&for&syscat.&
　　4.&建立表&
　　create&table&zjt_tables&as&
　　(select&*&from&tables)&definition&&
　　create&table&zjt_views&as&
　　(select&*&from&views)&definition&&
　　5.&插入记录&
　　insert&into&zjt_tables&select&*&from&&
　　insert&into&zjt_views&select&*&from&&
　　6.&建立视图&
　　create&view&V_zjt_tables&as&select&tabschema,tabname&from&zjt_&
　　7.&建立触发器&
　　CREATE&TRIGGER&zjt_tables_del&
　　AFTER&DELETE&ON&zjt_tables&
　　REFERENCING&OLD&AS&O&
　　FOR&EACH&ROW&MODE&DB2SQL&
　　Insert&into&zjt_tables1&values(substr(o.tabschema,1,8),substr(o.tabname,1,10))&
　　8.&建立唯一性索引&
　　CREATE&UNIQUE&INDEX&I_ztables_tabname&
　　ON&zjt_tables(tabname);&
　　9.&查看表&
　　select&tabname&from&tables&
　　where&tabname='ZJT_TABLES';&
　　10.&查看列&
　　select&SUBSTR(COLNAME,1,20)&as&列名,TYPENAME&as&类型,LENGTH&as&长度&
　　from&columns&
　　where&tabname='ZJT_TABLES';&
　　11.&查看表结构&
　　db2&describe&table&user1.department&
　　db2&describe&select&*&from&user.tables&
　　12.&查看表的索引&
　　db2&describe&indexes&for&table&user1.department&
　　13.&查看视图&
　　select&viewname&from&views&
　　where&viewname='V_ZJT_TABLES';&
　　14.&查看索引&
　　select&indname&from&indexes&
　　where&indname='I_ZTABLES_TABNAME';&
　　15.&查看存贮过程&
　　SELECT&SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)&
　　FROM&SYSCAT.PROCEDURES;&
　　16.&类型转换(cast)&
　　ip&datatype:varchar&
　　select&cast(ip&as&integer)+50&from&log_comm_failed&
　　17.&重新连接&
　　connect&reset&
　　18.&中断数据库连接&
　　disconnect&db2_gcb&
　　19.&view&application&
　　LIST&APPLICATION;&
　　20.&kill&application&
　　FORCE&APPLICATION(0);&
　　db2&force&applications&all&(强迫所有应用程序从数据库断开)&
　　21.&lock&table
　　lock&table&test&in&exclusive&mode&
　　22.&共享&
　　lock&table&test&in&share&mode&
　　23.&显示当前用户所有表&
　　list&tables&
　　24.&列出所有的系统表&
　　list&tables&for&system&
　　25.&显示当前活动数据库&
　　list&active&databases&
　　26.&查看命令选项&
　　list&command&options&
　　27.&系统数据库目录&
　　LIST&DATABASE&DIRECTORY&
　　28.&表空间&
　　list&tablespaces&
　　29.&表空间容器&
　　LIST&TABLESPACE&CONTAINERS&FOR&
　　Example:&LIST&TABLESPACE&CONTAINERS&FOR&1&
　　30.&显示用户数据库的存取权限&
　　GET&AUTHORIZATIONS&
　　31.&启动实例&
　　DB2START&
　　32.&停止实例&
　　db2stop&
　　33.&表或视图特权&
　　grant&select,delete,insert,update&on&tables&to&user&
　　grant&all&on&tables&to&user&WITH&GRANT&OPTION&
　　34.&程序包特权&
　　GRANT&EXECUTE&
　　ON&PACKAGE&PACKAGE-name&
　　TO&PUBLIC&
35.&模式特权&
　　GRANT&CREATEIN&ON&SCHEMA&SCHEMA-name&TO&USER&
　　36.&数据库特权&
　　grant&connect,createtab,dbadm&on&database&to&user&
　　37.&索引特权&
　　grant&control&on&index&index-name&to&user&
　　38.&信息帮助&(?&XXXnnnnn&)&
　　例:?&SQL30081&
　　39.&SQL&帮助(说明&SQL&语句的语法)&
　　help&statement&
　　例如，help&SELECT&
　　40.&SQLSTATE&帮助(说明&SQL&的状态和类别代码)&
　　?&sqlstate&或&?&class-code&
　　41.&更改与&管理服务器&相关的口令&
　　db2admin&setid&username&password&
　　42.&创建&SAMPLE&数据库&
　　db2sampl&
　　db2sampl&F:(指定安装盘)&
　　43.&使用操作系统命令&
　　!&dir&
　　44.&转换数据类型&(cast)&
　　SELECT&EMPNO,&CAST(RESUME&AS&VARCHAR(370))&
　　FROM&EMP_RESUME&
　　WHERE&RESUME_FORMAT&=&'ascii'&
　　要运行&DB2&Java&存储过程或&UDF，还需要更新服务器上的&DB2&数据库管理程序配置，以包括在该机器上安装&JDK&的路径&
　　db2&update&dbm&cfg&using&JDK11_PATH&d:\sqllib\java\jdk&
　　TERMINATE&
　　update&dbm&cfg&using&SPM_NAME&sample&
　　46.&检查&DB2&数据库管理程序配置&
　　db2&get&dbm&cfg&
　　47.&检索具有特权的所有授权名&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'DATABASE'&FROM&SYSCAT.DBAUTH&
　　UNION&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'TABLE&'&FROM&SYSCAT.TABAUTH&
　　UNION&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'PACKAGE&'&FROM&SYSCAT.PACKAGEAUTH&
　　UNION&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'INDEX&'&FROM&SYSCAT.INDEXAUTH&
　　UNION&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'COLUMN&'&FROM&SYSCAT.COLAUTH&
　　UNION&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'SCHEMA&'&FROM&SYSCAT.SCHEMAAUTH&
　　UNION&
　　SELECT&DISTINCT&GRANTEE,&GRANTEETYPE,&'SERVER&'&FROM&SYSCAT.PASSTHRUAUTH&
　　ORDER&BY&GRANTEE,&GRANTEETYPE,&3&
　　create&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&&
　　insert&into&yhdab&values&
　　('','123456','user01',''),&
　　('','123456','user02','');&
　　49.&业务类型说明&
　　insert&into&ywlbb&values&
　　('user01','业务申请'),&
　　('user02','业务撤消'),&
　　('user03','费用查询'),&
　　('user04','费用自缴'),&
　　('user05','费用预存'),&
　　('user06','密码修改'),&
　　('user07','发票打印'),&
　　('gl01','改用户基本信息'),&
　　('gl02','更改支付信息'),&
　　('gl03','日统计功能'),&
　　('gl04','冲帐功能'),&
　　('gl05','对帐功能'),&
　　('gl06','计费功能'),&
　　('gl07','综合统计')&
　　二.&目录视图说明
说明&&&目录视图&
检查约束&&&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)&字符串。&
　　字符大对象&(CLOB)&字符串，它的字符序列可以是单字节字符或多字节字符，或这两者的组合。&
　　双字节字符大对象&(DBCLOB)&字符串，它的字符序列是双字节字符。&
　　四.&数据库范式
　　第一种规范形式：表中的每一行和每一列均有一个值，永远不会是一组值。&
　　第二种规范形式：不在关键字中的每一列提供取决于整个关键字的事实。&
　　第三种规范形式：每个非关键字列提供与其他非关键字列无关并只取决于该关键字的事实。&
　　第四种规范形式：没有行包含有关一个实体的两个或更多个独立的多值事实。&
　　五.&数据类型
数据类型&类型&&特性&&示例或范围&
CHAR(15)&定长字符串&最大长度为&254&&&'Sunny&day&'&
VARCHAR(15)&变长字符&最大长度为&4000&&&'Sunny&day'&
SMALLINT&&数字&长度为&2&字节精度为&5&位&&范围为-32768&至&32767&
INTEGER&&数字&长度为&4&字节精度为&10&位&&范围为-&至&&
REAL&&数字&单精度浮点32&位近似值&&范围为-3.402E+38至-1.175E-37或&1.175E-37&至-3.402E+38或零&
DOUBLE&&数字&双精度浮点64&位近似值&范围为-1.79769E+308&至-2.225E-307或&2.225E-307&至&1.79769E+308或零&
DECIMAL(5,2)&数字&精度为&5小数位为&2&范围为&-10**31+1&至&10**31-1&
DATE&日期时间&&三部分&#2-10-27&&&&
TIME&日期时间&&三部分&#2.05&&&&
TIMESTAMP&日期时间&&七部分值&&&-13.30.05.000000&
六.&列函数&
　　列函数对列中的一组值进行运算以得到单个结果值。下列就是一些列函数的示例。&
　　返回某一组中的值除以该组中值的个数的和&
　　COUNT&
　　返回一组行或值中行或值的个数&
　　返回一组值中的最大值&
　　返回一组值中的最小值&
　　七.&标量函数&
　　标量函数对值进行某个运算以返回另一个值。下列就是一些由DB2&通用数据库提供的标量函数的示例。&
　　返回数的绝对值&
　　返回值的十六进制表示&
　　LENGTH&
　　返回自变量中的字节数（对于图形字符串则返回双字节字符数。）&
　　抽取日期时间值的年份部分&
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：35684次
排名：千里之外
原创：20篇
转载：11篇
(4)(1)(1)(2)(3)(3)(1)(3)(4)(10)}