NEWS
IBM i 技術解説 IBM i 技術解説
2024.01.18
SHARE
  • twitter
  • facebook
  • hatena

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

【IBM Power Salon】松沢書店様が語る <br />「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

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

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

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


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

目的と経緯

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

1.概略図

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

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

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

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

ODBCが必要になりますので、
https://www.i-cafe.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での設定

SSMSを開き、IBMiを登録した時と同様に「リンクサーバー」で右クリック「新しいリンクサーバー」をクリックします。

黄色の枠上から

  • 「リンクサーバー」 こちらは任意で分かり易い名前を付けて下さい。
  • 「プロバイダー」 こちらは「Oracle Provider for OLE DB」
  • 「データソース」 こちらはODBCの設定で付けたDSNではなく、TNSサービス名を入れて下さい。

次に画面左上「セキュリティ」をクリックし右下の「このセキュリティコンテキストを使用する」にチェックを入れ、リモートログイン、パスワードにORACLEのユーザーID、パスワードを入力し「OK」をクリックします。

上図の様に「ORAIBMI」という新しいリンクサーバーが作成されました。

● ドメインに参加している場合

上記手順でSQLを実行した際にエラーが出る場合があります。
ドメインに参加しWINDOWS認証でログインしている場合以下を試してみてください。

WINDOWS検索窓から
「SQL Server 2022 構成マネージャー」を開きます。

「SQL Serverのサービス」右画面「SQL Server(SQL~」を右クリックして「プロパティ」をクリック

「アカウント名」に 「ドメイン名¥ユーザー名」を入力し「パスワード」はそのユーザーのパスワードを入力してください。

○ SQLServerでのSQL文の実行

になります。

注意が必要な箇所はORAIBMIとスキーマ名の間で「..」ドットが2個続きます。

● 異なるDB同士でJOINする。

上記の様なSQLで異なるDB同士を結合することが可能です。

● MySQLもリンクサーバーにする。

※弊社環境 MySQL version 5.7.21 こちらも古くてすみません。

● ODBCの設定

MySQL ODBCドライバを公式よりダウンロードします。
https://dev.mysql.com/downloads/connector/odbc/

古いMySQLの場合は上記サイトから「Arcives」のタブをクリックしてください。

バージョンを任意のものに選択します。弊社では「5.3.13」にしました。 ダウンロードしたらインストールします。インストールは特に考える必要はないので「TYPICAL」を選んで次へ次へで終わらせます。

WINDOWSの検索窓に「ODBC」と打ち込んでデータソースを追加していきます。

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

「MySQL ODBC 5.3 ANSI Driver」を選びます。

黄色の枠を上から記入していきます。

  • 「Data Source Name」こちらは任意で分かり易い名前を付けて下さい。
  • 「TCP/IP Server」こちらはMySQLのあるサーバーのIPを指定してください。
  • 「User」「Password」はMySQLのものを入力してください。

誤りが無ければ「Database」に登録されたDatabaseが表示されますので、接続したいDatabaseを選びます。

SQLServer側の設定

再度「新しいリンクサーバー」をクリックします。

黄色の枠を上から記入していきます。

  • 「リンクサーバー」任意の分かり易い名前を付けてください。
  • 「プロバイダー」 Microsoft OLE DB Provider for ODBC Drivers
  • 「データソース」 こちらは先ほど設定したODBCのDSNを入れます。
  • 「プロバイダー文字列」 
    少し長いですが上記の「」を置き換えて入力してください。

画面左上「セキュリティ」をクリック、右下の「このセキュリティコンテキストを使用する」にチェック、「リモートログイン」「パスワード」はMySQLのユーザー、パスを入力してください。

「サーバーオプション」をクリックし、「RPC」「RPC出力」を「True」にします。
最後に「OK」

MYSQLのリンクサーバーは作成されておりますが、最後に「リンクサーバー」「プロバイダー」「MSDASQL」を右クリックします。「プロパティ」をクリックすると以下画面になります。

「レベル0のみ」にチェックを入れます。

これで設定完了です。お疲れ様でした。

SQLServerでSQL文の実行

今度はリンクサーバー名とテーブル名の間のドットが3つになります。
カタログはリンクサーバーの設定時に指定したカタログになるためなにも指定しません。

上記の様にちゃんとデータが取れています。

● OPENQUERYを使用したデータ取得

テーブルの指定で先ほどの様に、[リンクサーバー名].[カタログ名].[スキーマ名].[テーブル名]
無い場合は省いてドットで繋ぐという書き方だと、非常に重たいケースが出てきます。

「OPENQUERY」関数を使えば随分と軽くなります。

上記の様に、OPENQUERY関数の第一引数にリンクサーバー名、第二引数に文字列、文字列はSQL文になります。
このクエリをサブクエリにすることで色々なDBとも接続が可能になります。

ただ、文字列はリンターが利きませんので長いSQL文は非常に見にくくなります。

また文字列を扱う時は

の様にクオーテーションで更に囲みますのでお気を付けください。

あとがき

今回は設定ばかりになりましたが、リンクサーバーは設定してみると意外にうまくいかないことが多いです。そのうえ解決策もなかなか見つからなかったりします。過去につまずいた方の助けになれば幸いです。

連載は今回で一旦終了になります。つたない文章を読んで頂いた方がいたら心から感謝いたします。
また、こんな機会を与えて頂いたIBM佐々木様、オムニサイエンス下野様、加邉様、ありがとうございました。

いいねと思ったらシェア
twitter
facebook
hatena
関連記事
IBM i 用の新しいODBCドライバー
IBM i 用の新しいODBCドライバー
使ってみよう!Rational Developer for i
使ってみよう!Rational Developer for i
Power10 プロセッサー搭載スケールアウトモデル登場
Power10 プロセッサー搭載スケールアウトモデル登場
あなたにオススメの連載
できるIBM i 温故知新編
5記事
できるIBM i 温故知新編
IBM i の”新”必須言語 〜FFRPG入門〜
14記事
IBM i の”新”必須言語 〜FFRPG入門〜
IBM i アプリの第二の柱 OSS
15記事
IBM i アプリの第二の柱 OSS
PAGE TOP