PeopleSoft Process Monitor Run Status

PeopleSoft Process Monitor Run Status

Ever had trouble identifying the runstatus in PSPRCSRQST table with what it meant. Run the following SQL to determine what it means.

SELECT C.FIELDVALUE, C.XLATLONGNAME, C.XLATSHORTNAME,C.FIELDNAME,TO_CHAR(C.EFFDT,’YYYY-MM-DD’)

FROM PSXLATITEM C

WHERE C.EFFDT =

(SELECT MAX(C_ED.EFFDT) FROM PSXLATITEM C_ED

WHERE C.FIELDNAME = C_ED.FIELDNAME

AND C.FIELDVALUE = C_ED.FIELDVALUE

AND C_ED.EFFDT <= SYSDATE)

AND C.FIELDNAME = ‘RUNSTATUS’

AND C.EFF_STATUS = ‘A’

you can read it More about PeopleSoft

LifeSaver of the Week – 4

UNIX command for the PS DBA – find

In several of my previous projects, we used to get 4-5 tickets per week because of space related issues. Finally we implemented automated scripts to take care of this issue. The PS_HOME and psreports are the two main directories which have to be monitored for space growth. Below I explain the relevant examples of the UNIX command “find” that you can use to build your shell script. of Peoplesof

find $PS_HOME -name ‘*.AET’

The above command will recursively find all files with .AET extension residing in the PS_HOME directory.

find $PS_HOME -name ‘*.AET’ -mtime +1

The above command will recursively find all files with .AET extension and older than 1day in the PS_HOME directory.

find $PS_HOME -name ‘*.AET’ -mtime +1 -exec gzip {} ;

The above command will recursively find all files with .AET extension, older than 1day and will gzip the file in the PS_HOME directory.

find $PS_HOME -name ‘*.AET’ -mtime +1 -exec rm {} ;

The above command will recursively find all files with .AET extension, older than 1day and will remove the file in the PS_HOME directory.

find $PS_HOME -name ‘*.AET’ -size +10000000c -exec gzip {} ;

The above command will recursively find all files with .AET extension, greater than 10MB in sizeand will gzip the file in the PS_HOME directory.

You can replace AET with trc or out or log as appropriate

To add more variety to your thoughts on Operational Data, you can read it More about Peoplesoft 

Data Integration Challenge – Capturing Changes

When we receive the data from source systems, the data file will not carry a flag indicating whether the record provided is new or has it changed. We would need to build process to determine the changes and then push them to the target table.

There are two steps to it

1. Pull the incremental data from the source file or table
2. 
   
3. Process the pulled incremental data and determine the impact of it on the target table as Insert or Update or Delete
4. 
   

Step 1: Pull the incremental data from the source file or table
If source system has audit columns like date then we can find the new records else we will not be able to find the new records and have to consider the complete data
For source system’s file or table that has audit columns, we would follow the below steps

1. While reading the source records for a day (session), find the maximum value of date(audit filed) and store in a persistent variable or a temporary table
2. Use this persistent variable value as a filter in the next day to pull the incremental data from the source table
3. 
   

Step 2: Determine the impact of the record on target table as Insert/Update/ Delete 

Following are the scenarios that we would face and the suggested approach

1. Data file has only incremental data from Step 1 or the source itself provide only incremental data
   
   
   • do a lookup on the target table and determine whether it’s a new record or an existing record
   • if an existing record then compare the required fields to determine whether it’s an updated record
   • have a process to find the aged records in the target table and do a clean up for ‘deletes’
     
     
2. Data file has full complete data because no audit columns are present
   
   
   • The data is of higher
     
     
     • have a back up of the previously received file
     • perform a comparison of the current file and prior file; create a ‘change file’ by determining the inserts, updates and deletes. Ensure both the ‘current’ and ‘prior’ file are sorted by key fields
     • have a process that reads the ‘change file’ and loads the data into the target table
     • based on the ‘change file’ volume, we could decide whether to do a ‘truncate & load’
   • The data is of lower volume
     
     
     • do a lookup on the target table and determine whether it’s a new record or an existing record
     • if an existing record then compare the required fields to determine whether it’s an updated record
     • have a process to find the aged records in the target table and do a clean up or delete
       
       
       Read more about Data Integration Challenge
       
       

LifeSaver of the Week -3

Trace SQR

The most common approach to tracing SQR is to use the–DEBUG or –S flag. In this post, I will share the techniques I use to generate the SQL Trace that I can use to troubleshoot and determine the bad SQL.
If I need to trace a SQR in a development environment, I choose to modify the SQR and include the following procedure which will be called at the start of the program.

begin-procedure SetSQLTrace ! Set SQL Trace

begin-SQL

ALTER SESSION SET SQL_TRACE = TRUE;

end-SQL

end-procedure

However, in a production or any other environment which is under change control, I need to co-ordinate with the functional analyst to execute the SQR. Here are the steps I follow.
1.Determine the session id as soon as the SQR program starts processing.
Tip – Use the CLIENT_INFO and PROGRAM in V$SESSION to determine the user session.
2.Execute below SQL’s.

exec dbms_system.set_bool_param_in_session(sid, serial#, ‘TIMED_STATISTICS’, TRUE);

exec dbms_system.set_int_param_in_session(sid, serial#, ‘MAX_DUMP_FILE_SIZE’, 2147483647);

– Turn on trace

exec dbms_system.set_ev(sid, serial#, 10046, 8, ”)

– or use below

exec dbms_system.set_sql_trace_in_session(sid,serial#,Y)

– Turn off trace

exec dbms_system.set_ev(sid, serial#, 10046, 0, ”)

– Run TKPROF on trace file

I have found the above approaches very useful to identify the problem SQL whenever I receive a ticket from the user complaining about a long running SQR report or process.

Business Intelligence Utopia – Dream to Reality: Key Enablers

In the last post, I discussed my view of BI Utopia in which information is available to all stakeholders at the right time, in the right format enabling them to make actionable decisions at both strategic & operational levels. Having said that, the BI street is not paved with gold.

I consider the following key enablers as pre-requisites to achieve true ‘Information Democracy’ in an enterprise. The “Power of Ten” includes:

1. Proliferation of agile, modular & robust transaction processing systems.
2. 
   
3. Real Time Data Integration Components
4. 
   
5. Strong Data Governance structure
6. 
   
7. Service Oriented Architecture
8. 
   
9. Extensible Business centric Data Models
10. 
    
11. Flexible business rules repositories surrounded by clean metadata/reference data environments
12. 
    
13. Ability to integrate unstructured information into the BI architectural landscape
14. 
    
15. Guided context-sensitive, user-oriented analytics
16. 
    
17. Analytical models powered by Simulations
18. 
    
19. Closed loop Business Intelligence Utopia
20. 
    

Each of these units comprising the “Power of Ten” is a fascinating topic on its own. We will drill-down and focus on some of the salient features of each of these areas in the coming weeks.

LifeSaver of the Week -2

CLIENT_INFO

Pinpointing PeopleSoft user sessions to Oracle session is vital for troubleshooting performance related issues. Previously, I used some crude techniques (using the V$SESSION.STATUS =
‘ACTIVE’ and V$SESSION.PROGRAM) to map the PeopleSoft session to the Oracle session. This was not always accurate in a production system where multiple user sessions are active at the same time and running the same program. I came across V$SESSION.CLIENT_INFO and it made my life so much easier…

PeopleSoft populates CLIENT_INFO by default for all 2-tier sessions, COBOL, SQR and Process Scheduler. However, for 3-tier connections we need to enable ‘EnableDBMonitoring = 1’ in the psappsrv.cfg configuration file.

Now, if I get a call from a user having performance problem running the custom report AABC123.sqr then I can log on to SQLPLUS and run the following SQL to determine what the session for OPRID NPAI is currently doing.

SELECT SQL_TEXT FROM V$SQLTEXT

WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION

WHERE STATUS = ‘ACTIVE’ AND UPPER(PROGRAM) LIKE ‘SQR%’

AND CLIENT_INFO LIKE ‘NPAI%’)

ORDER BY PIECE

/

Note:

PeopleSoft also delivers a function GET_PS_OPRID which can be used to determine the OPRID. So if you have that function created in your database then you can use the below SQL to determine the OPRID.

SELECT GET_PS_OPRID(client_info) FROM V$SESSION WHERE …

Alternatively you can use -

SELECT SUBSTR(client_info,1,INSTR(client_info,’,') – 1) FROM V$SESSION WHERE …

Read more about LifeSaver.

LifeSaver Series: LifeSaver of the Week–1

Every seasoned DBA has his bag of tips and tricks. I have such a bag which I will open and share with you all one code/script/tip/trick at a time. I am calling this new series‘LifeSaver of the Week’ because these scripts actually save life (most of the time) during crunch time. There have been occasions when I had a high priority ticket breathing down my neck and pulling the right trick helped identify the problem instantly.

The LifeSaver series will include tips relevant to Oracle or PeopleSoft or UNIX.

Here is the first one to kick-off the series.

”LifeSaver of the Week – 1”

LAST_CALL_ET:

A few years back, I was trying to build a script to identify long running SQL’s. The script was supposed to check for any SQL running for more than 5 mins and notify me if it found any such SQL. I was trying different methods to determine the optimal approach to build this script. I came across the column LAST_CALL_ET present in V$SESSION. The data in this column shows in seconds the time since last call. Below is the SQL I used in the script.

SELECT A.SQL_TEXT, B.PROGRAM, B.CLIENT_INFO

FROM V$SQLTEXT A, V$SESSION B

WHERE A.ADDRESS = B.SQL_ADDRESS

AND B.STATUS = ‘ACTIVE’

AND B.LAST_CALL_ET > 300

AND B.USERNAME IS NOT NULL

ORDER BY A.PIECE

Read more about LifeSaver.