【虎】第13回「SQLとモダナイゼーション」

2022年1月3日　チャーリー・グアリーノ

チャーリー・グアリーノがIBMチャンピオンのビルギッタ・ハウザーと、Db2 for i のモダナイゼーションやSQLについて語ります。

チャーリー・グアリーノ（以下、チャーリーと略します）： TechTalk SMBの別エディションにようこそ。今日のゲストは世界的に有名なSQLの専門家であり、データベースのモダナイゼーションと教育の専門家であり、独立したコンサルタントでもあるビルギッタ・ハウザー氏です。加えて、彼女はRedBookの著者であり、最近ではIBM Powerチャンピオンでもあります。ビルギッタ、今日はご出演いただきありがとうございます。

ビルギッタ・ハウザー(以下、ビルギッタと略します)：ありがとう。お話できて嬉しいです、チャーリー。

チャーリー：今日はデータベースのモダナイゼーションについて話しますが、これは明らかにあなたの専門分野です。今日のこの議論の準備として、私は何人かの開発者と話をし、開発初心者がいくらか怖気付いていることを知りました。彼等はどこから始めればよいかさえ知りませんし、データベースとの関係でモダナイゼーションという言葉が何を意味するかも分からないので、最初にその背景を少し教えてください。データベースのモダナイゼーションを行う理由、目標は何ですか？

ビルギッタ：最終的な目標は、データベースを再設計することです。
私にとってモダナイゼーションの第1段階は、できるだけ多くのビジネスロジックをプログラムからデータベースに移すことで、そのための最も簡単な方法はSQLビューを使用することです。最終的にビューでラップし、プログラムでこのビューだけを使用します。こうすることでSQLを使用して、例えば小計と合計を作成する、IFS内のファイルにアクセスする、Webサービスにアクセスするなど、非常に多くのことが行えます。また、XML形式でデータを取得してそれを分解することができます。このデータを使用する最も簡単な方法は、ビューを使って必要なフィールドをいくつかの条件で選択し、並び変えるだけです。複雑なSQL文を書ける人が1人か2人と、平均的なプログラマーがいれば、プログラマーはビューを使用するだけでSQL文を気にする必要はありません。
次にパフォーマンスです。この複雑なSQL文を記述できる人は、適切なインデックスを作成して高いパフォーマンスを実現することもできます。
更にもう1つ、今日では複数の言語を使うのが当たり前になっていますが、データアクセスをビューでなく個々のプログラムで行っている場合、既にあるものを言語ごとに１から作り直す必要があります。ビューを使うことで、まずソースコードを最小限に抑えることができ、次にデータアクセスを外部化できます。つまり、更新、挿入、読み込み用のプロシージャをラップするだけでソフトウェア資産の外部化が可能になります。このように進めば、最終的にデータベースを再設計できる状態になります。つまり、冗長性を取り除き、新しい表を構築し、新しい表に纏め、参照整合性などを実装できます。

チャーリー：最終的な目標は、ビジネスロジックを備えたデータベースをデータ中心にすることだというあなたの意見には完全に同意しますが、それは大きな第一歩だと思います。もしロジックをデータベースに単純に移動し、制約やトリガー及びあなたが言ったその他のものをデータベースに持たせたとしたら、同時にプログラムも更新する必要があります。違いますか？

ビルギッタ：プログラムを書き直さなければならないことは明らかですが、モジュラープログラミングを行えば、適切な関数を記述し、それを実行するプロシージャを記述するだけで、プログラムの修正を開始できます。
例えば、各物理ファイルまたは表に対して常に行うこととして挿入、更新、および削除があります。この表の更新、挿入、削除用のルーチンを作成すると、それらによって多くのことが実行できます。挿入について言うと、デフォルト値を設定でき、何かを比較できます。最初の段階では、これらの機能すべては表に直接もたせられないかもしれませんが、既に機能を集約する過程にある場合は、段階ごとに改修を進めることができます。
私がすることは、第1段階でスケルトンプログラムの1つを取り出し、その中のファイル名を置き換えるだけです。既に基本的なプロシージャが生成されています。それを自分のプログラムに移動するか、そのプロシージャを呼び出します。これは、プログラムのモダナイゼーションやモジュール化を行っているのと同じことです。そして一歩一歩進み、プログラムを書き直します。

チャーリー：やるべきことが沢山あるように思えます。あなたが説明している究極の目標が最善の解決策であることは認識していますが、一般的に言われているのは最初にDDSを取り除くことです。それが私達の最初の一歩でもあるはずです。それは正しいですか?

ビルギッタ：DDSからDDLに移行するのは正しいです。
物理ファイルを使用するのと同じ方法で DDLの表を使用できますが、内部的には100%同じではありません。例えば、何かをDDSファイルにコピーしようとすると、物理ファイル内のすべてを検査なしでコピーできます。DDLの表で同じことをしようとすると、無効なデータを挿入することはできません。
DDSで記述されたファイルに書き込むときには検査は行われず、読み取るときにデータが検査されますが、DDLの表では書き込むときにデータが検査され、表から読み取るときにはデータは検査されないので、読み取り時間が短くなります。どのくらいの頻度で読み、どのくらいの頻度で書いたり更新したりしますか? おそらく80%の読み取りと20%の書き込みなので、何もせずにDDSからDDLに変換するだけでも、プログラムはわずかに高速になる可能性があります。
ただし、古いプログラムがまだ残っていて、適切に初期化されていないデータ構造を使用してデータを書き込んでいるせいでクラッシュが発生する可能性があります。この問題があることは分かっているので、比較的素早くこれを修正できます。
DDSからDDLに変換する以外は他に何もしない場合は、ACSのSQL生成オプションで比較的簡単にDDSファイルをDDLに変換するSQLコードが生成できます。このCREATE TABLEまたはREPLACE TABLE文を実行するだけでデータベースまたは表がDDSからDDLに変換されます。
更に良いことに、フィールドなどを追加しない限り、フォーマットレベルは同じです。したがって、プログラムを再コンパイルする必要はありませんが、これは最初のステップです。これで、DDSからDDLに変換され、監査列の追加やID列などのようなSQLで使用できるあらゆる優れた新機能を使用できるようになります。ネイティブ I/O がある場合は再コンパイルします。それですべてです。ちなみに、DDSで記述された論理ファイルをSQLインデックスに変換することもできます。SQLインデックスはSQL文自体では使用できませんが、F仕様書に指定できます。例えば、RPGプログラムはキー論理ファイルと同様に、DDS論理ファイルからSQLインデックスに変換しても、プログラムは引き続き実行され、何も再コンパイルする必要はありません。

チャーリー：分かりました。しかしファイルを表に変換しただけなら、あなたが既に言ったように、データエラーが発生する可能性があります。以前のスタイルのプログラムでは、表に不正なデータが入力されていた可能性があるからです。アクティブなすべてのプログラムが適切に実行されることを確認する必要があります。ファイルの使用を許可し、次の段階に進む前に何かすべき推奨事項はありますか?

ビルギッタ：いいえ、それだけです。私が提案したいのは、表を1つずつ変換し実行することです。データが正しくないと、すぐにクラッシュするとあなたは言いましたが、まだ古い表があり、適切に初期化されていない可能性がありますのでそれを検査します。同じプログラム及び同じデータを持っているテスト環境を用意し、変換して実行します。テストして問題が発生した場合、ここでそれを修正する必要があることが分かります。

チャーリー：しかし、データベース全体でそれを行うのですか?それともアプリケーションごと、またはマスター表とトランザクション表ごとに行うのですか? それを最初に行う特定の方法はありますか。

ビルギッタ：通常は重要ではない表から始めます。1つずつ実行するので、「何千ものファイルがあり、今週末それらをすべて変換する必要があります」と言う必要はありません。段階的に行うことができるので、週末に3～5個のファイルをテスト環境で変換しテストします。すべてが正常に機能する場合は、本番機で同じ表を変換して見てみましょう。来週は、次の5個または10個の表を変換しますので問題ありません。
このようなことを手動で行う必要がないように、私はいくつかの追加関数を作成しました。単なる変換以上のこともしました。例えば、日付フィールドを追加して数字日付を日付型データに変換したり、データの一貫性を保つトリガープログラムを生成したりしました。顧客と一緒にデータベースを調べ、何をしているのかを説明し、作業を自動化するいくつかの個別のプログラムを書きました。変換するレコードの数によって異なりますが 、すべての新しいファイルとトリガープログラムを15～30分で生成しました。ですから、非常に高速に変換を実行し、テストできます。何か問題が発生した場合はリセットするだけです。

チャーリー：このプロセスのために実際の予算をつけることの利点を説得するのに苦労している開発者、マネージャー、またはエグゼクティブに何と言いますか? 経営陣は、この作業の真の利益や投資収益率に気付いてこのプロジェクト全体を理解し、本当に支持したいと考えますか?

ビルギッタ：ええ、それはとても簡単という訳ではありません。 管理者が見ることのできる新しいインターフェースを追加したい場合でも管理者を納得させるのは非常に困難です。私達がするべきことの１つは、データに非常に高速にアクセスすることです。社内のデータだけでなく、社外のデータも持っており、それらをまとめなければなりません。データへのアクセスが高速になればなるほど、新しいレポートをより迅速に作成できます。また、ソースコードが少なくなればなるほど、制約や参照整合性、行と列のアクセス制御などのセキュリティ機能もプログラムから表にもたせる必要があります。これらは非常に重要なことです。IBM i はもはや孤島ではないので、誰かが入ってきてプログラムを迂回した場合、古い表にはオブジェクト権限を持つ人物がいて、データに対して何でも好きなことができます。表またはデータにアクセスできる人は誰でも好きなことができ、データを破壊できます。IBM iに入り込み何かをハッキングしたり、データを盗もうとしたりする人は、私達のプログラムを調べようとする代わりにSQLインジェクションのようなものを使うので、データベースをセキュアにする必要があります。

チャーリー：開発者として言えば、プログラムの書き直しもあると理解しています。確かに、それも行う必要がありますが、SQLをアプリケーションに導入すると、データを処理する方法が、RPGは伝統的にレコードレベルのアクセス（RLA）であるのに対し、SQLの場合はそれと大きく異なる集合処理ですので、プログラムに対するより大きな変更でもあります。これは私達のパラダイムの完全な変化です。対処の準備をする必要があります。

ビルギッタ：ええ。プログラムを書き直さなければなりません。それはただの考え方なので、再考することを学ばなければなりません。昔は多くのプログラムが次のように書かれていたとします。まず、ネイティブI/Oで最初に注文ヘッダーを読み取り、次に住所マスターを読み取ります。そして、品目マスター表を読み取り、最終的に写真、画面またはリストを手に入れてそれをテストします。 私が経験から学んだのはテストの仕方です。プログラムを書いた後でクエリを書く代わりに、最初にクエリを書きます。まさにこのデータが必要で、これをどのように取得するか 再考する必要があります。最も簡単な方法は、新しいプログラムで再考を開始し、パフォーマンスの観点からも継続的に再考することです。
ネイティブI/Oを埋め込みSQLに置き換えて使用したいと言う人もいますが、単にネイティブI/Oを埋め込みSQLに変換するのは良い考えではありません。なぜなら、SQLでフルオープンが必要な場合、ODPを開かなければなりませんが、これはCPU負荷の高いステップなので時間がかかります。ネイティブI/Oを使ったプログラムと埋め込みSQLを使ったプログラムを1対１で比較すると、SQLは最適化されていないので、少なくとも1回目または2回目の実行ではRPGがSQLに勝ちます。しかし、10万件を超えるレコードを反復処理する場合、これはパフォーマンスの観点で問題です。SQLはこの点で遥かに柔軟で、表を結合でき、1つの表に対し1つのSQL文で複数のインデックスを使って最適化することもできます。様々な方法で再考して実行すると、SQLはRPGのネイティブI/Oよりもはるかに高速になるはずです。
また、ネイティブI/Oを単純にSQLに置き換えた場合、RPGのネイティブI/Oでは常にレコード全体を戻すので、SELECT * FROMを使用する必要があります。SQLでは必要なフィールドだけが返されます。レコード全体を取得して20文字しか必要としない場合、残りは破棄することになります。SQLでは必要なデータを効率よくアクセスできます。これをネイティブI/Oでやろうとすると、ずっと複雑なロジックになる可能性があります。最善の方法は、この複雑さだけをSQLビューに押し込め、プログラムは単にそのSQLビューを使うことです。

チャーリー：そして、あなたが今言ったように、SQLクエリエンジンとSQEの利点を手にすることができます。従来のネイティブI/ Oに止まるのを放棄することですべての追加の利点を享受できます。

ビルギッタ：ええ、ロチェスターのチームは、Db2 for iで本当に良い仕事をしましたが、それは多くの人が知らないことの 1 つです。多くの人はデータベースアクセスに関する統計があること、統計が永続的に更新されることなどを知りません。例えば、インデックスアドバイスについて見てみましょう。ACSという非常に優れた分析ツールがあり、SQL文に関するすべての情報が取得できます。このツールの中心的な機能（注：Visual Explain）によって提供されるアクセスプラン情報のお陰で、実行するステップ及びそのステップに要する時間を正確に確認してからSQL文の書き直しを試みることができます。
パフォーマンスについて言えば、SQLで影響を与えることができるのは2つだけだと言いました。1つは適切なインデックスを作成することですが、もっと重要なのは適切なSQL文を作成することです。異なる構文で書いても同じ結果が得られますが、ある構文ではオプティマイザーがインデックスを使用でき、別の構文では使用できません。表に500レコードしかない場合は大きな違いが生じませんが、5億のレコードがある場合は表全体を読み取るか、それとも直接アクセスするかで大きな違いが生じます。SQLは範囲が広く見かけほど簡単ではありません。
IBMは常々DBE（データベースエンジニア）が必要だと説いています。DBEはデータベースに責任をもち、正しいSQL構文を知った上でSQL文を書くだけでなく、適切なインデックスを作成することもできます。そして平均的なプログラマーは、プログラムから見てこれで問題ないと言うだけです。使うプログラミング言語に関係なく、平均的なプログラマーはデータベースを気にしません。データベースを気に掛けるのはRPGやCOBOLプログラマーだけです。Javaプログラマーはただそれをあるがままに受け入れます。彼等に尋ねると、それに気を配るのはDBEの役割だと答えます。今日、私達はJavaプログラマーと同じ方法でそれを行う途上にあります。
データベースの責任者が1人か2人いてSQL文を準備し、その最適化や再設計などを行い、プログラマーは自分のプログラムを書くだけです。SQLは非常に強力で、ネイティブI/Oと同じ方法でデータにアクセスするだけでなく、Webサービスにアクセスすることもできます。例えば、SQLには約180の組み込み関数がありますがRPGにはその50%しかありません。SQLには、あらゆる種類の小計や合計を計算するなどの数多くの強力な機能があります。

チャーリー：私の職業人生の中で書いたり修正したりしたすべてのプログラムについて考えると、1つのプログラムのコード行の大部分は、データ検証のためだけのものであることは確かです。そして、それはすべてなくなる可能性があり、残るのは純粋なビジネス機能とアプリケーションです。それが、より標準的で業界で受け入れられているプロセスになりつつあります。

ビルギッタ：ええ、ネイティブI/Oを見たことがない若い人が採用されています。彼等は 学校で少なくともSQLをちょっと学んだので、もう一度見ればより親しみを感じます。今ではそれができ、大きなロジックはデータベース自体にあり、制約のビューでデータベースに隠されています。私は自分が作った倉庫管理システムをネイティブI/OからSQLに変更しました。最初、皆それについて懐疑的でしたが、その後2000を超える文からいくつかのプログラムを削減することができました。当時、クエリユーザーが多く、何か問題が発生した場合は、常に電話がかかってきました。私達がしたことは、私達が使用したのと同じビューに基づいて100個のクエリを提供しただけです。ビューを使用して検査し 5分で問題解決できました。以前は何時間もかけ、結局役に立たない検証プログラムを作っていましたから、これも手間を減らした1つのことです。

チャーリー：この旅を今まさに始めようとしている人がそれらのことを開始し、前進するための最初の一歩を踏み出すための最後のアドバイスは何ですか?

ビルギッタ：良い点は、複数の地点から開始できることです。最も重要なことは、どこかから始めなければならないということです。私の提案は、DDSからDDLに変換することから始めるか、SQLビューから始めることです。データアクセスの外部化、関数の準備などから始めることができます。これらはすべて開始できる場所ですが、最も重要なことは始めることです。新しい表を作成する場合は、正しいものにします。つまり、ID列を追加し、監査列を追加し、制約を追加し、参照整合性などを追加します。成長したアプリケーションでは、それはより困難になるので段階的にしか進むことができませんが、前述したように、使用を開始して複雑さをビューに隠します。

チャーリー： そしてもちろん、これは間違いなく価値のある旅です。一方で、メリットは途方もないものです。

ビルギッタ：テクノロジーが更新されるたびに、SQLとデータベースに非常に多くの新しい機能が追加されます。それは本当に素晴らしいです。なぜ古いテクニックを使用するのですか? なぜRPGを使用するのですか? 私は RPG プログラマーですが、SQLを使った方がずっと簡単なら、何故ネイティブI/Oを使用して何かを作成するのでしょう?

チャーリー：また、より幅広い開発者が保守することもできます。

ビルギッタ：もちろんです。もし若い人たちを採用したとしても、私は彼等にもうネイティブI/Oを教えません。ちょっと前にCOBOLプログラマーで、非常に簡単な RPGプログラムを書かなければならなかった人がいました。 私が教えたのは、COBOLやRPGで行うのと同じことをする埋め込みSQLを使う事でした。次にIFとTHENをいくつか実行してループを実行します。それが学ぶべきことのすべてでした。そして、RPGを見たことがないにもかかわらず、彼女は簡単なプログラムをRPGで書くことができました。

チャーリー：ビルギッタ、いつも通りあなたはこの短い時間に収められると思っていたよりもはるかに多くの情報を提供してくれました。ありがとうございます。

ビルギッタ：こちらこそ、ありがとうございます。

チャーリー：このポッドキャストを聞いてくれてありがとう。Twitter で ビルギッタをフォローしたい場合、ハンドルはBirgitta Hauserです。彼女はそこでもフォーラムでも非常に活発に情報発信しており、最初に言ったように時間を自由に割いてくれるので、彼女に連絡したり質問を投稿したりできます。ビルギッタ、いつもありがとう、そしてここで締めくくります。どうもありがとう。