HiRDB アプリケーション開発ガイド

Transcription

1 株式会社日立製作所情報 通信システム社 IT プラットフォーム事業本部開発統括本部ソフトウェア本部発行

2 HiRDB アプリケーション開発ガイド

3 < 他社所有商標に対する表示 > Eclipse は, 開発ツールプロバイダのオープンコミュニティである Eclipse Foundation, Inc. により構築された開発ツール統合のためのオープンプラットフォームです Microsoft, Windows, Windows Server, Windows Vista. Visual Studio, Visual Basic, および Visual C++ は, 米国 Microsoft Corporation の米国およびその他の国における登録商標または商標です Microsoft.NET は, お客様, 情報, システムおよびデバイスを繋ぐソフトウェアです ODBC は, 米国 Microsoft Corporation が提唱するデータベースアクセス機構です OLE は, 米国 Microsoft Corporation が開発したソフトウェア名称です Oracle と Java は,Oracle Corporation 及びその子会社, 関連会社の米国及びその他の国における登録商標です UNIX は,The Open Group の米国ならびに他の国における登録商標です X/Open は,The Open Group の英国ならびに他の国における登録商標です 記載の会社名 団体名 商品名は 各社 各団体の名称 商標または登録商標です < 注意事項 > 本書の一部または全部を 株式会社日立製作所または原著作者に無断で複製 複写または転載することはどのような形であっても 法律で禁じられております 本書および記載の製品を輸出される場合には 外国為替および外国貿易法の規制ならびに米国輸出管理規制など外国の輸出関連法規をご確認の上 必要な手続きをお取りください なお ご不明な点は弊社営業担当者にお問い合わせください 本文中には TM Rマークは明記していません < 発行履歴 > 2007 年 10 月第 1 版発行 2010 年 4 月第 2 版発行 2014 年 12 月第 3 版発行 All Rights Reserved. Copyright 2007,2014,Hitachi,Ltd.

4 はじめに 本書は 下記ホームページより ダウンロードまたは CD-ROM で配布している HiRDB 体験版プログラム ( 以下 体験版 HiRDB と表記 ) を利用して HiRDB のデータベースにアクセスする Java TM アプリケーションプログラムの開発から SQL のチューニングまでを体験していただくための手引書です 本書は 体験版 HiRDB と一緒にお使いください HiRDB のホームページの URL 1. 対象読者データベースにアクセスする Java アプリケーションプログラムの開発に携わっている方 2. 前提知識本書の内容は リレーショナルデータベースの基本的な知識があり Java プログラミングの基礎知識があることを前提としています (Java プログラミングの経験があることが望ましい ) 3. 本書が想定する HiRDB 体験版の使用環境 HiRDB Version 8 体験版 Windows 2000 / Windows Server 2003 / Windows XP Professional HiRDB/Single Server Version 8 HiRDB Version 9 体験版 Windows Server 2003/Windows XP Professional/Windows Vista/Windows Server 2008/Windows 7 ( すべて x64 版 ) HiRDB/Single Server Version 9 4. 本書の内容 1. HiRDB のアーキテクチャ HiRDB の構成要素とそれらの働き データベース構造など 本書を読み進めていく上で知っておいていただきたいことを説明しています 2. アプリケーションプログラムの開発環境アプリケーションプログラム開発に関する基本仕様 および体験版 HiRDB のインストール方法について説明しています 3. データベースの作成体験版 HiRDB を使用してデータベースを作成する手順と 表やインデックスを定義するときの考慮点について説明しています 4. アプリケーションプログラムの開発 HiRDB にアクセスするアプリケーションプログラムの記述内容や 開発にあたっての考え方 注意点について説明しています ここでは 原則的に 各種開発手法 ( 埋込み SQL や JDBC ADO.NET 等 ) に依存しない汎用的な内容について扱います 5. Java を使ったアプリケーションプログラムの開発 JDBC を用いた Java 言語によるアプリケーションプログラムの実装方法について説明しています I

5 はじめに II 6. SQL のチューニング 付録 HiRDB SQL Tuning Advisor を使用した SQL のチューニング方法について説明します 次の内容について説明します 付録 A CREATE TABLE の文法 付録 B CREATE INDEX の文法 付録 C Oracle と HiRDB のデータ型の対応 付録 D 暗黙的データ型変換 付録 E システム定義のパラメータの追加方法 付録 F HiRDB の GUI 運用支援製品 付録 G 体験版 HiRDB のインストールに失敗した場合の対処方法 付録 H SQL が警告エラーになるケース 5. 本書での表記 本書では ソフトウェアの名称を省略して表記しています 次に 製品の正式名称と本書での表記を 示します 正式名称本書での表記 MicrosoftR WindowsR 2000 Professional Operating System Windows 2000 MicrosoftR WindowsR 2000 Server Operating System MicrosoftR WindowsR 2000 Datacenter Server Operating System MicrosoftR WindowsR 2000 Advanced Server Operating System MicrosoftR Windows ServerR 2003, Standard Edition Windows Server 2003 MicrosoftR Windows ServerR 2003, Enterprise Edition MicrosoftR Windows ServerR 2003 R2, Standard Edition MicrosoftR Windows ServerR 2003 R2, Enterprise Edition MicrosoftR WindowsR XP Professional Operating System Windows XP Professional MicrosoftRWindows VistaR Business Windows Vista MicrosoftRWindows VistaR Enterprise MicrosoftRWindows VistaR Ultimate MicrosoftR Windows ServerR 2008, Standard Windows Server 2008 MicrosoftR Windows ServerR 2008, Enterprise MicrosoftR WindowsR 7 Ultimate Windows 7 MicrosoftR WindowsR 7 Enterprise MicrosoftR WindowsR 7 Professional MicrosoftR Windows ServerR 2003, Standard x64 Edition Windows (x64) MicrosoftR Windows ServerR 2003, Enterprise x64 Edition MicrosoftR Windows ServerR 2003 R2, Standard x64 Edition MicrosoftR Windows ServerR 2003 R2, Enterprise x64 Edition MicrosoftR WindowsR XP Professional x64 Edition MicrosoftR Windows VistaR Business (x64) MicrosoftR Windows VistaR Enterprise (x64) MicrosoftR Windows VistaR Ultimate (x64) MicrosoftR Windows ServerR 2008 Standard (x64) MicrosoftR Windows ServerR 2008 Enterprise (x64) MicrosoftR Windows ServerR 2008 R2, Standard Edition (x64) MicrosoftR Windows ServerR 2008 R2, Enterprise Edition (x64) MicrosoftR Windows ServerR 2008 R2, Datacenter Edition (x64) MicrosoftR WindowsR 7 Professional (x64) MicrosoftR WindowsR 7 Enterprise (x64) MicrosoftR WindowsR 7 Ultimate (x64)

6 はじめに 6.SQL の書き方で使用する記号 本書では 次の記号を使用して SQL の文法を説明しています 本書で使用している記号 意味 内に記述されている文字列は省略できることを示します の直前に記述されている文字列を繰り返し指定できることを示します { } { } 内に記述されている複数の文字列から 1 つ選択することを示します _( 下線 ) 省略時のデフォルト値を示します 7. 関連マニュアル 本書は 次に示す HiRDB のマニュアルと関連を持っています 本書とあわせてご利用ください HiRDB Version 8 のマニュアル マニュアル名称 マニュアル番号 HiRDB Version 8 解説 (WindowsR 用 ) HiRDB Version 8 システム導入 設計ガイド (WindowsR 用 ) HiRDB Version 8 UAP 開発ガイド (WindowsR 用 ) HiRDB Version 8 SQL リファレンス (WindowsR 用 ) また 体験版 HiRDB をインストールすると これらのマニュアルを参照できます これらのマニュアルは [ スタート ]-[ プログラム ]-[HiRDB Single Server]-[HiRDB マニ ュアル ] を選択すると表示されます 本文中に HiRDB Version 8 の記載があります HiRDB Version 9 をご使用の場合は HiRDB Version 9 に読み替えてご使用ください HiRDB Version 9 のマニュアル マニュアル名称 マニュアル番号 HiRDB Version 9 解説 (WindowsR 用 ) HiRDB Version 9 システム導入 設計ガイド (WindowsR 用 ) HiRDB Version 9 UAP 開発ガイド (WindowsR 用 ) HiRDB Version 9 SQL リファレンス (WindowsR 用 ) III

7 はじめに 8. 参照先の Oracle のマニュアルについて 本文中に Oracle の仕様を説明している箇所があります 各説明箇所には その説明の詳細が記載さ れている Oracle のマニュアル名と記載箇所を表記しています なお Oracle のマニュアル名と記載箇所は 次に示す部品番号および発行年月日のマニュアルのもの です マニュアル名称 部品番号 発行年月日 Oracle Database SQL リファレンス 10g リリース 2(10.2) B 年 3 月発行 Oracle Database 概要 10g リリース 2(10.2) B 年 3 月発行 Oracle Database アプリケーション開発者ガイド- 基礎編 10g リリース 2(10.2) B 年 3 月発行 Oracle の仕様の詳細については Oracle の最新のマニュアルでご確認ください 9. 注意事項 本書の例題で使用しているデータや検索結果は 体験版 HiRDB のサンプルデータベースの内容とは異なります そのため 例題をそのまま実行することはできませんのでご注意ください 本書の内容は HiRDB Version 8 をベースに作成されています 製品の仕様については 最新のマニュアルでご確認ください 最新のマニュアルは以下の URL で参照できます HiRDB オンラインマニュアルの URL HiRDB 講座について日立では HiRDB の講座を設けています アプリケーション開発の分野では 次の二つの講座をご用意しています 演習を通して効率的なスキルの習得にお役立てください HiRDB アプリケーションプログラミング HiRDB アプリケーションチューニング HiRDB 講座の詳細については HiRDB 講座のホームページをご覧ください HiRDB 講座のホームページの URL IV

8 目次 1 HiRDB のアーキテクチャ HiRDB の構成要素 プロセス メモリ ファイル HiRDB ファイルの構造 HiRDB ファイルシステム領域 RD エリア 表やインデックスの格納単位 HiRDB ファイルの構造のまとめ データベースアクセスの流れ SQL によるデータベースアクセス 更新内容を HiRDB ファイルに書き込むタイミング アプリケーションプログラムの開発環境 アプリケーションプログラム開発に関する基本仕様 体験版 HiRDB のインストール 体験版 HiRDB のインストール手順 体験版 HiRDB をインストールすると作成される RD エリアと表 クライアント環境定義の設定 データベースの作成 データベースを作成するのに必要な権限 データベースの作成手順 体験版 HiRDB を使用してデータベースを作成するときの手順 表を定義するときの考慮点 データ型 制約 i

9 目次 表の定義例 HiRDB SQL Executer で定義系 SQL を実行するときの注意事項 表の特殊な使い方 パブリックビュー 自動採番機能 インデックスを定義するときの考慮点 インデックスを定義すると効果が期待できるケース 単一列インデックスと複数列インデックス インデックスの優先順位 インデックスの定義例 インデックスの効果的な利用方法 検索条件を指定する場合 ORDER BY 句を指定する場合 結合処理を指定する場合 表へのデータ格納方法 アプリケーションプログラムの開発 アプリケーションプログラムの基本構成 静的 SQL と動的 SQL 埋込み変数 データ型に関する注意点 SQL 文中の定数の指定方法 プログラミング言語のデータ型と HiRDB のデータ型の対応 データ型の変換 文字列を比較するときの注意事項 HiRDB と Oracle のスカラ関数の対応 カーソル カーソルとは カーソルを使用したデータ取得手順 カーソルを使用したデータの更新とデータの削除 効果的な SQL の使い方 総ヒット件数と各行の値を求める場合 配列の利用 ホールダブルカーソルの利用 一行検索の利用 排他制御 ii

10 目次 排他資源の単位 排他制御モード 排他の期間 排他オプションの指定方針 デッドロックが発生するケース デッドロックの回避策 デッドロックが発生したときに出力される情報 SQL のエラー判定 性能が向上する SQL の書き方 表の結合方式 表の結合を行うときの考慮点 集合演算を行うときの考慮点 その他の考慮点 保守性を良くする SQL の書き方 SELECT 文を指定する場合の考慮点 Java を使ったアプリケープログラムの開発 JDBC 概要 アプリケーションプログラムの基本構成 JDBC ドライバの登録 HiRDB への接続 SQL によるデータベースの検索 SQL によるデータベースの更新 / 削除 / 追加 トランザクションの制御 HiRDB からの切り離し 例外処理 ? パラメタと PreparedStatement アプリケーションプログラムの例 環境設定 環境変数 CLASSPATH の確認 ucosminexus Developer Professional と Eclipse の環境設定 Java ソースファイルの作成とコンパイル Eclipse を使用する場合 Eclipse を使用しない場合 iii

11 目次 6 SQL のチューニング SQL チューニングの流れ アクセスパス テーブルスキャン<TABLE SCAN> インデックススキャン キースキャン<KEY SCAN/MULTI COLUMNS KEY SCAN> チューニング情報の収集 クライアント環境定義のパラメータの設定 HiRDB SQL Tuning Advisor の環境設定 SQL トレース情報の解析 アクセスパス情報の解析 解析手順 スキャン方法および結合方法を表すキーワード アクセスパス情報の分析例 SQL チューニング インデックス定義の変更 SQL 文の書き換え 付録 付録 A CREATE TABLE の文法 付録 B CREATE INDEX の文法 付録 C Oracle と HiRDB のデータ型の対応 付録 D 暗黙的データ型変換 付録 E システム定義のパラメータの追加方法 付録 F HiRDB の GUI 運用支援製品 F.1 簡易セットアップツール F.2 HiRDB Control Manager 付録 G 体験版 HiRDB のインストールに失敗した場合の対処方法 付録 H SQL が警告エラーになるケース 索引 iv

12 1 章 HiRDB のアーキテクチャ 1 HiRDB のアーキテクチャ この章では HiRDB の構成要素とそれらの働き データベース構造など 本書を読み進めていく上で知っておいていただきたいことを説明しています 1.1 HiRDB の構成要素 1.2 HiRDB ファイルの構造 1.3 データベースアクセスの流れ 1

13 1 章 HiRDB のアーキテクチャ 1.1 HiRDB の構成要素 ユーザがアプリケーションプログラムを実行すると その要求は HiRDB のサーバプロセスが受け付けます サーバプロセスは HiRDB ファイルにアクセスして必要なデータをメモリ上に格納し 要求結果をアプリケーションプログラムに返します これらの処理の流れを次に示します HiRDB システム アプリケーションプログラム サーバプロセス メモリ グローバルバッファ ディクショナリバッファ 排他制御プール HiRDB ファイル アプリケーションプログラム サーバプロセス SQL オブジェクトバッファ ログバッファ バックグラウンドプロセス システムファイル デファードライトプロセス ログライトプロセス システム回復プロセス トランザクション回復プロセス システム定義ファイル 作業表ファイル 図 HiRDB の処理の流れ 図に示すように HiRDB は プロセス メモリ およびファイルから構成されており これらをまとめて HiRDB システムといいます プロセス メモリ およびファイルの詳細について説明します 2

14 1 章 HiRDB のアーキテクチャ プロセス プロセスは アプリケーションプログラムからの要求を処理したり システムを稼働したりするために実行されるプログラムです HiRDB のプロセスには サーバプロセスとバックグラウンドプロセスがあります HiRDB のプロセスを次に示します 表 HiRDB のプロセスプロセス説明サーバプロセスアプリケーションプログラムから要求を受け付けて処理を実行します 一つのアプリケーションプログラムに対して 一つのサーバプロセスが起動します バックグラウンドプロセス次のプロセス群です デファードライトプロセス定期的にデータベースの更新内容をファイルに出力します ログライトプロセスシステムログをファイルに出力します システム回復プロセスシステム障害からデータベースを回復します トランザクション回復プロセストランザクション障害からデータベースを回復します メモリ SQL の解析と実行に必要となるデータは 一時的にメモリ上に格納されます HiRDB が 使用するメモリを次に示します メモリの種類グローバルバッファ ディクショナリバッファ SQL オブジェクトバッファ排他制御プール ログバッファ 表 HiRDB が使用するメモリ説明データ入出力時にデータを格納します グローバルバッファには 表やインデックスのデータ入出力時に使用するデータバッファと SQL 解析時 必要な情報の入出力に使用するシステム RD エリアバッファがあります データバッファとシステム RD エリアバッファについては SQL によるデータベースアクセス で説明しています SQL の解析時に必要な定義や情報を格納します 解析した SQL オブジェクトを格納します 排他情報 ( 対象となる排他資源 排他モードなどの情報 ) を格納します システムログを一時的に格納します 3

15 1 章 HiRDB のアーキテクチャ ファイル ファイルには HiRDB のデータベースを構成するファイルと HiRDB の実行環境を定 義するファイルがあります HiRDB のデータベースを構成するファイル HiRDB のデータベースを構成する主なファイルを次に示します ファイル HiRDB ファイル システムファイル 表 HiRDB のデータベースを構成する主なファイル 説明表やインデックスのデータを格納するファイルです また 表やインデックスの定義情報も格納します 次の 3 種類のファイルを総称して システムファイルといいます システムログファイルデータベースのすべての更新履歴 ( ログ ) が記録されます シンクポイントダンプファイルシステム障害から回復する場合 システムログファイルのどの時点から回復すればよいかという情報が シンクポイントごとに記録されます 作業表ファイル ステータスファイル HiRDB がどのような状態 ( ファイルのオープン クローズなど ) であるかが記録されます システム障害から回復する場合 この情報を基に障害発生前の状態に戻します 表を結合したり データをソートするときに使用する情報を一時的に格納するファイルです シンクポイントとは シンクポイントとは メモリの更新内容とディスクの内容を一致させる システムの同期点のことです HiRDB の実行環境を定義するファイル HiRDB の実行環境を定義するファイルを次に示します 表 HiRDB の実行環境を定義するファイルファイル説明システム定義ファイル HiRDB を開始する場合に必要となる HiRDB の実行環境や構成を定義するファイルです 例えば ファイル構成 ファイル名称 バッファの大きさ サーバプロセス数などを定義します このファイルはテキストファイルです ファイルを開いて データベース管理者が直接編集します 4

16 1 章 HiRDB のアーキテクチャ ファイルについて HiRDB のデータベースを構成するファイルは HiRDB が使用するため データベース管理者が直接内容を参照することはありません 一方 システム定義ファイルは OS 上の一般的なテキストファイルであり データベース管理者がエディタなどを用いて作成するファイルです 実際の構成と同じになるようにシステム定義ファイルを定義してください 5

17 1 章 HiRDB のアーキテクチャ 1.2 HiRDB ファイルの構造 ここでは ユーザが格納する表やインデックス (HiRDB ファイル ) が HiRDB のデー タベースでは 物理的および論理的にどのように位置づけられるのかについて説明します HiRDB ファイルシステム領域 HiRDB のデータベースを構成するファイル (HiRDB ファイル システムファイル 作業表ファイルなど ) は HiRDB 専用の領域である HiRDB ファイルシステム領域に作成されます HiRDB ファイルシステム領域は OS のファイルシステム上に 1 ファイルとして割り当てられます OS のファイルシステム 図 HiRDB ファイルシステム領域 6

18 1 章 HiRDB のアーキテクチャ RD エリア 作成された表やインデックスを管理する論理的な単位を RD エリアといいます 異なる HiRDB ファイルシステム領域上に作成された HiRDB ファイルを 一つの RD エリアで管理できます HiRDB ファイルシステム領域 HiRDB ファイルシステム領域 RD エリア RD エリア HiRDB ファイル HiRDB ファイル HiRDB ファイル RD エリア HiRDB ファイル HiRDB ファイル 図 HiRDB ファイルと RD エリア また RD エリアは 表やインデックスを格納する論理的な領域でもあります 複数の表を一つの RD エリアに格納したり 表とインデックスを別々の RD エリアに格納したりできます 表 インデックス RD エリア RD エリア 図 表やインデックスと RD エリア 7

19 1 章 HiRDB のアーキテクチャ RD エリアの種類 RD エリアには次に示す種類があります 表 RD エリアの種類 RD エリアの種類 主な管理対象 作成基準 マスタディレクトリ RD エリア HiRDB ファイルシステム領域の構 必須 成 パスなど データディレクトリ RD エリア RD エリアがどの HiRDB ファイルシ 必須 ステム領域にあるかという情報などシステムデータディクショナリ RD エリアデータディクショナリや 表やイン RD エリアデックスがどの RD エリアにあるかと 必須 いう情報など データディクショナリ LOB RD エ ストアドプロシージャ ストアドファ 任意 リア ンクション トリガーの SQL 情報 ユーザ RD エリア 表やインデックス 必須 RD エリアの作成基準について作成基準が 必須 になっている RD エリアは HiRDB を構築するときに必要となります また 表やインデックスに BLOB 型のデータ ( 音声や画像などの大容量のデータ ) を定義する場合 BLOB 型のデータだけを格納する RD エリア ( ユーザ LOB RD エリア ) を作成します 8

20 1 章 HiRDB のアーキテクチャ 表やインデックスの格納単位 表やインデックスは 物理的には HiRDB ファイルシステム領域に 論理的には RD エリアに格納されます 表やインデックスを RD エリアに格納する場合の最小単位をセグメントといいます 表を定義してデータを格納する場合 HiRDB はセグメント単位に割り当てをします 一度割り当てられたセグメントは ほかの表やインデックスが使用することはできません 割り当てられたセグメントをすべて使ってしまうと HiRDB は RD エリア内のまだ割り当てられていないセグメントを割り当てます このように 表やインデックスは RD エリアに対してセグメント単位に割り当てられます このため セグメントの大きさは表やインデックスの容量と関連があります セグメントは 連続した複数のページの固まりです ページは データを入出力する場合の最小単位です セグメントとページの関係を次に示します ページ HiRDB ファイルシステム領域 RD エリア セグメント HiRDB ファイル 図 セグメントとページの関係 なお 一つの RD エリアで セグメントやページの大きさは一律同じになります 一つの RD エリア内でセグメントやページの大きさを変更することはできません 9

21 1 章 HiRDB のアーキテクチャ HiRDB ファイルの構造のまとめ HiRDB ファイルシステム領域 HiRDB のデータベースを構成するファイルを格納する HiRDB 専用の領域です HiRDB のデータベースを構成するファイルは すべてこの領域に作成されます RD エリア HiRDB ファイルを管理する論理的な単位です また 表やインデックスを格納する論理的な領域でもあります セグメント表やインデックスを RD エリアに格納する場合の最小単位です 連続した複数のページから構成されています ページデータを入出力する場合の最小単位です HiRDB ファイルの構造を次に示します 図 HiRDB ファイルの構造のまとめ 10

22 1 章 HiRDB のアーキテクチャ 1.3 データベースアクセスの流れ ここでは SELECT 文と UPDATE 文を例にして アプリケーションプログラムがデータベースにアクセスする場合のプロセス メモリ およびファイルの関係について説明します また データベースの更新内容が実際にファイルに反映されるタイミングについても説明します SQL によるデータベースアクセス SELECT 文の場合 SELECT 文アプリケー 1 ションプログラム 13 サーバプロセス 2 グローバルバッファ システム RD エリアバッファ 5 システム RD エリア 6 7 データディクショナリなど 12 グローバルバッファ データバッファ 10 ユーザ RD エリア 4 表やインデックス ディクショナリバッファ 3 8 SQLオブジェクトバッファ 9 ログバッファ 排他制御プール 11 図 SELECT 文実行時の処理の流れ 1~2: アプリケーションプログラムから SQL を受け取ったサーバプロセスは まず SQL オブジェクトバッファを参照します SQL オブジェクトとは HiRDB が解析および最適化した SQL の実行手順です 同じ SQL を以前に実行している場合 SQL オブジェクトバッファ上の SQL オブジェクトを利用するため 9に進みます 3~7: 受け取った SQL を以前に実行していない場合 検索対象の表の定義や物理アドレスなどの管理情報を取得するため システム RD エリアを参照します このとき ディクショナリバッファおよびシステム RD エリアバッファを経由し ディクショナリバッファに必要な情報があれば8に システム RD エリアバッファに必要な情報があれば7に進みます 11

23 1 章 HiRDB のアーキテクチャ 8 : サーバプロセスは 受け取った SQL と3~7で取得した管理情報を基に最適な実行手順を選択し SQL オブジェクトを生成します 生成した SQL オブジェクトは SQL オブジェクトバッファ上に保存されます 9~10: サーバプロセスは SQL オブジェクトを基にして 検索対象の表のデータを取得するため ユーザ RD エリアを参照します このとき データバッファを経由します 11 : 表のデータを参照するとき 排他情報を排他制御プールに取得します 12~13: サーバプロセスがアプリケーションプログラムに結果を返します UPDATE 文の場合 UPDATE 文 アプリケー 1 ションプログラム 16 サーバプロセス 2 グローバルバッファ システム RD エリアバッファ 5 システムRDエリア 6 7 データディクショナリなど 15 4 グローバルバッファ データバッファ 更新 12 変更後 変更前 ユーザRDエリア 10 表やインデックス ディクショナリバッファ 3 8 SQL オブジェクトバッファ 9 ログバッファ 排他制御プール 11 図 UPDATE 文実行時の処理の流れ 12

24 1 章 HiRDB のアーキテクチャ 1~11:SELECT 文の場合と同じです 12~14: 変更前の情報をログバッファに出力し データバッファでデータを更新します 更新後の情報もログバッファに出力します 15~16: サーバプロセスがアプリケーションプログラムに結果を返します システムログの書き込みログバッファに格納されたログは 次の時点でシステムログファイルに書き込まれます ログバッファが満杯になったとき トランザクションが決着 ( コミットまたはロールバック ) したとき システムログファイルをスワップしたとき 更新内容を HiRDB ファイルに書き込むタイミング データバッファの更新内容は デファードライトプロセスによって定期的に HiRDB ファイルに書き込まれます それ以外に シンクポイント時にも データバッファの更新内容とディスクの内容を一致させる ( 同期を取る ) ため HiRDB ファイルに書き込まれます COMMIT 文の発行ごとに書き込まないで シンクポイント時にまとめて書き込むため 入出力回数が減ります シンクポイント時の HiRDB ファイルへの書き込みを次に示します シンクポイント シンクポイント 時間の流れ トランザクション 1 トランザクション 3 トランザクション 5 トランザクション 2 トランザクション 4 トランザクション 6 グローバルバッファ 更新 A B C D 更新 X Z 更新 H G 更新 B M 更新 G Y ファイルへの書き込み ファイルへの書き込み HiRDB ファイル HiRDB ファイル 図 シンクポイント時の HiRDB ファイルへの書き込み 13

25 1 章 HiRDB のアーキテクチャ 空 白 14

26 2 章アプリケーションプログラムの開発環境 2 アプリケーションプログラム の開発環境 この章では アプリケーションプログラム開発に関する基本仕様 および体験版 HiRDB のインストール方法について説明します 2.1 アプリケーションプログラム開発に関する基本仕様 2.2 体験版 HiRDB のインストール 15

27 2 章アプリケーションプログラムの開発環境 2.1 アプリケーションプログラム開発に関する基本仕様 (1) SQL のサポートレベル HiRDB での SQL のサポートレベルを次の表に示します SQL のサポートレベル 1 ISO SQL:1992 Entry SQLに準拠 ISO SQL:1999 ISO SQL:2003 表 HiRDB での SQL のサポートレベル 概要 外結合 CASE 式 CAST 指定などに関して 上位水準の SQL に準拠 動的 SQL に対応 ( 4.2 静的 SQL と動的 SQL 参照) 2 ストアドルーチン用のSQL/PSMに準拠 オブジェクト指向に対応 トリガー機能などに関して準拠 SQL 中に XQuery を指定する SQL/XML に対応 注 1 SQL 1992 では Entry SQL Intermediate SQL および Full SQL の三つの水 準が設けられています 注 2 SQL/PSM(Persistent Stored Module: 永続格納モジュール ) は データベース にアクセスする一連の処理を ルーチンとして定義する機能と それらのルーチンを呼び出すための機能を提供しています SQL/PSM によって 分岐 繰り返しなどの手続き型の文も含められるようになりました (2) アプリケーションプログラミングインターフェース HiRDB がサポートするアプリケーションプログラミングインターフェースを次に示します Windows の開発環境である Visual Studio や.NET Framework 環境や Java 環境でのデータベースアクセスインターフェースをサポートしています JDBC(JDBC 2.0) ODBC(ODBC 2.0 ODBC 3.0 ODBC 3.5) ADO.NET ADO OLE DB(OLE DB 1.1) DAO RDO (3) XA インターフェース UNIX 関連技術の標準化団体 (The Open group: 旧 X/Open) が定義した DTP モデル (Distributed Transaction Processing model) に準拠しています (4) アプリケーションプログラム開発言語 HiRDB がサポートする 埋込み SQL を使えるアプリケーションプログラム開発言語を次に示します 直接 SQL を記述できるため (2) アプリケーションプログラミングインターフェース で説明したアプリケーションプログラミングインターフェースより高速で最適な処理ができます 16

28 2 章アプリケーションプログラムの開発環境 C C++ COBOL Java(SQLJ) Perl Visual Basic Visual C++ (5) 文字コード HiRDB がサポートしている文字コードを次に示します ASCII コード シフト JIS コード EUC 中国語漢字コード 中国語漢字コード (GB18030) Unicode(UTF-8) HiRDB を初期インストールしたときは デフォルトでシフト JIS コードが設定されています 文字コードを変更する場合は HiRDB のコマンド (pdntenv) で変更します 上記以外に次に示す文字集合を使用することも可能です 文字集合は文字データに対する属性です UTF-16 EBCDIK UTF-16 は pdntenv コマンドで文字コード種別に utf-8 を指定した場合 文字集合に指定できます EBCDIK は pdntenv コマンドで文字コード種別に sjis を指定した場合 文字集合に指定できます 文字集合については マニュアル HiRDB Version 8 SQL リファレンス を参照してください HiRDB がサポートしている文字コード種別と アプリケーションプログラムが利用する文字コード種別が異なる場合があるため注意が必要です 17

29 2 章アプリケーションプログラムの開発環境 2.2 体験版 HiRDB のインストール 体験版 HiRDB のインストール CD-ROM には アプリケーションプログラムの開発に必 要なソフトウェアが格納されています 体験版 HiRDB をインストールして アプリケー ションプログラムを開発してみましょう 体験版 HiRDB のインストール手順 体験版 HiRDB のインストール CD-ROM を CD-ROM ドライブにセットすると HiRDB のインストールが始まります 表示される画面のメッセージに従ってインストールを行ってください 体験版 HiRDBをインストール 1 した後に チュートリアルの説明に従ってHiRDB SQL Executer HiRDB SQL Tuning Advisor HiRDB Control Managerなどを操作してみてください チュートリアルは [ スタート ]-[ プログラム ]-[HiRDBSingleServer] -[HiRDB 体験版チュートリアル ] を選択すると表示されます インストールされるソフトウェア体験版 HiRDB をインストールすると 次に示すソフトウェアがインストールされます HiRDB/Single Server 日立のリレーショナルデータベース管理システムです HiRDB SQL Executer アプリケーションプログラムの開発を支援するソフトウェアです 対話形式で SQL を実行できるため アプリケーションプログラムで利用する表の定義や 表の検索 データの作成などに利用します アプリケーションプログラムを開発するのに必須のソフトウェアではありませんが 使用することをお勧めします HiRDB SQL Tuning Advisor SQL のチューニングを支援するソフトウェアです 開発したアプリケーションプログラムに記述している SQL のチューニングを行うときに使用します HiRDB SQL Tuning Advisor を使用した SQL のチューニングについては 6 章 SQL のチューニング で説明します HiRDB Control Manager HiRDB のシステム管理者が行う操作を支援するソフトウェアです RD エリアの作成 バックアップの取得 データベースの回復などの操作を GUI で実行できます 1 体験版 HiRDB のインストールに失敗した場合は 付録 G 体験版 HiRDB のインストールに失敗した場合の対処方法 を参照して対処してください 18

30 2 章アプリケーションプログラムの開発環境 体験版 HiRDB をインストールすると作成される RD エリアと表 体験版 HiRDB をインストールすると 次に示す RD エリアが作成されます 表 体験版 HiRDB をインストールすると作成される RD エリア RD エリアの種類 RD エリア名 説明 マスタディレクトリ RD エリア RDMAST システム RD エリアです データディクショナリ RD エリア RDDICT データディレクトリ RD エリア RDDIRT データディクショナリ LOB RD エリア DIC_RTN_SRC DIC_RTN_OBJ ユーザ RD エリア RDDATA10 表 を格納しているRDエリアです RDINDX10 インデックスを格納している RD エリアです ユーザ LOB RD エリア RLOB1 BLOB 型のデータを格納している RD エリアです 注 RDDATA10 には次に示す表が格納されています CUSTOM GOODS LAYIN SENDODR SHIPMNT STOCK TAKEODR VENDOR WAREHUS 体験版 HiRDBを使用してアプリケーションプログラムを作成する場合 これらの表や RDエリアを利用できます 上記の表にアクセスするアプリケーションプログラムを作成してみてもよいし 新たに表を定義 2 しても構いません 新たに表を定義した場合 その表はユーザRDエリアRDDATA10 に格納し インデックスを定義した場合 そのインデックスはユーザRDエリアRDINDX10 に格納してください 備考 RD エリアや表の情報については HiRDB Control Manager で確認できます 確認方法については 体験版 HiRDB のチュートリアルを参照してください 2 3 章データベースの作成 で表およびインデックスの定義方法を説明します 19

31 2 章アプリケーションプログラムの開発環境 クライアント環境定義の設定 クライアント環境定義とは アプリケーションプログラムの実行環境を定義しているパラメータ群のことです クライアント環境定義に設定されているパラメータの内容に基づいてアプリケーションプログラムが実行されます クライアント環境定義の各パラメータは次に示すどちらかのファイルに指定します HiRDB.ini ファイル 任意のファイル同じパラメータを両方のファイルに指定した場合 任意のファイルに指定したパラメータの値が優先されます 両方のファイルに指定がないパラメータについてはデフォルト値が適用されます なお 任意のファイルに指定したパラメータは アプリケーションプログラム中に任意のファイル名を指定したアプリケーションに対してだけ有効になります 一方 HiRDB.ini ファイルで指定したパラメータは HiRDB.ini ファイルを作成したマシン上で実行する全てのアプリケーションに対して有効になります (1) HiRDB.ini ファイルへの指定 HiRDB.ini ファイルはテキストファイルであり Windows のシステムルートディレクトリ ( デフォルト :C: Windows) に格納されています HiRDB.ini ファイル内には パラメータ名 = 値 という行をパラメータごとに記述します HiRDB.ini ファイル (C:\Windows\HiRDB.ini) の指定例 PDHOST=s PDNAMEPORT=22200 なお 体験版 HiRDB をインストールすると HiRDB.ini ファイルは自動的に作成されます 20 指定必須のパラメータクライアント環境定義の次に示すパラメータは必ず指定する必要があります PDHOST このパラメータには アプリケーションプログラムが接続する HiRDB のホスト名を指定します PDNAMEPORT このパラメータには アプリケーションプログラムが接続する HiRDB のポート番号を指定します 上記のパラメータの指定によって アプリケーションプログラムが接続する HiRDB が決まります 体験版 HiRDB の場合 上記のパラメータは体験版 HiRDB のインストール時に自動的に設定されます

32 2 章アプリケーションプログラムの開発環境 (2) 任意のファイルへの指定 クライアント環境定義のパラメータを任意のファイルに記述しておき そのファイル名をアプリケーションプログラム中に記述 3 すると そのファイル中に記述したパラメータの値が有効になります 任意のファイルにパラメータを指定すると HiRDB への接続情報 (PDHOST で指定する接続先ホスト名や PDNAMEPORT で指定するポート番号等 ) をアプリケーションプログラム中に記述する必要がなくなります そのため 例えば 接続先が変更になった場合はファイル中の PDHOST および PDNAMEPORT の指定値を変更すればよいので アプリケーションプログラムの保守作業が容易になります 任意のファイル (C:\envgrp\clientdef) の指定例 [HIRDB] PDHOST=s PDNAMEPORT=22200 : 1 行目に [HIRDB] を記述する必要があります ファイル名称は任意です ここでは C: envgrp clientdef に作成したとします (3) クライアント環境定義を変更する場合 3 章以降で クライアント環境定義の各パラメータの説明がでてきます その際 クライアント環境定義にパラメータを追加する場合は HiRDB.ini ファイルまたは任意のファイルにパラメータを追加してください 3 アプリケーションプログラム中の記述方法については 5.4 HiRDB への接続 で説明します 21

33 2 章アプリケーションプログラムの開発環境 Break Time: 英字の大文字小文字の扱い HiRDB での英字の大文字小文字の扱いを次に示します 大文字小文字が区別される場合 表に格納されるデータ表に格納したデータは大文字小文字が区別されます 例えば s と S は異なるデータとして扱われます 名前( ダブルクォーテーション (") で括る場合 ) 表や列 インデックス ストアドプロシジャ ユーザー等の名前は ダブルクォーテーション ( ") で括れば 大文字と小文字が区別されます 例えば "TABLE1" と "table1" は異なるものとして扱われます 大文字小文字が区別されない場合 SQL でのキーワード例えば CREATE と create は同じキーワードとして扱われます 名前( ダブルクォーテーション (") で括らない場合 ) 表や列 インデックス ストアドプロシジャ ユーザー等の名前は ダブルクォーテーション ( ") で括らない限り 大文字として扱われます したがって 例えば TABLE1 と table1 は同じものとして扱われます 22

34 3 章データベースの作成 3 データベースの作成 この章では 体験版 HiRDB を使用してデータベースを作成する手順と 表 やインデックスを定義するときの考慮点について説明します 3.1 データベースを作成するのに必要な権限 3.2 データベースの作成手順 3.3 表を定義するときの考慮点 3.4 表の特殊な使い方 3.5 インデックスを定義するときの考慮点 3.6 インデックスの効果的な利用方法 3.7 表へのデータ格納方法 23

35 3 章データベースの作成 3.1 データベースを作成するのに必要な権限 HiRDB を利用するには HiRDB を利用する権限が必要になります HiRDB では 与えられた権限によって実行できる操作が制御されているため 表 ( データベース ) を定義するには表を定義する権限が 表にアクセスするには表にアクセスする権限が必要になります HiRDB の場合 次に示す権限があります ユーザ権限データベース管理者権限 DBA 権限 データベース利用者権限 CONNECT 権限 スキーマ定義権限アクセス権限 SELECT 権限 ( 表を検索するのに必要な権限 ) INSERT 権限 ( 表にデータを追加するのに必要な権限 ) DELETE 権限 ( 表のデータを削除するのに必要な権限 ) UPDATE 権限 ( 表のデータを更新するのに必要な権限 ) 図 HiRDB を利用する際に必要な権限 (1) ユーザ権限 HiRDB を利用するのに必要な権限です ユーザ権限にはデータベース管理者権限 (DBA 権限 ) とデータベース利用者権限 (CONNECT 権限 スキーマ定義権限 ) があります 各権限について次に説明します 表 ユーザ権限 権限の種類 説明 この権限でできる操作 DBA 権限 ユーザを管理するのに必要な権限です この権限があると ほかのユーザに権限 (DBA 権限 スキーマ定義権限 および CONNECT 権限 ) を与えたり 削除したりできます また ほかのユーザが所有する表を削除できます なお DBA 権限は スキーマ定義権限と CONNECT 権限を包括しています つまり DBA 権限を与えられると 自動的にスキーマ定義権限および CONNECT 権限も与えられたことになります したがって DBA 権限を与えられると スキーマを定義して表を定義することができます 他のユーザへの権限の付与と削除 スキーマの定義と削除 表およびインデックスの定義と削除 他のユーザのスキーマの定義と削除 他のユーザが所有する表およびインデックスの削除なお DBA 権限を持っている場合でも次に示す操作はできません 他のユーザが所有する表およびインデックスの定義変更 他のユーザが所有する表へのアクセス CONNECT 権限 スキーマ定義権限 HiRDB に接続するのに必要な権限です CONNECT 権限がないユーザは HiRDB を利用することができません また 表の所有者から表のアクセス権限を与えてもらうと その表に対してアクセスできるようになります 自分のスキーマを定義するのに必要な権限です スキーマを定義すると 表を定義できます ただし スキーマを定義するには CONNECT 権限も必要になります 他人が所有する表へのアクセス ( その表へのアクセス権限がある場合 ) 自分のスキーマの定義と削除 表およびインデックスの定義と削除 24

36 3 章データベースの作成 まとめ 表およびインデックスを定義するには CONNECT 権限とスキーマ定義権限が必要になります ユーザ権限を管理 ( 権限を与えたり 削除したりする等 ) するには DBA 権限が必要になります DBA 権限はスキーマ定義権限と CONNECT 権限を包括しているため DBA 権限が設定されていれば 表およびインデックスを定義できます スキーマスキーマとは 各ユーザが所有する表やインデックスなどを包含した概念です 利用者は 初めて表やインデックスなどを定義する前に スキーマを定義する必要があります なお 同一スキーマ内には 同じ名前の表やインデックスを定義できません インデックス インデックス 図 スキーマの概要 (2) アクセス権限表をアクセスするのに必要な権限です アクセス権限には 次に示す四つの種類があります SELECT 権限 ( 表を検索するのに必要な権限 ) INSERT 権限 ( 表にデータを追加するのに必要な権限 ) DELETE 権限 ( 表のデータを削除するのに必要な権限 ) UPDATE 権限 ( 表のデータを更新するのに必要な権限 ) 25

37 3 章データベースの作成 アクセス権限は表の所有者が管理します 表へのアクセスを許可するユーザに必要なアクセス権限を与えてください なお 表の所有者は その表に対するすべてのアクセス権限 (SELECT 権限 INSERT 権限 DELETE 権限 および UPDATE 権限 ) を持っています (3) 体験版 HiRDB をご利用の場合体験版 HiRDBをご利用の場合 APPUSER 4 というユーザが既に登録されています APPUSERにはDBA 権限 スキーマ定義権限 CONNECT 権限が設定されているため APPUSERを使用して新たに表を定義することができます なお ユーザに与えられているユーザ権限とアクセス権限は HiRDB Control Manager で確認できます また HiRDB Control Manager を使用して新規にユーザを登録し 権限を与えることもできます HiRDB Control Manager でのユーザ権限とアクセス権限の確認方法と ユーザの追加方法を次に示します 4 ユーザを追加する場合は このボタンをクリックします 1ここをクリックするとこの画面が表示されます 2 権限を確認したいユーザをクリックします 3 権限情報が表示されます 4 APPUSER は 体験版 HiRDB をインストールしたときに作成されるすべての表 ( 体験版 HiRDB をインストールすると作成される RD エリアと表 で説明した表 ) の所有者になっています 26

38 3 章データベースの作成 3.2 データベースの作成手順 ここでは 体験版 HiRDB を使用してデータベースを作成するときの手順について説明し ます 既に登録されているユーザ APPUSER を使用して表およびインデックスを定義し ます 体験版 HiRDB を使用してデータベースを作成するときの手順 体験版 HiRDB を使用してデータベースを作成するときの手順を次に示します なお 体験版 HiRDB は既にインストールされているとします 1CREATE TABLE で表を定義する 2CREATE INDEX でインデックスを定義する 3 定義した表にデータを格納する 1CREATE TABLE で表を定義する HiRDB SQL Executer を使用して CREATE TABLE を実行し 表を定義します 表を定義するときの考慮点については 3.3~3.4 で説明します CREATE TABLE の文法については 付録 A CREATE TABLE の文法 で説明します 2CREATE INDEX でインデックスを定義する HiRDB SQL Executer を使用して CREATE INDEX を実行し インデックスを定義します インデックスを定義するときの考慮点については 3.5~3.6 で説明します CREATE INDEX の文法については 付録 B CREATE INDEX の文法 で説明します 3 定義した表にデータを格納する HiRDB Control Manager を使用して 定義した表にデータを格納します データの格納方法については 3.7 で説明します 表を定義するユーザを新規に追加した場合ユーザを新規に追加した場合は 1CREATE TABLE で表を定義する の操作の前に CREATE SCHEMA でスキーマを定義する必要があります CREATE SCHEMA の文法については マニュアル HiRDB Version 8 SQL リファレンス を参照してください 27

39 3 章データベースの作成 3.3 表を定義するときの考慮点 ここでは 表を定義するときの主な考慮点として データ型と制約について説明します データ型 ここでは データ型を決めるときのポイントについて説明します HiRDB で使用できる データ型を次の表に示します 表 HiRDB で使用できるデータ型一覧 分類 データ型 データ形式 数データ INTEGER 整数 (4 バイトの 2 進形式 ) SMALLINT 整数 (2 バイトの 2 進形式 ) DECIMAL(m,n) 固定小数点数 ( (m+1)/2 バイトのパック 10 進形式 ) m は全体のけた数 n は小数点以下のけた数 FLOAT 倍精度浮動小数点数 (8 バイト ) SMALLFLT 単精度浮動小数点数 (4 バイト ) 文字データ CHAR(n) 固定長文字列 ( 長さ n バイト ) VARCHAR(n) 可変長文字列 ( 最大長 n バイト ) NCHAR(n) 固定長各国文字列 ( 長さ n 文字 ) NVARCHAR(n) 可変長各国文字列 ( 最大長 n 文字 ) MCHAR(n) 固定長混在文字列 ( 長さ n バイト ) MVARCHAR(n) 可変長混在文字列 ( 最大長 n バイト ) 日付 時刻データ DATE 日付 (4 バイトの符号なしパック形式 YYYYMMDD) TIME 時刻 (3 バイトの符号なしパック形式 hhmmss) TIMESTAMP(p) 時刻印 (7~10 バイトの符号なしパック形式 YYYYMMDDhhmmss nn...n ) nn...n は p けたの少数秒 バイナリデータ 長大データ BINARY(n) バイナリデータ ( 最大長 n バイト ) BLOB(n) 長大データ ( 最大長 n バイト ) ここでは 主なデータ型だけを説明しています HiRDB で使用できるデータ型の詳細については マニュアル HiRDB Version 8 解説 を参照してください 28

40 3 章データベースの作成 Oracle との違い Oracle と HiRDB のデータ型の対応については 付録 C Oracle と HiRDB のデータ型の対応 で説明します (1) データ型を使い分けるときの目安 数データ :INTEGER SMALLINT DECIMAL FLOAT SMALLFLT 数データのデータ型を決定する場合 扱うデータが整数なのか または実数なのかによって 次の表のように使い分けます 表 数データの使い分け データ型 整数 実数 けた落ちする けた落ちしない INTEGER SMALLINT DECIMAL FLOAT SMALLFLT ( 凡例 ) : 該当するデータ型を使用してください 備考 最大値が収まるのであれば 値の範囲の狭い方が格納効率が良くなります 例えば INTEGER より SMALLINT FLOAT より SMALLFLT の方が格納効率は良くなり ます 同じデータ長であれば INTEGER と SMALLINT FLOAT と SMALLFLT での性 能差はありません DECIMAL は 前記のデータ型に比べると性能面で劣ります 文字データ :CHAR VARCHAR NCHAR NVARCHAR MCHAR MVARCHAR 文字データのデータ型を決定する場合 扱うデータが半角文字なのか または全角文字なのかによって 次の表のように使い分けます 表 文字データの使い分け データ型 扱うデータ 半角 全角 半角と全角が混在 CHAR VARCHAR NCHAR NVARCHAR MCHAR MVARCHAR ( 凡例 ) : 該当するデータ型を使用してください 29

41 3 章データベースの作成備考 半角 全角とは それぞれ ASCII コードでの 1 バイト文字 2 バイト文字を表しています CHAR/VARCHAR と NCHAR/NVARCHAR の性能差はありません MCHAR と MVARCHAR は半角文字と全角文字の判定を行う処理があるため 前記のデータ型に比べると性能面で劣ります データがすべて半角文字とわかっている場合には CHAR または VARCHAR を すべて全角文字とわかっている場合には NCHAR または NVARCHAR を それ以外の場合には MCHAR または MVARCHAR を使用してください 対応するデータ型にしていないと 検索したときに意図した結果が返ってこないことがあります 日付 時刻データ :DATE TIME TIMESTAMP 日付を扱う場合は DATE 時刻を扱う場合は TIME を使用します また 日付と時刻を組み合わせたデータを扱う場合は TIMESTAMP を使用します ( アプリケーションプログラムで日時の比較処理を行う場合は TIMESTAMP を使用すると効率的に比較処理ができます ) バイナリデータ 長大データ :BINARY BLOB データの最大長が短い場合 (32,000 バイト以内 ) BINARY を使用します BINARY は BLOB に比べて格納効率が良く 専用の RD エリアも必要ないため ディスクリソース削減や アクセス性能の向上が見込めます 上記に該当しないデータが 1 件でもある場合は BLOB を使用します (2) 固定長と可変長を使い分けるときの目安通常は可変長のデータ型にします 可変長はデータの長さに合わせて格納するため 固定長に比べると格納効率が良くなります 固定長でデータを格納する場合 データ長に満たない部分に空白を埋めてデータを格納します 列のデータの長さが固定の場合は 固定長を使用した方が処理性能は良くなります ( 例えば 従業員コードなど 文字数が決まっているケース ) NO SPLIT オプション 256 バイト以上の可変長のデータ型 (BLOB BINARY を除く ) では NO SPLIT オプションを指定して下さい RD エリアの格納効率の低下を緩和することができます HiRDB のバージョンが 以降の場合は設定不要です 例 ) c1 MVARCHAR(512) NO SPLIT NOT NULL 30

42 3 章データベースの作成 可変長のデータ型の 0 バイトデータの扱い可変長のデータ型では 0 バイトデータ (' ' などの空文字 ) もデータとして扱います なお 可変長のデータ型とは VARCHAR NVARCHAR MVARCHAR BLOB および BINARY を指します Oracle との違い Oracleの場合 長さ 0 の文字列 ( ) はナル値として扱われます 5 一方 HiRDBでは 長さ 0 の文字列は標準 SQL 同様ナル値ではなくそのまま長さ 0 の文字列として扱われます 例えば 可変長の列に長さ 0 のデータを格納する場合 長さ 0 の文字列が格納され 固定長の場合は 空白が格納されます ナル値と長さ 0 の可変長文字列 ( 及び空白だけの固定長文字列 ) は 異なる値です 探索条件中での比較結果も異なります Oracle と同様に HiRDB でも長さ 0 の文字列をナル値と同じとして扱うには スカラ関数の NULLIF を用います NULLIF は 第一引数と第二引数が一致すれば NULL を 一致しなければ第一引数の値をそのまま返します 例 ) SELECT NULLIF( 列名,'') FROM 表名 ; INSERT INTO 表名 VALUES (NULLIF(: 変数名,'')); ナル値の注意点 INSERT 時に初期値としてナル値 (NULL) を格納した後に実データで UPDATE すると RD エリアの格納効率が低下します ナル値から実データへの更新件数が多い場合は 初期値はナル値ではなく実データを格納することを検討して下さい なお CREATE TABLE 文に DEFAULT 句を指定しておくことで INSERT 時に実データではなく DEFAULT を指定することも可能です 例 ) INSERT 時に NULL ではなく初期値を格納する場合の変更例 変更前 INSERT INTO 表名 VALUES (1,NULL); UPDATE 表名 SET C2 = WHERE C2 IS NULL; 変更後 INSERT INTO 表名 VALUES (1, ); 1 UPDATE 表名 SET C2 = WHERE C2 = ; 2 1: 業務的に使用しない日付 (2000/1/1) を初期値として格納 2: 初期値を実データ (2014/10/1) に更新 5 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 4 演算子 中の 連結演算子 で説明されています 31

43 3 章データベースの作成 (3) データの格納時 および比較 / 変換時の空白の扱い データ格納時の空白の扱い 定義長 ( 表定義時に指定した列の長さ ) より短いデータを格納する場合 固定長と可変長とでは 格納されるデータの扱いが異なります 固定長の場合は足りない部分に空白が埋められて格納されます 可変長の場合はそのまま格納されます 例を次に示します CHAR(8) C1 列 S 半半半 VARCHAR(8) VC1 列 S データを格納 S S NCHAR(8) NC1 列鈴木次郎全全全全 NVARCHAR(8) NVC1 列鈴木次郎 MCHAR(10) MC1 列電子レンジ半 半半 鈴木次郎 鈴木次郎 電子レンジ MVARCHAR(10) MVC1 列 電子レンジ 電子レンジ ( 凡例 ) 半 : 半角の空白 (X'20') を表します 全 : 全角の空白を表します 図 データ格納時の空白の扱い 比較 / 変換時の空白の扱い 文字データおよび混在文字データを比較 変換する場合 埋字の空白として半角文字の空白 (X'20') を使用します また 各国文字データを比較 変換する場合には 埋字の空白として全角文字の空白を使用します ( 全角文字の空白のコードは 使用している文字コードに依存します ) 例を次に示します 32

44 3 章データベースの作成 固定長と可変長の列の比較 C1=VC1 という条件を指定すると 実データ長の長い方の長さまで空白が埋められた状態で比較されます CHAR(10) C1 列 S 半半半半半 VARCHAR(12) VC1 列 S CHAR(5) C1 列 S VARCHAR(10) VC1 列 S S 半半半半半 比較 = S 半半半半半 比較した結果 同じデータと認識 比較 S = S 比較した結果 同じデータと認識 可変長の列同士の比較 VC2=VC3 という条件を指定すると 実データ長で比較されます NVARCHAR(5) VC2 列 NVARCHAR(10) VC3 列 NVARCHAR(10) VC2 列 NVARCHAR(10) VC3 列 電子レンジ 電子レンジ テレビ全全全全 テレビ 電子レンジ 比較 = 電子レンジ テレビ全全全全 比較 テレビ 比較した結果 同じデータと認識 比較した結果 異なるデータと認識 ( 凡例 ) 半 : 半角の空白 (X'20') を表します 全 : 全角の空白を表します 図 データの比較 変換時の空白の扱い 固定長と可変長での条件の指定の仕方 固定長と可変長では 同じデータであっても条件の指定方法によってはヒットしないこと があります 例えば 次のような二つの列があるとします C1 CHAR(5) C2 VARCHAR(10) 上記の列に S100 というデータを格納した場合 次の表のような検索結果となりま す ( は空白を表します) 表 固定長と可変長での結果の違い 指定する条件 検索結果 C1 C2 ='S100' S100 がヒットします ヒットしません LIKE 'S100' ヒットしません LIKE 'S100%' S100 がヒットします 33

45 3 章データベースの作成 制約 表を定義するとき データが意図した値になるように ユーザのデータ操作をチェックする機能を設定できます このユーザのデータ操作をチェックする機能を制約といいます 制約に違反した場合は SQL エラーとなります 制約の種類制約には 次の四つの種類があります 非ナル値制約 一意性制約 検査制約 参照制約 非ナル値制約 指定した列に対して ナル値を禁止する制約です 非ナル値制約を定義した列には 必ず何らかの値を格納する必要があります 一意性制約指定した列に対して 値の重複を禁止する制約です 一意性制約を定義した列には 必ず一意な値 またはナル値を格納する必要があります また 一意性制約を定義した列には自動的にインデックスが作成されます 一意性制約は表定義時に CREATE TABLE で定義しますが インデックス定義時にも CREATE INDEX で定義できます 検査制約 指定した条件を満たさない値の更新を禁止する制約です 34

46 3 章データベースの作成 参照制約二つの表の間に関連性を持たせて データの矛盾を防ぐ制約です 参照している側の表を参照表といい 参照表の共通のキーとなる列を外部キーといいます また 参照されている側の表を被参照表といい 被参照表の共通のキーとなる列が主キーとなります 主キーを定義した列には 自動的に非ナル値制約と一意性制約が適用され インデックスも作成されます 参照制約の概要を次の図に示します 1 削除 2 更新 (B B') dept 表 ( 被参照表 ) dept_no dept_name A 第一システム部 B 第二システム部 C 営業部 emp 表 ( 参照表 ) emp_no emp_name dept_no 1001 鈴木 A 1002 小笠原 A 1003 横山 A 1004 武 B 1005 山田 B 3 追加 1006 山田 D [ 説明 ] 1 参照表である emp に dept_no 列の値が A の行が存在するため この削除要求は参照制約に抵触します この場合 参照制約動作の定義により以下のような結果となります 参照制約動作の定義 DELETE RESTRICT( デフォルト ) DELETE CASCADE 結果エラー削除要求のあった dept 表上の行が削除され 関連する emp 表の行 (dept_no が A の行 ) も全て削除される 2 参照表である emp に dept_no 列の値が B の行が存在するため この更新要求は参照制約に抵触します この場合 参照制約動作の定義により以下のような結果となります 参照制約動作の定義 UPDATE RESTRICT( デフォルト ) UPDATE CASCADE 結果エラー更新要求のあった dept 表上の行の dept_no 列の値が B' に更新され 関連する emp 表の行 (dept_no が B の行 ) の dept_no 列の値も全て B' に更新される 3 被参照表である dept に dept_no 列の値が D の行が存在しないため この追加要求は参照制約に抵触します この場合 追加要求がエラーとなります 図 参照制約の概要 35

47 3 章データベースの作成 表の定義例 商品表 (GOODS) を 次の仕様に基づいて定義します 商品表の仕様 商品表 ( 表名 :GOODS) 仕様 日本語列名 実装上の列名 データ型 備考 商品コード GOODS_CODE CHAR(10) 非ナル値制約主キー 商品名 GOODS_NAME MVARCHAR(85) 非ナル値制約 商品単価 GOODS_PRICE INTEGER 非ナル値制約検査制約 ( 商品単価 0) 商品小分類 GOODS_SCLASS NCHAR(20) 非ナル値制約 商品大分類 GOODS_BCLASS NCHAR(20) 非ナル値制約 商品在庫 GOODS_STOCK INTEGER 非ナル値制約検査制約 ( 商品在庫 0) 商品写真 GOODS_PICTURE CHAR(20) - 商品説明 GOODS_NOTES MVARCHAR(255) - 生産者 ID SUPPLIER_ID CHAR(10) 非ナル値制約外部キー ( 参照先は SUPPLIERS 表の SUPPLIER_ID 列 ) 商品表の定義例 CREATE TABLE goods( goods_code CHAR(10) NOT NULL PRIMARY KEY IN rdindx10, goods_name MVARCHAR(85) NOT NULL, goods_price INTEGER NOT NULL CHECK(goods_price>=0), goods_sclass NCHAR(20) NOT NULL, goods_bclass NCHAR(20) NOT NULL, goods_stock INTEGER NOT NULL CHECK(goods_stock>=0), goods_picture CHAR(20), goods_notes MVARCHAR(255), supplier_id CHAR(10) NOT NULL REFERENCES suppliers ) IN rddata10 CREATE TABLE の文法については 付録 A CREATE TABLE の文法 で説明します 36

48 3 章データベースの作成 HiRDB SQL Executer で定義系 SQL を実行するときの注意事項 一つのトランザクション内で操作系 SQL と定義系 SQL を連続して実行することはできません HiRDBは 定義系 SQLの実行後だけCOMMITを自動的に発行しているため HiRDB SQL Executerで INSERTの実行に続いてCREATE TABLEを実行すると INSERTと CREATE TABLEが 1 トランザクションで実行されるため エラー 6 になります 定義系 SQL の実行前にも自動的に COMMIT を発行するには クライアント環境定義の PDCMMTBFDDL パラメータに YES を指定する必要があります Oracle との違い Oracleは 定義系 SQLの実行前後でCOMMITを自動的に発行するため 7 OracleのSQL インタプリタツールで INSERTの実行に続いてCREATE TABLEを実行しても INSERTとCREATE TABLEが別トランザクションとなるため エラーになりません 6 ただし HiRDB SQL Executer のオートコミットモードをオンにしている場合 (SET AUTO COMMIT コマンドで ON を指定した場合 ) 1つの SQL 文の実行が 1 つのトランザクションとなるためエラーになりません 7 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 13 SQL 文 : ALTER TRIGGER~COMMIT 中の COMMIT で説明されています 37

49 3 章データベースの作成 3.4 表の特殊な使い方 ここでは 表の特殊な使い方として パブリックビューと自動採番機能について説明しま す パブリックビュー DB 上に存在する全ユーザで共有できるビューを パブリックビューといいます パブリックビューを使うことで ビューの所有者を意識せずに 作成者以外のユーザからも ビューに対するアクセスが可能です パブリックビューは 実表またはビュー表から作成することができます 作成者は 実表またはビュー表の所有者です (1) パブリックビューの定義方法 文法 create public read only view 表名 ( 列名, 列名 ) as select 文 public ビュー表をパブリックビューとして定義する場合に指定します 注意事項作成するパブリックビューの名前には 既に定義されている表 ( 実表 外部表 ビュー表 ) と同じ表名を指定できます その場合は 表名の前に 引用符 ( ) で囲んだ大文字の PUBLIC を指定し パブリックビューを検索します ユーザ名を省略して表名を使用する場合 AP 実行ユーザが所有している表 ( 実表 外部表 及びビュー表 ) がパブリックビューより優先されます 38

50 3 章データベースの作成 例 パブリックビュー P_VIEW 表を作成する create public view P_VIEW as select * from SEMINARS where SUBJECT_ID = 'DB01' SEMINARS 表から パブリックビュー SEMINARS 表を作成する create public view SEMINARS as select * from SEMINARS where SUBJECT_ID = 'DB01' パブリックビュー P_VIEW を検索する select * from P_VIEW パブリックビュー SEMINARS を検索する select * from "PUBLIC".seminars select * from seminars; 実表の SEMINARS 表が検索される (2) パブリックビューの削除方法 文法 drop public view 表名 例 パブリックビュー P_VIEW を削除する drop public view P_VIEW Oracle との違い Oracleの場合はビューの元となる表を削除してもビュー自体は削除されませんが 8 HiRDBの場合はビューの元となる表を削除するとビューも削除されます したがって HiRDBの場合 ビューの元となる表を削除した後にその表を再定義したときは ビューも再定義する必要があります 8 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 18 SQL 文 : DROP SEQUENCE~ROLLBACK 中の DROP TABLE で説明されています 39

51 3 章データベースの作成 自動採番機能 自動採番機能とは データベース中でデータを呼び出すごとに一連の整数値を返す機能です この機能は 順序数生成子を定義することで使用できます 自動採番機能を使用すると 採番を行う UAP の開発効率が向上します また 順序数生成子をサポートしている他 DBMS で作成した UAP からの移行性も向上します Oracle との違い HiRDBの順序数生成子機能は Oracleのシーケンスオブジェクトと同様の操作で自動採番が可能です 9 Oracle の場合は SQL 文に最新の値の次の値を取得する NEXTVAL と 現在値を取得する CURRVAL の指定ができます 一方 HiRDB の順序数生成子機能は 最新の値の次の値を取得する NEXT VALUE 式の指定はありますが 現在値を取得する指定がありません (1) 順序数生成子を作成する例 Oracle HiRDB (2) 採番データを基に業務テーブルへ行を追加する例 Oracle HiRDB 本例は 1 から発番して 1 ずつ増加させる順序数生成子を示しています ( 最大値 最小値の設定はしていません ) 9 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 15 SQL 文 : CREATE LIBRARY~CREATE SPFILE 中の CREATE SEQUENCE- 用途 で説明されています 40

52 3 章データベースの作成 3.5 インデックスを定義するときの考慮点 インデックス 10 はアプリケーションプログラムの性能に大きく影響するため どの列にイ ンデックスを定義するかを十分に検討する必要があります ここでは インデックスを定義するときの考慮点について説明します インデックスを定義すると効果が期待できるケース どのような列にインデックスを定義するとよいのか 主なケースについて説明します インデックスを定義すると効果が期待できるケース 次の表に示す 列の条件 に当てはまる列にインデックスを定義すると効果が期待できます 表 インデックスを定義すると効果が期待できる列列の条件理由データを絞り込むための条件に使用する列条件に合致した行を インデックスから直接検索するため 処理が速くなります 表の結合処理の条件として使用する列データのソート またはグループ分けの条件として使用する列 集合関数の MAX MIN を指定する列参照制約を定義した列 ( 外部キー列 ) インデックスはソート済みであり ソート処理が不要となるため 処理が速くなります 被参照表で 参照する列が他から参照されていないかのチェック処理が実行されます インデックスが定義されていると チェック処理は先頭から行わないで 条件に合致した行を検索するため 処理が速くなります インデックスを定義すると逆効果になるケース次の条件に当てはまる列にインデックスを定義すると 逆にアクセス性能が低下します 更新頻度の高い列データの追加 更新 および削除をするときに 関係するインデックスがすべて更新されるため インデックスの数が処理効率に影響します データの重複度が高い列重複度が高いと 何件もヒットしてしまうため 処理性能が低下します インデックスを定義しても効果が期待できないケース次の条件に当てはまる列にインデックスを定義しても効果は期待できません 検索条件への使用頻度が低い列 全体のデータ量が少ない表 10 HiRDB のインデックスは B-tree 構造になっています 41

53 3 章データベースの作成 インデックスの定義が適切でない場合の問題点インデックスの定義が適切でないと次の問題が発生することがあります インデックスを利用したヒット率の高い検索処理を行うと リーフページから表への I/O 数が ヒット件数と同数となるため 表の全データページ数 ( テーブルスキャン時の I/O 数 ) を大きく超える恐れがあります インデックスを定義した列に対して頻繁に更新処理が行われると データと共にインデックスを更新するためのオーバヘッドが発生します インデックスが効果的に使用されているかどうかを HiRDB SQL Tuning Advisor を使用して確認できます 詳細については 6 章 SQL のチューニング で説明します インデックスを定義できない列次のデータ型の列にはインデックスを定義できません 全体のけた数が 20 けた以上の DECIMAL BLOB BINARY インデックスでのナル値の扱い表の列のデータがナル値の場合でも インデックスのキー値は作成されますが インデックスの定義時に EXCEPT VALUES を指定すると ナル値のインデックスのキー値は作成されません そのため インデックスの容量削減 および行の挿入 / 削除 / 更新時のインデックスメンテナンスのオーバヘッドとログ量を削減できます 次の条件を両方満たす場合には インデックス定義時に EXCEPT VALUES を設定することをお勧めします 列にナル値が多い インデックス容量を節約したい EXCEPT VALUES の指定例を次に示します 例 ) 表 T1 に C1 C2 C3 という列があり C2 に EXCEPT VALUES 指定のインデックス I1 を定義します CREATE INDEX I1 ON T1(C2) EXCEPT VALUES (NULL) 42

54 3 章データベースの作成 単一列インデックスと複数列インデックス インデックスには単一列インデックスと複数列インデックスがあります 一つの列に定義するインデックスを単一列インデックス 複数の列に定義するインデックスを複数列インデックスといいます 複数列インデックスは 単一列インデックスを組み合わせたものです 単一列インデックスを定義すると効果が期待できる列 探索条件に指定する列 結合条件に指定する列 ソートおよびグループ化に使用する列 データの分布が大きな列 ( 重複データが少ない列 ) 複数列インデックスを定義すると効果が期待できる列 論理演算 AND を使用して複数の探索条件を指定する列 探索条件でデータを絞り込んだ後 並び替えおよびグループ分けをおこなう時に指定する列条件に指定する列が複数ある場合や 結合条件に指定する列が複数ある場合などは 複数列インデックスを定義した方が効果的です 例を次に示します 条件に指定する列が複数ある場合の例次のように C1 C2 および C3 の三つの列をキー項目として条件を指定するとします SELECT * FROM T1 WHERE C1=10 AND C2=20 AND C3=30 この場合 C1 C2 および C3 のそれぞれの列に単一列インデックスを作成するのではなく C1 C2 および C3 の三つの列で構成する複数列インデックスを作成した方が効果的です 43

55 3 章データベースの作成 インデックスの優先順位 where 句に and による複数かつ異なる述語 (IN 述語 BETWEEN 述語等 ) の条件が指定されている場合 その述語の種類によってインデックス使用の優先度が決まっています 例えば where 句に次の条件が指定されている場合 C2 列上のインデックスが使用されます where C1 like abc% and C2=100 これは like よりも = の方が インデックス優先順位が高いためです 条件ごとのインデックス優先順位を以下の表に示します 表 インデックスの優先順位 優先順位 条件 インデックスがある列 (C1) に対する検索条件の指定例 1 = 条件列の UNIQUE インデックス C1=100 2 = 条件列のインデックス C1=100 3 IS NULL 条件列のインデックス C1 IS NULL 4 LIKE 述語または SIMILAR 述語列のインデックス ワイルドカード % を使用かつ前方一致比較 C1 LIKE 'abc%' C1 SIMILAR TO 'ABC%' 5 LIKE 述語列または SIMILAR 述語列のインデックス ワイルドカード _ を使用かつ前方一致比較 C1 LIKE ' abc_' C1 SIMILAR TO 'ABC_' 6 IN 述語の制限条件の対象になる列にあるインデックス C1 IN(10,20,30) BETWEEN 述語列のインデックス C1 BETWEEN 20 AND 40 7 範囲条件を指定した列のインデックス C1>=20 AND C1<=40 IN 述語列のインデックス C1 IN(SELECT C1 FROM T2) 外への参照がない副問合せを使用 8 限定述語 =ANY または =SOME 列のインデックス C1=ANY(SELECT C1 FROM T2) 外への参照がない副問合せを使用 C1=SOME(SELECT C1 FROM T2) 9 > >= < <= 条件列のインデックス C1>50 C1<= スカラ演算指定列のインデックス システム定義スカラ関数と length(c1)=10 IS_USER_CONTAINED_IN_HDS_GROUP は除く 11 NOT BETWEEN 述語列のインデックス C1 NOT BETWEEN 10 AND XLIKE 述語および上記 4,5 以外の LIKE 列のインデックス? パラメタを指定している LIKE 列のインデクス 13 集合関数 (MIN または MAX) の引数指定列のインデックス C1 XLIKE 'abc%' C1 LIKE '%abc%' C1 LIKE? MIN(C1) MAX(C1) 14 結合条件列 グループキー ソート列にあるインデックス ORDER BY C1 否定 (NOT BETWEEN を除く ) 指定列にあるインデックス C1 NOT LIKE '%ABC%' C1 IS NOT NULL 上記 7 以外の限定述語 ANY または SOME 列のインデックス インデックスを使用しない 11 C1>=ANY(SELECT C1 FROM T2) C1>SOME(SELECT C1 FROM T2) 限定述語 ALL 列にあるインデックス C1>ALL(SELECT C1 FROM T2) 表中で使用されている用語のうち 本書で説明されていない用語については HiRDB のマニュアルを参照してください 11 キースキャンを行わせることが可能な場合は 当該列にインデックスを作成します キースキャンについては キースキャン で説明します 44

56 3 章データベースの作成 インデックスの定義例 商品表 (GOODS) のインデックス (G_GCODE_IDX) を 次の仕様に基づいて定義しま す 商品表の仕様 商品表 ( 表名 :GOODS) 仕様 日本語列名 実装上の列名 データ型 インデックス名 備考 商品コード GOODS_CODE CHAR(10) - 非ナル値制約主キー 商品名 GOODS_NAME MVARCHAR(85) G_GNAME_IDX 非ナル値制約 商品単価 GOODS_PRICE INTEGER - 非ナル値制約検査制約 ( 商品単価 0) 商品大分類 GOODS_BCLASS NCHAR(20) - 非ナル値制約 商品小分類 GOODS_SCLASS NCHAR(20) - 非ナル値制約 商品在庫 GOODS_STOCK INTEGER 非ナル値制約検査制約 ( 商品在庫 0) 商品写真 GOODS_PICTURE CHAR(20) - - 商品説明 GOODS_NOTES MVARCHAR( ) 生産者 ID SUPPLIER_ID CHAR(10) G_SID_IDX 非ナル値制約外部キー ( 参照先は SUPPLIERS 表の SUPPLIER_ ID 列 ) 商品表のインデックスの定義例 CREATE INDEX G_GNAME_IDX ON GOODS(GOODS_NAME) CREATE INDEX G_SID_IDX ON GOODS(SUPPLIER_ID) CREATE INDEX の文法については 付録 B CREATE INDEX の文法 で説明します 45

57 3 章データベースの作成 3.6 インデックスの効果的な利用方法 インデックスを効率良く使用するには SQL の内容に合わせて 適切な列にインデックスを作成する必要があります 基本的には 前節で説明した適用基準に従いますが さらに SQL の条件内容や構文に対応させることで インデックスの効果を最大限に引き出すことができます ここでは インデックスの効果的な利用方法の中で代表的なものを説明します 検索条件を指定する場合 検索条件列にインデックスを作成すると スキャン対象の範囲が限定されるため アクセス効率を上げることができます さらに検索条件に応じてインデックスを使い分けることで より高い効果を得ることができます theory1 = 条件が 1 つのみ指定されており かつ条件のヒット率が低い場合は 検索条件列に単一列インデックスを作成する 検索条件のヒット率が低い場合は インデックススキャン 12 が効果的です したがって 検索条件列に単一列インデックスを作成します select * from SEMINARS where SUBJECT_ID = DB01 SEMINARS SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 条件 ヒット率は 0.1% SUBJECT_ID 列に単一列インデックスを作成する 図 ヒット率が低い検索 12 インデックススキャンについては インデックススキャン で説明します 46

58 3 章データベースの作成 theory2 検索条件に and を使用しており かつ両方または片方の条件のヒット率が低い場合は 検索条件列を全て含む複数列インデックスを作成する and で指定している検索条件列の全てがインデックスの構成列に含まれていると 条件の 指定順にかかわらず インデックスに対する絞込みを 最も効率良く行うことができます select * from SEMINARS where START_DATE = and SUBJETC_ID = DB02 SEMINARS SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 条件 1 第 1 条件のヒット率は 0.2% 2 第 2 条件のヒット率は 30% (START_DATE,SUBJECT_ID) 列に複数列インデックスを作成する 図 and 演算子を使用する検索 and で指定した検索条件列のうち 一部の列のみがインデックスの構成列に含まれている場合であっても インデックスに対する絞込みは有効となります ただし インデックスの第一構成列が and で指定した検索条件列のいずれかに指定されていなければなりません また 検索条件列の全てがインデックスの構成列に含まれている場合に比べ 効果は劣ります 47

59 3 章データベースの作成 theory3 検索条件に or を使用しており かつ両方の条件のヒット率が低い場合は それぞれの 検索条件列に 単一列インデックスを作成する or を指定した各検索条件列に対し 単一列インデックスを作成すると インデックスに対 する絞込みを効率良く行うことができます select * from SEMINARS where START_DATE = or SUBJETC_ID = DB02 SEMINARS SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 条件 1 第 1 条件のヒット率は 0.2% 2 第 2 条件のヒット率は 0.3% START_DATE 列と SUBJECT_ID 列にそれぞれ単一列インデックスを作成する 図 or 演算子を使用する検索 48

60 3 章データベースの作成 theory4 検索対象列 (SELECT 句指定列 ) が限られる場合は 検索条件のヒット率にかかわら ず 検索条件列と検索対象列を組み合わせた複数列インデックスを作成する ただし 検索条件列をインデックスの第一構成列とする 特定の列が SELECT 句に指定されている場合は ヒット率にかかわらず実行時にキースキャンを行うと最も効果的です 検索条件が指定されている場合は 検索条件列と検索対象列を組み合わせた複数列インデックスを作成します このとき 検索条件列をインデックスの第一構成列とします すると 絞込みと必要なデータの取得をインデックス上で行うことができ オーバヘッドとなる表データへのアクセスをなくすことができます select INST_ID from INSTRUCTORS where HIRE_YEAR = 1990 INSTRUCTORS INST_ID INST_NAME HIRE_YEAR INST_GRADE 条件 ヒット率は 30% (HIRE_YEAR,INST_ID) 列に複数列インデックスを作成する 図 検索対象列を限定する検索 49

61 3 章データベースの作成 ORDER BY 句を指定する場合 ORDER BY 句によるソート処理の負荷を軽減させるためには インデックスを利用すると効果的です ただし HiRDB の場合 インデックスによるソート処理の負荷を軽減するためには SELECT 文に検索条件が指定されており かつ検索条件列と ORDER BY 句に指定されている列 ( ソート基準列 ) から構成されるインデックスが定義されていることが前提条件となります theory5 検索条件に = が指定されており かつ条件のヒット率が低い場合は 検索条件列とソート基準列を組み合わせた複数列インデックスを作成する ただし 検索条件列をインデックスの第一構成列とする 検索条件に = が指定されている場合にインデックスをソート処理に使用するためには 検索条件列とソート基準列を組み合わせた複数列インデックスを作成します このとき 検索条件列をインデックスの第一構成列とします すると インデックスの第一構成列に対する絞込みを行った後 第二構成列のキー順にデータアクセスを行うことができるため 内部的なソート処理を行うことなくソート済みの結果を得ることができます select * from SEMINARS where SUBJECT_ID = AP01 order by START_DATE SEMINARS SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 条件 ヒット率は 0.3% (SUBJECT_ID,START_DATE) 列に複数列インデックスを作成する 図 ORDER BY 句を指定した検索 ( その 1) 50

62 3 章データベースの作成 theory6 検索条件に = 以外が指定されており かつ条件のヒット率が低い場合 ORDER BY 句の中で検索条件列を第一ソートキー 13 に指定する また 検索条件列とソートキーを組み合わせた複数列インデックスを作成する ただし 検索条件列をインデックスの第一構成列とする 検索条件に = 以外が指定されている場合にインデックスをソート処理に使用するためには SELECT 文の ORDER BY 句で 検索条件列を第一ソートキーに指定する必要があります また 検索条件列とソートキーを組み合わせた複数列インデックスを作成します このとき 検索条件列をインデックスの第一構成列とします さらに ORDER BY 句に複数のソート基準列を指定している場合 ORDER BY 句内のソート順と複数列インデックス上のソート順は 完全に同一の向きまたは完全に逆向きに対応している必要があります select * from SEMINARS where INST_ID >= 9020 order by INST_ID asc,seminar_id desc SEMINARS SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 条件 ヒット率は 0.1% (INST_ID asc,seminar_id desc) 列に複数列インデックスを作成する ( INST_ID desc,seminar_id asc) でも可 ) 図 ORDER BY 句を指定した検索 ( その 2) 13 ORDER BY 句に指定した列をソートキーといいます また ソートキーが複数ある場合は ORDER BY 句の先頭に指定した列を第一ソートキーといいます 51

63 3 章データベースの作成 結合処理を指定する場合 結合処理を行う際は 複数の表をスキャンする必要があるため 実行時に選択された結合処理によっては負荷が高くなります そこで 結合する表の行数や SQL 文の内容から 最も効率の良い結合方法が選択されるようにインデックスを作成する必要があります theory7 検索条件によって結合対象表の母体件数を絞り込める場合は 外表の検索条件列と内表の結合条件列のそれぞれを第一構成列に指定したインデックスを作成する 検索条件列によって結合対象表を絞り込める場合は ネストループ結合 14 が最適です ネストループ結合を効率良く行わせるためには 絞り込める表を外表とし 内表の結合列に対するインデックスを作成する必要があります 同時に 外表の絞り込みを効率良く行わせるために 検索条件列に対しインデックスを作成します select STU.STU_ID,STU_NAME,SEMINAR_ID,INST_NAME,S_F_FLAG from STUDENTS STU inner join ATTENDANCE_HISTORY AT on STU.STU_ID = AT.STU_ID where STU.STU_ID = 4 STUDENTS:10,000 件 STU_ID STU_NAME CORP_ID 1 Taro Yamada A001 2 Jiro Ito K003 3 Hanae Suzuki H153 4 Yoshihiro Sato A045 ATTENDANCE_HISTORY:100,000 件 SEMINAR_ID STU_ID INST_ID INST_NAME ROOM S_F_FLAG 05K Hiroshi Sato K Tsukasa Goto K Daisuke Yoshida K Megumi Takahashi K Toshio Mamiya K Keiko Kikuchi K Hiroshi Sato K Eri Yokoyama ATTENDANCE_HISTORY 表の STU_ID 列に単一列インデックスを作成する ( STUDENTS 表の STU_ID 列は主キーであるため インデックスは自動的に作成される ) 図 結合を使用した検索 14 ネストループ結合については 表の結合方式 で説明します 52

64 3 章データベースの作成 3.7 表へのデータ格納方法 表 インデックスを定義した後に HiRDB Control Manager を使用して表にデータを格 納します データの格納手順を次に示します < 手順 > 1. 入力データを CSV 形式で作成します 入力データの例を次に示します 中村,36, ,yokohama 鈴木,25, ,kamakura 田中,41, ,shibuya 佐藤,36, ,kawasaki 小田,46, ,shinjuku : 2. 1 で作成した CSV 形式の入力データを HiRDB Control Manager を使用して表に格 納します HiRDB Control Manager で次に示す設定を行い [ 実行 ] ボタンをクリックしてくだ さい 2データを格納する表の表名 表の所有者名とパスワードを指定します 所有者が APPUSER の場合 パスワードは STDDB になります 3この欄は デフォルトのまま変更しないでください 1 ここをクリックすると この画面が表示されます 4 手順 1 で作成した CSV 形式の入力データファイルを指定します 53

65 3 章データベースの作成 追加モードと作成モードデータ処理方針欄には 追加モード と 作成モード の指定があります 追加モードを指定すると 既にあるデータを残したまま 入力データを追加します 作成モードを指定すると 既にあるデータをすべて削除してから 入力データを格納します したがって 一度データを格納した後に追加でデータを格納する場合は 追加モードを指定してください 54

66 4 章アプリケーションプログラムの開発 4 アプリケーションプログラムの開発 この章では HiRDB にアクセスするアプリケーションプログラムの記述内容や 開発にあたっての考え方 注意点について説明します ここでは 原則的に 各種開発手法 ( 埋込み SQL や JDBC ADO.NET 等 ) に依存しない汎用的な内容について扱います Java を使用したアプリケーションプログラム開発については 5 章で説明します 4.1 アプリケーションプログラムの基本構成 4.2 静的 SQL と動的 SQL 4.3 埋込み変数 4.4 データ型に関する注意点 4.5 HiRDB と Oracle のスカラ関数の対応 4.6 カーソル 4.7 効果的な SQL の使い方 4.8 排他制御 4.9 SQL のエラー判定 4.10 性能が向上する SQL の書き方 4.11 保守性を良くする SQL の書き方 55

67 4 章アプリケーションプログラムの開発 4.1 アプリケーションプログラムの基本構成 HiRDB にアクセスするアプリケーションプログラムの基本構成は 以下のようになり ます (1) 変数の宣言 (2) HiRDB との接続 (3) SQL によるデータの検索 / 更新 (4) エラー処理 (5) トランザクションのコミット (6) HiRDB からの切断 (1) 変数の宣言 埋込み SQL の場合 埋込み変数を宣言します 埋込み変数については 4.3 埋込み変数 で説明します (2) HiRDB との接続 アプリケーションプログラムと HiRDB とを接続します アプリケーションプログラムは HiRDB との接続が確立されると SQL による処理要求をすることが可能となります (3) SQL によるデータの検索 / 更新業務内容にあわせ SQL 文でデータを検索 / 更新します 複数の行を検索するには カーソルを使用します カーソルについては 4.6 カーソル で説明します (4) エラー処理 HiRDB にアクセスするアプリケーションプログラムでは 原則的に SQL 文を実行するたびに SQL 文の実行が成功だったのか / エラーだったのかを判定し エラーだった場合に行う処理を記述する必要があります SQL 文の実行がエラーだった場合 明示的にトランザクションをロールバックします 56

68 4 章アプリケーションプログラムの開発 ただし 実行した SQL 文が参照 (SELECT 文 ) のみであることが明白な場合は 明示的にロールバックしなくても問題ありません SQL 文の実行が成功だったのか / エラーだったのかの判定については 4.9 SQL のエラー判定 で説明します Oracle との違い Oracleの場合はSQLエラーの発生時 自動的にトランザクションをロールバックしますが 15 HiRDBの場合はSQLエラーの発生時 自動的にトランザクションをロールバックする場合と 自動的にはロールバックしない場合とがあります したがって SQLエラーの発生時 明示的にトランザクションをロールバックするようにプログラミングする必要があります (5) トランザクションのコミットコミット処理をし それまでに行った SQL 文を 1 トランザクションとして確定します 埋込みSQLでは 最初に実行したSQL 文がトランザクションの開始となります 16 JDBC やADO.NETの場合 オートコミットがデフォルトとなります オートコミットとは SQL 文を実行するとトランザクションが自動的にコミットされることです そのため オートコミットが有効な場合 アプリケーションプログラム中でコミットを明示する必要はありません JDBCで複数のSQL 文を 1 トランザクションにする方法については 5 章 Java を使ったアプリケーションプログラムの開発 で説明します トランザクション内に複数のSQL 文があった場合 それらは全て実行されるか全く実行されないかのどちらかとなることがHiRDBによって保証されます 17 デフォルトでは トランザクションをコミットした時点でリソースに対する排他が解放されます トランザクションと排他制御についての詳細は 4.8 排他制御 で説明します また トランザクションをコミットする前にアプリケーションプログラムが異常終了した場合は HiRDB によってそのトランザクションはロールバックされます (6) HiRDB からの切断アプリケーションプログラムを HiRDB から切断します アプリケーションプログラムがトランザクションを開始しており 上記 (5) で示すトランザクションのコミットを明示的に行っていない場合は 切断時に自動的にコミット処理が行われます 切断処理を行わずにアプリケーションプログラムを正常終了した場合は 自動的に切断処理が行われます 15 Oracle の仕様については Oracle のマニュアル Oracle Database 概要 の 4 トランザクションの管理 中の トランザクションの概要 - 文レベルのロールバック で説明されています 16 アプリケーションプログラム内で以前にもコミット処理をしている場合 その ( 以前の ) コミット処理が 2 つ目のトランザクションの開始となります 17 これをトランザクションの原子性 (Atomicity) といいます 57

69 4 章アプリケーションプログラムの開発 4.2 静的 SQL と動的 SQL アプリケーションプログラムからSQLを実行する方法は 静的 SQLと動的 SQLに分類されます 静的 SQLとは アプリケーションプログラム開始時にSQLが決まっているもの 18 であり 動的 SQLとは アプリケーションプログラム実行時にSQLを文字列として組み立てるものです 静的 SQLが可能なのは 埋込みSQLのみです JDBCやADO.NETは アプリケーションプログラム実行時にSQLを文字列として組み立てるため 動的 SQLといえます 静的 SQLの場合 アプリケーションプログラム実行時に値を渡すことができるのは INSERT 文の追加値 UPDATE 文の更新値 SELECT 文やUPDATE 文 DELETE 文の検索条件値等に限定されます 19 一方 動的 SQLはアプリケーションプログラム実行時に SQLを文字列として組み立てるため あらゆる箇所をアプリケーションプログラム実行時に渡すことが可能です 例えば 静的 SQL の場合は 表名や列名をアプリケーションプログラム実行時に渡すことが不可能なのに対して 動的 SQL では表名や列名のみならず SQL 全てをアプリケーションプログラム実行時に渡す ( 組み立てる ) ことさえ可能です 下図は 静的 SQL および動的 SQL において アプリケーションプログラム実行時に値を渡すことの可能な箇所の例です 静的 SQL UPDATE 表名 SET 列名 = 更新値 WHERE 列名 = 条件値 アプリケーションプログラム実行時に値を渡すことが可能な箇所 動的 SQL UPDATE 表名 SET 列名 = 更新値 WHERE 列名 = 条件値 SQL 文全てをアプリケーションプログラム実行時に組み立てることが可能 図 アプリケーションプログラム実行時に値を渡すことが可能な箇所 18 アプリケーションプログラム実行前に プリプロセッサにより SQL 文を解析済みのため 少なくともアプリケーションプログラム開始時には SQL 文は決まっているといえます ただし 変数とすることが可能な INSERT 文の追加値 UPDATE 文の更新値 SELECT 文や UPDATE 文 DELETE 文の検索条件値等は アプリケーションプログラム開始時に決まっていなくてもかまいません 19 実際には 値を渡したい箇所を埋込み変数として定義し SQL 実行前に値をセットします 58

70 4 章アプリケーションプログラムの開発 4.3 埋込み変数 埋込み SQL プログラムの SQL 文中に指定する親言語の変数のことを埋込み変数またはホ スト変数といいます 埋込み変数とすることができるのは 静的 SQL におけるアプリケーションプログラム実 行時に値を渡すことができる箇所 のみです 例えば INSERT 文の追加値 UPDATE 文の更新値 SELECT 文や UPDATE 文 DELETE 文の検索条件値等に埋込み変数を使 用できます 埋込み変数を SQL 文中で使用する場合 変数の前にコロン (:) を指定します ただし SQL 以外で埋込み変数を使用する場合はコロン (:) を指定する必要はありません 埋込み SQL(COBOL 言語 ) での埋込み変数の使用例 MOVE ' 川崎市 ' TO XADDR MOVE '519' TO XMID EXEC SQL UPDATE members SET member_addr = :XADDR WHERE member_id = :XMID END-EXEC 備考 begin declare section 外で宣言した埋込み変数を有効にする場合は プリプロセス時に -E3 又は-E2 オプションを指定します そうすると HiRDB ではプリプロセッサがホスト言語の文法に従って埋込み変数の有効範囲を認識します 59

71 4 章アプリケーションプログラムの開発 4.4 データ型に関する注意点 SQL 文中の定数の指定方法 SQL 文中に定数を指定する場合 データ型によって指定方法が異なります 例えば VARCHAR 型に対する定数 ( 文字列定数 ) は 以下のようにシングルクォーテーションで括る必要があります 各データ型の定数の指定方法を次の表に示します SELECT * FROM goods WHERE goods_name = ' ウォッカ ' VARCHAR 型 文字列定数 表 SQL 文中の定数の指定方法 データ型 指定方法 例 INTEGER ( 整数定数 ) 半角数字で表します 符号は + または - で表し 符号を省略 した場合は正の数となります DECIMAL (10 進定数 ) 小数点を必ず付ける以外は整数定数と同じです FLOAT CHAR VARCHAR NCHAR NVARCHAR MCHAR MVARCHAR DATE ( 日付定数 ) TIME ( 時刻定数 ) TIMESTAMP ( 日付時刻定数 ) 仮数部 +E+ 指数部 という形式で表します 仮数部は整数定数または 10 進数定数で表し 指数部は 1~3 けたの整数定数で表します 文字 E は必ず付ける必要があります 文字列をシングルクォーテーションで括ります 文字列の長さは 32,000 文字 (32,000 バイト ) 以内です キーワード N に続けて 文字列をシングルクォーテーションで括ります 文字列の長さは 16,000 文字 (32,000 バイト ) 以内です キーワード M に続けて 文字列をシングルクォーテーションで括ります 文字列の長さは 32,000 バイト以内です 'YYYY-MM-DD' 形式で表現します YYYY は年を MM は月を DD は日を表します 'hh:mm:ss' 形式で表現します hh は時を mm は分を ss は秒を表します 'YYYY-MM-DD hh:mm:ss' 形式で表現します 1.0E2.5E+67 'HITACHI' '88' N' 日立 ' M'1996 年 ' ' ' '11:03:58' ' :03:58' 60

72 4 章アプリケーションプログラムの開発 プログラミング言語のデータ型と HiRDB のデータ型の対応 HiRDB から検索した結果をアプリケーションプログラム中の変数で受け取る場合 受け取り用変数のデータ型と結果データの型とが対応している必要があります 例えば HiRDB の INTEGER 型は Java 言語の int 型に対応し COBOL 言語の場合は整数型 (COMPUTATIONAL) に対応しています したがって HiRDB の INTEGER 型列のデータは Java 言語では int 型変数で COBOL 言語では整数型 (COMPUTATIONAL) の変数で受け取る必要があります VARCHAR 型データを Java の int 型変数や COBOL の整数型 (CONPUTATIONAL) 変数で受け取ることはできません INSERT 文の追加値をアプリケーションプログラム中で変数とする場合 あるいは UPDATE 文の更新値 SELECT 文や UPDATE 文 DELETE 文の検索条件値 (WHERE 句で比較する値 ) を変数とした場合も同様です 埋込み SQL(COBOL 言語 ) での検索結果の取得例 MOVE 100 TO XONO. 整数型 (COMPUTATIONAL) でなければならない INTEGER 型の列 EXEC SQL SELECT deliv_flag INTO :XDFLG:IDFLG FROM orders WHERE order_no = :XONO END-EXEC. INTEGER 型の列整数型 (COMPUTATIONAL) でなければならない したがって アプリケーションプログラムの開発者は HiRDB のデータ型とプログラミング言語のデータ型の対応を認識しておく必要があります HiRDB のデータ型と Java のデータ型の対応については 5.5 SQL によるデータベースの検索 で説明します データ型の変換 (1) 暗黙的データ型変換 で説明した通り SQL の WHERE 句で HiRDB の列と変数とを比較する場合 両者のデータ型は原則的に対応が取れている必要があります 列と列とを比較する場合 あるいは列と定数を比較する場合も同様です しかし 対応が取れていなくても 互換性のあるデータ型間では HiRDB が自動的にデータ型の変換を行ってくれる場合があります これを暗黙的データ型変換といいます 代入 ( 検索結果を変数に代入する場合や変数の値を表に追加する場合等 ) に関しても同様です 例えば HiRDB の INTEGER 型に対応する COBOL のデータ型は整数型 61

73 4 章アプリケーションプログラムの開発 (COMPUTATIONAL) です したがって INTEGER 型の列と埋込み変数とを比較する場合 埋込み変数のデータ型は整数型 (COMPUTATIONAL) であるべきですが 以下のように HiRDB の INTEGER 型の列と COBOL の文字列型 (X) の埋込み変数を比較することができます これは 文字列型の埋込み変数を HiRDB が暗黙的に整数型へと変換しているためです 埋込み SQL(COBOL 言語 ) での暗黙的データ型変換の例 IDENTIFICATION DIVISION. : XONO PIC X(10). : MOVE '10' TO XONO. : EXEC SQL DELETE FROM orders WHERE order_no = :XONO END-EXEC : INTEGER 型 文字列型 暗黙的データ型変換の可否については 付録 D 暗黙的データ型変換 で説明します (2) CAST による明示的データ型変換 SQL 文中で CAST 指定をすると ユーザーが明示的にデータ型の変換を行うことができます CAST の文法と指定例を次に示します CAST CAST( { 値式 NULL } AS 変換後データ型 ) 例 (CAST による明示的データ型変換 ) TIMESTAMP 型 SELECT * FROM t1 WHERE CAST(c1 AS TIME) < TIME('12:00:00') BINARY 型 VARCHAR 型 INSERT INTO t2(c2) SELECT CAST(c3 AS BINARY(32000)) FROM t3 CAST によるデータ型変換の可否を次に示します なお ここでは 主なデータ型についてだけ説明しています 62

74 4 章アプリケーションプログラムの開発 表 CAST によるデータ型変換の可否 AS 指定 ( 変換後データ型 ) 値式 ( 変換元データ ) 真数データ INTEGER SMALLINT DECIMAL 概数データ FLOAT SMALLFLT 文字データ CHARACTER VARCHAR 各国文字データ NCHAR NVARCHAR 混在文字データ MCHAR MVARCHAR 日付データ DATE 時刻データ TIME 時刻印データ TIMESTAMP BINARY データ (32000 バイト以内 ) 真数 INTEGER SMALLINT DECIMAL 概数 FLOAT SMALLFLT 文字データ CHARACTER VARCHAR 各国文字データ NCHAR NVARCHAR 混在文字データ MCHAR MVARCHAR 日付データ DATE 時刻データ TIME 時刻印データ TIMESTAMP BINARY データ (32000 バイト以内 ) Oracle との違い Oracleの場合は : 埋込み変数 をSELECT 句に直接指定します 20 一方 HiRDBの場合は SELECT 句中で : 埋込み変数 を使用する場合 次に示すようにCASTによるデータ型の変換を明示します ここで CAST 内で指定する データ型 は 埋込み変数に対応する HiRDB のデータ型とします 表 SELECT 句での整数指定 HiRDB Oracle SELECT, CAST(: 埋込み変数名 AS データ型 ) SELECT, : 埋込み変数名埋込み変数 FROM ~ FROM ~ 20 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 6 式 中の SQL 式 と 変数式 で説明されています 63

75 4 章アプリケーションプログラムの開発 (3) スカラ関数による明示的データ型変換スカラ関数を使用してユーザーが明示的にデータ型を変換することもできます スカラ関数によるデータ型変換を次に示します なお ここでは 主なデータ型についてだけ説明しています 変換前データ型 変換後データ型 表 スカラ関数による主なデータ型変換 スカラ関数 数文字列 NUMEDIT SELECT NUMEDIT(goods_price, '"\"<9,999') 文字列定 数 FROM goods; \2,000 \1,800 \250 数 STRTONUM STRTONUM(' ', ) 日付文字列 VARCHAR_FORMAT SELECT 例 DECIMAL(13,3) 型の VARCHAR_FORMAT(order_date, 'YYYY" 年 "MM" 月 "DD" 日 "') FROM orders; 2005 年 11 月 23 日 2005 年 11 月 24 日 文字列日付 DATE DATE('2006/01/26', 'YYYY/MM/DD') DATE 型の ' ' スカラ関数の詳細については マニュアル HiRDB Version 8 SQL リファレンス の システム定義スカラ関数 を参照してください 文字列を比較するときの注意事項 文字列を比較するときの注意事項について Oracle との違い 21 を含めて説明します 可変長列と固定長列を比較する場合可変長列と固定長列の比較は 固定長同士の比較と同様に短い方に空白を埋めて比較します なお Oracle の場合は 可変長同士の比較と同様に長さも比較します 21 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 2 Oracle SQL の基本要素 中の リテラル - テキスト リテラル と データ型の比較規則 - 文字値 - 空白埋め比較セマンティクスおよび非空白埋め比較セマンティクス で説明されています 64

76 4 章アプリケーションプログラムの開発 列と文字列定数を比較する場合文字列定数は可変長として扱われます しかし 列と文字列定数との比較は 比較規則が一致します なお Oracle の場合は 文字列定数は固定長として扱われます 文字列定数同士を比較する場合文字列定数同士を比較する場合 可変長の比較規則が適用されるため 後続の空白も含めて一致しなければ同じものとはみなされません なお Oracle の場合は 固定長の比較規則が適用されるため 後続の空白の数にかかわらず 固定文字さえ一致すれば同じものとみなされます 例えば HiRDB と Oracle に以下の SQL 文を実行し それぞれ表を作成しデータを追加したとします HiRDB create table t1 ( c1 int, c2 char(5), c3 varchar(5) ); insert into t1 values(1,'a','a '); insert into t1 values(2,'a ','A'); Oracle create table t1 ( c1 number(4), c2 char(5), c3 varchar2(5) ); insert into t1 values(1,'a','a '); insert into t1 values(2,'a ','A'); は空白を表す このとき 上記で作成したそれぞれの表に対し 様々な比較をする SELECT 文を実行したときの結果を以下に示します 実行する SQL 文 select * from t1 where c2 = c3; select * from t1 where c2='a'; select * from t1 where c3='a'; select * from t1 where 'S' = 'S '; は空白を表す 表 文字列の比較 HiRDB C1 C2 C A A 2 A A C1 C2 C A A 2 A A C1 C2 C A A 1 件もヒットしない 結果 Oracle 1 件もヒットしない C1 C2 C A A 2 A A C1 C2 C A A C1 C2 C A A 2 A A ( 中身に関わらず全件ヒット ) 65

77 4 章アプリケーションプログラムの開発 4.5 HiRDB と Oracle のスカラ関数の対応 ここでは HiRDB と Oracle 22 の主なスカラ関数の対応について説明します (1) 部分文字列の取得 部分文字列の取得には HiRDB Oracle 共にスカラ関数の SUBSTR を用います 例 ) SELECT SUBSTR(C1,2,3) FROM T1 この例では C1 列の先頭から数えて 2 文字目から 3 文字分の文字列を取得します 23 例えば C1 列の値が " 日立製作所 " の場合 関数の結果は " 立製作 " となります 末尾から数えて m 文字目から ( 末尾方向へ )n 文字分の文字列を取得する方法は HiRDB と Oracle では異なります 例えば C1 列の値 " 日立製作所 " から 末尾から 3 文字目 (" 製 ") から ( 末尾方向へ )2 文字分のデータ (" 製作 ") を取得するには 以下のようになります 表 部分文字列の取得 HiRDB の場合 Oracle の場合 SELECT substr(c1,length(c1)-3+1,2) FROM T1 SELECT SUBSTR(C1,-3,2) FROM T1 注 length(c1) は C1 列データの文字列数を取得します (2) 現在日時の取得 HiRDB と Oracle の現在日時を取得する関数の対応を次に示します 表 現在日時取得関数 HiRDB の場合 Oracle の場合 日付 CURRENT_DATE SYSDATE または CURRENT_DATE 時刻 CURRENT_TIME なし 日時 CURRENT_TIMESTAMP SYSTIMESTAMP または CURRENT_TIMESTAMP 22 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 5 ファンクション で説明されています 23 あくまでも m 文字目から n 文字分の文字列 であって m バイト目から n バイト分のデータ ではないことに注意してください Oracle で m バイト目から n バイト分のデータ を取得するには SUBSTRB を使用します 66

78 4 章アプリケーションプログラムの開発 (3) 型変換 ( 数値 文字 ) 数値データを文字データに変換する場合 HiRDB では NUMEDIT 関数を Oracle では TO_CHAR 関数を使用します 使用例を次に示します 表 数値から文字への型変換 HiRDB の場合 Oracle の場合 結果 NUMEDIT(123,'000000') TO_CHAR(123,'000000') NUMEDIT(123,'999999') TO_CHAR(123,'999999') 123 NUMEDIT(123,' ') TO_CHAR(123,' ') NUMEDIT(123.45,' ') TO_CHAR(123.45,' ') NUMEDIT(123.45,' ') TO_CHAR(123.45,' ') NUMEDIT(123.45,'"\" ') '\' TO_CHAR(123.45,' ') \ NUMEDIT(123.45,'"\"< ') TO_CHAR(123.45,'L ')) \123.5 (4) 型変換 ( 日付 / 時刻 文字 ) 日付 / 時刻データを文字データに変換する場合 HiRDB では VARCHAR_FORMAT 関数 を Oracle では TO_CHAR 関数を使用します 使用例を次に示します 表 日付 / 時刻から文字への型変換 HiRDB の場合 Oracle の場合 結果 ( 例 ) VARCHAR_FORMAT(CURRENT_TIMESTAMP, TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD') 'YYYY/MM/DD') 2006/08/24 VARCHAR_FORMAT(CURRENT_TIMESTAMP, TO_CHAR(CURRENT_TIMESTAMP, 'YY/MM/DD') 'YY/MM/DD') 06/08/24 VARCHAR_FORMAT(CURRENT_DATE, TO_CHAR(CURRENT_DATE, 'YYYY/MM/DD') 'YYYY/MM/DD') 2006/08/24 VARCHAR_FORMAT(CURRENT_DATE, TO_CHAR(CURRENT_DATE, 'YY/MM/DD') 'YY/MM/DD') 06/08/24 VARCHAR_FORMAT(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH:MI:SS') TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH:MI:SS') 2006/08/24 11:39:28 67

79 4 章アプリケーションプログラムの開発 (5) 型変換 ( 文字 日付 / 時刻 ) 文字データを日付 / 時刻データに変換する場合 HiRDB では TIMESTAMP 関数や TIMESTAMP_FORMAT 関数 DATE 関数等を Oracle では TO_DATE 関数を使用します 使用例を次に示します 表 文字から日付 / 時刻への型変換 HiRDB の場合 Oracle の場合 変換結果 TIMESTAMP( ' :29:05.12') TO_TIMESTAMP(' :29:05.12') ' :29:05.12' (TIMESTAMP 型 ) TIMESTAMP_FORMAT( '08/24/200611:29:05.12', TO_TIMESTAMP( '08/24/200611:29:05.12', ' :29:05.12' (TIMESTAMP 型 ) 'MM/DD/YYYYHH:MI:SS.FF') 'MM/DD/YYYYHH:MI:SS.FF') DATE( '08/24/2006','MM/DD/YYYY') TO_DATE( '08/24/2006','MM/DD/YYYY') ' ' (DATE 型 ) (6) 最初のナル値でない値の取得複数の列から ( 最初の ) ナル値でない値を取得する場合 HiRDB では VALUE 関数または COALESCE 関数を Oracle では NVL 関数または COALESCE 関数を使用します 使用例を次に示します 表 最初のナル値でない値の取得 HiRDB の場合 Oracle の場合 結果 CREATE TABLE t1 (C1 INT, C2 CHAR(5), C3 CHAR(5), C4 CHAR(5)); INSERT INTO t1 VALUES (1,'AAA',NULL,NULL); INSERT INTO t1 VALUES (2,NULL,'BBB',NULL); INSERT INTO t1 VALUES (3,'CCC',NULL,'DDD'); CREATE TABLE t1 (C1 NUMBER(4), C2 CHAR(5), C3 CHAR(5), C4 CHAR(5)); INSERT INTO t1 VALUES (1,'AAA',NULL,NULL); INSERT INTO t1 VALUES (2,NULL,'BBB',NULL); INSERT INTO t1 VALUES (3,'CCC',NULL,'DDD'); SELECT C1, VALUE(C3,C2) AS "NVL_VALUE", COALESCE(C4,C2,C3) AS "COALESCE" FROM t1; SELECT C1, NVL(C3,C2) AS "NVL_VALUE", COALESCE(C4,C2,C3) AS "COALESCE" FROM t1; C1 NVL_VALUE COALESCE AAA AAA 2 BBB BBB 3 CCC DDD 注 HiRDB の VALUES 関数は 3 つ以上指定できます Oracle の NVL 関数は 引数を 2つ指定します 68

80 4 章アプリケーションプログラムの開発 4.6 カーソル ここでは カーソルの使い方について説明します カーソルとは カーソルとは 検索結果の行を特定するためのポインタです アプリケーションプログラムで複数の行を検索する場合 カーソルが必要となります アプリケーションプログラムが複数の検索結果行を取得するには HiRDB によって一時的に保持された検索結果全行から 1 行ずつ受け取ります アプリケーションプログラムで複数行を取得するには カーソルを使用して 検索結果の現在行を移動させながら 1 行ずつ受け取ります orders 表 order_no order_date member_id deliv_flag SELECT order_no,order_date,member_id FROM orders WHERE deliv_flag = 0 対応付け カーソル HiRDB 検索結果 order_no order_date member_id カーソルを使用して 現在行を移動させながらデータを取得 アプリケーションプログラム XONO XODATE XMID order_no 列取得用変数 member_id 列取得用変数 図 カーソルの概要 order_date 列取得用変数 69

81 4 章アプリケーションプログラムの開発 カーソルを使用したデータ取得手順 カーソルを使用して HiRDB から複数行を取得する主な手順を以下に示します (1) カーソルの宣言 カーソルを宣言し SELECT 文との対応付けを行います 例 ( カーソルの宣言 ) DECLARE cr1 CURSOR FOR SELECT order_no,order_date,member_id FROM orders WHERE deliv_flag = 0 Oracle との違い Oracleの場合は カーソルに対応付ける任意のSELECT 文に対し排他モードのロック獲得を目的にFOR UPDATEの指定が可能です 24 一方 HiRDBの場合は カーソル定義時のFOR UPDATE 指定は標準 SQLと同様にカーソルを介しての更新が目的です したがって 結合処理やDISTINCT GROUP BY UNION 等 元の表を加工 集計するSELECT 文に対するカーソルは更新不可となるため FOR UPDATEを指定できません Oracleの排他モードのロック獲得を目的に指定したFOR UPDATEは HiRDBではWITH EXCLUSIVE LOCK 指定 25 と対応しています (2) カーソルのオープン カーソルをオープンします カーソルをオープンすると HiRDB はカーソルに対応付けされた SELECT 文を実行し 検索結果を保持します 24 Oracle の仕様については Oracle のマニュアル Oracle Database アプリケーション開発者ガイド - 基礎編 の 2 アプリケーション開発者用の SQL 処理 中の 明示的なデータのロック - ロック方法の選択 で説明されています 25 WITH EXCLUSIVE LOCK 指定については 4.8 排他制御 で説明します 70

82 4 章アプリケーションプログラムの開発 例 OPEN cr1 orders 表 order_no order_date member_id deliv_flag SELECT order_no,order_date,member_id FROM orders WHERE deliv_flag = 0 (2) カーソルのオープン (1) カーソル cr1 の宣言 対応付け カーソル cr1 HiRDB 検索結果 order_no order_date member_id 図 カーソルの宣言とオープン (3) カーソルを使用したデータの取得カーソルを移動しながら 1 件 1 件データをアプリケーションプログラムの変数に取得します SQL の FETCH 文は カーソルの現在行を 1 行進め カーソルの現在行のデータをアプリケーションプログラムの変数に渡します 71

83 4 章アプリケーションプログラムの開発 例 FETCH cr1 INTO :XONO,:XODATE,:XMID FETCH1 回目 HiRDB アプリケーションプログラム cr1 order_no order_date member_id 検索結果 XONO XODATE XMID FETCH2 回目 cr1 HiRDB order_no order_date member_id 検索結果 アプリケーションプログラム XONO XODATE XMID FETCH3 回目 cr1 HiRDB 検索結果 order_no order_date member_id NOT FOUND(SQLCODE に 100 が返る ) ループを抜ける 図 カーソルの FETCH アプリケーションプログラム XONO XODATE XMID 72

84 4 章アプリケーションプログラムの開発 (4) カーソルのクローズ カーソルをクローズし 全ての処理を終了します 例 CLOSE cr カーソルを使用したデータの更新とデータの削除 カーソルを使用した UPDATE の場合 カーソルの現在行を UPDATE 対象にします 同様にカーソルを使用した DELETE の場合 カーソルの現在行を DELETE 対象にします カーソルを使用した UPDATE UPDATE 表名 SET 列名 ={ 値式 NULL} [,{ 値式 NULL}, ] WHERE CURRENT OF カーソル名 カーソルの指している行 ( 現在行 ) の列の値を更新します カーソルを使用した DELETE DELETE FROM 表名 WHERE CURRENT OF カーソル名 カーソルの指している行 ( 現在行 ) を表中から削除します 73

85 4 章アプリケーションプログラムの開発 4.7 効果的な SQL の使い方 ここでは 効果的な SQL の使い方について代表的なものを説明します ここで説明して いる SQL を使用すると 性能や操作性の向上などが期待できます 総ヒット件数と各行の値を求める場合 通常 総ヒット件数 (COUNT(*) の結果 ) と各行の値を求める場合 2 つの SQL 文を実行します しかし 総ヒット件数返却機能を使用すると 総ヒット件数を求める SQL と ヒットした行の値を求める SQL と 1 つに統合できます これにより 1 つの SQL 文で 2 つの SQL 文を実行した場合と同等の結果を求めることができるため 検索時間を短縮することができます 特に ORDER BY 句を指定しており 26 かつ総ヒット件数を求めたい場合 COUNT(*) OVER( ) を使用することで SQLの発行回数が削減されるため 効果的です select STU_ID,STU_NAME,count(*) over() as numbers from STUDENTS order by STU_ID STUDENTS STU_ID STU_NAME CORP_ID 1 Taro Yamada A001 2 Jiro Ito K003 3 Hanae Suzuki H153 4 Yoshihiro Sato A045 5 Makoto Mikami S009 検索結果 STU_ID STU_NAME NUMBERS 1 Taro Yamada 5 2 Jiro Ito 5 3 Hanae Suzuki 5 4 Yoshihiro Sato 5 5 Makoto Mikami 5 図 COUNT(*) OVER() 関数 総ヒット件数返却機能を使用するためには COUNT(*) OVER( ) を SELECT 句内に指定します 文法 select { 列名 式 * count(*) over( ) } from where 26 インデックスを使用して処理を行う場合は 内部で作業表を作成しません その場合は 逆に count(*) over( ) 実行時に作成する作業表の影響が大きくなり パフォーマンスが低下する恐れがあります 74

86 4 章アプリケーションプログラムの開発 注意事項 COUNT(*) OVER( ) は 次に示す箇所には指定できません INSERT 文の問合せ式本体 副問合せ 導出表 集合演算対象となる問合せ スカラ演算内 COUNT(*) OVER( ) を含む SQL 文に 次に示す構文は指定できません GROUP BY 句 HAVING 句 選択式内への集合関数 COUNT(*) OVER( ) を指定する場合 次の規則を守る必要があります SELECT 句内に COUNT(*) OVER() を指定する場合 COUNT(*) OVER() 以外の 選択式を一つ以上指定する 配列の利用 (1) 配列とは埋込み SQL で配列を使用すると 1 つの SQL 文で複数行を同時に処理することができます 例えば 一回の INSERT で複数行を追加したり 一回の FETCH で複数行を取り出すことなどが可能です これにより クライアントとサーバ間の通信回数を削減することができ 性能を向上させることができます 特に 大量の検索 更新 追加 削除時に効果的です 埋込み SQL AP 配列 XONO XGCODE XQTY 120 C B HiRDB DETAILS 表 ORDER_NO GOODS_CODE QTY EXEC SQL FOR :INS-CNT INSERT INTO DETAILS VALUES(:XONO,:XGCODE,:XQTY) END-EXEC. 転送 (1 回のみ ) 2 行追加 120 C B クライアント サーバー 図 配列 75

87 4 章アプリケーションプログラムの開発 (2) 配列の指定方法埋め込み SQL で配列を使用するためには 複数行のデータを扱うことができる配列型の変数を使用します 配列型変数は FETCH 文では INTO 句 INSERT 文では VALUES 句 UPDATE 文では SET 句および WHERE 句 DELETE 文では WHERE 句に指定します また 複数行の追加 更新 削除を行う場合に for 句に変数を指定することで 一回の処理行数を制御することができます 文法 for : 変数 insert into values (: 配列型変数 [: 標識変数 ], : 配列型変数 [: 標識変数 ], ) for : 変数 delete from where = : 配列型変数 for : 変数 update set = : 配列型変数 where = : 配列型変数 fetch カーソル名 into : 配列型変数 [: 標識変数 ] [,: 配列型変数 [: 標識変数 ], ] for : 変数一回で処理する行数を設定した変数名を指定します 変数は HiRDB の SMALLINT 型に対応する型で宣言します 設定値は 1~4,096 までの範囲で かつ VALUES 句 SET 句 WHERE 句 INTO 句で指定した配列型変数の要素数以下の値を指定します 範囲外の値を設定した場合は実行時にエラーとなります 76

88 4 章アプリケーションプログラムの開発 例 COBOL による埋込み SQL において 配列を用いて 1 回の INSERT で 2 行を追加する EXEC SQL BEGIN DECLARE SECTION END-EXEC * 配列型変数 INS-DATA XONO PIC S9(4) COMP OCCURS XGCODE PIC X(10) OCCURS XQTY PIC S9(4) COMP OCCURS * 追加行数 INS-CNT PIC S9(4) COMP EXEC SQL END DECLARE SECTION END-EXEC * 配列変数の各要素に値設定 MOVE 120 TO XONO(1) MOVE 'C10012' TO XGCODE(1) MOVE 2 TO XQTY(1) MOVE 120 TO XONO(2) MOVE 'B10004' TO XGCODE(2) MOVE 3 TO XQTY(2) * 追加行数 MOVE 2 TO INS-CNT * 配列変数を用いた INSERT 文の実行 EXEC SQL FOR :INS-CNT INSERT INTO DETAILS (ORDER_NO,GOODS_CODE,QTY) VALUES(:XONO,:XGCODE,:XQTY) END-EXEC. 上記例では 要素数が2の配列型変数 XONO XGCODE XQTY を使用して 1 回の INSERT 文の実行により 2 行を HiRDB の DETAILS 表に追加しています 配列型変数の格納イメージ INS-DATA 添字 XONO XGCODE XQTY C B

89 4 章アプリケーションプログラムの開発 例 COBOL による埋込み SQL において 配列を用いて 1 回で 12 行を FETCH する IDENTIFICATION DIVISION PROGRAM-ID. TEST_ARRAY ENVIRONMENT DIVISION INPUT-OUTPUT SECTION DATA DIVISION FILE SECTION WORKING-STORAGE SECTION * 出力領域 O-DATA O-ID PIC X(4) PIC X(5) VALUE SPACE O-YEAR PIC X(4) PIC X(7) VALUE SPACE O-GRADE PIC X(3) PIC X(9) VALUE SPACE O-NAME PIC X(30) * 見出し行 O-HEADER PIC X(9) VALUE INST_ID PIC X(11) VALUE HIRE_DATE PIC X(12) VALUE INST_GRADE PIC X(30) VALUE INST_NAME O-HEADER PIC X(9) VALUE PIC X(11) VALUE PIC X(12) VALUE PIC X(30) VALUE * 処理済件数 DONE-CNT PIC S9(4) COMP *FETCH サイズ FETCH-SIZE PIC S9(4) COMP * 添え字 I PIC S9(4) COMP * * 埋込み変数宣言開始 EXEC SQL BEGIN DECLARE SECTION END-EXEC * 検索結果受け取り用領域 RESULT-DATA * * 要素数 12 の配列 R-ID PIC S9(4) COMP OCCURS * * 要素数 12 の配列 R-NAME OCCURS R-NAME-LENGTH PIC S9(4) COMP R-NAME-VALUE PIC X(30) * * 要素数 12 の配列 R-YEAR PIC S9(4) COMP OCCURS * * 要素数 12 の配列 R-GRADE OCCURS R-GRADE-LENGTH PIC S9(4) COMP R-GRADE-VALUE PIC X(3) * * 埋込み変数宣言終了 EXEC SQL END DECLARE SECTION END-EXEC PROCEDURE DIVISION MAIN SECTION * *SQL エラー発生時の処理宣言 EXEC SQL WHENEVER SQLERROR PERFORM ERRPROC END-EXEC * *HiRDB へ接続 (HiRDB.ini 参照 ) EXEC SQL CONNECT END-EXEC * * カーソル宣言 78

90 4 章アプリケーションプログラムの開発 EXEC SQL DECLARE ICR CURSOR FOR SELECT INST_ID, INST_NAME, HIRE_YEAR, INST_GRADE FROM INSTRUCTORS END-EXEC * * カーソルオープン EXEC SQL OPEN ICR END-EXEC * * 検索行なしの時の処理宣言 EXEC SQL WHENEVER NOT FOUND PERFORM NORMPROC END-EXEC * * 添え字 / 処理済件数の初期化 MOVE 1 TO I MOVE 0 TO DONE-CNT * * 見出しの出力 DISPLAY O-HEADER DISPLAY O-HEADER * *FETCH 回数だけ繰り返し PERFORM UNTIL I NOT = I * * 検索結果の取得 EXEC SQL FETCH ICR INTO :R-ID,:R-NAME,:R-YEAR,:R-GRADE END-EXEC * *FETCH サイズの計算 SQLERRD(3) は SQL 連絡領域の累積取得行数 COMPUTE FETCH-SIZE = SQLERRD(3) - DONE-CNT * *FETCH サイズだけ繰り返し PERFORM FETCH-SIZE TIMES * * 出力領域に検索結果を代入 MOVE R-ID(I) TO O-ID MOVE R-NAME-VALUE(I) TO O-NAME MOVE R-YEAR(I) TO O-YEAR MOVE R-GRADE-VALUE(I) TO O-GRADE * * 出力領域の標準出力 DISPLAY O-DATA COMPUTE I = I END-PERFORM * * 処理済件数の退避 MOVE SQLERRD(3) TO DONE-CNT MOVE 1 TO I END-PERFORM STOP RUN NORMPROC * *(NOT FOUND がリターンされた ) 最終 FETCH に対する処理 IF SQLERRD(3) NOT = DONE-CNT * * 最終 FETCH に行があった場合 THEN MOVE 1 TO I * *FETCH サイズの計算 COMPUTE FETCH-SIZE = SQLERRD(3) - DONE-CNT * *FETCH サイズだけ繰り返し PERFORM FETCH-SIZE TIMES * * 出力領域に検索結果を代入 MOVE R-ID(I) TO O-ID MOVE R-NAME-VALUE(I) TO O-NAME MOVE R-YEAR(I) TO O-YEAR MOVE R-GRADE-VALUE(I) TO O-GRADE * * 出力領域の標準出力 DISPLAY O-DATA COMPUTE I = I END-PERFORM * * 最終 FETCH に行がなかった場合 ELSE CONTINUE END-IF. 79

91 4 章アプリケーションプログラムの開発 * * カーソルのクローズ EXEC SQL CLOSE ICR END-EXEC * * トランザクションのコミット EXEC SQL COMMIT END-EXEC * *HiRDB との切り離し EXEC SQL DISCONNECT END-EXEC GOBACK ERRPROC DISPLAY SQLCODE=,SQLCODE DISPLAY SQLERRM=,SQLERRM EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC EXEC SQL DISCONNECT END-EXEC GOBACK 上記例では 要素数が 12 の配列型変数 R-ID R-NAME R-YEAR R-GRADE に FETCH することにより 1 回 ( の FETCH) で 12 行をクライアントアプリケーションに転送しています FETCH により取得したデータの標準出力は ライン からライン のループ処理で行っています FETCH 結果 12 行を 1 行ずつ 出力領域に代入し標準出力処理を繰り返しています SQLERRD(3) は SQL 連絡領域の1つであり FETCH を実行する毎に累積の取得行数が HiRDB により返ります ( 埋込み SQL の親言語が C の場合は SQLERRD2 となります ) 上記例では 12 行ずつ FETCH するため 1 回目の FETCH 時には 12 2 回目には 24 が SQLERRD(3) に返ります FETCH 自体の繰り返し処理はライン からライン で行っています 例えば以下の様に INSTRUCTORS 表に 30 行あった場合 SQLCODE に 100(NOT FOUND) が返るのは 3 回目の FETCH 時になります 3 回目の FETCH により取得したデータは 標準出力される前にループ処理を抜けることに注意して下さい 従って 最後の FETCH により取得したデータの標準出力処理はループを抜けた後に改めて行います 実行イメージ 12 行 FETCH 1 回目 SQLERRD(3):12 30 行 12 行 FETCH 2 回目 SQLERRD(3):24 6 行 FETCH 3 回目 SQLERRD(3):30 SQLCODE:100(NOT FOUND) 80

92 4 章アプリケーションプログラムの開発 ブロック転送機能 - 配列を使用できないプログラミング方式の場合 ブロック転送機能を使用することで クライアントとサーバ間の通信回数を削減することができます ブロック転送機能とは 検索結果をメモリ上に構築し 複数行をまとめてクライアント側へ転送する機能のことです メモリ メモリ 転送 AP DB クライアント サーバ 図 ブロック転送機能 ブロック転送機能を使用するためには 以下のパラメータを設定します ブロック転送機能で使用する通信バッファサイズ 主な指定箇所 : クライアント環境定義ファイル (HiRDB.ini) PDBLKBUFFSIZE=((0~ ))( 単位 : キロバイト ) 一回の転送処理で使用する サーバとクライアント間の通信バッファサイズを指定します ここで指定したバッファサイズまで検索結果を詰め込んで転送します OS の通信バッファサイズを目安に指定します クライアント環境定義 PDBLKF( ブロック転送機能の行数指定 ) は省略して下さい PDBLKF を指定すると 行長によって転送量が変動し 性能が向上しないことがあります 注意事項 検索時に警告エラーになると 警告エラー発生時点で転送処理を行います PDBLKBUFFSIZE に満たないサイズで転送するため ブロック転送機能の効果を得られなくなります 従って 警告エラーにならない SQL にすることを推奨します 警告エラーになるケースは 付録 H を参照下さい 81

93 4 章アプリケーションプログラムの開発 JDBC によるプリフェッチ / バッチ更新 JDBC アプリケーションでは プリフェッチ機能を使用すると SELECT 文の実行結果を 1 行ずつではなく 複数行をクライアントメモリ ( アプリケーション ) 上に取得することもできます これにより 埋込み SQL の配列による FETCH 同様 クライアントとサーバ間の通信回数を削減することができ 性能を向上させることができます プリフェッチ機能を使用するには Statement もしくは PreparedStatement オブジェクトの setfetchsize メソッドによりフェッチサイズを指定するだけです サーバーからクライアントへの物理的な転送行数が変更になりますが アプリケーションには透過的です 例 1:JDBC アプリケーションにおいて プリフェッチサイズを 12 にする 20 Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPASS); 21 String query = "select INST_ID,INST_NAME,HIRE_YEAR,INST_GRADE from INSTRUCTORS"; 22 PreparedStatement stmt = con.preparestatement(query); 23 // プリフェッチサイズ指定 24 stmt.setfetchsize(12); 25 // SQL 文実行 26 rs = stmt.executequery(); 27 System.out.println("INST_ID HIRE_DATE INST_GRADE INST_NAME"); 28 System.out.println(" "); 29 // カーソルを1つずつ進めてデータを取得 & 標準出力 30 while(rs.next()){ 31 System.out.print(rs.getInt(1) + " "); 32 System.out.print(rs.getInt(3) + " "); 33 System.out.print(rs.getString(4) +" "); 34 System.out.println(rs.getString(2)); 35 } 同様に JDBC アプリケーションでは バッチ更新機能により 複数の更新系 SQL 文 (UPDATE,DELETE,INSERT) を一括して HiRDB へ送信し その結果を一括して受け取ることができます バッチ更新を実装するには Statement もしくは PreparedStatement クラスの addbatch メソッドおよび executebatch メソッドを使用します 82

94 4 章アプリケーションプログラムの開発 Statement クラスのバッチ更新では 異なる更新系 SQL のバッチ更新が可能です 例 2:Statement クラスでのバッチ更新 JDBC アプリケーションにおいて 1 回の呼び出しで 2 行追加する 22 Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPASS); 23 // SQL テキスト 24 String upd1 = "insert into TBL1 values(10,'aaa')"; // 更新 SQL 1 25 String upd2 = "insert into TBL2 values(20,'bbb')"; // 更新 SQL 2 26 Statement stmt = con.createstatement(); 27 stmt.addbatch(upd1); 28 stmt.addbatch(upd2); 29 // バッチ更新の実行 30 stmt.executebatch(); // 2 個の更新 SQL を実行 PreparedStatement クラスのバッチ更新では 複数回の更新を 1 つの SQL で実行できます 同じ更新 SQL を繰り返し実行する場合に有効です 例 3:PreparedStatement クラスでのバッチ更新 JDBC アプリケーションにおいて 1 回の呼び出しで 2 行追加する 31 Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS); 32 // SQL テキスト 33 pstmt1 = conn.preparestatement("insert into tbl1(c1,c2) values(?,?)"); 34 pstmt1.setint(1,10); // 1 行目列 1 データ 35 pstmt1.setstring(2, "AAA"); // 1 行目列 2 データ 36 pstmt1.addbatch(); 37 pstmt1.setint(1,20); // 2 行目列 1 データ 38 pstmt1.setstring(2, "BBB"); // 2 行目列 2 データ 39 pstmt1.addbatch(); 40 // バッチ更新の実行 41 pstmt1.executebatch(); // 2 行のデータを INSERT catch(sqlexception ex1){ // 例外処理 注意事項 executeupdate ではトランザクションが継続となる警告エラーでも PreparedStatement クラスのバッチ更新では暗黙的ロールバックになることがあります 例えば INSERT 時の重複エラーは次の違いがあります SQL 実行結果は例外 SQLException の SQLSTATE で確認できます 5.9 例外処理 を参照下さい 項番種別 SQL 結果トランザクション状態 SQLSTATE 1 executeupdate 更新 Statement クラスのバッチ更新 警告継続中 PreparedStatement クラスの エラー 暗黙的 40A02 バッチ更新 ロールバック 83

95 4 章アプリケーションプログラムの開発 ホールダブルカーソルの利用 埋め込み SQL の場合 通常 HiRDB は COMMIT 時にカーソルをクローズします しかし ホールダブルカーソルを使用することで COMMIT 文を実行してもカーソルをクローズせずに 使用し続けることができます これによって カーソルを使用した検索 更新途中に COMMIT 文が実行できるため 排他資源を削減することができます ホールダブルカーソルは 以下に示す SQL が実行されるまで開いたままにすることができます CLOSE 文 DISCONNECT 文 ( システムによって自動的に実行される DISCONNECT 文も含む ) ROLLBACK 文 ( システムによって自動的に実行される ROLLBACK 文も含む ) ホールダブルカーソルを使用するためには DECLARE CURSOR 文に以下の指定を行います 文法 declare カーソル名 cursor with hold for SELECT 文 until disconnect with hold または until disconnect ホールダブルカーソルを使用する場合は 両方 またはどちらか片方のキーワードを指定します 両方指定した場合と片方のみ指定した場合の機能差はありません 84

96 4 章アプリケーションプログラムの開発 例 STUDENTS 表 (100,000 行 ) の全件検索に ホールタブルカーソル (CR01) を使用する declare CR01 cursor with hold for select * from STUDENTS; declare CR01 cursor for select * from STUDENTS until disconnect; Oracle との違い Oracleの埋込みSQLプログラムの場合 オープン中のカーソルをCOMMIT 時に自動的に閉じるかどうかをプリプロセス 27 時に指定します 28 COMMIT 時に自動的に閉じないがデフォルトになります 一行検索の利用 埋め込み SQL では 表から 1 行だけを検索する場合に カーソルを使用しないで処理を行うことができます ただし 検索結果が1 行の場合に限られるため 検索結果が 2 行以上ある場合は実行エラーになります なお 一行検索は 静的または動的に実行することができます (1) 静的な一行検索 一行検索を静的に行うためには SELECT INTO 文を使用します これは 行の検索と AP へのデータの受け渡しを同時に行うことができる構文です 文法 select into : ホスト変数名 : 標識変数,: ホスト変数名 : 標識変数, from where group by INTO 句検索したデータを受け取るホスト変数を指定します ホスト変数の前には コロン (:) を指定します 注意事項 SELECT 句内で指定する列数およびデータ型と INTO 句内で指定するホスト変数の数およびデータ型は 対応させる必要があります 27 Oracle のプリコンパイルのことです 28 Oracle の仕様については Oracle のマニュアル Oracle Database アプリケーション開発者ガイド- 基礎編 の 2 アプリケーション開発者用の SQL 処理 中の アプリケーションでのカーソルの使用 -カーソルのクローズ で説明されています 85

97 4 章アプリケーションプログラムの開発 例 STUDENTS 表から STU_ID 列 ( 主キー列 ) が 5 である行の STU_ID 列 STU_NAME 列 CORP_ID 列を検索する 検索したデータは 変数 xid xname xcid で受け取る EXEC SQL BEGIN DECLARE SECTION END-EXEC XID PIC X(9) XNAME PIC S9(30) XCID PIC X(9) EXEC SQL END DECLARE SECTION END-EXEC EXEC SQL select STU_ID,STU_NAME,CORP_ID into :XID,:XNAME,:XCID from STUDENTS where stu_id = 5; END-EXEC. (2) 動的な一行検索 一行検索を動的に行うためには PREPARE 文による解析処理と EXECUTE 文による実行処理が必要です 文法 prepare SQL 文識別子 from SELECT 文 SQL 文識別子解析対象の SQLT 文を識別するための名前を指定します from 句解析する SELECT 文を表す文字列を指定します execute SQL 文識別子 into : ホスト変数名 : 標識変数,: ホスト変数名 : 標識変数, USING ホスト変数名 into 句検索したデータを受け取るホスト変数を指定します ホスト変数の前には コロン (:) を指定します using 句解析対象の SQL 文に? パラメタが含まれている場合 値を設定した変数名を指定します 86

98 4 章アプリケーションプログラムの開発 例 STUDENTS 表から STU_ID 列 ( 主キー列 ) が 5 である行の STU_ID 列 STU_NAME 列 CORP_ID 列を検索する 検索したデータは 変数 xid xname xcid で受け取る EXEC SQL BEGIN DECLARE SECTION END-EXEC XID PIC X(9) XNAME PIC S9(30) XCID PIC X(9) SID PIC X(9) EXEC SQL END DECLARE SECTION END-EXEC MOVE 5 TO SID EXEC SQL PREPARE ST FROM select STU_ID,STU_NAME,CORP_ID where stu_id =? ; END-EXEC EXEC SQL execute ST into :XID,:XNAME,:XCID using :SID END-EXEC.? パラメタ? パラメタとは 動的 SQL の SQL 文中に? と指定したパラメタのことです 前処理 ( 解析処理 ) の後に? パラメタに値を渡し SQL を実行します? パラメタとすることができる箇所は 静的 SQL におけるアプリケーションプログラム実行時に値を渡すことができる箇所 と同様です 動的埋込み SQL(COBOL 言語 ) での? パラメタの使用例 MOVE 'INSERT INTO orders VALUES(?,CURRENT_DATE,?,0)' TO QUERY-STR EXEC SQL PREPARE ST1 FROM :QUERY-STR END-EXEC MOVE O-NO TO XONO MOVE '519' TO XMID EXEC SQL EXECUTE ST1 USING :XONO,:XMID END-EXEC 例えば 同一表に対する追加処理を繰り返す場合 その都度解析要求をするのではなく? パラメタを使用して解析処理は最初に 1 回のみ行い? パラメタに与える値を変えて実行のみを繰り返すようにすれば効率が良くなります 解析済みの SQL はトランザクション内で再利用可能です 87

99 4 章アプリケーションプログラムの開発 4.8 排他制御 同一データを複数のトランザクションが同時に更新すると データに矛盾が発生する恐れがあります そこで HiRDB には トランザクションの同時実行時の矛盾を防ぐ機能が備えられています これを排他制御といいます ここでは 排他制御の仕組みについて説明します 排他資源の単位 排他をかける対象を排他資源といいます 排他資源の単位には 図のような包含関係があるため 上位の資源に排他をかけると それより下位の資源には排他をかける必要がなくなります 例えば ページに排他をかけると 行には排他をかける必要はありません RD エリア 表 ページ インデックス 行 キー値 図 排他資源の包含関係 特に指定をしない限り 行が排他の単位となりますが 行 ページ 表単位のいずれかを明示的に指定することができます ただし 排他資源の単位を変更する場合は トランザクションの同時実行性と排他のオーバヘッドとのトレードオフを十分に考慮する必要があります 排他資源の単位を小さくすると排他が競合する可能性が小さくなり 同時に実行できるトランザクション数が多くなります ただし 頻繁に排他の獲得や 排他の解除が行われるため 排他のオーバーヘッドが大きくなります 逆に排他資源の単位を大きくすると同時実行性は低下しますが 排他のオーバーヘッドは小さくなります 排他資源の単位 大 同時実行性 低 ( 悪い ) 排他のオーバーヘッド 小 ( 良 ) トレードオフ 小 高 ( 良い ) 図 排他資源単位指定時のトレードオフ 大 ( 悪 ) 88

100 4 章アプリケーションプログラムの開発 排他単位の指定方法 排他単位の明示的な指定は CREATE TABLE 文 ( 行 / ページ ) ALTER TABLE 文 ( 行 / ページ ) LOCK 文 ( テーブル単位 ) で行います CREATE TABLE 文の文法 CREATE TABLE テーブル名 ( 列定義 ) IN RD エリア名 {LOCK ROW LOCK PAGE} ALTER TABLE 文の文法 ALTER TABLE テーブル名 CHANGE {LOCK ROW LOCK PAGE} LOCK 文の文法については 4.8.4(2) LOCK 文に指定する排他オプション で説明します 排他制御モード HiRDB の排他には次の 2 種類があります これら総称して排他制御モードといいます 共用モード (PR モード :Protected Retrieve Mode) 排他モード (EX モード :EXclusive Mode) 特に指定をしない限り 検索をするときは共用モード 更新をするときは排他モードで排他がかかります 同一資源に対して処理を行う二つのトランザクションがある場合 同時に実行できるかどうかは双方の排他制御モード間の関係で異なります 排他制御モードと同時実行性との関係を次に示します 排他制御モード 共用モード 排他モード 表 排他制御モード内容 データ参照時に用います 他のトランザクションは 参照はできますが更新はできません データ更新時に用います 他のトランザクションは 参照も更新もできません 89

101 トランザクションA 4 章アプリケーションプログラムの開発 表 排他制御モード間の同時実行性 トランザクション B 共用モード 排他モード 共用モード 排他モード ( 凡例 ) : 同時に実行できます 待ち状態にはなりません : 同時に実行できません 後から開始したトランザクションが待ち状態になります 排他制御モードの指定 アプリケーション開発者は 排他制御モードを指定することができます 排他の期間 特に指定をしない限り トランザクションがある資源に対して排他モードで排他をかけると コミットまたはロールバックするまで資源を占有します 例えば トランザクション 1 がある行に対して更新処理を行うと その行には排他モードで排他がかかります この場合 その行に対して トランザクション 2 はトランザクション 1 がコミットまたはロールバックされるまで待ち状態になります トランザクション 1 行 X の更新 1:EX ロック X トランザクション 2 2:PR ロック Wait 行 X の検索 3 コミット トランザクション 1 のコミット後に トランザクション 2 の wait が解除される 図 排他による資源の占有 排他期間の指定 アプリケーション開発者は 排他の期間を指定することができます 90

102 4 章アプリケーションプログラムの開発 排他オプションの指定方針 業務内容やトランザクションの実行内容に応じ アプリケーション開発者は 適切な排他オプションを指定する必要があります 次に 排他オプションを指定する際の主な方針を示します (1) SELECT 文に指定する排他オプション WITHOUT LOCK NOWAIT どのトランザクションからも検索対象の表への更新処理が行われない場合や ダーティリード 29 を許す場合に指定します WITHOUT LOCK WAIT 同一トランザクション内で 同一行を複数回読まない場合に指定します WITH SHARE LOCK 検索した行をトランザクション完了時まで保証したい場合に指定します WITH EXCLUSIVE LOCK 検索した行を同一トランザクション内で更新する場合に指定します Oracle との違い Oracleの場合は 整合性よりも同時実行性を優先するため デフォルトではSELECT 文実行時にロックの獲得を行いません 30 一方 HiRDBの場合は SELECT 文実行時に共用モードのロックを獲得するのがデフォルトになります したがって OracleのFOR UPDATE 指定のないSELECT 文 31 は HiRDBの WITHOUT LOCK NOWAITまたはWITHOUT LOCK WAIT 指定に対応しています データ不整合の一種であるダーティリードを許す場合は WITHOUT LOCK NOWAIT を指定し 許さない場合はWITHOUT LOCK WAITを指定します Oracle の FOR UPDATE 指定のある SELECT 文は HiRDB の次の指定に対応しています 29 他の確定前のトランザクションによる更新結果を読むことです 更新トランザクションがロールバックされた場合 誤った結果を読んだことになります 30 Oracle の仕様については Oracle のマニュアル Oracle Database 概要 の 第 I 部 Oracle の概要 Oracle Database の機能 中の スケーラビリティおよびパフォーマンス機能の概要 - 読取り一貫性 で説明されています 31 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 19 SQL 文 : SAVEPOINT~UPDATE 中の SELECT-for_update_clause で説明されています 91

103 4 章アプリケーションプログラムの開発 WITH EXCLUSIVE LOCK SELECT 文に WITH EXCLUSIVE LOCK を指定します PDFORUPDATEEXLOCK=YES クライアント環境定義 PDFORUPDATEEXLOCK=YES を指定します (2) LOCK 文に指定する排他オプション LOCK 文は バッチ処理の実行時に 他のトランザクションからの操作を防ぎたい場合に使用します PR ロックまたは EX ロックを表単位に獲得することができるため アプリケーションの ISO Isolation レベルを serializable にすることができます 文法 LOCK TABLE ユーザ名. 表名 IN{SHARE EXCLUSIVE } MODE デッドロックが発生するケース 二つのトランザクションが ある資源に対して排他をかけ お互いに相手のコミットまたはロールバックを待つ状態のことをデッドロックといいます デッドロックは 複数の資源に対するアクセス順の不統一や同一資源を参照後 更新を行うトランザクションが複数同時に実行されることが原因で発生します デッドロックの発生例を次に示します 複数のリソースに対し EX ロックの獲得順が交差する場合 トランザクション 1 トランザクション 2 行 A の更新 1:EX A 2:EX 行 B の更新 行 B の更新 3:EX Wait B 4:EX Wait 行 A の更新 図 デッドロック発生例 1 92

104 4 章アプリケーションプログラムの開発 同一リソースに対し PR ロックで排他を獲得後に EX ロックを獲得する場合 トランザクション 1 トランザクション 2 行 X の検索 1:PR X 2:PR 行 X の検索 行 X の更新 3:EX Wait 4:EX Wait 行 X の更新 図 デッドロック発生例 2 デッドロックが発生した場合 HiRDB は基本的に後から開始したトランザクション ( 図 のトランザクション 2) をロールバックします デッドロックが解除されると 先に開始したトランザクション ( 図 のトランザクション 1) は先の処理へ進みます デッドロックの回避策 デッドロックを回避するための対策を次に示します (1) データのロック獲得順をトランザクション間で統一する行 A B を更新するトランザクションが複数ある場合 先行のトランザクションが A B の順で更新し 後続のトランザクションが B A の順で更新すると 先行のトランザクションが行 A に 後続のトランザクションが行 B にそれぞれ EX ロックを獲得することになり その後の処理が進まなくなる恐れがあります (4.8.5 の 図 デッドロック発生例 1 参照) そこで 両トランザクションの更新順序を A B に統一し 先行のトランザクションがコミットするまで後続のトランザクションの処理を待機させることで 両トランザクション間のデッドロックの発生を防ぐことができます トランザクション 1 トランザクション 2 行 A の更新 1:EX A 2:EX Wait 行 A の更新 行 B の更新 3:EX B 6:EX 行 B の更新 4: コミット 5:Wait の解除 実行 図 デッドロック回避策 1 (2) 検索時に WITH EXCLUSIVE LOCK を指定する 検索後に更新を行うトランザクションを同時に複数実行すると PR ロックと EX ロックとが競合し その後の処理が進まなくなる恐れがあります (4.8.5 の 図 デッドロッ 93

105 4 章アプリケーションプログラムの開発 ク発生例 2 参照) そこで 検索時に EX ロックを獲得することで 他のトランザクションからのロックの獲得を阻止し デッドロックの発生を防ぐことができます ただし この場合 同時実行性が下がります トランザクション 1 トランザクション 2 行 X の検索 1:EX X 2:PR Wait 行 X の検索 行 X の更新 3:EX 6:EX 行 X の更新 4: コミット 5:Wait の解除 実行 図 デッドロック回避策 2 (3) 処理対象表に LOCK 文を実行する処理対象の表に LOCK 文を実行することで 表単位に EX ロックをかけることができます この方法により 先行のトランザクションの処理中に他のトランザクションからは検索も更新もできなくなるため デッドロックの発生を防ぐことができます ただし 排他資源の単位が大きくなるため 同時実行性は著しく下がります 図 デッドロック回避策 デッドロックが発生したときに出力される情報 デッドロックが発生した場合 次に示すファイルにデッドロックの発生情報が出力されます アプリケーションプログラムの開発者は これらの情報からデッドロックの発生原因を特定し 対処してください SQLエラーレポートファイル 32 このファイルには デッドロックによってエラーになった SQL 文とパラメータ情報が出力されます 32 SQL エラーレポートファイルに情報を出力するには クライアント環境定義の PDUAPEXERLOGUSE パラメータに YES を指定する必要があります 94

106 4 章アプリケーションプログラムの開発 システム定義のpd_uap_exerror_log_dirパラメータ 33 で指定したディレクトリにこのファイルが出力されます エラーログファイルこのファイルには エラーになった SQL の情報が出力されます アプリケーションプログラム実行時のカレントディレクトリにこのファイルが出力されます なお ファイルの出力先はクライアント環境定義の PDCLTPATHパラメータで変更できます デッドロック情報ファイルこのファイルには デッドロックの原因となった排他の種類や 要求した資源の情報 排他待ちのタイムアウト情報が出力されます %PDDIR% spool pdlckinf 下にこのファイルが出力されます 体験版 HiRDB の場合は "C:\Program Files\HiRDB_TR\SRV\SPOOL\PDLCKINF" 下に出力されます 33 簡易セットアップツールで pd_uap_exerror_log_dir パラメータを追加できます パラメータの追加方法については 付録 E システム定義のパラメータの追加方法 で説明しています 95

107 4 章アプリケーションプログラムの開発 (1) ファイルの出力例 RD エリア RDDATA10 内の MEMBERS 表へのアクセス中にデッドロックが発生しました このときの SQL エラーレポートファイル エラーログファイル およびデッドロック情報ファイルの出力例を次に示します トランザクション 1 MEMBERS 表トランザクション 2 member_id ='30' の検索 1:PR member_no member_mail 2:PR member_id ='30' の検索 member_id ='30' の行の member_mail 列の値を yyy に更新 3:EX Wait 30 aaa 4:EX Wait member_id ='30' の行の member_mail 列の値を zzz に更新 DeadLock SQL エラーレポートファイル 96

108 4 章アプリケーションプログラムの開発 エラーログファイル デッドロック情報ファイル Deadlock information Aug 10 10:18: program:pw9ja049 server:sds01 pid:3656 trnbid:hrd1unt actid: dprio:64 テ ット ロックによりロールハ ックされたトランサ クション テ ット ロックを構成したトランサ クション 排他制御モード排他資源種別 (0007: 行 ) occupy 占有 server:sds01 lock mode:pr kind:0007 resource info: e00007b 排他による占有資源 wait 待ち server:sds01 lock mode:ex kind:0007 resource info: e00007b 排他待ち資源 wait start time 10:18:56 program:pw9ja049 server:sds01 pid:2632 trnbid:hrd1unt actid: dprio:64 occupy server:sds01 lock mode:pr kind:0007 resource info: e00007b wait server:sds01 lock mode:ex kind:0007 resource info: e00007b wait start time 10:18:45 97

109 4 章アプリケーションプログラムの開発 4.9 SQL のエラー判定 SQL 文を実行すると HiRDB によりリターンコードが SQLCODE 及び SQLSTATE という特殊な変数にセットされます これらの値を参照することにより SQL 文の実行状態が判定できます SQL 文の実行状態と SQLCODE 及び SQLSTATE 変数に設定される値の関係を次に示します 表 SQL の実行状態と変数に設定される値 SQLCODE の値 SQLSTATE の値 SQL の実行状態 正常終了 01nnn 警告付き正常終了 正 ( 100) データなし 負 ( 暗黙的にロールバックされた ) エラー終了 nnnnn( 40000) ( 暗黙的にロールバックされていない ) エラー終了 ( 凡例 )-: 状態の判断に使用する必要はありません 98

110 4 章アプリケーションプログラムの開発 4.10 性能が向上する SQL の書き方 SQL の書き方によってアプリケーションプログラムのパフォーマンスに影響がでます ここでは アプリケーションプログラムの性能を向上するための SQL の書き方について 代表的なものを説明します 表の結合方式 表結合では FROM 句に指定された表の順序や結合方式の違いによって パフォーマン スが大きく左右されます 適切でない結合順序または結合方法が選択された場合は アク セスするデータページ数が増加し パフォーマンスが大きく低下してしまう恐れがありま す ここでは 表の各種結合方式について説明します (1) ネスト ( 入れ子 ) ループ結合 <Nested Loop Join> 起点となる片方の表からデータを 1 行ずつ取り出し もう片方の表と突き合わせながら 結合する方法です このとき起点となる表のことを外表 比較対象となる表のことを内表 といいます ネストループ結合は 一方または両方の結合対象表の母体件数を絞り込むことができ かつ結合結果の件数が少ない場合に 最も効果を発揮します ネストループ結合を行う際 HiRDBでは 結合処理対象件数の少ない表を外表とし 結合条件列にインデックスが存在し 効率良くアクセスできる表を内表とします 34 select INST.INST_ID,INST_NAME,SEMINAR_ID,SUBJECT_ID,START_DATE from INSTRUCTORS INST inner join SEMINARS SEM on INST.INST_ID = SEM.INST_ID where INST_GRADE = M-1 このキーワードは 6 章のチューニング情報の説明にでてきます (2) 以降の結合方式についても同様にキーワードがあります SEMINARS 表の INST_ID 列上のインデックス 外表 INSTRUCTORS:30 件 内表 SEMINARS:1000 件 INST_ID INST_NAME HIRE_YEAR INST_GRADE 9001 Mariko Sasaki 1975 M Yuji Miyamoto 1980 M Keiko Kikuchi 1988 M Hiroshi Sato 1990 M Eri Yokoyama 1990 S Tsukasa Goto 1992 M Masayuki Eguchi 2004 P-2 SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 05K DB K AP K UN K MG K NW K IT K DB K AP 図 ネストループ結合の概要 34 基本的には 結合対象表の行数やインデックスの有無などをもとに HiRDB が外表 内表を決定しますが ユーザが SELECT 文の構文で指定することもできます 99

111 4 章アプリケーションプログラムの開発 (2) マージ ( 併合 ) 結合 <Merge Join> マージ結合は 結合対象の表をソートすることにより 結合条件を満たす行を効率良く探し出す方法です ソートによる負荷が伴いますが 全体の処理時間が最小化されるため 大量データ同士を結合する場合に効果的です select SUB.SUBJECT_ID,SUBJECT_NAME,SEMINAR_ID,INST_ID from SUBJECTS SUB inner join SEMINARS SEM on SUB.SUBJECT_ID = SEM.SUBJECT_ID SUBJECTS:500 件 SUBJECT_ID SUBJECT_NAME DAYS DB01 Database fundamentals 1 DB02 HiRDB DBA 2 DB03 Data Modeling 3 DB04 Database Perfomance Tuning 2 AP01 Java Programming fundamentals 2 NW01 Network fundamentals 1 NW02 TCP/IP fundamentals 1 SEMINARS:10,000 件 SEMINAR _ID START_DATE END_DATE SUBJECT_ID INST_ID 05K DB K AP K UN K MG K NW K IT K DB K AP ソート ソート SUBJECT_ID AP01 AP02 DB01 DB02 DB03 DB04 作業表 SUBJECT_NAME Java Programming fundamentals Build J2EE Applications Database fundamentals HiRDB DBA Data Modeling Database Perfomance Tuning 作業表 SEMINAR _ID SUBJECT_ID INST_ID 05K009 AP K020 AP K011 AP K022 AP K001 DB K015 DB K050 DB K013 DB K031 DB 図 マージ結合の概要 100

112 ハッシュ関数4 章アプリケーションプログラムの開発 (3) ハッシュ結合 <HASH JOIN> 結合対象表の結合列の値をハッシュ関数にとおし ( ハッシング ) 合致する結果同士を結合する方法です まず内表を 結合列の値でハッシングし メモリ上にハッシュ表を作成します 続いて外表から 1 行取り出すごとに結合列の値でハッシングし メモリ上のハッシュ表と突き合わせながら結合します ハッシュ結合は 結合対象表の母体件数の大小にかかわらず 結合結果の件数が多い場合に効果的です ただし コストベース最適化モード 2 35 を適用していて かつハッシュ表用に十分なメモリ容量が確保できる場合でないと 十分な効果が得られないためため 使用範囲は限られます 内表 INSTRUCTORS:30 件 select INST.INST_ID,INST_NAME,SUBJECT_ID,SEMINAR_ID from INSTRUCTORS INST inner join SEMINARS SEM on INST.INST_ID = SEM.INST_ID INST_ID INST_NAME HIRE_YEAR INST_GRADE 9001 Mariko Sasaki 1975 M Yuji Miyamoto 1980 M Keiko Kikuchi 1988 M Hiroshi Sato 1990 M Eri Yokoyama 1990 S Tsukasa Goto 1992 M Masayuki Eguchi 2004 P-2 ハッシュ関数 外表 SEMINARS:10,000 件 SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID メモリ上 INSTRUCTORS 表から生成したハッシュ表ハッシュ値 INST_ID INST_NAME 05K DB K AP K UN K MG K NW K IT K DB K AP Keiko Kikuchi 9006 Tsukasa Goto 9001 Mariko Sasaki 9004 Hiroshi Sato 9002 Yuji Miyamoto 9005 Eri Yokoyama 図 ハッシュ結合の概要 35 コストベース最適化モード 2 については マニュアル HiRDB Version 8 UAP 開発ガイド を参照してください 101

113 4 章アプリケーションプログラムの開発 (4) 効率の良い結合方式 検索の条件ごとに効率の良い結合方式を次に示します 結合結果の件数が少ない 結合結果の件数が多い 検索の条件 一方の表の母体件数が少ない 両方の表の母体件数が多い メモリサイズに余裕があるメモリサイズに余裕がない 効率の良い結合方式ネストループ結合ハッシュ結合ハッシュ結合マージ結合 表の結合を行うときの考慮点 結合処理のパフォーマンスを向上させるためには 以下の点を考慮します theory1 不必要な表を結合しない 結合処理では 結合表数が増えるほど処理負荷が高くなるので 可能な限り 結合する表数は少なく抑えます theory2 結合条件は=( 等号 ) 条件を指定する スカラ演算 36 やOR 演算を使用しない 結合条件に対しスカラ演算や OR 論理演算を指定すると 直積で処理を行うため処理負荷が高くなります したがって可能な限り 結合条件には=( 等号 ) 条件を指定します STOCKS GOODS_ID STOCK_NUM MAKER_ID CB A066 CB F097 CB H108 CB D763 select ST.GOODS_ID,ST.STOCK_NUM,ORD.ORDER_NUM from ORDERS ORD,STOCKS ST where ORD.GOODS_ID = ST.GOODS_ID or ORD.ORDER_NUM = ST.STOCK_NUM; ORDERS ORDER_ID GOODS_ID ORDER_NUM 001 CB CB CB DH select ST.GOODS_ID,ST.STOCK_NUM,ORD.ORDER_NUM from ORDERS ORD,STOCKS ST where ORD.GOODS_ID = ST.GOODS_ID UNION ALL select ST.GOODS_ID,ST.STOCK_NUM,ORD.ORDER_NUM from ORDERS ORD,STOCKS ST where ORD.ORDER_NUM = ST.STOCK_NUM and ORD.GOODS_ID!= ST.GOODS_ID; 重複行を排除 図 or 演算の書き換え 36 値式中で指定できる演算のうち 四則演算 日付演算 時刻演算 連結演算 CASE 式 CAST 指定 ウィンドウ関数 およびスカラ関数を総称して スカラ演算といいます 102

114 4 章アプリケーションプログラムの開発 theory3 表の結合は ネストループ結合になるように設計する 結合結果の行数が少ない場合は ネストループ結合 を使用すると 最も性能が良くなります そのためには ネストループ結合が行われるようにインデックスを作成する必要があります Oracle との違い Oracle の場合は標準 SQL の左外結合と右外結合をサポートしていますが HiRDB の場合は標準 SQL の左外結合だけをサポートしています ( 右外結合はサポートしていません ) しかし 右外結合を左外結合に書き換えることができます また Oracle の場合は 標準 SQL 以外の独自文法で外結合を書くことができますが 全て標準 SQL の左外結合に書き換えることができます OracleとHiRDBの外結合の対応を次に示します 37 次のSQLは書き方が違うだけで内容は同じです 表 外結合の対応 Oracle の場合 HiRDB の場合 左外結合 ( 標準 SQL) 右外結合 ( 標準 SQL) SELECT FROM T1 LEFT OUTER JOIN T2 ON T1.α=T2.α SELECT FROM T1 RIGHT OUTER JOIN T2 ON T1.α=T2.α SELECT FROM T1 LEFT OUTER JOIN T2 ON T1.α=T2.α SELECT FROM T2 LEFT OUTER JOIN T1 ON T1.α=T2.α 左外結合 (Oracle 独自 ) SELECT FROM T1,T2 WHERE T1.α=T2.α(+) SELECT FROM T1 LEFT OUTER JOIN T2 ON T1.α=T2.α 右外結合 (Oracle 独自 ) SELECT FROM T1,T2 WHERE T1.α(+)=T2.α SELECT FROM T2 LEFT OUTER JOIN T1 ON T1.α=T2.α 37 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の 9 SQL 問い合わせ及び副問い合わせ 中の 結合 - 外部結合 で説明されています 103

115 4 章アプリケーションプログラムの開発 集合演算を行うときの考慮点 集合演算を使用すると 各 SELECT 文は 表に対するアクセスを別々に行います また UNION を指定した場合 処理結果から重複を省くために作業表を作成します そのため 集合演算を他の書き方に変更すると パフォーマンスが向上することがあります (1) 集合演算 他の文法への書き換え theory4 各 SELECT 文の検索条件列や条件に使用している述語が同一である場合は 集合演 算を使用しない 次の例では SEMINAR_ID が 05K001 か 05K006 であるセミナーを受講した者を検索し ています このような場合に 検索条件の書き方を工夫すると 表へのアクセス回数や作業表作成時の負荷を削減できます ATTENDANCE_HISTORY SEMINAR_ID STU_ID INST_ID INST_NAME ROOM S_F_FLAG ] select INST_ID from ATTENDANCE_HISTORY where SEMINAR_ID = 05K001 UNION select INST_ID from ATTENDANCE_HISTORY where SEMINAR_ID = 05K006 05K Hiroshi Sato K Tsukasa Goto K Daisuke Yoshida K Megumi Takahashi select INST_ID from ATTENDANCE_HISTORY where SEMINAR_ID in ( 05K001, 05K006 ) 図 集合演算の書き換え 104

116 4 章アプリケーションプログラムの開発 (2) UNION UNION ALL への書き換え theory5 演算結果に重複データが含まれておらず UNION と UNION ALL の結果が同じになる 場合 または重複データをまとめる必要がない場合は UNION ALL を使用する UNION を指定すると 重複データをまとめるために 作業表上でソート等の負荷の高い 処理が行われます したがって 検索結果に重複データが含まれていない場合や 重複データが結果に存在しても問題ない場合は UNION ALL を使用します その他の考慮点 同じ結果が得られる SQL 文であっても 書き方によって内部的にソートやグループ化等の負荷の高い処理が発生することがあります そのため 高負荷の処理の発生を必要最低限に抑えるための書き換えを検討します (1) HAVING 句 WHERE 句 theory6 グループ化と検索条件を指定する場合 集計結果に対する条件指定以外は WHERE 句を使用する HAVING 句は GROUP BY 句の結果に対し条件を指定します その際 グループキーに インデックスを指定している場合 全ての行を対象に条件を評価します これに対し WHERE 句では 条件に一致する行に対してだけアクセスを行うため 負荷を軽減する ことができます ただし 集合関数の結果を検索条件に指定する場合は WHERE 句を 使用できません INSTRUCTORS INST_ID INST_NAME HIRE_YEAR INST_GRADE 9001 Mariko Sasaki 1975 M Yuji Miyamoto 1980 M Keiko Kikuchi 1988 M Hiroshi Sato 1990 M-1 select INST_GRADE,MIN(HIRE_YEAR) select INST_GRADE,MIN(HIRE_YEAR) A,AVG(C) from T1 from INSTRUCTORS from INSTRUCTORS where A = B894 and B = C051 group by INST_GRADE where group INST_GRADE by A = M-1 HAVING INST_GRADE = M-1 group by INST_GRADE 図 FOR UPDATE を含む SQL 文の書き換え 105

117 4 章アプリケーションプログラムの開発 (2) DISTINCT 指定 GROUP BY 句 theory7 確実に重複があり かつ重複を排除する必要がある場合以外は DISTINCT を使用し ない また 検索対象列が単一列であり かつその列にインデックスが作成されている場合は GROUP BY 句を使用する DISTINCT は重複をまとめる処理です この場合 作業表作成とソート処理が発生するため 負荷が高くなります そのため 必要のない DISTINCT は指定しないようにします また 重複をまとめる必要がある場合も 検索対象列が単一列であり かつその列にインデックスが作成されているのであれば GROUP BY 句に書き換えると負荷を軽減することができます 38 ATTENDANCE_HISTORY SEMINAR_ID STU_ID INST_ID INST_NAME ROOM S_F_FLAG 05K Hiroshi Sato K Tsukasa Goto K Daisuke Yoshida K Megumi Takahashi select ROOM from ATTENDANCE_HISTORY select distinct ROOM from ATTENDANCE_HISTORY where INST_ID = 9004 where INST_ID = 9004 group by ROOM 図 DISTINCT の書き換え 38 インデックスを利用できなくても グループ分け高速化機能 の適用によって 処理性能が向上する場合があります グループ分け高速化機能は デフォルトで適用されています 106

118 4 章アプリケーションプログラムの開発 4.11 保守性を良くする SQL の書き方 SQL 文の書き方を工夫して テーブルの構造変更などに伴うプログラムの変更頻度を減 らし 保守性を良くすることができます ここでは アプリケーションプログラムの保守性を良くするための SQL の書き方について代表的なものを説明します SELECT 文を指定する場合の考慮点 theory8 複数の表の結合時 すべての列名に対し テーブル名かテーブルの別名を付与する 結合とは 2 つ以上の表から 共通の列の値が等しい行をつなげて検索する処理のことです このとき SQL 文中に指定されているすべての列に対してテーブルの別名を付与すると 列が属しているテーブルを明確にすることができ 可読性 保守性が良くなります INSTRUCTORS:30 件 INST_ID INST_NAME HIRE_YEAR INST_GRADE 9001 Mariko Sasaki 1975 M Yuji Miyamoto 1980 M Keiko Kikuchi 1988 M Hiroshi Sato 1990 M Eri Yokoyama 1990 S Tsukasa Goto 1992 M Masayuki Eguchi 2004 P-2 SEMINARS:1000 件 SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 05K DB K AP K UN K MG K NW K IT K DB K AP select INST.INST_ID,INST.INST_NAME,SEM.SEMINAR_ID,SEM.SUBJECT_ID,SEM.START_DATE from INSTRUCTORS INST inner join SEMINARS SEM on INST.INST_ID = SEM.INST_ID 図 テーブル名の付与 本書に出てくる SELECT 文の例は ここの説明に従っていないことがあります 107

119 4 章アプリケーションプログラムの開発 theory9 全列を検索する際に SELECT 句を * で省略せずに 列名を明示する SELECT 句に * を指定していて かつアプリケーションプログラムが列順どおりに値を受け取るようにコーディングされていると 表に新たな列を追加したときなどに アプリケーションプログラムの修正が必要となります また 処理上全ての列が必要ない場合に * が指定されていると 必要のない無駄な列も読み込むことになるため 処理 転送時間やデータディクショナリ表を解析する時間が増えて パフォーマンス低下につながります select * from STUDENTS where HIRE_YEAR = 2002 INSTRUCTORS INST_ID INST_NAME HIRE_YEAR INST_GRADE 9001 Mariko Sasaki 1975 M Yuji Miyamoto 1980 M Keiko Kikuchi 1988 M Hiroshi Sato 1990 M-1 または select INST_ID,INST_NAME from STUDENTS where HIRE_YEAR = 2002 select INST_ID,INST_NAME,HIRE_YEAR,INST_GRADE from STUDENTS where HIRE_YEAR = 2002 図 SELECT 句の * 指定の禁止 INSERT 文の場合も同様です INSERT 文に 追加対象の列名を明示的に指定すると 表に新たな列を追加しても アプリケーションプログラムの修正を減らすことができます STUDENTS STU_ID STU_NAME CORP_ID 1 Taro Yamada A001 2 Jiro Ito K003 3 Hanae Suzuki H153 4 Yoshihiro Sato A Yumi Maeda A001 INSERT INTO STUDENTS VALUES (31, 'Yumi Maeda', A001 ) INSERT INTO STUDENTS(STU_ID,STU_NAME,CORP_ID) VALUES (31, 'Yumi Maeda', A001 ) 図図 INSERT 文の列名指定 108

120 5 章 Java を使ったアプリケーションプログラムの開発 5 Java を使ったアプリケーション プログラムの開発 この章では JDBC を用いた Java 言語によるアプリケーションプログラム の実装方法について説明します 5.1 JDBC 概要 5.2 アプリケーションプログラムの基本構成 5.3 JDBC ドライバの登録 5.4 HiRDB への接続 5.5 SQL によるデータベースの検索 5.6 SQL によるデータベースの更新 / 削除 / 追加 5.7 トランザクションの制御 5.8 HiRDB からの切り離し 5.9 例外処理 5.10? パラメタと PreparedStatement 5.11 アプリケーションプログラムの例 5.12 環境設定 5.13 Java ソースファイルの作成とコンパイル 109

121 5 章 Java を使ったアプリケーションプログラムの開発 5.1 JDBC 概要 JDBC(Java DataBase Connectivity) は Java アプリケーションプログラムから DBMS にアクセスするときの API の一種であり 特定の DBMS 製品に依存しない標準的な API です JDBC は 下図に示す通り JDBC API JDBC ドライバマネージャ JDBC ドライバ API JDBC ドライバという4 要素から構成されます Java アプリケーションプログラムは HiRDB にアクセスするときも 他社 DBMS にアクセスするときも 共通のインターフェースである JDBC API を呼び出します したがって Java アプリケーションプログラムの開発者は JDBC API さえ知っていればよいといえます 各 DBMS 製品の違いは JDBC ドライバが吸収します Java アプリケーションプログラム JDBC JDBC API(java.sql) JDBC ドライバマネージャ JDBC ドライバ API JDBC ドライバ for HiRDB JDBC ドライバ for 他社 DBMS HiRDB 他社 DBMS 図 JDBC の構成要素 110

122 5 章 Java を使ったアプリケーションプログラムの開発 JDBC API Java のアプリケーションプログラムから様々な DBMS にアクセスするための標準の API です JDBC API は java.sql パッケージによって提供されます したがって JDBC を使用して DBMS にアクセスするアプリケーションプログラムでは クラス名やインターフェース名のみで JDBC API を参照できる様に java.sql パッケージをインポートするのが一般的です JDBC ドライバマネージャ JDBC ドライバを管理するソフトウェアです 標準のインターフェースである JDBC API による接続要求があったとき それを正しく処理できる JDBC ドライバを選択し 要求を渡します JDBC ドライバ共通のインターフェースである JDBC API を 各 DBMS 製品固有のインターフェースに変換するソフトウェアです DBMS 製品固有の処理 ( 接続や SQL の送受信など ) は JDBC ドライバが行います JDBC アプリケーションプログラムを開発 / 実行するマシンでは アクセス対象となる DBMS に対応した JDBC ドライバをあらかじめクライアントにインストールし 環境設定をしておく必要があります HiRDB では Type4 の JDBC ドライバを利用できます 体験版 HiRDB をインストールすると Type4 の JDBC ドライバが自動的にインストールされます JDBC ドライバ API JDBC ドライバマネージャから JDBC ドライバを取り扱うためのインターフェースを規定した API です つまり JDBC ドライバの開発者 のために用意された標準の API です したがって 通常の Java アプリケーションプログラムの開発者は 詳細を知る必要がありません 111

123 5 章 Java を使ったアプリケーションプログラムの開発 5.2 アプリケーションプログラムの基本構成 JDBC を使用して HiRDB にアクセスする Java アプリケーションプログラムの基本構成 は以下のようになります 1 // 1 java.sql パッケージのインポート 2 import java.sql.*; Connection con = null; 6 try { 7 // 2 JDBC ドライバの登録 8 Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver"); 9 10 // HiRDB への接続情報の設定 11 String url = "jdbc:hitachi:hirdb://dbid=22200,dbhost=localhost";// 接続先 12 String userid = "username"; // ユーザ名 13 String passwd = "password"; // パスワード 14 // 3 HiRDB へ接続 15 con = DriverManager.getConnection(url, userid, passwd); // 4 オートコミットモードの設定 18 con.setautocommit( false ) ; // 5 SQL によるデータベースの操作 // 6 トランザクションのコミット 24 con.commit() ; } catch (ClassNotFoundException ce) { // 8 例外処理 27 System.err.println(ce.getMessage()); 28 } catch (SQLException se1) { 29 try{ 30 // トランザクションのロールバック 31 if ( con!= null){ 32 con.rollback(); 33 } 34 }catch(sqlexception se2){ 35 System.err.println(" ロールバックエラーです \n" + se2.getmessage()); 36 } 37 System.err.println(se1.getMessage()); 38 } finally { 39 try{ 40 // 7 HiRDB から切り離し 41 if ( con!= null &&!con.isclosed() ){ 42 con.close(); 43 } 44 } catch (SQLException se3) { 45 System.err.println(se3.getMessage()); 図 JDBC アプリケーションプログラムの基本構成 1 java.sql パッケージのインポートクラス名やインターフェース名のみで JDBC API を参照できる様に java.sql パッケージをインポートします 112

124 5 章 Java を使ったアプリケーションプログラムの開発 2 JDBC ドライバの登録 HiRDB に接続するための準備として HiRDB の JDBC ドライバをロードし ロードした JDBC ドライバの名称 ( パッケージを含めた完全なクラス名 ) を JDBC ドライバマネージャに登録します 詳細については 5.3 JDBC ドライバの登録 で説明します 3 HiRDB へ接続アプリケーションプログラムを HiRDB へ接続します 詳細については 5.4 HiRDB への接続 で説明します 4 オートコミットモードの設定 1つの SQL 文を1つのトランザクションとする場合はオートコミットを有効に 複数の SQL 文を1つのトランザクションとする場合はオートコミットを無効にします デフォルトではオートコミットが有効となっています 詳細については 5.7 トランザクションの制御 で説明します 5 SQL によるデータベースの操作 SQL を使用してデータベースの操作を行います 詳細については 5.5 SQL によるデータベースの検索 および 5.6 SQL によるデータベースの更新 / 削除 / 追加 で説明します 6 トランザクションのコミットオートコミットが無効の場合 トランザクションをコミットします オートコミットが有効の場合 ( デフォルト ) 1つの SQL 文を実行するごとに自動的にトランザクションがコミットされるため 明示的なコミットの必要はありません 詳細については 5.7 トランザクションの制御 で説明します 7 HiRDB から切り離しアプリケーションプログラムを HiRDB から切り離します 詳細については 5.8 HiRDB からの切り離し で説明します 8 例外処理上記 1~7によりスローされる可能性のある例外オブジェクトをキャッチします 上記 5 で更新系の SQL を実行する場合 例外処理の中でトランザクションを明示的にロールバックします 詳細については 5.9 例外処理 で説明します 113

125 5 章 Java を使ったアプリケーションプログラムの開発 5.3 JDBC ドライバの登録 JDBC ドライバをドライバマネージャに登録するには 引数に JDBC ドライバのクラス名 ( パッケージ を含めた完全なクラス名 ) を指定して Class クラスの forname メソッドを呼び出します ドライバの登録 Class.forName( driver ); 意味 driver String 型 JDBC ドライバのクラス名 ( パッケージを含めた完全なクラス名 ) を指定します String 型の引数のため 文字列を直接指定する場合は ダブルクォーテーションで括る必要があります HiRDB の JDBC ドライバのクラス名は以下になります JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver 例外 ClassNotFoundException 登録時に指定されたドライバがロードできなかった場合に発生します その場合 JDBC ドライバのクラスファイルが CLASSPATH に正しく設定されているかを確認してください 例 ( ドライバの登録 ) Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver"); 114

126 5 章 Java を使ったアプリケーションプログラムの開発 5.4 HiRDB への接続 アプリケーションプログラムを HiRDB へ接続するには DriverManager クラスの getconnection メ ソッドを呼び出します getconnection メソッドは 接続が確立されると Connection 型のオブジェク トを返します HiRDB への接続 Connection con = DriverManager.getConnection(JDBCURL,DBUser,DBPassword); 意味 JDBC URL String 型一般に DBMS への接続情報 ( どの DBMS にどのような手段で接続するかといった情報 ) を指定します HiRDB の場合 以下を指定します jdbc:hitachi:hirdb:[//dbid={ ポート番号 クライアント環境定義ファイル名 }] [,][DBHOST= 接続先ホスト名 ] [//DBID={ ポート番号 クライアント環境定義ファイル名 }] 接続先 HiRDB のポート番号を指定するか またはクライアント環境定義ファイル名を指定します クライアント環境定義ファイル名には 2.2.3(2) 任意のファイルへの指定 で作成したファイル名を指定します 次に示す形式で指定します DBID=@HIRDBENVGRP=C:\\envgrp\\clientdef 2.2.3(2) 任意のファイルへの指定 で作成したファイルのパス名を指定します 接続先 HiRDB のポート番号を指定しない場合は 次に示すどちらかの指定値がポート番号として設定されます ( 優先順位は 1 2 の順です ) 1.getConnection メソッドの Properties 引数中の HiRDB_for_Java_ENV_VARIABLES で指定したクライアント環境定義の PDNAMEPORT の指定値 2.URL 中の DBID で指定したクライアント環境定義ファイルの PDNAMEPORT の指定値どちらも指定されていない場合は getconnection メソッド実行時に SQLException がスローされます [DBHOST= 接続先ホスト名 ] 接続先 HiRDB のホスト名を指定します 省略した場合は 次に示すどちらかの指定値が接続先ホスト名として設定されます ( 優先順位は 1 2 の順です ) 1.getConnection メソッドの Properties 引数中の HiRDB_for_Java_ENV_VARIABLES で指定したクライアント環境定義の PDHOST の指定値 115

127 5 章 Java を使ったアプリケーションプログラムの開発 2.URL 中の DBID で指定したクライアント環境定義ファイルの PDHOST の指定値どちらも指定されていない場合は getconnection メソッド実行時に SQLException がスローされます DBUser String 型 HiRDB のユーザー名を指定します DBPassword String 型 HiRDB のユーザーに対するパスワードを指定します 例外 SQLException JDBC URL 内で指定した HiRDB に接続できなかった場合に発生します 例 (HiRDB への接続 ) Connection con = DriverManager.getConnection("jdbc:hitachi:hirdb://DBID=22200,DBHOST= ", "APPUSER","STDDB"); Connection con = DriverManager.getConnection("jdbc:hitachi:hirdb:// DBID=@HIRDBENVGRP=C:\\envgrp\\clientdef","APPUSER","STDDB"); クライアント環境定義ファイル名 116

128 5 章 Java を使ったアプリケーションプログラムの開発 5.5 SQL によるデータベースの検索 HiRDB との接続後 (Connection オブジェクト取得後 ) SQL によりデータベースを操作するには 主に Statement インターフェースと ResultSet インターフェースを使用します 問合せ操作 (SELECT 文 ) を行う場合には Statement オブジェクトの executequery メソッド 更新系の操作 (UPDATE 文 DELETE 文 INSERT 文 ) を行う場合には Statement オブジェクトの executeupdate メソッドを使用します 以下に SELECT 文による検索処理の主な手順を示します SELECT 文による検索処理の主な手順 // 1 Statement オブジェクト生成 4 Statement stmt = con.createstatement(); 5 6 String query = " SELECT order_no,order_date,member_id" 7 + " FROM orders WHERE deliv_flag = " + flg; 8 9 // 2 SELECT 文の実行 10 ResultSet rs = stmt.executequery( query ); // 3 検索結果の取得 13 while(rs.next()){ 14 System.out.println(rs.getInt("order_no")); 15 System.out.println(rs.getDate("order_date")); 16 System.out.println(rs.getString("member_id")); 17 } // 4 Resultset オブジェクトのクローズ 20 if(rs!=null){ 21 rs.close(); 22 } 23 // 5 Statement オブジェクトのクローズ 24 if(stmt!=null){ 25 stmt.close(); 26 } Statement オブジェクトの生成 Connection インターフェースの createstatement メソッドにより Statement オブジェクトを生成します Statement オブジェクトの生成 Statement stmt = con.createstatement(); con: DriverManager.getConnection() で取得した Connection オブジェクト 例外 SQLException 117

129 5 章 Java を使ったアプリケーションプログラムの開発 2 SELECT 文の実行 Statement オブジェクトの executequery メソッドにより SELECT 文を実行し ResultSet オブジェクトを取得します SELECT 文の実行 ResultSet rs = stmt.executequery( query ); stmt: Statement オブジェクト 意味 query String 型実行する SELECT 文の文字列 (String オブジェクト ) を指定します 例外 SQLException 例 (SELECT 文の実行 ) ResultSet rs = stmt.executequery( "SELECT * FROM goods" ); 3 検索結果の取得 Statement インターフェースの executequery メソッドの戻り値は ResultSet オブジェクトであり この中には executequery メソッドの引数に指定した SELECT 文の実行結果が格納されています カーソル 検索結果 order_no order_date member_id メソッド next() close() getstring() getint() getdate() 図 ResultSet オブジェクトのイメージアプリケーションプログラムで ResultSet から検索結果を 1 行ずつ取得するには ResultSet に内蔵されたカーソルを用います カーソルは next メソッドが呼ばれるたびに 1 行下に移動し 行がない場合は false を返します ( 行がある場合は true を返します ) 118

130 5 章 Java を使ったアプリケーションプログラムの開発 カーソルの移動 boolean result = rs.next(); rs: ResultSet オブジェクト 例外 SQLException 例 while(rs.next()){ カーソルの現在行からデータ取得 } rs: ResultSet オブジェクト executequery メソッド実行直後 カーソルは検索結果の 1 行目のさらに1つ前を指し示しています カーソルを 1 行目に移動するには ResultSet オブジェクトの next メソッドを 1 回呼び出します したがって 例え検索結果が1 行であっても 必ず next メソッドを ( 少なくとも1 回は ) 呼び出す必要があります 2 回目の next メソッド呼び出しを行うとカーソルは検索結果の 2 行目に移動します 検索結果が 2 行のとき 3 回目の next メソッド呼び出しを行うと 戻り値として false が返ります executequery 呼び出し直後 order_no order_date member_id rs.next() 1 回目戻り値 :true rs.next() 2 回目戻り値 :true rs.next() 3 回目戻り値 :false order_no order_date member_id order_no order_date member_id order_no order_date member_id 現在行 現在行 図 ResultSet のカーソル移動 119

131 5 章 Java を使ったアプリケーションプログラムの開発 カーソルの現在行から各列のデータを取り出すには 取得対象のデータ型に対応する getxxx メソッドを使用します メソッドの Xxx の部分は取得対象のデータ型に依存します 例えば 取得対象データの型が INT 型の場合は getint メソッドを使い 取得対象データの型が DATE 型の場合は getdate メソッドを使い 取得対象データの型が CHAR 型の場合は getstring メソッドを使います また getxxx メソッドの引数には 列名 または列番号のいずれかを指定します メソッドの戻り値は カーソルの現在行における取得対象データとなります 例えば 以下の図において rs.getint("order_no") メソッド (rs.getint(1) メソッドでも同等 ) を呼び出せば メソッドの戻り値として int 型の7を取得します INT 型 DATE 型 CHAR 型 カーソル 検索結果 order_no order_date member_id 現在行 rs.getint("order_no") or rs.getint(1) rs.getstring("member_id") or rs.getstring(3) 戻り値 :int 型の 7 rs.getdate("order_date") or rs.getdate(2) 戻り値 :String 型の "5" 戻り値 :java.sql.date 型の " " 図 ResultSet からのデータ取得 カーソルの現在行から INT 型データの取得 int result = rs.getint(column); rs: ResultSet オブジェクト 意味 column String 型取得対象データの列名 ( 列ヘッダ名 ) を指定します 例外 SQLException 120

132 5 章 Java を使ったアプリケーションプログラムの開発 カーソルの現在行から INT 型データの取得 int count = rs.getint(number); rs: ResultSet オブジェクト 意味 number int 型取得対象データが SELECT 文の実行結果の左から何番目かを整数で指定します 例 while(rs.next()){ System.out.println(rs.getInt("order_no")); System.out.println(rs.getDate("order_date")); System.out.println(rs.getString("member_id")); } HiRDB のデータ型と Java データ型の対応 ResultSet オブジェクト内のカーソルの現在行から各列のデータを取り出すには 取得対象のデータ型に対応する getxxx メソッドを使用します 同様に PreparedStatement の引数に指定した SQL 文中の? パラメタにデータをセットするには HiRDB のデータ型に対応する setxxx メソッドを使用します HiRDB の主なデータ型と getxxx メソッド /setxxx メソッドの対応を次に示します 121

133 5 章 Java を使ったアプリケーションプログラムの開発 表 主な HiRDB のデータ型と Java データ型 分類 HiRDB のデータ型取得メソッド (getxxx) Java の型 (getxxx の戻り値 ) 数データ INTEGER getint int setint 文字データ SMALLINT getshort short setshort? ハ ラメタ設定メソット (setxxx) DECIMAL getbigdecimal java.math.bigdecimal setbigdecimal FLOAT getdouble double setdouble SMALLFLT getfloat float setfloat CHAR MCHAR VARCHAR MVARCHAR NCHAR getstring String setstring NVARCHAR 日付 時刻 DATE getdate java.sql.date setdate データ TIME gettime java.sql.time settime TIMESTAMP gettimestamp java.sql.timestamp settimestamp バイナリデータ BINARY getbinarystream java.sql.longvarbinary setbinarystream 長大データ BLOB 4 ResultSet オブジェクトのクローズ ResultSet オブジェクトのクローズ rs.close(); rs: ResultSet オブジェクト 例外 SQLException 5 Statement オブジェクトのクローズ Statement オブジェクトのクローズ stmt.close(); stmt: Statement オブジェクト 例外 SQLException 122

134 5 章 Java を使ったアプリケーションプログラムの開発 5.6 SQL によるデータベースの更新 / 削除 / 追加 更新系の操作 (UPDATE 文 DELETE 文 INSERT 文 ) を行う場合には Statement オブジェクトの executeupdate メソッドを使用します executeupdate メソッドは 引数に指定した UPDATE 文 /DELETE 文 /INSERT 文を実行し 戻り値として更新 / 削除 / 追加された行数を返します UPDATE/DELETE/INSERT 文の実行 int count = stmt.executeupdate( sqlstr ); stmt: Statement オブジェクト 意味 sqlstr String 型実行する UPDATE/DELETE/INSERT 文の文字列 (String オブジェクト ) を指定します 例外 SQLException 123

135 5 章 Java を使ったアプリケーションプログラムの開発 5.7 トランザクションの制御 アプリケーションプログラムが HiRDB に接続すると デフォルトでオートコミットが有効になります オートコミットモードでは 1つの SQL 文が完了すると自動的にトランザクションがコミットされます そのため 複数の SQL 文を 1 トランザクションにするには トランザクションのオートコミットを無効にする必要があります オートコミットを無効にした場合は 以下に示すトランザクション終了 ( コミット / ロールバック ) メソッドの呼び出しまでに実行した複数の SQL 文が 1 トランザクションになります オートコミットの無効化 con.setautocommit(false); con: Connection オブジェクト 意味このメソッド呼出し後 Connection オブジェクトの commit メソッドが呼び出されるまでに実行された SQL 文が 1 トランザクションとなります 例外 SQLException トランザクションのコミット con.commit(); con: Connection オブジェクト 意味トランザクション内で行った変更をすべて有効とし トランザクションを終了します 例外 SQLException データベースにエラーが発生したためコミットできない場合や オートコミットモードの場合等にスローされます トランザクションのロールバック con.rollback(); con: Connection オブジェクト 意味トランザクション内で行った変更をすべて取り消し トランザクションを終了します トランザクションが終了すると 保持している全てのロックを解放します 124

136 5 章 Java を使ったアプリケーションプログラムの開発 例外 SQLException データベースにエラーが発生しロールバックできない場合や オートコミットモードの場合等にスローされます 一般的に rollback メソッドは executeupdate メソッド (UPDATE/DELETE/INSERT) を含むトランザクション実行中の例外処理中 (catch 句 ) で呼び出します rollback メソッド自体が例外オブジェクト SQLException を発生する可能性があるため catch 句での rollback 呼び出しは try~catch ブロック内に記述する必要があります トランザクションの制御 1 トランサ クション try { 32 // 接続時 オートコミットモードが設定される 33 Connection con = DriverManager.getConnection(url, userid, passwd); 34 // オートコミットモードを無効にする 35 con.setautocommit(false) ; 36 // ステートメントの作成 37 PreparedStatement stmt1 = con.preparestatement( 38 "UPDATE Account set Balance = Balance where AccountID= 100" ); 39 PreparedStatement stmt2 = con.preparestatement( 40 "UPDATE Account set Balance = Balance where AccountID= 205" ); 41 try { 42 // 複数の SQL 文の実行 43 int cnt1 = stmt1.executeupdate( ); 44 // トランザクションがオートコミットモードの場合はここでコミットされる 45 int cnt2 = stmt2.executeupdate( ); 46 // トランザクションのコミット 47 con.commit() ; 48 } catch(sqlexception e) { 49 try { // ロールバックでの例外に備えて try~catch 50 // トランザクションのロールバック 51 con.rollback(); 52 } catch(sqlexception re) { 53 System.err.println(" ロールバックに失敗しました \n" 54 + re.getmessage()); 55 System.err.println("SQLSTATE=" + re.getsqlstate()); 56 } 57 } 58 } catch(sqlexception e) { 59 System.err.println("SQL 文の実行に失敗しました \n" + e.getmessage()); 60 System.err.println("SQLSTATE=" + e.getsqlstate()); 61 }

137 5 章 Java を使ったアプリケーションプログラムの開発 5.8 HiRDB からの切り離し アプリケーションプログラムを HiRDB から切り離すには Connection オブジェクトの close メソッド を呼び出します HiRDB からの切り離し con.close(); con: DriverManager.getConnection() で取得した Connection オブジェクト 例外 SQLException 正常に HiRDB から切り離せなかった場合に発生します アプリケーションプログラムが HiRDB に接続していないときに Connection オブジェクトの close メソッドを呼び出すと SQLException が発生します したがって close メソッドを呼び出す前に Connection オブジェクトの参照変数が null でないことと アプリケーションプログラムが HiRDB に接続中であることを確認する必要があります 接続中であることを確認するには 次に示す (Connection オブジェクトの )isclosed メソッドを使います このメソッドは アプリケーションプログラムが HiRDB に接続中でなければ true を 接続中であれば false を返します 接続状態の確認 boolean stat = con.isclosed(); con: DriverManager.getConnection() で取得した Connection オブジェクト 例外 SQLException アプリケーションプログラムが HiRDB に接続していない場合に呼び出すと発生します HiRDB との接続は データベースに対する処理が終わり次第 確実に切り離す必要があります 切り離しができていないと HiRDB に高い負荷がかかったり 新規の接続が確立できなくなったりすることがあります 次の例のように 例外発生時にも確実にアプリケーションプログラムを HiRDB から切り離すために finary 句に Connection オブジェクトの close メソッド呼び出しを記述します 126

138 5 章 Java を使ったアプリケーションプログラムの開発 例 (HiRDB からの切り離し ) Connection = null; 4 5 try{ 6 // HiRDB 接続 7 con = DriverManager.getConnection(DBURL, DBUSER, DBPASS); 8 9 // データベースに対する処理 }catch(sqlexception ex1){ 14 System.err.println(ex1.getMessage()); 15 }finally{ 16 try{ //finally 句内の例外に備えて 17 if(con!= null &&!con.isclosed()){ 18 // HiRDB 切り離し 19 con.close(); 20 } 21 }catch(sqlexception ex2){ 22 System.err.println(ex2.getMessage()); 23 } 24 }

139 5 章 Java を使ったアプリケーションプログラムの開発 5.9 例外処理 JDBC API(java.sql パッケージ ) の各クラス / インターフェースにおけるメソッドは エ ラーが発生した場合 SQLException 例外オブジェクトをスローします 例えば HiRDB への接続 切り離し SQL による操作等は全て SQLException をスローする可能性があ ります したがって これらの操作は 下図のように SQLException をキャッチ可能な try~catch ブロック内に記述する必要があります 同様に Class クラスの forname メソッドは ClassNotFoundException 例外オブジェ クトをスローする可能性があるため ClassNotFoundException をキャッチ可能な try ~catch ブロック内に記述する必要があります 例外処理 try { 4 5 // JDBC ドライバの登録 6 Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver"); 例外発生 7 8 // HiRDB へ接続 9 con = DriverManager.getConnection(url, userid, passwd); 10 ClassNotFoundException 11 // ステートメントオブジェクト生成 12 Statement stmt = con.createstatement(); // SQL 実行 15 ResultSet rs = stmt.executequery( 16 "SELECT goods_name,goods_price FROM goods"); 例外発生 // 検索結果の取得 19 while(rs.next()){ SQLException 20 System.out.println(rs.getString("goods_name")); 21 System.out.println(rs.getInt("goods_price")); 22 } // Resultset オブジェクトのクローズ 25 if(rs!=null){ 26 rs.close(); 27 } 28 // Statement オブジェクトのクローズ 29 if(stmt!=null){ 30 stmt.close(); 31 } } catch (ClassNotFoundException ce) { 34 System.err.println(ce.getMessage()); 35 } catch (SQLException e) { 36 System.err.println(e.getMessage()); 37 System.err.println("SQLSTATE=" + e.getsqlstate()); 38 }

140 5 章 Java を使ったアプリケーションプログラムの開発 SQLException に対する catch 句では 次に示すメソッドを使用してエラー情報を出力し ます 表 SQLException の主なメソッド メソッド 戻り値 説明 getmessage() String 詳細情報の文字列を取得します printstacktrace() void SQLException と そのバックトレースを標準エラー出力します getsqlstate() STring SQLState を取得します geterrorcode() int 製品 (HiRDB) 固有の整数型エラーコードを取得します getnextexception() SQLException 例外がネストしている場合に 次の例外を取得します 注意事項例外 SQLException を無視しないで下さい 例外 SQLException を無視して 業務処理 (SQL) を続行すると トランザクションの整合性が失われることがあります 無視する必要がある場合は SQLSTATE のコードで無視する対象を限定して下さい 例えば INSERT 時の重複エラーを無視したい場合は 重複エラーの SQLSTATE コードだけを無視して下さい 例 ( 例外 SQLException を限定的に無視する場合 ) 1 2 try { 3 // 複数の INSERT 文実行 4 int cnt1 = stmt1.executeupdate( ); 5 int cnt2 = stmt2.executeupdate( ); 6 : 7 // トランザクションのコミット 8 con.commit() ; 9 } catch(sqlexception se1) { 10 if ( se1.getsqlstate().equals("23505")) // 重複エラーの SQLSTATE だけを 11 { // 限定して無視する 12 } else { // 他の SQLSTATE は例外を処理する 13 System.err.println("SQL 文の実行に失敗しました \n" + 14 se1.getmessage()); 15 System.err.println("SQLSTATE=" + se1.getsqlstate()); 16 se1.printstacktrace(); 17 try { // ロールバック中の例外に備えて try~catch 18 // トランザクションのロールバック 19 con.rollback(); 20 } catch(sqlexception re) { 21 System.err.println(" ロールバックに失敗しました \n" 22 + re.getmessage()); 23 System.err.println("SQLSTATE=" + re.getsqlstate()); 24 re.printstacktrace(); 25 } 26 } 27 }

141 5 章 Java を使ったアプリケーションプログラムの開発 SQLSTATE について SQLSTATE は SQL の実行後に HiRDB から返されるリターンコードの一つです コードは 5 けたあり, 先頭 2 けたのクラス 及び下 3 けたのサブクラスで構成しています クライアント環境変数 PDSTANDARDSQLSTATE に YES を設定 又は HiRDB サーバのシステム共通定義 pd_standard_sqlstate に Y を設定すると SQLSTATE のコードが詳細化されます SQLSTATE のコードについては マニュアル HiRDB Version 8 メッセージ を参照してください 130

142 5 章 Java を使ったアプリケーションプログラムの開発 5.10? パラメタと PreparedStatement (1)PreparedStatement の利点 Statement インターフェースは executequery/executeupdate メソッドの呼び出し時に SQL 文をテキストとして HiRDB に送信し 解析 および実行要求をします 一方 PreparedStatement インターフェースは オブジェクト生成時に SQL 文の解析要求を行います 以降の executequery/executeupdate メソッド呼び出し時は 解析済みの SQL 文に対する実行要求のみを行います そのため 同一の SQL を繰り返し実行する場合 PreparedStatement を用いるとパフォーマンスの向上が見込めます また? パラメタを使うこともできます (2)PreparedStatement を用いた SQL の実行手順 PreparedStatement を使用して SQL を実行するには 次の手順で行います 1 PreparedStatement オブジェクトの生成 2 入力パラメータに値の設定 3 executequery メソッド ( または executeupdate メソッド ) による SQL の実行 << 1PreparedStatement オブジェクトの生成例 >> PreparedStatement pstmt = con.preparestatement( "INSERT INTO details(order_no,goods_code,qty) VALUES(?,?,?)"); con: Connection オブジェクト PreparedStatement オブジェクトを生成するには Connection オブジェクトの preparestatement メソッドを呼び出します このとき preparestatement メソッドの引数に指定した SQL 文が解析されます また SQL 文に対する入力パラメータは? によって表現します 131

143 5 章 Java を使ったアプリケーションプログラムの開発 << 2? パラメータに値の設定例 pstmt.setint(1, 10); pstmt.setstring(2, "B10013"); pstmt.setint(3, 2); pstmt: PrepareStatement オブジェクト? パラメータに値を設定するには 設定対象となる表の列のデータ型に対応したsetXxx メソッドを用います 例えば 対象となる列のデータ型がINT 型の場合はsetIntメソッドを使い 対象となる列のデータ型がCHAR 型の場合はsetStringメソッドを使います 39 また SQL 文中の左から1 番目の? パラメタに値を設定するには setxxx メソッドの第一引数に 1 を 左から 2 番目の? パラメタに値を設定するには setxxx メソッドの第一引数に 2 を指定します ( 以下同様 ) 第一パラメタ setint(1, ) PreparedStatement pstmt = con.preparestatement( "INSERT INTO details(order_no,goods_code,qty) VALUES(?,?,?)"); 第二パラメタ setstring(2, ) INT 型 CHAR 型 INT 型 第三パラメタ setint(3, ) << 3 解析済み SQL の実行例 >> ResultSet rs = pstmt.executequery(); SELECT 文の実行 int count = pstmt.executeupdate(); UPDATE/DELETE/INSERT 文の実行 pstmt: PreparedStatement オブジェクト PreparedStatement を使用して解析済みの SQL を実行するには executequery メソッド ( または executeupdate メソッド ) を用います このとき SQL は既に解析済みであるため executequery/executeupdate メソッドに引数を指定してはいけません 39 HiRDB のデータ型と setxxx メソッドの対応については 5.5 SQL によるデータベースの検索 の HiRDB のデータ型と Java データ型の対応 で説明しています 132

144 5 章 Java を使ったアプリケーションプログラムの開発 5.11 アプリケーションプログラムの例 単価が実行時に指定した引数以上の商品情報を P.36 で定義した GOODS 表から検索し 表示するアプリケーションプログラムの例を次に示します 1 import java.sql.*; 2 import java.util.*; 3 import java.io.*; 4 5 /** 6 * 日立インフォメーションアカデミー 9 * 実行時引数以上の単価の商品情報を表示するアプリケーションプログラム 10 * 11 */ public class ListGoods { 14 //HiRDB JDBC Driver 15 static private String DBCLASS = "JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver" ; 16 //HiRDB JDBC URL 17 static private String DBURL = "jdbc:hitachi:hirdb://dbid=22200,dbhost=localhost" ; 18 static private String DBUSER = "\"hiuser\"" ; // ユーザ名 19 static private String DBPASS = "\"hipass\"" ; // パスワード // メインメソッド 22 public static void main(string args[]){ 23 String query=null; 24 PreparedStatement stmt=null; 25 ResultSet rs=null; 26 Connection con=null; 27 if(args.length==1){ 28 try{ 29 // 実行時引数を String 型から int 型へ変換 30 int price = Integer.parseInt(args[0]); 31 // HiRDB JDBC Driver の登録 32 Class.forName(DBCLASS); 33 // HiRDB への接続 34 con = DriverManager.getConnection(DBURL, DBUSER, DBPASS); 35 // SQL テキスト 36 query = "SELECT goods_code,goods_price,goods_name " + 37 "FROM goods WHERE goods_price >=? ORDER BY goods_price"; 38 // PreparedStatement オブジェクトの生成 39 stmt = con.preparestatement(query); 40 // PreparedStatement の第一パラメータに price をセット 41 stmt.setint(1,price); 42 // SQL ステートメントの実行 43 rs = stmt.executequery(); 44 if(rs.next()){// 検索結果が1 件以上あった場合 45 // 見出しの出力 46 System.out.println("GOODS_CODE" + "\t" + "GOODS_PRICE" + "\t" + "GOODS_NAME"); 47 System.out.println(" " + "\t" + " " + "\t" + 48 " "); 49 //1 件目の出力 50 System.out.print(rs.getString("GOODS_CODE") + "\t"); 51 System.out.print(rs.getInt("GOODS_PRICE") + "\t\t"); 52 System.out.println(rs.getString("GOODS_NAME") ); 53 //2 件目以降の出力 54 while(rs.next()){ 55 System.out.print(rs.getString("GOODS_CODE") + "\t"); 56 System.out.print(rs.getInt("GOODS_PRICE") + "\t\t"); 57 System.out.println(rs.getString("GOODS_NAME") ); 58 } 59 }else{// 検索結果が1 件もなかった場合 133

145 5 章 Java を使ったアプリケーションプログラムの開発 60 System.out.println(" 単価が " + price + " 円以上の商品情報は存在しません "); 61 } 62 }catch(numberformatexception ex1){ 63 System.err.println(" 無効な引数が指定されました \n 処理を中断します "); 64 }catch (ClassNotFoundException ex2) { 65 System.err.println(" ドライバの登録エラーです \n"+ ex2.getmessage()); 66 }catch (SQLException ex3) { 67 System.err.println(" データベースエラーが発生しました \n" + ex3.getmessage()); 68 System.err.println("SQLSTATE=" + ex3.getsqlstate()); 69 }finally{ 70 try{ 71 // Resultset オブジェクトのクローズ 72 if(rs!= null){ 73 rs.close(); 74 } 75 // Statement オブジェクトのクローズ 76 if(stmt!= null){ 77 stmt.close(); 78 } 79 // HiRDB 切り離し 80 if(con!= null &&!con.isclosed()){ 81 con.close(); 82 } 83 }catch(sqlexception ex4){ 84 System.err.println(" オブジェクトのクローズに失敗しました \n" + ex4.getmessage()); 85 } 86 } 87 }else{ 88 System.err.println(" 引数が指定されていないか 2つ以上指定されました " 89 + "\n 処理を中断します "); 90 } 91 } } 94 < 実行イメージ > 134

146 5 章 Java を使ったアプリケーションプログラムの開発 5.12 環境設定 環境変数 CLASSPATH の確認 体験版 HiRDBをインストールすると 通常 環境変数 CLASSPATHに下記のjarファイル名が追加 40 されます C:\Program Files\HiRDB_TR\SRV\CLIENT\UTL\pdjdbc2.jar CLASSPATH にこれらのファイル名が設定されているかを確認し 設定されていない場合は CLASSPATH にこれらのファイル名を追加してください CLASSPATH の設定を確認 ( または追加 ) するには デスクトップ上の マイコンピュータ を右クリックし メニューから [ プロパティ ] を選択します 以下のような システムのプロパティ が表示されるので [ 詳細 ] タブを選択し [ 環境変数 ] ボタンをクリックします [ 環境変数 ] 画面のシステム環境変数として既存の CLASSPATH があれば [ 編集 ] ボタンを なければ [ 新規 ] ボタンをクリックし CLASSPATH に上記の jar ファイル名 ( 絶対パス ) を追加します < 値例 >.;C:\Program Files\HiRDB_TR\SRV\CLIENT\UTL\pdjdbc2.jar 40 JDBC により HiRDB にアクセスする Java アプリケーションプログラムを開発 / 実行するマシンでは 環境変数 CLASSPATH に jar ファイル名を追加する必要があります 135

147 5 章 Java を使ったアプリケーションプログラムの開発 ucosminexus Developer Professional と Eclipse の環境設定 Java アプリケーションプログラムの開発環境に ucosminexus Developer Professional と Eclipse を 使用する場合のインストールと環境設定について説明します (1) ucosminexus Developer Professional のインストール ucosminexus Developer Professional をインストールする前に次に示すことを確認してください 構成ソフトウェアのすべてのプロセスが実行中でないことを確認してください 実行中の場合は 構成ソフトウェアのすべてのプロセスを停止してから インストールを実行してください インストールを実行するには Administrators 権限が必要です Administrators 権限があるかを確認してください ucosminexus Developer Professional のインストール手順を次に示します < 手順 > 1.uCosminexus Developer Professional のインストール CD-ROM を CD-ROM ドライブにセットし <CD-ROM>: Products HCD_INST.EXE を起動します 2. 総合インストーラのダイアログが表示されるので インストールするソフトウェアを選択します ucosminexus Developer Professional を選択し [ インストール実行 ] ボタンをクリックします 3. 画面の指示に従ってインストールを行ってください [ インストール先の選択 ] ダイアログでインストールパスを指定しますが デフォルト (C: Program Files Hitachi Cosminexus) のままでインストールを実行してください 4. リブートの確認ダイアログが表示されたら [ はい ] ボタンをクリックします (2) Eclipse のインストール (Eclipse を使用する場合 ) アプリケーションの開発 実行時に使用する Eclipse のインストール手順を次に示します < 手順 > 1.Eclipse をインストールするインストールディレクトリ および Eclipse のインストール時に一時的に使用する作業用ディレクトリを作成します 2.Eclipse のダウンロードサイトから次に示す Eclipse のファイルをダウンロードします 136

148 5 章 Java を使ったアプリケーションプログラムの開発 ファイル名 URL Eclipse Platform /R /eclipse-platform win32.zip Eclipse Platform NLpack1 /L-3.2.1_Language_Packs /NLpack1-eclipse-platform win 32.zip Eclipse Java Development Tools /R /eclipse-JDT zip Eclipse Java Development Tools NLpack1 /L-3.2.1_Language_Packs /NLpack1-eclipse-JDT zip 注 URL は 2007 年 6 月現在の URL であり 変更になることがあります 3. 次のコマンドを実行して ダウンロードした Eclipse のファイルを手順 1 で作成した作業用ディレクトリで解凍します C: Program Files Hitachi Cosminexus jdk bin jar.exe" xvf < 展開するファイル名 > 次に示す順序で解凍してください Eclipse Platform Eclipse Platform NLpack1 Eclipse Java Development Tools Eclipse Java Development Tools NLpack1 4. 解凍作業が完了すると 作業用ディレクトリ下に eclipse フォルダが作成されます eclipse フォルダ下の全ファイルを 手順 1 で作成したインストールディレクトリ下にコピーします 5. 作業用ディレクトリを削除します (3) 環境変数の設定次に示すシステム環境変数を設定してください 表 設定する環境変数 環境変数設定値 PATH 1 < インストールディレクトリ 2 > jdk bin 注 1 既にパス名が設定されている場合は 先頭に追加してください 注 2 ucosminexus Developer Professional のインストールディレクトリのことです 環境変数は コントロールパネルで [ システム ] を選択し [ 詳細設定 ] タブ中の [ 環境変数 ] ボタンをクリックして設定します 環境変数に設定済みの定義内容がある場合は ; ( セミコロン ) で区切って設定してください 環境変数が正しく設定されていないとエラーが発生します 137

149 5 章 Java を使ったアプリケーションプログラムの開発 (4) JDK の確認 (Eclipse を使用する場合 ) ucosminexus Developer Professional で提供されている JDK が登録されているかどうかを確認します 確認手順を次に示します < 手順 > 1.Eclipse のメニューから [ ウインドウ ]-[ 設定 ] を選択します [ 設定 ] ダイアログが表示されます 2. 左ペインで [Java]-[ インストール済みの JRE] を選択します 右ペインに [ インストール済みの JRE] ページが表示されます 3. インストール済みの JRE が ucosminexus Developer Professional で提供されている JDK かどうかを確認します ロケーションの欄に次のパスが表示されているかどうかで確認します <ucosminexus Developer Professional のインストールディレクトリ > jdk パスが表示されていない場合: [ 追加 ] ボタンをクリックして 上記のパスを設定してください パスの設定後 名前の欄のチェックボックスをチェックしてください パスが表示されている場合: 名前の欄のチェックボックスにチェックが入っているかどうかを確認してください チェックが入っていない場合はチェックを入れてください 特に 複数の JDK がインストールされている場合は <ucosminexus Developer Professional のインストールディレクトリ > jdk にチェックが入っていないことがあるので注意してください 138

150 5 章 Java を使ったアプリケーションプログラムの開発 5.13 Java ソースファイルの作成とコンパイル Eclipse を使用する場合 次に示す手順で Java ソースファイルを作成します < 手順 > 1.Eclipse のメニューから [ ファイル ]-[ 新規 ]-[ プロジェクト ] を選択して Java プロジェクトを作成します 2.Eclipse のメニューから [ ファイル ]-[ 新規 ]-[ クラス ] を選択してください Java ソースの雛型が生成されているので それを基に Java ソースファイルを作成します なお Java ソースファイルを保存する度にコンパイルが実行されるため コンパイル作業を意識する必要はありません Eclipse を使用しない場合 テキストエディタなどで Java ソースファイルを作成し コンパイルを実行します 作成した Java ソースファイルをコンパイルするには コマンドプロンプトを開き Java ソースファイルの格納されたディレクトリで以下を実行します Java ソースファイルの拡張子は.java である必要があります また JDK(1.4 以降 ) が正しくセットアップされていることが前提となります javac ソースファイル名.java コンパイルが完了すると クラスファイルが作成されます クラスファイルのファイル名はソースファイル名.class となります 例えば Aaa.java をコンパイルすると Aaa.class ファイルが作成されます メインメソッドを持つクラスファイル ( アプリケーション ) は 以下のように実行します java 拡張子を除いたクラスファイル名 [ 引数 [, ] ] 139

151 5 章 Java を使ったアプリケーションプログラムの開発 空 白 140

152 6 章 SQL のチューニング 6 SQL のチューニング ここでは HiRDB SQL Tuning Advisor を使用した SQL のチューニング方 法について説明します 6.1 SQL チューニングの流れ 6.2 アクセスパス 6.3 チューニング情報の収集 6.4 SQL トレース情報の解析 6.5 アクセスパス情報の解析 6.6 SQL チューニング 141

153 6 章 SQL のチューニング 6.1 SQL チューニングの流れ インデックスが適切に定義されていなかったり アクセスパス ( アクセスパスについては 6.2 で説明 ) が適切でない SQL があったりすると ディスク I/O の多発や CPU 利用率の低下が起こり アプリケーションプログラムのパフォーマンスに問題が発生します これらの問題および原因を見つけ アプリケーションプログラムのパフォーマンスを改善する作業を SQL チューニングといいます データベース構築から SQL チューニングまでの流れを次に示します 1 データベースを構築する 2 アプリケーションプログラムを開発する 3 適切な列にインデックスを定義する インデックスを定義するときの考慮点については 3.5 と 3.6 で説明しています 4 チューニング情報を取得する 5SQL トレース情報を解析する 手順 2で開発したアプリケーションプログラムをシミュレーションし チューニング情報を取得します 詳細については 6.3 で説明します HiRDB SQL Tuning Advisor を使用してチューニング情報を解析します 詳細については 6.4 と 6.5 で説明します 6 アクセスパス情報を解析する 7SQL チューニングを行う 手順 5と6の結果から SQL チューニングを実施します 詳細については 6.6 で説明します 手順 4~7 を 問題がなくなるまで繰り返します 図 データベース構築から SQL チューニングまでの流れ アクセスパスに問題がなくなったら SQL チューニングは完了です ただし SQL チューニングが完了すれば 必ずしも性能が良くなる訳ではありません 性能が良くならない場合は グローバルバッファのチューニングを実施してヒット率の向上を図るなど SQL チューニング以外のチューニングも必要になります 142

154 6 章 SQL のチューニング 6.2 アクセスパス SQLチューニングを行う前に アクセスパスについて理解しておく必要があります アクセスパスとは HiRDBがSQLを実行するときの処理手順のことです アクセスパスは インデックスの定義やSQLの構文などをもとにHiRDBが決定 41 しています アプリケーションプログラム開発者は インデックスの定義を変更したり SQL の構文を変更したりすることでアクセスパスを変更 ( チューニング ) することができます アクセスパスは アプリケーションプログラムのパフォーマンスに大きな影響を与えるため 仕組みを正しく理解しておく必要があります ここでは スキャン方法における代表的なアクセスパスについて説明します 結合方法における代表的なアクセスパスについては 表の結合方式 で説明しています テーブルスキャン <TABLE SCAN> このキーワードは 6.6 以降のチューニング情報の説明にでてきます 以降のスキャン方法についても同様にキーワードがあります テーブルスキャンとは 検索条件の内容にかかわらず 検索対象表の全行をシーケンシャルにアクセスする方法です したがって いずれの検索時もアクセス速度は一定となります ただし 表の行数に対し検索対象となる行の割合が低い場合 アクセス効率は悪くなります select STU_ID,STU_NAME,CORP_ID from STUDENTS STUDENTS STU_ID STU_NAME CORP_ID 1 Taro Yamada A001 2 Jiro Ito K003 3 Hanae Suzuki H153 4 Yoshihiro Sato A045 5 Makoto Mikami S009 6 Kaori Yamamoto M004 7 Jennifer Smith W006 8 Kenji Arai Y041 9 Emiko Watanabe R Kohei Makihara S Shinichi Karasawa K003 図 テーブルスキャン 41 一つの SQL 文に対する SQL 処理手順は 複数存在します 検索時を例に考えると 表全体を読み込む方法やインデックスを利用する方法など 様々な経路が存在します HiRDB は SQL の構文などをもとに 最も効率的であると思われるアクセスパスを選択し 決定しています 143

155 6 章 SQL のチューニング インデックススキャン インデックススキャンには 単一列インデックススキャン 複数列インデックススキャン および複数のインデックススキャンの 3 種類があります (1) 単一列インデックススキャン <INDEX SCAN> 単一列インデックススキャンとは 単一列インデックスを 1 つ利用してインデックス上で絞込みを行い データにアクセスする方法です select STU_ID,STU_NAME from STUDENTS where STU_ID=215 STUDENTS STU_ID STU_NAME CORP_ID 1 Taro Yamada A001 2 Jiro Ito K003 3 Hanae Suzuki H153 4 Yoshihiro Sato A045 5 Makoto Mikami S009 6 Kaori Yamamoto M004 7 Jennifer Smith W006 8 Kenji Arai Y041 9 Emiko Watanabe R Yoko Okamoto W STU_ID 列上のインデックス 217 図 単一列インデックススキャン 144

156 6 章 SQL のチューニング (2) 複数列インデックススキャン<MULTI COLUMNS INDEXSCAN> 複数列インデックススキャンとは 複数列インデックスを 1 つ使用して インデックス上の全ての構成列 または一部の構成列をもとにインデックス上で絞込みを行い データにアクセスする方法です select SEMINAR_ID,START_DATE,SUBJECT_ID from SEMINARS where SUBJECT_ID= DB01 and INST_ID=9005 SEMINARS SEMINAR_ID START_DATE END_DATE SUBJECT_ID INST_ID 05K DB K AP K UN K MG K NW K IT K DB K AP SUBJECT_ID INST_ID DB DB DB DB DB SUBJECT_ID 列および INST_ID 列に作成したインデックス 図 複数列インデックススキャン 145

157 6 章 SQL のチューニング (3) 複数のインデックススキャン<AND PLURAL INDEXES SCAN/OR PLURAL INDEXES SCAN> SQL 文の条件節内で複数のインデックスを利用する場合は 内部的に以下のような操作が行われます まずインデックスのキー値をもとに 各々の条件に合致する行を指し示すポインタ ( 行番号 ) を 作業表に格納します その後 作業表間で条件に即した集合演算を行い 最終的な検索対象行を特定します したがって 作業表作成時および演算時のオーバヘッドが発生します select INST_ID,INST_NAME from INSTRUCTORS where HIRE_YEAR = 2004 or INST_GRADE = M-1 INSTRUCTORS INST_ID INST_NAME HIRE_YEAR INST_GRADE 9001 Mariko Sasaki 1988 M Yuji Miyamoto 1990 S INST_ID Keiko INST_NAME Kikuchi HIRE_YEAR 2002 INST_GRADE P Mariko Hiroshi Sasaki Sato M-1 A Eri Yuji Yokoyama Miyamoto M-1 P Tsukasa Keiko Kikuchi Goto M-2 A Hiroshi Sato 1990 M Masayuki Eri Yokoyama Eguchi P-1 S Tsukasa Goto 1992 M Masayuki Eguchi 2004 P-2 HIRE_YEAR 列上のインデックス 条件 1 作業表 行番号 OR 集合演算 和集合 作業表 行番号 条件 2 作業表 行番号 INST_GRADE 列上のインデックス 146 図 複数のインデックスを使用した検索

158 ースキャン6 章 SQL のチューニング キースキャン <KEY SCAN/MULTI COLUMNS KEY SCAN> キースキャンとは インデックスのリーフのキー値から検索結果を得る方法です 表データにアクセスする必要がないため ヒット率が高い検索に対しても 非常に効果的です インデックス上で全てのアクセスを済ませることができる場合は キースキャンが行われます 表データにアクセスせず インデックスのキー値から検索結果を得る 101 表データ 102 アクセスしない 103 図 キースキャン概要 例 select 句内に指定する検索対象列 CORP_ID にインデックスを作成する select CORP_ID from STUDENTS STUDENTS STU_ID STU_NAME CORP_ID Taro Yamada A Jiro Ito K Hanae Suzuki H153 CORP_ID 列上のインデックス キA001 A002 A003 B001 B001 B

159 6 章 SQL のチューニング 6.3 チューニング情報の収集 SQL チューニングを行う前に 次に示すチューニング情報を収集する必要があります 表 SQL チューニングを行うために収集するチューニング情報 収集するチューニング情報 主な出力情報 チューニング情報の利用目的 SQL トレース情報 SQL の実行時間 パフォーマンス低下の原因となっている SQL を特定するためにこのチューニング情報を収集します アクセスパス情報 アクセスパス情報 SQL のアクセスパスに問題がないか確認するためにこのチューニング情報を収集します これらのチューニング情報を収集するには 次に示す環境設定を行う必要があります クライアント環境定義のパラメータの設定 チューニング情報を収集するには 次に示すクライアント環境定義のパラメータを HiRDB.ini ファイルなどに指定する必要があります PDSQLTRACE=0 このパラメータには SQL トレース情報を出力する SQL トレースファイルの最大値を指定します 0 を指定すると ファイルの最大値は Windows が作成可能なファイルサイズになります PDCLTPATH=C:\WORK\TRACESQL SQL トレースファイルを格納するフォルダを指定します 任意のフォルダを指定できます 本書では C:\WORK\TRACESQL を指定したとして説明します PDSQLEXECTIME=YES SQL トレース情報に SQL 実行時間を出力する場合に YES を指定します PDTAAPINFPATH=C:\WORK\TRACESQL アクセスパス情報を出力するアクセスパス情報ファイルを格納するフォルダを指定します 任意のフォルダを指定できます 本書では C:\WORK\TRACESQL を指定したとして説明します PDUAPREPLVL=a アクセスパスの解析に必要な中間結果情報を出力するために a を指定します HiRDB SQL Tuning Advisor の環境設定 HiRDB SQL Tuning Advisor で次に示す環境設定を行います 148

160 6 章 SQL のチューニング (1) 解析対象ファイルの設定解析対象とする SQL トレースファイルとアクセスパス情報ファイルを指定します 手順を次に示します < 手順 > 1. [ スタート ]-[ プログラム ]-[HiRDB SQL Tuning Advisor]-[ HiRDB SQL Tuning Advisor] を選択し HiRDB SQL Tuning Advisor を起動します 2. [ オプション ]-[ 対象ファイル指定 ] を選択します 3. 対象ファイルの指定画面が表示されます 下記のラジオボタンがオンになっていることを確認してください このラジオボタンがオンの場合 PDCLTPATH に指定したフォルダ下の SQL トレー スファイルを解析対象とします 4. [ アクセスパス ] タブをクリックし 下記の指定を行います 1 解析対象のアクセスパス情報ファイル (PDTAAPINFPATH に指定したフォルダ \pdtapinf*) を指定します 2 クリックします 3 クリックします 149

161 6 章 SQL のチューニング (2) アクセスパスの環境設定アクセスパスの環境設定をします 手順を次に示します < 手順 > 1. [ オプション ]-[ 環境設定 ] を選択します 2. 環境設定画面が表示されます [ アクセスパス ] タブをクリックしてください 3. [ 設定 ] ボタンをクリックして 下記のように情報を指定してください 任意の名称を指定します STDDB を指定します 情報を指定した後にクリックします 150

162 6 章 SQL のチューニング 6.4 SQL トレース情報の解析 SQL トレース情報を解析して SQL の実行時間が想定していたよりも長い SQL があるか確認します SQL トレース情報の解析は HiRDB SQL Tuning Advisor を使用して行います 手順を次に示します < 手順 > 1. [ スタート ]-[ プログラム ]-[HiRDB SQL Tuning Advisor]-[ HiRDB SQL Tuning Advisor] を選択し HiRDB SQL Tuning Advisor を起動します 2. ツールバーの SQL トレースから [SQL 一覧 ] を選択します 3. SQL トレース画面が表示されます パフォーマンス低下の原因となっている SQL を特定してください 対象の行をダブルクリックすると オペレーション単位の情報が表示されるSQL トレース詳細画面が表示されます SQL-NO:SQL 通番 FROM:SQL 実行開始時刻 TO:SQL 実行終了時刻 (CONNECT-NO): コネクション番号 CLT-PID:UAPのプロセス番号 CLT-TID:UAPのスレッド番号 (SEC-NO): セクション番号 SQL 発行回数 : 実行したオペレーションの数 合計実行時間 : 各オペレーションの実行時間の合計 最大実行時間 : 各オペレーションの実行時間の最大値 最小実行時間 : 各オペレーションの実行時間の最小値 SQL 実行時間 : オペレーションコードSET AUXI AUI2 OPN2での実行時間 経過時間 :FROMからTOまでの時間 SQL: 実行したSQL 文 151

163 6 章 SQL のチューニング 4. SQL トレース詳細画面が表示されます 各オペレーションの情報を確認してください 開始から終了までの時間 開始時間 終了時間 前の要求終了から開始までの時間 CNCT-NO: コネクション番号 NO:SQLカウンタ OP-CODE:SQLに対応するオペレーションコード SEC-NO: セクション番号 SQL-CODE:SQLCODE WARN:SQLWARN0からSQLWARNFまでの警告情報の16 進表記 START-TIME: 該当オペレーション実行要求受付時刻 END-TIME: 該当オペレーション実行要求終了時刻 実行時間 :(END-TIME) - (START-TIME) UAP 時間 :( 前のオペレーションのEND-TIME) - (START-TIME) OPTION:HiRDBの保守情報 SQL: 実行したSQL 文 ( オペレーションコードSET AUXI AUI2 OPN2の場合のみ ) このように SQL トレース画面では SQL の各オペレーションの合計実行時間を確認でき SQL トレース詳細画面では SQL の各オペレーションの実行時間を確認できます SQL トレース画面の表示項目と SQL トレース詳細画面の表示項目の関係を次に示します SQL トレース画面 実行したオペレーションの数です FROMからTO までの時間です 最初のオペレーションのSTART-TIMEが FROMになります 最後のオペレーションのEND-TIMEがTOになります 各オペレーションの実行時間の合計 最大 最小です また SQL 実行時間は OPN2の実行時間になります SQL トレース詳細画面 152

164 6 章 SQL のチューニング SQL トレース情報の分析例 SQL トレース画面で SQL 実行時間 を確認します SQL 実行時間が長い SQL をチューニングした方がより効果が期待できるため SQL 実行時間をキーにして SQL 実行時間が長い順にソートしてください SQL 実行時間が想定していたよりも長い SQL をチェックの対象にしてください その SQL のアクセスパスが適切かどうかを次節で説明する方法で確認します ここをクリックすると SQL 実行時間の長い順 または短い順にソートできます 153

165 6 章 SQL のチューニング 6.5 アクセスパス情報の解析 解析手順 SQL トレース情報を解析した結果 SQL の実行時間が想定していたよりも長い SQL のアクセスパスが適切かどうかを確認します アクセスパス情報の解析は HiRDB SQL Tuning Advisor を使用して行います 手順を次に示します < 手順 > 1. [ スタート ]-[ プログラム ]-[HiRDB SQL Tuning Advisor]-[ HiRDB SQL Tuning Advisor] を選択し HiRDB SQL Tuning Advisor を起動します 2. ツールバーの [ アクセスパス解析 ] をクリックします 3. UAP 一覧画面が表示されます UAP 名 欄を見て 調査対象のアプリケーションプログラム名を選択して ダブルクリックしてください 4. アプリケーションプログラム中で実行されている SQL の一覧が表示されます 調査対象の SQL を選択して ダブルクリックしてください SQL 文と SQL 最適化実行日時の欄を見て 該当する SQL を選択します 154

166 6 章 SQL のチューニング 5. SQL に対する警告とアクセスパスが表示されます 警告メッセージを参考にして対 処してください 解析対象のSQLが表示されます 予約語や文字列は 色分けされて表示されます このSQLに対する警告が表示されます 警告を選択してダブルクリックすると発生箇所を表示します アイコンをクリックすると詳細情報が表示されます 警告発生箇所 6. [ インデクス提案 ] ボタンをクリックすると より適切なインデックス定義を表示できます 155

167 6 章 SQL のチューニング スキャン方法および結合方法を表すキーワード HiRDB SQL Tuning Advisor に出力されるアクセスパス情報の中で スキャン方法および結合方法には次に示すキーワードを使用しています なお 表中のスキャン方法 結合方法の HiRDB SQL Tuning Advisor での表示箇所については アクセスパス情報の分析例 で説明しています 表 スキャン方法および結合方法を表すキーワード TABLE SCAN テーブルスキャンインデックスを使用しないで 表のデータページを検索します ージ結合結合方法マ分類キーワード意味スキャン方法INDEX SCAN KEY SCAN MULTI COLUMNS INDEX SCAN MULTI COLUMNS KEY SCAN AND PLURAL INDEXES SCAN OR PLURAL INDEXES SCAN SORT MERGE JOIN KEY SCAN MERGE JOIN LIST SCAN MERGE JOIN L-KEY R-LIST MERGE JOIN L-LIST R-KEY MERGE JOIN L-LIST R-SORT MERGE JOIN または L-KEY R-SORT MERGE JOIN pdvwopt L-SORT R-KEY MERGE JOIN L-SORT R-LIST MERGE JOIN インデックススキャン キースキャン 複数列インデックススキャン複数列インデックスのキースキャン複数のインデックスを使用したスキャン複数のインデックスを使用したスキャン ソートマージ結合 キースキャンマージ結合 リストスキャンマージ結合 左キー右リストマージ結合 左リスト右キーマージ結合 左リスト右ソートマージ結合 左ソート右キーマージ結合 左ソート右リストマージ結合 単一列インデックスのインデックスページを検索して絞り込んだ後 表のデータページを検索します 単一列インデックスのインデックスページのみ検索します データページは検索しません 複数列インデックスのインデックスページを検索して絞り込んだ後 表のデータページを検索します 複数列インデックスのインデックスページのみ検索します データページは検索しません AND 演算子又は OR 演算子で結ばれた探索条件に対して それぞれのインデックスを使用して検索します OR 演算子で結ばれた探索条件に対して それぞれのインデックスを使用して検索します 外表と内表から行を取り出して それぞれ作業表を作成しソートします その後 結合条件を満たした行同士を結合します 外表と内表から KEY SCAN で行を取り出します その後 結合条件を満たした行同士を結合します 外表と内表から作業表を作成し ソートはせずに結合列の昇順に行を取り出します その後 結合条件を満たした行同士を結合します 外表は KEY SCAN で行を取り出し 内表は作業表を作成し ソートはせずに行を取り出します その後 結合条件を満たした行同士を結合します 外表は作業表を作成し ソートはせずに行を取り出し 内表は KEY SCAN で行を取り出します その後 結合条件を満たした行同士を結合します 外表は作業表を作成し ソートはせずに行を取り出し 内表は作業表を作成し ソートをして行を取り出します その後 結合条件を満たした行同士を結合します 外表は作業表を作成し ソートをして行を取り出し 内表は KEY SCAN で行を取り出します その後 結合条件を満たした行同士を結合します 外表は作業表を作成し ソートをして行を取り出し 内表は作業表を作成し ソートはせずに行を取り出します その後 結合条件を満たした行同士を結合します ネストループ結合ハッシュ結合クロス結合NESTED LOOPS JOIN HASH JOIN{FOR EACH} ネストループ結合 ハッシュ結合 外表から 1 行ずつ行を取り出し 内表のそれぞれの行に突き合わせて 結合条件を満たす行を取り出す入れ子型のループ処理をする結合方法です あらかじめ内表の結合列の値でハッシングしてハッシュ表を作成しておきます その後 外表を 1 行取り出すごとに外表の結合列の値でハッシングし 内表から作成しておいたハッシュ表と突き合わせて結合します CROSS JOIN クロス結合 外表のすべての行と 内表のすべての行をそれぞれ組み合わせて結合します 二つ以上の表にわたった条件があれば 結合した後に判定します 注マージ結合 ネストループ結合 ハッシュ結合については 表の結合方式 で説明しています 156

168 6 章 SQL のチューニング サーチ条件種別を表すキーワードサーチ条件とは インデックス上のサーチ範囲 つまりインデックス上をスキャンする範囲を表します インデックススキャン時に選択されているサーチ条件の種別を確認することで where 句に指定した検索条件に合わせて 効率良くインデックスをスキャンしているかどうかを判断できます C1 列上に作成したインデックス 検索条件 :where C1 between 'b' and 'z' b サーチ開始値 z サーチ終了値 インデックス上のスキャン範囲 図 サーチ条件とは HiRDB SQL Tuning Advisor で出力される サーチ条件種別を表すキーワードを以下に示します 表示箇所については アクセスパス情報の分析例 で説明しています AT[ 絞り込み値 ] AT は 検索条件に =(C1='a',C1=(select C1 from T2 where C2 = 10) など ) を指定し インデックスから単一の値をサーチする場合に選択されます 例 単一列インデックススキャンの場合 INSTRUCTORS 表の INST_ID 列上に作成したインデックス 検索条件 :where INST_ID =9001 サーチ種別 :SearchCnd:AT[9001] 9001 絞り込み値 複数列インデックススキャンの場合 SEMINARS 表の SUBJECT_ID 列および INST_ID 列上に作成したインデックス 検索条件 :where SUBJECT_ID ='DB01' and INST_ID =9004 サーチ種別 :SearchCnd:AT['DB01',9004] インデックス第一構成列 インデックス第ニ構成列 DB 絞り込み値 図 サーチ条件 AT 157

169 6 章 SQL のチューニング ATS[ 絞り込み値 ],,[ 絞り込み値 ] ATS は 検索条件に IN 述語 (C1 in ('a','b','c') など ) などを指定して インデックスか ら複数の値を繰り返しサーチする場合に選択されます 例 単一列インデックススキャンの場合 INSTRUCTORS 表の INST_ID 列上に作成したインデックス 検索条件 :where INST_ID in(9001,9002,9003) サーチ種別 :SearchCnd:ATS[9001],[9002],[9003] 絞込み値 1 絞込み値 2 絞込み値 3 図 サーチ条件 ATS RANGE RANGE は 検索条件に比較条件 (C2>10 など ) や BETWEEN 述語 (C2 BETWEEN 10 and 30 など ) などを指定し インデックス上を 開始位置から終了位置までサーチする場合に使用します 絞込み終了値絞込み開始値 STUDENTS 表の STU_ID 列上に作成したインデックス 検索条件 :where STU_ID between 0001 and 5000 サーチ種別 :SearchCnd:RANGE(CS-CE) [1,5000] 0001 サーチ開始値 5000 サーチ終了値 サーチ開始値サーチ終了値 サーチ範囲 CS,CE の C は 絞込みの開始値および終了値が サーチ範囲に含まれることを意味します サーチ範囲に 絞込みの開始値および終了値を含まない場合 (where C1 > 0001 and C1 < 5000 など ) は キーワードが OS OE となります 図 サーチ条件 RANGE 158

170 6 章 SQL のチューニング RANGES RANGES は 複数列インデックスを使用したスキャンにのみ適用されます インデックス上のサーチ範囲 ( 開始値から終了値までの範囲 ) を 複数回サーチする場合に選択されます 絞込み値 2 絞込み値 1 絞込み終了値絞込み開始値 INSTRUCTORS 表の INST_GRADE 列および HIRE_YEAR 列上に作成したインデックス 検索条件 :where INST_GRADE in ( M-1, M-2 ) and HIRE_YEAR between 1990 and 1995 サーチ種別 :SearchCnd:RANGES(CS-CE) [('M-1',1990),('M-1',1995)],[('M-2,1990),('M-2',1995)] サーチ範囲 1 サーチ範囲 2 M M M M サーチ範囲 1 サーチ範囲 2 CS,CE の C は 絞込みの開始値および終了値が サーチ範囲に含まれることを意味します サーチ範囲に 絞込みの開始値および終了値を含まない場合 (where C1 > 001 and C1 < 5000 など ) は キーワードが OS OE となります 図 サーチ条件 RANGES RANGE (FULL SCAN) または NONE(FULL SCAN) サーチ条件種別に (FULL SCAN) が含まれている場合は インデックス上の全てのリーフがサーチされることを意味します インデックス第 2 構成列に対する条件 INSTRUCTORS 表の INST_GRADE 列および HIRE_YEAR 列上に作成したインデックス 検索条件 :where HIRE_YEAR between 1990 and 1995 サーチ種別 :SearchCnd:RANGE(CS-CE) [(MIN,1990),(MAX,1995)] (FULL SCAN) インデックス第 1 構成列 :INST_GRADE インデックス第 2 構成列 :HIRE_YEAR M M M M サーチ範囲 図 サーチ条件 (FULL SCAN) 159

171 6 章 SQL のチューニング アクセスパス情報の分析例 アクセスパスに問題があった場合 ( 警告メッセージが表示された場合 ) は SQL チュー ニングを検討してください SQL チューニングの必要性の判断基準を次に示します 表 チューニングの必要性の判断基準 1 : チューニング必要 : チューニング検討要 : 現状維持 検索条件 検索の内容 1 選択されたスキャン方法 2 インデックスに対する絞込み条件 絞り込みのできる検索条件が指定されている AND を使用し 複数の述語による条件を指定している OR を使用し 複数の述語による条件指定している 全ての条件のヒット率が低い 一部および全ての条件のヒット率が低い INDEX SCAN KEY SCAN MULTI COLUMNS INDEX SCAN MULTI COLUMNS KEY SCAN 上記以外 MULTI COLUMNS INDEX SCAN MULTI COLUMNS KEY SCAN KEY SCAN AND PLURAL INDEXES SCAN TABLE SCAN 上記以外 OR PLURAL INDEXES SCAN KEY SCAN 上記以外 TABLE SCAN KEY SCAN 上記以外 SQL チューニングが必要か? AT ATS RANGE RANGES (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) 160

172 6 章 SQL のチューニング その他 表の検索範囲が広い ( 全件検索等 ) 検索の内容 1 選択されたスキャン方法 2 インデックスに対する絞込み条件 SELECT 文内で使用する列数が テーブルの列数と比較して 少ない TABLE SCAN KEY SCAN 上記以外 KEY SCAN 上記以外 AT ATS RANGE RANGES SQL チューニングが必要か? (FULL SCAN) AT ATS RANGE RANGES (FULL SCAN) 検索の内容 結合内容 3 結合方法中間結果情報各表のスキャン方法 SQL チューニングが必要か? 結合 結合結果の件数が少ない NESTED LOOPSJOIN 絞り込める表を外表に指定している 内表に対しインデックススキャンをしている内表に対しテーブルスキャンをしている 絞り込める表を外表に指定していない 上記以外 HASH JOIN 検索処理行数が少ない表を内表に指定している 一方の表の母体件数が {FOR EACH} 検索処理行数が少ない表を内表に指定していない少ない 上記以外 結合結果の件数が HASH JOIN 検索処理行数が少ない表を内表に指定している 多いメモリサイズに {FOR EACH} 両方の表の検索処理行数が少ない表を内表に指定していない余裕がある 母体件数が 上記以外 多い メモリサイズに SORT MERGEJOIN 余裕がない 上記以外 結合条件が指定されていない CROSS JOIN 注スキャン方法や 結合方法の表示箇所については 次のページで説明しています また 表中および次の行の丸付き数字 (1~4) は 次のページの説明と対応しています 注 ヒット率 =4 検索条件で絞り込まれた結果の行数 / 表の母体件数 161

173 6 章 SQL のチューニング 外表の情報が表示されます 1 選択されたスキャン方法 表名 4 検索条件で絞り込まれた結果の行数 などが表示されます 内表の情報が表示されます 1 選択されたスキャン方法 表名 4 検索条件で絞り込まれた結果の行数 などが表示されます 3 結合方法 が表示されます 2インデックスに対する絞込み条件 がここのどれかの欄に表示されます 備考 外表は左表を意味し 内表は右表を意味しています 162

174 6 章 SQL のチューニング 6.6 SQL チューニング アクセスパス情報を解析した結果 アクセスパスに問題があった場合は 次に示す SQL チューニングを検討してください インデックスの定義を変更する SQL 文を書き換える インデックス定義の変更 アクセスパス情報を解析した結果 インデックス提案が表示された場合 その提案に基づいてインデックスの定義を変更することを検討してください インデックスの定義を変更するには いったん DROP INDEX でインデックスを削除したあとに CREATE INDEX でインデックスを定義してください 詳細情報 42 の 検索方法 が TABLE SCAN または AND PLURAL INDEXES SCAN になっていたり サーチ条件 が NONE(FULL SCAN) または RANGE (FULL SCAN) になっていたりする場合は インデックスの構成がSQL 文の条件内容や構文に合っていない可能性があります このような場合は 3.5 インデックスを定義するときの考慮点 および 3.6 インデックスの効果的な利用方法 を参照して より最適なインデックス構成へ変更するか 新しいインデックスの追加を検討します SQL 文の書き換え アクセスパス情報を解析したときの警告メッセージを参考にして 表の結合方式などを再検討してください 表の結合方式については 4.10 性能が向上する SQL の書き方 で説明しています また インデックスを有効に使える SQL の書き方の例を次に示します インデックスを有効に使える SQL の書き方同じ結果を得る SQL 文であっても 構文の書き方によってインデックスの使用に影響を与える場合があります 以下に インデックスを有効に使用させるための SQL 記述方法を示します インデックスの定義内容 CREATE INDEX INDX02 ON T1(C1,C2,C3) 42 警告発生箇所のアイコンをクリックすると表示されます 163

175 6 章 SQL のチューニング theory1 where 句のインデックス定義列に対し スカラ演算指定しない where 句内に インデックスが定義されている列 ( インデックス定義列 ) を指定した場合 その列にスカラ演算を使用すると インデックスに対する絞込みが行われません したがって 可能な限り 他の記述方法への書き換えを検討します select where C1*0.9 > select where C1 > 10000/0.9 select where SUBSTR(C1,1,5) = 'Tokyo' select where C1 like 'Tokyo%' theory2 同一列に対する or 条件は in 述語へ書き換える 同じ列に対する = 条件を OR で指定しており かつ条件指定列にインデックスを作成して いる場合は IN 述語に書き換えることで インデックスのスキャン回数のみならず 作 業表作成 集合演算のオーバヘッドを削減することができます select where C1 = 10 or C1 = 20 select where C1 in (10,20) theory3 or 条件の両辺に同じ条件を指定している場合は or の外へ条件を抜き出す select where (C1=10 and C2=20) or (C1=10 and C3=30) select where C1=10 and (C2=20 or C3=30) 164

176 付録 付録 付録 A CREATE TABLE の文法付録 B CREATE INDEX の文法付録 C Oracle と HiRDB のデータ型の対応付録 D 暗黙的データ型変換付録 E システム定義のパラメータの追加方法付録 F HiRDB の GUI 運用支援製品付録 G 体験版 HiRDB のインストールに失敗した場合の対処方法付録 H SQLが警告エラーになるケース 165

177 付録 付録 A CREATE TABLE の文法 ここでは CREATE TABLE と DROP TABLE の文法について説明します (1) CREATE TABLE 文法 CREATE TABLE 表名 ( 列名データ型 NOT NULL 列制約定義 PRIMARY KEY [IN RD エリア名 ], 列名データ型 NOT NULL UNIQUE CLUSTER KEY 列制約定義 PRIMARY KEY, { PRIMARY KEY( 列名 [, 列名 ]) [IN RD エリア名 ] 表制約定義 } ) IN RD エリア名 列制約定義 : CONSTRAINT 制約名 {CHECK( 条件 ) REFERENCES 被参照表名 ON [ DELETE CASCADE DELETE RESTRICT ] [ UPDATE CASCADE UPDATE RESTRICT ] } 表制約定義 : CONSTRAINT 制約名 {CHECK( 条件 ) FOREIGN KEY ( 列名 [, 列名 ]) REFERENCES 被参照表名 ON [ DELETE CASCADE DELETE RESTRICT ] [ UPDATE CASCADE UPDATE RESTRICT } 意味データ型列のデータ型を指定します NOT NULL 非ナル値制約を設定する場合に指定します PRIMARY KEY 列レベルで ( 列制約として ) 主キーを設定する場合に指定します 主キーを設定した列には自動的にユニークインデックスが作成されます 166

178 付録 PRIMARY KEY( 列名 [, 列名 ]) 表レベルで ( 表制約として ) 主キーを設定する場合に指定します ( キー列名 [, キー列名 ]) には 主キーを設定する列名を指定します 複数列に主キーを設定する場合には 表レベルでのみ定義できます RD エリア名表またはインデックスを格納する RD エリア名を指定します PRIMARY KEY 指定後の RD エリア名指定は 自動的に作成されるユニークインデックスの格納先となります 省略すると HiRDB が自動的に格納 RD エリアを決定します 体験版 HiRDB をインストールしている場合 表を格納するためのユーザ RD エリア (RDDATA10) が既に作成されています RD エリア名には RDDATA10 を指定してください 制約名検査制約または参照制約の制約名を指定します 省略すると 次の名称が仮定されます 検査制約 : 制約を定義した時刻 (100 分の 1 秒まで ) を使用した名称 (CK_ 表番号 _yyyymmddhhmmssth) 参照制約 : 制約を設定した列名条件列の値を制限する条件を指定します SELECT 文の WHERE 句と同様に記述します 被参照表名参照先の表名を指定します FOREIGN KEY( 列名 [, 列名 ]) 外部キーとなる列名を指定します 外部キー列名は複数指定できます DELETE CASCADE 被参照表の行を削除したときに 削除対象行の主キーの値と同じ値を外部キーに持つ参照表の行をすべて削除します DELETE RESTRICT 被参照表の行を削除すると 参照表の外部キーに同じ値があるときは参照制約エラーとなり 参照表の対応する行は削除されません UPDATE CASCADE 被参照表の行を更新したときに 更新対象行の主キーの値と同じ値を外部キーに持つ参照表の行の外部キー値をすべて被参照表の主キーと同じ値に更新します UPDATE RESTRICT 被参照表の行を更新すると 参照表の外部キーに同じ値があるときは参照制約エラーと 167

179 付録 なり 参照表の対応する行は更新されません Oracle との違い Oracleの場合は TYPE KEY FIXED 等が予約語ではないため これらを表や列等の名前等にすることができます 43 HiRDBの場合は TYPE KEY FIXED 等は予約語のため これらの語句を表や列等の名前等に使用する場合は 二重引用符で囲む必要があります (2) DROP TABLE 文法 DROP TABLE 表名 意味 表名 削除する表の名前を指定します 43 Oracle の仕様については Oracle のマニュアル Oracle Database SQL リファレンス の D Oracle データベース予約語 で説明されています 168

180 付録 付録 B CREATE INDEX の文法 ここでは CREATE INDEX と DROP INDEX の文法について説明します (1) CREATE INDEX 文法 CREATE UNIQUE INDEX インデックス名 ON 表名 ( 列名 ASC DESC, 列名 ASC DESC ) IN インデックス格納 RD エリア名 EXCEPT VALUES(NULL,NULL ) 意味インデックス名定義するインデックスの名前を指定します 表名インデックスを設定する表の名前を指定します 列名インデックスを設定する列の名前を指定します 複数列インデックスを定義する場合は 複数の列名をコンマ (,) で区切って指定します ASC または DESC インデックスを昇順にするか または降順にするかを指定します ASC は昇順 DESC は降順です 省略すると ASC がデフォルトとなります インデックス格納 RD エリア名インデックスを格納する RD エリア名を指定します 省略すると HiRDB が自動的に格納 RD エリアを決定します ( 基本的には 表が格納されている RD エリアとなります ) 体験版 HiRDB をインストールしている場合 インデックスを格納するためのユーザ RD エリア (RDINDX10) が既に作成されています RD エリア名に RDINDX10 を指定するか または RD エリア名を省略してください インデックス格納 RD エリア名を省略した場合は ユーザ RD エリア RDDATA10 にインデックスが格納されます EXCEPT VALUES(NULL,NULL ) 列のデータがナル値の場合 そのインデックスを作成しないときに指定します なお 複数列インデックスの場合は 列数分の NULL を記述することになります 169

181 付録 (2) DROP INDEX 文法 DROP INDEX インデックス名 意味 インデックス名 削除するインデックスの名前を指定します 170

182 付録 付録 C Oracle と HiRDB のデータ型の対応 ここでは Oracle と HiRDB の主なデータ型の対応について説明します 44 次の表は Oracle と HiRDB の主なデータ型の対応関係を示します 表 C-1 Oracle と HiRDB のデータ型の対応 分類 Oracle のデータ型条件 HiRDB のデータ型 数 値 NUMBER(p) p<5 SMALLINT または p<10 INTEGER NUMBER(p,0) 9<p<30 DECIMAL(p) NUMBER(p,s) p<30, かつ,0<s p DECIMAL(p,s) かつ s 0 p<s DECIMAL(s,s) s<0 DECIMAL(p-s,0) p>29,s>29 又は p-s>29 DECIMAL(p,s) または FLOAT NUMBER - INTEGER BINARY_FLOAT - SMALLFLT 文 字 日付時刻 バイナリ BINARY_DOUBLE - FLOAT CHAR(n) または 半角のみ CHAR(n) NCHAR(n) 全角のみ NCHAR(n) 半角全角混在 MCHAR(n) VARCHAR2(n) 半角のみ VARCHAR(n) または 全角のみ NVARCHAR(n) NVARCHAR2(n) 半角全角混在 MVARCHAR(n) LONG 半角のみ VARCHAR(n) または 全角のみ NVARCHAR(n) CLOB 半角全角混在 MVARCHAR(n) 32,000B< テ ータ長 2GB BINARY または BLOB 2GB< テ ータ長 4GB 該当データ型なし DATE 日付のみ DATE 時刻のみ TIME 日時 ( 日付と時刻 ) TIMESTAMP TIMESTAMP - TIMESTAMP RAW(n) - BINARY または BLOB(n) BLOB または テ ータ長 2GB BINARY または LONGRAW BLOB(n) 2GB< テ ータ長 4GB 該当データ型なし 44 Oracle の仕様については Oracle のマニュアル Oracle Database アプリケーション開発者ガイド- 基礎編 の 3 アプリケーション開発における SQL データ型の使用 で説明されています 171

183 付録 注 システム定義に pd_sql_dec_op_maxprec=38 の指定が必要です 数データの対応 OracleのNUMBER(p,s) 型 45 は パック形式でのデータ格納方式であり 標準 SQLの DECIMAL 型と同等で HiRDBのDECIMAL 型もこれに相当します また OracleのBINARY_FLOATおよびBINARY_DOUBLE 46 は 浮動小数点形式でのデータ格納方式です HiRDB では これらの他に 2 進形式 (INTEGER 等 ) の数値データ型があります HiRDB では 表 C-1 のように場合分けしてデータを実装することができます 場合分けすることにより 格納効率や演算処理速度等が良くなります 文字データの対応 HiRDB では 文字データを固定長と可変長で分類し さらに固定長や可変長を 半角のみ 全角のみ 半角全角の混合の 3 種類 ( 計 6 種類 ) に細分します 従って 例えば Oracle の CHAR 型は HiRDB では 半角データのみが格納されている場合は CHAR 型 全角データのみが格納されている場合は NCHAR 型 半角データと全角データが混合する場合は MCHAR 型にそれぞれ対応します また Oracle の LONG 型や CLOB 型は データ長が 32,000 バイトを超える場合のみ BINARY 型や BLOB 型で対応します ただし その場合は文字列を操作する関数を使用することができません LONG 型および CLOB 型以外の文字データ型は Oracle よりも HiRDB の方がいずれも最大データ長が大きいためそのまま移行できます 日付時刻データの対応 Oracle の DATE 型は日付と時刻を同時に格納するものです また Oracle では日付専用 時刻専用のデータ型がありません 従って 例えば注文日付のように日付データのみが必要な場合でも Oracle では DATE 型を使用します この場合 DATE 型の時刻部分には 0 が入ります HiRDB では日付専用 時刻専用のデータ型があるため 表 C-1 のように 日付のみの場合と時刻のみの場合 日付と時刻を同時に格納する場合とに分けてデータを格納することができます 45 ここでの p は全体の桁数を表し s は小数点以下の桁数を表します 46 BINARY_FLOAT は単精度 (32bit) BINARY_DOUBL は倍精度 (64bit) です 172

184 付録 付録 D 暗黙的データ型変換 変換前データ型 数データ INTEGER SMALLINT DECIMAL FLOAT SMALLFLT 文字データ CHARACTER VARCHAR 各国文字データ NCHAR NVARCHAR 混在文字データ MCHAR MVARCHAR 日付データ DATE 時刻データ TIME 時刻印データ TIMESTAMP データの比較 / 代入をする場合 基本的には同じデータ型である必要があります ただし データ型が異なっていても 暗黙的にデータ型が変換されることがあります 暗黙的な データ型変換 ( 主なデータ型についてだけ ) を次の表に示します 表 D-1 暗黙的なデータ型変換 変換後データ型 数データ 文字データ 各国文字 混在文字 日付 時刻 時刻印 INTEGER CHARACTER データ データ データ データ データ SMALLINT VARCHAR NCHAR MCHAR DATE TIME TIMESTAMP DECIMAL NVARCHAR MVARCHAR FLOAT SMALLFLT,, 1,, 1,,,, 2 3,, 7,, 4, 5, 6,,,,,,,, 2 3,,,,,,,, 8,,,,,,, 9,,,,,,, 10,,,,, 11 ( 凡例 ) : 変換できます : 制限付きで変換できます : 変換できません 注表中の変換可否は 代入可否, 比較可否 となっています 例えば, の場合は代入および比較ができて, の場合は代入はできるが比較はできないということになります 注 1 数データを文字データに変換する場合の対応関係を次に示します なお 変換後のデータ型が混在文字データの場合は VARCHAR を MVARCHAR に CHAR を MCHAR に読み替えてください INTEGER 型の数データは VARCHAR(11) の文字データに変換されます SMALLINT 型の数データは VARCHAR(6) の文字データに変換されます DECIMAL(p,0) 型の数データは VARCHAR(p+1) の文字データに変換されます DECIMAL(p,s) 型の数データは VARCHAR(p+2) の文字データに変換されます 173

185 付録 FLOAT 型または SMALLFLT 型の数データは CHAR(23) の文字データに変換されます INTEGER 型 SMALLINT 型 および DECIMAL 型での数データが正の場合 変換された文字データには正の符号は含みません また FLOAT 型および SMALFLT 型の数データを文字データに変換する場合 指数部と仮数部に必ず符号を付加します 注 2 文字データと数データを比較する場合 文字データを数データに変換して比較をします 文字データと数データを比較できるのは 数データが列指定であり 文字データが次の値式の場合だけです 定数? パラメタ 埋込み変数 SQL 変数 SQL パラメタ文字データと数データを比較できるのは 比較述語 限定述語 IN 述語 および BETWEEN 述語だけです 比較述語の場合は 左辺右辺のどちらかが数データの列指定でも比較できます 限定述語の場合は 左辺の値式が数データの列指定でなければなりません IN 述語の場合は 左辺の値式が数データの列指定でなければなりません BETWEEN 述語の場合は 左辺の値式 1 が数データの列指定でなければなりません 注 3 文字データを数データに代入 比較する場合の対応関係を次に示します 整数の文字列表現は INTEGER 型に変換されます 10 進数の文字列表現は DECIMAL 型に変換されます 浮動小数点数の文字列表現は FLOAT 型に変換されます 数の文字列表現の前後に空白がある場合 空白を無視して変換をします 注 4 次に示すものは 日付データへの代入 および日付データとの比較ができます 日付に対する定数表現('YYYY-MM-DD') CHAR(10) に対応する埋込み変数注 5 次に示すものは 時刻データへの代入 および時刻データとの比較ができます 時刻に対する定数表現('hh:mm:ss') CHAR(8) に対応する埋込み変数注 6 次に示すものは 時刻印データとの比較 および時刻印データへの変換ができます 時刻印に対する定数表現('YYYY-MM-DD hh:mm:ss') 長さが および 26 バイトの CHAR に対応する埋込み変数注 7 次に示す項目で文字列定数を記述した場合 各国文字列定数とみなし 文字データの長 174

186 付録 さだけをチェックし 文字コードはチェックしません INSERT 文 UPDATE 文 探索条件注 8 次に示すものは 日付データと比較できます 日付に対する定数表現('YYYY-MM-DD') CHAR(10) に対応する埋込み変数また 次の場合は 文字データに変換できます 日付データを 長さが 10 バイト以上の CHAR または VARCHAR の埋込み変数に代入した場合注 9 次に示すものは 時刻データと比較できます 時刻に対する定数表現(' hh:mm:ss') CHAR(8) に対応する埋込み変数また 次の場合は 文字データに変換できます 時刻データを 長さが 8 バイト以上の CHAR または VARCHAR の埋込み変数に代入した場合注 10 次に示すものは 時刻印データと比較できます 時刻印に対する定数表現('YYYY-MM-DD hh:mm:ss') 長さが および 26 バイトの CHAR に対応する埋込み変数また 次の場合は文字データに代入できます 長さが および 26 バイト以上の CHAR または VARCHAR の埋込み変数への代入注 11 代入元の小数秒精度が代入先の小数秒精度より高い場合 代入先の精度に合わせて小数行部分を切り捨てます 代入元の小数秒精度が代入先の代入先の小数秒精度より低い場合 代入先の精度に合わせて拡張した小数秒部分に 0 を補って格納します 175

187 付録 付録 E システム定義のパラメータの追加方法 簡易セットアップツールを使用してシステム定義のパラメータを追加します 手順を次に示します < 手順 > 1. [ スタート ]-[ プログラム ]-[HiRDBSingleServer]-[ 簡易セットアップツール ] を選択し 簡易セットアップツールを起動します 2. [HiRDB セットアップツール- 開始 ] 画面で [ セットアップ種別 ] の [ 定義更新 ] を選択し [ 定義更新 ] ボタンをクリックします 3. セットアップを続けるかどうかの確認メッセージが表示されたら [ はい ] を選択します 4. [HiRDB セットアップツール- 詳細定義 ( 定義更新 )] 画面で ツールバーの [ オペランド追加 ] をクリックします 176

188 付録 5. [ 追加オペランド選択 ] ダイアログで 追加したいパラメータを選択して [OK] ボ タンをクリックします 6. [ オペランド値編集 ] ダイアログで パラメータに設定する値を入力して [OK] ボ タンをクリックします 7. パラメータの追加が終わったら ツールバーの [ システム定義のチェック ] ボタンをクリックして システム定義のチェックをします システム定義のチェックでエラーがあると 定義の更新はできません 例えば ディレクトリ名を設定しなければならないパラメータに数値を入力した場合はエラーになります その場合は パラメータに設定した値を修正してから再度システム定義のチェックをしてください 177

189 付録 8. システム定義のチェックで問題がなければ ツールバーの [ システム定義の更新 ] ボタンをクリックして システム定義を更新します これによって システム定義にパラメータが追加されます 178

190 付録 付録 F HiRDB の GUI 運用支援製品 HiRDB の GUI 運用支援製品を紹介します F.1 簡易セットアップツール 簡易セットアップツールとは HiRDB の GUI 運用支援製品の一つで このツールを使用すると 表示される画面に従って環境設定情報を入力していくだけで HiRDB の環境設定が完了するため HiRDB の初期構築に掛かる時間を短くすることができます 簡易セットアップツールの開始画面を次に示します [ セットアップ開始 ] ボタンをクリックすると セットアップに必要な情報を入力する画面が表示されます 体験版 HiRDBをインストールしたときも 簡易セットアップツールが自動的に実行されています 体験版 HiRDBは セットアップ種別がカスタムセットアップ 47 で システム規模が小規模 48 で作成されています また 付録 E で説明したとおり このツールでシステム定義のパラメータを追加 変更することができます F.2 HiRDB Control Manager HiRDB Control Manager とは HiRDB のシステム管理者が行う操作を支援する製品です HiRDB ファイルシステム領域の作成 RD エリアの作成 バックアップの取得 データベースの回復などの操作が GUI で実行できます また 表の定義を行うときには HiRDB SQL Executer から CREATE TABLE を実行しますが HiRDB Control Manager からも HiRDB SQL Executer を起動できます 47 簡易セットアップツールを使用したセットアップには 標準セットアップとカスタムセットアップの 2 種類があります 48 簡易セットアップツールでは構築するシステムの規模を選択できます 小規模 中規模 大規模の 3 種類から選択できます 179

191 付録 1 ここをクリックします 2 認可識別子には APPUSER を パスワードには STDDB を指定します 180