新しい運用管理ツールとアプリケーション開発の生産性向上 · （参考）...

本資料掲載事項は、ある特定の環境・使用状況においての正確性がIBMによって確認されていますが、すべての環境において同様の結果が得られる保証はありません。これらの技術を自身の環境に適用する際には、自己の責任において十分な検証と確認を実施いただくことをお奨めいたします。

© 2012 IBM Corporation 1

5. 新しい運用管理ツールとアプリケーション開発の生産性向上 -Data Studio、Optim Performance Manager、タイムトラベル照会、新しいデータ移動ユーティリティ

＜第１．００版２０12年 9月＞


• Data Studio

• Optim Performance Manager (OPM)

• Optim Query Workload Tuner (OQWT)

• タイムトラベル照会

• (参考) テンポラル表 I/U/D 検証結果

• (参考) その他のSQL／アプリケーション機能拡張

• Ingest

• パーティション表機能強化

内容


ぜひ活用していただきたい”新しい運用管理ツール”のご紹介

DB2 V10.1 Advanced Enterprise(AESE) に含まれる機能&製品

• Workload Management (DB2 WLM)

• DB2とOracleとのFederation

• Continuous Data Ingest (新機能)

• InfoSphere Data Architect 8.1

• Optim Configuration Manager 2.1.1

• Optim Performance Manager Extended Edition 5.1.1

• Optim Query Workload Tuner 3.1.1

• Optim pureQuery Runtime 3.1.1

無償提供のDB2 運用管理＆アプリケーション開発ツール

• IBM Data Studio 3.1.1

当セッションでは、下線の製品(ツール）についてご紹介します。太字(下線なし)の製品は、下線のツールと連携可能な製品です。


Data Studio


DB DB

DB

Eclipseベース

IBM Data

Studio

Data Studio とは？

データベース管理およびアプリケーション開発用ツール

① DB運用と開発の両方ができる統合GUIツール

② Eclipseをベースとしたツール

③ DB2コントロールセンターから置き換わるもの

Optim Development Studio (開発)

Optim Database Administrator (運用管理)

Data Studio Health Monitor (状態監視)

Control

Center

～V9.7

統合

IDA

RAD

Optim 製品

無償提供ツールdeveloperWorks からDL可能

⇔


① DB運用と開発の両方ができる統合GUIツール

• Data Studioひとつで、DBの運用管理、開発、チューニング、監視が可能

• V3.1からOptim Development Studio (開発)、Optim Database Administrator (運用管理)、Data Studio Health Monitor (状態監視)を統合

• Optim製品との連携

• Optim pureQuery (DB2 V10.1 AESE同梱)を使用するアプリケーション・プログラム開発／デバッグ／実行可能

• Optim Query Workload Tuner (DB2 V10.1 AESE同梱)を使用した高度なSQLチューニング

② Eclipseをベースとしたツール

• Eclipseに慣れたユーザー様にとっては直感的でわかりやすい仕様

• InforSphere Data Architect (DB2 V10.1 AESE同梱)やRational Application Developer等のEclipseベース製品とEclipse環境を共有可能

③ DB2コントロールセンターから置き換わるもの

• V9.7以前に付属していたDB2コントロールセンターはV10.1で廃止

Data Studio V3.1.1 の特徴

（参考） Data Studio とコントロール・センター・ツールの機能対応表

DB2 V9.7 Infomation Center 『コントロール・センター・ツールは推奨されなくなった』

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.wn.doc/doc/i0054250.html

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.wn.doc/doc/i0054250.html


Data Studioのコンポーネント

• Data Studioフル・クライアント

完全なフィーチャー・セット。Java IDE を持つ

• Data Studio 管理クライアント

フル・クライアントの代わりとなるもの

より軽量で、DBA 指向。Java IDE を持たない

• Data Studio Web コンソール

Webブラウザー・ベースのモニタリング・インターフェース

Data Studio として3つのコンポーネントを提供

• DB2インストール時に一緒に導入されないため、別途インストールが必要


Data Studioの一般的な構成

Informix

DB2

JDBC type4

IBM Data Studio

プラットフォームOSはWindowsや主要な

Linuxディストリビューションをサポート

Oracle

LUW

zDB2

iDB2

(参考) Data Studio 3.1.1のシステム要件 IBM Data Studio Version 3.1.1 system requirements

http://www-01.ibm.com/support/docview.wss?uid=swg27024588

Disk Memory (RAM)

Data Studio

フルクライアント

2 GB

最低：1 GB

推奨：2 GB

Data Studio

管理クライアント

500 MB 最低：1 GB

推奨：2 GB

Data Studio

Webコンソール

300 MB 最低：1 GB

推奨：2 GB

ハードウェア用件





Data Studio機能一覧

• データベースおよびデータ・オブジェクトの管理フィーチャー:

• DPF および pureScale データベースのサポート

• データ・ソース接続: データ・オブジェクトおよびそのプロパティーを参照

• エディターおよびタスク・アシスト: データ・オブジェクトの作成、変更、または削除

• 特権の変更: データ・オブジェクトおよび認証 ID

• データ操作: 編集、解凍、およびロード

• データ・ダイアグラム: データ・オブジェクトの関連の視覚化

• 変更時の影響分析

• データベースの比較および同期

• 変更内容の保存および取り消し

• アプリケーション開発フィーチャー:

• エディターおよびタスク・アシスト: ストアード・プロシージャーおよび UDF の作成、テスト、デバッグ、およびデプロイ

• SQL クエリー・ビルダー、SQL エディターと XQuery エディター

• Visual Explain

• ルーチン・デバッガー

• Web サービスの作成、XML および SQLJ の開発


Data Studio (Eclipseベース共通)の基本用語

パースペクティブの選択

ビュー

SQLの実行結果

エディター

(プロシージャーの例)

ビュー

データソース・エクスプローラー

カスタマイズ可能

パースペクティブ内のビューやエディターを開く、閉じる、またはサイズ変更することや、任意の場所にドラッグ・アンド・ドロップすることができる


Data Studio 使用例

• データベースの運用管理

Webコンソールへ

構成パラメーターの変更

自動保守の設定



• 表のメンテナンス

管理エクスプローラー RunstatsやReorgの実行



• データベース管理タスクの実行例) DBバックアップ

設定内容に従って、コマンドが生成され、ここから直接実行可能。

各オプションを設定



• 表の変更（オブジェクト・タスク） 1/2

「列」画面カラムの追加

属性の変更

変更(プランニング)開始

変更確認とデプロイ

表のプロパティー変更前後

の比較



• 表の変更（オブジェクト・タスク） 2/2

表の変更前と変更後を比較

変更のためのコマンド確認とデプロイメント



• ストレージ・グループと表スペースの確認／作成／変更

ストレージ・グループ

表スペース

管理エクスプローラー



• 権限および特権の管理

オブジェクト特権の管理

データベース権限の管理



• SQL作成、フォーマット、実行

SQL を編集

右クリックメニューから[SQL

のフォーマット] を実施

SQLの実行

実行結果

右クリック



• アクセス・プランの生成

すべてのノードでドリルダウンして詳細を参照できる

右クリックメニュー

（参考）製品別のチューニング機能一覧 (OQWTライセンス有無の違い)

http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.qrytune.relinfo.doc/topics/featuresbyproduct.html





• 影響の分析（オブジェクトの従属関係の表示）

影響を受けるオブジェクトのダイアグラム

影響を受けるオブジェクトの詳細リスト



• 総括ダイアグラム（指定オブジェクト関係の確認）

ダイアグラム内の任意のオブジェクトまたは関係を移動、変更、または編集可能



• データの編集

行の挿入

追加した行に

データを入力



• オブジェクト同士の比較 GSDBデータベースと

GOSLデータベースの

比較

同じオブジェクト

は同じ行に表示さ

れている



• pureScaleメンバー・ビューアーおよびタスク・アシスト


Data Studio Web コンソールの機能一覧

① ヘルスの概要のサマリー

• サーバーの状況

• 操作 (日常的な操作上の問題)

• データベース・パーティションの状況

• pureScale メンバーおよびクラスター・ファシリティーの状況

• 接続状況 (実行中のアプリケーションおよびユーティリティー別)

• ストレージ状況 (表スペースを含む)

• リカバリー関連のアラートの問題

② アラート管理

• カスタマイズしたしきい値を使用して上記すべてを構成

• E メールまたはその他の通知方式を作成

③ ジョブ管理

• DB2 保守ジョブの作成、スケジュール、レビュー、および編集

ブラウザー・ベースのデータベース・モニタリングおよびジョブ制御


Data Studio Web コンソール

①正常性の要約

②アラート管理

③ジョブ管理


Optim Performance Manager


Users

Application

Servers

Network

パフォーマンス問題を解決するための４フェーズ

• 問題の詳細を把握するためのドリルダウン分析

• キャプチャーデータの調査

2. 原因分析

• 顕在化していない段階でのアラート出現

• 問題発生が分かりやすいGUIの提供

1. 問題認識

4. 未然防止 • 問題解決のためのアドバイス機能 • 問題の修正（SQL, データベース） →Optim Query Workload Tunerと連携

3. 解決

DBMS & OS

• 過去のデータを蓄積、分析

• ワークロードの自動調整

Optim Performance Managerで対応！


Optim Performance Manager (OPM) とは?

複数のDB2データベースのパフォーマンス・モニタリング/管理GUIツール

- DB2 V10.1 AESEに同梱

- DB2 Performance Expertの後継

- Optim Query Workload Tunerとの連携

- OPM Extended Insight (EI)によるDBアプリケーションのE2Eモニタリング

Optim Performance Manager (メイン機能) • DB2システムを管理するための、Webインターフェースのパフォーマンス分析

Optim Performance Manager Extended Insight

• DBアプリケーション・トランザクションのエンドツーエンドのパフォーマンス分析

Optim Query Workload Tuner • SQL ステートメントのパフォーマンス分析と最適化

OPM

OPM EI

OQWT


Optim Performance Managerの特徴システムの日常の状態を監視して蓄積

• H/W,OS,DB2の問題の発生した時点に遡った分析

• システムの傾向分析、将来のプランニング

問題発生時のボトルネック解析

• 複数エレメントの詳細ダッシュボードへのドリルダウン

• DBアプリケシーションのエンド・ツー・エンドモニタリング(Extended Insight)

• Optim Query Workload Tunerと連携しSQLチューニング

パフォーマンス・レポートの容易な作成、エクスポート

• テキストデータ取得やExcel加工が不要

DB2ワークロード・マネージャー(DB2 WLM)の設定、監視

Webブラウザ・インターフェースによる複数DB環境の一元管理と簡単操作


Meta snapshot

E2E

repository

レポジトリー・サーバー

コンソール・サーバー RS API

Insert

maintain

DB2 LUW ESE

Browser (IE/Firefox)

Adobe Flash

Http or Https

-構成 -ダッシュボード -アラート -正常性


TCP/IP

Performance Expert Client（オプション）

監視対象

DB 監視対象

DB 監視対象

DB

TCP/IP TCP/IP

• Optim Performance Manager Server （サーバー）

• Optim Performance Manager License Activation Kit （ライセンス）

• Optim Performance Manager Extended Insight Licence Activation

Kit (オプション：Extended InsightのActivate）

• DB2 Performance Expert Client（オプション）

監視端末

WebSphere Application Server

アプリケーション・サーバー

• Optim Performance Manager

Extended Insight Client（オプション）

• Common Information Model （オプション:CIMサーバー）

TCP/IP

Optim Performance Managerの全体構成


OPM Web Console基本画面遷移

アクションと確認事項 OPM画面遷移

正常性の要約

アラート

ダッシュボード

データベースごとに問題の所在（ワーニング/アラートの有無）を確認

ワーニング/アラート･イベントの発生時刻、内容を確認

・解析対象の「アクション」から対応したダッシュボード画面へ遷移

解析原因の解析と対応


OPM Web Console基本画面遷移 – 正常性の要約

• ”正常性の要約”によりDBごとに定常監視・問題箇所を確認

画面左上 “開く↓” より”

正常性の要約”を選択

それぞれのデータベースについてKey

Performance Indicators

(KPIs)が表示される

問題がある場合、該当KPIにワーニング▲ やアラート■が表示される。


OPM Web Console基本画面遷移 – アラート画面

• ワーニング/アラート･イベントの発生時刻、内容を確認

ダッシュボードへ

“正常性の要約”のそれぞれのKPIをクリックすると対応する“アラート画面”

が表示される

更に詳細情報を得るために、

”アクション”タブから関連する

”ダッシュボード”へのリンクへと飛ぶことができる


OPM Web Console基本画面遷移 – ダッシュボード

• ”ダッシュボード” から詳細情報やグラフ表示による時系列の遷移を確認例)バッファープールのダッシュボード


• DBアプリケーションの応答時間の詳細をグラフ、SQLステートメント、実際の数値を表示

DBアプリケーションのエンド・ツー・エンドモニタリング (Extended Insight) 1/2


DBアプリケーションのエンド・ツー・エンドモニタリング (Extended Insight) 2/2

さらに詳細な応答時間の詳細な数値の表示

上部のグラフにカーソルを近づけると、取得項目や時刻が確認可能


レポートの作成

• OPMで管理されているDBモニタリング情報をレポートで出力

作成可能なレポートタイプ一覧

この例では、[上位SQLレポート]

を選択


レポートのエクスポート

• 表示されたレポートをファイル形式（xls,ppt,pdf）を指定してエクスポートして保存

平均や合計、ソートやロックなど、表示したい算出方法やカテゴリを選択できる

形式を指定してエクスポート or プリント


(参考) 出力レポートイメージ – エクセル形式


(参考) 出力レポートイメージ – PDF形式


(参考) 出力レポートイメージ – PPT形式


Optim Query Workload Tuner


SQLステートメントのパフォーマンス分析および最適化ツール

•パフォーマンス問題の発見

• SQLやワークロードの収集し、問題箇所を指摘

•実行プランの分析

• SQLの可視化、情報の補足

• アクセスパスの可視化、比較、情報の補足、参照方法のアドバイス

• 実行時とアクセスパスの差分を補足

•チューニングアドバイザーによる問題の解決

• SQLの改善をアドバイス

• 統計情報の改善をアドバイス

• データベース設計(インデックス、パーティショニングなど)の改善をアドバイス

•改善の実行と未然防止

• SQLの変更

• ワークロードの再実行

• 結果の比較

Advisor overview

Recommendations

問題を発見

アドバイス

結果の比較

Optim Query Workload Tuner(OQWT)とは？

・統計アドバイザー

・照会アドバイザー

・アクセス・パス・アドバイザー

・索引アドバイザー

© 2012 IBM Corporation

チューニング対象DB2 環境

・Optim Query Workload Tuner License

Activation Kit

・Data Studio フル・クライアント

DB2管理者（クライアント）

Optim Query Workload Tuner 製品コンポーネント

DB2

LUW

DB2

LUW

OPM

レポジトリ

Optim Performance Manager サーバ

OPM Webコンソール

OPMで取得したSQLステートメントやワーロードファイルを取得

定常的にモニタリングし、データベース稼動状況を監視、モニター情報を収集

Data Studio

Optim Query Workload Tunerの構成

Activation Kit 導入環境にはData Studioフル・クライアントが必須

OQWTライセンスをアクティブにするための設定が必要 (Data

Studio もしくはスクリプトを使用)

DB2開発/管理者（クライアント）

Data Studio

管理クライアント

（参考）

IBM InfoSphere Optim Query Workload Tuner、バージョン 3.1.1 のインストール

http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.qrytune.installconfig.doc/topics/ioqwt311_top.html

IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows バージョン 3.1.1 クイック・スタート・ガイド

http://www-01.ibm.com/support/docview.wss?uid=swg27024586&aid=21

V3.1.1より、Query Tuner Client

の代わりに、Data Studioをクライアントとして使用

V3.1.1より、Query Tuner Client

の代わりに、Data Studioをクライアントとして使用







参考) OQWTはData Studioのアドオン

1 .[分析とチューニング] ＞[チューニング用に構成]＞[ガイド付構成] からOQWT

の使用を有効化

2. [チューニングの開始] でOQWTを使用開始

• データソース・エクスプローラーのデータベース選択時の右クリックメニューからOQWTの使用を有効化


OQWTによるクエリーチューニングの流れ

モニター

起動

分析

アドバイス

実行

0. OPMなどを利用し、ハイコストなSQLステートメントをモニター

1 . 問題のSQLを特定し、OQWTを起動

2 . 現状の統計情報を下に、アクセ

スプランを分析

3 . OQWTから各種アドバイザを起動、最

適なアクセスプランや照会のアドバイスを実施

4 . 各種アドバイザのアドバイス内容を実施し、

アクセスプランの比較・検証を実施

改善！


OQWTによるSQLチューニング例

• チューニング対象のSQLステートメント入力

照会テキストを直接入力(コピー可)

チューニング対象と入力方法として様々な手段がある


• ツール、アドバイザーの選択

この例では[すべてを選択]

分析ツールの選択

アドバイス内容を選択

サマリーレポートの

生成の選択



•OQWTのアドバイス結果の表示

優先順位をつけた形でSQLチューニングのアドバイスが列挙される



• 推奨情報の実行／統計アドバイザの結果表示と実行各アドバイザの概要の表示

複数アドバイザ結果がある場合は、高い効果が期待できる順に出力される

統計アドバイザの詳細項目

アドバイザ項目をクリックすると、その詳細と共に実行すべきDDL文を作成、表示可能

内容を確認次第、DDLを実行することが可能



• 索引アドバイザの結果表示と実行

索引アドバイザの詳細項目

推奨索引の各項目説明が羅列される

具体的に実行されるされるDDL文が表示され、内

容確認後実行することが可能



• アドバイス内容の実施の前と後でアクセスプランの比較検証

索引作成前索引作成後



参考) OPMとの連携

• OPMのSQLステートメント詳細[チューニング]ボタンOQWT起動できる

OQWT(DataStudio上)

起動&SQL入力


まとめ

Data Studio

• 無償提供 DB運用管理とアプリケーション開発の統合GUIツール

• 他のEclipseベースの開発環境と共有して使用可能

• DB2 9.7以前のコントロールセンターから置き換わるもの

Optim Query Workload Tuner

•DB2 V10.1 AESE 同梱高度なSQLチューニングツール

•複数のアドバイザーによる推奨情報の提供と適用


•DB2 V10.1 AESE 同梱パフォーマンス管理GUIツール

•ブラウザ・インターフェースによる複数DB環境の一元管理と簡単操作

•DBアプリケーションのエンド・ツー・エンドモニタリング(Extended Insight)


(参考) Data Studio 無償ダウンロードサイトのご紹介

• Data Studio 無償ダウンロードサイト

developerWorks IBM Data Studio: Available at no charge

http://www.ibm.com/developerworks/downloads/im/data/

http://www.ibm.com/developerworks/downloads/im/data/


• IBM Data Studio v3.1.1/IBM InfoSphere Optim Query Workload Tuner v3.1.1 (マニュアル) http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.ds.nav.doc/topics/helpindex_

ds.html

• InfoSphere Optim Performance Manager v5.1 (マニュアル)

http://publib.boulder.ibm.com/infocenter/perfmgmt/v5r1/index.jsp

• IBM Data Studio 入門 ― DB2運用管理の新GUIツール

http://www.ibm.com/developerworks/jp/data/library/optim/data_studio_intro/download.html

• developerWorks 『Optim Performance Manager 構築ガイド』

http://www.ibm.com/developerworks/jp/data/products/optim/opm_structure_guide.html

• IBM REDBOOKS ”IBM Optim Performance Manager for DB2 for Linux, UNIX, and

Windows”

http://www.redbooks.ibm.com/abstracts/sg247925.html

• (社内) ザ・技術 SIL 『DB2パフォーマンス管理ツール構築・利用ガイド(OPM/OQWT)』

http://w3-06.ibm.com/jp/domino02/ise/iseinfo.nsf/604755fe61c93ffc492563fd002551ec/d4ce853d03aaff4949257a59001f6c17?OpenDocument

57

Ｎｅｗ！ V5.1.1 対応。

近日developerWorks公開予定です。

(参考) Data Studio, OPM, OQWT参考資料のご紹介

旧バージョンのガイドです。

基本的なUIや機能は参考にし

ていただけます。

旧バージョンのガイドです。

基本的なUIや機能について参考にしていただけます。

http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.ds.nav.doc/topics/helpindex_ds.html

http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.ds.nav.doc/topics/helpindex_ds.html

http://publib.boulder.ibm.com/infocenter/perfmgmt/v5r1/index.jsp

http://www.ibm.com/developerworks/jp/data/products/optim/opm_structure_guide.html

http://www.redbooks.ibm.com/abstracts/sg247925.html





タイムトラベル照会


タイム・トラベル照会のポジション

• この機能が必要となる背景

• 時間データに対するニーズの高まり

• 時間データを開発・管理する為のコスト

- 現状

- ビジネスロジックに固有の時間管理方法で時間データを表に追加

- レコード追加/変更/削除履歴保存の為のアプリケーションやトリガーの作成

- 現在データと履歴データを使用した独自の照会アプリケーションの開発

データベース

3月31日24:00時点の売り上げ集計を行いたい

監査のために過去5年間の顧客レコードの更新履歴を知りたい

自動車事故が起きた時点での保険契約条件を知りたいお客様への請求金額に

カード入会３ヶ月期間の割引が適用されていなかったことが請求後にわかった。過去に遡って金額を訂正し計算しなおしたい


新機能：テンポラル表

• 機能特徴

• ユーザー・データに時間情報を付加する新しい機能

• アプリケーションのロジックではなく、DB2の機能で時間的要素を持つデータを格納/蓄積

• 3種類のテンポラル表を提供

• システム期間テンポラル表

• アプリケーション期間テンポラル表

• バイテンポラル表

• お客様にとっての価値

• 現在のデータだけではなく、データ収集が開始された時間以降であれば、任意の期間のデータへアクセス可能

• 監査のためにデータの更新履歴をトラッキング可能

• 誤って削除・更新されたレコードのみを復旧可能

• 時間データの管理を行うためのアプリケーション開発の負荷軽減


システム期間テンポラル表（System-period Temporal Table）

• レコードの履歴管理を行うためのテンポラル表

• 基礎表と同レイアウトの履歴表を持ち、履歴表はユーザーが作成する

• 基礎表のレコードが更新/削除されると、beforeイメージのデータがDB2により履歴表に蓄積される

• 基礎表、履歴表の全ての行は、3つの特別な列を持つ

• SYSTEM_TIME開始列：レコード作成時間

• SYSTEM_TIME終了列：レコード削除時間

• トランザクション開始ID列: トランザクションを識別するための時間

• SYSTEM_TIMEはDB2によって自動的に挿入/更新される

• SYSTEM_TIMEを指定した照会は、自動的に基礎表および履歴表を参照し、指定された時間に有効なデータが返される


システム期間テンポラル表(イメージ図)

UPDATE travel_sys SET destination = „THAILAND‟ WHERE trip_name = „ASIA_TOUR‟;

• 基礎表にUpdate、DeleteのSQLが流れると、beforeイメージの情報をDB2が履歴表に挿入してくれる

• 例：destinationを‟TAIWAN‟から‟THAILAND‟に変更した場合、元の‟TAIWAN‟の情報は履歴表に保持される

• 例：基礎表のdestinationは‟THAILAND‟だが、2012年6月7日を指定してselectすると‟TAIWAN‟が戻される

①update Current Date = June 8, 2012

trip_name destination departure_date price sys_start sys_end

ASIA_TOUR THAILAND 2012/09/01 400.00 2012-06-08 9999-12-30


ASIA_TOUR TAIWAN 2012/09/01 400.00 2012-06-07 2012-06-08

基礎表

履歴表

after ①Updateされた‟TAIWAN‟

はDB2により履歴表に書き込まれる

新しいシステム期間が挿入される(updateされた時間）

②select SELECT destination FROM travel_sys FOR SYSTEM_TIME AS OF ‟2012-06-07‟ ;

②’TAIWAN’

が返される


ASIA_TOUR TAIWAN 2012/09/01 400.00 2012-06-07 9999-12-30

基礎表

before 実際にはTIMESTAMPですが、この図では

見やすくするために日付にしています


1. SYSTEM_TIME属性を指定して基礎表を作成する

2. 履歴表を作成する

3. システムテンポラル表に履歴表を関連付ける

CREATE TABLE travel_sys(

trip_name CHAR(30) NOT NULL PRIMARY KEY,

destination CHAR(50) NOT NULL,

departure_date DATE NOT NULL,

price DECIMAL (8,2) NOT NULL,

sys_start TIMESTAMP(12) NOT NULL generated always as row begin implicitly hidden,

sys_end TIMESTAMP(12) NOT NULL generated always as row end implicitly hidden,

tx_start TIMESTAMP(12)NOT NULL generated always as transaction start id implicitly hidden,

PERIOD SYSTEM_TIME (sys_start, sys_end));

CREATE TABLE travel_sys_history LIKE travel_sys with RESTRICT ON DROP;

ALTER TABLE travel_sys

ADD VERSIONING USE HISTORY TABLE travel_sys_history;

レコードの有効期間の開始終了時点を表す列

DB2が値を自動生成することを明示指定

トランザクションを識別するtimestamp

一つのトランザクション中で複数列が更新された場合は同一

SYSTEM_TIMEを構成する列を指定

基礎表と同じ列構成

基礎表を削除したときに履歴表も同時に消されたくない場合指定

システム期間テンポラル表の定義


アプリケーション期間テンポラル表（Application-period Temporal Table）

• ビジネスとしてレコードの有効期限を管理するためのテンポラル表

• 例：契約開始、人事異動発効など

• 全ての行は、BUSINESS_TIME開始列、BUSINESS_TIME終了列を持つ

• BUSINESS_TIME開始列：ビジネスとしての有効期限開始日時

• BUSINESS_TIME終了列：ビジネスとしての有効期限終了日時

• BUSINESS_TIMEはアプリケーションが挿入/更新する

• BUSINESS_TIMEに基づく照会ができる

• BUSINESS_TIME期間を指定してレコードを更新/削除すると、整合性を保つように自動的に既存レコードの分割更新が行われる

• DB2 は、あるBUSINESS_TIME期間中でprimary keyがユニークであることを保証する（制約を作成する）

• システム期間テンポラル表のように履歴表を別表として作成する必要はない


アプリケーション期間テンポラル表(イメージ図)

• 1つの新しい旅行を2012/06/08をビジネス期間の開始として追加する

• 旅行名： JAPAN_TOUR （出発日： 2012/12/31）

trip_name destination departure_date price bus_start bus_end

JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-06-08 2013-01-01

ビジネス期間

Current Date = June 8, 2012

INSERT INTO travel_app VALUES (

‘JAPAN_TOUR’,’KYOTO’,’2012-12-31’,1500.00,’2012-06-08’,’2013-01-01’);

アプリケーションによって、bus_start列およびbus_end列にデータを挿入する

システム期間テンポラル表とは異なり、DB2によるデータの挿入は行われない



• 旅行「JAPAN_TOUR」の売れ行きがよくないため、8月に特別プライス（$1000.00）で旅行を提供するように変更


JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-06-08 2012-08-01

JAPAN_TOUR KYOTO 2012-12-31 1000.00 2012-08-01 2012-09-01

JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-09-01 2013-01-01

ビジネス期間

Current Date = July 15, 2012

UPDATE travel_app FOR PORTION OF BUSINESS_TIME FROM „2012-08-01‟ TO ‟2012-09-01‟

SET price = 1000.00 WHERE trip_name = „JAPAN_TOUR‟;

DB2により内部的に挿入、更新が行われる

履歴表は使用されないため、以前のレコードもTravel_app表で保管される

ビジネス期間



• アプリケーション期間テンポラル表に任意の時間を指定してselectすることにより、その時間(BUSINESS_TIME)の情報を返してくれる

• 例：任意の時間①、②を指定してselectすることで、返ってくるpriceの結果が異なる


JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-06-08 2012-08-01

JAPAN_TOUR KYOTO 2012-12-31 1000.00 2012-08-01 2012-09-01

JAPAN_TOUR KYOTO 2013-12-31 1500.00 2012-09-01 2013-01-01

Current Date = May 15, 2012

SELECT price FROM travel_app FOR BUSINESS_TIME AS OF ’2012-08-15’

WHERE trip_name = ‘JAPAN_TOUR’;



①

②

travel_app表

ビジネス期間の境目をselectすると、ビジネス開始列が該当します

例：ビジネス期間 ‟2012-08-01‟を指定して、priceを照会すると‟1000.00‟が返されます


アプリケーション期間テンポラル表の定義

• BUSINESS_TIME属性を指定して表を作成する

CREATE TABLE travel_app

(trip_name CHAR(25) NOT NULL,



price DECIMAL(8,2) NOT NULL,

bus_start DATE NOT NULL,

bus_end DATE NOT NULL,

PERIOD BUSINESS_TIME (bus_start, bus_end),

PRIMARY KEY (trip_name, BUSINESS_TIME WITHOUT OVERLAPS));

各レコードの有効期間の開始と終了を表す列（DATA型 or TIMESTAMP型）

BUSINESS_TIMEにオーバーラップが生じないことをDB2にチェックさせるための指定

BUSINESS_TIMEとtrip_nameの組み合わせはユニークとなる

→ある期間に有効な同一名の旅行は一つ


バイテンポラル表

• システム期間テンポラル表とアプリケーション期間テンポラル表を組み合わせた表

• レコードごとにSYSTEM_TIMEとBUSINESS_TIMEを持つ

• ユーザーは、SYSTEM_TIMEとBUSINESS_TIMEの両方を参照可能

• 例： 2012/05/10に登録された2012/6/20時点で有効な旅行名を照会する

trip_name destination departure_date price bus_start bus_end sys_start sys_end

US_TOUR LA 2012-02-15 2000.00 2012-02-01 2013-01-01 2012-02-01 9999-12-30

SELECT trip_name, destination FROM TRAVEL_BI FOR BUSINESS_TIME AS

OF ’2012-06-20’ FOR SYSTEM_TIME AS OF ’2012-05-10’;

• INSERT/UPDATE/DELETEでは、アプリケーション期間テンポラル表と同様に、データ挿入、分割、削除が必要に応じて行われる

• UPDATE/DELETEでは、システム期間テンポラル表と同様に履歴表へのデータ蓄積が自動的に行われる

• SELECTでは、履歴表からのレコード取得が必要に応じて行われる

履歴表へのデータ挿入、sys_start列 sys_end列への値追加はDB2が自動的に行う


バイテンポラル表(イメージ図)

UPDATE travel_bi SET departure_date = ‟2012-09-15‟, bus_end = ‟2012-09-16‟

WHERE trip_name = „US_TOUR‟ and departure_date = ‟2012-10-15‟;

• 基礎表にUpdate、DeleteのSQLが流れると、beforeイメージの情報をDB2が履歴表に挿入してくれる。Selectでは、BUSINESS_TIMEとSYSTEM_TIMEを指定可能

• 例：①updateすることによって、beforeイメージが履歴表に記録される

• 例：②BUSINESS_TIMEとSYSTEM_TIME両方を指定し、参照可能

①update

Current Date = February 2, 2012

②select SELECT departure_date FROM travel_bi FOR BUSINESS_TIME AS OF ‟2012-06-01‟

FOR SYSTEM_TIME AS OF TIMESTAMP „2012-02-01‟ WHERE trip_name = „US_TOUR‟ ②’2012-10-15’ が返される

after 基礎表

履歴表


US_TOUR LA 2012-05-15 2000.0

0

2012-02-16 2012-05-16 2012-02-01 9999-12-30

US_TOUR LA 2012-09-15 2000.0 2012-05-16 2012-09-16 2012-02-02 9999-12-30


US_TOUR LA 2012-10-15 2000.0 2012-05-16 2012-10-16 2012-02-01 2012-02-02

①bus_endが変更され、updateされた時の時間がsys_startに記載される


US_TOUR LA 2012-05-15 2000.0

0

2012-02-16 2012-05-16 2012-02-01 9999-12-30

US_TOUR LA 2012-10-15 2000.0 2012-05-16 2012-10-16 2012-02-01 9999-12-30

before 基礎表


バイテンポラル表の定義

CREATE TABLE travel_bi(

trip_name CHAR(25) NOT NULL,



price DECIMAL(8,2) NOT NULL,

BUS_START DATE NOT NULL ,

BUS_END DATE NOT NULL,

SYS_START TIMESTAMP(12) NOT NULL

GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN,

SYS_END TIMESTAMP(12) NOT NULL

GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN,

TX_ID TIMESTAMP(12) NOT NULL

GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,

PERIOD SYSTEM_TIME (SYS_START, SYS_END),

PERIOD BUSINESS_TIME (BUS_START, BUS_END),

PRIMARY KEY (trip_name, BUSINESS_TIME WITHOUT OVERLAPS));

CREATE TABLE travel_history LIKE travel with RESTRICT ON DROP;

ALTER TABLE travel_bi ADD VERSIONING USE HISTORY TABLE travel_history;

アプリケーション期間テンポラル表のキーワードを指定

システム期間テンポラル表のキーワードを指定


Note:SYSCAT.TABLES

• 下記のシステム・カタログ・ビューを照会することにより、表にテンポラル・サポートがあるかどうかを確認できます

• SYSCAT.TABLES (TEMPORALTYPE 列)

• テンポラル表のタイプ

• A = アプリケーション期間テンポラル表

• B = バイテンポラル表

• N = テンポラル表ではない

• S = システム期間テンポラル表

データ・タイデータ・列のスケ

列名プ・スキーマタイプ名長さール NULL

------------------------------- --------- ------------------- ---------- ----- ------

TABSCHEMA SYSIBM VARCHAR 128 0 いいえ

TABNAME SYSIBM VARCHAR 128 0 いいえ

＜省略＞

CONTROL SYSIBM CHARACTER 1 0 いいえ

TEMPORALTYPE SYSIBM CHARACTER 1 0 いいえ

REMARKS SYSIBM VARCHAR 254 0 はい


Note:

こんなリンクもつける

db2 select substr(TABSCHEMA,1,20) TABSCHEMA,substr(TABNAME,1,20) TABNAME,substr(OWNER,1,20) OWNER,TEMPORALTYPE from syscat.tables where TABSCHEMA ='AHA03926'

TABSCHEMA TABNAME OWNER TEMPORALTYPE

-------------------- -------------------- -------------------- ------------

AHA03926 TRAVEL_SYS AHA03926 S

AHA03926 TRAVEL_SYS_HISTORY AHA03926 N

AHA03926 TRAVEL_BI AHA03926 B

AHA03926 TRAVEL_BI_HISTORY AHA03926 N

AHA03926 TRAVEL_APP AHA03926 A

AHA03926 AUDIT AHA03926 N

履歴表はTYPE=„N‟になる


Note:SYSCAT.PERIODS

• 下記のシステム・カタログ・ビューを照会することにより、テンポラル表と履歴表の結びつきがわかります。

• SYSCAT.PERIODS (HISTORYTABSCHEMA列、HISTORYTABNAME列)

データ・タイデータ・列のスケ

列名プ・スキーマタイプ名長さール NULL

------------------------------- --------- ------------------- ---------- ----- ------

PERIODNAME SYSIBM VARCHAR 128 0 いいえ

TABSCHEMA SYSIBM VARCHAR 128 0 いいえ

TABNAME SYSIBM VARCHAR 128 0 いいえ

BEGINCOLNAME SYSIBM VARCHAR 128 0 はい

ENDCOLNAME SYSIBM VARCHAR 128 0 はい

PERIODTYPE SYSIBM CHARACTER 1 0 いいえ

HISTORYTABSCHEMA SYSIBM VARCHAR 128 0 はい

HISTORYTABNAME SYSIBM VARCHAR 128 0 はい


Note:

こんなリンクもつける

db2 select substr(PERIODNAME,1,16) as PERIODNAME,substr(TABNAME,1,16) TABNAME,PERIODTYPE,substr(HISTORYTABSCHEMA,1,16) HISTORYTABSCHEMA,substr(HISTORYTABNAME,1,20) HISTORYTABNAME from syscat.periods

PERIODNAME TABNAME PERIODTYPE HISTORYTABSCHEMA HISTORYTABNAME

---------------- ---------------- ---------- ---------------- --------------------

SYSTEM_TIME TRAVEL_SYS S AHA03926 TRAVEL_SYS_HISTORY

BUSINESS_TIME TRAVEL_APP A - -

SYSTEM_TIME TRAVEL_BI S AHA03926 TRAVEL_BI_HISTORY

BUSINESS_TIME TRAVEL_BI A - -

履歴表がどの表に紐づいているのかがわかる


Note:システム期間テンポラル表への変更

• 既存表にTIMESTAMP型の列を3つ定義する

– 新規列の追加、もしくは既存列の変更を行い、SYSTEM_TIME開始列、 SYSTEM_TIME終了列、トランザクション開始ID列を定義する

– アプリケーションから列を見えないようにするにはIMPLICITLY HIDDENオプションを指定する

– 履歴表が存在しない場合

– 履歴表を新規作成する

– 既存表と同様、履歴表にTIMESTAMP型の列を3つ定義する

– 履歴表が既に存在する場合

– TIMESTAMP列が、既存表に追加定義したTIMESTAMP列と同型であるかを確認する型が異なる場合は、新規に履歴表を作成してLOAD REPLACEでデータ移行するTIMESTAMP列にGENERATED ALWAYSオプションを使用することで、LOAD元にあるデータをオーバーライドすることが可能

ALTER TABLE test ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;

ALTER TABLE test ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;

ALTER TABLE test ADD COLUMN tran_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION

START ID;

CREATE TABLE testtbl_hist LIKE testtbl;


Note:システム期間テンポラル表への変更(続き)

– 基礎表に対してALTER TABLEステートメントを使用して、SYSTEM_TIMEを構成する列を指定する

– 基礎表に履歴表を関連付ける

– アプリケーションが実行するSQLのシンタックスを、システム期間テンポラル表を使用できるように変更する

– FOR SYSTEM TIME AS OF… 等

ALTER TABLE testtbl ADD PERIOD system_time(sys_start,sys_end);

ALTER TABLE testtbl ADD VERSIONING USE HISTORY TABLE testtbl_hist;


Note:アプリケーション期間テンポラル表への変更

• 既存表にTIMESTAMP型もしくはDATE型の列を2つ定義する

– 新規列の追加、もしくは既存列の変更を行い、BUSINESS_TIME開始列、

BUSINESS_TIME終了列を定義する

• 基礎表に対してALTER TABLEステートメントを使用して、BUSINESS_TIMEを構成する列を指定する

• （オプション）あるBUSINESS_TIME期間中でprimary keyがユニークであることを保証するにはPRIMARY KEY制約を定義する

• アプリケーションが実行するSQLのシンタックスを、システム期間テンポラル表を使用できるように変更する

• FOR BUSINESS TIME BETWEEN… 等

ALTER TABLE testtbl2 ADD COLUMN bus_start TIMESTAMP default NOT NULL;

ALTER TABLE testtbl2 ADD COLUMN bus_end TIMESTAMP default NOT NULL;

ALTER TABLE testtbl2 ADD PERIOD BUSINESS_TIME(bus_start, bus_end);

ALTER TABLE testtbl2 ADD CONSTRAINT uniq UNIQUE(C1, BUSINESS_TIME WITHOUT

OVERLAPS)


Note:テンポラル表へのビュー

• テンポラル表へは2種類のビューを定義可能

• ビュー定義にBUSINESS_TIMEもしくはSYSTEM_TIMEを含める

• ある時点または期間を見せるビューを作成可能

• ビューに対するSELECTには、SYSTEM_TIME、BUSINESS_TIMEを再び指定することはできない

• ビュー定義にBUSINESS_TIMEもしくはSYSTEM_TIMEを含めない

• ビューに対するSQLで時間指定可能

CREATE VIEW travel_view AS SELECT * FROM travel FOR SYSTEM_TIME BETWEEN '2012-06-01' and '2012-08-31‘;

SELECT * FROM travel_view;

CREATE VIEW testtbl_view AS SELECT * FROM testtbl;

SELECT * FROM testtbl_view FOR SYSTEM TIME AS OF 2012-08-25’;


Note:特殊レジスター

• アプリケーションやストアドプロシージャを変更することなく、時間を変更して処理させることが可能

• 特殊レジスター

• CURRENT TEMPORAL BUSINESS_TIME

• CURRENT TEMPORAL SYSTEM_TIME

• 照会処理時に１つもしくは両方のレジスターを設定

• システム期間：過去の特定時間を指定可能

• ビジネス期間：過去もしくは将来の特定時間を指定可能

• SQLステートメントでは特殊レジスターの値が暗黙的に使用される

• FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME

• FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME

DB2 SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP – 1 YEAR DB2 SET CURRENT TEMPORAL BUSINESS_TIME = ‘2012-12-31’



• テーブルの状態

• 現在の表の状態をselect

db2 select * from testtbl

C1 C2 SYS_START SYS_END

----------- ---- -------------------------------- --------------------------------

1 TEST 2012-08-27-23.32.02.957562000000 9999-12-30-00.00.00.000000000000

2 BBB 2012-08-22-10.03.16.202822000000 9999-12-30-00.00.00.000000000000

db2 select * from testtbl_hist


----------- ---- -------------------------------- --------------------------------

1 AAA 2012-08-22-10.02.31.152059000000 2012-08-27-23.32.02.957562000000

db2 set current temporal system_time = current timestamp

DB20000I SQL コマンドが正常に完了しました。



----------- ---- -------------------------------- --------------------------------

1 TEST 2012-08-27-23.32.02.957562000000 9999-12-30-00.00.00.000000000000

2 BBB 2012-08-22-10.03.16.202822000000 9999-12-30-00.00.00.000000000000

Current timestampをsetすると現在の状態を返す

(C2=TESTの行)



• SET CURRENT TEMPORAL SYSTEM_TIMEで3日前の時間に設定してselectを行う

db2 set current temporal system_time = current timestamp - 3 day

DB20000I SQL コマンドが正常に完了しました。



----------- ---- -------------------------------- --------------------------------

1 AAA 2012-08-22-10.02.31.152059000000 2012-08-27-23.32.02.957562000000

2 BBB 2012-08-22-10.03.16.202822000000 9999-12-30-00.00.00.000000000000

同じselectを行っていても、CURRENT TEMPORAL

SYSTEM_TIMEを3日前にsetすると、C2にはAAAが入る


まとめ

タイムトラベル照会

•ユーザー・データに時間情報を付加する新しい機能

•アプリケーションのロジックではなく、DB2の機能で時間的要素を持つデータを格納/蓄積

•時間データの管理を行うためのアプリケーション開発の負荷軽減

•現在のデータだけではなく、データ収集が開始された時間以降であれば、任意の期間のデータへアクセス可能

•3種類のテンポラル表を提供

•システム期間テンポラル表

•アプリケーション期間テンポラル表

•バイテンポラル表


テンポラル表 I/U/D 検証 (参考)

当項目は、資料のみのご紹介です。


• 2つの新しい旅行を2012/06/07に追加する

• 旅行名： LONDON （出発日：2012/10/28）

• 旅行名： ASIA_TOUR（出発日： 2012/09/01）

INSERT INTO travel_sys VALUES (‘EURO_TOUR’,’LONDON’,’2012-10-28’,1000.00);

INSERT INTO travel_sys VALUES (‘ASIA_TOUR’,‘TAIWAN’,’2012-09-01’,400.00);



EURO_TOUR LONDON 2012-10-28 1000.00 2012-06-07 9999-12-30

ASIA_TOUR TAIWAN 2012-09-01 400.00 2012-06-07 9999-12-30

sys_start と sys_end 列のデータはDB2によって自動設定される。（アプリケーションはこの列の存在を意識しなくてよい） ※ 実際にはTIMESTAMPですが、この図では見やすくするために日付になっています

この行が有効な日付

(inclusive, exclusive)

(参考) システム期間テンポラル表へのデータ挿入


(参考) システム期間テンポラル表でのデータ更新

• 旅行「ASIA_TOUR」の目的地が「TAIWAN」から「THAILAND」に変更された場合

• 履歴表へのデータ修正はDB2が自動的に行う


EURO_TOUR LONDON 2012-10-28 1000.00 2012-06-07 9999-12-30

ASIA_TOUR THAILAND 2012-09-01 400.00 2012-06-08 9999-12-30

UPDATE travel_sys SET destination = ‘TAIWAN’

WHERE trip_name = ‘THAILAND’;


ASIA_TOUR TAIWAN 2012-09-01 400.00 2012-06-07 2012-06-08

システム期間


基礎表

履歴表


新しいシステム期間


(参考) システム期間テンポラル表からのデータ削除

• 旅行「ASIA_TOUR」の申し込みが取り消された場合


EURO_TOUR LONDON 2012-10-28 1000.00 2012-06-07 9999-12-30

DELETE FROM travel_sys WHERE trip_name = ‘ASIA_TOUR’;


ASIA_TOUR TAIWAN 2012-09-01 400.00 2012-06-07 2012-06-08

ASIA_TOUR THAILAND 2012-09-01 400.00 2012-06-08 2012-07-15

システム期間

基礎表

履歴表


Current Date = July 15, 2012

基礎表から「ASIA_TOUR」を削除

deleteされた時間が挿入される


(参考) システム期間テンポラル表への照会

• 2012/06/08時点で $500未満で提供される旅行名を照会する

• 照会結果: ASIA_TOUR

• 現在に利用可能なLONDONへの旅行名を照会する

• 照会結果: EURO_TOUR

• 2012年に申し込まれた旅行数を照会する

• 過去に提供されていた旅行を含めての旅行申し込み数の照会

• 照会結果： 2

SELECT trip_name FROM travel_sys FOR SYSTEM_TIME AS OF ’2012-06-08’

WHERE price < 500.00;

SELECT trip_name FROM travel_sys WHERE destination = ‘LONDON’;

SELECT COUNT (DISTINCT trip_name) FROM travel_sys

FOR SYSTEM_TIME BETWEEN ’2012-01-01’ AND ’2013-01-01’;

通常のselectでは、基礎表のみが照会される

（「FOR SYSTEM TIME AS OF CURRENT TIMESTAMP(DATE)」指定時と同じ動作）


(参考)アプリケーション期間テンポラル表のユニーク性の保証

• 旅行「JAPAN_TOUR」が売り切れになったため、2012/10/01をビジネス開始期間とする新しい旅行枠を追加して、2013年末まで利用可能に設定する


JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-06-08 2012-08-01

JAPAN_TOUR KYOTO 2012-12-31 1000.00 2012-08-01 2012-09-01

JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-09-01 2013-01-01

ビジネス期間がオーバーラップするレコードが存在するため、データの挿入が失敗する

Current Date = Sept 1, 2012

INSERT INTO travel_app VALUES ('JAPAN_TOUR','KYOTO','2012-11-15',1500.00,'2012-10-01','2013-01-01') DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQLステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。SQL0803N INSERT ステートメント、UPDATE ステートメントの 1 つ以上の値、およびDELETE ステートメントが原因で発生した外部キーの更新は無効です。これは、"1"で識別される主キー、ユニーク制約、またはユニーク索引が表 "AHA03926.TRAVEL_APP" が索引キーに対して重複する値を持つことを制限しているためです。 SQLSTATE=23505

エラー例

INSERT INTO travel_app VALUES

(„JAPAN_TOUR‟,‟KYOTO‟,‟2012-11-15‟,1500.00,‟2012-10-01‟,‟2013-01-01‟);


(参考)アプリケーション期間テンポラル表のユニーク性の保証

• 前頁のエラーを回避するには、新しい旅行名「JAPAN_TOUR 2」を使用したデータ挿入を行う

INSERT INTO travel_app VALUES (

‘JAPAN_TOUR 2’,’KYOTO’, ’2012-11-15’,1500.00,’2012-10-01’,’2013-01-01’);


JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-06-08 2012-08-01

JAPAN_TOUR KYOTO 2012-12-31 1000.00 2012-08-01 2012-09-01

JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-09-01 2013-01-01

JAPAN_TOUR 2 KYOTO 2012-11-15 1500.00 2012-10-01 2013-01-01

ビジネス期間

新しいデータの挿入に成功！



(参考) アプリケーション期間テンポラル表への照会

• 2012/08/15時点での旅行「JAPAN_TOUR」の費用を照会する

• 照会結果: 1000.00

• 2012年での旅行「JAPAN_TOUR」の最低価格を照会する

• 照会結果: 1000.00

• 10月に利用可能な旅行名を照会する

• 照会結果:

• JAPAN_TOUR

• JAPAN_TOUR 2



SELECT MIN (price) FROM travel_app FOR BUSINESS_TIME FROM ’2012-01-01’

TO ’2013-01-01’ WHERE trip_name = ‘JAPAN_TOUR’;

SELECT trip_name FROM travel_app

FOR BUSINESS_TIME BETWEEN ’2012-10-01’ AND ’2012-11-01’;





(参考) アプリケーション期間テンポラル表からのデータ削除

• JAPAN_TOUR行きの飛行機が故障してしまった為、JAPAN_TOURへの旅行の提供を、2012/09/15時点で中止する

DELETE FROM travel_app FOR PORTION OF BUSINESS_TIME FROM ’2012-09-15’

TO ’9999-12-30’ WHERE trip_name LIKE ‘JAPAN_TOUR%’;


JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-06-08 2012-08-01

JAPAN_TOUR KYOTO 2012-12-31 1000.00 2012-08-01 2012-09-01

JAPAN_TOUR KYOTO 2012-12-31 1500.00 2012-09-01 2012-09-15

ビジネス期間

・DB2は、 trip_name列の値が「JAPAN_TOUR」のbus_end列の値を「2012-09-15」に更新する・trip_name列の値が「JAPAN_TOUR 2」のレコードについては、ビジネス期間全体が2012/09/15以降であるため、レコードを削除する



(参考) バイテンポラル表へのデータ挿入

• 新しい旅行を2012/02/01に追加する

• US_TOUR （出発日：2012/02/15、2012/05/15、2012/10/15）

• 出発日が2012/02/15のレコードを挿入する

• さらに、出発日が2012/05/15、2012/10/15のレコードを挿入する


US_TOUR LA 2012-02-15 2000.00 2012-02-01 2012-02-16 2012-02-01 9999-12-30

US_TOUR LA 2012-05-15 2000.00 2012-02-16 2012-05-16 2012-02-01 9999-12-30

US_TOUR LA 2012-10-15 2000.00 2012-05-16 2012-10-16 2012-02-01 9999-12-30

INSERT INTO TRAVEL_BI VALUES (‘US_TOUR’, ‘LA’, ’2012-02-15’,

2000.00, ’2012-02-01’, ’2012-02-16’);

sys_start列およびsys_end列はアプリケーションではなく、DB2が挿入する



(参考) バイテンポラル表でのデータ更新

• 2012/02/02に、出発日が10/15の旅行プランを9/15に変更して、旅行プランの終了日を9/16に変更する

UPDATE travel_bi SET departure_date = ’2012-09-15’, bus_end = ’2012-09-

16’’ WHERE trip_name = ‘US_TOUR’ and departure_date = ’2012-10-15’;

基礎表

履歴表


US_TOUR LA 2012-02-15 2000.00 2012-02-01 2012-02-16 2012-02-01 9999-12-30

US_TOUR LA 2012-05-15 2000.00 2012-02-16 2012-05-16 2012-02-01 9999-12-30

US_TOUR LA 2012-09-15 2000.00 2012-05-16 2012-09-16 2012-02-02 9999-12-30


US_TOUR LA 2012-10-15 2000.00 2012-05-16 2012-10-16 2012-02-01 2012-02-02

新しいbus_end列の値は、アプリケーションで指定する

DB2はsys_start列をupdateした時間に更新して、履歴表にデータを挿入する



(参考) バイテンポラル表からのデータ削除

• 2012/06/11において、「US_TOUR」の旅行ガイドが辞めてしまったため、新しいガイドを雇うまでの3ヶ月間は、旅行プランを中断する

DELETE FROM travel_bi FOR PORTION OF BUSINESS TIME FROM ’2012-06-

11’ TO ‘2012-09-11’WHERE trip_name = ‘US_TOUR’

基礎表

履歴表


US_TOUR LA 2012-02-15 2000.00 2012-02-01 2012-02-16 2012-02-01 9999-12-30

US_TOUR LA 2012-05-15 2000.00 2012-02-16 2012-05-16 2012-02-01 9999-12-30

US_TOUR LA 2012-09-15 2000.00 2012-05-16 2012-06-11 2012-06-11 9999-12-30

US_TOUR LA 2012-09-15 2000.00 2012-09-11 2012-09-16 2012-06-11 9999-12-30


US_TOUR LA 2012-10-15 2000.00 2012-05-16 2012-10-16 2012-02-01 2012-02-02

US_TOUR LA 2012-09-15 2000.00 2012-05-16 2012-09-16 2012-02-02 2012-06-11


6月11日から9月11日の間、BUSSINESS_TIMEは有効ではない


(参考) バイテンポラル表への照会 • 2012/02/01に登録された旅行で、2012/03/01時点で利用可能な旅行「US_TOUR」の出発日を照会する

SELECT departure_date FROM travel_bi FOR BUSINESS_TIME AS OF ’2012-03-01’

FOR SYSTEM_TIME AS OF TIMESTAMP ‘2012-02-01-00.00.00.000000’

WHERE trip_name = ‘US_TOUR’

基礎表

履歴表


US_TOUR LA 2012-02-15 2000.00 2012-02-01 2012-02-16 2012-02-01 9999-12-30

US_TOUR LA 2012-05-15 2000.00 2012-02-16 2012-05-16 2012-02-01 9999-12-30

US_TOUR LA 2012-09-15 2000.00 2012-05-16 2012-06-11 2012-06-11 9999-12-30

US_TOUR LA 2012-09-15 2000.00 2012-09-01 2012-09-16 2012-06-11 9999-12-30


US_TOUR LA 2012-10-15 2000.00 2012-05-16 2012-10-16 2012-02-01 2012-02-02

US_TOUR LA 2012-09-15 2000.00 2012-05-16 2012-09-16 2012-02-02 2012-06-11


departure_date‟ 2012-05-15‟が返される

‟


その他のSQL/アプリケーション機能拡張

当カテゴリは、資料のみのご紹介です。

(参考)


• トリガーに関する拡張サポート

• 宣言済みのタイプとプロシージャー

• 新しいスカラー関数

• JDBC および SQLJ のサポートの機能拡張

• 組み込みグローバル変数による SQL プログラミング機能の拡張

• 汎用表関数

内容


トリガーに関する拡張サポートの概要

• 下記の機能拡張がDB2 V10.1 に追加（同様に V97 Fixpak4から使用可能)

• 複数イベント・トリガーのサポート

• CREATE TRIGGER ステートメントのトリガー・イベント節には、複数の操作を含めることができ、単一の節で UPDATE、DELETE、INSERT の操作を一緒に使用可能

• コンパウンド SQL (コンパイル済み) ステートメントを使用する場合のみ

• Before-TriggerでのIUDステートメント・サポート

• BEFORE-TRIGFERのSQL Body内で Insert/Update/Deleteが発行可能

• コンパウンド SQL (コンパイル済み) ステートメントを使用する場合のみ

• Nested Triggerも64階層までサポートされる

• FOR EACH STATEMENT サポートの制約解除 (PL/SQL)

• FOR EACH STATEMENT オプションは、PL/SQL トリガーの CREATE TRIGGER ステートメントでサポートされるようになりました


トリガーに関する拡張サポートの概要

• 複数イベント・トリガー

CREATE TRIGGER HIRED

AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE

REFERENCING NEW AS N OLD AS OLD FOR EACH ROW

BEGIN

IF UPDATING

THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;

END IF;

IF DELETING

THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP – 1;

END IF;

IF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))

THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'

END IF;

END;

INSERT/DELETE/

UPDATEを１つで定義

SYSCAT.TRIGGERS表のTRIGEVENT=„M‟ (通常はI/U/D) ,

EVENTUPDATE, EVENTDELETE,EVENTINSERTは‟Y‟で登録される


Before-TriggerでのIUDステートメント・サポート

• Before-TriggerでのIUDステートメント・サポート

CREATE TRIGGER REORDER

BEFORE UPDATE OF ON_HAND, MAX_STOCKED ON PARTS

REFERENCING NEW AS N

FOR EACH ROW

WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)

BEGIN

INSERT INTO ORDERS VALUES (N.MAX_STOCKED – N.ON_HAND, N.PARTNO);

CALL ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO);

END;

コンパウンド SQL (コンパイル済み)

ステートメントを使用


宣言済みのタイプとプロシージャーの概要

• ブロック内でのプロシージャ宣言が可能になりました

• ブロック内での型宣言が可能になりました

DECLARE PROCEDURE P() BEGIN ..END;

IS

BEGIN

P();

END;

DECLARE

TYPE emprec_typ IS RECORD(

empno NUMBER(4),ename VARCHAR(10));

var emprec_typ;

IS

BEGIN

var.empno = 5;

END;


(参考) 宣言済みのタイプとプロシージャーの概要

SET SERVEROUTPUT ON;

DECLARE

TYPE type_rec_test is RECORD( n NUMBER, curdate DATE );

rec1 type_rec_test;

PROCEDURE localproc1(

rec IN OUT type_rec_test) IS

BEGIN

rec.n := rec.n * 2;

rec.curdate := sysdate;

END localproc1;

BEGIN

rec1.n := 2;

localproc1(rec1);

dbms_output.put_line( rec1.n );

dbms_output.put_line( rec1.curdate );

END;

/

QUIT;

＜V9.7 FP5 実行例＞

データベース接続情報 :

---------------------------------

ホスト名 = server1

データベース・サーバー = DB2/LINUX SQL09075

SQL 許可 ID = db2v975

ローカル・データベース別名 = ORADB

ポート = 50002

行 8 の近くでエラー:

SQL0104N "PROCEDURE localproc1(" に続いて予期しないトークン "PROCEDURE" が見つかりました。予期されたトークンに "BEGIN" が含まれている可能性があります。

＜V10.1 GA 実行例＞

データベース接続情報 :

---------------------------------

ホスト名 = server1

データベース・サーバー = DB2/LINUX SQL10010

SQL 許可 ID = db2v10

ローカル・データベース別名 = ORADB

ポート = 50004

4

2012-06-05-14.13.59

DB250000I: コマンドは正常に完了しました。

ブロック内での

プロシージャ宣言

ブロック内での宣言型

• 検証で使用したプログラム


新しいスカラー関数の概要

• SYSIBM.INSTRB（） • ある文字列において、その中に含まれている別の検索対象文字列の開始位置をバイト単位で戻す

• SYSIBM.TO_SINGLE_BYTE（） • 文字列に含まれるマルチバイト文字列を同等のシングルバイト文字列に変換して戻す

• データベースのコードページはunicodeおよびIBM-943のみサポート

• UTF-8 コード・ポイントで U+0020 から U+007E の範囲の文字で表記される、等価 1 バイト文字が存在する文字のみが変換

• FOR BIT DATAはエラーとなる

• SYSIBM.TIMESTAMPDIFF（） • 2つのタイムスタンプの差を表す文字列に基づいて、第一引数によって定義されたタイプの間隔の見積数を

戻す。V9.7までのSYSFUN.TIMESTAMPDIFF関数も使用可能。

＜例＞

$ db2 "values( TIMESTAMPDIFF(16,

CHAR(TIMESTAMP('2010-03-01-13.25.30')-TIMESTAMP('2010-02-01-13.25.30'))))"

-----------

30

見積もりの仮定

• 365 days = 1 year.

• 30 days = 1 month.

• 24 hours = 1 day.

• 60 minutes = 1 hour.

• 60 seconds = 1 minute.

第一引数

1 Microseconds 32 Weeks

2 Seconds 64 Months

4 Minutes 128 Quarters

8 Hours 256 Years

16 Days


(参考) 検証新しいスカラー関数の概要

• INSTRB

• TO_SINGLE_BYTE

$ db2 "values(instr(G'あabc',G'b'), instrb(G'あabc',G'b'), length(G'あabc',octets))"

1 2 3

----------- ----------- -----------

3 5 8

$ db2 "values(instr( 'あabc', 'b'), instrb( 'あabc', 'b'), length( 'あabc',octets))"

1 2 3

----------- ----------- -----------

5 5 6

$ db2 "values(length('ＡA',octets),to_single_byte('ＡA'), length(to_single_byte('ＡA'),octets))"

1 2 3

----------- ---- -----------

4 AA 2

Instr関数は引数の型によって、単位となるバイト数が変化する

Instrb関数は常にバイト単位

マルチバイト混在文字列


(参考) 検証新しいスカラー関数の概要

• TIMESTAMPDIFF

• ORACLE互換モードOFF

• ORACLE互換モードON

$ db2 "values( TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2012-03-01-13.25.30')-TIMESTAMP('2012-02-01-13.25.30'))))"

1

-----------

30

$ db2 "values( SYSFUN.TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2012-03-01-13.25.30')-TIMESTAMP('2012-02-01-13.25.30'))))"

1

-----------

30

$ db2 "values( TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2012-03-01-13.25.30')-TIMESTAMP('2012-02-01-13.25.30'))))"

1

-----------

29

$ db2 "values( SYSFUN.TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2012-03-01-13.25.30')-TIMESTAMP('2012-02-01-13.25.30'))))"

1

-----------

0

第二パラメータは、タイムスタンプ期間（次ページ参照）

ORACLE互換モードONではSYSFUN版はサポートされない。（V9.7共通）また、第二パラメータは差を日数で表す（次ページ参照）ため、戻される見積もりが互換モードOFFの場合と異なる場合がある。


(参考)検証新しいスカラー関数の概要

• TIMESTAMPDIFF

• ORACLE互換モードOFF

• ORACLE互換モードON

db2 "values(timestamp('2012-03-01')-timestamp('2012-02-01'),timestamp('2012-02-01'))"

1 2

---------------------- --------------------------

100000000.000000 2012-02-01-00.00.00.000000

db2 "describe values(timestamp('2012-03-01')-timestamp('2012-02-01'),timestamp('2012-02-01'))"

列情報

列の数: 2

SQL タイプタイプ長列名名前長

-------------------- ----------- ------------------------------ -----------

484 DECIMAL 20, 6 1 1

392 TIMESTAMP 26 2 1

db2 "values(timestamp('2012-03-01')-timestamp('2012-02-01'),timestamp('2012-02-01'))"

1 2

------------------------------------------ --------------------------

29 2012-02-01-00.00.00.000000

db2 "describe values(timestamp('2012-03-01')-timestamp('2012-02-01'),timestamp('2012-02-01'))"

列情報

列の数: 2

SQL タイプタイプ長列名名前長

-------------------- ----------- ------------------------------ -----------

996 DECFLOAT 16 1 1

392 TIMESTAMP 26 2 1

Oracle互換のON/OFF

により、TIMESTAMP

の演算結果が異なる。


JDBC および SQLJ のサポートの機能拡張の概要

• システム・モニタリングの機能拡張

• DB2SystemMonitor.getServerTimeMicros によって戻されるサーバー時間に、コミットとロールバックの時間が含まれるようになりました。

• パスワードを変更する新しいメソッド

• com.ibm.db2.jcc.DB2Driver.changeDB2Password という新しいメソッドで、パスワードの有効期限が切れているかどうかにかかわらず、パスワードを変更することができます。

• 自動クライアントリルート、ワークロードバランシング関連プロパティのデフォルト値改善

• グローバル構成プロパティー

• Connection プロパティーと DataSource プロパティー

• V10.1より前

• 自動クライアントリルートが有効になっている環境でmaxRetriesForClientReroute、retryIntervalForClientRerouteの設定がなければ、10分間接続の再試行が繰り返される

• V10.1以降

• 接続サーバーがDB2 for z/OSで、enableSysplexWLB プロパティーが trueになっている環境は、以下のデフォルト値。それ以外の場合には、V10より前と同じデフォルト値

• maxRetriesForClientReroute=5

• retryIntervalForClientReroute=0

1 秒-1 (無制限)db2.jcc.maxTransportObjectWaitTime

1000-1 (無制限)db2.jcc.maxTransportObjects

10 秒30 秒db2.jcc.maxRefreshInterval

V10以降のデフォルト値V10より前のデフォルト値構成プロパティー

1 秒-1 (無制限)db2.jcc.maxTransportObjectWaitTime

1000-1 (無制限)db2.jcc.maxTransportObjects

10 秒30 秒db2.jcc.maxRefreshInterval

V10以降のデフォルト値V10より前のデフォルト値構成プロパティー


(参考)検証 DB2SystemMonitor.getServerTimeMicros (1/2) • 検証で使用したプログラム

import java.util.*; import java.sql.*; import com.ibm.db2.jcc.*; public class test5 { public static void main( String[] args ) throws Exception{ Class.forName("com.ibm.db2.jcc.DB2Driver"); Properties pro = new Properties(); pro.put("user",“db2admin"); pro.put("password",“db2admin"); pro.put("traceFile","test.log"); pro.put("traceLevel",String.valueOf(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_SYSTEM_MONITOR)); Connection con = DriverManager.getConnection("jdbc:db2://localhost:50001/sample",pro); Statement stm = con.createStatement(); con.setAutoCommit(false); stm.executeUpdate("insert into t1 values (1,1)"); DB2SystemMonitor systemMonitor = ((DB2Connection)con).getDB2SystemMonitor(); systemMonitor.enable(true); systemMonitor.start(DB2SystemMonitor.RESET_TIMES); con.commit(); systemMonitor.stop(); System.out.println("Server elapsed time (microseconds)=" + systemMonitor.getServerTimeMicros()); System.out.println("Network I/O elapsed time (microseconds)=" + systemMonitor.getNetworkIOTimeMicros()); System.out.println("Core driver elapsed time (microseconds)=" + systemMonitor.getCoreDriverTimeMicros()); System.out.println("Application elapsed time (milliseconds)=" + systemMonitor.getApplicationTimeMillis()); } }

トレースとしてパフォーマンスモニターを出力するように指定

Commit時のパフォーマンスモニターを取得


(参考)検証 DB2SystemMonitor.getServerTimeMicros (2/2)

C:¥work¥javatest>java com.ibm.db2.jcc.DB2Jcc -version IBM DB2 JDBC Universal Driver Architecture 3.63.123 C:¥work¥javatest>java test5 Server elapsed time (microseconds)=505 Network I/O elapsed time (microseconds)=600 Core driver elapsed time (microseconds)=1547 Application elapsed time (milliseconds)=1 C:¥work¥javatest>type test.log [jcc][SystemMonitor:start] [jcc][Time:2012-06-10-11:33:26.640][Thread:main][Connection@66526652] setAutoCommit (false) called [jcc][SystemMonitor:stop] core: 1.2568629999999998ms | network: 0.0ms | server: 0.0ms [jcc][SystemMonitor:start] [jcc][Time:2012-06-10-11:33:26.640][Thread:main][Statement@1d5c1d5c] executeUpdate (insert into t1 values (1,1)) called [jcc][Time:2012-06-10-11:33:26.718][Thread:main][Statement@1d5c1d5c] executeUpdate () returned 1 [jcc][SystemMonitor:stop] core: 74.541596ms | network: 71.80605299999999ms | server: 70.328ms [jcc][SystemMonitor:start] [jcc][Time:2012-06-10-11:33:26.718][Thread:main][Connection@66526652] commit () called [jcc][Connection@66526652] DB2 LUWID: 127.0.0.1.3596.120610023326.0001 [jcc][Time:2012-06-10-11:33:26.718][Thread:main][Connection@66526652] commit () returned null [jcc][SystemMonitor:stop] core: 1.547124ms | network: 0.600914ms | server: 0.505ms

C:¥work¥javatest>java -cp .¥db2jcc_v97fp4.jar;. com.ibm.db2.jcc.DB2Jcc -version IBM DB2 JDBC Universal Driver Architecture 3.62.56 C:¥work¥javatest>java -cp .¥db2jcc_v97fp4.jar;. test5 Server elapsed time (microseconds)=0 Network I/O elapsed time (microseconds)=841 Core driver elapsed time (microseconds)=1763 Application elapsed time (milliseconds)=2 C:¥work¥javatest>type test.log [jcc][SystemMonitor:start] [jcc][Time:2012-06-10-11:43:42.015][Thread:main][Statement@649b649b] executeUpdate (insert into t1 values (1,1)) called [jcc][Time:2012-06-10-11:43:42.015][Thread:main][Statement@649b649b] executeUpdate () returned 1 [jcc][SystemMonitor:stop] core: 4.849219ms | network: 0.444749ms | server: 0.14300000000000002ms [jcc][SystemMonitor:start] [jcc][Time:2012-06-10-11:43:42.015][Thread:main][Connection@28d828d8] commit () called [jcc][Connection@28d828d8] DB2 LUWID: 127.0.0.1.3737.120610024341.0001 [jcc][Time:2012-06-10-11:43:42.015][Thread:main][Connection@28d828d8] commit () returned null [jcc][SystemMonitor:stop] core: 1.763353ms | network: 0.841169ms | server: 0.0ms

V9.7FP4レベルでは0秒です。

Commit時のServer time

が出力されます。

トレースファイルにも出力されています。

• V10.1レベル

• V9.7 FP4レベル

V10.1レベル

V9.7FP4レベル


(参考) 検証com.ibm.db2.jcc.DB2Driver.changeDB2Password

• 形式

• public static void changeDB2Password (String url,

String userid,

String oldPassword,

String newPassword)

throws java.sql.SQLException

C:¥work¥javatest>db2 connect to tgt user a using b Database Connection Information Database server = DB2/AIX64 9.7.5 SQL authorization ID = A Local database alias = TGT C:¥work¥javatest>type test11.java import com.ibm.db2.jcc.*; public class test11 { public static void main( String[] args ) throws Exception{ DB2Driver.changeDB2Password(args[0],args[1],args[2],args[3]); } } C:¥work¥javatest>javac test11.java C:¥work¥javatest>java test11 jdbc:db2://XXX.XXX.XXX.XXXX:54321/TGT a b c C:¥work¥javatest>db2 connect to tgt user a using c Database Connection Information Database server = DB2/AIX64 9.7.5 SQL authorization ID = A Local database alias = TGT

データベースTGTにユーザ“a”がパスワード”b”で接続できることを確認

ユーザー“a”のパスワードを”b”から”c”に変更

データベースTGTにユーザ“a”がパスワード”b”で接続できることを確認


(参考) 検証 db2.jcc.maxRefreshInterval (1/2)

import java.util.*; import java.sql.*; public class test10 { public static void main( String[] args ) throws Exception{ Class.forName("com.ibm.db2.jcc.DB2Driver"); Properties pro = new Properties(); pro.put("user","db2005"); pro.put("password","db2005"); pro.put("enableSysplexWLB","true"); pro.put("traceFile","test.log"); pro.put("traceLevel",String.valueOf(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL)); Connection con = DriverManager.getConnection("jdbc:db2://XXX.XXX.XXX.XXX:XXXX/DBV10",pro); System.out.println("connect"); String sql = "select current member from sysibm.sysdummy1"; Statement stmt = con.createStatement(); ResultSet rs = null; for (int i = 1;i < 60;i++) { rs = stmt.executeQuery(sql); rs.next(); System.out.print("."); rs.close(); Thread.sleep(1000); } } }

• 検証で使用したプログラム

トレースを出力するように指定

60秒以上処理を続けるようにループさせる


(参考) 検証 db2.jcc.maxRefreshInterval (2/2)

C:¥work¥javatest>java com.ibm.db2.jcc.DB2Jcc -version IBM DB2 JDBC Universal Driver Architecture 3.63.123 C:¥work¥javatest>java test10 connect ........................................................... C:¥work¥javatest>find "Server List" test.log ---------- TEST.LOG [jcc]findBestSysplexMember [time:2012-06-11-17:09:31.031][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:09:45.375][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:09:56.000][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:10:05.593][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:10:16.234][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:10:25.859][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:10:35.515][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:10:46.093][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:10:55.687][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:11:05.265][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:11:15.843][Thread:main][tracepoint:200] Refresh Server List {SWLBG@37f837f8: ・・・

C:¥work¥javatest>java -cp .¥db2jcc_v97fp4.jar;. com.ibm.db2.jcc.DB2Jcc -version IBM DB2 JDBC Universal Driver Architecture 3.62.56 C:¥work¥javatest>java -cp .¥db2jcc_v97fp4.jar;. test10 connect ........................................................... C:¥work¥javatest>find "Server List" test.log ---------- TEST.LOG [jcc]findBestSysplexMember [time:2012-06-11-17:18:10.031][Thread:main][tracepoint:200] Refresh Server List {SWLBG@3750375: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:18:44.671][Thread:main][tracepoint:200] Refresh Server List {SWLBG@3750375: ・・・ [jcc]findBestSysplexMember [time:2012-06-11-17:19:14.609][Thread:main][tracepoint:200] Refresh Server List {SWLBG@3750375: ・・・

• V10.1レベル

• V9.7 FP4レベル

V9.7 FP4レベルでは30秒間隔でServer List

のリフレッシュが実施されています

V9.7 FP4レベル

V10.1レベルでは10秒間隔でServer Listのリフレッシュが実施されています

V10.1レベル


組み込みグローバル変数によるSQLプログラミング機能の拡張

変数名説明

CLIENT_HOST オペレーティング・システムによって戻される現行クライアントのホスト名

CLIENT_IPADDR オペレーティング・システムによって戻される現行クライアントの IP アドレス

CLIENT_ORIGUSERID 明示的なトラステッド接続を介するアプリケーション・サーバーなどの外部アプリケーションによって提供されるオリジナル・ユーザー ID

CLIENT_USRSECTOKEN 明示的なトラステッド接続を介するアプリケーション・サーバーなどの外部アプリケーションによって提供されるセキュリティー・トークン

MON_INTERVAL_ID 現在のモニター・インターバルの ID

PACKAGE_NAME 現在実行されているパッケージの名前

PACKAGE_SCHEMA 現在実行されているパッケージのスキーマ名

PACKAGE_VERSION 現在実行されているパッケージのバージョン ID

ROUTINE_MODULE 現在実行されているルーチンのモジュール名

ROUTINE_SCHEMA 現在実行されているルーチンのスキーマ名

ROUTINE_SPECIFIC_NAME 現在実行されているルーチンの具体的な名前

ROUTINE_TYPE 現在実行されているルーチンの種類

TRUSTED_CONTEXT 現在のトラステッド接続の確立のために突き合わされたトラステッド・コンテキストの名前

• 組み込みグローバル変数

• データベース・マネージャーで作成され、システム・カタログでデータベース・マネージャーに対して登録されるグローバル変数です

• DB2® バージョン 10.1 で導入されている以下の組み込みグローバル変数は、データベース作成時に自動生成されます。

いったん生成されるとSQL ステートメントを介してプログラム的にこれらの値にアクセスし、データを共有することができます (追加のアプリケーション・ロジックは必要ありません)


(参考) 組み込みグローバル変数による SQL プログラミング機能の拡張

C:¥Program Files¥IBM¥SQLLIB¥BIN>db2 values sysibm.client_host

1

--------------------------------------------------------------------------------------------

MACHINENAME

• グローバル変数 CLIENT_HOST にアクセスする例

• SELECT ステートメント内で使用する例

C:¥Program Files¥IBM¥SQLLIB¥BIN>db2 select * from T1

C1 C2 C3

----------- ---- ------------

1 AAAA MACHINENAME

2 BBBB HOSTNAME

C:¥Program Files¥IBM¥SQLLIB¥BIN>db2 select C1,C2,C3 from T1 where C3 = CLIENT_HOST

C1 C2 C3

----------- ---- ------------

1 AAAA MACHINENAME


汎用表関数

JAVA ジェネリック表関数

file1

file3

file2

出力1

出力2

出力3

• V10.1より、新しく実行時に出力形式を指定することができる表関数が提供された

• 今まで別々のソースからデータを読み込むためには各ソースに対応したUDFを作成する必要があった

• 汎用表関数を使用すれば、同じUDFを使って別々のソースから読み込むことが可能

汎用表関数は、Hadoopとの連携に使用できます。詳

細は、当ワークショップ資料『7.ビッグデータを含めた多用なデータへの対応』をご参照ください。


汎用表関数の作成方法 • CREATE FUNCTIONステートメント内での使い方

• 事前に外部プログラムの作成・コンパイルを行い、sqllib/function以下にクラスファイルのコピーを行う（通常外部プログラムを使ったUDFと同様）

• 以下の例では、‟csvRead‟というUDF名で登録

CREATE FUNCTION csvRead(fnme VARCHAR(255),coltype VARCHAR(255)) RETURNS GENERIC TABLE EXTERNAL NAME ‘UDFcsvReader.jar:UDFcsvReader!csvReadString’ LANGUAGE JAVA PARAMETER STYLE DB2GENERAL;

DB2マネージャーがUDFを実行するための外部コードを指定

表関数の出力を実行時に決定することを記述

RETURNS GENERIC TABLEを指定する場合、以下の指定が必須 LANGUAGE JAVA PARAMETER STYLE DB2GENERAL


汎用表関数の使用方法

• selectステートメントのfrom句の中で汎用表関数が呼ばれる

• as句で表に含まれる列名とデータタイプを指定する

• 同じUDFを使ったselectステートメントで、別の表からもデータの取得が可能

select * from table(csvRead('C:¥Program Files¥IBM¥SQLLIB_v10¥test¥test2.csv')) as T(first varchar(23), last varchar(10), age smallint, salary integer, id bigint, rand_double double) where T.first like 'J%';

前頁で登録したUDFをコール

Table Function


(参考) 汎用表関数の実行例

select * from table(csvRead('C:¥Program Files¥IBM¥SQLLIB_v10¥v10test/test2.csv')) as TX(first varchar(23),

last varchar(10), age smallint, salary integer, id bigint,rand_double double) where TX.first like 'J%' FIRST LAST AGE SALARY ID RAND_DOUBLE ----------------------- ---------- ------ ----------- -------------------- ------------------------ John Doe 26 46000 56255333387 +5.32340983000000E+004 Jack McGinnis 42 650 482525320 +2.34532320500000E+002 2 レコードが選択されました。 select * from table(csvRead('C:¥Program Files¥IBM¥SQLLIB_v10¥v10test/test1.csv')) as T(first

varchar(23), age smallint, last varchar(10)) FIRST AGE LAST ----------------------- ------ ---------- Chris 78 Weed Jonathan 11 Thompson "JJ ""Demo""" 66 Michel Steve 88 Arnold 0 Blankman 5 レコードが選択されました。

同じUDFを使用

異なるcsvファイルからデータを読み込んでいる


INGEST


これまでのデータ移動ユーティリティーの問題点

• データウェアハウス環境でデータを格納しながら・・・

• データに同時アクセスしたい！

• リアルタイムで、タイムリーにデータにアクセスしたい！

• LOAD処理だと・・・

• バッチ・ウィンドウの間、ロード開始前のデータにアクセス可能だが、ロード中のデータにはアクセスができない

• 月次や日次のバッチでは、最新のデータにアクセスできない

• IMPORT処理だと・・・

• 遅すぎる

• PIPEが使えない

その他・・・

・データ変換ができない

・DBサーバー側の負荷が高い


DB2 V10.1 INGEST機能概要

• INGEST機能の特徴

• 大量のリアル・タイム・データをデータのアベイラビリティを損なわずに移動および変換処理させることが可能

• ファイル、パイプ、TCPソースからDB2テーブルへデータを高速に投入

• クライアント・サイドで実行することでサーバーの負荷軽減

• トランザクション・ログへの書き出し

• INGEST機能のお客様にとっての価値

• 最新のデータを元に意思決定ができる

• ストリームのデータを投入しながら、業務を継続することができる


INGESTアーキテクチャー

• Transporter

• ソースから読み取り、Formatterに渡す

• INSERTではソースごとに1つずつ、UPDATE, DELETEでは各1つ

• Formatter

• データをフォーマットして、パーティションごとにFlusherに振り分ける

• Flusher

• データをTargetに適用する


サポートする入力データ・フォーマットとオペレーション

• サポートする入力データ・フォーマット

• Delimited ASCII (DEL) Format

• デリミタで区切られた可変長テキスト

• Fixed Format ASCII (ASC)

• 固定長テキスト

• サポートするオペレーション

• DMLオペレーション

• INSERT, UPDATE, DELETE, MERGE, REPLACE

• SQLのように指定可能

1, “Mark Kate”, “DB2 Specialist”, “IBM Canada” 2, “John Doe”, “DB2 Specialist”, “IBM Canada”

1 Mark Kate DB2 Specialist IBM Canada 2 John Doe DB2 Specialist IBM Canada

INGEST FROM <FILEs / PIPEs / TCP> FORMAT <DELIMITED / POSITIONAL> <SQL>


（参考）INGESTコマンドの例

• Example 1: basic command

• Example 2: file using a ASC format

• Example 3: pipe using DEL format and a customized delimiter

INGEST FROM FILE my_file.txt FORMAT DELIMITED INSERT INTO my_table;

INGEST FROM FILE my_file.txt FORMAT POSITIONAL( $field1 POSITION(1:8) INTEGER EXTERNAL, $field2 POSITION(10:19) DATE ’yyyy-mm-dd’, $field3 POSITION(25:34) CHAR(10))

INSERT INTO my_tableVALUES($field1, $field2, $field3);

INGEST FROM PIPE mypipe FORMAT DELIMITED BY ‘/' ( $prod_ID CHAR(8), $description CHAR(32), $price DECIMAL(5,2) EXTERNAL, $sales_tax DECIMAL(4,2) EXTERNAL, $shipping DECIMAL(3,2) EXTERNAL )

INSERT INTO my_table(prod_ID, description, total_price) VALUES($prod_id, $description, $price + $sales_tax + $shipping);


INGESTを使ったデータ変換

• ファイルに存在する行とマッチする行があればTEST表を更新

C1 C2

----------- --------

1 A

2 B

3 C

C1 C2

----------- --------

1 X

2 B

3 C

表：

test 表：

test

1 ,'X ‟

ファイル:test_diff.del

INGEST FROM FILE test_diff.del FORMAT DELIMITED ( $c1 INTEGER EXTERNAL, $c2 CHAR(8) ) UPDATE test SET (c1, c2) = ($c1, $c2) WHERE (c1 = $c1);

C1がマッ

チ

C1、C2を更

新


• INGEST SETコマンドで設定（セッション内のみで有効）

• commit_count - コミット・カウント

• commit_period - コミット期間

• num_flushers_per_partition - データベース・パーティションごとのフラッシャーの数構成パラメーター

• num_formatters - フォーマッターの数

• pipe_timeout - パイプ・タイムアウト構成パラメーター

• retry_count - 再試行数

• retry_period - 再試行期間

• shm_max_size - 共有メモリーの最大サイズ

INGESTユーティリティー構成パラメーター


リジェクトとリカバリー

• リジェクト

• リジェクトされた行はデフォルトでは捨てられる

• 例外表およびファイルを指定可能

• リカバリー

• INGEST実行時に、失敗した場合のINGESTのリカバリー方法を指定可能

• RESTART OFF

• RESTART NEW 'ingestjob001„

• 失敗後に実行するINGESTでは以下のいずれかを選択可能（RESTART OFFでは選択不可）

• RESTART CONTINUE „ingestjob001„

• RESTART TERMINATE 'ingestjob001'

再開表が必要


INGESTのモニター

• INGESTのモニター機能

• INGEST LIST、 INGEST GET STATS

$ db2 ingest list

Ingest job ID = JOB_INS00

Ingest temp job ID = 1

Database Name = TPCC

Target table = DB2INST1.STOCK

Input type = FILE

Start Time = 06/05/2012 14:13:29.242747

Running Time = 00:00:16

Number of records processed = 221000

DB20000I The INGEST LIST command completed successfully.

処理した件数


INGEST/IMPORT/LOADパフォーマンス

• INGESTはLOADよりパフォーマンスは务るが、IMPORTよりパフォーマンスが良い

• INGESTはトランザクション・ログに書き込みを行う

• IMPORTの代替として使用可能

500万件投入時の投入速度：　万件/sec

38.46

8.93

2.98

0

5

10

15

20

25

30

35

40

45

LOAD INGEST IMPORT


（参考）INGESTユーティリティのセットアップ

• DB2クライアントの一部としてインストールされる

• DB2 AESEのみサポート

• インストール・ロケーション

• 既存のDB2サーバー

• 新規INGESTサーバー

• 既存のETLサーバー

• DPFのコーディネータパーティション


パーティション表機能強化


パーティション表の登場と機能強化

• DB2 V9.1 パーティション表の登場

• DB2 V9.7での機能強化

• 索引のパーティション分割

• アタッチ処理の飛躍的な高速化

• パーティション操作（デタッチ）時の可用性向上（FP1）

• デタッチ時により限定的なロック取得へ変更

• フェーズ分割によりパーティション操作の完了が高速に

• DB2 V10.1での機能強化

• パーティション操作（アタッチ）時の可用性がさらに向上

• 表全体を対象とした排他ロック（Zロック）の取得が不要となる

• 継続的な参照、更新がある状態でパーティションのアタッチ、追加が可能に

• より短時間でのパーティションアタッチ完了

• パーティション有効化（SET INTEGRITY）時のデータ範囲チェックが、ユーザー責任でバイパス可能となる

V9.7～

V9.1～


まとめ

INGEST

•データやテーブルのアベイラビリティーを維持した状態で高速にデータ処理が可能

•データ変換や、リジェクト及びリカバリーなどの機能が充実

パーティション表機能強化

•アタッチ処理の可用性向上

新しい運用管理ツールとアプリケーション開発の生産性向上 · （参考）...

Documents

Transcript of 新しい運用管理ツールとアプリケーション開発の生産性向上 · （参考）...

新しい運用管理ツールと アプリケーション開発の生産性向上 · （参考）...

Documents

Transcript of 新しい運用管理ツールと アプリケーション開発の生産性向上 · （参考）...

新しい運用管理ツールとアプリケーション開発の生産性向上 · （参考）...

Transcript of 新しい運用管理ツールとアプリケーション開発の生産性向上 · （参考）...