Take 2: Jolt Session Pooling Continued….

Take 2: Jolt Session Pooling Continued….

In our last blog, we talked about Jolt Session Pooling. That was kind of introduction to this concept I suppose. I am now going much deeper into this parameter to understand how this parameter works with Peoplesoft context (also I am interested to know more about this parameter anyway!)…

A quick look at the web.xml file at PORTAL/WEB-INF folder can give you a list of servlets that Peoplesoft application uses: Some of them are:

• psc
• psp
• cs
• xmllink
• PSAttachServlet
• psreports
• SchedulerTransfer
• SyncServer
• monitor
• ppmi etc…

I am not going into each and every servlet that Peoplesoft uses and their details. That is out of scope for my explanation here. And, primarily because, I do not know about them neither… However an overall understanding of this will definitely help us to understand the underlying architecture behind the Peoplesoft Internet Architecture.

For each and every servlet that Peoplesoft uses, there exists a definition at web.xml file. For simplicity sake, this file can be treated as Servlet Configuration File and it is an XML file by nature. If you work with weblogic and Java J2EE, they call this file as Deployment Descriptor Elements. I find it hard to remember that way. So, just to keep things easy, consider this web.xml file as Servlet Configuration File created as part of application deployment…

We are talking here for Peoplesoft technology. We dont need that much gory details to know about weblogic (we need to know some basics!). Just basics. A weblogic server has many servlets that is used for processing requests. A servlet basically connects to the Tuxedo using a session pool manager, which assigns a session based on availablity etc. This session is, then, connecting to the Tuxedo system (application server) using Jolt Server.

So, back to our main point.. why JoltPooling needs to be disabled for all the servlet entries in the web.xml file to resolve the “download to excel” button to work… Probably you have guessed it already.

Read More About  Jolt Session Pooling

Jolt Session Pooling on the Web Server Configuration

Okay, long time no see! I am back. First of all, let me wish you all a Happy and Prosperous New year. Lets hope for the best.

I had some project , as well as, some personal commitments that I needed to complete during last year. One of my project commitment was, obviously due to the reason that I moved to a new project. Also, coincidently I moved to a new location in another country which added some more complications with my new project assignment…

I had a goal last year (2007) to become a certified PMP. PMP is an acronym for Project Management Professional offered by PMI (Project Management Institute). I took the training from Hexavarsity starting of the year. And eventually I have become PMP after spending quite a considerable time. That was my personal commitment that delayed me from writing in this blog.. So, long story short, I am going to write again in this blog. All that I know about Peoplesoft and Unix (Dont laugh, I will try!).

Lets start! We had a situation recently with our UAT environment. The “download to Excel” button is not working. You should be aware of what this “Download to Excel” button I am talking about. For those who are unable to guess, here it is. It is a small image, just like an excel sheet, that will be shown on the right corner of the any tabular output from Peoplesoft application ( Example Process monitor, Reports etc). If you click on this image, you will receive the excel sheet of whatever data you were looking at…

We started doing some research on this issue(still doing!). You know where to start first. You guessed, right, Peoplesoft Customer Connection. They recommended to try disabling JoltPooling in Web Server. This directive is available in web.xml file for the weblogic web server, and called Jolt Session Pooling.

Starting with Tools 8.48, Jolt Session Pooling is enabled by default. What does this mean? Let me explain this from the scratch. As you are aware, web server makes a connection to app server using Jolt. If you enable Jolt Session Pooling , the user connections between web server and app server are simply shared. This setting is expected to minimise system resources by sharing the user connection by pooled sessions…

Web.xml file is an xml file that has directives for all the peoplesoft servlets that the web server uses ( for example, psp, psc etc). Every servlet is set to JoltPooling set as true by default starting with Peopletools Version 8.48. All we did was, disabled JoltPooling on all of these servlets. And this issue is resolved.

Is the issue really resolved? Not exactly, I will say. We still need to find why Jolt Session Pooling needs to be disabled to make “Download to Excel” button to work. Mystery continues…. Until next time.

Read More About  Web Server Configuration

Determine Portal Navigation for all custom processes

The below SQL lists the complete portal navigation along with the process name. I built this SQL when we had to provide this list to our client to review processes that could be retired. The output is useful for generating an inventory or assisting in KEEP/DROP decisions during upgrade.

Here it is:

SELECT NAVIGATION, A1.PRCSNAME

FROM

(SELECT LPAD(‘–’,2*(LEVEL-1)) || PORTAL_LABEL “NAVIGATION”, PORTAL_URI_SEG2 FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A

WHERE PORTAL_NAME = ‘EMPLOYEE’ ) B

WHERE B.PORTAL_PRNTOBJNAME != ‘ ‘

START WITH (B.PORTAL_URI_SEG2 IN

(SELECT D.PNLGRPNAME

FROM PSMENUITEM A, PSMENUDEFN B, PS_PRCSDEFNPNL C, PSPNLGROUP D

WHERE A.MENUNAME=B.MENUNAME

AND A.PNLGRPNAME = C.PNLGRPNAME

AND A.PNLGRPNAME = D.PNLGRPNAME

AND C.PRCSNAME IN

(SELECT PRCSNAME FROM PS_PRCSDEFN

WHERE LASTUPDOPRID !=

 Read More About  Determine Portal Navigation

Getting Current and Previous EFFDTed Rows

Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.

SELECT

            A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT

FROM   PS_JOB A, PS_JOB B

WHERE

            B.EFFDT =(SELECT MAX(A_ED1.EFFDT) FROM PS_JOB A_ED1

                        WHERE B.EMPLID = A_ED1.EMPLID

                        AND B.EMPL_RCD = A_ED1.EMPL_RCD

                        AND A_ED1.EFFDT < A.EFFDT)

            AND B.EFFSEQ =(SELECT MAX(A_ES1.EFFSEQ) FROM PS_JOB A_ES1

                                WHERE B.EMPLID = A_ES1.EMPLID

                                AND B.EMPL_RCD = A_ES1.EMPL_RCD

                                AND B.EFFDT = A_ES1.EFFDT)

            AND A.EMPLID = B.EMPLID

            AND A.EMPLID=’KA0002′

Is this the only way to get the results?????

Here is the Simple and Advanced way to achieve the same results without using Self Join

SELECT

            A.EMPLID, A.EFFDT CURR_EFFDT, LAG (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) PREV_EFFDT

FROM   PS_JOB A

WHERE            A.EMPLID=’KA0002′

Like this, we can also get the Current and Following EFFDTed rows by using LEAD Function

SELECT

A.EMPLID, A.EFFDT CURR_EFFDT, LEAD (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) LEFFDT

FROM PS_JOB A

WHERE A.EMPLID=’KA0002′

Note: Above SQL holds good for Oracle DB. In Oracle, The Function LAG/LEAD is called Analytic Function, which is there right from Oracle 8i onwards.

Read More About  EFFDTed Rows

How can we have DYNAMIC Prompt table for Record Fields?

Most of the times we would have faced a requirement of having Dynamic prompt table for Record Fields. Also PeopleSoft itself very much utilizes Dynamic Prompt table. How can we achieve the same in our project????

Here we go

This is accomplished by 2 ways

1. Using EDITTABLE fields for Prompt Table
2. Using Dynamic Views
3.  

1. Usage of EDITTABLE Fields

In this method, Prompt table property of Record Fields should be assigned with %EDITTABLE value. Actually what does it mean????

Prompt Table value for the Record Field is populated from the Record Field DERIVED.EDITTABLE value. The Record Field DERIVED.EDITTABLE should be assigned with value either in one of the Peoplecode events (Either in FieldChange or FieldEdit or RowInit events). This is simply done by

DERIVED.EDITTABLE = “PERSON_NAME”;

Note: EDITTABLE Field should be present in the SAME Page, where Record Field (In this case, The Field is nothing but EMPLID) is also referred. If the Record Field is not coming under Component Search Record Field, there is no need for placing the EDITTABLE field in the Page.

2. Usage of Dynamic Views

As we know, while creating Dynamic view, there is no need to specify the SQL. This SQL should be generated dynamically and the same should be assigned to the Record Field.

Say for example, if we see the above Record Field TASK_PROFILE_ID, it is assigned with dynamic prompt table view TL_TSKGRP_DVW and this view TL_TSKGRP_DVW is dynamically initialized by the following Peoplecode, which can be assigned both in FieldChange and RowInit events of the Record Field TASK_PROFILE_ID.

RECORDNAME.TASK_PROFILE_ID.SqlText = “SELECT T.TASKGROUP, T.TASK_PROFILE_ID, T.DESCR FROM PS_TL_TSKGRP_PRF_W T WHERE T.TASKGROUP = ‘” | &TSKGRP | “‘ AND T.EFFDT =(SELECT MAX(T1.EFFDT) FROM PS_TL_TSKGRP_PRF_W T1 WHERE T1.TASKGROUP = T.TASKGROUP AND T1.TASK_PROFILE_ID=T.TASK_PROFILE_ID AND T1.EFFDT<= %datein( ‘” | &maxdate | “‘) )”;

Both &TSKGRP, &maxdate are dynamic bind parameters.

Read More About  DYNAMIC Prompt Table

Fine Grained Access Control for PeopleSoft database – I

Today, I will demonstrate use of Fine Grained Access Control to create row-level security for the database to mimic the row level security setup in the PeopleSoft application. Let me start with asking a few questions.

In your PeopleSoft database, do you have Oracle ids created for the PeopleSoft users? Most of the PeopleSoft shops create Oracle ids for Functional support teams, so that they can query the database when they are troubleshooting some issues.

If you answered ‘yes’ to the above question then what kind of access has been given to these ids? Most of the PeopleSoft shops will provide the Oracle ids SELECT access to all access id owned tables. Is this how you have setup your ids? This basically allows the Oracle id to bypass the security setup in PeopleSoft and be able to view all the data in the table that the user has access

Won’t you love to have online query row level security available in the database such that it will only retrieve the rowsets that the user should have access?

Providing anyone with SELECT privileges to all PeopleSoft application or tools tables is not recommended. You might want to consider identifying sensitive datasets or tables and implement FGAC policies on them. The below scenario demonstrates the need to have this feature in the database.

1.In this example, I have identified PS_PERSONAL_DATA as the table on which I want to replicate the online security in the database

2.As shown below, the Query Security Record for PS_PERSONAL_DATA is PERS_SRCH_QRY

3.Now, lets try to create a query in Query Manager and check out the SQL generated by PeopleSoft

4.  The Query Manager has automatically added a join with PS_PERS_SRCH_QRY and an additional filter by OPRID.

5.   If I run this query then it fetches 1901 rows

6.  Now, I logon to Oracle database as PS and query PS_PERSONAL_DATA. And below are the results

7.  As we knew, PS has access to all the rows in PS_PERSONAL_DATA when queried from the database.

In my next post, I will share steps to implement the Fine Grained Access Control feature.

How to execute OS Commands in SQR?

Most of the projects have the below mentioned scenarios…..

1. FTPing the file from one server location to another
2. Deleting the file

We can simply execute those in SQR by using CALL SYSTEM built-in…Here is the syntax

CALL SYSTEM Using $del_file #del_status Wait

if edit(#del_status,’9′) = ‘0′
show ‘intfiles.txt was deleted sucessfully’
end-if

$del_file – Specifies the operating system command to execute. The command can be a quoted string, string variable, or column.
#del_status – Represents the status of execution of OS Command. This is always going to be Numeric variable
UNIX/Linux: Zero (0) indicates success. Any other value is the system error code.
PC/ Microsoft Windows: A value less than 32 indicates an error.
Wait – This flag is used to enable to do multitask processing.

(Microsoft Windows only): WAIT specifies that SQR suspend its execution until the CALL SYSTEM command has finished processing. NOWAIT specifies that SQR start the CALL SYSTEM command but continue its own processing while that command is in progress.

For Microsoft Windows, the default is NOWAIT. On UNIX\Linux operating systems the behavior is always WAIT.

Note : While executing OS Commands, we should append the value of Command Environment Settings along with OS Command. This is done by !Command Environment Settings
let $comspec = getenv(‘COMSPEC’) /* For Window OS */
let $comspec = getenv(‘SHELL’) /* For UNIX */
let $del_file = $comspec||’ /c del’||$int_file_name

Read More About  How To Execute OS Commands

How to trace AE and determine which AE has trace enabled?

In this blog, I will answer two questions.

1. How to trace AE?
2.  

There are a few ways to trace an AE program. You can enable the trace using some of the below methods:

1. In the process scheduler configuration file psprcs.cfg
2. You can enable the trace in the“Process Definition”.
3. Grab the Oracle session information and generate SQL Trace
4.  

It is not advised to enable the trace using psprcs.cfg because it will enable trace for ALL AE programs using that scheduler. However, this might be the best choice in an environment where you want to trace multiple AE programs or you do not have access to modify the process definition. Exercise this option with caution knowing that it might generate trace files for all AE programs.

To enable trace using “Process Definition”, make the below change. You can use the “-TRACE” or “-TOOLSTRACESQL” or “-TOOLSTRACEPC” option depending on what information you require.

1.  
2. Which AE program has trace enabled in its process definition?
3.  

The below SQL will help you determine all the AE programs that have TRACE enabled. It will be useful to use this SQL in your monitoring scripts if you notice developers enabling trace using the process definitions and getting the program with its process definition migrated all the way to production or if your analyst or DBA have the tendency to enable the trace and forget about it.

SELECT PRCSNAME, PARMLIST

FROM PS_PRCSDEFN

WHERE UPPER(PARMLIST) LIKE ‘%TRACE%’

AND PRCSTYPE = ‘Application Engine’;

Read More About How To Trace AE