Back to OIM Explorer

dbo.QBM_PJobQueueDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.088 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVJobParamDeflate source text reference
  • references source dbo.QBM_FCVJobParameterToString source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

Complete Source

SQL341 lines
1CREATE PROCEDURE QBM_PJobQueueDelete(2  @RowLimit int = 50,3  @MaxLimit int = 300004)5AS6BEGIN7  DECLARE @Trees QBM_YParameterList8  DECLARE @TreePortion QBM_YParameterList9  DECLARE @einRuck int10  DECLARE @LaufDelete int = 011  DECLARE @AnzahlLoesch int12  DECLARE @AnzahlLoeschGesamt int = 013  DECLARE @justNow datetime = getUTCDate()14  DECLARE @DebugSwitch int = 015  DECLARE @DebugLevel char(1) = 'I'16  DECLARE @DebugMessage nvarchar(1000)17  DECLARE @Start datetime18  DECLARE @ende datetime19  DECLARE @diff int20  DECLARE @ExpectedRuntime float = 1000.021  DECLARE @HistoryEntries QBM_YJobQueueShadow22  DECLARE @anzahl int23  SET XACT_ABORT OFF24  BEGIN TRY25    SET lock_timeout 5026    SELECT @anzahl = dbo.QBM_FGITableCountAll('JobQueue')27    IF @anzahl > 028    BEGIN29      GOTO normal30    END31    IF @anzahl = 0 AND @@TRANCOUNT = 032    BEGIN33      BEGIN34        TRANSACTION35        BEGIN TRY36          UPDATE JobQueue37          SET MinutesToDefer = q.MinutesToDefer38          FROM JobQueue q39            WITH(tablockx)40        END TRY41        BEGIN CATCH42          SELECT @anzahl = 143        END CATCH44        IF dbo.QBM_FGITableCountAll('JobQueue') = 0 AND @anzahl = 045        BEGIN46          BEGIN TRY47            truncate TABLE QBMElementAffectedByJob truncate TABLE JobQueue COMMIT48            SELECT @LaufDelete = 149          END TRY50          BEGIN CATCH51            ROLLBACK52          END CATCH53        END54        ELSE55        BEGIN56          ROLLBACK57        END58      END59      IF @LaufDelete = 160      BEGIN61        GOTO endLabel62      END63      normal:64      INSERT INTO @HistoryEntries(ComponentAssembly,65      ComponentClass,66      EndedAt,67      JobChainName,68      ParamIN,69      Priority,70      Queue,71      StartAt,72      TaskName,73      UID_Job,74      UID_Tree,75      WasError,76      XDateInserted,77      XDateUpdated,78      XUserInserted,79      XUserUpdated,80      IsRootJob,81      UID_JobError,82      UID_JobSuccess,83      GenProcID,84      UID_JobOrigin,85      BasisObjectKey,86      ErrorMessages,87      LogMode,88      Ready2EXE)89      SELECT90        q.ComponentAssembly,91        q.ComponentClass,92        isnull(q.xdateupdated,93        GETUTCDATE()) AS EndedAt,94        q.JobChainName,95        dbo.QBM_FCVJobParamDeflate(q.ComponentClass,96        q.TaskName,97        q.ParamIN),98        q.Priority,99        q.Queue,100        q.StartAt,101        q.TaskName,102        q.UID_Job,103        q.UID_Tree,104        q.WasError,105        q.XDateInserted,106        q.XDateUpdated,107        q.XUserInserted,108        q.XUserUpdated,109        q.IsRootJob,110        e.UID_Job,111        s.UID_Job,112        q.GenProcID,113        q.UID_JobOrigin,114        q.BasisObjectKey,115        q.ErrorMessages,116        q.LogMode,117        q.Ready2EXE118      FROM(119      SELECT120        DISTINCT TOP(@MaxLimit) qt.UID_Tree121      FROM JobQueue qt122        WITH(readpast)123      WHERE124        qt.Ready2EXE = N 'HISTORY') t125      JOIN JobQueue q126        WITH(readpast)127        ON t.UID_Tree = q.UID_Tree128      LEFT129      OUTER130      JOIN JobQueue e131        WITH(readpast)132        ON q.UID_Tree = e.UID_Tree AND q.UID_JobError = e.UID_Job AND e.Ready2EXE IN(N 'FINISHED',133      N 'HISTORY')134      LEFT135      OUTER136      JOIN JobQueue s137        WITH(readpast)138        ON q.UID_Tree = s.UID_Tree AND q.UID_JobSuccess = s.UID_Job AND s.Ready2EXE IN(N 'FINISHED',139      N 'HISTORY')140      WHERE141        q.Ready2EXE IN(N 'FINISHED',142      N 'HISTORY')143      IF @@ROWCOUNT = 0144      BEGIN145        GOTO loeschen146      END147      INSERT INTO @Trees(Parameter1,148      ContentFull)149      SELECT150        DISTINCT q.UID_Tree,151        ''152      FROM @HistoryEntries q153      IF EXISTS(154        SELECT TOP 1 1155        FROM @Trees t156        JOIN JobTreeParamColl co157        WITH(readpast)158        ON co.UID_Tree = t.Parameter1)159      BEGIN160        UPDATE @Trees161        SET ContentFull = dbo.QBM_FCVJobParameterToString(t.Parameter1)162        FROM @Trees t163        UPDATE @Trees164        SET ContentFull = SUBSTRING(t.ContentFull,165        2,166        LEN(t.ContentFull) -1)167        FROM @Trees t168        WHERE169          t.ContentFull > ' '170        UPDATE @Trees171        SET ContentFull = ''172        FROM @Trees t173        WHERE174          t.ContentFull IS NULL175      END176      INSERT INTO JobHistory(ComponentAssembly,177      ComponentClass,178      EndedAt,179      JobChainName,180      ParamIN,181      Priority,182      Queue,183      StartAt,184      TaskName,185      UID_Job,186      UID_JobHistory,187      UID_Tree,188      WasError,189      XDateInserted,190      XDateUpdated,191      XUserInserted,192      XUserUpdated,193      IsRootJob,194      UID_JobError,195      UID_JobSuccess,196      GenProcID,197      UID_JobOrigin,198      BasisObjectKey,199      ErrorMessages,200      LogMode)201      SELECT202        q.ComponentAssembly,203        q.ComponentClass,204        q.EndedAt,205        q.JobChainName,206        dbo.QBM_FCVJobParamDeflate(q.ComponentClass,207        q.TaskName,208        q.ParamIN + t.ContentFull),209        q.Priority,210        q.Queue,211        q.StartAt,212        q.TaskName,213        q.UID_Job,214        newid(),215        q.UID_Tree,216        q.WasError,217        q.XDateInserted,218        q.XDateUpdated,219        q.XUserInserted,220        q.XUserUpdated,221        q.IsRootJob,222        q.UID_JobError,223        q.UID_JobSuccess,224        q.GenProcID,225        q.UID_JobOrigin,226        q.BasisObjectKey,227        q.ErrorMessages,228        q.LogMode229      FROM @HistoryEntries q230      JOIN @Trees t231        ON q.UID_Tree = t.Parameter1232      WHERE233        NOT EXISTS(234      SELECT TOP 1 1235      FROM JobHistory h236      WHERE237        h.UID_Tree = q.UID_Tree AND h.UID_Job = q.UID_Job) loeschen:238      INSERT INTO @Trees(Parameter1)239      SELECT240        DISTINCT TOP(@MaxLimit) qt.UID_Tree241      FROM JobQueue qt242        WITH(readpast)243      WHERE244        ready2exe = N 'DELETE'245      SELECT @LaufDelete = 1246      SELECT @einRuck = @RowLimit247      WHILE @LaufDelete > 0248      BEGIN249        SELECT @Start = getutcdate()250        DELETE @TreePortion251        INSERT INTO @TreePortion(Parameter1)252        SELECT TOP(@einRuck) t.Parameter1253        FROM @Trees t254        WHERE255          t.HasContentFull = 0256        SELECT @LaufDelete = @@ROWCOUNT257        IF @LaufDelete = 0258        BEGIN259          CONTINUE260        END261        UPDATE @Trees262        SET HasContentFull = 1263        FROM @Trees t264        JOIN @TreePortion p265          ON t.Parameter1 = p.Parameter1266        BEGIN TRY267          DELETE Jobqueue268          WHERE269            UID_Tree IN(270          SELECT t.Parameter1271          FROM @TreePortion t)272          SELECT @AnzahlLoesch = @@rowcount273        END TRY274        BEGIN CATCH275          SELECT @AnzahlLoesch = 0276          EXEC QBM_PWaitForSeconds 0.05277        END CATCH278        SELECT @AnzahlLoeschGesamt += @AnzahlLoesch279        SELECT @ende = getutcdate()280        SELECT281          @diff = DATEDIFF(ms,282          @start,283          @ende)284        SELECT @einRuck = CASE285        WHEN @diff = 0 THEN286        @RowLimit287        WHEN @AnzahlLoesch = 0 THEN288        @RowLimit289        WHEN @diff > @ExpectedRuntime THEN290        @RowLimit291        ELSE convert(int,292        @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff))293        END294      END295      IF(@AnzahlLoeschGesamt > 0)296      BEGIN297        IF('1' = dbo.QBM_FGIConfigparmValue('Common\JobQueueStats'))298        BEGIN299          INSERT INTO JobQueueStats(UID_JobQueueStats,300          queue,301          jobYear,302          jobMonth,303          jobDay,304          jobHour,305          countInserted,306          countActivated,307          countDeleted)308          SELECT309            'XX' +convert(varchar(64),310            newid()),311            N '',312            year(@justNow),313            month(@justNow),314            day(@justNow),315            datepart(hh,316            @justNow),317            0,318            0,319            (-1) * @AnzahlLoeschGesamt320        END321      END322      IF @DebugSwitch > 0323      BEGIN324        SELECT @DebugMessage = 'count entries in Jobqueue deleted ' + STR(@AnzahlLoeschGesamt)325        EXEC QBM_PJournal @debugmessage,326          @@PROCID,327        'I',328          @debuglevel329      END330    END TRY331    BEGIN CATCH332      EXEC QBM_PSessionErrorAdd DEFAULT333      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()334      RAISERROR(@Rethrow,335      18,336      1)337        WITH NOWAIT338    END CATCH339    endLabel:340    RETURN(@AnzahlLoeschGesamt)341  END
Open raw exported source
SQL ยท Raw40 lines
1  create   procedure QBM_PJobQueueDelete ( @RowLimit int = 50  , @MaxLimit int = 30000  ) as begin declare @Trees QBM_YParameterList declare @TreePortion2 QBM_YParameterList declare @einRuck int  declare @LaufDelete int = 0 declare @AnzahlLoesch int declare @AnzahlLoeschGesamt int = 0 declare @justNow datetime3 = getUTCDate() declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'I' declare @DebugMessage nvarchar(1000) declare @Start datetime declare @ende4 datetime declare @diff int declare @ExpectedRuntime float = 1000.0  declare @HistoryEntries QBM_YJobQueueShadow declare @anzahl int SET XACT_ABORT OFF5 BEGIN TRY set lock_timeout 50 select @anzahl = dbo.QBM_FGITableCountAll('JobQueue') if @anzahl > 0 begin goto normal end if @anzahl = 0 and @@TRANCOUNT6 = 0  begin begin transaction BEGIN TRY  update JobQueue set MinutesToDefer = q.MinutesToDefer from JobQueue q with (tablockx) END TRY BEGIN CATCH select7 @anzahl = 1  END CATCH  if dbo.QBM_FGITableCountAll('JobQueue') = 0 and @anzahl = 0 begin BEGIN TRY  truncate table QBMElementAffectedByJob truncate table8 JobQueue  commit select @LaufDelete = 1 END TRY BEGIN CATCH   rollback END CATCH end else begin  rollback end end if @LaufDelete = 1 begin goto endLabel9 end normal: insert into @HistoryEntries ( ComponentAssembly, ComponentClass, EndedAt, JobChainName, ParamIN, Priority, Queue, StartAt, TaskName, UID_Job10,  UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, UID_JobError, UID_JobSuccess, GenProcID, UID_JobOrigin, BasisObjectKey11, ErrorMessages  , LogMode, Ready2EXE)  select q.ComponentAssembly, q.ComponentClass, isnull(q.xdateupdated, GETUTCDATE()) as EndedAt, q.JobChainName, 12dbo.QBM_FCVJobParamDeflate (q.ComponentClass, q.TaskName, q.ParamIN), q.Priority, q.Queue, q.StartAt, q.TaskName, q.UID_Job,  q.UID_Tree, q.WasError, q.XDateInserted13, q.XDateUpdated, q.XUserInserted, q.XUserUpdated, q.IsRootJob, e.UID_Job, s.UID_Job, q.GenProcID, q.UID_JobOrigin, q.BasisObjectKey, q.ErrorMessages ,14 q.LogMode , q.Ready2EXE  from ( select distinct top (@MaxLimit) qt.UID_Tree from JobQueue qt with (readpast) where qt.Ready2EXE = N'HISTORY' ) t join 15JobQueue q with (readpast) on t.UID_Tree = q.UID_Tree left outer join JobQueue e with (readpast) on q.UID_Tree = e.UID_Tree and q.UID_JobError = e.UID_Job16 and e.Ready2EXE in ( N'FINISHED', N'HISTORY') left outer join JobQueue s with (readpast) on q.UID_Tree = s.UID_Tree and q.UID_JobSuccess = s.UID_Job and17 s.Ready2EXE in ( N'FINISHED', N'HISTORY') where  q.Ready2EXE in ( N'FINISHED', N'HISTORY') if @@ROWCOUNT = 0 begin goto loeschen end insert into @Trees18 (Parameter1 , ContentFull ) select distinct q.UID_Tree , '' from @HistoryEntries q  if exists (select top 1 1 from @Trees t join JobTreeParamColl co with19 (readpast) on co.UID_Tree = t.Parameter1  ) begin  update @Trees set ContentFull  = dbo.QBM_FCVJobParameterToString(t.Parameter1 ) from @Trees t  update20 @Trees set ContentFull  = SUBSTRING(t.ContentFull , 2 , LEN(t.ContentFull ) -1) from @Trees t where t.ContentFull  > ' ' update @Trees set ContentFull21  = '' from @Trees t where t.ContentFull  is null end insert into JobHistory ( ComponentAssembly, ComponentClass, EndedAt, JobChainName, ParamIN, Priority22, Queue, StartAt, TaskName, UID_Job, UID_JobHistory, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, UID_JobError23, UID_JobSuccess, GenProcID, UID_JobOrigin, BasisObjectKey, ErrorMessages  , LogMode)  select q.ComponentAssembly, q.ComponentClass, q.EndedAt, q.JobChainName24, dbo.QBM_FCVJobParamDeflate (q.ComponentClass, q.TaskName, q.ParamIN + t.ContentFull ), q.Priority, q.Queue, q.StartAt, q.TaskName, q.UID_Job, newid()25, q.UID_Tree, q.WasError, q.XDateInserted, q.XDateUpdated, q.XUserInserted, q.XUserUpdated, q.IsRootJob, q.UID_JobError, q.UID_JobSuccess, q.GenProcID,26 q.UID_JobOrigin, q.BasisObjectKey, q.ErrorMessages , q.LogMode  from @HistoryEntries q join @Trees t on q.UID_Tree = t.Parameter1  where not exists (select27 top 1 1 from JobHistory h where h.UID_Tree = q.UID_Tree and h.UID_Job = q.UID_Job )  loeschen:  insert into @Trees (Parameter1 ) select distinct top (@MaxLimit28) qt.UID_Tree from JobQueue qt with (readpast) where ready2exe = N'DELETE' select @LaufDelete = 1 select @einRuck = @RowLimit while @LaufDelete > 0 begin29 select @Start = getutcdate() delete  @TreePortion  insert into @TreePortion (Parameter1 ) select top (@einRuck) t.Parameter1 from @Trees t where t.HasContentFull30 = 0  select @LaufDelete = @@ROWCOUNT if @LaufDelete = 0 begin continue end update @Trees set HasContentFull = 1  from @Trees t join @TreePortion p on 31t.Parameter1 = p.Parameter1  BEGIN TRY delete Jobqueue where UID_Tree in (select t.Parameter1  from @TreePortion t ) select @AnzahlLoesch = @@rowcount 32END TRY BEGIN CATCH  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate33() select @diff = DATEDIFF(ms, @start, @ende) select @einRuck = case when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff > @ExpectedRuntime34 then @RowLimit else convert(int, @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff)) end  end  if (@AnzahlLoeschGesamt > 0) begin if 35('1' = dbo.QBM_FGIConfigparmValue('Common\JobQueueStats')) begin  insert into JobQueueStats (UID_JobQueueStats, queue , jobYear, jobMonth , jobDay , jobHour36 , countInserted, countActivated, countDeleted ) select 'XX'+convert(varchar(64), newid()), N'' , year(@justNow) ,month(@justNow) ,day( @justNow) , datepart37(hh,@justNow) , 0, 0, (-1) * @AnzahlLoeschGesamt end end  if @DebugSwitch > 0 begin select @DebugMessage = 'count entries in Jobqueue deleted ' + STR(@AnzahlLoeschGesamt38) exec QBM_PJournal @debugmessage, @@PROCID, 'I', @debuglevel end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) 39= dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt) end 40