-- 혹시 DB가 없는 경우 생성
--CREATE DATABASE DBA
--ALTER DATABASE DBA SET RECOVERY SIMPLE
-- 로깅용 테이블 없으면 생성
IF object_id('NHN_runParalellSQL') is null BEGIN
CREATE TABLE NHN_runParalellSQL (jobName nvarchar(2000), regDate datetime, status varchar
(50))
END
DECLARE @jobNames table (jobNo int identity(1,1), jobName nvarchar(4000))
DECLARE @jobNames2 table (jobNo int, jobName nvarchar(4000))
DECLARE @v_jobName nvarchar(4000)
, @v_cmd nvarchar(4000)
, @v_DBName nvarchar(4000)
, @initTime datetime
SET @initTime = getdate()
--=======================================
-- 병렬처리용 JOB을 통해 수행 시작
-- 이 부분만 채워 넣어서 사용하면 된다.
--=======================================
SELECT @v_jobName = 'WORKING_SUBJOB1', @v_cmd = 'exec BBS1.DB1.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName
SELECT @v_jobName = 'WORKING_SUBJOB2', @v_cmd = 'exec BBS2.DB2.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName
SELECT @v_jobName = 'WORKING_SUBJOB3', @v_cmd = 'exec BBS3.DB3.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName
SELECT @v_jobName = 'WORKING_SUBJOB4', @v_cmd = 'exec BBS4.DB4.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName
--=======================================
-- 병렬처리용 JOB을 통해 수행 끝
--=======================================
-- 등록한 작업의 후속처리 부분
-- 각 서브작업들이 잘 수행되는지 체크하고, 실패하는 서브작업이 있을경우 오류를 발생시키며 종료한다.
DECLARE @cnt int
, @totalCnt int
SELECT @cnt = 1
, @totalCnt = (SELECT COUNT(*) FROM @jobNames)
delete @jobNames2
insert into @jobNames2 select * from @jobNames
while 1=1 BEGIN
SELECT @v_jobName = jobName FROM @jobNames2 WHERE jobNo = @cnt
-- JOB 생성에 실패한 경우 체크
IF EXISTS (SELECT * FROM NHN_runParalellSQL with(nolock) where jobName = @v_jobName and
regDate > @initTime and status = 'FAILED') BEGIN
RAISERROR ('자동 병렬처리 배치작업 실패', 19, 1) with log
BREAK
END
-- JOB 실행에 실패한 경우 체크
IF EXISTS (
select * from msdb..sysjobhistory
where run_status <> 1 -- 작업 실패
and job_id = (select top 1 job_id from msdb..sysjobs with(nolock) where
name = @v_jobName)
and convert(varchar(50), run_date)+right('0'+convert(varchar(50), run_time),
6) >= convert(char(8), @initTime, 112)+replace(convert(char(8), @initTime, 108), ':', '')
) BEGIN
RAISERROR ('자동 병렬처리 배치작업 실패', 19, 1) with log
BREAK
END
-- JOB 실행에 성공한 경우 체크
IF EXISTS (
select * from msdb..sysjobhistory
where run_status = 1 -- 작업 성공
and job_id = (select top 1 job_id from msdb..sysjobs with(nolock) where
name = @v_jobName)
and convert(varchar(50), run_date)+right('0'+convert(varchar(50), run_time),
6) >= convert(char(8), @initTime, 112)+replace(convert(char(8), @initTime, 108), ':', '')
) BEGIN
insert into NHN_runParalellSQL select @v_jobName, getdate(), 'END'
EXECUTE msdb.dbo.sp_delete_job @job_name = @v_jobName
DELETE @jobNames2 WHERE jobNo = @cnt
UPDATE @jobNames2 SET jobNo = jobNo-1 WHERE jobNo > @cnt
SET @totalCnt = @totalCnt - 1
-- 모든 JOB이 실행에 성공한 경우 중단
IF @totalCnt = 0 BREAK
END
SET @cnt = (@cnt % @totalCnt) + 1
END
--=================================
---- 작업을 위한 SP 생성
--=================================
CREATE PROC runParallelSQL
@jobName nvarchar(4000)
, @cmd nvarchar(4000)
, @DBName nvarchar(4000)
AS
declare @jobStepName nvarchar(4000)
select @jobStepName = @jobName+'_단계'
-- JOB 생성
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]')
< 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- 동일한 이름을 가진 작업이 있으면 삭제
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @jobName)
IF (@JobID IS NOT NULL)
BEGIN
-- 작업이 다중 서버 작업인지 점검
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- 스크립트를 중단하십시오.
RAISERROR (N'이 이름으로 된 다중 서버 작업이 이미 존재하기 때문에 특정작업_하위작업 작업을 가
져올 수 없습니다.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- [로컬] 작업 삭제
EXECUTE msdb.dbo.sp_delete_job @job_name = @jobName
SELECT @JobID = NULL
END
BEGIN
-- 작업 추가
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name =
@jobName, @owner_login_name = N'agent', @description = N'설명이 없습니다.', @category_name =
N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0,
@notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 작업 단계 추가
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1,
@step_name = @jobStepName, @command = @cmd, @database_name = @DBName, @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0,
@retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 대상 서버 추가
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name =
N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
insert into NHN_runParalellSQL select @jobName, getdate(), 'START'
-- JOB 수행
EXECUTE msdb..sp_start_job @job_name = @jobName
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
insert into NHN_runParalellSQL select @jobName, getdate(), 'FAILED'
RAISERROR ('자동 병렬처리 배치작업 실패', 19, 1) with log
EndSave:
GO
하만철 / Ha Man-cheol
EMail : feisia@hanmail.net
openkr
2009/08/17 10:27
2009/08/17 10:27
Trackback Address :: 이 글에는 트랙백을 보낼 수 없습니다
