Accenture Enkitec Group E4 Webinar

Wednesday, April 30, 2008

Bulk Re-Sending Batch Output to Report Repository

If output files from a batch process fail to post to the report repository, you can request that they are resent within Process Monitor. However, if you have had a problem with posting report output for a period of time you could have a large number of processes that have not posted, and it isn't reasonable to request that each of them are resent individually. I have not found anything delivered to repost all unposted content.

If you trace the Process Monitor while resending content for a single process you can see what is updated to force a resend.

PSAPPSRV.20142 (1880)    1-16     11.07.51    0.000039 Cur#2.20142.PMONITOR RC=0 Dur=0.000025 COM Stmt=UPDATE PSPRCSRQST SET DISTSTATUS = :1 WHERE PRCSINSTANCE = :2
PSAPPSRV.20142 (1880) 1-17 11.07.51 0.000009 Cur#2.20142.PMONITOR RC=0 Dur=0.000001 Bind-1 type=2 length=1 value=7
PSAPPSRV.20142 (1880) 1-18 11.07.51 0.000008 Cur#2.20142.PMONITOR RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=458
PSAPPSRV.20142 (1880) 1-19 11.07.51 0.000886 Cur#2.20142.PMONITOR RC=0 Dur=0.000023 COM Stmt=UPDATE PSPRCSQUE SET DISTSTATUS = :1 WHERE PRCSINSTANCE = :2
PSAPPSRV.20142 (1880) 1-20 11.07.51 0.000009 Cur#2.20142.PMONITOR RC=0 Dur=0.000001 Bind-1 type=2 length=1 value=7
PSAPPSRV.20142 (1880) 1-21 11.07.51 0.000008 Cur#2.20142.PMONITOR RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=458
PSAPPSRV.20142 (1880) 1-22 11.07.51 0.000424 Cur#2.20142.PMONITOR RC=0 Dur=0.000027 COM Stmt=UPDATE PS_CDM_LIST SET DISTSTATUS = '8',TRANSFERINSTANCE = 0 WHERE PRCSINSTANCE = :1 AND DISTSTATUS <> '5'
PSAPPSRV.20142 (1880) 1-23 11.07.51 0.000009 Cur#2.20142.PMONITOR RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=458

Hence it is easy to build this simple PL/SQL loop to request to all unsent content is reposted.
BEGIN
FOR x in (
SELECT prcsinstance
FROM psprcsrqst
WHERE diststatus = 4 --that are not posted
AND runstatus = 9 --successful processes
)
LOOP
UPDATE PSPRCSRQST
SET DISTSTATUS = '7'
WHERE PRCSINSTANCE = x.prcsinstance;

UPDATE PSPRCSQUE
SET DISTSTATUS = '7'
WHERE PRCSINSTANCE = x.prcsinstance;

UPDATE PS_CDM_LIST
SET DISTSTATUS = '8'
, TRANSFERINSTANCE = 0
WHERE PRCSINSTANCE = x.prcsinstance
AND DISTSTATUS <> '5'; --posted
END LOOP;
END;
/
COMMIT
/

As always, the standard rules for updating PeopleTools tables apply. Don't do it unless you are sure you know what you are doing, because it isn't supported, and if it goes wrong you are on your own!

1 comment :

Hmmm said...

The following code will work with MS SQL.

DECLARE @PRCSINSTC INT;

DECLARE PROCESSINSTANCE_CURSOR CURSOR FOR
SELECT PRCSINSTANCE
FROM PSPRCSRQST
WHERE DISTSTATUS = 4 --THAT ARE NOT POSTED
AND RUNSTATUS = 9; --SUCCESSFUL PROCESSES

OPEN PROCESSINSTANCE_CURSOR
FETCH NEXT FROM PROCESSINSTANCE_CURSOR INTO @PRCSINSTC
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PRCSINSTC --OPTIONAL, TO SEE THE INSTANCE NUMBER
UPDATE PSPRCSRQST
SET DISTSTATUS = '7'
WHERE PRCSINSTANCE = @PRCSINSTC;

UPDATE PSPRCSQUE
SET DISTSTATUS = '7'
WHERE PRCSINSTANCE = @PRCSINSTC;

UPDATE PS_CDM_LIST
SET DISTSTATUS = '8'
, TRANSFERINSTANCE = 0
WHERE PRCSINSTANCE = @PRCSINSTC
AND DISTSTATUS <> '5'; --POSTED
FETCH NEXT FROM PROCESSINSTANCE_CURSOR INTO @PRCSINSTC
END;
CLOSE PROCESSINSTANCE_CURSOR;
DEALLOCATE PROCESSINSTANCE_CURSOR;
GO