使用 Sqoop 操作 DB2 服务器笔记

DB2 10.1 部署

操作系统:centOS 7
DB2 版本:10.1

一、解压 tar 文件

$ tar -zxvf db2_v101_linuxx64_expc.tar.gz

解压之后,生成一个 expc 的目录文件。

$ ls
db2  db2ckupgrade  db2_deinstall  db2_install  db2ls  db2prereqcheck  db2setup

二,检验安装的先决条件

$ ./db2prereqcheck

将缺失的依赖安装好。

三、进行安装

./db2_install

刚开始的时候,需要输入两次yes,同意协议,同意安装路径。
之后就都自动了。
接下来创建用户组。

$ groupadd -g 2000
$ groupadd -g 2001
$ useradd -m -g db2iadm1 -d /home/db2inst1db2inst1
$ useradd -m -g db2fadm1 -d /home/db2fenc1 db2fenc1
$ passwd db2inst1

密码输入后自己记录下来。

四、注册

$ cd /opt/ibm/db2/V11.1/adm/
$ chmod -R 775 *

这里是自己的软件安装目录。

$ ./db2licm -a /home/software/expc/db2/license/db2expc_uw.lic

LIC1402I  License added successfully.
LIC1426I  This product is now licensed for use as outlined in your License Agreement.  USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V11.1/license/zh_CN.utf8"

五、绑定端口号

$ cd /opt/ibm/db2/V11.1/instance/
$ chmod -R 775 *
$ ./db2icrt -p 50000 -u db2fenc1 db2inst1
DBI1446I  The db2icrt command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)
Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end
Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end
Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end
The execution completed successfully.
For more information see the DB2 installation log at "/tmp/db2icrt.log.5871".
DBI1070I  Program db2icrt completed successfully.

六、完成安装

要切换到实例用户,才可以登录db2数据库。

$ su - db2inst1

创建样本数据库。

$ db2sampl
Starting the DB2 instance...
Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Creating tables with XML columns and XML data in schema "DB2INST1"...
Stopping the DB2 instance...
'db2sampl' processing complete.
$ db2start
SQL1063N  DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server        = DB2/LINUXX8664 11.1.2.2
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE
$ db2 "select * from staff"
$ db2licm -l
Product name:                     "DB2 Express-C"
License type:                     "Unwarranted"
Expiry date:                      "Permanent"
Product identifier:               "db2expc"
Version information:              "11.1"
Max number of CPUs:               "2"
Max amount of memory (GB):        "16"
Max number of cores:              "2"
Enforcement policy:               "Soft Stop"

七、配置 ODBC 连接

添加DB2端口在文件。

$ vim /etc/services

加入下面一行:

db2inst  50000/tcp

添加 DB2 配置。

$ su - db2inst1

下面都是以 用户db2inst1 来运行。

$ db2set DB2_EXTENDED_OPTIMIZATION=ON
$ db2set DB2_DISABLE_FLUSH_LOG=ON
$ db2set AUTOSTART=YES
$ db2set DB2_STRIPED_CONTAINERS=ON
$ db2set DB2_HASH_JOIN=Y
$ db2set DB2COMM=tcpip
$ db2set DB2_PARALLEL_IO=*
$ db2set DB2CODEPAGE=819#Update dbm cfg

配置 TCP/IP Service name。

$ db2 update dbm cfg using SVCENAME db2inst
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully. 
$ db2 update dbm cfg using INDEXREC ACCESS
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

在 ibm 官网下载 DB2 客户端工具:
ibm_data_server_client_winx64_v11.1.zip
解压后进行安装
然后在 Widows 端打开 ODBC 数据源管理程序,进行以下图示操作:







连接成功,之后可以通过 ODBC 对所部署的 DB2 服务进行操作。

sqoop 部署

操作系统:centOS 7
DB2 版本:1.4.7

一、解压 tar 文件

$ tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz

二、配置环境变量

$ vim /etc/profile
export SQOOP_HOME=/opt/sqoop-1.4.7.bin__hadoop-2.6.0
export CLASSPATH=$CLASSPATH:$SQOOP_HOME/lib
export PATH=$PATH:$SQOOP_HOME/bin
$ source /etc/profile

三、导入 JDBC 驱动包

导入 JDBC 驱动 jar 包。
将 db2jcc.jar、db2jcc4.jar、db2jcc_license_cu.jar 包导入到 /usr/share/java/ 或者 /opt/sqoop-1.4.7.bin__hadoop-2.6.0/lib 路径下。

四、验证是否连接成功

执行命令:

$ sqoop list-databases --connect jdbc:db2://localhost:50000/sample --username db2inst1 -P

输入 db2inst1 用户对应的密码。

Warning: /opt/cloudera/parcels/CDH-5.14.0-1.cdh5.14.0.p0.24/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
19/01/22 13:55:59 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.0
Enter password: 
19/01/22 13:56:03 INFO manager.SqlManager: Using default fetchSize of 1000
DB2INST1
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS

终端显示出 SAMPLE 下所有的数据库列表,说明 sqoop 连接 DB2 服务器成功,接下来可以使用 sqoop 对 DB2 服务器的数据进行各种操作。