An article by
Fabry19dice
Commenti
Ecco un modo per gestire in maniera sequenziale la lavorazione di tutti gli step all'interno di un solo JOB.
1) Primo passo da fare è creare un Job con tutti gli step che dovranno essere lavorati e chiamarlo BONUS_TRACK.
2) Creiamo una Stored Procedure chiamata Run_wait_job_2.
CREATE PROCEDURE [dbo].[Run_Wait_Job_2]
@Nome_Job AS SYSNAME,
@Stato_Job AS INT OUTPUT
AS
--DECLARE @Nome_Job AS VARCHAR(50)
--DECLARE @Stato_Job AS INT
--SET @Nome_Job = 'PIPPO'
--SET @Stato_Job = 0
---((( Run_Wait_Job
---((( Run_Wait_Job
---((( Run_Wait_Job
---/// VARIABILI DI LAVORO
DECLARE @Riga AS VARCHAR(255)
DECLARE @RSql AS VARCHAR(1000)
DECLARE @ERR AS INT
DECLARE @RC AS INT
DECLARE @Ind AS INT
DECLARE @Ind_Mon AS INT
DECLARE @NumRec AS INT
DECLARE @Ret_Code AS INT
DECLARE @Run_Job_Ini_D AS DATETIME
DECLARE @Run_Job_Ini AS VARCHAR(20)
DECLARE @Secs_Past AS INT
DECLARE @Secs_Inter AS INT
DECLARE @JOB_Secs_Min AS INT
DECLARE @JOB_Secs_AVG AS INT
DECLARE @JOB_Secs_Max AS INT
DECLARE @Step_Ret_Code AS INT
DECLARE @Time_Now AS DATETIME
DECLARE @Secs_Inter_T AS VARCHAR(9)
DECLARE @Secs_Inter_HH AS INT
DECLARE @Secs_Inter_MM AS INT
DECLARE @Secs_Inter_SS AS INT
DECLARE @Fine_Wait AS INT
DECLARE @Job_Status AS VARCHAR(40)
--- cancello tabella temporanea se esiste
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'zz_Run_Wait_Job')
DROP TABLE zz_Run_Wait_Job
--- cancello tabella temporanea se esiste
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'zz_Run_Wait_Job_2')
DROP TABLE zz_Run_Wait_Job_2
--- cancello tabella temporanea se esiste
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'zz_Run_Wait_Job_3')
DROP TABLE zz_Run_Wait_Job_3
---((( LANCIO JOB ESPORTAZIONE
--- memorizzo ora inizio lancio
SET @Run_Job_Ini_D = GETDATE()
SET @Run_Job_Ini = CAST (DATEPART (YEAR, @Run_Job_Ini_D ) AS VARCHAR(9))
SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(MONTH, @Run_Job_Ini_D ) AS VARCHAR(9)), 2)
SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(DAY, @Run_Job_Ini_D ) AS VARCHAR(9)), 2)
SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(HOUR, @Run_Job_Ini_D ) AS VARCHAR(9)), 2)
SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(MINUTE, @Run_Job_Ini_D ) AS VARCHAR(9)), 2)
SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(SECOND, @Run_Job_Ini_D ) AS VARCHAR(9)), 2)
---///
PRINT '@Run_Job_Ini: ' + @Run_Job_Ini
---((( RUN!
EXEC @Ret_Code = msdb.dbo.sp_start_job @job_name = @Nome_Job
---((( ROUTINE di controllo fine job
---((( ROUTINE di controllo fine job
---((( ROUTINE di controllo fine job
SET @IND = 0
SET @Ind_Mon = 1
SET @Secs_Inter = 60
---((( CREO E POPOLO temporanea steps History
SELECT
msdb.dbo.sysjobs.NAME AS Nome_Job,
msdb.dbo.sysjobhistory.step_name AS Nome_Step,
RIGHT('000000' + CAST(msdb.dbo.sysjobhistory.run_duration AS VARCHAR(22)), 6) AS Time_HMS
INTO
zz_Run_Wait_Job
FROM
msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobhistory
ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id
WHERE
msdb.dbo.sysjobhistory.step_name = '(Job outcome)'
AND msdb.dbo.sysjobs.NAME = @Nome_Job
---((( ... affinamento
SELECT
Nome_Job,
JOB_Secs = (CAST(LEFT(Time_HMS, 2) AS INT) * 3600
+ CAST(SUBSTRING(Time_HMS, 3, 2) AS INT) * 60
+ CAST(RIGHT(Time_HMS, 2) AS INT) )
INTO
zz_Run_Wait_Job_3
FROM
zz_Run_Wait_Job
---((( stats tempi run jobs
SET @JOB_Secs_Min = (SELECT MIN(JOB_Secs) FROM zz_Run_Wait_Job_3)
SET @JOB_Secs_AVG = (SELECT AVG(JOB_Secs) FROM zz_Run_Wait_Job_3)
SET @JOB_Secs_Max = (SELECT MAX(JOB_Secs) FROM zz_Run_Wait_Job_3)
--- monitoring
PRINT CONVERT(VARCHAR(30), GETDATE(), 113)
SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - Stats orginal'
SET @Riga = @Riga + ' Min: ' + ISNULL(CAST(@JOB_Secs_Min AS VARCHAR(9)), '--')
SET @Riga = @Riga + ' AVG: ' + ISNULL(CAST(@JOB_Secs_AVG AS VARCHAR(9)), '--')
SET @Riga = @Riga + ' Max: ' + ISNULL(CAST(@JOB_Secs_Max AS VARCHAR(9)), '--')
PRINT @Riga
EXEC [FLUSSI].[dbo].[LogInsert2] 4, @Riga
---((( ritocco statistiche per margini sicurezza
SET @JOB_Secs_Min = @JOB_Secs_Min - @JOB_Secs_Min / 10
SET @JOB_Secs_Max = @JOB_Secs_Max + @JOB_Secs_Max / 10
--- e correzione per valori anomali
SET @JOB_Secs_Min = ISNULL(@JOB_Secs_Min, 30)
SET @JOB_Secs_Max = ISNULL(@JOB_Secs_Max, 3600)
SET @JOB_Secs_AVG = ISNULL(@JOB_Secs_AVG, 60)
IF @JOB_Secs_Min < 30
SET @JOB_Secs_Min = 30
IF @JOB_Secs_Max <= @JOB_Secs_Min
SET @JOB_Secs_Max = @JOB_Secs_Min * 10
IF @JOB_Secs_Max < 1000
SET @JOB_Secs_Max = 1000
IF @JOB_Secs_AVG < @JOB_Secs_Min
SET @JOB_Secs_AVG = (@JOB_Secs_Min + @JOB_Secs_Max) / 2
--- monitoring
PRINT CONVERT(VARCHAR(30), GETDATE(), 113)
SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - Stats'
SET @Riga = @Riga + ' Min: ' + CAST(@JOB_Secs_Min AS VARCHAR(9))
SET @Riga = @Riga + ' AVG: ' + CAST(@JOB_Secs_AVG AS VARCHAR(9))
SET @Riga = @Riga + ' Max: ' + CAST(@JOB_Secs_Max AS VARCHAR(9))
PRINT @Riga
EXEC [FLUSSI].[dbo].[LogInsert2] 4, @Riga
--- A questo punto abbiamo raccolto dalle statistiche
--- di sistema i tempi di esecuzione del job in secondi.
--- Li utilizzeremo per ridurre le query di stato del job.
---((( Settings per LOOP
SET @Secs_Past = 0
SET @Step_Ret_Code = -1
SET @Secs_Inter_T = '000:00:01'
SET @Fine_Wait = 0
---((( INIZIO LOOP
---((( INIZIO LOOP
---((( INIZIO LOOP
WHILE @Fine_Wait = 0
BEGIN
--- ATTESA!
IF @Fine_Wait = 0
WAITFOR DELAY @Secs_Inter_T
--- Contaloop
SET @IND = @IND + 1
--- Interrogo statistiche di sistema
SET @Step_Ret_Code =
(SELECT TOP 1
msdb.dbo.sysjobhistory.run_status
FROM
msdb.dbo.sysjobhistory INNER JOIN msdb.dbo.sysjobs
ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
WHERE
msdb.dbo.sysjobhistory.step_name = '(Job outcome)'
AND msdb.dbo.sysjobs.name = @Nome_Job
AND CAST(msdb.dbo.sysjobhistory.run_date AS VARCHAR(22)) +
RIGHT('000000' + CAST(msdb.dbo.sysjobhistory.run_time AS VARCHAR(9)), 6)
>= @Run_Job_Ini
ORDER BY msdb.dbo.sysjobhistory.run_date DESC,
msdb.dbo.sysjobhistory.run_time DESC)
--- controllo null
SET @Step_Ret_Code = ISNULL(@Step_Ret_Code, -1)
SET @Time_Now = GETDATE()
SET @Secs_Past = DATEDIFF( SECOND, @Run_Job_Ini_D, @Time_Now)
IF @Secs_Past < @JOB_Secs_Min
SET @Secs_Inter = @JOB_Secs_Min - @Secs_Past
IF @Secs_Past >= @JOB_Secs_Min AND @Secs_Past < @JOB_Secs_AVG
SET @Secs_Inter = (@JOB_Secs_AVG - @JOB_Secs_Min) / 10
IF @Secs_Past >= @JOB_Secs_AVG
SET @Secs_Inter = (@JOB_Secs_Max - @JOB_Secs_AVG) / 10
IF @Secs_Inter < 30
SET @Secs_Inter = 30
IF @Secs_Inter > 60
SET @Secs_Inter = 60
---((( CONVERSIONE FORMATO tempo
--- da secondi (INT)
--- a stringa 'HHH:MM:SS'
--- le ore sono secondi / 3600
SET @Secs_Inter_HH = FLOOR (@Secs_Inter / 3600)
--- i minuti sono i secondi rimanenti dalle ore / 60
SET @Secs_Inter_MM = FLOOR ((@Secs_Inter - (@Secs_Inter_HH * 3600))/60)
--- I secondi sono il resto della divisione per 60
SET @Secs_Inter_SS = @Secs_Inter - (FLOOR (@Secs_Inter / 60) * 60)
--- CONVERSIONE in STRINGA
SET @Secs_Inter_T = RIGHT('000' + CAST(@Secs_Inter_HH AS VARCHAR(9)), 3)
SET @Secs_Inter_T = @Secs_Inter_T + ':' + RIGHT('00' + CAST(@Secs_Inter_MM AS VARCHAR(9)), 2)
SET @Secs_Inter_T = @Secs_Inter_T + ':' + RIGHT('00' + CAST(@Secs_Inter_SS AS VARCHAR(9)), 2)
IF @Step_Ret_Code < 0
SET @Job_Status = 'In esecuzione'
IF @Step_Ret_Code = 0
SET @Job_Status = 'Failed'
IF @Step_Ret_Code = 1
SET @Job_Status = 'Succeeded'
IF @Step_Ret_Code = 2
SET @Job_Status = 'Retray'
IF @Step_Ret_Code = 3
SET @Job_Status = 'Canceled'
IF @Step_Ret_Code = 4
SET @Job_Status = 'In progress'
--- LOG & MONITOR
IF @Ind >= @Ind_Mon
BEGIN
PRINT CONVERT(VARCHAR(30), GETDATE(), 120)
SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - (' + CAST(@IND AS VARCHAR(9)) + ')'
SET @Riga = @Riga + ' - secondi tot: ' + CAST(@Secs_Past AS VARCHAR(9))
SET @Riga = @Riga + ' - intervallo secs: ' + @Secs_Inter_T
SET @Riga = @Riga + ' - JOB STATUS: ' + @Job_Status
PRINT @Riga
EXEC [FLUSSI].[dbo].[LogInsert2] 4, @Riga
SET @Ind_Mon = @Ind_Mon * 2
END
--- controllo per uscita dal loop
IF @Step_Ret_Code >= 0
SET @Fine_Wait = 1
IF @Secs_Past > @JOB_Secs_Max
SET @Fine_Wait = 1
END
---((( FINE LOOP
--- monitoring timeout
IF @Step_Ret_Code < 0
SET @Job_Status = @Job_Status + ' (Timeout scaduto)'
---PRINT @Riga
---EXEC [FLUSSI].[dbo].[LogInsert2] 4, @Riga
--- valorizza parametro output
SET @Stato_Job = @Step_Ret_Code
--- cancello tabella temporanea se esiste
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'zz_Run_Wait_Job')
DROP TABLE zz_Run_Wait_Job
--- cancello tabella temporanea se esiste
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'zz_Run_Wait_Job_2')
DROP TABLE zz_Run_Wait_Job_2
--- cancello tabella temporanea se esiste
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'zz_Run_Wait_Job_3')
DROP TABLE zz_Run_Wait_Job_3
RETURN
3) In un nuovo JOB chiamato Lavora_Step inseriamo il seguente codice:
DECLARE @Stato_Job AS INT
--- EXPORT_AM_CORP
EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job OUTPUT
--- Rilancia se job failed
IF @Stato_Job = 0
EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job OUTPUT
Come potete vedere: Run_wait_Job_2 è il nome della Stored procedures, Bonus_Track è il nome del nostro Job iniziale (quello con tanti Step).
Ho inoltre inserito lo status dell'operazione per determinarne l'esito positivo o negativo.
Buon divertimento.
Se volete seguire i post di www.informarea.it potete iscrivervi al suo feed RSS.
I Vostri commenti sono benvenuti!
Non esitate a lasciare le Vostre impressioni o a porre domande.