T-SQL: come possiamo eseguire in modo sequenziale tutti gli step in un Job?

0 Condivisioni

Ecco un modo per gestire in maniera sequenziale la lavorazione di tutti gli step all’interno di un solo JOB

sql server

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.


0 Condivisioni

Fabrizio Cannatelli

Fondatore di Informarea.it, è un appassionato di informatica. Ha lavorato per molti anni come Analista Programmatore presso varie aziende utilizzando diversi linguaggi di sviluppo, oggi svolge un lavoro completamente diverso ma la voglia di comunicare e di condividere con il web i suoi studi e le sue curiosità lo hanno spinto a far nascere questo blog non solo per esprimere e mostrare la passione per questo mondo, ma anche per confrontarsi con nuove esperienze di sviluppo.

Fabrizio Cannatelli

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.