IBM dashDB 2014 年发布,是基于 IBM DB2 数据库的 BLU Acceleration 内存计算技术的数据仓库、及分析解决方案。在 dashDB 家族中,有两个版本,一个是 IBM dashDB for Analytics (数据仓库),另一个是 dashDB Local (一个可用于部署在私有云上的 Docker 容器)。IBM Cloud 平台现在提供的 IBM Db2 Warehouse on cloud 的服务,即是之前的 IBM dashDB for Analytics 服务。
本文将根据一个实际的例子,讲述如何从 Db2 上 "复制" 一个数据库到 IBM Db2 Warehouse on cloud 或 dashDB Local 中。如果复制的步骤对两者有不同,我会分别指出。
本文中,我选用了 IBM Cloud 平台提供的 IBM Db2 Warehouse on cloud 的服务的入门级别的免费服务。它虽然有一定的限制,比如不能创建数据自己的模式,只能单一用户访问。但是这并不影响对数据库过程中关键步骤的理解。
IBM dashDB 提供了三个客户端,一个是网页客户端,即 web Console,一个是 Command line processor plus,简称 CLPplus,另一个是 IBM Data Server Client 中的 Command line processor,简称 CLP。Web Console 可以支持绝大多数 SQL/DDL 的执行,例如 Insert, Update, Delete,Select 语句。 CLPplus 则功能更加强大,在连接到数据库后,除了可以批量处理 SQL,DDL 语句之外,更可以执行一些 SQL Procedure,从本地导入空间位置位置信息。CLP 则可以支持从本地导入图片数据到 dashDB 中,而不必将图片数据放置在远程的 dashDB 容器内。
我所需要复制的 IBM Db2 数据库中,主要有以下四大部分: 1) DDL & SQL,用来创建表,索引,主键,填充数据; 2) 图片信息; 3) 空间位置数据信息; 4) 普通表单数据。 下面我将依次对这几种类型的数据在数据库复制的过程中所需要留意的地方,分别进行介绍。
对于创建数据库表,索引,主键以及增删查改等 SQL 语句,绝大部分 Db2 的 SQL 都可以无缝迁移到 dashDB 中来使用,但是有下面几个地方需要注意:
1. 在 IBM dashDB for Analytics 入门级别的免费服务中,是不支持自定义数据库模式的。而默认登录 IBM Db2 Warehouse on cloud 务的用户名就是就是在 IBM Db2 Warehouse on cloud 中创建表的模式名。比如登录用户是:dash13460,那么所有创建的数据库表都在 dash13460 这个模式下。如果需要自定义模式,或者需要更多的模式,可以升级 IBM Db2 Warehouse on cloud 的服务到进阶级别。
2. 因为 IBM dashDb 中要求使用按行组织的表,所以 DDL 中,我们需要在 Create Table 语句后添加上 ORGANIZE BY ROW。例如:
- CREATE TABLE MySchema.Table1 (
- NAME VARCHAR(1024) NOT NULL,
- ID CLOB,
- UID VARCHAR(128) NOT NULL,
- LASTUPDATEDATE TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
- )
- DATA CAPTURE NONE;
修改为:
- CREATE TABLE MySchema.Table1 (
- NAME VARCHAR(1024) NOT NULL,
- ID CLOB,
- UID VARCHAR(128) NOT NULL,
- LASTUPDATEDATE TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
- )
- ORGANIZE BY ROW
- DATA CAPTURE NONE;
3. REORG TABLE 命令以及 RUNSTATS 命令都需要做一些变更处理。在 IBM dashDB local 环境中,需要调用 ADMIN_CMD 过程来实现,同时因为登录用户名即是模式名,所以对象表的名字不再需要是 [模式名].[表名] 的格式,而是直接使用 [表名],例如:
reorg Table MySchema.Table1
修改为:
Call Sysproc.ADMIN_CMD('reorg Table"Table1"');
RUNSTATS ON TABLE MySchema.Table1
修改为:
Call Sysproc.ADMIN_CMD('RUNSTATS ON TABLE"Table1"');
对于 IBM Db2 Warehouse on cloud 服务来说,以上 ADMIN_CMD 过程不能在 Web Console 端执行,所以需要使用 CLPplus 客户端连接 IBM Db2 Warehouse on cloud 服务实例后再执行。
4. 当通过 dashDb Web Console 执行 DDL/SQL 语句的时候,需要留意当前脚本的终止符是什么。常见的有 ";" 或者 "@"。默认 dashDb Web Console 使用的是 ";" 作为终止符。
如果所开发的应用是需要基于空间位置位置信息的服务,那么 dashDb 这里同样可以提供相关的支持。下面我就以一个例子来介绍如何在 dashDB 中创建 Spatial Reference System(简称 SRS) 并且导入 Shapefile. 目前在 dashDB local 环境和 IBM Db2 Warehouse on cloud 两个环境中,导入的步骤有些不同,下面我将分开进行介绍。
1. 在 IBM Db2 Warehouse on cloud 中通过 CLPplus 来创建 SRS 以及导入 Shapefile。
a) 先通过 CLPplus 连接远程的 dashDB 服务实例。
clpplus -nw dash13460@mydb_bx
b) 然后用下面的命令来创建需要的 SRS。
call db2gse.ST_create_srs('WGS84_AUX',300,-180,100000,-180,null, null, null, null, null, 'GCS_WGS_1984_MAJOR_AUXILIARY_SPHERE', 'MAJOR_AUXILIARY_SPHERE',?,?);
然后把我们准备好的 *.shp, *.dbf, *.prj and *.shx
放在一个子目录中,并且将这四个文件直接打包压缩生成一个 *.zip
文件。注意,不要将这四个文件所在的子目录直接打包压缩。因为这样会导致 *.zip 文件上传到远程 IBM dashDB for Analytics
on IBM Cloud 服务端后,解压缩解析失败。例如在 Linux 下,用下述命令来生成一个压缩包。
zip c_01oc20.zip c_01oc20.shp
c_01oc20.dbf c_01oc20.prj c_01oc20.sh
c) 用下面的命令将本地的打包文件上传至远程,供远程 dashDB 服务解析和导入。例如:
ida loadgeospatialdata file /tmp/shapefile/c_01oc20/c_01oc20.zip table US_County mode new column LOCATION srsname NAD83_SRS_1
上传的速度取决于文件大小以及带宽,或许会比较慢。
关于 ida loadgeospatialdata 的具体命令参数的说明,请参考: 参数说明
d) 当空间位置位置信息导入完成后,可以通过 dashDB for Analytics on IBM Cloud 的 Web console 中的 Explore -> <Your Schema> -><Your Table> 来查看刚才导入的数据。
2. 在 IBM dashDB local 中通过 Web Console 来导入空间位置信息数据。如果是使用 IBM dashDB local 服务,那么你可能拥有更大的用户权限。那么这里可以通过 IBM dashDB local 的 Web Console 来直接导入空间位置信息。
a) 以 Administrator 权限身份登录 IBM dashDB local Web console,选择 Load -> Load Geospatial Data 来导入数据。
b) 这里我们同样需要把需要导入的 *.shp, *.dbf, *.prj and *.shx 放在一个自目录中,并且将这四个文件直接打包压缩生成一个 *.zip 或 *.tar.gz 或 *.tar 文件。例如:
tar -cvf c_01oc20.tar c_01oc20.shp c_01oc20.dbf c_01oc20.prj c_01oc20.shx
c) 然后将生成的文件拖放到界面中的指定区域(红框中的区域)。如图 3 所示:
文件上传进度会显示在界面上,如图 4 所示:
文件上传完成后,IBM dashDB local 会自动检测一下上传的文件,如图 5 所示:
d) 下一步可以选择是否在既有的数据库表内附加数据,或者选择创建一个新的表格来盛入数据。这里选择创建一个新的表格。如图 6 所示:
e) 下一步需要制定新创建表格的名字以及保存空间信息的列名。例如:我们创建一个名为 MY_ZONE 的表格,其中使用 MY_LOCATION 的字段来保存导入的空间信息。如图 7 所示
f) 下一步指定一个与导入空间信息兼容的 SRS 系统,如图:
g) 最后一步点击完成,结束空间信息的导入。界面上会显示空间信息导入的一个结果统计信息。
当我们开发一个应用的时候,有时需要在数据库中保存一些图片 / 图标文件。对于 IBM Db2 Warehouse on cloud 服务或者 IBM dashDB local 服务来说,因为受限于不同的容器访问权限,所以这里提供了两种向 dashDB 数据库内导入图片 / 图标文件的方法。
1. 向 IBM Db2 Warehouse on cloud 中导入图片。
向 IBM Db2 Warehouse on cloud 中导入图片时,默认需要图片文件在 dashDB 所在容器本地目录中的。所以这也是为什么直接在连接 IBM Db2 Warehouse on cloud 服务的 CLPplus 客户端中,执行 import from *.del of del lobs from <dir> insert into <Table Name> 命令时,就会返回如下的错误:
- IMPORT FROM /mnt/clientdir/clienthome/db/del/multi_geometry_icons_dark.del of del lobs from
- /mnt/clientdir/clienthome/db/images/multi_geometry_icons/dark insert into
- dash13460.IMAGE
- SQL3235N The utility cannot use the "LOB" path
- "/mnt/clientdir/clienthome/db/images/multi_geometry_icons/dark" parameter as
- specified. Reason code: "3".
为了解决这样的错误,我们需要使用 IBM Data Server Client, 因为它包含了 Command line processor(CLP)。而 IBM Data Server Client 又被包含在 IBM Db2 Warehouse client 容器中,所以我们需要有一台 Linux 机器,配置好 Docker 的运行环境,然后从 Docker Store 中下载 IBM Db2 Warehouse client 的 Docker 容器镜像。下载地址可以从这里找到: 下载地址
具体启动容器信息可以参考: 容器信息
a) 使用下面的命令来启动这个容器, 等待一段时间直到这个容器完全启动。
docker run -dit --net=host --privileged=true -v /mnt/tools:/mnt/clientdir --name=client -e REMOTE_DB=dashdb-entry-yp-dal09-08.services.dal.bluemix.net:dash13460:<Your dashDB password> store/ibmcorp/db2wh_ce:v2.1.0-db2wh_client-linux
b) 将需要导入的 *.del 文件以及图片 / 图标文件放入 Linux 机器本地的 / mnt/tools/clienthome 目录,并且赋予文件以及子目录 644 的权限。
c) 运行 docker exec -it client cli 进入到容器的交互模式。
如果不知道已创建的 Db2 连接的名字,可以通过在容器内执行下面的命令获得。
- [dashDB Local - Client CLI ~]\> db2 list db directory
- System Database Directory
- Number of entries in the directory = 1
- Database 1 entry:
- Database alias = BLUDB_78
- Database name = BLUDB
- Node name = DB2WH_24
- Database release level = 14.00
- Comment =
- Directory entry type = Remote
- Catalog database partition number = -1
- Alternate server hostname =
- Alternate server port number =
或者在宿主机通过 docker logs --follow <容器名> 来查看容器启动的日志,连接名会显示在其中。然后使用下述命令连接远程的数据库:db2 connect to <dsn alias> user <dashDB user name>,然后输入 dashDB 访问密码。
d) 连接成功后,使用下面的命令来导入图片 / 图标文件。
IMPORT FROM <the *.del file directory in container> of del lobs from <the image file directory in container> insert into <Schema Name>.<Table name>
例如:IMPORT FROM /mnt/clientdir/clienthome/db/del/multi_geometry_icons_dark.del of del lobs from /mnt/clientdir/clienthome/db/images/multi_geometry_icons/dark insert into dash13460.IMAGE
由于我们是将图片 / 图标文件从本地上传到远程的服务器上,所以图片 / 图标文件的导入速度取决于文件的大小,数量以及带宽。
- SQL3110N The utility has completed processing. "7" rows were read from the
- input file.
- SQL3221W ...Begin COMMIT WORK. Input Record Count = "7".
- SQL3222W ...COMMIT of any database changes was successful.
- SQL3149N "7" rows were processed from the input file. "7" rows were
- successfully inserted into the table. "0" rows were rejected.
- Number of rows read = 7
- Number of rows skipped = 0
- Number of rows inserted = 7
- Number of rows updated = 0
- Number of rows rejected = 0
- Number of rows committed = 7
这里需要注意的是,每创建一次数据库连接时,容器都会把数据库连接信息记录在 / mnt/tools/clienthome/db2inst1 目录下。所以如果有需要创建一个新的数据库连接,那么请在容器启动的时候挂载到一个新的目录,比如 / mnt/new_folder/. 否则在容器启动时,容器启动日志中会报告 db2 client instance 启动失败的错误。
2. 向 IBM dashDB local 中导入图片
如果我们需要对 IBM dashDB local 中导入图片 / 图标数据,在我们可以直接访问 dashDB local 容器的前提下,我们把图片 / 图标数据直接复制到 dashDB local 的容器中, 然后通过 dashDB Local Web console 执行相关导入命令来导入图片 / 图标信息。
鉴于 dashDB local 容器启动时会挂载本地目录,那么可以将此本地目录作为中转目录。将需要导入的图片 / 图标文件以及对应的 *.del 文件放入。并且确保在 dashDB local 容器的用户对于这些文件和子目录有读写权限。那么就可以在 dashDB local Web Console 中执行 call Sysproc.ADMIN_CMD 管理过程来导入数据。例如:
call Sysproc.ADMIN_CMD('IMPORT FROM /opt/IBM/db/del/multi_geometry_icons_dark.del of del lobs from /opt/IBM/db/images/multi_geometry_icons/dark insert into MySchema.IMAGE');
IBM dashDB local 以及 IBM Db2 Warehouse on cloud 服务中,都支持通过 csv 文件向数据库中导入数据。
在 Db2 本地环境中,我们可以通过下面的语句来导出指定表格中的数据到 CSV 文件中:
db2 "export to mytable.csv of del select * from MySchema.mytable"
然后在 dashDB Web Console 中,选择 Load -> Load From File
IBM dashDB local 和 IBM Db2 Warehouse on cloud 的界面会略有不同,但是主要步骤都是一样的。
1. 上传 CSV 文件,例如:
2. 选择要导入数据的对象表。可以向既有的表中载入数据,也可以新创建表来载入数据。这里以创建新表为例,例如:
3. 进行一些必要的字段设定,例如日期格式,时间格式,以及时间戳格式。如果是新创建一个表,那么需要对列名进行一个设定。
4. 接下来就可以导入数据了。
5. 数据导入完成后,界面上可以看到这次数据导入的结果。
至此,以上介绍了当我们要从 IBM Db2 环境下,复制一个数据库到 dashDB local 或者 IBM Db2 Warehouse on cloud 服务中,常遇到的一些问题,以及在导入空间信息以及图片 / 图标信息时候,应当如何应对,希望能对您有所帮助。
本文中用到的参考资料,请从下面列表中获取:
来源: http://www.ibm.com/developerworks/cn/analytics/library/ba-cn-db2-to-dashdb/index.html