スケジュール(実行順序,起動時刻)の妥当性をSQLで確認する
はじめに
こんにちはキャスレーコンサルティングの岩井です。
今回はSQLについて書いてみます。
とある問題の調査のために考えたSQLの紹介です。
Tipsみたいな感じになりますが…
スケジュール(実行順序,起動時刻)データの妥当性を確認するためにSQLを考えたのですが、
思ったより頭を使いました(→おもしろかった)ので書いてみようかと。
スケジュールデータの仕様
あるシステムのスケジューラーはDB(Oracle)に
プログラムの「実行順序」と「起動時刻」を保持しています。
(「スケジュールTBL」に保持していることとします)
例1.スケジュールTBL
| 実行順序 | 起動時刻 | プログラム |
|---|---|---|
| 1 | 0900 | A |
| 2 | 1200 | B |
| 3 | 1500 | C |
スケジューラーは「実行順序」昇順に、
「起動時刻」を迎えたプログラムを実行していくといったものです。
「実行順序」は、前のプログラムが終了しないと次のプログラムは実行できません。
09:00にプログラムAを実行
12:00にプログラムBを実行
15:00にプログラムCを実行
18:00にプログラムDを実行
21:00にプログラムEを実行
その他に
・「実行順序」は1以上の整数
・「実行順序」の重複はNG
・「実行順序」の欠番はOK
・「起動時刻」の重複はOK
・「起動時刻」に設定できるのは00:00～23:59(24h以内に完結すること)
・「実行順序」の昇順≠「起動時刻」の昇順 でもOK
(「実行順序」と「起動時刻」の逆転OK…日跨ぎOK)
といったルールがあり、下記例2.のような設定が可能です。
例2.スケジュールTBL
| 実行順序 | 起動時刻 | プログラム |
|---|---|---|
| 1 | 2000 | A |
| 6 | 2300 | B |
| 11 | 0200 | C |
| 16 | 0200 | D |
| 21 | 0500 | E |
20:00にプログラムAを実行
23:00にプログラムBを実行
02:00にプログラムCを実行
プログラムC終了後にプログラムDを実行
05:00にプログラムEを実行
とある問題
上記のようなシンプルなスケジューラーですが、データのチェックが行われておらず…
結果、以下のようなデータが設定されている可能性があり、
スケジューラーがまともに動けない(かもしれない)という問題がありました。
＆そんなスケジューラーが数十の環境にリリースされている…
例3.スケジュールTBL
| 実行順序 | 起動時刻 | プログラム |
|---|---|---|
| 1 | 0900 | A |
| 2 | 1500 | B |
| 3 | 1200 | C |
(問題点)
プログラムCは12:00に実行したいようだが
12:00には実行されず、プログラムBを15:00に実行した後に実行される
→ 実行順序の誤り？起動時刻の誤り？
例4.スケジュールTBL
| 実行順序 | 起動時刻 | プログラム |
|---|---|---|
| 1 | 1200 | A |
| 6 | 1800 | B |
| 11 | 0000 | C |
| 16 | 0600 | D |
| 21 | 1300 | E |
(問題点)
プログラムEは、プログラムD実行後の13:00に実行したいようだが、
プログラムDを06:00に実行した後、13:00前に実行される12:00には実行されず
(スケジュールが24h以上設定されている(開始：12:00、終了：(翌日)13:00))12:00には実行されず
→ 実行順序の誤り？起動時刻の誤り？
仕様をまとめると
そんなNGデータが設定されているかもしれないスケジューラー、
1環境ずつデータを直接確認するのではなく、SQLで1発で OK/NG を判定しようと考えました。
ということで、データの仕様(あるべき形)をまずは考えました。
結果、
・「実行順序」昇順＝「起動時刻」昇順 であればOK
・「実行順序」と「起動時刻」の逆転は1回までOK
(ただし、実行順序MINの起動時刻＞実行順序MAXの起動時刻)
のいずれかを満たす場合はOK。
ということは、NGの条件は…
・「実行順序」と「起動時刻」の逆転が2回以上
・「実行順序」と「起動時刻」の逆転が1回 且つ 実行順序MINの起動時刻≦実行順序MAXの起動時刻
のいずれかを満たすということに。
SQLを考える
上記条件を満たすSQLを考えると…
「実行順序」昇順で並べたレコードと「起動時刻」昇順で並べたレコードが一致するか
を確認すれば、逆転の数がカウントできるのでは？
と最初考えましたが、それではうまくいかず…
結局、逆転をカウントするには
「実行順序」が昇順で次のレコードの「起動時刻」が
自身の「起動時刻」未満のレコード件数をカウント
すれば良いという結論に至りました。
で、どうすれば!?
|
|
上記のようにレコードをつなげれば「次の起動時刻」がわかります。
じゃあ、どうやってつなぐ？
「次の実行順序」＝「実行順序」＋1
のように見えますが、「実行順序」は連番ではないので結合要素としては使えません…
じゃ採番しなおしましょう！
| 実行順序 | 新実行順序 |
|---|---|
| 1 | 1 |
| 6 | 2 |
| 11 | 3 |
加えて、それぞれ余分なレコードが1件ずつ(MIN実行順序のレコードとMAX実行順序のレコード)ありますが…
取り除きましょう！
ということでSQLにすると…
SELECT
COUNT(C.*) AS 逆転回数
FROM
(
SELECT – 実行順序MAX以外のレコードの起動時刻を実行順序昇順に採番して取得
ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
A.起動時刻 AS 起動時刻
FROM
スケジュールTBL A
WHERE
A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
) C,
(
SELECT – 実行順序MIN以外のレコードの起動時刻を実行順序昇順に採番して取得
ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
A.起動時刻 AS 起動時刻
FROM
スケジュールTBL A
WHERE
A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
) D
WHERE
C.新実行順序=D.新実行順序 AND
C.起動時刻>D.起動時刻
C … 実行順序MAX以外のレコードの起動時刻を実行順序昇順に採番(新実行順序)して取得 D … 実行順序MIN以外のレコードの起動時刻(次起動時刻)を実行順序昇順に採番(新実行順序)して取得
「新実行順序」は、CとDを結合するための値です。
CとDを結合し、「起動時刻」と「次起動時刻」を求め比較します。
例3.で考えると
C(例3)
| 新実行順序 | 起動時刻 |
|---|---|
| 1 | 2000 |
| 2 | 0000 |
| 3 | 0800 |
| 4 | 01200 |
D(例3)
| 新実行順序 | 起動時刻 |
|---|---|
| 1 | 0000 |
| 2 | 0800 |
| 3 | 01200 |
| 4 | 0400 |
C+D
| 新実行順序 | C起動時刻 | D起動時刻 | C＞D |
|---|---|---|---|
| 1 | 2000 | 0000 | TRUE |
| 2 | 0000 | 0800 | FALSE |
| 3 | 0800 | 1200 | FALSE |
| 4 | 1200 | 0400 | TRUE |
→COUNT：2
と逆転が2回あることがわかります。
このSQLに
「逆転が1回 且つ 実行順序MINの起動時刻≦実行順序MAXの起動時刻」
という条件も踏まえたものが、以下になります。
SELECT
‘スケジュールデータに問題があります’
FROM
(
SELECT – 実行順序MAX以外のレコードの起動時刻を実行順序昇順に採番して取得
ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
A.起動時刻 AS 起動時刻
FROM
スケジュールTBL A
WHERE
A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
) C,
(
SELECT – 実行順序MIN以外のレコードの起動時刻を実行順序昇順に採番して取得
ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
A.起動時刻 AS 起動時刻
FROM
スケジュールTBL A
WHERE
A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
) D
WHERE
C.新実行順序=D.新実行順序 AND
C.起動時刻>D.起動時刻
HAVING
COUNT(*)>=2 OR
(
COUNT(*)=1 AND
(SELECT E.起動時刻 FROM スケジュールTBL E WHERE
E.実行順序=(SELECT MIN(F.実行順序) FROM スケジュールTBL F))
<=
(SELECT G.起動時刻 FROM スケジュールTBL G WHERE
G.実行順序=(SELECT MIN(H.実行順序) FROM スケジュールTBL H))
)
いかがでしたでしょうか？
順序と時刻が適切に設定されているか
人が目で確認すれば簡単にわかりそうなものですが、
SQLにするとこんなにも長々としたものになります。
人間すげぇ！
とも思いますし
けど、SQLにおとせないものでもない
とも思いました。
そこが個人的に、おもしろかったところです。
ではでは
