データベーススペシャリスト 2024年 秋期 午後II 問題

共通の文章

の記述を読んで,設問に答えよ。 A社は,。法人向けに車両のリース及び保守業務を行っており,業務に車両保守シス テムを使用している。A社では,夜間に実施しているバッチ処理の昼間帯への移行を 検討している。 [車両保守システムの概要〕 1.業務の概要 (1)車両のリース提供先の法人を顧客という。顧客との間でリース契約を結び, 契約に基づいて車両を定額制で貸し出す。 (2)リース契約に基づいて新しい車両を購入することもあれば,リース契約終了 後の車両をま再リースすることもある。 (3)契約のオプションとして車両保守を受託する。車両保守には,BR,AER 検(以下,点検という),整備などの保守区分がある。複数の保守区分を受託 することもある。 (4)外部の自動車整備工場などの委託先に保守区分の実施を依頼する。 (5)受託ごとに一定期間内の保守区分を洗い出し,保守区分ごとに着手予定日を 決めて予定表を作成する。予定表に従って手配を開始し,進捗を確認する。手 配は,顧客への事前案内,入庫日の調整,委託先への実施依頼などから成る。 手配は。定められた順に実施し,各手配の完了時に進捗を記録する。車検と整 備を同時期に実施するなど,同じ車両の異なる保守区分を同時に実施すること もある。この場合,保守区分ごとに同じ予定を立てる。 (6)各車両のーつの保守区分はーつの委託先で実施する。委託先に複数の保守区 分の実施を同時に依頼することもある。委託先から保守区分の実施結果を受け 取り,進捗を記録する。実施完了の報告を受けると車両の出庫手続に移る。 (7)車両ごとに,全ての車検,点検,整備の結果及び内容を記録潤に記録する。 2.テーブル構造 車両保守システムのテーブル構造を図1に,主な列の意味・制約を表1に示す。 =Be 顧客(顧客C,顧客名,…) 委託先(委託先C,委託先名所在地,…) 契約(契約4顧客C,契約種別,車両#開始YMD,終了YMD,…) 受託(契約受託保守区分,受託開始YMD,受託終了YMD,報告間隔,次回報告YMD,…) 車両(Smt,メーカー名,BZ,グレード名,メーカー型式,保守状態,車台番号,FAR, 定員,最大積載量,次回点検期限YMD,BIT YMD,--) FER(契約#受託着手予定YMD,AM,EAH,委託先C,開始YMD,完了YMD,…) 進捗(契約4受託着手予定YMD,手配#入庫予定YMD,出庫予定YMD,、…) 実施結果(報告YMD,ソート連番,委託先C,契約4受託着手予定YMD,受信TS, 実施項目#点数,結果,作業内容,BE,単価,金額,…) 点検率(契約予定点数,実績点数) 報告(顧客C,報告明細#保守区分,保守開始YMD,保守終了YMD,保守内容。…) 記録澄(車両4記録』記録TS記録YMD,結果,内容,…) 注記属性名の“#"は番号,“C"はコード,“YM”EAR,“TS”はタイムスタンプを略した記 号である。 図1車両保守システムのテーブル構造(一部省略) 表1主な列の意味・制約 ok 契約4受託保|リース車両ごとにリース契約を結び,契約#で識別する。車検,点検,整備な 守区分どの保守区分ごとに車両保守を受託し,受託#で識別する。 手配現手配#|手配は,1:事前案内2:入庫予約受付,3:委託先手配,4:入庫,5:実施完了 予定確認。6:出庫予約受付,7:完了確認.8:出庫のいずれかである。現手配# には,予定表の手配が現在どれであるかを記録する。 ソート連番,実施|“実施結果"テーブルのソート連番は,報告YMIDごとに,委託先からの複数 項目#の報告を識別する番号である。実施項目#は,点検・検査の項目,部品交換な どの作業内容を識別する番号である。 記録#車両ごとに,車両の登録,名義変更,車検,点検,整備などの内容を発生の 順に記録#を付与して記録する。 3.主な処理 オンライン時間帯は平日の9:09一19:099 C,オンライン時間帯終了後の夜間にバ ッチ処理を行っている。オンライン処理及びバッチ処理は,それでぞれ複数のアプ リケーションプログラム(以下,APという)から成る。オンライン処理,バッチ 処理の概要及び1日の処理行数を表2,3に,バッチ処理の実行スケジュールを図 2に示す。表2,3中の二重引用符で囲んだ名前は図1中のテーブル名である。 ae 7= 表2オンライン処理の概要及び1日の処理行数 rT"mise日の処理行数 契約登録契約#ごとに"契約”及び付随する“BH”,"点検率"に行を追加,更10,000 10,000 新する。 車両登録車両#ごとに“車両"に行を追加,更新する。追加時には,基となる10.000 6 000 "契約”の車両#を更新し,“記録簿”に行を追加する。' 車両照会指定された車両#について,車両","契約",“受託,“記録簿などの 車両に関わる全記録を表示する。 書類発行“Ses”に従って顧客に送付する登録書類を作成し,"進捗"に行を :10,000 追加する。 予約登録顧客からの電話又は電子メールで受け付けた車両保守のための入庫又は 出庫に対応して,“PER”の現手配#を更新し,"進捗"に行を追加す10,000 4,000 る。 表3バッチ処理の概要及び1日の処理行数 aps処理内容処理行数 実施登録委託先から受信した保守区分の実施記録ファイルのデータについて“WR 施結果”に行を追加する。同時に実施項目#ごとに"記録簿"に行を追 加する。各委託先から1日に複数回受信するファイルを一つにまとめた|20,000 データを,報告YMD,受信TS順にソートし,報告YMDごとにソート連番 を設定する。 報告作成次回報告YMDの近い受託を対象に,一定期間の保守区分の一覧及び明細 に基づく報告について夫客別に"報告"に行を追加し,追加ごとに"受|20,000)20,000 託”の次回報告YMDを更新する。 点検抽出"車両"を参照し,一定期間内に点検期限を迎える車両を抽出して,ワ30,000 ークテーブル(WK)に行を追加する。" 車検抽出1ー定期間内に車検の期限を迎える車両を抽出してワークテーブル30,000 (WK2)に行を追加する。 車検抽出2車検抽出1の結果,“Hw,“PER”を参照して,事故による修理中 BE,一定の条件で車検不要なものを除外した上で車検が必要な車両の20,000 行をワークテーブル(WK1)に追加する。 予定表作成|抽出した車両について,保守区分の委託先,開始YMDなどを決めて,20.000 “FER”に行を追加する。1 進捗更新当日分の"実施結果"の行を基に"進捗"に行を追加,“車両",“PE 10.099|40,000 KR”を更新,"記録簿"に行を追加する。'" 依頼作成"予定表”を基に,委託先に車両ごとの保守区分の実施を依頼するため の依頼データファイルを作成する。 点検率更新“実施結果"を報告YMID,ソート連番の順に読み込み,契約#が一致す20,000 る“点検率”の実績点数に"実施結果"の点数を加算して更新する。’ 注記各APは,特に断りのない限り,処理の最後に一度だけコミットを発行するものとする。 にea 8 ees T1 T2 T3 T4 1 T6 ih a 5ーー5 のの :tae; 注記TI~T6は各契機を表し,矢線は処理実行の前後関係を表す。 図2バッチ処理の実行スケジュール | I 4.主なAPとテーブルの関係 車両保守システムの主なAPのCRUDを表4に示す。 表4主なAPのCRUD SMHMMMHMBHH還 AP名果 客|託|約|託]両|定|捗|施|検|告|録|二| 先表結|率5G 7 ewem eT lewlewl|||lel|||_u ン本名=||fet|wl||||[el|| i femme。|slmlmlmlmlmlsm|||[ml 1 m lames|s||s||mlmlc|||fel|| "Gee TT||m||mlel|||||| 実全録。||R|||mlm|fey|[ef| easter dT RT||m|||||cl||| pri Tk ee PROT||||[el 2[eit|||R|ml||||||lxl z imam?|||||]mlm|||||[ele ge|予定表作成||R||mlmlc|||||[rf| ese|||||ulmlclsm|l||cl| 依頼人成”||R||RlR|mlm||||||| pameeer||||||||Rlml|||| 注記C:追加,R:参照,U:更新 ーー9ーー 5.障害の考え方 車両保守システムでは,障害を,AP障害,サーバ障害,メディア障害の3種類 に分けて,それぞれの対策を立てている。 (1)AP障害は,APのバグ,メモリリークなどによってトランザクションの途中で APが異常終了する障害である。 (2)サーバ障害は,RDBMSが稼働するサーバが,ハードウェア異常,電源遮断な どによって機能停止する障害である。 (3)メディア障害は,RDBMSが使用する制御ファイル,表領域を物理的に格納す るストレージの故障,破損によって入出力ができなくなる障害である。 (RDBMSの主な仕様 1.表領域 (1)テーブル及び索引のストレージ上の物理的な格納場所を表領域という。 (2)RDBMSとストレージとの間の入出力単位をページという。同じページに異な るテーブルの行が格納されることはない。 2.参照時の専有ロック データ参照時にFOR UPDATE句を指定すると,ISOLATIONレベルにかかわらず,対 象行に専有ロックを掛け,トランザクション終了時に解放する。 ま。Be ログは,データより先にログバッファからストレージに出力される。これをロ グ出力処理と呼ぶド。このとき,トランザクションのコミットはログ出力処理の完 了まで待たされる。ログ出力処理は,次のいずれかの事象を契機に行われる。 (1)ログバッファが一杯になった。 (2)トランザクションがコミット又はロールバックされた。 |(3)あるテーブルのデータバッファが変更ページによって一杯になった。 A Fou OR yh (1)チェックポイントは,指定されたログの出力量になった時点,又は指定され た経過時間に従ってRDBMSが自動で取得する。 (2)チェックポイントは,コマンドを使用して任意に手動で取得することもでき る。 ー19一 5.異常終了後のRDBMS再開始 RDBMSがサーバ障害によって異常終了した後のRDBMS再開始において,RDBMSは 直前のチェックポイントから異常終了時までに完了していたトランザクションの 更新データ及び異常終了時に未完了のトランザクションの更新データを自動で回 復する。 具体的には,RDBMSはログを用いて,直前のチェックポイントから異常終了時ま でロールフォワードを行い,異常終了時点と同じデータベースの状態に回復する。 COR,異常終了時に未完了だったトランザクションをロールバックする。その 際,同じ行の更新順序を担避するために,ロールフォワードは,全てのトランザ クションのログに対して,ログが出力されている順に逐次に実行される。トラン ザクション間の排他制御によって同じ行の同時更新がないことは担保されている OT,ロールバックはトランザクションごとに並行して実行される。 6.ログなしモード ログなしモードとは,ログなしモードを指定されたテーブルの更新に対してロ グを出力しないモードである。テーブルごとにログなしモードを指定することが できる。ログなしモードのテーブルを更新するAPで実行されるトランザクション は,ログを取得しないので,その分だけトランザクションの実行時間を短縮でき る。ログなしモードのテーブルを更新するトランザクションには次の制約がある。 (1)ログなしモードのテーブルについて,トランザクション内で最初の更新時に テーブル全体に専有ロックを掛け,トランザクションがコミットされたときに 専有ロックを解放する。 (2)ログなしモードのテーブルに専有ロックが掛けられている間は,チェックポ イントを取得しない。手動でも取得できない。 (3)トランザクションがロールバックされたとき,ログなしモードのテーブルは 使用不可になる。使用不可になったテーブルは,バックアップから復元するか, テーブルを削除して再度定義(以下,再定義という)する必要がある。 7.オンラインバックアップ・回復 データベースの乏働中に更新中のデータを含めて全表領域。全ログをRDBMS OR 働環境とは異なるストレージに複写してバックアップを取得する機能を備えてい る。 ー11一 (1)専用のコマンドを実行して,全表領域,全ログのバックアップを一定の多重 度で別系のストレージに格納する。 (2)オンラインバックアップ取得開始後に発生したログは,ストレージに書き込 まれると同時にそのログのバックアップが別系のストレージに格納される。 (3)メディア障害発生時には,ストレージ交換後,全表領域,全ログ,オンライ ンバックアップ取得後に発生したログのバックアップをストレージに復元し, 異常終了後のRDBMS再開始と同様にロールフォワード及びロールバックによっ てデータを回復する。 &ロックメモり RDBMSがロックを掛けるときに排他制御のために使用するメモリ領域をロックメ モリという。1行又は1テーブルにロックを掛けるごとにロックメモリにロック情 報を登録し,そのロックを解放したらロック情報を削除する。ロックメモリは, 同時にロックを掛ける行数,テーブル数から見積もって指定されたサイズで確保 する。必要以上に大きなサイズで確保しようとするとメモリ領域が不足してRDBMS を開始できない。逆に,必要なサイズを確保していなければ,SQLの実行時にロッ クメモリの未使用領域がなくなり,ロックを掛けることができずにSQLがエラーと なる。 【現行バッチ処理のバックアップ・リカバリ〕 1.バッチ処理のバックアップの取得スケジュール 車両保守システムでは,オンライン時間帯の終了後に次の要件でバックアップ を取得している。バックアップの取得スケジュールを表5にまとめた。表5では, 図2中の各契機でテーブル単位のバックアップを取得するテーブルに“〇"”印をつ け定いる。 (1)当日のオンライン処理のAPの実行結果を全て反映した状態に回復できること |(2)全てのバッチ処理のAP(以下,バッチAPという)の実行結果を反映した状 |態に回復できること (3)それぞれのバッチAPの実行中にメディア障害が発生した場合,速やかに対象 テーブルを回復した後,バッチAPを再実行できること (4)必要最小限のバックアップを取得すること ー12一 (5)点検抽出”",“車検抽出1",“車検抽出2”の各APで更新する,“WKI",“WK2” の各テーブルには,ログなしモードを指定すること (6)“WK1”,“WK2”の各テーブルは,ワークテーブルであり,バックアップの対 象外とすること。これらのワークテーブルは,オンライン時間帯の終了後,バ ッチ処理を開始する前に再定義して使用する。 表5バックアップの取得スケジュール(未完成) RE託|約|託|両|定|捗|施|検|告|& 願|委|契|受|車|ネ|進|則|点|報| Im|||||||||||| lf||||||||||| 3|||||||lo|||ol moe tr 4 ls||||lololo||||ol Lie T|[||[||||||| 注記T1てT6は,図2中の各契機を示す。 2.サーバ障害時のRDBMS再開始 RDBMS再開始に備えて,バッチ処理中のチェックポイントに関する検証を行った。 なお,T1~T6は図2中の各契機を示す。 (1)RDBMS再開始後の運用 "実施登録"APと“車検抽出2"APの実行中に,サーバ障害が発生した場合 に,RDBMS再開始後にどのような対処が必要になるか,チェックポイントの取 得状況に応じた対処を整理した。 契機12でチェックポイントを取得している場合,“| a|”テーブルを 1L_5|しな後中6|"e[3]"4Pを下朱行する。ま た,"|6|"APも再実行する。その後,契機13以降のバッチ処理を綴 続する。 契機12でチェックポイントを取得していない場合,チェックポイントを取得 している場合の対処を行う前に,"|f|]"テーブルを|b dur 後。"| g』|"APを再実行しておく必要がある。 (2)RDBMS再開始に要する時間 契機13でチェックポイントを取得後,契機T5からT6の間でサーバ障害が発 生して再開始する状況を想定する。契機T3の後,サーバ障害が発生するまでの 間にチェックポイントは取得していないものとする。追加・更新1行当たりロ ールフォワードに2ミリ秒,ロールバックに1ミリ秒を要するものと仮定して, RDBMS再開始のときにデータベースの回復に要する時間を見積もった。 最も時間が掛からないケースは,バッチAPでの更新が発生する直前にサーバ 障害が発生した場合で,トランザクションのロールフォワードが必要なAPは [||ロールバックが必要なAPは|」|,回復に要する時間は | 」 ]秒となる。最も時間が掛かる,理論上の最悪のケースは,更新処理 のある全てのバッチAPがコミットする直前にサーバ障害が発生した場合で,ト ランザクションのロールフォワードが必要なAPは|k|,ロールバック が必要なAPは|||,回復に要する時間は[|nm|秒となる。 〔バッチ処理のオンライン時間帯への移行 2中のバッチ処理の実行順序(先行・後続の関係)を変更せずに,R3 OF AP をオンライン時間帯に移行するに当たって,APに必要な変更を検討した。 1.移行後に発生する問題 #2,3中のAPを変更せずに,表3中の全処理をオンライン時間帯に移行すると 仮定した場合,発生し得る問題を表6にまとめ,その対処を検討した。表6では, どのAPもISOLATIONレベルをREAD COMMITTEDとしてトランザクションを実行する ことを前提としている。 表6発生し得る問題 が発生する。 の現手配#が不正になることがある。 ンライン終了時刻までに終わらない。 ご1h= 2.問題への対処 表6の問題に対処して,表2,3中のいずれかのAPを変更する。 (1)問題1 この問題は,"報告作成”APが“|P|"テーブルの更新対象行に対す る|イ|を処理終了まで保持し,“契約登録”APがその解放を待つことで FETS.問題への対処として"報告作成”APを変更する。 (2)問題2 “PRISER”AP,“進捗更新”APの処理内容を図3,4に示す。図3,4では特 に断りのない限り,行の読込みには共有ロックが掛かるものとする。 この問題は,“予約登録"APが点検の出庫予約の受付中に,“進捗更新”APで 同じ受託の“PER”テーブルの現手配#を5(実施完了予定確認)から に変更し,その後で“予約登録”APが同じ現手配#を|エ| に変更することで発生する。手配#の順序の制約及びISOLATIONレベルを変えず に問題に対処するために,一つのAPを変更することにした。 受け付けた1回の入庫又は出庫の予約について,契約#受託#を指定して,次を行う。 ①“受託テーブルの行を,専有ロックを掛けて読み込む。 ②“FER”テーブルから対象行を読み込む。 ③"進捗"テーブルに行を追加する。 Q“FER”テーブルの行の現手配#が"進捗"テーブルの手配#よりも小さい番号であれば, ⑤に進み,それ以外の場合は⑥に進む。 ©“FER”テーブルの行の現手配#に手配#と同じ値を設定して更新する。 ©コミットを発行する。 図3“PHAR”APの処理内容 "実施結果"テーブルから報告YMDが当日に等しい行を読み込み,行ごとに次を行う。 OQ“進捗"テーブルに,完了確認の手配#に対応する行を追加する。 ②“FER”テーブルの該当行の現手配#に完了確認の手配#を設定して更新する。 ③保守区分が車検で,"進捗"テーブルの手配#が完了確認の場合は,“車両”テーブルの車検満 了YMDを次回の車検満了日に更新する。 ④“記録簿"テーブルに行を追加する。 ©コミットを発行する。 図4“進捗更新"APの処理内容 (3)問題3 処理時間の短縮を図るため,"点検率更新”APを同じ報告YNIDの中で重複し ていないソート連番の範囲を指定することで,ジョブを多重処理できるように 変更した。"点検率更新”APの処理内容を図5に示す。 ーーニーーー ②“KHER”テーブルから指定された引数に報告YMDが一致し,開始連番から終了連番まで ③読み込んだ行ごとに,契約#が一致する"点検率”テーブルの行を読み込み,実績点数に点数 ④©,③を繰り返し,結果行を全て処理したら,最後に一度だけコミットを発行する。 の間の行をソート連番順に読み込む。 を加えて更新する。 図5“点検率更新”APの処理内容 図5の処理内容変更案をレビューしたところ,APの多重処理によって,Fy ドロックが発生するおそれがあるとの指摘を受けた。デッドロックの発生を防 ぐために,次の二つの案を検討した。 案1図5中の処理内容を修正する。 22表3中の“点検率更新”AP以外のAPを一つ変更する。 [移行後の運用設計] 1.メディア障害への対策 メディア障害への対策として,オンライン時間帯に何回かのオンラインバック アップを取得し,オンライン時間帯終了後にRDBMSを停止してデータベース全体の オフラインバックアップを取得することにした。オンライン時間帯にメディア障 害が発生した場合のリカバリでは,ログを用いて回復に要する時間がリカバリ時 間の多くを占めるので,回復に要する時間を見積もり,オンラインバックアップ の取得頻度を求めた。 (1)見積りの前提 ①表2,3の1日の処理行数の合計がログ量に等しいと仮定する。1日のログ |Blt,処理行数を合計して,追加に190,000 47,更新に100,000行になる。 ②全テーブルの1ページ当たりの平均行数を20行,1ページ当たりのストレ ージへの1/0時間を19ミリ秒とし,ストレージへの1/0以外のCPU処理,索 —ig= 引探索,ネットワーク通信などに掛かる時間を考慮しないものとする。 ③ログによる回復では,追加については,ページ中の行数が平均行数を超え るまではバッファ内で処理し,更新については,バッファヒット率を9%と して試算する。 (2)見積りの結果 1日の全てのログによる回復時間は,追加では,|オ|ページの1/0が 発生するので|カ|秒,更新では,|キ|ページの1/0が発生する ので|7|秒を要する。ログ量が均等になるタイミングでオンラインバ ックアップを取得し,ログによる回復時間を5分以内にするためには,1日に [|回のオンラインバックアップを取得すればよい。 2.バッチAPの運用変更 ログなしモードのテーブルを対象に実行していた点検抽出",“車検抽出1”, "車検抽出2”OB APをオンライン時間帯に移行する影響について検証した。ロ グなしモードのテーブルを更新するAPをオンラインで実行すると,開始から完了 までの間,チェックポイントが取得されない。この間にサーバ障害が発生すると, RDBMS再開始のとき,|コ|が大量に行われるおそれがあり,回復に要する時 間が長期化するという問題がある。 そのため,ログなしモードの指定を取りやめることにした。ただし,APの処理 内容は変更しない。また,“WK1",“WK2”の各テーブルは,バックアップ対象外の ままとする。この場合,運用の変更に当たって,次の2点のリソースの見直しが必 要となる。 (1)“WKI"テーブルと“WK2”テーブルのログが新たに出力されるので,ログの 格納領域が不足しないよう見直す。 (2)“WKI"テーブルと“WK”テーブルの|サ|ので|シ|が不足し ないよう見直す。