簡単シリーズ データベース･オブジェクト作成編 目次 第1章 データベース・オブジェクトの概要

簡単シリーズ
簡単シリーズ
データベース･オブジェクト
データベース･オブジェクト作成編
2002/04
SS&WSCC#1
目次
第1章 データベース・オブジェクトの概要
1.1 オブジェクトの構成図
1.2 オブジェクトの管理方法
第2章 オブジェクト作成方法
2.1 オブジェクト作成フロー
2.2 データ記憶域管理
2.3 データ-ベース
2.4 表スペース
2.5 スキーマ
2.6 表
2.7 視点
2.8 別名
2.9 索引
第3章 データのロード
添付資料
Systems Solution & Web Server Competence Center No.1 DM Group
1-2
第１章 データベース・オブジェクトの概要
Systems Solution & Web Server Competence Center No.1 DM Group
1.1 オブジェクトの構成図
データベース・オブジェクトとは、表スペース、表、視点、索引、パッケージ、ログ、ロックなど、
DB2 UDBデータベースの構成要素を表します。
インスタンス
データベース１
表スペース０
表
表スペース１
索引
表スペース２
表
表
表
当
資
料
で
扱
う
対
象
データベース２
表スペース０
表
表スペース１
表
索引
DB2 UDB(Windows,UNIXなど)
※インスタンス
データベース・オブジェクト
インスタンス
データベース
表スペース
スキーマ
表
視点
別名
索引
システムカタログ表
トリガー
ストアードプロシージャー
ユーザー定義関数
バッファープール
パッケージ
DB2導入時に自動的に一つのインスタンスが作成されます。
インスタンスはデータベースをカタログしたり、構成パラメータを設定したりすることのできる論理的なデータベース管理システムです。
使用環境に応じて一つのサーバー上に複数のインスタンスを定義することができます。
Systems Solution & Web Server Competence Center No.1 DM Group
3-4
1.2 オブジェクトの管理方法 DB内のオブジェクトを作成、変更、削除するにはSQLデータ定義言語(DDL)を使用します。
DDL 以下3つの主要なSQL
データベース・オブジェクトの作成 CREATE ＜database object＞ XXXX
データベース・オブジェクトの削除 DROP ＜database object＞ XXXX
データベース・オブジェクトの変更 ALTER ＜database object＞ XXXX
以下に示すさまざまなインターフェースを使用してSQLを実行することができます。
コマンド行プロセッサー
対話式SQL文、またはDB2UDBコマンドを実行するのに使用できる、テキスト形式のアプリケーションです。このインターフェースからデータベースにア
クセスして操作することができます。
コントロール・センター
システムの構成、ディレクトリーの管理、システムのバックアップと回復、ジョブのスケジューリング、および媒体の管理などの管理タスクをグラ
フィックを使用して実行することができます。またコントロール・センターには、システム間のデータの複製をグラフィックを使用してセットアップす
るための複製管理が含まれています。さらに、グラフィカル・ユーザー・インターフェースを介して DB2 ユーティリティー機能を実行できます。
他のDB2ツール ：SQL文を作成、実行するほかスケジューリング機能も提供します。
スクリプト・センター
ジョブ（SQL、DB2UDBコマンド、オペレーティング・システムのコマンド）の保存、実行、スケジューリングを行います。
コマンド・センター
SQL、DB2UDBコマンド、オペレーティング・システムのコマンドを実行する対話式のウィンドウを提供します。
Systems Solution & Web Server Competence Center No.1 DM Group
このページはブランクです。
Systems Solution & Web Server Competence Center No.1 DM Group
5-6
第2章 オブジェクトの作成方法
Systems Solution & Web Server Competence Center No.1 DM Group
2.1 オブジェクト作成フロー
インスタンス作成
物理特性と運用管理基準の検討と決定
2.2章
データベース作成 2.3章
表スペースの作成 2.4章
デフォルトのユーザー表,一時表,カタログ表スペースの定義
データベースの物理配置,コンテナー,SMS or DMS
バッファー・プール
エクステント・サイズ,プリフェッチ・サイズ
スキーマの作成 2.5章
論理設計の反映
表の作成 2.6章
列の設計とキー,参照整合性,制約
視点,別名の作成 2.7,2.8章
論理設計の反映とパフォーマンス
索引の作成 2.9章
索引の列,索引タイプ
データのロード 3章
Systems Solution & Web Server Competence Center No.1 DM Group
7-8
2.2 データ記憶域管理
SMS
とDMSの比較
SMS(システム管理記憶域表スペース)
DMS(データベース管理記憶域表スペース)
ディレクトリー
ファイル、RAWデバイス
物理スペース(コンテナー)
/db/payroll/tbsp1/cont1
RAWデバイス
ファイル
特性
予め記憶域は割り当てない 予め、記憶域を割り当てる 拡張可能 一つの表の、索引、LOBを別の表スペースに保存可能
表スペース内のコンテナーの数を動的に増やせる
×
×
×
○
×
○
○
○
×
○
索引データを別の表スペースに保管できる
ロングデータを別の表スペースに保管できる
スペースは必要なときにのみ割り当てられる
コンテナーのページを動的に増やせる
通常は、十分に調整したDMS 表スペースの方が SMS 表スペースよりも性能が優れています。
また、SMS 表スペースでは個人用の小さなデータベースを管理するのが一番簡単です。
一方、サイズが大きく、これからも拡張するデータベースの場合、 SMS 表スペースは一時表スペースとしてのみ使用し、ユーザー表にはDMS表スペースをし、
さらに各表スペースに複数のコンテナーを割り当てるのが効果的です。
CREATE TABLE文で表を定義する際にテーブルデータを置く表スペースを明示的に指定できます。
Systems Solution & Web Server Competence Center No.1 DM Group
2.2 データ記憶域管理
表スペース,コンテナーにより、システムでデータが保管される方法を定義できます。
●表スペース
データベースは、表スペース(表を保管するスペース)と呼ばれる部分に編成されています。表スペースにはコンテナー(物理記憶域:ファイルまたは装置など)が割
り当てられ、システム管理スペース(SMS)かデータベース管理スペース(DMS)のどちらかを使用します。
・SMS：OSのファイルシステム・マネージャーが表を保管するスペースを割り振って管理
・DMS：データベース・マネージャーが記憶スペースを制御
●コンテナー は、物理記憶域装置で、ディレクトリー名、装置名、またはファイル名によって識別できます。
単一の表スペースはいくつものコンテナーにまたがることができますが、それぞれのコンテナーが属することができる表スペースは 1つだけです。
表のデータは、表スペースの全コンテナーにラウンドロビン方式で保管され、特定の表スペースに属するコンテナー間でデータが均等になります。別のコンテ
ナーを使用する前に、データベース・マネージャーがコンテナーに書き込むページ数は、エクステント・サイズ と呼ばれます。
・ディレクトリーコンテナ ：SMS表スペースで指定できる唯一のタイプ
・デバイスコンテナー(RAWデバイス)：AIX,solaris では文字特殊インターフェースを備えた論理ボリューム 、Windowsでは未フォーマットの区画または物理ディスク
・ファイルコンテナー
：DMS表スペースが使用する事前割り振りサイズのファイル
●SMS表スペースとDMS表スペース
・SMS表スペースの利点:スペースが必要になる時点まで、スペースがシステムによって割り振られることはありません。
コンテナーの事前定義が不要なため、データベースの作成に必要な初期作業が少なくてすみます。
・DMS表スペースの利点:表スペースのサイズは、 ALTER TABLESPACEのコンテナー追加・拡張によって増加できます。
コンテナー追加の場合、既存のデータは、最適な入出力効率を保つため、新しいコンテナーのセットにわたって自動的に
再バランスが行われます。
格納するデータのタイプ(長形式フィールドおよび LOB,索引,正規表データ)で、表を複数の表スペースへ分割できます。
●デバイスを使用するメリットと考慮点(RAWデバイスとファイルコンテナーの比較)
ファイルとデバイスの間に操作上の違いはありません。
・メリット:ファイルコンテナーの場合は、ファイルシステムの実行時のオーバーヘッドがかかるため、デバイスより効率的でないことがあります。
ファイルシステムをバイパスすることによりDB2UDBはデバイスコンテナーを直接管理してパフォーマンスを改善していますが、ファイルの場合は
DB2UDBがファイルシステムと対話する必要があります。
・考慮点
ファイルシステムのキャッシュを使用できません(ファイルは可)。これを補うためにDB2UDBのバッファーを増す必要が生じることがあります。
デバイスはセットアップと保守に関連した管理上のオーバーヘッドがかかります。
デバイスはコンテナーをひとつしかサポートできません。
Systems Solution & Web Server Competence Center No.1 DM Group
9-10
2.3 データベース
例 ｢SAMPLE｣という名前のDBを作成します。 CREATE DATABASE SAMPLE
-CREATE----+-DATABASE-+--database-name------------------------>
'-DB-------'
省略時の3つの表スペース,ログがここに取られます。
十分な空きスペースがあることをご確認ください。
SampleDBのための最低15MBのディスク容量が必要になります。
Create Database options
￨---+----------------+---+------------------------+------------->
データベースを作成すると３種類の表スペース(デフォルトSMS)が作られます。
'-ON--+-path--+--'
・カタログ表スペース (calatog)SYSCATSPACE システム・カタログ表を含む
'-drive-'
>-----+-----------------------------------------------+--------->
・一次表スペース (temporary)TEMPSPACE 一次表を含む
'-USING CODESET--codeset--TERRITORY--territory--'
・ユーザー表スペース（regular)USERSPACE1ユーザー表,索引を含む
>-----+-----------------------------------+--------------------->
￨
.-SYSTEM--------. ￨
'-COLLATE USING--+-COMPATIBILITY-+--'
'-IDENTITY------'
>-----+--------------------------------+------------------------>
'-DFT_EXTENT_SZ--dft_extentsize--'
on path/drive
>-----+----------------------------------------+---------------->
'-CATALOG TABLESPACE--￨ tblspace-defn ￨--'
>-----+-------------------------------------+------------------->
'-USER TABLESPACE--￨ tblspace-defn ￨--'
>-----+------------------------------------------+-------------->
'-TEMPORARY TABLESPACE--￨ tblspace-defn ￨--'
tblspace-defn
node0000
SQL00001
SQLT0000.0
SYSCATSPACE
SQLT0001.0
TEMPSPACE
￨---MANAGED BY-------------------------------------------------->
>-----+-SYSTEM
USING--(-----'container-string'--+---)-------------------------------------+>
￨
.-,-----------------------------------------------------.
￨
￨
V
￨
￨
'-DATABASE
USING--(------+-FILE---+---'container-string'---number-of-pages---+---)--'
'-DEVICE-'
>-----+------------------------------+-------------------------->
'-EXTENTSIZE--number-of-pages--'
SQLT0002.0
USERSPACE1
表
索引
物理構造コンテナー
論理構造表スペース
>-----+--------------------------------+------------------------>
'-PREFETCHSIZE--number-of-pages--'
Systems Solution & Web Server Competence Center No.1 DM Group
2.3 データベース
データベースは表や索引の集合体です。接続、運用上の単位となります。
一つのインスタンス上に複数のデータベースを作成することができます。
●ON path/drive
UNIX ベースのシステムでは、データベースを作成するパスを指定します。パスを指定しないと、データベースはデータベース・マネージャー構成ファイル
(dftdbpath パラメーター) に指定されている省略時値のデータベース・パスに作成されます。Windows OSでは、ドライブ識別子を指定します。
●USING CODESET codeset
このデータベースに入力するデータに使用するコードセットを指定します。
●TERRITORY territory
このデータベースに入力するデータに使用する地域を指定します。
●COLLATE USING
データベースに使用する照合順序のタイプを識別します。
Systems Solution & Web Server Competence Center No.1 DM Group
11-12
2.4 表スペース
例
SMS
DMS
AIX
CREATE TABLESPACE AIXSMS MANAGED BY SYSTEM USING
('/database/firstcontain','/database/secondcontain')
CREATE TABLESPACE AIXDMS MANAGED BY DATABASE USING
(device '/dev/rdata1' 1024,device'/dev/rdata2' 1024)
EXTENTSIZE 8
NT
CREATE TABLESPACE NTSMS MANAGED BY SYSTEM USING
('E:¥db¥firstcnt','F:¥db¥sndcnt','G:¥thirdcnt')
CREATE TABLESPACE NTDMS MANAGED BY DATABASE USING
(file 'C:¥db¥index.tbs' 1000)
.-REGULAR------------------.
>>-CREATE----+--------------------------+--------TABLESPACE--tablespace-name-------------------------->
+-LONG---------------------+
￨ .-SYSTEM--.
￨
'-+---------+---TEMPORARY--'
'-USER----'
.-PAGESIZE--4096------.
>-----+-----------------------------------+------------------>>-----+---------------------+----------->
￨
.-NODEGROUP-.
￨
'-IN-+-----------+--nodegroup-name--'
'-PAGESIZE--integer-+--+-'
'-K-'
>----MANAGED BY--+-SYSTEM--￨ system-containers ￨------+------>>-----+----------------------+--------->
'-DATABASE--￨ database-containers ￨--' >-----+---------------------------+--------->
'-EXTENTSIZE--+-number-of-pages-+--'
'-integer--+-K-+--'
+-M-+
'-G-'
>-----+------------------------------------+-------------------->>-----+-----------------------+------>
'-PREFETCHSIZE--+-number-of-pages-+--'
'-BUFFERPOOL--bufferpool-name--'
'-integer--+-K-+--'
+-M-+
'-G-'
コンテナー定義
system-containers
SMS表スペース
データベース内に新しい表スペースを作成し、その表スペー
スにコンテナーを割り当て、表スペースの定義と作成をカタ
ログに記録します。
container-clause
.-,------------------------------------------------------------.
V
￨
￨---(------+-FILE---+--'--container-string--'----+-number-of-pages-+--+---)-->
'-DEVICE-'
'-integer--+-K-+--'
+-M-+
'-G-'
database-containers
DMS表スペース
.--------------------------------------------------------.
￨
.-,-------------------------.
￨
V
V
￨
￨
￨-----USING--(-----'--container-string--'---+---)----+---+--+->
.--------------------------------------------.
V
￨
￨------USING--￨ container-clause ￨--+--------+--+->
Systems Solution & Web Server Competence Center No.1 DM Group
2.4 表スペース
表スペースは、データベースとそのデータベース内に格納されている表との間に入る記憶モデル、論理層です。
データベースの中に表スペースが作成され、表スペースの中に表が作成されます。
実際にデータを格納する物理的な媒体はコンテナーです(2.2章参照)。
バックアップ/リストアーの単位にもなりえます。
●REGULAR ：一時表を除くすべてのデータを保管します。
●LONG ：長形式または LOB の表の列を保管します。表スペースは DMS 表スペースでなければなりません。
●TEMPORARY：一時表を格納します。ソートや結合時にデータベースマネージャーが使用する作業領域です。一つのデータベースに最低1つは必要です。
●PAGESIZE integer [K] ：表スペースに使用するページサイズを定義します。4 または 8、16、または 32 を指定してください。
●表スペースタイプの指定
SMS 表スペースか DMS 表スペースかの選択
表スペースをデータベース管理スペースにするか、システム管理スペースにするかの選択は、トレードオフの関係を含む基本的な選択です。
検討の際の考慮点につきましては2.2章をご参照ください。
●MANAGED BY SYSTEM ：表スペースを、システム管理スペース (SMS) 表スペースとして指定します。
・system-containers SMS 表スペースに対するコンテナーを指定します。
・USING ('container-string',...)で ディレクトリー名を指定します。
●MANAGED BY DATABASE ：表スペースを、データベース管理スペース (DMS) 表スペースとして指定します。
・database-containers でDMS 表スペースに対するコンテナーを指定します。
・USING FILE ファイル名を指定します。
・USING DEVICE 装置名を指定します。
●EXTENTSIZE number-of-pages ：コンテナーに書き込むページ数を指定します。一度設定すると変更は不可能です。
デフォルトはDFT_EXTENT_SZ 構成パラメーターによって決まります。
●PREFETCHSIZE number-of-pages ：データのプリフェッチ中に、表スペースから一度に読み取られるページ数を指定します。プリフェッチは照会が必要なデー
タを実際に参照する前に先読みします。これにより、照会を実行するときに入出力する必要がなくなります。デフォルトは DFT_PREFETCH_SZ 構成パラメーター
によって決まります。
Systems Solution & Web Server Competence Center No.1 DM Group
13-14
2.4 表スペース
RAWデバイスの場合のDMS表スペースの作成例
NT
create tablespace TSRAWNT managed by database using (device '¥¥.¥F' 40960) EXTENTSIZE 8
AIX
create tablespace TSRAWAIX managed by database using (device '/dev/rn3hd03' 40960)
EXTENTSIZE 8
※AIXでRAWデバイスを指定する場合
●論理ボリュームを使用します
●rootで論理ボリュームを作成します。
# mklv -y'n1hd01' db2vg 20
# ls -l /dev/*n1hd01
brw-rw---- 1 root
system ... /dev/n1hd01
crw-rw---- 1 root
system ... /dev/rn1hd01R
●インスタンス・オーナーはRAWディバイスに書き込み権限が必要となります。
# chown instnn.admnn /dev/rn1hd01
# ls -l /dev/*n1hd01
brw-rw---- 1 root
system ... /dev/n1hd01
crw-rw---- 1 instnn admnn
... /dev/rn1hd01
Systems Solution & Web Server Competence Center No.1 DM Group
2.4 表スペース
DB2 UDBは、RAWデバイスをサポートしています(Windows 95、および Windows 98以外)
この種類の装置を識別するための物理的また論理的方法は以下のとおりです。
●Windows NTでの指定方法(構文)
¥¥.¥PhysicalDriveN
ここで N は、システムにある物理ドライブのいずれかを表します。この場合、N を 0、1、2、または他の正の整数に置き換えることができます。
¥¥.¥PhysicalDisk5
Windows で論理RAW区画 (つまり未フォーマットの区画) を指定するには、次の構文を使用します。
¥¥.¥N:
ここで N: は、システムにある論理ドライブ名を表します。たとえば、N: を E: または他のドライブ名で置き換えることができます。
注: 装置にログを書き込むには、 Windows NT バージョン 4.0 (サービス・パック 3 適用済み) をインストールしておく必要があります。
●UNIX ベースのプラットフォームでは、文字のシリアル装置名 (/dev/rhd0 など) を使用します。
UNIXプラットフォームではインスタンス・オーナーは、使用するデバイス・コンテナーの文字部分に対するアクセス権限を持っている必要があります。
AIXではDB2UDBコンテナー用に使用する物理ディスク上にボリューム・グループを作成します。
Systems Solution & Web Server Competence Center No.1 DM Group
15-16
2.5 スキーマ
完全なテーブル名 ”スキーマ名.テーブル名”
例 DBADM 権限のあるユーザーが、 RICK という名前のスキーマをユーザー RICK を所有者として作成します。
CREATE SCHEMA RICK AUTHORIZATION RICK
>>-CREATE SCHEMA------------------------------------------------>
>-----+-schema-name-------------------------------------+------->
+-AUTHORIZATION--authorization-name---------------+
'-schema-name--AUTHORIZATION--authorization-name--'
>-----+------------------------------+-------------------------><
￨ .------------------------. ￨
￨ V
￨ ￨
'----schema-SQL-statement---+--'
スキーマの所有者の決定方法
オブジェクトを作成する際にスキーマを指定しない場合、現行の許可IDで修飾されたテーブルになります。
そのオブジェクトはユーザーの許可IDを使用して暗黙スキーマに関連付けられます。オブジェクトがSQLの中で参照されるときに、スキーマ名がSQLの
中で指定されない場合もオブジェクトは発行者(動的SQL)の許可IDを使用して暗黙的に修飾されます。
Systems Solution & Web Server Competence Center No.1 DM Group
2.5 スキーマ
スキーマは、DB2 UDB内でデータベース・オブジェクトを論理的にグループ化するのに使用されるデータベース・オブジェクトです。
ほとんどのデータベース・オブジェクトは、二つの部分からなる命名規則(schema_name.object_name)を使用して命名されます。
名前の最初の部分はスキーマと呼ばれます。２番目の部分はオブジェクト名です。
●schema-name ：スキーマの名前を指定します。これは、カタログですでに記述されているスキーマを指定するものであってはなりません。 "SYS" で始まる
名前は使用できません。スキーマの所有者は、ステートメントを発行した許可 ID です。
●AUTHORIZATION authorization-name ：スキーマの所有者であるユーザーを指定します。値 authorization-name は、スキーマの名前の指定にも使用され
ます。 authorization-name は、カタログですでに記述されているスキーマを指定するものであってはなりません。
●schema-name AUTHORIZATION authorization-name ：authorization-name という名前のユーザーをスキーマの所有者として、 schema-name という名前
のスキーマを指定します。 schema-name は、カタログですでに記述されているスキーマのスキーマ名を指定するものであってはなりません。 schema-name に
は "SYS" で始まる名前は使用できません。
●schema-SQL-statement ：CREATE SCHEMA ステートメントの一部として組み込むことができる SQL ステートメントは、次のとおりです。
タイプ付き表および要約表を除外した CREATE TABLE ステートメント (2.6章CREATE TABLEを参照)
タイプ付き視点を除外した CREATE VIEW ステートメント (CREATE VIEWを参照)
CREATE INDEX ステートメント (CREATE INDEXを参照)
COMMENT ON ステートメント
GRANT ステートメント
Systems Solution & Web Server Competence Center No.1 DM Group
17-18
2.6 表
例
DEPARTX 表スペースに表 TDEPT を作成します。 DEPTNO、 DEPTNAME、MGRNO、および ADMRDEPT は列の名前です。 CHAR は、列が文字データを含むことを意味していま
す。 NOT NULLは、列にヌル値を含めることができないことを示します。VARCHARは、列のデータが可変長文字データであることを意味します。基本キーは、列 DEPTNO
で構成されます。
CREATE TABLE TDEPT
(DEPTNO CHAR(3)
NOT NULL,
DEPTNAME VARCHAR (36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3)
NOT NULL,
PRIMARY KEY(DEPTNO))
IN DEPARTX
>>-CREATE---TABLE--table-name---->>---+-￨ element-list
￨------->>--+-----------------------------------------------+--*-->>--*--+----------------------+---*---><
'-IN--tablespace-name1--￨ tablespace-options ￨--' '-NOT LOGGED INITIALLY-'
column-options
element-list
￨------+-----------------------------------------------------------------------+--+->
￨---(------+-￨ column-definition
+-NOT NULL--------------------------------------------------------------+
￨------+--+-)-￨
+-+----------------------------------+---+-+-PRIMARY KEY-+------------+-+
+-￨ unique-constraint ￨------+
￨ ￨
￨
￨ '-UNIQUE------'
￨ ￨
+-￨ referential-constraint ￨-+
￨ '-CONSTRAINT-------constraint-name-'
+-￨ references-clause ￨------+ ￨
'-￨ check-constraint ￨------￨
'-CHECK--(--check-condition-)'￨
column-definition
￨
￨
+-￨ column-default-spec ￨-----------------------------------------------+
￨---column-name----+---------------+------->
￨
￨
￨
￨
'-￨ data-type ￨-'
>-----+---------------------+------￨
'-￨ column-options ￨--'
表内の最大列
行の最大長(バイト)
最大表サイズ(64区画あたり）（GB)
4K ページ・サイズ
8K ページ・サイズ
16K ページ・サイズ
32K ページ・サイズ
500
4005
64
1012
8101
128
1012
16293
256
1012
32677
512
Systems Solution & Web Server Competence Center No.1 DM Group
2.6 表
表は列と行からなり、順序付けられていないデータ・レコードの集合を保管します。
データ・タイプ
●element-list
表の要素を定義します。これには、表の列と制約の定義が含まれます。
●column-definition
列の属性を定義します。
・データタイプ DB2 提供のデータ・タイプ
2進ストリング
文字ストリング
日時
2バイト文字
ストリング
日付
時刻
タイムスタ
ンプ
CHAR
10進数
整数
BLOB
GRAPHIC
VARCHAR
DATALINK
数値
DBCLOB
浮動
少数点数
DECIMAL
SMALLINT
INTEGER
VARGRAPHIC
BIGINT
DOUBLE/FLOAT
●column-options
REAL
詳細はマニュアル(SQL解説書)をご参照ください。
CLOB
表に制約を設けることによって、表の間の関係、表の内部での関係などを維持でき、データレコードが固有であることを保証できます。制約はデータべー
ス・マネージャーが適用する規制です。次の3種類の制約(固有制約,参照保全,検査制約)があります。詳細につきましては、添付資料をご参照ください。
●not logged initially
表を作成したものと同じ作業単位の中で、表の変更が行われた場合ログに記録しないようにしたいときに設定します。
ALTER TABLE 文で ACTIVATE NOT LOGGED INITIALLY オプションで変更することが可能です。
※表の制限
一旦作成すると、列名とデータタイプの変更は不可
Systems Solution & Web Server Competence Center No.1 DM Group
19-20
2.7 視点
例 PROJECT 表に基づく視点 MA_PROJ を作成します。この視点には、文字 'MA' で始まるプロジェクト番号 (PROJNO) を持つ行だけを入れます。
CREATE VIEW MA_PROJ
AS SELECT * FROM PROJECT
WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
構文
-CREATE--+-----------+---VIEW--view-name---------------------->
>-----+---------------------------------------------+--AS------->
￨
.-,--------------.
￨
￨
V
￨
￨
+-(-----column-name---+---)-------------------+
>----+---------------------------------------+--fullselect------>
>-----+--------------------------------------+-----------------><
￨
.-CASCADED--.
￨
'-WITH--+-----------+---CHECK OPTION---'
'-LOCAL-----'
※ 視点を作成する基礎表または視点は事前に存在していなければなりません。
CREATE VIEW 文を使用して作成される論理表
基礎表と同様に DML 文でアクセス
create view emp_view1 (empid,deptno,jobtitle,hiredate) as select id,dept,job,hiredate from employee where dept=10
検査オプション付き視点
条件が常に検査されるようにする
create view emp_view2 (empid,deptno,jobtitle,hiredate) as select id,dept,job,hiredate from employee where dept=10 with check
option
with check option が指定されていない場合はdept=10以外の行は追加、更新の検査はしない
Systems Solution & Web Server Competence Center No.1 DM Group
2.7 視点
視点はCREATE VIEW文を使用して作成される論理表です。
一つ以上の表のデータを表とは別の形で表示するための仮想の表のことです。視点には表の全てを含めることもできますし、一部の列のみを含めることもできま
す。
定義すると、基礎表と同様にDML文(SELECT,INSERT,UPDATE,DELETEなど)を使用してアクセスできるようになります。視点は視点に基づいて作成することもでき
ます。
視点を使用すると、アプリケーション・プログラミングに表データのサブセットを提供して挿入または更新されるデータを検証することができます。
視点には元の表と対応する列名と異なる列名を付けることが出来ます。視点を使用すれば、アプリケーション・プログラミングやエンドユーザーは照会によって
表データを柔軟に調べることができます。
視点用のデータは表データと別に保存されるわけではなく、視点で表示されるデータは基本表に保存されているものです。
視点の作成後、アクセス特権を指定できます。これにより、基礎表の制限された視点にしかアクセスできなくなるので、データセキュリティーが得られます。
●column-name
視点の列の名前を指定します。列名のリストを指定する場合、リスト中の列の名前の数は、全選択の結果表の列の数と同じ数でなければなりません。各
column-name (列名) は、固有で、しかも非修飾でなければなりません。列名のリストの指定がない場合、視点の列は、全選択の結果表の列名を継承します。
●WITH CHECK OPTION
視点によって挿入または更新される行すべてが、視点の定義に従う(視点の探索条件を満たしている)という制約を指定します。
・CASCADED ：その視点が従属する視点の制約も全て適用します。さらにその視点に従属する視点もこの制約の対象になります。
・LOCAL ：その視点の制約だけを適用します。その視点に従属する視点もこの制約の対象となります。
Systems Solution & Web Server Competence Center No.1 DM Group
21-22
2.8 別名
例
1: HEDGES は表 T1 に対して別名 A1 を作成します。
CREATE ALIAS A1 FOR T1
⇒⇒⇒ HEDGES.T1 に対して別名 HEDGES.A1 が作成されます。
ここで、
SELECT * FROM A1 は、実際には次のものになります。
SELECT * FROM T1
構文
->>-CREATE----+-ALIAS--------+--alias-name---FOR----------------->
>-----+-table-name--+------------------------------------------><
+-view-name---+
+-nickname----+
'-alias-name2-'
※ 別名は存在しない表または視点にも定義することができます。ただし、その別名を含むSQLステートメントがプリコンパイ
ルされるときまでにはそのオブジェクトは存在していなければなりません。
別名または別名が参照するオブジェクトを削除すると、その別名に存在する全てのパッケージが無効とマークされ、その別名に
依存する全ての視点およびトリガーが作動不能とマークされます。
Systems Solution & Web Server Competence Center No.1 DM Group
2.8 別名
別名は、表や視点を間接的に参照する方法です。
これにより、SQL ステートメントに表や視点の修飾名を指定せずに済みます。表名や視点名を変更しても、別名の定義を変えるだけで済みます。
24時間365日連続稼動のシステムのバックアップ/リカバリー運用のときなどに使用されます。
別名は他の別名に対して作成することもできます。別名は、視点やトリガーの定義、また SQL ステートメントの中で使用できます。ただし、既存の表名や
視点名を参照する表検査制約では使用できません。
●alias-name
別名を指定します。この名前が、現行データベースに存在する表、視点、ニックネーム、または別名を指定していてはなりません。
2 つの部分からなる名前を指定する場合、"SYS"で始まるスキーマ名は使用できません。
別名を定義する際の規則は、表名の定義に使用される規則と同じです。
●FOR table-name、view-name、nickname、 または alias-name2
alias-name を定義する対象のオブジェクト名を指定します。他の別名 (alias-name2) を指定する場合、その別名は、定義される新しいalias-name (完全
修飾形式の) と同じであってはなりません。
CREATE ALIAS <alias_name> FOR <table_name>
別名は、ステートメントのコンパイル時に表名や視点名に置き換えられます。別名または別名連鎖が表名や視点名に置換できないと、エラーになります。
Systems Solution & Web Server Competence Center No.1 DM Group
23-24
2.9 索引
例
PROJECT 表に対して UNIQUE_NAM という名前の索引を作成します。この索引の目的は、プロジェクト名 (PROJNAME) の値が同じ 2
つの項目が表に作成されないようにすることです。索引項目は昇順に並べます。
CREATE
UNIQUE INDEX UNIQUE_NAM ON PROJECT(PROJNAME)
構文
CREATE----+---------+--INDEX--index-name--------------------->
'-UNIQUE--'
V
.-ASC--. ￨
>----ON--+-table-name------+--(-----column-name--+------+--+---)->
￨
￨
'-DESC-'
>----*--+--------------------------------------------+---------->
￨
.-,--------------.
￨
￨
V
￨
￨
'-INCLUDE---------(-----column-name---+---)--'
※ 索引を作成する基本表は事前に存在していなければなりませ
ん。
>----*--+--------------------------------------------------+---*->
+-CLUSTER------------------------------------------+
>----+-------------------------+---*-----------><
'-ALLOW REVERSE SCANS-----'
※インデックス作成の指針
●基本キーまたはユニークインデックスを作成する。
●結合列となるカラムにインデックスを作成する。
●WHERE句、ORDER BY句、GROUP BY句で指定されることが多いカラムにインデックスを作成する。
※インデックスの数
●OLTP系の環境では１テーブルにインデックスは１つか２つにします。
●照会専用のテーブルでは、５つ以上のインデックスを作成しても問題ありません。
●照会/OLTP混在環境では、インデックスは２つから５つくらいに抑えます。
Systems Solution & Web Server Competence Center No.1 DM Group
2.9 索引
索引は一つの表に関連した物理オブジェクトで、指定された列の内容によって分類された、行の場所のリストです。一つの表に対して複数の索引を作成するこ
ともできます。
索引の目的
・SQL照会のパフォーマンスを向上させる。
索引を作成すると、表の中の行により効率的にアクセスすることができます。索引は表から条件に一致するデータを検索する際に表全体を走査するかわりに使
用されます。
●UNIQUE
ユニーク索引としたい場合に指定します。ユニーク索引を作成すると、索引キーの一意性が保証されます。
表の中に同じ値の索引キーを持つ行がないようにすることができます。この制約は表の行を更新したり、新しい行を挿入するときに適用されます。また、この
固有性は、CREATE INDEX ステートメントの実行の過程でも検査されます。重複するキー値を含む行がすでに表に含まれている場合、索引は作成されません。
UNIQUE を使用する場合、ヌル値は他の値と同様に扱われます。たとえば、キーが、ヌル値可能の単一列である場合、その列では 1 つのヌル値しか含めること
ができません。 UNIQUE オプションの指定があり、しかも表に区分化キーがある場合、索引キーの列は区分化キーのスーパーセットである必要があります。
●ASC
索引項目が、列の値の昇順で保持されるように指定します。これがデフォルト設定です。
●DESC
索引項目が、列の値の降順で保持されるように指定します。
●INCLUDE
UNIQUEを指定した索引にのみ指定することができます。索引列とはなりますが、ユニーク性のチェックは行われません。
●column-name
索引には組み込まれているものの、固有索引キーの一部ではない列を指定します。固有索引キーの列に定義された規則と同様な次の規則が適用されます。
column-name に続けてキーワード ASC または DESC を指定しても構いませんが、順序に影響はありません。
●CLUSTER
当該の索引を表のクラスター化索引として指定します。物理的なデータの並び順もこの索引の並び順にできるだけしたいときに指定します。
●ALLOW REVERSE SCANS
索引が前方向走査と反対方向走査の両方、すなわちINDEX CREATE の実行時に定義した順序とその反対(逆)の順序をサポートすることを指定します。
Systems Solution & Web Server Competence Center No.1 DM Group
25-26
第3章 データのロード
Systems Solution & Web Server Competence Center No.1 DM Group
3 データのロード
データを表に挿入するユーティリティーとして、LOADとIMPORTがあります。
LOADはテーブルの初期ロードなど、大量のデータを移動する処理を目的として設計されています。データ量が多い場合、IMPORTよりも高速にデー
タを挿入することができます。
IMPORTは内部的にSQLのINSERTを実行するのに対し、LOADは形式化されたページを直接データベースに書き出します。
LOADとIMPORTの比較
LOADユーティリティー
IMPORTユーティリティー
大量のデータを挿入する場合
SQL の Insert
IMPORT に比べはるかに高速
ロードはフォーマットされたページを直接データベー
スに書き込む
ログ
ログ記録は最小限
すべての行がロギングされる
データの挿入先
表および索引は宛先データベースに存在している必要
がある
IXF形式のファイルの場合、表定義、索引
の作成はインポートの過程で行われる。
表および索引が入っている
表スペース
ロードの間、オフラインになる
インポートの間もオンライン
統計
LOAD REPLACEモードで STATISTICS YESオプション
にすればロードの最中に統計が収集される(※注1)
IMPORT後にRUNSTATSを実行する
(Commit Countパラメータを指定しないとログ
がいっぱいになることがあるので注意)
※注1 使用例 LOAD FROM .. STATISTIC YES WITH DISTRIBUTION AND DETAILED INDEXES ALL..
Systems Solution & Web Server Competence Center No.1 DM Group
27-28
3 データのロード
●LOADには主に以下の2つのモードを用います。
１．REPLACEモード ・・・ 既存の表にあるデータの置換。既存データを入れ替える場合に用いる。 LOAD FROM product.ixf OF IXF.. REPLACE INTO product
２．INSERTモード
既存の表への新規データの挿入 ・・・既存データに追加する場合に用いる。
LOAD FROM product.ixf OF IXF.. INSERT INTO PRODUCT
空の表にデータを挿入する場合は、INSERTモードで実行したほうがパフォーマンスがよくなります。
それ以外はREPLACEモードでLOADを実行するほうが、最高のパフォーマンスが得られます。
●ロードの入力ファイル
・DEL (区切り ASCII ファイル) ・ASC (非区切り ASCII ファイル）
・IXF (統合交換形式ファイル)
●ロード・フェーズ
例 100001, "Peter", "Smith",
"307"
例 100001 Peter Smith 307
100002 Jone Adams
204
・データが表に保管され、索引キーが収集される
・SAVECOUNT パラメーターで指定された間隔で、一貫性ポイントが設定
・障害が発生した場合、RESTARTCOUNT オプションにより、最後の一貫性ポイントより続行可能
・ロードの前にバックアップを取得することを推奨
●作成フェーズ
・ロード・フェーズで収集された索引キーに基づいて索引が作成される
●削除フェーズ
・固有制約に違反したすべての行が削除される
例外表が作成されていると、無効の行が保管される
Systems Solution & Web Server Competence Center No.1 DM Group
3 データのロード
構文
.-,-------------.
V
￨
>>-LOAD----+---------+--FROM------+-filename-+--+--------------->>----OF--filetype----+-----------------------------+------------>
'-CLIENT--'
+-pipename-+
'-device---'￨
>-----+-----------------------------------------------------------------------------------------------------------------+>
￨
.-,---------------------------.
￨
￨
V
￨
￨
'-METHOD--+-L--(-----column-start--column-end---+---)--+-----------------------------------------------------+-+--'
￨
￨
.-,----------------------.
￨ ￨
￨
￨
V
￨
￨ ￨
￨
'-NULL INDICATORS--(-----null-indicator-list---+---)--' ￨
￨
.-,--------------.
￨
￨
V
￨
￨
+-N--(-----column-name---+---)-----------------------------------------------------------------------+
￨
.-,------------------.
￨
￨
V
￨
￨
'-P--(-----column-position---+---)-------------------------------------------------------------------'
>-----+---------------+---+--------------+---------------------->>-----+------------------+---+-------------------------+-------->
'-SAVECOUNT--n--'
'-ROWCOUNT--n--'
'-WARNINGCOUNT--n--' '-MESSAGES--message-file--'
>---INSERT----+-------->>----INTO--table-name----+------------------------------+------->>-----+----------------------------+---------------------------->
+-REPLACE---+
￨
.-,----------------.
￨
'-FOR EXCEPTION--table-name--'
+-RESTART---+
￨
V
￨
￨
'-TERMINATE-'
'-(-----insert-column---+---)--'
>-----+-------------------------------------------------------------------------------------------+>
￨
.-YES-.
￨
'-STATISTICS--+-+-----+--+-+---------------------------------------------------------+-+-+--'
￨
￨ '-WITH DISTRIBUTION--+---------------------------------+--' ￨ ￨
￨
￨
'-AND--+----------+--INDEXES ALL--'
￨ ￨
￨
￨
'-DETAILED-'
￨ ￨
￨
'-+---------------------------------------+-------------------' ￨
￨
'--+-AND-+---+----------+--INDEXES ALL--'
￨
￨
'-FOR-'
'-DETAILED-'
￨
'-NO-----------------------------------------------------------------------'
>-----+------------------------------------------------------------------+>-+---------------+---+--------------------+---------------->>-----+--------------><
￨
.-NO-----------------------------------------------------. ￨ '-HOLD QUIESCE--'
'-WITHOUT PROMPTING--'
'-INDEXING
MODE--+-AUTOSELECT--+--'
+-COPY--+-YES--+-USE TSM--+---------------------------+--------+-+-+
+-REBUILD-----+
￨
￨
'-OPEN--num-sess--SESSIONS--'
￨
￨
+-INCREMENTAL-+
￨
￨
.-,-------------------.
￨
￨
-DEFERRED----'
￨
￨
V
￨
￨ ￨
￨
+-TO-----device/directory---+-------------------+
￨
￨
'-LOAD--lib-name--+---------------------------+-'
￨
￨
'-OPEN--num-sess--SESSIONS--'
￨
'-NONRECOVERABLE---------------------------------------------------'
Systems Solution & Web Server Competence Center No.1 DM Group
29-30
添付資料
Systems Solution & Web Server Competence Center No.1 DM Group
添付 表の制約
表は列と行からなり、順序付けられていないデータ・レコードの集合を保管します。
表に制約を設けることによって、表の間の関係、表の内部での関係などを維持でき、データレコードが固有であることを保証できます。制約はデー
タべース・マネージャーが適用する規制です。次の3種類の制約があります。
●固有制約
キーの値が表の中で固有
NOT NULL
primary 文節、または unique 文節
create table 文、または alter table 文で定義
●参照保全
department表（親表）
INSERT 規則
暗黙的に適用
deptname
mgrno
deptno
DELETE 規則
(primary
key)
RESTRICT,NO ACTION,CASCADE,SET NULL
UPDATE 規則
empno
RESTRICT, NO ACTION
(primary key) firstname
●検査制約
表レベルでデータ保全性を定義
employee表(従属表）
列に許可する値を指定
表の作成時、alter table 文を使用して定義
insert,update が実行されるたびに検査がおこなわれる
検査制約の定義はシステム・カタログ表に保管
SYSIBM.SYSCHECKS
検査制約の追加
全ての行が検査制約に適合している -検査制約は正常に作成
一部またはすべての行が検査制約に適合しないｰ 検査制約は作成されない
検査制約のオフ
set integrity
検査制約の変更
alter table employee drop constraint check_job
alter table employee add constraint check_job check(job in ('operator','clerk'))
Systems Solution & Web Server Competence Center No.1 DM Group
31-32
lastname
workdept
(foreign key)
phoneno