Saturday, November 13, 2010

Scheduled Crystal Process remains in initiated status when the account is locked

If you have implemented password controls that locks the users account after x no of invalid attempts and the user has a crystal report scheduled, it makes crystal report process to remain in initiated status forever. It also generates an ever increasing log file, which has a potential of consuming entire available disk space and disrupting the other batch processes.

Following Trace files are generated.
CRW_XRFWIN_12345.log
pssqltrace[1].trc


It appears that Crystal Report  repeatedly calls the database sql statements and never comes out of it.

Steps to reproduce the issue.
1. Create a testid testps and assign roles PeopleTools and PeopleSoft User.
2. Schedule XRFWIN Crystal Report to run within next 5 minutes.
3. Update the testps user profile and lock the account.
4. Go back to process monitor and observe that process remains in initiated status.
Also if you go back to server and check the log_output folder for xrfwin you will see that log file and trace file size keeps on increasing.

Only workaround is to Run a SQL to detect this situation and cancel the process. Unlocking the user account also fixes the issue. The fix is targeted in next Tools release 8.51. We observed this behavior in only PT 8.50 and Crystal Report 2008 SP1. We are currently using patch 8.50.10.

SQL to determine the Processes in initiated status.

SELECT 'Processes in Initiated Status ',
       prcsinstance,
       oprid,
       prcsname,
       servernamerqst,
       servernamerun,
       rundttm,
       lastupddttm
FROM   psprcsrqst
WHERE  runstatus = 6
       AND ( SYSDATE - Cast(lastupddttm AS DATE) ) * 24 * 60 > 15 

SQL to determine Scheduled Processes and user account is locked.

SELECT b.prcsinstance,
       b.prcstype,
       b.prcsname,
       (SELECT e.descr
        FROM   ps_prcsdefn e
        WHERE  e.prcstype = b.prcstype
               AND e.prcsname = b.prcsname)     descr,
       b.oprid,
       (SELECT a.oprdefndesc
        FROM   psoprdefn a
        WHERE  a.oprid = b.oprid)               NAME,
       runcntlid,
       (SELECT f.qryname
               || ', '
               || f.descr
        FROM   ps_query_run_cntrl f
        WHERE  f.oprid = b.oprid
               AND f.run_cntl_id = b.runcntlid) qryname,
       recurname,
       runstatus,
       (SELECT xlatshortname
        FROM   psxlatitem c
        WHERE  c.fieldname = 'RUNSTATUS'
               AND c.fieldvalue = b.runstatus)  rundescr,
       diststatus,
       (SELECT d.xlatshortname
        FROM   psxlatitem d
        WHERE  d.fieldname = 'DISTSTATUS'
               AND d.fieldvalue = b.diststatus) distdescr,
       pt_retentiondays,
       rundttm,
       rqstdttm,
       b.lastupddttm,
       servernamerqst
FROM   psprcsrqst b,
       psoprdefn z
WHERE  b.runstatus IN ( 5 )
       AND b.oprid = z.oprid
       AND z.acctlock = 1
ORDER  BY rundttm DESC 

No comments: