Performance Trigger over the F986110

[PAULDCLARK]

One of the big issues I've had with JDE is the way that the WSJ from the F986110 doesn't show a seperate wait time from a run time, which makes the F986110 table a complete waste of time when finding how long a job took, what jobs were running at the same time etc etc. I understand that 8.98 has this sort of information, but on 8.97 it doesn't, so I wrote a trigger for it (This is SQL Server Code) and it shows the start time, run time, end time and run and wait times in seconds in a new table.

It can even account for the job number being reset, as this is one of the unique keys in the file. It been tested and has been live for a month or so with no issues, and it's proved very useful in finding average times, wait times etc etc. The order of status changes is quite odd, when I ran diagnostics it went: W - S - P - P - D\E, so its based on an insert of the W, then a delete of S and a delete of P, the latter gets processed twice but that doesn't really matter.

There are two SQL scripts included, the first is the gen of the table, the other the actual trigger over the F986110.

Hope this helps!

Table Gen:

USE [JDE812]
GO
/****** Object: Table [SVB812].[F986110_JobTimes] Script Date: 04/30/2009 09:49:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [SVB812].[F986110_JobTimes](
[JCJOBNBR] [float] NOT NULL,
[JCEXEHOST] [char](40) NOT NULL,
[JCFNDFUF2] [char](30) NOT NULL,
[JCENHV] [char](10) NULL,
[JCUSER] [char](10) NULL,
[JCJOBQUE] [char](10) NULL,
[JCJOBSTS] [nchar](2) NULL,
[Submit_Date_Time] [datetime] NULL,
[Start_Date_Time] [datetime] NULL,
[End_Date_Time] [datetime] NULL,
[Wait_Time] [int] NULL,
[Run_Time] [int] NULL,
CONSTRAINT [F986110_JobTimes_PK] PRIMARY KEY NONCLUSTERED
( [JCEXEHOST] ASC,
[JCJOBNBR] ASC,
[JCFNDFUF2] ASC )
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF

The trigger:

USE [JDE812]
GO
/****** Object: Trigger [SVB812].[F985110_Timings] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE trigger [SVB812].[F986110_Timings] on [SVB812].[F986110] after insert, update, delete
as
BEGIN
SET NOCOUNT ON

/* Variable Declarations */

Declare @JCJOBSTS_Var Char (2),
@JCJOBNBR_Var Float,
@JCEXEHOST_Var Char (40),
@Submit_Date_Time_Var DateTime,
@Start_Date_Time_Var DateTime,
@Date_Now_Var DateTime

/* Set Constants */


/* Initial Insert has a W in the Job Status */
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
SET @JCJOBSTS_Var = (Select JCJOBSTS from inserted)
SET @JCJOBNBR_Var = (Select JCJOBNBR from inserted)
SET @JCEXEHOST_Var = (Select JCEXEHOST from inserted)
SET @Date_Now_Var = (getDate())
IF @JCJOBSTS_Var = 'W'
BEGIN
insert into SVB812.F986110_JobTimes
(JCJOBNBR, JCEXEHOST, JCFNDFUF2, JCENHV, JCUSER, JCJOBQUE, JCJOBSTS, Submit_Date_Time )
select JCJOBNBR, JCEXEHOST, JCFNDFUF2, JCENHV, JCUSER, JCJOBQUE, JCJOBSTS, @Date_Now_Var from inserted;
/* This line deletes any wraparound records, i.e. if the job number has been reset */
delete from SVB812.F986110_JobTimes where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var and Submit_Date_Time <> @Date_Now_Var);
END
END


IF (SELECT COUNT(*) FROM deleted) > 0
BEGIN
SET @JCJOBSTS_Var = (Select JCJOBSTS from deleted)
SET @JCJOBNBR_Var = (Select JCJOBNBR from deleted)
SET @JCEXEHOST_Var = (Select JCEXEHOST from deleted)
SET @Date_Now_Var = (getDate())
/* When the job goes active, S is deleted from the Job Status */
IF @JCJOBSTS_Var = 'S'
BEGIN
SET @Submit_Date_Time_Var = (Select Submit_Date_Time from SVB812.F986110_JobTimes where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var))
Update SVB812.F986110_JobTimes set Start_Date_Time = @Date_Now_Var,
Wait_Time = DateDiff(ss,@Submit_Date_Time_Var, @Date_Now_Var)
where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var);
Update SVB812.F986110_JobTimes set JCJOBSTS = (select JCJOBSTS from inserted)
where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var);
END

/* When the job ends, P is deleted from the Job Status */
IF @JCJOBSTS_Var = 'P'
BEGIN
SET @Start_Date_Time_Var = (Select Start_Date_Time from SVB812.F986110_JobTimes where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var))
Update SVB812.F986110_JobTimes set End_Date_Time = @Date_Now_Var,
Run_Time = DateDiff(ss,@Start_Date_Time_Var, @Date_Now_Var)
where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var);
Update SVB812.F986110_JobTimes set JCJOBSTS = (select JCJOBSTS from inserted)
where (JCJOBNBR = @JCJOBNBR_Var and JCEXEHOST = @JCEXEHOST_Var);
END
END
END

 

[altquark]

Paul

That is an excellent suggestion - I like this a lot. From this information, it is possible to sense exactly how long it takes for a job to go from W to S to P and finally to D...

This trigger can be adapted to similar other tables in OneWorld - I've put similar triggers on other tables such as Vendor Master changes for SOX Compliancy instead of turning on CFR21.

Good tip !

 

[PAULDCLARK]

We have a few of them as well, F0101, F4201, F4211, F0911 etc, although to be fair I have no idea why, I've been here for a year and:

1) These aren't replicated into BI
2) No one has ever asked to look at them once
3) We are French owned and therefore don't need SOX
4) We aren't a pharmaceutical\manufacturer and don't need CFR21

Ho Hum.

the other benefit of this trigger is you can actually tell what jobs were running at a particular time when something went wrong, a piece of information I would have given my left pinkie for at times...

 

[Gopal]

I wasnt sure but when we had to troubleshoot another issue, we noticed that the Jobs which were submitted through report interconnect gets in to the job master table with status D.(When completed).
Did the trigger accounts for the reports called through report interconnect(Synchronouse report interconnect call).

 

[PAULDCLARK]

Not seen that behaviour, have you got an example? I'm only running financials here and the only NULL values in the DB are ones were the job never ran.

Interconnects for invoice prints etc work OK, these are where the master report calls two others in sucession and waits for the last job to complete, all of these put an entry in the audit table...