How do you handle/purge Workcenter messages?

MFreitag

MFreitag

Reputable Poster
I recently ran into a problem in an E900 environment when we hit SERK=99999999 and came back to purging Workcenter messages.
How do you handle the massive amount of database records? Is the standard report a good choice or do you stick to SQL?
I wrote a SQL to clear the tables step by step and was wondering how other CNC admins handle the task :)
 
My recommendation is the 'standard report' if there is one, it will do all the validation first. There are a couple of third party purge products on the market, the only name I can think of is ArcTools, but there are others. That would be my second recommendation if there is no 'standard purge', assuming the 3rd party has a predefined purge. Using SQL would be my last choice.

JMHO

Tom
 
E1: WRKFLW: Frequently Asked Questions on Work Center (Doc ID 626538.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=626538.1

E1: WRKFLW: Frequently Asked Questions on Purging Work Center Messages (Doc ID 1329669.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=1329669.1

E1: RDA: How to Disable the UBE Job Completion Message (Doc ID 645319.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=645319.1

E1: UBE: How to Troubleshoot Error Messages in Work Center When There Are No Valid Logs to Analyze (Doc ID 1058052.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=1058052.1


F01131.ZZSERK->F0311M.ZMPSRK->F00165.GDTXKY



------------
**RUN IN PRODUCTION**


------------
R01131P **RUN IN PRODUCTION**
R01131M? Only with matching R01131P date data selection
https://support.oracle.com/epmos/faces/BugDisplay?id=12776607

R98860P (May not be many records)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=626602.1


All records with delta:

USE JDE_PRODUCTION
IF OBJECT_ID('tempdb..#TempCount') IS NOT NULL
DROP TABLE #TempCount
SELECT DISTINCT id=IDENTITY (int, 1,1), GETDATE() 'Date/Time',
(SELECT COUNT(*) FROM jde_production.proddta.F01131M) AS 'F0113M Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131) AS 'F01131 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131T) AS 'F01131T Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01133) AS 'F01133 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F00165) AS 'F00165 Records'
INTO #TempCount
WAITFOR DELAY '00:00:05'
GO
INSERT INTO #TempCount
SELECT GETDATE() 'Date/Time',
(SELECT COUNT(*) FROM jde_production.proddta.F01131M) AS 'F0113M Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131) AS 'F01131 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131T) AS 'F01131T Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01133) AS 'F01133 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F00165) AS 'F00165 Records'
GO
with v as (select * from #TempCount)
select b.[Date/Time],
b.[F0113M Records], b.[F0113M Records] - a.[F0113M Records] Delta,
b.[F01131 Records], b.[F01131 Records] - a.[F01131 Records] Delta,
b.[F01131T Records], b.[F01131T Records] - a.[F01131T Records] Delta,
b.[F01133 Records], b.[F01133 Records] - a.[F01133 Records] Delta,
b.[F00165 Records], b.[F00165 Records] - a.[F00165 Records] Delta
FROM v a, v b
WHERE a.id = b.id-1


then more than 30 days (should be zero when finished?):

SELECT
(select COUNT (*) 'F0113M' from [JDE_PRODUCTION].[PRODDTA].[F01131M] where ZMDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F0113M Records > 30 Days',
(select COUNT (*) 'F01131' from [JDE_PRODUCTION].[PRODDTA].[F01131] where ZZDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F01131 Records > 30 Days',
(select COUNT (*) 'F01131T' from [JDE_PRODUCTION].[PRODDTA].[F01131T] where ZCUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F01131T Records > 30 Days',
(select COUNT (*) 'F01133' from [JDE_PRODUCTION].[PRODDTA].[F01133] where ZTDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F01133 Records > 30 Days',
(select COUNT (*) 'F00165' from [JDE_PRODUCTION].[PRODDTA].[F00165] where GDUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F00165 Records > 30 Days'



To show remaining records after purge:

select jde920.dbo.fn_extractdatefromjulian(ZMDTI) as 'Date', * from JDE_PRODUCTION.PRODDTA.F01131M
order by JDE_PRODUCTION.PRODDTA.F01131M.ZMDTI

select jde920.dbo.fn_extractdatefromjulian(ZZDTI) as 'Date', * from jde_production.proddta.F01131
order by ZZDTI desc

select jde920.dbo.fn_extractdatefromjulian(ZCUPMJ) as 'Date', * from jde_production.proddta.F01131T
order by ZCUPMJ desc

select jde920.dbo.fn_extractdatefromjulian(ZTDTI) as 'Date', * from jde_production.proddta.F01133
order by ZTDTI desc

select jde920.dbo.fn_extractdatefromjulian(GDUPMJ) as 'Date', * from jde_production.proddta.F00165
order by GDUPMJ desc




--If records don't delete:

--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01131M] where ZMDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END

--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01131] where ZZDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END


--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01131T] where ZCUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END

--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01133] where ZTDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END


-- **Careful with F00165, there's some important stuff in there

--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F00165] where GDUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30) and GDOBNM = 'GT01131'
-- IF @@ROWCOUNT < 1 BREAK
--END
 
Last edited:
fn_ExtractDateFromJulian is:

USE JDE920
GO
IF OBJECT_ID (N'fn_ExtractDateFromJulian', N'IF') IS NOT NULL
DROP FUNCTION fn_ExtractDateFromJulian;
GO
CREATE FUNCTION dbo.fn_ExtractDateFromJulian
(
@pJulian numeric(18, 0)
)
RETURNS datetime
AS
-- In JDE, date columns are populated with a numeric value.
-- The value is comprised of:
-- a one-byte century code (1 = 20, 0 = 19),
-- a two-byte year
-- a three-byte Julian day
--
-- Examples are:
-- September 16, 2002 102259
-- June 15, 2000 100167
-- November 13, 1995 095317
--
-- This function will extract the Julian day and the year.
-- It will then add the number of days (of the Julian day)
-- to December 31 of the prior year
BEGIN
-- General variable
DECLARE @vJulian int
DECLARE @vDay int
DECLARE @vYear int
DECLARE @vExtractDate datetime
-- Initialize
SET @vExtractDate = 0
SET @vJulian = CONVERT(int, @pJulian)
IF ( @pJulian <= 0 )
RETURN @vExtractDate
SET @vDay = @vJulian % 1000 -- returns the remainder
SET @vYear = @vJulian / 1000 -- integer division; truncates the last 3 digits
SET @vExtractDate = DATEADD( dd, @vDay, CONVERT(datetime, '12/31/' + CONVERT(varchar, @vYear + 1899 )))
-- Return the result
RETURN @vExtractDate
END
 
@schojo44 - brother_of_karamazov's post is the most complete answer you will ever get. They don't teach that in any class.

@brother_of_karamazov - Wow! Now that is an answer from a professional that has struggled with this issue and resolved it. Thanks for sharing your expertise!
 
Awesome, thanks a lot :)

Here's the SQL i used (MSSQL). To avoid locks i chose to clear smaller blocks of records only. Worked pretty solid but i'll definetly look into the one you posted, @brother_of_karamazov

Code:
set nocount on;

declare @date_start_1 numeric = 118000;
declare @date_start_2 numeric = 118001;
declare @date_end numeric = 118010;

while @date_start_1 < @date_end
begin
  DELETE FROM PRODDTA.F01131M WHERE ZMDTI BETWEEN @date_start_1 AND @date_start_2;
  DELETE A FROM PRODDTA.F01131T A JOIN PRODDTA.F01131 B ON ZCSERK=ZZSERK WHERE ZZDTI BETWEEN @date_start_1 AND @date_start_2;
  DELETE FROM PRODDTA.F01133 WHERE ZTDTI  BETWEEN @date_start_1 AND @date_start_2;
  DELETE A FROM PRODDTA.F00165 A JOIN PRODDTA.F01131 B ON GDTXKY=ZZSERK WHERE GDOBNM='GT01131' AND ZZDTI BETWEEN @date_start_1 AND @date_start_2;
  DELETE A FROM PRODDTA.F00166 A JOIN PRODDTA.F01131 B ON GTTXKY=ZZSERK WHERE GTOBNM='GT01131' AND ZZDTI BETWEEN @date_start_1 AND @date_start_2;
  DELETE FROM PRODDTA.F01131 WHERE ZZDTI BETWEEN @date_start_1 AND @date_start_2;
 
  set @date_start_1 = @date_start_1 + 1;
  set @date_start_2 = @date_start_2 + 1;
 
  waitfor delay '00:00:00.010'
  PRINT @date_start_1;  
end
 
Back
Top