NoteBook > DB2 Tips
DB2 Tips
主に、DB2のCLP（コマンド行プロセッサー）の使用方法および、DB2特有のコマンドやSQLについての情報。
より正確で詳細な情報は下記のサイトを参照してください。
IBM データベース(DB2) - Japan
データベース(DB2) マニュアル
本ページの内容は、DB2 CLPを対象としています。DB2v9.7からの新機能であるCLP Plusについての記述ではありませんのでご注意ください。CLP PlusはOracleのSQL*Plusと互換性のあるコマンド行プロセッサーです。
関連コマンド
DB2のバージョンを調べる
$ db2level環境変数の設定
$ db2set 環境変数=値CLPの起動方法とオプション
CLPは、DB2の対話型コマンドラインインターフェースです。
起動方法
$ db2
または
$ db2 [オプション]起動時に下記のオプションを指定することができます。
| オプション | 説明 | デフォルト設定 |
|---|---|---|
| -a | SQLCAを表示する | OFF |
| -c | 自動コミット | ON |
| -e | SQLCODE/SQLSTATEを表示する | OFF |
| -f | 入力ファイルから読み込む | OFF |
| -l | 履歴ファイルにコマンドのログをとる | OFF |
| -n | 改行文字を除去する | OFF |
| -o | 出力を表示する | ON |
| -p | db2対話式プロンプトを表示する | ON |
| -r | 出力報告をファイルに保管する | OFF |
| -s | コマンドエラーで実行を停止する | OFF |
| -t | ステートメント終了文字を設定する | OFF |
| -v | 現行コマンドをエコーする | OFF |
| -w | FETCH/SELECT警告メッセージを表示する | ON |
| -x | 列見出しの印刷を抑制する | OFF |
| -z | 出力ファイルにすべての出力を保管する | OFF |
例えば、-tを指定するとステートメントの終了文字が';'となります。
select count(*) from tbl_exsample;コマンド名に続けて、ステートメントを記述して実行することもできます。
DB2プロンプトが表示されないため、quitで終了する必要がなく、shellプログラミングに便利です。
下記のコマンドは、順番に実行することによりDBに接続した後、SQLを実行して結果をファイルに格納します。
※windows環境では、db2ではなく、db2cmdかもしれません
$ db2 connect to データベース名$ db2 set schema スキーマ名$ db2 'select * from テーブル名' > output.txt主要なCLP用コマンド
CLPの終了
quitデータベースへの接続
connect to データベース名ユーザーの切り替え
connect to データベース名 user ユーザー名スキーマの設定
set schema スキーマ名コマンド一覧の表示
?コマンドのヘルプ
? コマンド名コマンドラインオプションのヘルプ
? optionヘルプ画面のヘルプ
? helpCLPのオプションの状態の表示
list command optionsオートコミットのoff
update command options using c offテーブル操作関連
テーブル一覧の表示
CLPでの一般的な方法（for all の他に for system , for schema スキーマ名 , for user）list tables for allselect table_name from sysibm.tablesテーブル定義の表示
describe table テーブル名テーブルのコピー（データなし）
create table 新テーブル名 like 元テーブル名テーブルの作成
create table 新テーブル名 ( テーブル属性定義 )テーブルの削除
drop table テーブル名テーブルの全レコード削除
普通の方法（遅い。件数が多い場合はトランザクションログがオーバーする可能性がある）delete from テーブル名truncate table テーブル名 immediateimport from /dev/null OF DEL REPLACE INTO テーブル名alter table テーブル名 activate not logged initially with emptyテーブル名の変更
rename table 旧テーブル名 to 新テーブル名列の追加（char(10)の列を追加し、NOT NULL制約とデフォルト値を設定)
alter table テーブル名 add 列名 char(10) not null with default ''列の削除や列名の変更について(v8.2から可能)
altobjというストアドプロシージャを使用するか、コントロールセンターで行います。
どちらも内部では表の作り変えをおこなっているようです
ビュー関連
ビューの一覧の表示
select table_name from sysibm.views where table_schema = スキーマ名ビューの作成
create view ビュー名 [ (列名) ] as SELECT文ビューの削除
drop view ビュー名インデックス関連
インデックスの一覧の表示
select name , tbname from sysibm.sysindexesテーブルのインデックスの表示
describe indexes for table テーブル名又は
select name from sysibm.sysindexes where tbname = テーブル名一意インデックスの作成
create unique index インデックス名 on テーブル名 ( 列名 [ , ... ] )非一意インデックスの作成（重複した値を持つ列へのインデックス）
create index インデックス名 on テーブル名 ( 列名 [ , ... ] )インデックスの削除
drop index インデックス名インデックスの再構築（断片化の解消）
reorg indexes all for テーブル名日付と時刻
現在日付を取得する
select current date from sysibm.sysdummy1現在時刻を取得する
select current time from sysibm.sysdummy1現在日付と時刻を取得する（YYYY-MM-DD HH:MM:SS）
select ( char(current date) || ' ' || char(current time,jis) ) as datetime from sysibm.sysdummy1曜日を取得する(1:日曜日～7:土曜日)
select dayofweek(current date) as week from sysibm.sysdummy1１日前の日付を取得する
select current date -1 days from sysibm.sysdummy1
※使用できるキーワード
year , years , month , months , day , days , hour , hours , minute , minutes , second , seconds , microsecond , microseconds
HOGEテーブルのdate型の列FUGAが前月以前の場合'1'を、当月の場合は'2'を取得する
select case when year(FUGA) < year(current date) or month(FUGA) < month(current date) then '1' else '2' from HOGE日付関係の特殊レジスター
| 特殊レジスター | 説明 | 別名 |
|---|---|---|
| CURRENT DATE | 現在日付 | CURRENT_DATE |
| CURRENT TIME | 現在時刻を返します | CURRENT_TIME |
| CURRENT TIMESTAMP | タイム・スタンプを返します | CURRENT_TIMESTAMP |
| CURRENT TIMEZONE | 世界標準時と現行サーバーでの地方時との差を指定します。地方時から CURRENT TIMEZONE を引くと、その地方時が UTC に変換されます | CURRENT_TIMEZONE |
日付関係のスカラー関数
| スカラー関数 | 説明 | 使用例 |
|---|---|---|
| CURDATE | 現在日付を返します(CURRENT DATEと同じ) | CURDATE() |
| CURTIME | 現在時刻を返します(CURRENT TIMEと同じ) | CURTIME() |
| DATE | 指定された値に基づく日付を返します | DATE('1995-11-21') |
| DAY | 指定した値の日の部分を返します | DAY(NOW()) |
| DAYOFMONTH | 指定した値の日の部分を返します | DAYOFMONTH(NOW()) |
| DAYOFWEEK | 曜日を表す 1 から 7 までの整数 (1 は日曜日を表し、7 は土曜日を表す) を返します。 | DAYOFWEEK(NOW()) |
| DAYOFYEAR | 年間通算日を表す 1 から 366 までの整数 (1 は 1 月 1 日を表す) を返します。 | DAYOFYEAR(NOW()) |
| DAYS | 日付の整数表現を返します | DAYS(NOW()) |
| HOUR | 指定した値の時の部分を返します | HOUR(NOW()) |
| MICROSECOND | 値のマイクロ秒の部分を返します | MICROSECOND(NOW()) |
| MINUTE | 指定した値の分の部分を返します | MINUTE(NOW()) |
| MONTH | 指定した値の月の部分を返します | MONTH(NOW()) |
| NOW | タイム・スタンプを返します(CURRENT TIMESTAMPと同じ) | NOW() |
| QUARTER | 日付が存在する四半期を表す 1 から 4 までの整数を返します。 | QUARTER(NOW()) |
| SECOND | 指定した値の秒の部分を返します | SECOND(NOW()) |
| TIME | 指定された値から時刻を返します | TIME('23:10:51') |
| TIMESTAMP | 指定された 1 つの値または 2 つの一対の値からタイム・スタンプを返します | TIMESTAMP('1995-11-21', '23:10:51') |
| WEEK | 年間通算週を表す 1 から 54 までの範囲の整数を返します | WEEK(NOW()) |
| YEAR | 指定された値の年の部分を返します | YEAR(NOW()) |
行番号（検索結果に番号を振る）
取得順に番号を振る
select row_number() over() as row_num , 列名 from テーブル名ソート結果に番号を振る
select row_number() over() as row_num , 列名 from テーブル名 order by 列名最初の10行を取り出す
select 項目名 from テーブル名 fetch first 10 rows only11行目以降を取り出す
select 項目名 from ( select row_number() over() as row_num , 列名 from テーブル名 ) as tbl1 where row_num > 1011行目以降の10行を取り出す
select 項目名 from ( select row_number() over() as row_num , 列名 from テーブル名 ) as tbl1 where row_num > 10 fetch first 10 rows onlyエクスポート
構文
EXPORT TO エクスポート先ファイル OF データ形式
[ LOBS TO LOBデータ出力先 LOBFILE LOBデータファイル接頭辞 ]
[ MODIFIED BY オプション ]
[ MESSAGES メッセージ出力先ファイル ] SELECT文
- エクスポート先ファイル
- エクスポートしたデータを格納するファイルのパスを指定します。
- データ形式
-
ASC: 区切りなしASCIIフォーマット
DEL: 区切り文字付きASCIIフォーマット。
IXF: 表の型情報なども含むバイナリー形式。
WSF: ワークシートフォーマット。Lotusで使用します。 - LOBデータ出力先
- 出力先のディレクトリを相対パス又は絶対パスで指定します。ディレクトリは/で終わる必要あります
- LOBデータファイル接頭辞
- LOGデータ出力ファイルのプレフィックスを指定します。複数ファイルを扱う場合はファイル名の後ろに３桁の数字の連番が振られます。
- オプション
-
lobsinfile: ファイルにLOBデータのパスが含まれます。
chardel: 指定すると列を引用符(')で括ります。
chardel"": 指定すると列を二重引用符(")で括ります。
codepage=コードページ: DEL形式の際のコードページを指定します。LOBSINFILEと同時には指定できません。 - メッセージ出力先ファイル
- コマンドの出力メッセージを記録するファイルパスを指定します。省略すると標準出力となります。
※DB2プロファイル変数(DB2_LOAD_OLD_LOBFILES)の値がOFFの場合、LOBSを含む複数のレコードを一度に扱えません。
db2setの実行権限があるならば、下記のコマンドを発行することで扱えるようになります。
$ db2set DB2_LOAD_OLD_LOBFILES=ON通常のテーブルの全レコードエクスポート(tbl_exsampleの全件をexp_fileにエクスポート)
$ db2 export to exp_file of del select * from tbl_exsampleLOBデータを含むテーブルのエクスポート(上記に加えてlobファイルは./explob として出力)
$ db2 export to exp_file of del lobs to ./ lobfile explob modified by lobsinfile select * from tbl_exsampleLOBデータを含むテーブルのエクスポート(LOBデータが1000件以上の場合)
lobデータ1000件毎に1つのファイル名が使用されるため、件数に合わせてファイル名を複数指定します。
$ db2 export to exp_file of del lobs to ./ lobfile explob1 , explob2 , explob3 modified by lobsinfile select * from tbl_exsampleインポート
構文
IMPORT FROM インポート元ファイル OF データ形式
[ LOBS FROM LOBディレクトリ [ MODIFIED BY オプション ]
[ ALLOW WRITE ACCESS ]
[ COMMITCOUNT コミットカウント ]
[ RESTARTCOUNT リスタートカウント ]
[ MESSAGES メッセージ出力先ファイル ]
インポート方法
テーブル名
- インポート元ファイル
- インポートするデータを格納しているファイルのパスを指定します。
ALLOW WRITE ACCESS- インポート実行中の表に対する読み書きを許可します。インポート方法がREPLACEもしくはCREATEの場合は指定できません。
- コミットカウント
- インポート処理中のコミットを何行毎に行うかを指定します。
- リスタートカウント
- インポート元ファイルの何行目からを対象とするのか指定します。
- インポート方法
INSERT INTO: 追加INSERT_UPDATE INITO: 追加（同一キーは上書き）REPLACE INTO: テーブルの中身を入れ替えます。REPLACE_CREATE INTO: テーブルの中身を入れ替えます。テーブルがない場合は作成します。CREATE INTO: テーブルを作成して挿入します。- テーブル名
- インポート先のテーブル名を指定します。インポート方法がCREATE INTOの場合は、下記のようにテーブル名に続けてスペースの指定が可能です。
テーブル名 [IN テーブルスペース名 [ INDEX IN インデックス用テーブルスペース名 ][ LONG IN LONGテーブルスペース名 ]]
その他の内容はエクスポート時と同じです。
通常の全レコードのインポート
$ db2 import from exp_file of del replace into tbl_exsampleLOBデータを含むインポート
$ db2 import from exp_file of del lobs from ./ modified by lobsinfile replace into tbl_exsample