【IBM Power Salon】松沢書店様が語る「IBM i エンジニア不在でもDXはできる」実践編その5

第5回：複数のDBをSQLServerのリンクサーバーを用いてまとめよう

日本アイ・ビー・エム株式会社が毎月開催しているIBM Powerユーザーのための自由な語り場「IBM Power Salon」(月1回、第二水曜日の朝9時から開催)をご存じでしょうか？
https://www.ibm.com/blogs/systems/jp-ja/ibm-power-salon/

2022年12月14日に開催された第13回の、株式会社松沢書店様による「IBM i エンジニア不在でもDXはできる」では、既存IBM i環境を見事に活用し、DXを実現された素晴らしい事例が披露されました。
https://video.ibm.com/recorded/132400405

とはいえ、１時間の講演時間では語りきれなかった詳細は、きっと他のIBM i ユーザーの方にも参考になるはず！ということで、松沢書店　システム部部長の山口様に課題解決の方法について具体的にご説明いただきます。

第5回目の今回は社内に点在する複数のデータベースをあたかもひとつのデータベースとして使えたら、という誰しもが抱える課題解決の事例をご紹介します。ここでは、マイクロソフト社のSQLServerのリンクサーバーを使ってお馴染みDb2 for i をOracle、MySQLなどの他データベースと結合する方法を解説いただきました。有償のETLツールを購入していない方も採用できる事例として多くの方の参考になるのではないでしょうか？また具体的な設定についてご説明頂いておりますので、過去リンクサーバーでつまずいた方も是非ご一読ください。

第5回：複数のDBをSQLServerのリンクサーバーを用いてまとめよう

株式会社松沢書店
システム部
山口昌一

目的と経緯

弊社では、様々な事情（事情は割愛）により様々なDBが存在しております。SQLServer、MySQL、Oracle、そしてDb2 for i（旧DB/400）という具合です。ここまでバラバラは珍しいかもしれませんが、２種類くらいある状況は少なくないかと思います。そういった状況ではDb2 for iのデータとMySQLのデータを結合したいな、といった事があるかと思います。そんな時便利なのがSQLServerのリンクサーバーです。この機能を使えば前述した全てのDBのテーブル同士を簡単に結合することができます。リンクサーバーは簡単な設定で作成可能ですので、各DBのリンクサーバーの作成方法、OPENQUERY関数を使用した、割と速度が出る結合方法をご説明していきます。
※SQLServerが有る事が必須条件になります。無い場合は無料版もありますのでダウンロードしてみてください。

1.概略図

▲図1. リンクサーバーのイメージ

2. リンクサーバーの作成

それでは図１の環境を一つずつ作成していきます。せっかくですのでDb2 for iから作成していきましょう。

● リンクサーバーを使用するにあたって

ODBCが必要になりますので、
https://iworldweb.info/column/serials/dekiruibmi_no8
iWorld Webのこちらの記事を参考にインストールします。こちらの記事の「4. WindowsにODBCデータソースを追加する」を行い、そこで設定した「データソースネーム」を控えておきます。

● SQL Server Management Studioを開きます。

オブジェクトエクスプローラーから、「サーバーオブジェクト」を展開し「リンクサーバー」で右クリックすると「新しいリンクサーバー」が表示されますのでクリックします。

黄色点線の中を上から入れていきます
「リンクサーバー」ここは任意の名前になります。「TESTIBMI」にしてみました。
「プロバイダー」こちらはドロップダウンリストから「Microsoft OLE DB Provider for ODBC Drivers」を選択します。
「データソース」はODBCの設定時に控えたデータソースネームを入れます。
全て入れたら「OK」をクリックします。
この時点でエラーが出る場合がありますがそのまま作成します。

上記の手順で図の様に「TESTIBMI」というリンクサーバーが作成されました。
再度この「TESTIBMI」を右クリックし「プロパティ」をクリックします。

画面左上「セキュリティ」をクリックし、右下の「このセキュリティコンテキストを使用する」をチェックし、「リモートログイン」「パスワード」を入力します。こちらはIBM i にログインする際のユーザー名とパスワードになるのでそちらを入力してください。
入力したら「OK」をクリックしてめでたく作成完了になります。

上図の様に「TESTIBMI」を展開していくと「S780～」というデータベース名（名前は環境によって異なります）が出てきます。

● データ抽出

それでは早速SQL文を書いてデータを抽出していきます。
FROM句にはルールがありますので以下の様に記述いたします。
SSMS上で「新しいクエリ」をクリックし以下を記述し実行します。

SELECT * FROM TESTIBMI.S780～. ライブラリ名.ファイル名

これでデータの抽出ができる様になりました。
ただ、1つのリンクサーバーではありがたみがないので、続いてORACLEもリンクサーバーにしていきます。

○ Oracle Clientのインストール

※弊社環境はoracle12c　バージョンが古いですが、適宜読み替えて頂ければ幸いです。oracleのリンクサーバーはつまずきやすい所が多くググってもあまり出てきませんので参考になれば幸いです。

ORACLEもODBCが必要になりますのでoracle clientをインストールしていきます。 (oracleからダウンロードしてください。)

「カスタム」を選択し次へ

そのまま「次へ」

上図でチェックしているもの全てチェックしてください。漏れがあるとなんらかのエラーが出る可能性があります。

「インストール」

インストールが済むと
C:\app\ユーザー名\product\12.1.0\client_1
上記に各種ファイルが置かれます。この配下に
「network」というフォルダがあるのでこの下に
「admin」というフォルダを作ります。
更ににその下に「tnsnames.ora」というファイルを作成します。

上記の様に「IBMi」の部分はODBCのDSNになりますのでDSNに設定したい名前を入れてください。 IPはORACLEがあるサーバーのIPを入れます。

● システム環境変数を設定

さきほど配置したファイルを環境変数に設定します。
WINDOWSの検索窓で「システム環境」と入れます。

「環境変数」をクリックします。

画面下半分の方の「新規」をクリックします。

「TNS_ADMIN」という名前で先ほどのファイルがあるPATHを変数値に入力して「OK」をクリックします。

● ODBCデータソースの作成

WINDOWSの検索窓で「ODBC」と入力します。

「追加」をクリックします。

「Oracle in OracleClient12Home1」を選び「完了」

黄色の箇所を上から入力していきます。
「データソース名」　任意で分かり易い名前を付けてください。
「TNSサービス名」　IBMIこちらは先ほど設定した「tnsnames.ora」ファイルで記述した名前にします。リンクサーバーでもこの名前で接続します。
「ユーザーID」こちらはORACLEに接続するユーザーIDを入れて下さい。

● SQL Serverでの設定