DSQLによるテーブルの再構築
BI技術者の雑記
データドリブンを実現するデータ基盤ソリューション「Dr.Sum」。
「導入してはみたものの使いこなせていない」「これから使ってみたいけど分からない機能がある」…そんなお悩みも少なくないと思います。
このため、我々システムエグゼのBI部門が、少しでもお手伝いになるような情報を発信していけたらと思います。
今回は、Dr.Sumが提供するコマンドとDSQLを使用し、テーブルの再構築を自動化するバッチを作成する方法をご紹介します。
1.内容紹介
1-1 テーブルの再構築
テーブルの更新・削除が日々行われると、不要領域が発生し、テーブルファイルの肥大化や集計速度の低下を招きます。
テーブルを再構築すると、新しい領域を確保してレコードが再配置され、テーブルサイズの最小化と集計速度の改善が見込めます。
そのため、実際の運用では定期的にテーブルの再構築処理が必要です。
手動でテーブルを再構築する方法は以下になります。
- 項目一覧表示エリアで再構築するテーブルを選択し、右クリックメニューから[再構築]を選択します。
- [テーブルの再構築]画面で、実行モードとロックモードで再構築時の動作を設定します。
- 確認画面で[OK]ボタンをクリックすると、テーブルの再構築処理が開始されます。
「テーブル数が多い場合は1つ1つ手動で再構築を行うことが面倒」、「エラーの原因となるため、ユーザーアクセスとテーブル更新以外の時間帯に実施が必要!」といった悩みを持つ方も多いと思います。
テーブル再構築が自動化ができれば楽になりますね。
1-2 DSQLによるテーブル再構築自動化の実現
上記の状況を踏まえて、テーブル再構築を自動化しましょう。
ここでDr.Sumの管理者向けツール「DSQL」をご紹介します。
DSQLはコマンドでのSQL文の実行で、Dr.Sumデータベースに対して検索、追加、削除などができます。
また、Dr.SumのSQLコマンド以外に、独自のコマンドも使用できます。
次に、引数としてDSQLのコマンドが記述された外部コマンドファイルを指定して、外部プログラムを組み合わせて、「テーブルの再構築作業を自動化する」といったバッチ処理を作成しましょう。
2.バッチ作成について
2-1 ディレクトリ構成
準備作業として、以下のようにディレクトリを作成します。
ここで注意が必要ですが、confフォルダのdsqlファイルは、文字化けを防ぐために文字コードがutf8形式になります(詳細は後述します)。
●親ディレクトリ(rebuildフォルダ)
・binフォルダ rebuild.bat
・confフォルダ dsqlファイル格納ディレクトリ(※utf8形式必須)
・logフォルダ ログ出力ディレクトリ
・tempフォルダ 一時ファイル出力ディレクトリ
2-2 バッチ処理の説明
上記のrebuild.batを分けて、フロー図とバッチのサンプルソースを見てみましょう。
まず、データベース名、Dr.Sumコマンドの実行パスなどの環境変数の設定を行います。
設定が終わったら、DSQLコマンドを記述したファイル(データベース名.dsql)を呼び出し、再構築テーブルのリストファイル取得の処理に入ります。
●バッチのフロー図(環境変数の設定)
バッチ内容(環境変数の設定と再構築のリストファイルの取得):
REM 引数を定義する SET BAT_NAME=SALES SET REBUILD_MODE=0 SET LOCK_MODE=0 SET ERROR_MODE=0 SET CMD_PATH=C:DrSum55AdminTools SET BAT_PATH=%~dp0.. SET LOG_PATH=%BAT_PATH%log SET LISTFILE=%BAT_NAME%.txt SET TIME2=%TIME: =0% SET TIME3=%TIME2:~0,2%%TIME2:~3,2%%TIME2:~6,2% REM /* バッチ実行ログファイル名 */ SET LOG_FILE=%LOG_PATH%rebuild_log_%DATE:/=%_%BAT_NAME%.txt echo rebuild_start %TIME%>>%LOG_FILE% call :get_lists %BAT_NAME% if not %errorlevel% == 0 goto error call :convert if not %errorlevel% == 0 goto error :get_lists del /q %BAT_PATH%temp%1_tmp.txt REM 再構築のリストファイル(データベース名_tmp.txt)を取得する call %CMD_PATH%binDWCMDW %BAT_PATH%conf%1.dsql exit /b
リストファイルの取得処理を行う場合、呼び出されたファイル「データベース名.dsql」は以下の内容で動きます。
●dsqlファイルのフロー図(呼び出されたリストファイル取得処理)
●dsqlファイル内容(呼び出されたリストファイル取得処理)
REM Dr.Sum Serverにログインする login ユーザーID/パスワード@ホスト名またはIPアドレス:ポート番号; REM テーブル再構築したいデータベースに接続する connect データベース名; REM リストファイル(データベース名_tmp.txt)に、④のコマンドと共に実行結果も出力する append -ret X:/rebuild/temp/データベース名_tmp.txt; REM システムテーブルに、類別がテーブルのテーブル名を探します。また、再構築したくないテーブルがある場合、 "" の箇所に自由追加できる SELECT distinct table_name FROM __all_tables__ where assortment="table" and table_name not in ("","")order by 1; REM 出力を終了させる append off; REM Dr.Sum Serverにログアウトする logout; REM DSQLを終了する exit;
エラーが発生した場合、エラーログが出力されます。
正常終了すると、次の文字変換処理に移行します。
ここで注意が必要なのは、出力されたリストファイルを読み取る際、日本語の場合は文字化けしてしまうことがあります。
そのため、文字変換処理にpowershellでUTF-8の文字コードを指定してリストファイルを読み込み、設定ファイルに書き込みます。
文字変換処理が正常終了したら、繰り返し処理で設定ファイル中のテーブル名を読み込んで、最後のテーブル再構築処理を行います。
文字コード変換処理でエラーが発生した場合、エラーログの出力に分岐します。
●バッチのフロー図(設定ファイル作成とテーブル再構築の処理)
●バッチ内容(再構築用の設定ファイル作成とテーブルの再構築)
:convert REM リストファイル読み込み、再構築のテーブルリストを設定ファイルに書き込む powershell -NoProfile -ExecutionPolicy Unrestricted -Command "& { get-content -Encoding UTF8 %BAT_PATH%temp%BAT_NAME%_tmp.txt | Set-Content %BAT_PATH%temp%BAT_NAME%.txt}" exit /b REM 設定ファイル(データベース名.txt)を1行ずつ読み込んで、現行環境のDBデータをテーブル単位で再構築する CD /D %BAT_PATH%temp FOR /F "skip=2 tokens=* usebackq" %%a IN (%LISTFILE%) DO ( echo %%a !TIME!>>%LOG_FILE% call %CMD_PATH%cmdJPNdwtab_rebuild "localhost" 6001 "Administrator" "" %BAT_NAME% %%a %REBUILD_MODE% %LOCK_MODE% %ERROR_MODE% ) echo rebuild_end %TIME%>>%LOG_FILE% if not %errorlevel% == 0 goto error goto end
バッチ処理のフローは以上になります。
2-3 テーブル再構築のコマンドについて
続いて、テーブル再構築コマンド「dwtab_rebuild」の書式と実行例をご紹介します。
●書式
dwtab_rebuild
※詳細はDr.Sum Ver.5.5マニュアルをご参照ください。
<参考サイト>
Dr.Sum Ver.5.5マニュアル 2-21 dwtab_rebuild(テーブルを再構築する)
●実行例
データベース「SALES」のテーブル「Sample」を中断モードで完全リビルドし、エラー発生した際に処理を中断します。
dwtab_rebuild "localhost" 6001 "Administrator" "" "SALES" "Sample" 0 0 0
上記の内容で作成し、バッチをタスクスケジューラに登録すると、夜間に自動的に実行して、テーブル再構築ができるようになります。
3.バッチ処理の実行について
では、バッチを実行してテーブルを自動再構築してみましょう。
3-1 実行前のテーブル
ROWIDはテーブル内のレコードの管理番号です。
例としてテーブル「受注データ」のROWIDを確認すると、データ件数が9179件なのに、ROWIDが10000存在しています。
また、赤枠の部分を見ると、不連続なレコードを配置していることがわかりました。
これはテーブル内のデータを削除した後も、そのレコードはテーブル内から完全に削除されず、削除済みレコードとして残っているためです。
3-2 外部コマンドファイルの編集
今回は、データベース「SALES」のテーブル「SStore」以外のテーブル再構築を想定して、バッチを実行します。
そのため、DSQLコマンドを記述したファイル(データベース名.dsql)を以下のように編集し、SStoreを「””」に追加しました。
3-3 バッチ実行
準備ができたら、「SALES」のテーブルを再構築しましょう。
コマンドプロンプトでバッチを実行します。
tempフォルダを確認して、リストファイルと設定ファイルが出力されました。
SStore以外のテーブル名が正しく出力できました。
続いて、logフォルダを確認すると、テーブル再構築に関するログが出力されました。
SStore以外のテーブル名が再構築されました。
再構築後のテーブル「受注データ」のデータを確認すると、テーブル内のROWIDが再配置され、データ件数と一致しました。
4.テーブル再構築の注意事項
テーブル再構築について、4つの注意事項があります。
①テーブル再構築の必要性があるかどうかを判断します。
判断基準として、Enterprise Managerの[データファイル情報]画面で確認できます。
テーブル再構築には、2つのモードがあります。
●「簡易リビルド」モード:
不要領域だけ削除します。
件数と最大ROWIDに大きな差がある場合は、簡易リビルドの検討が必要です。
また、ROWIDは上限値2,147,418,112に達する前に簡易リビルドが必要となります。
●「完全リビルド」モード:
不要領域を削除してインデックスを再配置します。
最大NIDとカーディナリティに大きな差がある場合は、完全リビルドの検討が必要です。
また、NIDは上限値2,147,418,112に達する前に完全リビルドが必要となります。
※最大NIDが削除された行も含んだ値で、カーディナリティが有効な行から算出した値です。
②データベースサイズが大きい、また、テーブル数が多い場合、再構築には時間がかかります(特に完全再構築)。
③以下の時間帯を避けて、再構築を実施してください。
- エラーとなるため、ユーザーアクセス以外の時間に再構築します。
- テーブルがデータ更新でロックされているため、更新以外の時間に再構築します。
④不要領域が存在しないことで、再構築前後のファイルサイズが変わらない可能性があります。
※ テーブル再構築のタイミング判断については下記のDr.Sum Ver. 5.5マニュアルをご参照ください。
5.おわりに
DSQLを利用してバッチを作成することで、テーブル再構築が自動化できるようになります。
今回の記事を是非ご参考にしていただき、定期運用の際にご活用いただければ幸いです。
最後までお読みいただきありがとうございました。