スケジュール(実行順序,起動時刻)の妥当性を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におとせないものでもない
とも思いました。
そこが個人的に、おもしろかったところです。
ではでは