Wednesday, August 11, 2021

Reporting View Hierarchies

It is a feature of PeopleSoft that it uses lots of views to present data within the application, and frequently views reference other views.  There are examples of this design going 5 levels deep in HR and deeper in Financials.  When faced with a SQL execution plan for such a view you often wonder which view referenced which table.

However, the Oracle database describes "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links" in the view ALL_DEPENDENCIES.

This hierarchical query (depend_heir.sql) on this view will report the structure of views within views. 

REM depend_hier.sql
undefine view_name
set pages 999 lines 176 long 50000
break on name skip 1 on owner 
ttitle 'Dependency Hierarchy'
column my_level format a5 heading 'Level'
column owner format a12
column name format a18
column type format a7
column referenced_type format a7 heading 'Refd|Type'
column referenced_owner format a6 heading 'Refd|Owner'
column referenced_name format a18 heading 'Refd|Name'
column referenced_link_name format a10 heading 'Refd|Link'
column dependency_type heading 'Dep|Type'
column text heading 'View Text' format a80 wrap on
spool depend_hier.&&view_name..lst
with d as (
  select * from all_dependencies
  union all
  select null, null, null, owner, view_name, 'VIEW', null, null
  from all_views 
  where owner = 'SYSADM' and view_name = UPPER('&&view_name')
select LPAD(TO_CHAR(level),level,'.') my_level
, d.type, d.owner,
, d.referenced_type, d.referenced_owner, d.referenced_name, d.referenced_link_name
, d.dependency_type
, v.text
from d
  left outer join all_views v 
    on  v.owner = d.referenced_owner
    and v.view_name = d.referenced_name
  connect by nocycle = prior d.referenced_name
  and   d.owner = prior d.referenced_owner
start with d.owner IS NULL and IS NULL
spool off
ttitle off
For example, this is the report for PS_POSN_HISTORY3 from a demo HCM database. It is only three levels deep. "POSN_HISTORY3 is the third of three nested views which retrieve position incumbent history.  It selects job records with effective dates before position exits to obtain exit salaries."
Wed Aug 11                                                                                                                                                             page    1
                                                                              Dependency Hierarchy

                                              Refd    Refd   Refd               Refd       Dep
Level TYPE    OWNER        NAME               Type    Owner  Name               Link       Type View Text
----- ------- ------------ ------------------ ------- ------ ------------------ ---------- ---- --------------------------------------------------------------------------------
1                                             VIEW    SYSADM PS_POSN_HISTORY3                   SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,B.EFFDT ,B.EFF
                                                                                                SEQ ,B.Sal_Admin_Plan ,B.Grade ,B.Step ,B.Comprate ,B.Comp_Frequency ,B.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_POSN_HISTORY2 A ,PS_JOB B WHERE B.EmplID = A.Emp
                                                                                                lID AND B.EMPL_RCD = A.EMPL_RCD AND B.Position_Nbr = A.Position_Nbr AND B.EffDt
                                                                                                = ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD =
                                                                                                 B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND
                                                                                                 C.EffSeq = A.EffSeq - 1))) AND B.Effseq = ( SELECT MAX(C.Effseq) FROM PS_JOB C
                                                                                                WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_
                                                                                                End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1)))

.2    VIEW    SYSADM       PS_POSN_HISTORY3   TABLE   SYSADM PS_JOB                        HARD
.2    VIEW                                    VIEW    SYSADM PS_POSN_HISTORY2              HARD SELECT A.Position_Nbr , A.Position_Entry_Dt , A.Emplid , A.EMPL_RCD , B.EffDt ,
                                                                                                B.EffSeq , B.Action FROM PS_POSN_HISTORY A , PS_JOB B WHERE A.EmplID = B.EmplID
                                                                                                AND A.Empl_Rcd = B.Empl_Rcd AND B.EffDt = ( SELECT MIN(C.EffDt) FROM PS_JOB C WH
                                                                                                ERE C.EmplID = B.EmplID AND C.Empl_Rcd = B.Empl_Rcd AND (C.EffDt > A.Position_En
                                                                                                try_Dt OR (C.EffDt = A.Position_Entry_Dt AND C.EffSeq > A.EffSeq)) AND ((C.Posit
                                                                                                ion_Nbr <> A.Position_Nbr) OR (C.HR_STATUS <> 'A'))) AND B.EffDt<=TO_DATE(TO_CHA
                                                                                                R(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND B.EffSeq = ( SELECT MIN(D.EffSeq) FROM
                                                                                                 PS_JOB D WHERE D.EmplID = B.EmplID AND D.Empl_Rcd = B.Empl_Rcd AND D.EffDt = B.
                                                                                                EffDt AND ((D.Position_Nbr <> A.Position_Nbr) OR (D.HR_STATUS <> 'A')))

..3   VIEW    SYSADM       PS_POSN_HISTORY2   TABLE   SYSADM PS_JOB                        HARD
..3   VIEW                                    VIEW    SYSADM PS_POSN_HISTORY               HARD SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,A.EFFSEQ ,A.EF
                                                                                                FDT ,A.Sal_Admin_Plan ,A.Grade ,A.Step ,A.CompRate ,A.Comp_Frequency ,A.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_Job A WHERE A.Position_Entry_Dt = A.Effdt AND A.
                                                                                                Effseq = ( SELECT MIN(B.Effseq) FROM PS_Job B WHERE B.Emplid = A.Emplid AND B.EM
                                                                                                PL_RCD = A.EMPL_RCD AND B.Effdt = A.Effdt AND B.Position_Nbr = A.Position_Nbr)

...4  VIEW    SYSADM       PS_POSN_HISTORY    TABLE   SYSADM PS_JOB                        HARD
We can see from the report that view PS_POSN_HISTORY3 calls view PS_POSN_HISTORY2 that in turn calls view PS_POSN_HISTORY
Each of the views also contains multiple references to PS_JOB that perform various effective date/sequence sub-queries. Where there are multiple references to the same object, there is still only one dependency.
The script is available on Github as a part of my psscripts repository.

Friday, August 06, 2021

Oracle Active Data Guard in PeopleSoft with Oracle 19c DML Redirection

Active Data Guard is a configuration option that became available in Oracle 11g where a standby database is maintained as a synchronised physical replica of the primary database and is also open for read-only SQL queries.

PeopleSoft added configuration to direct certain read-only components and processes to an ADG standby using secondary connections in the application servers and process schedulers.  However, in PeopleSoft, all scheduled processes update at least the process scheduler request tables, even if they make no updates to application tables.  This cannot be done on a read-only standby database and must be directed back to the primary database.

PeopleBooks sets out a method for Implementing Active Data Guard (this link is to the PeopleTools 8.58 documentation).  It uses a second schema to which PeopleSoft application servers and process schedulers connect using a second access profile.  The second schema contains synonyms for each table and view that either point to the corresponding object in the original schema on the ADG standby, or if the object is going to be updated by the application then via database links to the corresponding object in the primary database.  This approach requires knowledge of which tables are updated during otherwise read-only processing, a lot of scripting to generate all the synonyms and grants, and ongoing maintenances as new objects are added to the database.

However, that approach is rendered obsolete by Active Data Guard DML redirection, a new feature in 19c.  This post explains how to configure PeopleSoft to make use of Active Data Guard on Oracle 19c with DML redirect.

With DML redirection enabled, updates on the secondary database are automatically redirected back to the primary database via a SQL*Net connection between the databases (not unlike a database link), and then they will be replicated back to the standby database like any other change.  PeopleSoft no longer needs to be configured specially to handle updated tables differently.  Consequently, the PeopleSoft ADG configuration is massively simplified.

Processes no longer have to be strictly read-only to run on the ADG database.  If there are only a small quantity of updates the redirect can handle it.

Database Initialisation Parameters

The parameter ADG_REDIRECT_DML should be set to true on both the primary and active data guard standby databases.

PeopleSoft Connection Configuration

A second row must be added to PS.PSDBOWNER to map the TNS name of the standby database to the database owner ID, in this case SYSADM.
SQL>select * from ps.psdbowner;

-------- --------

Application Server Configuration

To facilitate read-only components in the PIA (such as the query manager) redirecting to the standby database, all Application Server domains should still be configured to connect to both the primary database and the standby database, as described in the PeopleSoft documentation
; Database Signon settings

Process Scheduler Configuration

The approach for Process Schedulers is slightly different.

Application Engine Limitation in Active Data Guard

It is a documented limitation of Application Engine that only PSAESRV Application Engine server processes can connect via the second connection to the standby database.  If a standalone PSAE process attempts this the connection will fail.  
  • Oracle Support Note: E-AE: Application Engine Process Might Stay in Initiated Status if PSAESRV Disabled on PeopleSoft ADG Enabled Environment (Doc ID 1641764.1)
    • This was raised as Bug 18482301: PSAE may stay in initiated status on ADG configured environment.  It was closed as 'not a bug'.
  • Using Two Temporary Tablespace in PeopleSoft.
Error in sign on
 Database Type: 7 (ORACLE)
 Database Name: HCM91
 Server Name: 
 ConnectID: people
 Process Instance: 0
 Reason: Invalid user ID or password for database signon. (id=)
 Note: Attempt to authenticate using GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740
Invalid command line argument list.
 process command line: -CT ORACLE -CD HCM91 -GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740 -SS NO -SN NO 
 GUID command line : -CT ORACLE -CD HCM91 -CO "PS" -CP Unavailable -R 1 -I 852 -AI AEMINITEST -OT 6 -FP 
"C:\app\pt\appserv\prcs\HCM91PSNT\log_output\AE_AEMINITEST_852\" -OF 1
To continue to use stand-alone PSAE processes, as recommended in the PeopleTools Performance Guidelines Red Paper (Doc ID: 747389.1), it is necessary to configure separate process schedulers that connect only to the Active Data Guard standby database, and processes will need to be redirected appropriately by process categories to these schedulers.

System Settings

Process Categories can be used to restrict certain processes to certain processes schedulers.  In the Process Scheduler System Settings component, create a new category ADGOnly.
Process Scheduler Process Category Admin

New Process Schedulers for ADG

New process schedulers should be created for running ADG only jobs.  They will only be connected to the standby database.  The standby connection should be left blank.  All of the SQL updates made by the Process Scheduler processes will be handled by DML redirection.  You will need to have at least 2 so they do not become a single point of failure.
; Database Signon settings
These schedulers will ONLY run:
  • Processes in the new ADGOnly process category.  
  • A single LOADCACHE category process will be permitted.
  • The max concurrence of all other categories will be 0.
  • Other process types such as SQR might be needed if they are to be run on the ADG standby
They should not run master process scheduler processes. This process should only run schedulers connected to the primary database.
ADG Process Scheduler Configuration

Existing Process Schedulers

All process categories apply to all process schedulers.  The concurrency of the ADG category should be set to 0 on all other process schedulers to prevent it from running there.  These schedulers will remain connected to the primary database only.  They will not be connected to the secondary database.
; Database Signon settings

Process Definition

Normally, the processes that are to be run on ADG should be marked read-only and then they will be scheduled using the secondary connection.    However, Application Engine cannot be run in standalone PSAE mode via the second connection.  So, the ADG process schedulers are configured to connect directly to the ADG database via the primary connection.  Thus, the read-only flag has no effect.  Instead, all read-only processes should be moved to the ADGOnly category.

Configuration by SQL

It may be easier to update the process scheduler configuration by SQL
  • ADGOnly concurrency will be 0, or it will be the maximum API aware concurrency on ADG process schedulers.
  • PSQUERY, any read-only processes, and any processes in ADGOnly category, will all be marked as being both read-only and in the ADGOnly category.
update pslock 
set version = version + 1
where objecttypename IN('SYS','PPC')
update psversion
set version = version + 1
where objecttypename IN('SYS','PPC')
update  ps_servercategory c 
set     maxconcurrent = CASE WHEN servername like 'PSUNX_A%' 
                             THEN (SELECT s.maxapiaware FROM ps_serverdefn s 
                                   WHERE  s.servername = c.servername)
                             ELSE 0 END
where   prcscategory = 'ADGOnly'
update ps_serverdefn
set    version = (SELECT version from psversion where objecttypename = 'PPC')
,      lastupddttm = systimestamp
update ps_prcsdefn 
set    version = (SELECT version from psversion where objecttypename = 'PPC')
,      prcsreadonly = 1
,      prcscategory = 'ADGOnly'
,      lastupddttm = systimestamp
where  prcsreadonly = 1
or     prcscategory = 'ADGOnly'
or     prcsname = 'PSQUERY'
select prcstype, prcsname, prcscategory, prcsreadonly
from   ps_prcsdefn
where  prcsreadonly = 1
or     prcscategory = 'ADGOnly'
or     prcsname = 'PSQUERY'
select * from ps_servercategory
where prcscategory IN('ADGOnly')
order by 2,1

Sample Output

PSOFT-FINADG>Select prcstype, prcsname, prcscategory, prcsreadonly
  2  From   ps_prcsdefn
  3  where  prcsreadonly = 1
  4  or     prcscategory = 'ADGOnly'
  5  or     prcsname = 'PSQUERY'
  6  /

PRCSTYPE                       PRCSNAME     PRCSCATEGORY                   P
------------------------------ ------------ ------------------------------ -
Application Engine             AEMINITEST   ADGOnly                        1
COBOL SQL                      PTPDBTST     ADGOnly                        1
SQR Report                     PTSQRTST     ADGOnly                        1
Application Engine             FB_GEN_EXTR  ADGOnly                        1
SQR Report                     XRFWIN       ADGOnly                        1
SQR Report                     SWPAUDIT     ADGOnly                        1
SQR Report                     SYSAUDIT     ADGOnly                        1
SQR Report                     XRFAPFL      ADGOnly                        1
SQR Report                     XRFAEPL      ADGOnly                        1
SQR Report                     XRFPGDZ      ADGOnly                        1
SQR Report                     DDDAUDIT     ADGOnly                        1
SQR Report                     XRFEVPC      ADGOnly                        1
SQR Report                     XRFFLPC      ADGOnly                        1
SQR Report                     XRFFLPN      ADGOnly                        1
SQR Report                     XRFFLRC      ADGOnly                        1
SQR Report                     XRFIELDS     ADGOnly                        1
SQR Report                     XRFMENU      ADGOnly                        1
SQR Report                     XRFPANEL     ADGOnly                        1
SQR Report                     XRFPCFL      ADGOnly                        1
SQR Report                     XRFPNPC      ADGOnly                        1
SQR Report                     XRFRCFL      ADGOnly                        1
SQR Report                     XRFRCPN      ADGOnly                        1
Application Engine             PSQUERY      ADGOnly                        1
Application Engine             PSCONQRS     ADGOnly                        1

SYSADM-FINADG>select * from ps_servercategory
  2  where prcscategory IN('ADGOnly')
  3  order by 2,1
  4  /

-------- ------------------------------ - -------------
PSNT     ADGOnly                        5             0
PSUNX    ADGOnly                        5             0
PSUNX_A1 ADGOnly                        5             5

PSQUERY Application Engine Amendment

If you have PS/Queries that reference a remote database via a database link then you may experience errors when you run them via the scheduled Application Engine process PSQUERY on an ADG database.
Using a database link, even if only for a query, starts a database transaction, and then you can't get DML redirect to work because it also starts a transaction.  However, if you already have a transaction open through DML redirect you can't start the transaction associated with the database link.  The two transactions become mutually exclusive.
PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved

PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc
PeopleTools PeopleCode Trace value: 64 (0x40): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc

File: /vob/peopletools/src/pssys/qpm.cppSQL error. Stmt #: 8495  Error Position: 3055  Return: 16000 - ORA-16000: database or pluggable database open for read-only access 
Failed SQL stmt: SELECT …
<a query that references a remote database via a database link>Error in running query because of SQL Error, Code=16000, Message=ORA-16000: database or pluggable database open for read-only access  (50,380)

PeopleCode Exit(1) Abort invoked by Application at PSQUERY.MAIN.ExecQry. (108,543)

Process xxxxxxxx ABENDED at Step PSQUERY.MAIN.ExecQry (PeopleCode) -- RC = 16 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s
So we need to close the transactions by making the PSQUERY application engine commit.  Application Engine steps explicitly commit after each step.  I have added an extra step added that does nothing, but Application Engine issues a commit

Disable Query Statistics

I have found that query statistics can cause a problem when the query is run on ADG.
PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved

PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA2/log_output/AE_PSQUERY_19356674/AE_PSQUERY_19356674.trc

File: /vob/peopletools/src/pssys/qdmutil.cppSQL error. Stmt #: 4608  Error Position: 91  Return: 16397 - ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed 
Application Engine program PSQUERY ended normally
They can be disabled in the PIA
  • Navigate to: PeopleTools, Utilities, Administration, Query Administration, 
  • Go to the last tab (Settings), 
  • Uncheck Run Query Statistics.

Enable Query Execution Logging

Query stats (logged in the table PSQRYSTATS) are not particularly useful as they only compute average metrics over a period that is not defined.  Instead, query execution logging (to the table PSQRYEXECLOG) is far more useful for performance analysis because you know who ran the query when it ran, and how many rows it retrieved.  It is enabled for individual queries.  It can be enabled across the board with the following SQL.
update pslock
set version = version + 1
where objecttypename IN('SYS','QDM')
update psversion
set version = version + 1
where objecttypename IN('SYS','QDM')
update psqrydefn
set    version = (SELECT version from psversion where objecttypename = 'QDM')
,      execlogging = 'Y'
,      lastupddttm = systimestamp
where  execlogging != 'Y'

Monday, April 12, 2021

Running nVision in a Mixed OpenXML/Excel mode

This blog post follows on from a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.
Updated 20.4.2021: OpenXML is an alternative method for running PeopleSoft nVision introduced in PeopleTools 8.54.  It is recommended by Oracle and is the default configuration in the process scheduler.  It generally outperforms Excel because it is a lightweight process and it evades Excel's concurrency limitations.
Only one Excel nVision will run concurrently on a Windows server because Excel itself is a single-threaded process since Microsoft Excel 2010.  If you need to run 10 concurrent nVision reports you would need 10 windows servers each running a process scheduler that will run a single nVision report in Excel mode.  One of the major advantages of OpenXML is that it is not subject to this restriction.  See also:

What is the Problem?

I have worked with PeopleSoft Financials customers where it became apparent that some of their nVision layouts did not run correctly on OpenXML, and until they could be redeveloped they had to run on Excel.  The problems seem to be around pagination and nPlosion.  Also, some variables are not populated when the layouts are run in OpenXML.  For example, the report on the left was run in OpenXML, and the one on the right was run in Excel.
I have also found a very few layouts, that contrary to expectation, perform better on Excel than OpenXML.
Reverting to Excel is the only other option, but to do so across the board would have a significant performance impact.  However, my experience suggests that customers can run the majority of their layouts perfectly successfully on OpenXML, but a relatively small proportion still has to be run on Excel.  
A mixed mode of operation is therefore a good option, where nVision is run on OpenXML by default, but certain reports and report books are run on Excel.  It avoids having to train end-users to run certain reports on certain process schedulers.  
This blog sets out a method by which reports using certain nVision layouts are automatically redirected to certain process schedulers that run Excel nVision by a combination of configuring process categories and two database triggers to adjust the request records before they are picked up by the process schedulers.

Excel nVision Process Schedulers and OpenXML nVision process schedulers

Whether nVision is run in Excel or OpenXML mode is determined by the UseExcelAutomation variable in the nVision section of the process scheduler configuration file (psprcs.cfg).  It applies to all nVision process run by that scheduler.  However, this variable is not documented well in the PeopleTools manual.  It takes the following values:
; General settings for nVision
Today, most PeopleSoft systems run most of their batch processes on Linux/Unix servers.  nVision is one of the few PeopleSoft process types that had to run on Windows, and that is still the case for Excel nVision.  It is typical for these PeopleSoft systems to have several Windows servers dedicated to nVision execution.  To run in a mixed-mode, it is necessary to have an Excel nVision process scheduler and an OpenXML nVision process scheduler on each server.

Process Categories 

In the past, I have suggested creating additional process types, but I think it is easier and more in keeping with the process scheduler structure to create additional process definitions and process categories.  It is necessary to create two new process categories (in PeopleTools->Process  Scheduler->System Settings)


Process Schedulers (or Servers) should be created in pairs for each physical server available.  In this example, I have created PSNT_X1 to run the OpenXML category and PSNT_E1 to run the Excel category.  
Note that:
  • Maximum concurrency for the Excel server has been reduced to 1.
  • The maximum number of API aware processes for PSNT_E1 has also be reduced to 1.
  • The 'Default' category is disabled on both PSNT_E1 and PSNT_X1.
  • These servers only run nVision and PSJob process types,
  • They load balance within the same operating system (although this is not appropriate for PSNT_X1 if you also run OpenXML on other non-Windows process schedulers).

Process Definitions

The various nVision process definitions are allocated to the nVisionOpenXML process category so they run on those process schedulers.

The whole configuration can be set up manually through the Process Scheduler components. However, I have also written a couple of scripts to manage the configuration that should ensure consistency.
  • nvisionprcsdefnclone.sql updates process definitions and creates the cloned server definitions.  It can also be configured to create multiple pairs of server definitions etc.
  • nvision_prcscategory.sql updates the concurrency on existing process definitions.


When a process request is submitted either in the PeopleSoft application or an enterprise scheduling tool (such as AutoSYS or Control-M), request records are inserted into:
  • PSPRCSQUE is polled by the process schedulers when looking for work.
  • PSPRCSRQST reports the status of processes and is exposed in the Process Monitor component in PeopleSoft.
These two tables are siblings, both keyed on PRCSINSTANCE.  Both have the following columns:
  • PRCSNAME determines the name of the process to be run.
  • PRCSCATEGORY specifies the category of the process in the request.


I have created two pairs of almost identical database triggers that fire on insert into each of these tables that will make certain changes to request for nVision layouts that should be run on Excel:
  • One pair of triggers updates requests for nVision-Report and nVisionReportBook process types.  The other pair update the PSJob request record where nVision reports are run in a Jobset.
  • The triggers change the process category from nVisionOpenXML to nVisionExcel where necessary.  It is changing the process category rather than the name of the process that makes the difference to which process scheduler picks up the request.
  • If the process request specifies a particular process scheduler (specified in SERVERNAMERQST) that cannot run the updated process category, then the requested server name is blanked out so that the master process scheduler reassigns the request.
If any one layout in a ReportBook is listed as requiring to be run on Excel, then the whole ReportBook is moved to Excel because the report book runs as a single process.  This may become a reason to split ReportBooks where only some layouts require to run on Excel.
Script gfc_nvsion_excel_redirect_triggers.sql creates the meta-data table PS_NVS_REDIR_EXCEL, and the triggers on PSPRCSRQST and PSPRCSQUE.
  • The corresponding triggers on PSPRCSRQST and PSPRCSQUE are almost identical except for the name of the table and one additional column that is updated on PSPRCSQUE (apart from the conditionally compiled debug code).  I will only exhibit the trigger on PSPRCSRQST in this post.
  • Normally, one would not code WHEN OTHERS THEN NULL exceptions handlers in PL/SQL.  However, I do this on triggers on process scheduler tables to prevent any error being raised causing the scheduler to crash.
  • Conditionally compiled debug code in the trigger is not compiled if the mydebug flag is set to FALSE.


CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_excel_redirect_rqst 
BEFORE INSERT ON s.psprcsrqst
WHEN (new.prcstype IN('nVision-Report','nVision-ReportBook'))
  l_excel INTEGER := 0;
  l_maxconcurrent INTEGER := 0;
  k_prcscategory CONSTANT VARCHAR2(15) := 'nVisionExcel';
  $IF $$mydebug $THEN dbms_output.put_line('Entering Trigger psoft.gfc_nvision_excel_redirect_rqst'); $END

  IF :new.prcstype = 'nVision-ReportBook' THEN
    --check for reportbook running report that uses layout on Excel list
    SELECT 1
    INTO   l_excel
    FROM   psnvsbookrequst b
    ,      ps_nvs_report n
    ,      ps_nvs_redir_excel e
    WHERE  b.oprid = :new.oprid
    AND    b.run_cntl_id = :new.runcntlid
    AND    b.eff_status = 'A'
    AND    n.business_unit = b.business_unit
    AND    n.report_id = b.report_id
    AND    n.layout_id = e.layout_id
    AND    e.eff_status = 'A'
    AND    rownum=1;
    --look in command line for report running layout on Excel list
    SELECT 1
    INTO   l_excel
    FROM   psprcsparms p
    ,      ps_nvs_report n
    ,      ps_nvs_redir_excel e
    WHERE  p.prcsinstance = :new.prcsinstance
    AND    n.report_id = substr(regexp_substr(p.parmlist,'-NRN[^ ]+'),5)
    AND    n.layout_id = e.layout_id
    AND    e.eff_status = 'A'
    AND    rownum=1;

--set category of request
  :new.prcscategory := k_prcscategory;

  --get max concurrency of new category on new server
  SELECT maxconcurrent
  INTO   l_maxconcurrent
  FROM   ps_servercategory
  WHERE  prcscategory = :new.prcscategory
  AND    servername = :new.servernamerqst;

  --if request assigned to server where it cannot run blank out server assignment and allow load balancing to determine it
  IF l_maxconcurrent = 0 THEN
    :new.servernamerqst := ' ';

  $IF $$mydebug $THEN dbms_output.put_line('set process name to '||:new.prcsname); $END
  WHEN no_data_found THEN 
    $IF $$mydebug $THEN dbms_output.put_line('No excel redirect found'); $ELSE NULL; $END
  WHEN others THEN 
    $IF $$mydebug $THEN dbms_output.put_line('Other Error'); $ELSE NULL;  $END
END gfc_nvision_excel_redirect_rqst;
show errors


The script gfc_nvsion_excel_redirect_metadata.sql populates a list of nVision layouts that must run on Excel into the metadata table PS_NVS_REDIR_EXCEL. You have to enter your own list of nVision layouts here.
REM gfc_nvsion_excel_redirect_metadata.sql
REM (c)Go-Faster Consultancy 2021
REM load metadata of layouts that have to run on Excel rather than OpenXML

spool gfc_nvsion_excel_redirect_metadata

INSERT INTO ps_nvs_redir_excel VALUES ('EXCELNVS','A');

spool off

Other Scripts

  • excel_only_reportbooks.sql determines which nVision ReportBooks contain only some layouts that require to be run on nVision.  These are candidates to be split up.
                                                              ReportBooks with both Excel and OpenXML nVision layouts

                                                   Number of
                                              All      Excel
OPRID      RUN_CNTL_ID                    Layouts    Layouts Excel Layouts                  OpenXML Layouts
---------- ------------------------------ ------- ---------- ------------------------------ ----------------------------------------------------------------------
BATCH      ADHOC_NVISION                        8          1 GLXXXO21                       GLXYZD03, GLXXXO03, GLXXXO05, GLXXXO22, GLXXXO23, GLXXXO31, GLXXXO32
BATCH      ADHOC_09062016                       3          1 ZYXVIS14                       ZYXVBS14, ZYXVIS12
BATCH      GLXXX_GLXXXO02_ABDC_YYY              2          1 GLXXXO02                       GLXXXO28
BATCH      GLXXX_GLXXXO21_ABDC_YYY              3          2 GLXXXO21, GLXXXO98             GLXXXO71
BATCH      GLXXX_ZYXB4080_M000_ZZZ             10          2 ZYXVBS64, ZYXVIS14             ZYXVBS04, ZYXVBS14, ZYXVBS14_LED, ZYXVBS16, ZYXVBS16_LED, ZYXVBS54, ZB
                                                                                            UVIS04, ZYXVIS16
  • nvision_processsmonitor.sql reports on nVision ReportBooks scheduled in the last 24 hours, their process category and the excel layouts within them, so that you can verify that the configuration is working.
  •                                                        Process         Server   Server   Server
    PRCSINSTANCE RUNDTTM                      PRCSNAME     Category        Request  Run      Assign   RUNCNTLID                      STA EXCEL_LAYOUT_IDS
    ------------ ---------------------------- ------------ --------------- -------- -------- -------- ------------------------------ --- ------------------------------
        12345680 31-MAR-21 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO10_ABDC_YYY        OK  GLXXXO10
        12345681 31-MAR-21 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO21_ABDC_YYY        OK  GLXXXO21, GLXXXO98
        12345683 31-MAR-21 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO98_ADHOC           OK  GLXXXO98
        12345684 31-MAR-21 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO21_ABDC_YYY        OK  GLXXXO21, GLXXXO98
        12345685 31-MAR-21 AM FBRPTBK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYAD06_ABDC_YYY        OK
        12345686 31-MAR-21 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYBP00_ABDC_YYY        OK
        12345687 31-MAR-21 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYAD03_ABDC_YYY        OK
        12345688 31-MAR-21 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLVLAD03_ABDC_XXX        OK
        12345689 31-MAR-21 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_ZYXAB001_M000_ZZZ        OK
    All the scripts mentioned in this blog have been included in my nVision Github repository.