DB, DB2の記事一覧： Javaってまだいけますか

システムカタログ参照方法をメモ。

・テーブル一覧

SELECT TABSCHEMA, TABNAME, DEFINER, TYPE, STATUS, CREATE_TIME
FROM SYSCAT.TABLES
WHERE
  TABSCHEMA = '[スキーマ名]' AND
  TYPE = 'T'
ORDER BY TABNAME

・ビュー一覧

SELECT TABSCHEMA, TABNAME, DEFINER, TYPE, STATUS, CREATE_TIME
FROM SYSCAT.TABLES
WHERE
  TABSCHEMA = '[スキーマ名]' AND
  TYPE = 'V'
ORDER BY TABNAME

・インデックス一覧

SELECT INDSCHEMA, INDNAME, DEFINER, TABNAME, COLNAMES, UNIQUERULE, CREATE_TIME
FROM SYSCAT.INDEXES
WHERE
  TABSCHEMA = '[スキーマ名]' AND
  UNIQUERULE != 'P'
ORDER BY
  TABNAME, INDNAME

・ファンクション一覧

SELECT FUNCSCHEMA, FUNCNAME, DEFINER, CREATE_TIME
FROM SYSCAT.FUNCTIONS
WHERE
  FUNCSCHEMA = '[スキーマ名]'
ORDER BY
  FUNCNAME

ロールを以下の3つに分けたと想定します。

DB_MANAGER : DB管理者(全権限)
SYSTEM_OPERATOR : システム運用保守担当者(更新可)
PUBLIC_USER : 参照専用ユーザ(参照のみ)

すると、各DBオブジェクトへのGRANTは以下のようになります。

・テーブルへのGRANT

GRANT CONTROL ON [スキーマ名].[テーブル名] TO GROUP DB_MANAGER
GRANT SELECT,INSERT,UPDATE,DELETE,REFERENCES ON [スキーマ名].[テーブル名] TO GROUP SYSTEM_OPERATOR
GRANT SELECT ON [スキーマ名].[テーブル名] TO GROUP PUBLIC_USER

・ビューへのGRANT(全部SELECT権限を付与)

GRANT SELECT ON [スキーマ名].[ビュー名] TO GROUP DB_MANAGER
GRANT SELECT ON [スキーマ名].[ビュー名] TO GROUP SYSTEM_OPERATOR
GRANT SELECT ON [スキーマ名].[ビュー名] TO GROUP PUBLIC_USER

・ファンクションへのGRANT(全部EXECUTE権限を付与)

GRANT EXECUTE ON FUNCTION [スキーマ名].[ファンクション名] TO GROUP DB_MANAGER
GRANT EXECUTE ON FUNCTION [スキーマ名].[ファンクション名] TO GROUP SYSTEM_OPERATOR
GRANT EXECUTE ON FUNCTION [スキーマ名].[ファンクション名] TO GROUP PUBLIC_USER

・プロシージャ(全部EXECUTE権限を付与)

GRANT EXECUTE ON PROCEDURE [スキーマ名].[プロシージャ名] TO GROUP DB_MANAGER
GRANT EXECUTE ON PROCEDURE [スキーマ名].[プロシージャ名] TO GROUP SYSTEM_OPERATOR
GRANT EXECUTE ON PROCEDURE [スキーマ名].[プロシージャ名] TO GROUP PUBLIC_USER

参考

外部キー制約やIdentity列がある場合にいろいろ面倒だったのでメモ。

◆エクスポート

db2 connect to [インスタンス名]

db2 export to [エクスポートファイル名] of IXF select * from [スキーマ名].[テーブル名]

db2 terminate

◆インポート

db2 connect to [インスタンス名]

rem 制約オフ
db2 set integrity for [スキーマ名].[テーブル名] off

rem ロード
db2 load client from [エクスポートファイル名] of ixf  REPLACE INTO [スキーマ名].[テーブル名] copy yes to nul

rem 制約オン
db2 set integrity for [スキーマ名].[テーブル名] immediate checked

db2 terminate

◆インポート（Identity列がある場合）

db2 connect to [インスタンス名]

rem 制約オフ
db2 set integrity for [スキーマ名].[テーブル名] off

rem ロード
db2 load client from [エクスポートファイル名] of ixf MODIFIED BY identityoverride  REPLACE INTO [スキーマ名].[テーブル名] copy yes to nul

rem 制約オン
db2 set integrity for [スキーマ名].[テーブル名] immediate checked

rem シーケンス開始値設定
db2 ALTER TABLE [スキーマ名].[テーブル名] ALTER COLUMN [Identity列名] RESTART WITH [最大値+1]

db2 terminate

[最大値+1]は事前に

SELECT MAX(Identity列名) + 1 FROM [スキーマ名].[テーブル名]

で取得しておきます。
ちなにみシーケンスオブジェクトを使ってPKを採番している場合は、

alter sequence [スキーマ名].[シーケンスオブジェクト名] restart with [最大値+1]

となります。

また、制約オフ、オンは外部キー制約の親テーブルから実行する必要があります。
あと、ロードコマンドの“copy yes to nul”をしないと、ロード完了後にテーブルがバックアップペンディング状態になります。

実際の仕事では親表から順にリストして各コマンドを生成するExcelを作って対応しました。
結構面倒ですよねぇ。

追記：
最初に制約をDROPしてインポート後に制約CREATEという方法もあるみたいです。
ERDツールでFKのDROPを生成するものもあるようですね。
こちらのブログ記事が参考になりました。

次のコマンドでlocklistを増やすといいようです。

db2 update db cfg for privacy_manager_db using LOCKLIST new_value

privacy_manager_db
Tivoli Privacy Manager が使用する DB2 インスタンスの名前
new_value
パラメーターに割り当てる値。 LOCKLIST のデフォルト値は 100。

参考にしたサイト

DB2が稼動しているサーバ上のDB2 CLP上で、以下のコマンドを実行することで確認できました。

set DB2INSTANCE=対象インスタンス名
db2 list application show detail

DB2だとLAPD関数が無いのでFUNCTIONとして自作しました。

・指定文字で埋めるバージョン

CREATE FUNCTION LPAD (STR VARCHAR(128), LEN INTEGER, PAD VARCHAR(1))
RETURNS VARCHAR(128)
LANGUAGE SQL CONTAINS SQL
RETURN (
  CASE WHEN LENGTH(RTRIM(STR)) < LEN THEN
    REPEAT(PAD, LEN - LENGTH(RTRIM(STR))) || STR
  ELSE
    STR
  END
);

・半角空白で埋めるバージョン

CREATE FUNCTION LPAD (STR VARCHAR(128), LEN INTEGER)
RETURNS VARCHAR(128)
LANGUAGE SQL CONTAINS SQL
RETURN (
  CASE WHEN LENGTH(RTRIM(STR)) < LEN THEN
    REPEAT(' ', LEN - LENGTH(RTRIM(STR))) || STR
  ELSE
    STR
  END
);

ちなみに同じ名前で引数違いのFUNCTIONを作ったら、DROPのときはシグネチャを指定します。

DROP FUNCTION LPAD (VARCHAR(128), INTEGER, VARCHAR(1));
DROP FUNCTION LPAD (VARCHAR(128), INTEGER);

残念ながら、使うときは

スキーマ名.LPAD(カラム名, 桁数, '0')

のようにスキーマ名の修飾が必要です。

DB2の定期的なメンテナンスについてのメモ。
下記４つの手順をバッチファイルにしといて定期的に実行すると良いようです。

１．テーブルを再編成する

db2 REORG TABLE [スキーマ名].[テーブル名]

２．インデックスを再編成する

db2 REORG INDEXES ALL FOR TABLE [スキーマ名].[テーブル名] ALLOW NO ACCESS

３．統計情報再収集

RUNSTATS ON TABLE [スキーマ名].[テーブル名] AND INDEXES ALL

４．すべてのデータベース・パッケージを再バインド

set DB2INSTANCE=[データベース名]
db2rbind [データベース名] -l logfile.out ALL

※４はDBサーバ上で実行する必要があるっぽい。

再編成とはデフラグみたいなものっぽいです。
パッケージの再バインドというのがよく分からないんですが。。。

参考にしたサイト